1. #1
    Expert éminent sénior
    Avatar de fsmrel
    Homme Profil pro
    Spécialiste en bases de données
    Inscrit en
    septembre 2006
    Messages
    5 975
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France

    Informations professionnelles :
    Activité : Spécialiste en bases de données
    Secteur : Conseil

    Informations forums :
    Inscription : septembre 2006
    Messages : 5 975
    Points : 18 867
    Points
    18 867
    Billets dans le blog
    15

    Par défaut Variations sur NULL, ou SUM(X+Y) <> SUM(X) + SUM(Y) ?

    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 ?
    Faites simple, mais pas plus simple ! (A. Einstein)
    E=mc², mais si on discute un peu, on peut l’avoir pour beaucoup moins cher... (G. Lacroix, « Les Euphorismes de Grégoire »)
    => La relativité n'existerait donc que relativement aux relativistes (Jean Eisenstaedt, « Einstein et la relativité générale »)

    De grâce, pas de questions techniques par MP, ma boîte de réception explose !
    __________________________________
    Bases de données relationnelles et normalisation : de la première à la sixième forme normale
    Modéliser les données avec MySQL Workbench

  2. #2
    J1
    J1 est déconnecté
    Membre averti Avatar de J1
    Inscrit en
    mai 2004
    Messages
    300
    Détails du profil
    Informations forums :
    Inscription : mai 2004
    Messages : 300
    Points : 331
    Points
    331

    Par défaut

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

  3. #3
    Expert éminent sénior
    Avatar de fsmrel
    Homme Profil pro
    Spécialiste en bases de données
    Inscrit en
    septembre 2006
    Messages
    5 975
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France

    Informations professionnelles :
    Activité : Spécialiste en bases de données
    Secteur : Conseil

    Informations forums :
    Inscription : septembre 2006
    Messages : 5 975
    Points : 18 867
    Points
    18 867
    Billets dans le blog
    15

    Par défaut

    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 ?
    Faites simple, mais pas plus simple ! (A. Einstein)
    E=mc², mais si on discute un peu, on peut l’avoir pour beaucoup moins cher... (G. Lacroix, « Les Euphorismes de Grégoire »)
    => La relativité n'existerait donc que relativement aux relativistes (Jean Eisenstaedt, « Einstein et la relativité générale »)

    De grâce, pas de questions techniques par MP, ma boîte de réception explose !
    __________________________________
    Bases de données relationnelles et normalisation : de la première à la sixième forme normale
    Modéliser les données avec MySQL Workbench

  4. #4
    J1
    J1 est déconnecté
    Membre averti Avatar de J1
    Inscrit en
    mai 2004
    Messages
    300
    Détails du profil
    Informations forums :
    Inscription : mai 2004
    Messages : 300
    Points : 331
    Points
    331

    Par défaut

    Citation Envoyé par fsmrel Voir le message
    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
    renvoie NULL.
    La requête équivaut donc à
    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 Voir le message
    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.

  5. #5
    Rédacteur/Modérateur

    Avatar de Antoun
    Homme Profil pro
    Architecte décisionnel
    Inscrit en
    octobre 2006
    Messages
    6 142
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 47
    Localisation : France, Paris (Île de France)

    Informations professionnelles :
    Activité : Architecte décisionnel
    Secteur : Conseil

    Informations forums :
    Inscription : octobre 2006
    Messages : 6 142
    Points : 11 390
    Points
    11 390

    Par défaut

    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.
    Antoun
    Expert Essbase, BO, SQL

    La bible d'Essbase est parue !

  6. #6
    Expert éminent sénior
    Avatar de fsmrel
    Homme Profil pro
    Spécialiste en bases de données
    Inscrit en
    septembre 2006
    Messages
    5 975
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France

    Informations professionnelles :
    Activité : Spécialiste en bases de données
    Secteur : Conseil

    Informations forums :
    Inscription : septembre 2006
    Messages : 5 975
    Points : 18 867
    Points
    18 867
    Billets dans le blog
    15

    Par défaut

    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 : 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).
    Faites simple, mais pas plus simple ! (A. Einstein)
    E=mc², mais si on discute un peu, on peut l’avoir pour beaucoup moins cher... (G. Lacroix, « Les Euphorismes de Grégoire »)
    => La relativité n'existerait donc que relativement aux relativistes (Jean Eisenstaedt, « Einstein et la relativité générale »)

    De grâce, pas de questions techniques par MP, ma boîte de réception explose !
    __________________________________
    Bases de données relationnelles et normalisation : de la première à la sixième forme normale
    Modéliser les données avec MySQL Workbench

  7. #7
    Rédacteur/Modérateur

    Avatar de Antoun
    Homme Profil pro
    Architecte décisionnel
    Inscrit en
    octobre 2006
    Messages
    6 142
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 47
    Localisation : France, Paris (Île de France)

    Informations professionnelles :
    Activité : Architecte décisionnel
    Secteur : Conseil

    Informations forums :
    Inscription : octobre 2006
    Messages : 6 142
    Points : 11 390
    Points
    11 390

    Par défaut

    Citation Envoyé par fsmrel Voir le message
    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é !
    Antoun
    Expert Essbase, BO, SQL

    La bible d'Essbase est parue !

  8. #8
    Expert éminent sénior
    Avatar de fsmrel
    Homme Profil pro
    Spécialiste en bases de données
    Inscrit en
    septembre 2006
    Messages
    5 975
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France

    Informations professionnelles :
    Activité : Spécialiste en bases de données
    Secteur : Conseil

    Informations forums :
    Inscription : septembre 2006
    Messages : 5 975
    Points : 18 867
    Points
    18 867
    Billets dans le blog
    15

    Par défaut

    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.
    Faites simple, mais pas plus simple ! (A. Einstein)
    E=mc², mais si on discute un peu, on peut l’avoir pour beaucoup moins cher... (G. Lacroix, « Les Euphorismes de Grégoire »)
    => La relativité n'existerait donc que relativement aux relativistes (Jean Eisenstaedt, « Einstein et la relativité générale »)

    De grâce, pas de questions techniques par MP, ma boîte de réception explose !
    __________________________________
    Bases de données relationnelles et normalisation : de la première à la sixième forme normale
    Modéliser les données avec MySQL Workbench

  9. #9
    Rédacteur/Modérateur

    Avatar de Antoun
    Homme Profil pro
    Architecte décisionnel
    Inscrit en
    octobre 2006
    Messages
    6 142
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 47
    Localisation : France, Paris (Île de France)

    Informations professionnelles :
    Activité : Architecte décisionnel
    Secteur : Conseil

    Informations forums :
    Inscription : octobre 2006
    Messages : 6 142
    Points : 11 390
    Points
    11 390

    Par défaut

    Citation Envoyé par fsmrel Voir le message
    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 .
    Antoun
    Expert Essbase, BO, SQL

    La bible d'Essbase est parue !

  10. #10
    Expert éminent sénior
    Avatar de fsmrel
    Homme Profil pro
    Spécialiste en bases de données
    Inscrit en
    septembre 2006
    Messages
    5 975
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France

    Informations professionnelles :
    Activité : Spécialiste en bases de données
    Secteur : Conseil

    Informations forums :
    Inscription : septembre 2006
    Messages : 5 975
    Points : 18 867
    Points
    18 867
    Billets dans le blog
    15

    Par défaut

    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 : Sélectionner tout - Visualiser dans une fenêtre à part
    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 : Sélectionner tout - Visualiser dans une fenêtre à part
    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 : Sélectionner tout - Visualiser dans une fenêtre à part
    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 : Sélectionner tout - Visualiser dans une fenêtre à part
    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 : 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
     
    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.
    Faites simple, mais pas plus simple ! (A. Einstein)
    E=mc², mais si on discute un peu, on peut l’avoir pour beaucoup moins cher... (G. Lacroix, « Les Euphorismes de Grégoire »)
    => La relativité n'existerait donc que relativement aux relativistes (Jean Eisenstaedt, « Einstein et la relativité générale »)

    De grâce, pas de questions techniques par MP, ma boîte de réception explose !
    __________________________________
    Bases de données relationnelles et normalisation : de la première à la sixième forme normale
    Modéliser les données avec MySQL Workbench

  11. #11
    J1
    J1 est déconnecté
    Membre averti Avatar de J1
    Inscrit en
    mai 2004
    Messages
    300
    Détails du profil
    Informations forums :
    Inscription : mai 2004
    Messages : 300
    Points : 331
    Points
    331

    Par défaut

    Bonjour à toi fsmrel, ainsi qu'à Antoun, qui a rejoint la discussion

    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 : Sélectionner tout - Visualiser dans une fenêtre à part
    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 Voir le message
    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 : Sélectionner tout - Visualiser dans une fenêtre à part
    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 : Sélectionner tout - Visualiser dans une fenêtre à part
    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 : Sélectionner tout - Visualiser dans une fenêtre à part
    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 : Sélectionner tout - Visualiser dans une fenêtre à part
    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.

  12. #12
    Expert éminent sénior
    Avatar de fsmrel
    Homme Profil pro
    Spécialiste en bases de données
    Inscrit en
    septembre 2006
    Messages
    5 975
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France

    Informations professionnelles :
    Activité : Spécialiste en bases de données
    Secteur : Conseil

    Informations forums :
    Inscription : septembre 2006
    Messages : 5 975
    Points : 18 867
    Points
    18 867
    Billets dans le blog
    15

    Par défaut

    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 : Sélectionner tout - Visualiser dans une fenêtre à part
    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...
    Faites simple, mais pas plus simple ! (A. Einstein)
    E=mc², mais si on discute un peu, on peut l’avoir pour beaucoup moins cher... (G. Lacroix, « Les Euphorismes de Grégoire »)
    => La relativité n'existerait donc que relativement aux relativistes (Jean Eisenstaedt, « Einstein et la relativité générale »)

    De grâce, pas de questions techniques par MP, ma boîte de réception explose !
    __________________________________
    Bases de données relationnelles et normalisation : de la première à la sixième forme normale
    Modéliser les données avec MySQL Workbench

  13. #13
    Rédacteur
    Avatar de SQLpro
    Homme Profil pro
    Expert SGBDR & SQL, spécialiste Microsoft SQL Server
    Inscrit en
    mai 2002
    Messages
    17 332
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Var (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : Expert SGBDR & SQL, spécialiste Microsoft SQL Server
    Secteur : Conseil

    Informations forums :
    Inscription : mai 2002
    Messages : 17 332
    Points : 40 139
    Points
    40 139
    Billets dans le blog
    1

    Par défaut

    Salut fmsrel,

    bonne remarque.

    Oui
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    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 : Sélectionner tout - Visualiser dans une fenêtre à part
    SUM(COALESCE(X, 0) + COALESCE(Y, 0)) = SUM(X) + SUM (Y)
    Je devris la rajouter à ma page : http://sqlpro.developpez.com/cours/null/

    A +
    Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
    Le site sur les SGBD relationnels et le langage SQL: http://sqlpro.developpez.com/
    Blog SQL, SQL Server, SGBDR : http://blog.developpez.com/sqlpro
    Expert Microsoft SQL Server - M.V.P. (Most valuable Professional) MS Corp.
    Entreprise SQL SPOT : modélisation, conseils, audit, optimisation, formation...
    * * * * * Enseignant CNAM PACA - ISEN Toulon - CESI Aix en Provence * * * * *

  14. #14
    Rédacteur/Modérateur

    Avatar de Antoun
    Homme Profil pro
    Architecte décisionnel
    Inscrit en
    octobre 2006
    Messages
    6 142
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 47
    Localisation : France, Paris (Île de France)

    Informations professionnelles :
    Activité : Architecte décisionnel
    Secteur : Conseil

    Informations forums :
    Inscription : octobre 2006
    Messages : 6 142
    Points : 11 390
    Points
    11 390

    Par défaut

    Bon, J1 a parfaitement répondu sur le fond, je n'ajouterai donc que de petits à-côtés.
    Citation Envoyé par fsmrel Voir le message
    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 Voir le message
    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 Voir le message
    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 Voir le message
    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 Voir le message
    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 : Sélectionner tout - Visualiser dans une fenêtre à part
    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 Voir le message
    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 Voir le message
    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

    Citation Envoyé par fsmrel Voir le message
    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
    Citation Envoyé par fsmrel Voir le message
    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 Voir le message
    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 : Sélectionner tout - Visualiser dans une fenêtre à part
    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.
    Antoun
    Expert Essbase, BO, SQL

    La bible d'Essbase est parue !

  15. #15
    Rédacteur/Modérateur

    Avatar de Antoun
    Homme Profil pro
    Architecte décisionnel
    Inscrit en
    octobre 2006
    Messages
    6 142
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 47
    Localisation : France, Paris (Île de France)

    Informations professionnelles :
    Activité : Architecte décisionnel
    Secteur : Conseil

    Informations forums :
    Inscription : octobre 2006
    Messages : 6 142
    Points : 11 390
    Points
    11 390

    Par défaut

    Citation Envoyé par SQLpro Voir le message
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    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 : Sélectionner tout - Visualiser dans une fenêtre à part
    SUM(COALESCE(X, 0) + COALESCE(Y, 0)) = COALESCE(SUM(X) + SUM (Y), 0)
    car SUM(X) et SUM(Y) peuvent également être NULL.
    Antoun
    Expert Essbase, BO, SQL

    La bible d'Essbase est parue !

  16. #16
    Expert éminent sénior
    Avatar de fsmrel
    Homme Profil pro
    Spécialiste en bases de données
    Inscrit en
    septembre 2006
    Messages
    5 975
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France

    Informations professionnelles :
    Activité : Spécialiste en bases de données
    Secteur : Conseil

    Informations forums :
    Inscription : septembre 2006
    Messages : 5 975
    Points : 18 867
    Points
    18 867
    Billets dans le blog
    15

    Par défaut

    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...
    Faites simple, mais pas plus simple ! (A. Einstein)
    E=mc², mais si on discute un peu, on peut l’avoir pour beaucoup moins cher... (G. Lacroix, « Les Euphorismes de Grégoire »)
    => La relativité n'existerait donc que relativement aux relativistes (Jean Eisenstaedt, « Einstein et la relativité générale »)

    De grâce, pas de questions techniques par MP, ma boîte de réception explose !
    __________________________________
    Bases de données relationnelles et normalisation : de la première à la sixième forme normale
    Modéliser les données avec MySQL Workbench

  17. #17
    Rédacteur/Modérateur

    Avatar de Antoun
    Homme Profil pro
    Architecte décisionnel
    Inscrit en
    octobre 2006
    Messages
    6 142
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 47
    Localisation : France, Paris (Île de France)

    Informations professionnelles :
    Activité : Architecte décisionnel
    Secteur : Conseil

    Informations forums :
    Inscription : octobre 2006
    Messages : 6 142
    Points : 11 390
    Points
    11 390

    Par défaut

    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 :
    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 ).
    Antoun
    Expert Essbase, BO, SQL

    La bible d'Essbase est parue !

  18. #18
    Rédacteur
    Avatar de SQLpro
    Homme Profil pro
    Expert SGBDR & SQL, spécialiste Microsoft SQL Server
    Inscrit en
    mai 2002
    Messages
    17 332
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Var (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : Expert SGBDR & SQL, spécialiste Microsoft SQL Server
    Secteur : Conseil

    Informations forums :
    Inscription : mai 2002
    Messages : 17 332
    Points : 40 139
    Points
    40 139
    Billets dans le blog
    1

    Par défaut

    De Antoun :
    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 : 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
    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 +
    Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
    Le site sur les SGBD relationnels et le langage SQL: http://sqlpro.developpez.com/
    Blog SQL, SQL Server, SGBDR : http://blog.developpez.com/sqlpro
    Expert Microsoft SQL Server - M.V.P. (Most valuable Professional) MS Corp.
    Entreprise SQL SPOT : modélisation, conseils, audit, optimisation, formation...
    * * * * * Enseignant CNAM PACA - ISEN Toulon - CESI Aix en Provence * * * * *

  19. #19
    Rédacteur
    Avatar de SQLpro
    Homme Profil pro
    Expert SGBDR & SQL, spécialiste Microsoft SQL Server
    Inscrit en
    mai 2002
    Messages
    17 332
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Var (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : Expert SGBDR & SQL, spécialiste Microsoft SQL Server
    Secteur : Conseil

    Informations forums :
    Inscription : mai 2002
    Messages : 17 332
    Points : 40 139
    Points
    40 139
    Billets dans le blog
    1

    Par défaut

    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 +
    Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
    Le site sur les SGBD relationnels et le langage SQL: http://sqlpro.developpez.com/
    Blog SQL, SQL Server, SGBDR : http://blog.developpez.com/sqlpro
    Expert Microsoft SQL Server - M.V.P. (Most valuable Professional) MS Corp.
    Entreprise SQL SPOT : modélisation, conseils, audit, optimisation, formation...
    * * * * * Enseignant CNAM PACA - ISEN Toulon - CESI Aix en Provence * * * * *

  20. #20
    J1
    J1 est déconnecté
    Membre averti Avatar de J1
    Inscrit en
    mai 2004
    Messages
    300
    Détails du profil
    Informations forums :
    Inscription : mai 2004
    Messages : 300
    Points : 331
    Points
    331

    Par défaut

    Bonjour Antoun, bonjour SQLpro,

    Citation Envoyé par SQLpro Voir le message
    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 :
    La requête
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
     
    SELECT SUM(X)  AS SumX,
           SUM(Y)  AS SumY
    FROM   T1
    renvoie
    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 Voir le message
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    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 : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    X	Y
    NULL	1
    NULL	NULL
    La requête
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
     
    SELECT SUM(COALESCE(X,0) + COALESCE(Y,0))    AS S1,
           SUM(X) + SUM(Y)                       AS S2a
    FROM   T2
    renvoie
    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 Voir le message
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    SUM(COALESCE(X, 0) + COALESCE(Y, 0)) = COALESCE(SUM(X) + SUM (Y), 0)
    Considérons une nouvelle fois la table T2 suivante :

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    X	Y
    NULL	1
    NULL	NULL
    La requête
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    SELECT SUM(COALESCE(X,0) + COALESCE(Y,0))    AS S1,
           COALESCE(SUM(X) + SUM(Y),0)           AS S2b
    FROM   T2
    renvoie
    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 : Sélectionner tout - Visualiser dans une fenêtre à part
    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

Discussions similaires

  1. [gridview]probleme de tri sur null
    Par anthyme dans le forum Windows Presentation Foundation
    Réponses: 2
    Dernier message: 05/08/2008, 17h29
  2. limiter un "-" dans un textbox et variations sur le thème ^^^
    Par petibonohm dans le forum Macros et VBA Excel
    Réponses: 75
    Dernier message: 29/02/2008, 16h17
  3. GROUP BY sur "NULL"
    Par alfredmobile dans le forum Langage SQL
    Réponses: 2
    Dernier message: 10/02/2008, 06h35
  4. Réponses: 11
    Dernier message: 03/05/2006, 16h12
  5. Pointeur sur NULL par défaut en parametre.
    Par KernelControl dans le forum Débuter
    Réponses: 3
    Dernier message: 15/12/2005, 11h09

Partager

Partager
  • Envoyer la discussion sur Viadeo
  • Envoyer la discussion sur Twitter
  • Envoyer la discussion sur Google
  • Envoyer la discussion sur Facebook
  • Envoyer la discussion sur Digg
  • Envoyer la discussion sur Delicious
  • Envoyer la discussion sur MySpace
  • Envoyer la discussion sur Yahoo