Discussion :

[Lyche]

Bonjour,

je me retrouve avec un soucis au sujet d'un retour de requête qui me parait étrange.

Je vous donne les codes :

Code sql :

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

1
2
3
4
5
6
7
8
 
SELECT *
  FROM dbo.tblUser
 WHERE UserID = '';
 
SELECT *
  FROM dbo.tblUser
 WHERE UserID = 0;

Le contexte : Sur une table avec un UserID Int Non NULL Indentity(1, 1)

Le soucis : Comment ces deux requêtes peuvent me retourner le même résultat, à savoir la ligne dont le UserID = 0 ? ce qui me pose soucis dans beaucoup de requêtes puisque j'ai parfois des jointures sur des fichiers fournis ou les données du UserID sont '' ce qui fait qu'il me compte la ligne UserID 0 comme étant égale à celles dont le UserID = ''

Est-ce un comportement normal, ou est-ce un soucis de ma Base et donc qu'il faut que je prennent en compte ce problème?

Cordialement,
Lyche

[sevyc64]

Oui, il y a un soucis.

'' ne signifie pas et n'a jamais signifié et ne signifiera jamais Null.

Ta colonne est de type Int, le paramètre que tu passe n'est pas de type Int. Le moteur SQL met donc en place une variable interne afin de pouvoir effectuer la conversion, variable de type Int avec comme valeur par défaut 0.
'' n'étant pas une valeur numérique, la conversion ne peut être faite et donc la variable interne est utilisée avec sa valeur par défaut qui est 0
Donc '' = 0

Refait ta requette avec UserID IS NULL et tu verra la différence

[StringBuilder]

Attention, avec Oracle, '' is null

Et si je ne m'abuse, on peut paramétrer SQL Server pour avoir le même comportement !

Vérifiez donc les paramètres de votre base de données.

[Lyche]

je cherchais pas les null, je sais très bien qu'un ne cherche pas un null en faisant un = null...

Je me demandais pourquoi = '' me donnait le même résultat que = 0.

Répondre aux gens c'est cool, je te remercie de la réponse, mais essaye de pas les prendre pour des incompétents non plus...

[StringBuilder]

Bonjour,

Je confirme le comportement absolument étrange !

Code sql :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
 
create table test
(
	id int not null identity(1,1) primary key,
	nom varchar(30) not null
);
 
set identity_insert test on;
insert into test (id, nom) values (0, 'toto');
insert into test (id, nom) values (1, 'titi');
insert into test (id, nom) values (2, 'tata');
set identity_insert test off;
 
(1 row(s) affected)
 
(1 row(s) affected)
 
(1 row(s) affected)
 
select *
from test where id = 0;
 
id          nom
----------- ------------------------------
0           toto
 
(1 row(s) affected)
 
select *
from test where id is null;
 
id          nom
----------- ------------------------------
 
(0 row(s) affected)
 
select *
from test where id = '';
 
id          nom
----------- ------------------------------
0           toto
 
(1 row(s) affected)
 
select * from test where 0 = '';
 
id          nom
----------- ------------------------------
0           toto
1           titi
2           tata
 
(3 row(s) affected)

D'après SQL Server, 0 = '' (???)

Moi je vote, et je dis que ça pue le bug quand même...

Parce que si on suit les règles de conversion implicite :

'' converti en int ça devrait provoquer une excption "not a number" non ?
Et dans l'autre sans, 0 devrait être transformé en '0' qui n'est en aucun cas égal à '' !

Après vérifications, c'est bien '' qui est converti en 0 ! Attention, ' ' aussi est converti en 0 !

Code sql :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
 
select CAST('' as int)
 
-----------
0
 
(1 row(s) affected)
 
select CAST(0 as varchar)
 
------------------------------
0
 
(1 row(s) affected)
 
select CAST(' ' as int)
 
-----------
0
 
(1 row(s) affected)
 
 
select CAST('      ' as int)
 
-----------
0
 
(1 row(s) affected)

SQL Server 2008R2


En tout cas, un exemple de plus qui prouve l'importance d'utiliser des requêtes paramétrée plutôt que des valeurs littérales quand on programme : un langage de haut niveau (C++, C#, Java, etc.) interdira la conversion implicite de '' en 0 et devrait résoudre le problème.

[Waldar]

Vous comparez un entier avec une chaîne de caractères => conversion implicite non maîtrisée => le mal.

[Lyche]

Moi je vote, et je dis que ça pue le bug quand même...

un

Code sql :

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

select * from maTable where 0 = '';

retourne toutes les lignes de la table. Ce qui dans le sens d'interprétation de sevyc64 est tout à fait logique. Si le '' se convertis implicitement en 0 (et cette requête tend à me conforter dans vers cette voie. Alors la lecture de la requête sera 0 = 0. Donc Toujours vrai.

En tout cas, un exemple de plus qui prouve l'importance d'utiliser des requêtes paramétrée plutôt que des valeurs littérales quand on programme : un langage de haut niveau (C++, C#, Java, etc.) interdira la conversion implicite de '' en 0 et devrait résoudre le problème.

C'est vrai, sauf que je suis en gestion de BDD donc je ne passe pas par d'autres langages que le T-SQL, et que parfois les fichiers reçus des clients sont très très loin d'être bons. Il est vrai en revanche que j'aurais du convertir les '' de la table d'import en null, afin d'éviter ce soucis. Je ne pensais juste pas (je crois que j'avais eu le cas dans le passé mais que ça ne m'a pas marqué à ce moment là), que mon '' serait traité ainsi par le SGBD.

Merci pour vos réponses et vos analyses.

Cordialement,
Lyche

[StringBuilder]

Waldar > Oui, les conversion implicites c'est le mal. Mais pour le coup, SQL Server a un comportement qui va en dépit du bon sens (bon, une fois qu'on le sait...)

Ensuite, pour Lyche, je pense que vous pouvez résoudre votre problème de la sorte :

Code sql :

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

1
2
 
where id = (case len(@val) when 0 then null else cast(@val as int) end)

En effet, pour SQL Server, un varchar(30) qui ne contient que des espaces ou chaîne vide à une longueur de 0 (il fait implicitement un rtrim() sur la chaîne).
Donc vous pouvez de la sorte remplacer à la volée vos chaînes vides par NULL dans votre requête sans avoir besoin de modifier votre programme.

[Lyche]

Waldar > Oui, les conversion implicites c'est le mal. Mais pour le coup, SQL Server a un comportement qui va en dépit du bon sens (bon, une fois qu'on le sait...)

Ensuite, pour Lyche, je pense que vous pouvez résoudre votre problème de la sorte :

Code sql :

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

1
2
 
where id = (case len(@val) when 0 then null else cast(@val as int) end)

En effet, pour SQL Server, un varchar(30) qui ne contient que des espaces ou chaîne vide à une longueur de 0 (il fait implicitement un rtrim() sur la chaîne).
Donc vous pouvez de la sorte remplacer à la volée vos chaînes vides par NULL dans votre requête sans avoir besoin de modifier votre programme.

NULLIF( champ, '' ) peut fonctionner aussi (enfin je pense ) plutôt qu'un case when

[fsmrel]

Bonjour,

Je me demandais pourquoi = '' me donnait le même résultat que = 0.

Parce que MS SQL Server est délibérément délinquant.


Définissons une table T ainsi :

Code SQL :

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

1
2
3
4
5
CREATE TABLE T
(
        UserId    INT           NOT NULL 
      , UserId2   VARCHAR(16)   NOT NULL
) ;

Et insérons la ligne suivante :

Code SQL :

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

INSERT INTO T (UserId, UserId2) VALUES (0, '') ;

Que dire de l’instruction ci-dessous ?

Code SQL :

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

1
2
3
SELECT UserId, UserId2 + 5
FROM   T
WHERE  UserId = UserId2 ;

Comme du point de vue théorique on ne peut comparer des colonnes que si elles sont du même type, cette instruction doit être rejetée. Si MS SQL Server ne le fait pas, alors il est laxiste et délinquant. Même chose quand il accepte d'effectuer des opérations arithmétiques ayant pour opérandes des données dont le type n'est pas numérique.

Par ailleurs, le type INT est un sous-ensemble de l’ensemble Z des entiers relatifs dont '' ne fait pas partie, donc MS SQL Server est doublement délinquant en faisant comme si. En ce sens, il est en contradiction avec sa propre définition du type INT et de ses variantes BIGINT, SMALLINT et TINYINT :

Types de données représentant des valeurs numériques exactes qui utilisent des entiers.

Les tribunaux relationnels devraient lui infliger un châtiment exemplaire...

[fsmrel]

En complément :

Une fois le mal identifié, il est préférable d’utiliser un remède préventif de bonne « fâme » (réputation) tel que l’opérateur CAST :

Code SQL :

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

WHERE  CAST (UserId AS VARCHAR(16)) = UserId2

Au lieu de :

Code SQL :

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

WHERE  UserId = UserId2