Variations sur NULL, ou SUM(X+Y) <> SUM(X) + SUM(Y) ?

Version imprimable

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

Exemple :
Code:

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 ?

01/03/2008, 11h20
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).
01/03/2008, 16h05
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 ?
01/03/2008, 17h25
J1
Citation:

Envoyé par fsmrel

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:

1 2 SELECT SUM(1 + NULL)
renvoie NULL car

Code:

(1 + NULL)

renvoie NULL.
La requête équivaut donc à
Code:

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.

Citation:

Envoyé par fsmrel

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:

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.
01/03/2008, 22h35
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.
01/03/2008, 23h57
fsmrel
Citation:

Envoyé par Antoun

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:

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).
02/03/2008, 00h20
Antoun
Citation:

Envoyé par fsmrel

Ne pas utiliser NULL est peut-être une erreur, mais utiliser NULL conduit vite à l'erreur.

Je suis d'accord que les NULL sont trompeurs. Néanmoins, ne pas utiliser NULL conduit également à l'erreur.

Supposons par exemple que tu aies une liste de livres (ou de n'importe quoi d'autre) avec leur prix d'achat. Tu en as eu certains gratuitement, ils ont donc un prix 0. Pour d'autres, tu les a vraiment payés, mais tu as oublié de noter le prix. Un prix NULL s'impose !

Si tu t'interdis le NULL, comment gérer ce cas ?
- La solution la plus évidente (et la plus mauvaise) consiste à mettre 0 ; mais dans ce cas, tu ne distingues plus ce livre payé de ceux qui t'ont été offerts. Quand tu vas calculer le coût moyen par livre, il faudrait distinguer les gratuits (qui doivent effectivement faire baisser la moyenne) des prix inconnus (qui ne doivent pas être pris en compte). SQL te fera ça naturellement, en ignorant les NULL et en prennant en compte les zéros.
Dans cet exemple, c'est donc le NULL qui évite l'erreur, et l'interdiction du NULL qui est trompeuse.
- Autre hypothèse : tu utilises un prix impossible, par exemple -1 ; ça te permet effectivement de distinguer les prix inconnus des gratuits. Mais il faudra faire attention lors de l'écriture de la requête de moyenne à écarter les -1. Là encore, le NOT NULL pousse à l'erreur.
Plus généralement, si le NULL effectivement trompeur, son comportement est standardisé. Ne pas l'utiliser conduit souvent à utiliser des contournements qui sont tout aussi trompeurs, mais de manière spécifique à chaque application. A mon sens, c'est bien pire !

Dernières solutions sans NULL :
- Tu enregistres un prix approximatif... ça peut se défendre dans certains cas, mais il y en a évidemment où une absence d'info est préférable à une info inexacte.
- Tu n'enregistres pas du tout le livre... c'est la seule solution qui soit vraiment satisfaisante du point de vue de la cohérence, mais c'est évidemment dommage pour l'exhaustivité !
02/03/2008, 01h46
fsmrel

Citation:

Envoyé par Antoun

Supposons par exemple que tu aies une liste de livres (ou de n'importe quoi d'autre) avec leur prix d'achat. Tu en as eu certains gratuitement, ils ont donc un prix 0

Tout d'abord, les livres figurent tous dans une table (disons LIVRE).

Maintenant, si un livre coûte 0, il coûte 0. Si je ne connais pas son prix, celui-ci n'est ni 0 ni aucun autre nombre, positif ou négatif. Les prix font l'objet d'une table (disons LIVRE_PRIX) dans laquelle ne figurent que les seuls livres dont le prix est connu (qu'il soit égal ou supérieur à 0).

Les livres figurent ainsi intégralement dans la base données, sans avoir recours à NULL (ou à une quelconque valeur par défaut). Le calcul du prix moyen et autres fonctionnent, il n'y a pas besoin de tester de marque -1, etc.
02/03/2008, 01h54
Antoun

Citation:

Envoyé par fsmrel

Maintenant, si un livre coûte 0, il coûte 0. Si je ne connais pas son prix, celui-ci n'est ni 0 ni aucun autre nombre, positif ou négatif. Les prix font l'objet d'une table (disons LIVRE_PRIX) dans laquelle ne figurent que les seuls livres dont le prix est connu (qu'il soit égal ou supérieur à 0).

Pur artifice (qui a en outre l'inconvénient de multiplier le nombre de tables sans réelle justification). En effet, cela t'amènera rapidement à utiliser une jointure externe entre LIVRE et LIVRE_PRIX... et la jointure externe attribuera un prix NULL à tous les livres sans prix. Je déduis donc te ta solution que tu es effectivement convaincu de l'utilité des NULL, mais que tu te refuses à assumer leur création ;).

Citation:

Envoyé par Antoun

Pur artifice (qui a en outre l'inconvénient de multiplier le nombre de tables sans réelle justification).

Ceci ne relève pas de l’artifice mais de l’art de la modélisation. L’inconvénient que vous évoquez est d’ordre psychologique et n’a rien à voir avec le problème initial qui relève de la logique et justifie l’opération de projection que j’effectue. Cet argument dénonçant la prolifération des tables a été avancé des milliers de fois pendant plus de trente ans, ne serait-ce que dans le cadre de la normalisation (respect au moins de la 3e forme normale), selon lequel, normaliser multiplie le nombre de tables et serait catastrophique par exemple pour la performance de la base de données. On sait qu’il n’en est rien, dès lors que l’on prend la peine de s’en assurer, en bâtissant des prototypes de performance pertinents et que la Production n’a pas d’état d’âme face au nombre de table spaces et d'index à gérer, dès lors qu’ils sont correctement mis en œuvre.
De mon côté, depuis longtemps j’ai été amené à concevoir des bases de données comportant de l’ordre de 1500 à 2000 tables, certaines comportant des dizaines, voire des centaines de millions de lignes, sans que la performance des applications n’en souffre. Simplement, j’ai passé des nuits à transpirer à prototyper, avant d’engager contractuellement mon entreprise quant aux performances des requêtes embarquées dans les programmes.

Citation:

Envoyé par Antoun

cela t'amènera rapidement à utiliser une jointure externe entre LIVRE et LIVRE_PRIX

Jadis, la jointure externe n’existait pas, cela ne me dérangeait aucunement et je ne l’utilise toujours pas. Une jointure naturelle et une Union me suffisent pour produire un résultat à usage de l’utilisateur final, tel que celui-ci :
Code:

1 2 3 4 LivreId Titre Auteur Prix L1 Vingt ans après Dumas père 20 L2 La Dame aux camélias Dumas fils /
Citation:

Envoyé par Antoun

Je déduis donc te ta solution que tu es effectivement convaincu de l'utilité des NULL, mais que tu te refuses à assumer leur création

Déduisez plutôt que je suis convaincu non seulement de l’inutilité des NULL, mais aussi du danger qu’ils représentent (Cf. le titre de la discussion).
Je reconnais néanmoins que SQL me les impose inévitablement. Je les considère comme des maux dont il est impossible de se débarrasser, même en prenant toutes les précautions. Si je les mets à la porte, le Sorry Query Language me les renvoie par la fenêtre.

Par exemple, avec SQL Server 2005, si je soumets la requête :
Code:

1 2 3 4 Select Prix From LivrePrix Where Prix > 100
Le résultat comporte zéro ligne, c’est un ensemble vide. Parfait.

Si maintenant je soumets la requête :
Code:

1 2 3 4 Select Max(Prix) As PrixSup100 From LivrePrix Where Prix > 100
Le résultat comporte une ligne (contenant évidemment NULL...), c’est un ensemble non vide.

Et si je soumets la requête :
Code:

1 2 3 4 5 Select Prix, Max(Prix) As PrixSup100 From LivrePrix Where Prix > 100 Group by Prix
Le résultat comporte à nouveau zéro ligne.

Bizarre, bizarre... J’ai dit bizarre ? Comme c’est bizarre...

Au passage, quel le comportement d’Oracle et de MySQL concernant ces requêtes ?

Je note entre moult choses, que les NULL peuvent être à l’origine de failles concernant l’intégrité référentielle.
Considérez l’exemple suivant (qui n’est pas à recommander puisqu’il manque un "Not Null", mais plausible) :
Code:

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 Create table Client (CliId Char(4) Not Null, Nom VarChar(48) Not Null, Primary Key (CLiId) ) ; Create table Facture (CliId Char(4) Not Null, FactId Char(4) Not Null, FactDate Char(10) Not Null, Primary Key (CLiId, FactId), Foreign Key (CliId) References Client ) ; Create Table Suivi (SuiviId Char(4) Not Null, CliId Char(4) Not Null, FactId Char(4), Primary Key (SuiviId), Foreign Key (CliId, FactId) References Facture ) ; Insert Into Client Values ('cli1', 'Client Un') ; Insert Into Client Values ('cli2', 'Client Deux') ; Insert Into Facture Values ('cli1', 'Fac1', '2008-03-02') ; Insert Into Suivi Values ('Sv01', 'cli1', 'Fac1') ; Insert Into Suivi Values ('Sv02', 'cli7', null) ; Select * From Suivi
Le client ’cli7’ n’existe ni dans la table Client, ni dans la table Facture et pourtant il est présent dans la table Suivi, malgré l’intégrité référentielle. Pour blinder le système, on pourrait établir une contrainte référentielle entre Client et Suivi, mais on commence à rapiécer les morceaux comme on peut, au coup par coup. En fait, la colonne FactId de la table Suivi doit comporter la clause "Not Null", à l’instar de la table LIVRE.

02/03/2008, 18h09
J1
Bonjour à toi fsmrel, ainsi qu'à Antoun, qui a rejoint la discussion :P

Ce week-end aura décidément été placé sous le signe du NULL, sujet intéressant il faut l'avouer !

Etudions les 3 requêtes que tu exposes, fsmrel.
Code:

1 2 3 4 Select Prix From LivrePrix Where Prix > 100
Tu demandes ici au SGBD de te fournir le prix des livres dont le prix est supérieur à 100. Le résultat de ta requête (aucune ligne) indique qu'il n'y a aucun livre répondant à ce critère. Bien.

Remarque : tu indiques que

Citation:

Envoyé par fsmrel

Le résultat comporte zéro ligne, c’est un ensemble non vide. Parfait.

Je suppose que c'est une coquille et que tu voulais écrire "ensemble vide", mais là n'est pas la question.

***

Dans ta requête suivante,
Code:

1 2 3 4 Select Max(Prix) As PrixSup100 From LivrePrix Where Prix > 100
tu demandes au SGBD de t'indiquer le prix maximum des livres dont le prix est supérieur à 100. La fonction MAX s'applique donc sur un ensemble vide. Le SGBD t'indique donc que son résultat est indéfini (NULL).

***

Passons à la requête suivante. Je comprends bien que c'est un cas d'école, je ne discuterai donc pas son intérêt. L'important étant seulement ici qu'elle soit syntaxiquement correcte.
Code:

1 2 3 4 5 Select Prix, Max(Prix) As PrixSup100 From LivrePrix Where Prix > 100 Group by Prix
Tu demandes ici au SGBD de t'indiquer, pour chaque prix supérieur à 100, quel est le prix maximum. C'est ce que le SGBD fait : il ré-exécute d'abord implicitement ta première requête
Code:

1 2 3 4 SELECT Prix FROM LivrePrix WHERE Prix > 100
qui ne renvoie aucune ligne.

Le SGBD regroupe ensuite ces lignes en fonction de leur prix (clause GROUP BY Prix).
Il produit donc autant de lignes qu'il y a de prix distincts.
Il ne produit donc aucune ligne.
Ensuite, si des lignes avaient été renvoyées, la fonction MAX aurait calculé, pour chacun de ces prix distincts, le prix maximum.

Dans ta deuxième requête, on voyait que la fonction MAX appliquée à un ensemble vide renvoyait NULL. Ici, le cas est différent, tu cherches à appliquer la fonction MAX à une absence d'ensemble.

La subtilité ne vient en fait pas de MAX, mais de GROUP BY. C'est parce qu'aucun ensemble ne ressort suite à la clause GROUP BY que la fonction MAX ne peut pas s'exécuter.

Pour nous en convaincre, indiquons nous-même un ensemble au SGBD :
Code:

1 2 3 4 5 SELECT Max(Prix) AS PrixEgal101 FROM LivrePrix WHERE Prix > 100 /*critère bien sûr facultatif*/ AND Prix = 101
Je lui demande ici de m'indiquer le prix maximum pour chaque prix égal à 101. C'est donc moi qui définis l'ensemble. Nous sommes donc en face d'un ensemble vide et non d'une absence d'ensemble. Cette fois, le SGBD est donc en mesure de lancer le calcul du prix maximum de cet ensemble et renvoie NULL (comme dans ta deuxième requête), puisqu'aucun prix n'est égal à 101.

PS : personnellement, je n'ai pas la même aversion (façon de parler bien sûr) que toi face au NULL, mais je trouve en tout cas ton point de vue, ainsi que l'ensemble de cette discussion, particulièrement intéressants.

Bonsoir J1,

Week-end effectivement sous le signe du NULL : quelque part ne serions-nous pas un peu pervers ?

Citation:

Envoyé par J1

Citation:

Envoyé par fsmrel

Le résultat comporte zéro ligne, c’est un ensemble non vide. Parfait.

Je suppose que c'est une coquille et que tu voulais écrire "ensemble vide", mais là n'est pas la question.

Désolé pour la coquille, il est évident qu’il fallait lire " ensemble vide" (corrigé le 16/03).

Citation:

Envoyé par J1

personnellement, je n'ai pas la même aversion (façon de parler bien sûr) que toi face au NULL

Il ne s’agit pas particulièrement d’aversion de NULL, mais plutôt d’une certaine crainte, d'un trauma, suite à quelques erreurs de ma part aussi bien que de collègues : certes, des tests très poussés permettent de débusquer ces erreurs, mais le doute plane toujours. Voyez par exemple le cas de l’intégrité référentielle que j’ai évoqué : jamais je ne me résoudrai à accepter NULL pour une clé étrangère (non seulement pour la raison que j’avais invoquée dans mon précédent message, mais aussi pour d’autres effets détestables).

Je me doute que ceux qui font la norme ainsi que les éditeurs de SGBD sont particulièrement vigilants au sujet de NULL, mais à l’autre bout de la chaîne, le développeur qui n’est pas un vieux baroudeur n’est pas à l’abri.

Tout le monde n’a pas votre compétence et votre talent pour montrer comment démonter les mécanismes délicats, merci donc pour votre démonstration. Pour la petite histoire, je retiens encore une chose après vous avoir lu : pour mieux comprendre ce qui se passera lorsqu’une requête sera exécutée, pourquoi ne pas commencer par l’écrire à peu de choses près ainsi ?
Code:

1 2 3 4 5 6 From ... /* le quoi */ Where ... /* filtrage */ Group by ... /* le quoi "nouveau" */ Having ... /* filtrage */ Select ... /* présentation du résultat final */
puis une fois qu’elle sera au point, tel Dagobert, la remettre à l’endroit...

02/03/2008, 23h13
SQLpro

Salut fmsrel,

bonne remarque.

Oui

Code:

SUM(X + Y) <> SUM(X) + SUM (Y)

du fait du NULL.
Si les colonnes X et Y sont not null alors sommes identiques.
Sinon, l'équivalent mathématique est :

Code:

SUM(COALESCE(X, 0) + COALESCE(Y, 0)) = SUM(X) + SUM (Y)

Je devris la rajouter à ma page : http://sqlpro.developpez.com/cours/null/

A +

Bon, J1 a parfaitement répondu sur le fond, je n'ajouterai donc que de petits à-côtés.

Citation:

Envoyé par fsmrel

Ceci ne relève pas de l’artifice mais de l’art de la modélisation.

Certes, mais à ma connaissance, aucune règle de modélisation n'interdit le NULL. J'en déduis donc qu'il s'agit de modélisation en Fsmrel NF ;).

Citation:

Envoyé par fsmrel

L’inconvénient que vous évoquez est d’ordre psychologique

Oui. Cognitif, pour être plus précis. Mais c'est vous qui avez soulevé le problème du NULL sur ce plan. Je pourrais donc vous paraphraser en disant "multiplier les tables 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."
Je précise tout de suite que je dis pas qu'il ne faut pas multiplier les tables ; il faut effectivement le faire en fonction des règles de normalisation, parce que 1/ au total le surcroît de complexité sera plus que compensé par la propreté des données, et 2/ que cela répond à des règles standard que tout développeur devrait connaître.

Pour le coup, il me semble que la règle d'interdiction des NULL n'améliore en rien l'intégrité des données, et tend au contraire à favoriser la création de règles spécifiques à chaque appli, voire même à chaque colonne. Mais sans y a-t-il une bonne part de subjectivité dans le choix entre deux inconvénients, je ne prétends donc pas que mon avis soit définitif sur la question.

Citation:

Envoyé par fsmrel

et n’a rien à voir avec le problème initial qui relève de la logique et justifie l’opération de projection que j’effectue.

oui, mais ça ne la justifie que dans le cadre de la règle d'interdiction des NULL. Si on accepte les NULL, votre opération est inutile (même si elle est parfaitement correcte).

Citation:

Envoyé par fsmrel

Cet argument dénonçant la prolifération des tables a été avancé des milliers de fois pendant plus de trente ans, ne serait-ce que dans le cadre de la normalisation (respect au moins de la 3e forme normale)
[...]

Nous sommes entièrement d'accord sur la prolifération des tables par rapport à la 3NF. Je ne la critiquais que par rapport à la fsmrel NF, où elle me semble excessive, pour les raisons citées plus haut.

Quant à l'argument de la performance, je me suis bien gardé de le soulever : l'objectif de la normalisation n'est pas la performance mais l'intégrité. Et effectivement, je ne crois pas qu'obtenir très rapidement des données fausses présente le moindre intérêt.

(J'ouvre néanmoins une petite parenthèse sur le sujet : je pratique couramment des "requêtes de la mort façon BI" (pour vous citer de mémoire). Et dans ce cadre-là, je dois reconnaître que parfois on atteint un seuil où une jointure de plus ou de moins fait basculer la requête de "un peu longue, une minute d'attente" à "totalement inutilisable". Je ne crois pas que cela remette en cause l'intérêt de la normalisation : ces problèmes de performance se règlent en créant une copie dénormalisée, mais pour créer ces copies, il est indispensable de partir d'un base bien normalisée.)
Citation:
Envoyé par fsmrel
Jadis, la jointure externe n’existait pas, cela ne me dérangeait aucunement et je ne l’utilise toujours pas. Une jointure naturelle et une Union me suffisent pour produire un résultat à usage de l’utilisateur final, tel que celui-ci :
Code:

1 2 3 4 LivreId Titre Auteur Prix L1 Vingt ans après Dumas père 20 L2 La Dame aux camélias Dumas fils /
Une jointure interne et une Union, cela revient effectivement à faire une jointure externe. Et vous obtenez exactement le même résultat, à ceci prêt que vous utilisez / au lieu de NULL. Dans le cadre d'une colonne numérique, cela rejoins ce que je disais quelques paragraphes plus haut : vouloir ignorer les NULL revient à les remplacer par des bidouilles spécifiques.

Citation:

Envoyé par fsmrel

Déduisez plutôt que je suis convaincu non seulement de l’inutilité des NULL, mais aussi du danger qu’ils représentent (Cf. le titre de la discussion).

Je suis tout aussi convaincu que vous du danger des NULL. Mais je pense que les ignorer est encore plus dangereux.

Citation:

Envoyé par fsmrel

Je reconnais néanmoins que SQL me les impose inévitablement. Je les considère comme des maux dont il est impossible de se débarrasser, même en prenant toutes les précautions. Si je les mets à la porte, le Sorry Query Language me les renvoie par la fenêtre.

exactement :lol:

Citation:

Envoyé par fsmrel

Week-end effectivement sous le signe du NULL : quelque part ne serions-nous pas un peu pervers ?

juste portés sur la sodomisation de diptères :mrgreen:

Citation:

Envoyé par fsmrel

Il ne s’agit pas particulièrement d’aversion de NULL, mais plutôt d’une certaine crainte, d'un trauma, suite à quelques erreurs de ma part aussi bien que de collègues

Je crois que nous en sommes tous passés par là !
Citation:
Envoyé par fsmrel
Pour la petite histoire, je retiens encore une chose après vous avoir lu : pour mieux comprendre ce qui se passera lorsqu’une requête sera exécutée, pourquoi ne pas commencer par l’écrire à peu de choses près ainsi ?
Code:

1 2 3 4 5 6 From ... /* le quoi */ Where ... /* filtrage */ Group by ... /* le quoi "nouveau" */ Having ... /* filtrage */ Select ... /* présentation du résultat final */
puis une fois qu’elle sera au point, tel Dagobert, la remettre à l’endroit...
Absolument. C'est d'ailleurs la méthodo que j'enseigne à mes stagiaires.

02/03/2008, 23h34
Antoun

Citation:

Envoyé par SQLpro

Code:

SUM(COALESCE(X, 0) + COALESCE(Y, 0)) = SUM(X) + SUM (Y)

Salut Fred, je m'étonnais de ton absence sur ce débat !

Je complèterais en disant :

Code:

SUM(COALESCE(X, 0) + COALESCE(Y, 0)) = COALESCE(SUM(X) + SUM (Y), 0)

car SUM(X) et SUM(Y) peuvent également être NULL.
03/03/2008, 03h10
fsmrel

Bonsoir,

Citation:

Envoyé par Antoun

à ma connaissance, aucune règle de modélisation n'interdit le NULL. J'en déduis donc qu'il s'agit de modélisation en Fsmrel NF

Ben non... Il s’agit de modélisation en 1NF. Puisqu’il faut une référence indiscutable, je vous renvoie au dictionnaire de Chris Date The Relational Database Dictionary, page 42 :
First Normal Form Normalized. By definition, relvars are always in first normal form... It follows that a "table" in a language such as SQL is in 1NF if and only if it’s a direct and faithful representation of some relvar, where direct and faithful means, among other things, that every row-and-column intersection in that table contains exactly one value of the applicable type, nothing more and nothing less...
Et Date poursuit, dans Date On Database, Writings 2000-2006, page 129 :
The fact that there must be exactly one value at each row-and-column intersection also rules out nulls (as noted earlier in this chapter, nulls aren’t values). Thus, if table T is to be in 1NF, then every column C of T must have "nulls not allowed"...

Citation:

Envoyé par Antoun

"multiplier les tables 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."

Bien paraphrasé. Je précise quand même : si l’univers du discours est dès le départ correctement urbanisé, il n’y a aucun problème : le référentiel Personnes d’un côté, le référentiel Contrats d’un autre, sans oublier le référentiel Produits et garanties, le référentiel Cotisations, les organismes partenaires, la prospection de masse, les habilitations, etc., avec bien entendu la coordination de l’ensemble, il n’y a pas de problème. Mais évidemment, on ne confie pas l’organisation de tout cela à un jeunot, ni même à un seul vieux briscard, mais à une équipe d’organisateurs, de chefs de projets et de concepteurs dont c’est le métier. Si l’assise de l'édifice est solide, assurer ensuite le fonctionnement de l’ensemble ne pose pas de problème pour une équipe de DBA dont la vigilance sera extrême, 24h/24 (performance, intégrité, ... La Production informatique ne manquera pas de donner du fouet au besoin, histoire vécue).

Citation:

Envoyé par Antoun

Pour le coup, il me semble que la règle d'interdiction des NULL n'améliore en rien l'intégrité des données

Tout comme avec la normalisation des données, le but est de mieux garantir la validité des résultats, si ce n’est que la normalisation s’appuie sur les dépendances fonctionnelles, multivaluées et de jointure, dont les définitions mêmes sont muettes quant à NULL : il faudrait que Ronald Fagin donne son point de vue.

Citation:

Envoyé par Antoun

et tend au contraire à favoriser la création de règles spécifiques à chaque appli, voire même à chaque colonne

Qu’entendez-vous par là ? Quoi qu’il en soit, on peut par exemple constater que l’interdiction de dates de fin à NULL (ou, évidemment, de valeurs par défaut) simplifie grandement la programmation. Il n’y pas en l’occurrence de règle spécifique à une appli ou à une colonne, il n’y a qu’une métarègle oserai-je dire, qui est la suivante (pour faire court) : tout ce qui est en cours reste à sa place d’origine (date d’embauche du salarié, date de début de tâche active, etc.) Tout ce qui est achevé (date de fin connue) dégage dans une table à caractère historique. Voyez par exemple la discussion avec Sieldev ou avec Fayred (et il y en a bien d'autres, moins soft...)

Citation:

Envoyé par Antoun

je pratique couramment des "requêtes de la mort façon BI" (pour vous citer de mémoire).

Fichtre ! J’aurais écrit et pratiqué de pareilles choses ?

Citation:

Envoyé par Antoun

vouloir ignorer les NULL revient à les remplacer par des bidouilles spécifiques.

Hélas ! Voilà à quoi me conduit le diktat de Askew Wall... Mais je tiens à préciser que cela ne doit être effectué que lors de la présentation (projection) du résultat final et que l’absence d’information peut alors être représentée par tout symbole convenu avec l’utilisateur, une fois pour toutes.

Citation:

Envoyé par Antoun

Je suis tout aussi convaincu que vous du danger des NULL. Mais je pense que les ignorer est encore plus dangereux.

Vous prêchez un convaincu, il faut les surveiller de très près, c'est de la dynamite et tout le monde n'est pas artificier...

Bonne semaine, en espérant ne pas me faire fusiller ou passer sur une mine...
10/03/2008, 04h46
Antoun

Bon, nous sommes à peu près d'accord sur tout, à commencer par la divergence de nos pratiques ;)

Sur la 1NF, je note tout de même que le point n'est pas si évident. Je tire ceci de la wikipedia :

Citation:

A nullable attribute would be in violation of condition 4, which requires every field to contain exactly one value from its column's domain. It should be noted, however, that this aspect of condition 4 is controversial. It marks an important departure from Codd's original vision of the relational model, which made explicit provision for nulls.

(http://en.wikipedia.org/wiki/1NF#_note-CoddRule)
... et effectivement, cela me rappelle quelques lignes enflammées de Celko sur l'abominable hérésie Chrisdatiste !

Quant aux "requêtes de la mort façon BI", c'est ma mémoire qui était défaillante, non pas sur la citation, mais sur son auteur, puisque c'est TheLeadingEdge qui décrit ainsi mon boulot. Mes excuses à tous les deux ! (je ferais mieux d'arrêter de poster en pleine nuit :oops:).

De Antoun :

Citation:

SUM(COALESCE(X, 0) + COALESCE(Y, 0)) = COALESCE(SUM(X) + SUM (Y), 0)

Inutile, car les NULL sont ignorés lors des opérations d'agrégats. Or une somme de NULL ignorés ou de zero donne le même résultat.
En revanche ce n'est pas la même chose pour la moyenne !

Code:

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
CREATE DATABASE DB_TEST;
GO
 
USE DB_TEST;
GO
 
CREATE TABLE T (X INT, Y INT);
GO
 
INSERT INTO T VALUES (1, 1)
INSERT INTO T VALUES (1, NULL)
INSERT INTO T VALUES (NULL, NULL)
INSERT INTO T VALUES (NULL, 1)
 
 
SELECT SUM(X + Y) AS S0,
       SUM(X) + SUM(Y) AS S1, 
       SUM(COALESCE(X, 0)) + SUM(COALESCE(Y, 0)) AS S2,
       COALESCE(SUM(X) + SUM(Y), 0) AS S3
FROM   T
 
S0          S1          S2          S3          
----------- ----------- ----------- ----------- 
2           4           4           4
 
SELECT AVG(X) AS M0,
       AVG(COALESCE(X, 0)) AS M2
FROM   T
 
M0          M2          
----------- ----------- 
1           0

A +

10/03/2008, 09h18
SQLpro

Sur le débat, NULL ou pas...

En algèbre relationnel et par définition, le NULL n'existe pas.
En modélisation de données (schéma ER) le NULL existe.

Si vous modélisez mathématiquement, donc à l'aide de relations et à coup de dépendances fonctionnelles (couverture minimale et tout le toutim) alors vous ne devriez pas avoir de NULL... Mais ça c'est la théorie !

En revanche, si vous modélisez via le schéma ER avec la notaion de votre choix
(Merise, IDEF1X, E/R, UML2...) alors là vous utilisez les formes normales, pour lesquelles le NULL est un aspect parmi d'autres...
Cependant, je rappellerais la 6eme forme normale (pour les jusqu'auboutistes) :
Une relation se réduit à un ensemble d'attributs formant la clef et au plus un seul attribut non clef.
Comme cela pas de jaloux : plus besoin de NULLs...

A +
11/03/2008, 12h58
J1
Bonjour Antoun, bonjour SQLpro,

Citation:

Envoyé par SQLpro

Or une somme de NULL ignorés ou de zero donne le même résultat.

Pas toujours en fait.
Prenons la table T1 suivante :
Code:

1 2 3 4 X Y NULL 0 NULL 0
La requête
Code:

1 2 3 4 SELECT SUM(X) AS SumX, SUM(Y) AS SumY FROM T1
renvoie
Code:

1 2 3 SumX SumY NULL 0
Lorsque toutes les valeurs de la colonne sont NULL, la fonction SUM les exclut toutes et n'a donc rien à sommer. Elle indique donc que le résultat est indéfini (NULL), car l'opération n'a pas d'opérande.

Cela a pour conséquence directe que l'égalité

Citation:

Envoyé par SQLpro

Code:

SUM(COALESCE(X, 0) + COALESCE(Y, 0)) = SUM(X) + SUM (Y)

n'est pas toujours vraie. Pour nous en convaincre, considérons à présent la table T2 suivante :
Code:

1 2 3 4 X Y NULL 1 NULL NULL
La requête
Code:

1 2 3 4 SELECT SUM(COALESCE(X,0) + COALESCE(Y,0)) AS S1, SUM(X) + SUM(Y) AS S2a FROM T2
renvoie
Code:

1 2 3 S1 S2a 1 NULL
S1 et S2a ne sont donc pas équivalentes. En effet, si l'on décompose l'expression S1, le SGBD la traite ainsi :

Etape 1
l'opérateur "+" somme COALESCE(X,0) et COALESCE(Y,0) sur la première ligne :
0 + 1 = 1
puis sur la deuxième ligne :
0 + 0 = 0

Etape 2
la fonction SUM somme ensuite chaque sous-total :
SUM(1,0) = 1

S1 renvoie donc 1.

Maintenant, si on décompose l'expression S2a, le SGBD la traite ainsi :

Etape 1
la fonction SUM somme les X non NULL de chaque ligne :
SUM(NULL donc ignoré, NULL donc ignoré) = NULL (car SUM n'a aucun opérande)
puis somme les Y de chaque ligne :
SUM(1, NULL donc ignoré) = 1

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

S2a renvoie donc bien NULL.

****************************************************************

Maintenant, venons en à ta proposition Antoun,

Citation:

Envoyé par Antoun

Code:

SUM(COALESCE(X, 0) + COALESCE(Y, 0)) = COALESCE(SUM(X) + SUM (Y), 0)

Considérons une nouvelle fois la table T2 suivante :
Code:

1 2 3 4 X Y NULL 1 NULL NULL
La requête
Code:

1 2 3 SELECT SUM(COALESCE(X,0) + COALESCE(Y,0)) AS S1, COALESCE(SUM(X) + SUM(Y),0) AS S2b FROM T2
renvoie
Code:

1 2 3 S1 S2b 1 0
S1 et S2b ne sont donc pas non plus équivalentes. Nous avons déjà étudié l'expression S1 précédemment, décomposons donc l'expression S2b :

Etape 1
la fonction SUM somme les X non NULL de chaque ligne :
SUM(NULL donc ignoré, NULL donc ignoré) = NULL (car SUM n'a aucun opérande)
puis somme les Y non NULL chaque ligne :
SUM(1, NULL donc ignoré) = 1

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

Etape 3
la fonction COALESCE convertit NULL en 0 :
COALESCE(NULL,0) = 0

S2b renvoie donc bien 0.

Remarques :
- comme dans mon premier message, 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).
- ci-dessous un script SQL de création des tables T1 et T2 de mes exemples (script compatible SQL Server 2000) :
Code:

1 2 3 4 5 6 7 8 9 10 SELECT NULL AS X, 0 AS Y INTO T1 UNION ALL SELECT NULL AS X, 0 AS Y SELECT NULL AS X, 1 AS Y INTO T2 UNION ALL SELECT NULL AS X, NULL AS Y
11/03/2008, 13h42
Antoun

Citation:

Envoyé par SQLpro

Inutile, car les NULL sont ignorés lors des opérations d'agrégats. Or une somme de NULL ignorés ou de zero donne le même résultat.

Pour dire la même chose que J1 en plus court : une somme de NULL ignorés donne NULL, tandis qu'une somme de zéros donne zéro (ou la tête à Toto ;)).
12/03/2008, 10h46
corrector

Je ne comprends pas pourquoi la somme des éléments d'un ensemble vide ne donne pas 0.

Y a t-il une justification à cette règle?
12/03/2008, 12h35
Antoun

@corrector :
J1 a très bien justifié ça quelques messages plus haut :

Citation:

Envoyé par J1

Or cette dernière requête [càd SUM(NULL)]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
13/03/2008, 01h15
fsmrel

Bonsoir tous,

Citation:

Envoyé par SQLpro

En modélisation de données (schéma ER) le NULL existe.

Avez-vous des références de théoriciens de l’E/R à ce sujet ? Je suis intéressé car en l’occurrence je suis démuni.

Citation:

Envoyé par SQLpro

En revanche, si vous modélisez via le schéma ER avec la notaion de votre choix (Merise, IDEF1X, E/R, UML2...) alors là vous utilisez les formes normales, pour lesquelles le NULL est un aspect parmi d'autres...

De quelles formes normales parlez-vous ? Concernant la méthode Merise, on y trouve au niveau du MCD un concept de DF radicalement différent de celui (datant de 1971) de la théorie relationnelle : en Merise, une DF met en jeu une contrainte d’unicité entre des entités-types en relation par le biais d’une association-type, alors que la théorie relationnelle met en jeu cette contrainte entre des sous-ensembles d’attributs d’une relvar (informellement table) et une seule. De même, la normalisation au sens Merise du terme, concerne seulement les associations-types : un attribut d’une association-type ne doit pas être "vérifié" par rapport à un sous-ensemble strict des entités-types faisant partie de la collection de ces entités-types participant à l’association-type (on a là une sorte d’analogie avec la 2NF de la théorie relationnelle).

Concernant IDEF1X, E/R, UML2, j’aimerais savoir comment ils sont positionnés au plan théorique par rapport à la normalisation.

Mais peut-être parlez-vous de traiter les entités-types, associations-types, classes, comme des relvars, auquel cas il faudra, outre les DF, mettre en évidence les DM et autres DJ, ainsi que l'ensemble des clés candidates (dont on exclut ce qui n’est pas attribut, par exemple les OID). Même sans pousser la normalisation dans les tréfonds de la fermeture et de la couverture irréductible, à coups d’axiomes d’Armstrong, pour des raisons évidentes de temps et de nausée garantie...

Concernant la façon dont NULL est perçu dans le contexte des formes normales, je rappelle que les énoncés relatifs à celles-ci sont muets (je fais évidemment référence aux seuls inventeurs, c'est-à-dire Codd, Heath, Boyce et Fagin).

Pour en revenir à Merise, une approche pragmatique et courante concernant l’absence d’information correspond à celle que l’on retrouve chez Diviné, dans Parlez-vous Merise ?, page 182 : «Les vélos on un moteur "à blanc" au niveau physique» (c'est-à-dire au niveau relationnel (gloups !), cf. page 171...)

Citation:

Envoyé par SQLpro

Cependant, je rappellerais la 6eme forme normale (pour les jusqu'auboutistes) :
Une relation se réduit à un ensemble d'attributs formant la clef et au plus un seul attribut non clef.
Comme cela pas de jaloux : plus besoin de NULLs...

Si SQL impose que les attributs composant la clé primaire d'une table T soient définis comme NOT NULL, il n’en va pas de même pour l’attribut non clé. Et si l’on n’en connaît pas la valeur lors de l’injection de la donnée correspondante dans la base, on pourra donc éprouver le besoin d’avoir recours à NULL...

Pardonnez-moi, mais votre définition de la 6NF est un peu sommaire, car elle omet les dépendances de jointure (en particulier triviales), à la base de cette forme normale : Une relvar est en 6NF si et seulement si elle ne comporte aucune DJ triviale. Je ne me lancerai pas dans un débat sur cette forme normale, mais permettez-moi quand même une digression : Avec son bon sens habituel, Chris Date rappelle que la décomposition des relvars en composants irréductibles n’offre d’intérêt que pour les attributs auxquels on associe une dimension temporelle, au sens période du terme (informellement début et fin, par exemple la date d’entrée d’une personne dans une entreprise et sa date de démission). C’est le contraste opposant ce que l’auteur appelle le SINCE (un fournisseur est sous contrat depuis telle date) et le DURING (ce même fournisseur a été sous contrat pendant telle(s) périodes(s)). Se reporter à son ouvrage Introduction to Database Systems, chapitre 23.

Concrètement, si les attributs d'une relvar ne sont pas concernés par les dates de fin, le respect de la 5NF suffit largement.
13/03/2008, 03h33
fsmrel

Bonsoir,

Citation:

Envoyé par Antoun

...effectivement, cela me rappelle quelques lignes enflammées de Celko sur l'abominable hérésie Chrisdatiste !

Ça fait au moins 10 ans que je n’ai pas lu quelque chose de Celko (c’était du temps de Database Programming & Design). J’en ai gardé le souvenir de quelqu’un doté d’une imagination fertile, bien plus fort que moi en SQL, mais manquant de rigueur et pas très regardant au plan théorique. Pour tout dire j’ai toujours été circonspect à son sujet.

Concernant l’hérésie de Date, je note que celui-ci fut dans l’orthodoxie au moins jusqu’en 1979, puisque ce n’est qu'à partir de ce moment-là que Codd traita de NULL (Cf. Extending the Database Relational Model to Capture More Meaning).

Dans la 4e édition (1986) de "An Introduction to Database Systems", Date n’a manifestement pas d’état d'âme et c’est l’époque du grand amour avec Codd (du temps où ils avaient quitté IBM et montèrent Codd & Date Limited). Dans la 5e édition du même ouvrage(1990), il évolue concernant l’absence d’information (pages 384 et suivantes) :

"...SQL-style nulls are not a solution..."

"...Codd now regards his approach as an integral part of the relational model... we feel that the incorporation of such a feature into the model is premature at this time, and that it would be preferable to wait until the problem is better understood..."
Dans son ouvrage "Relational Database, Writings 1985 - 1989", Date creuse le sujet, notamment dans le chapitre 8 (NOT is Not "Not") et son copain Hugh Darwen l’accompagne et donne des coups de bélier, voir le chapitre 23 (Into the Unknown) que l'on doit à Hugh.

La rupture avec Codd est consommée avec l’article paru dans Database Programming & Design (octobre 1993) "Much Ado about Nothing", dans lequel les deux protagonistes débattent et réfutent à tout va. Appelez hérésie la position de Date : je dirais plutôt qu’il est retourné au Modèle relationnel traditionnel, d’avant 1979, pour le faire évoluer autrement, en collant à la logique d’Aristote et de Frege. Cela n’a rien d’abominable...

L’abomination est à chercher ailleurs...
13/03/2008, 10h48
Antoun

Citation:

Envoyé par fsmrel

Appelez hérésie la position de Date : je dirais plutôt qu’il est retourné au Modèle relationnel traditionnel, d’avant 1979, pour le faire évoluer autrement, en collant à la logique d’Aristote et de Frege. Cela n’a rien d’abominable...

L’abomination est à chercher ailleurs...

Disons que je tentais d'exprimer l'impression que l'on retire des lignes de Celko (qui n'utilise évidemment pas le terme d'hérésie).

Merci beaucoup pour l'historique du schisme !