Discussion :

[Batoche]

Bonjour,

Je viens de faire face à un curieux problème, que j'ai contourné, mais vos avis m'intéressent sur ce 'truc' :

Je précise que j'ai le soucis sur un SQL Server 2008 SP3 64bits
Voici donc la requête (simplifiée le plus possible pour illustrer le pb) :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
 
  declare @test as table(string varchar(10))
  insert into @test values('01234 aaaa')
  insert into @test values('12345 bbbb')
  insert into @test values('cccccccccc')
 
  select sub.ceci_est_un_integer
  from
  (
	  select convert(integer,left(string,2)) ceci_est_un_integer
	  from @test
	  where string like '[0-9][0-9]%'
  ) as sub

Jusque là, tout va bien, j'ai bien comme résultat, les valeurs int 1 et 12.

Et là, je rajoute juste un petit where :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
  declare @test as table(string varchar(10))
  insert into @test values('01234 aaaa')
  insert into @test values('12345 bbbb')
  insert into @test values('cccccccccc')
 
  select sub.ceci_est_un_integer
  from
  (
	  select convert(integer,left(string,2)) ceci_est_un_integer
	  from @test
	  where string like '[0-9][0-9]%'
  ) as sub
  where sub.ceci_est_un_integer > 1

Et paf :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
 
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
Msg 245, Level 16, State 1, Line 6
Conversion failed when converting the varchar value 'cc' to data type int.

==> Je suppose que le SGBD tente de simplifier la requête, mais même si j'écris la requête de la sorte :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
 
  select convert(integer,left(string,2)) ceci_est_un_integer
  from @test
  where string like '[0-9][0-9]%'
  and convert(integer,left(string,2)) > 1

Et bien ça fonctionne.

Du coup, qu'en pensez-vous ?

[OokieDookie91]

Bonjour,


C'est au niveau de votre sélection que ça pêche, vous laissez passez des données non numériques.
Et vous n'avez pas besoin d'une sous-requête pour gérer votre demande.
Ceci ne me renvoie bien que le int 12.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
  declare @test AS TABLE(string varchar(10))
  INSERT INTO @test VALUES('01234 aaaa')
  INSERT INTO @test VALUES('12345 bbbb')
  INSERT INTO @test VALUES('cccccccccc')
 
SELECT convert(integer,LEFT(string,2)) AS ceci_est_un_integer
FROM @test
WHERE string LIKE '[0-9][0-9]%'
AND CASE WHEN ISNUMERIC(convert(integer,LEFT(string,2))) = 1 THEN convert(integer,LEFT(string,2)) ELSE 0 END > 1

Bonne journée

[Batoche]

Ah oui, je n'avais pas pensé au ISNUMERIC, merci.

Mais ce que je ne comprend pas c'est qu'avec le LIKE '[0-9][0-9]%', je devrais écarter toute sélection non numérique, non ? (d'ailleurs, mon dernier exemple sans la sous requête fonctionne)

[OokieDookie91]

J'avais un peu lu de travers...

Là j'ai pas le niveau, mais ça sent la subtilité de conversion implicite amha.

[Batoche]

J'en ai discuté avec un collègue, et en fait, on pense que c'est une simplification automatique qui modifie l'ordre de test tels que ce qu'ils devraient être dans le cas de la sous requête :

En gros la sous requete fait un LIKE sur un varchar, c'est l'étape 1
- le select convert est effectué à la suite de cette étape 1, c'est étape 2
- et la requete fait une comparaison d'entier, c'est l'étape 3

==> On suppose que le SGBD détermine automatiquement qu'il est possible 'd'applatir' la requête mais modifie les priorité d'exécutions des test tels qu'ils sont déterminés par la structure de la sous-requete. On se retrouve alors avec, au même niveau, un test de type like et un test de type comparaison d'entier. Nous supposons que le SGDB préfère commencer par tester la comparaison sur les entiers en premiers (etape 3) puis appliquer le LIKE ensuite (etape1).

Et effectivement si la requête suivante fonctionne :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
 
select convert(integer,LEFT(string,2)) ceci_est_un_integer
  from @test
  where string like '[0-9][0-9]%'
  and convert(integer,LEFT(string,2)) > 1

en inversant les deux contraintes, on retrouve notre erreur

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
 
  select convert(integer,LEFT(string,2)) ceci_est_un_integer
  from @test
  where convert(integer,LEFT(string,2)) > 1
  and string like '[0-9][0-9]%'

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
 
Msg 245, Level 16, State 1, Line 6
Conversion failed when converting the varchar value 'cc' to data type int.

Après je ne sais pas si c'est réellement à cause de ça ou pas ...

[mikedavem]

Hello,

Tout dépend de comment l'optimiseur de requête va réagir ... et des coûts engendrés par les différents prédicats.

Dans ton cas cela fonctionne parfaitement. Prenons une table de test et un jeu de données :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
CREATE TABLE test
(
 col2 varchar(10)
);
GO
 
insert test values ('test1')
go 10000
 
insert test values ('11111')
go 10000

Avec le premier prédicat pas de souci dans mon cas :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
WHERE string LIKE '[0-9][0-9]%'
  AND convert(integer,LEFT(string,2)) > 1

Mais par exemple avec le prédicat suivant :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
WHERE string LIKE '[0-9][0-9]%'
  AND convert(integer,string) > 1

Dans mon cas l'ordre des prédicats change lorsque je regarde dans la section predicate de l'opérateur (table scan)

En résumé l'ordre d'exécution des prédicats dans le WHERE dépendra uniquement de l'optimiseur en fonction des coûts engendrés par chaque prédicat ...

++

[Batoche]

Merci pour cette explication (mais du coup est ce qu'il est possible d'avoir un aperçu de la requête, après optimisation ?)

Je me doutais bien qu'on ne pouvait pas être assuré de l'ordre des test d'un WHERE.

J'ai cependant été étonné de constater que même lors de l'emploi d'une sous requête, on ne peut pas 'forcer' un premier filtre (ici le like) pour pouvoir ensuite appliquer une second filtre (le > 1) sur le résultat du premier.

Et donc ecrire

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
 
select result
from
( 
  select resultat_filtre1
  from data
  where filtre1(data)
) sub
where filtre2(resultat_filtre1)

Ne garanti pas du tout que filtre2 portera sur les données filtrées par filtre1, malgré les apparences.

[mikedavem]

Merci pour cette explication (mais du coup est ce qu'il est possible d'avoir un aperçu de la requête, après optimisation ?)

La requête est la même que la tienne mais je n'ai sûrement pas compris ce que tu voulais je pense :-)


L'emploi d'une sous requête ne changera pas grand chose ici car l'optimiseur est assez intelligent pour savoir qu'au final ce qu'on lui demande est de faire un SELECT d'une même table avec 2 prédicats (phase d'analyse).

Pour pouvoir forcer l'ordre des prédicats pas d'autre choix que d'utiliser un CASE par exemple :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
SELECT *
FROM test
WHERE CASE ISNUMERIC(col2)
           WHEN 1 THEN CASE
                               WHEN col2 LIKE '[0-9][0-9]%' AND CAST(col2 AS INT) > 1 THEN 1
                               ELSE 0
                              END
           ELSE 0
          END = 1

++