[2008] Problème convert et sous requête

Version imprimable

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:

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:

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:

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:

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 ?

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:

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

02/08/2013, 10h19
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)
02/08/2013, 10h44
OokieDookie91

J'avais un peu lu de travers...

Là j'ai pas le niveau, mais ça sent la subtilité de conversion implicite amha.
02/08/2013, 11h09
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:

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:

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:

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 ...
02/08/2013, 14h53
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:

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:

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:

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 ...

++
02/08/2013, 15h13
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:

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.

Citation:

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:

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
++