Discussion :

[fsmrel]

En SQL ,Sum(X+Y) et Sum(X) + Sum(Y) ne donnent pas nécessairement le même résultat.

Exemple :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
 
CREATE TABLE T
(X          Integer,
 Y          Integer
) ;
INSERT INTO T VALUES (5, null) ;
INSERT INTO T VALUES (0, 0) ;
 
Select Sum(X+Y) as 'Sum(X+Y)'
from T
 
Select Sum(X) + Sum(Y) as 'Sum(X) + Sum(Y)'
from T

Avec SQL Server 2005, on obtient respectivement 0 et 5. Dur, dur...

Qu’en est-il avec la norme ? Avec les autres SGBD ?

[J1]

Bonjour,

sous Access 2003, j'obtiens le même résultat, ce qui me semble normal.
En effet, contrairement à l'opérateur "+", la fonction d'agrégation SUM ignore le NULL.
En d'autres termes, là où l'opérateur "+" va laisser se propager NULL à l'ensemble de la somme, la fonction SUM va, elle, ne sommer que les valeurs non-NULL.

Ainsi, si l'on décompose ta première requête, le SGBD la traite ainsi :

Etape 1
l'opérateur "+" somme X et Y sur la première ligne :
5 + NULL = NULL (l'opérateur "+" n'ignore pas le NULL, qui se "propage" donc)
puis sur la deuxième ligne :
0 + 0 = 0

Etape 2
la fonction SUM somme ensuite chaque sous-total :
SUM(NULL,0) = 0 (la fonction SUM ignore le NULL, qui ne se propage donc pas)

Le résultat final de ta première requête est donc bien 0.


Maintenant, si on décompose ta deuxième requête, le SGBD la traite ainsi :

Etape 1
la fonction SUM somme les X de chaque ligne :
SUM(5,0) = 5
puis somme les Y de chaque ligne :
SUM(NULL,0) = 0 (la fonction SUM ignore le NULL, qui ne se propage donc pas)

Etape 2
l'opérateur "+" somme ensuite le résultat de chaque SUM :
5 + 0 = 5

Le résultat final de ta deuxième requête est donc bien 5.


Remarque : pour les besoins de l'explication, je représente schématiquement le fonctionnement de la fonction SUM, mais il est bien évident que sa syntaxe n'est pas réellement de la forme SUM(valeur1,valeur2).

[fsmrel]

Bonjour J1,

Merci pour votre réponse détaillée et pour avoir pris le temps de vérifier les résultats concernant Access. Notre approche est la même, car nous sommes manifestement en phase avec le raisonnement tenu par Ted Codd concernant le comportement des opérateurs arithmétiques dans le contexte d’une logique trivalente :

x + null = null

Vous écrivez : "la fonction SUM ignore le NULL, qui ne se propage donc pas". Mais si l’on code :

SELECT SUM (1 + Null)

le résultat est quand même null...

Peu importe. Le comportement général de SQL m'incite à déclarer systématiquement les colonnes des tables comme "Not null".

En effet, pour reprendre l'exemple de SUM, quand pour une même application, deux développeurs coderont en toute bonne foi, l'un SUM(X+Y) et l'autre SUM(X) + SUM(Y), l'utilisateur final obtiendra des résultats différents dès qu'une valeur nulle sera présente, ne serait-ce que le temps d'une minute. En revanche, si X et Y contiennent des nombres et rien d’autre, les deux formules sont bien équivalentes et les résultats égaux.

On peut supposer que le détail des opérations que vous avez fourni vaut pour la norme SQL. Peut-être avez-vous une réponse à ce sujet, sinon peut-être SQLpro nous éclairera-t-il ?

[J1]

Vous écrivez : "la fonction SUM ignore le NULL, qui ne se propage donc pas". Mais si l’on code :

SELECT SUM (1 + Null)

le résultat est quand même null...

Le comportement que tu décris est justement en phase avec mon explication.
En effet,

Code :

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

1
2
 
SELECT SUM(1 + NULL)

renvoie NULL car

Code :

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

(1 + NULL)

renvoie NULL.
La requête équivaut donc à

Code :

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

1
2
 
SELECT SUM(NULL)

Or cette dernière requête renvoie bien NULL. Non pas parce que SUM tente de sommer NULL, mais au contraire parce que SUM exclut NULL de la somme et n'a donc rien à sommer. En conséquence, la fonction renvoie NULL pour indiquer que le résultat de cette somme est indéfini, car sans opérande.

On peut supposer que le détail des opérations que vous avez fourni vaut pour la norme SQL. Peut-être avez-vous une réponse à ce sujet, sinon peut-être SQLpro nous éclairera-t-il ?

Trois cerveaux valent mieux que deux, je serais donc ravi d'avoir le point de vue de SQL Pro sur la question.


Remarque : pour que la discussion ne soit pas trop complexe à prendre en route, je précise que la syntaxe

Code :

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

1
2
 
SELECT SUM(1 + NULL)

employée ici n'a bien sûr aucun lien avec la syntaxe SUM(valeur1,valeur2) que j'employais tout à l'heure pour symboliser le fonctionnement de la fonction SUM.

[Antoun]

Je rejoins J1 sur tous les points. J'en profite pour préciser qu'Oracle et MySQL ont le même comportement.

Quant au fait de ne pas utiliser NULL, ça me semble une erreur fondamentale. Le NULL a effectivement des propriétés contre-intuitives, mais souvent souhaitables.

[fsmrel]

Quant au fait de ne pas utiliser NULL, ça me semble une erreur fondamentale

Une telle affirmation demande d'être étayée.

Cela dit, je tiens à rappeler en passant, que lorsque l'on écrit :

Code :

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

1
2
3
4
5
 
Create table T
(
    X     Integer 
)

X est du type entier mais que NULL n'est pas un élément de l'ensemble des entiers (même si SQLpro fera la distinction entre NULL et marque nulle) : la mixture reste suspecte et conduit à des choses bizarres du genre du comportement de la fonction SUM, ou à un constat du genre :

X < 0 ou X = 0 ou X > 0

n'est pas une tautologie.

Ne pas utiliser NULL est peut-être une erreur, mais utiliser NULL conduit vite à l'erreur. Un spécialiste de la chose sera d'une vigilance extrême, mais tout le monde n'est pas spécialiste. La logique ternaire nous entraîne vers des terrae incognitae, sur une pente fatale (comme dirait Fernand Naudin).