CoqBlog

SQL : Fonctions d'agrégation MIN/MAX et valeurs NULL

Les fonctions d'agrégation comme MIN et MAX ignorent les valeurs NULL présentes dans le jeu de données sur lequel porte leur calcul, d'où le fameux message d'avertissement :

Warning: Null value is eliminated by an aggregate or other SET operation.

Avertissement : la valeur NULL est éliminée par un agrégat ou par une autre opération SET.

Personnellement, j'aurais préféré que ces fonctions renvoient NULL si une des valeurs qui leur sont passées valait NULL.

L'exemple de problème et une proposition de solution

Ce post sur lequel je suis tombé ce weekend, décrit un cas dans lequel ce comportement pose problème.
L'auteur y contourne le problème en utilisant COALESCE dans l'expression passée à MAX, afin de remplacer la valeur NULL par une valeur connue et n'étant pas censée faire partie des valeurs présentes dans la table cible. Ce qui lui permet ensuite de tester le retour de MAX, et de replacer la valeur NULL si ce retour vaut notre fameuse valeur de remplacement :

...
CASE WHEN MAX(COALESCE(EndDate, '12/31/2099')) = '12/31/2099' THEN NULL ELSE MAX(EndDate) END AS Date
...
Source : Select NULL As Max or Min in SQL Query

Comme le dit l'auteur : ce n'est pas forcément très élégant mais ça marche.
J'ajouterais que ce n'est pas extrèmement intrusif donc ça reste assez compréhensible pour la personne amenée à lire le code par la suite.

Le problème avec ce type de solution de contournement

Ce qui me dérange avec cette technique de remplacement c'est que justement nous sommes tributaires du fait que jamais personne n'utilisera notre valeur de remplacement pour autre chose, et qu'elle ne sera donc jamais présente en tant que valeur réelle de la colonne.
L'exemple utilise une date relativement proche, le 31/12/2099, mais il aurait très bien pu utiliser le 31/12/9999 (ou le 01/01/1753 pour le cas du MIN).

De mon point de vue ça n'aurait pas changé le problème de fond : pour notre valeur de remplacement, nous choisissons bien sûr une valeur qui n'a aucune chance d'être valide du point de vue métier des applications qui produisent et exploitent les données : dommage que les personnes en charge de la maintenance et des évolutions de ces applications risquent de se faire la même réflexion si ils viennent eux aussi à avoir besoin d'une solution rapide (ne remettant pas en cause le modèle de stockage etc).

Exemple de scénario (oui, assez improbable : mais qui n'a jamais rencontré un bug causé par une situation improbable ?) :

1. Nous choisissons d'utiliser la valeur 31/12/9999 comme valeur de remplacement dans notre requête de reporting.
2. Six mois plus tard, les personnes chargées de la maintenance du système produisant et traitant les données prennent la décision d'utiliser la valeur 31/12/9999 pour marquer un état "supprimé", qui sera de toute façon retraité par l'application à la lecture des données : pas le temps de revoir le modèle de stockage et le code reposant dessus pour mettre en place cet état.
3. Un mois plus tard, quelqu'un s'aperçoit que le reporting affiche des éléments "supprimés" comme étant "en cours" : il ne nous reste plus qu'à espérer que ce rapport ne servait pas à des tâches vitales pour l'entreprise.

Pour ma part je préfère donc en général chercher une autre solution, quitte à ce que les coûts d'exécution soient tels qu'ils me poussent à changer le modèle de données.

Solutions envisageables dans le cas de l'exemple, sans utiliser de valeurs de remplacement (et sans utiliser MIN/MAX)

Ces solutions sont données à titre d'exemple et ne doivent pas être prises tel quel sans réflexion poussée.
Comme d'habitude il faut mesurer l'impact sur des volumes réels et prendre une décision suivant les constatations effectuées : la solution présentant des risques sera en fin de compte peut être envisageable, mais au moins nous avons une alternative (qui peut aider à plaider en la faveur d'une éventuelle refonte nécessaire du modèle de stockage, vu la tête des requêtes...)

Pour l'extraction des valeurs MIN

Il est possible d'éviter le problème en utilisant le comportement de la clause ORDER BY vis-à-vis des valeurs NULL :

Null values are treated as the lowest possible values.
Source : ORDER BY Clause (Transact-SQL)

Nous pouvons utiliser une CTE et ROW_NUMBER pour parvenir à nos fins :

;WITH IntermediateDataCTE AS
(
    SELECT
        StoreID,
        ROW_NUMBER() OVER (PARTITION BY StoreID
                           ORDER BY EndDate ASC) AS DateRank,
        EndDate AS Date
    FROM WorkSchedule
)
SELECT
    StoreID,
    Date
FROM IntermediateDataCTE
WHERE DateRank = 1

Si la valeur NULL est présente nous obtenons NULL, sinon nous obtenons la date la plus petite pour la partition donnée (StoreID, qui était la valeur utilisée dans la clause GROUP BY).

Pour l'extraction des valeurs MAX

Nous ne pouvons pas simplement nous contenter de changer l'ordre de tri.
Nous allons devoir complexifier un peu la partie ORDER BY de ROW_NUMBER, en lui ajoutant un premier critère de tri sur l'état NULL/NOT NULL de la date... au moyen d'une instruction CASE WHEN...

ORDER BY CASE WHEN EndDate IS NULL THEN 1 ELSE 0 END DESC, EndDate DESC

Si une valeur NULL est présente, elle sera en première position du fait du tri DESC sur 1, alors que dans le cas contraire c'est le tri DESC sur la date qui placera la plus grande en première position.
C'est moche et ça nécessitera un bon bloc de commentaires expliquant l'intention de la requête, mais c'est toujours mieux qu'un risque de bug (en partant du principe que les performances sont acceptables pour le jeu de données cible).

Ce qui donne

;WITH IntermediateDataCTE AS
(
    SELECT
        StoreID,
        ROW_NUMBER() OVER (PARTITION BY StoreID
                           ORDER BY CASE WHEN EndDate IS NULL THEN 1 ELSE 0 END DESC,
                                    EndDate DESC) AS DateRank,
        EndDate AS Date
    FROM WorkSchedule
)
SELECT
    StoreID,
    Date
FROM IntermediateDataCTE
WHERE DateRank = 1

Si la valeur NULL est présente nous obtenons NULL, sinon nous obtenons la date la plus grande pour la partition donnée (StoreID, qui était la valeur utilisée dans la clause GROUP BY).

A noter que l'expression "CASE WHEN EndDate IS NULL THEN 1 ELSE 0 END" peut éventuellement être définie en tant que formule d'une colonne calculée (persistée au besoin), ce qui lui permettrait de prendre part à un index. Comme ce n'est pas forcément une pratique bienvenue dans tous les cas et environnements, renseignez vous bien avant de l'envisager.