Single-Value Rule

FondamentalPrincipe de la "Single-Value Rule" établie par le standard SQL

Toute colonne de la clause GROUP BY doit désigner une colonne présente dans la clause SELECT :

  • soit comme attribut d'agrégation,

  • soit comme attribut présent dans une fonction d'agrégation.

AttentionRequête illégale

num. | Ligne | texte | copie |
CTRL+C pour copier, CTRL+V pour coller
1
SELECT countrycode, citycode, COUNT(citycode)
2
FROM city
3
GROUP BY countrycode;
SELECT countrycode, citycode, COUNT(citycode)
FROM city
GROUP BY countrycode;
Ligne | num. | copie |
CTRL+C pour copier, CTRL+V pour coller
1
ERROR:  column "city.citycode" must appear in the GROUP BY clause or be used in an aggregate function;
ERROR:  column "city.citycode" must appear in the GROUP BY clause or be used in an aggregate function;
Tentative de GROUP BY illégal

La requête est non standard et non logique car on cherche à mettre plusieurs données dans la case citycode après le GROUP BY (il y a plusieurs citycode par countrycode).

Elle sera refusée par tous les SGBD.

ComplémentSingle-Value Rule

https://mariadb.com/kb/en/sql-99/the-single-value-rule

Tolérance (de certains SGBD)

Requête non standard tolérée par certains SGBD

num. | Ligne | texte | copie |
CTRL+C pour copier, CTRL+V pour coller
1
SELECT citycode, countrycode, AVG(population)
2
FROM city
3
GROUP BY citycode;
SELECT citycode, countrycode, AVG(population)  
FROM city
GROUP BY citycode;
Ligne | num. | copie |
CTRL+C pour copier, CTRL+V pour coller
1
 citycode | countrycode |          avg
2
----------+-------------+------------------------
3
 LLL      | FR          | 0.20000000000000000000
4
 BAR      | ES          | 1.90000000000000000000
5
 PAR      | FR          |     2.2000000000000000
6
 LYO      | FR          | 0.50000000000000000000
7
 ZAR      | ES          | 0.70000000000000000000
8
 AMN      | FR          | 0.10000000000000000000
9
 MAD      | ES          |     3.3000000000000000
 citycode | countrycode |          avg           
----------+-------------+------------------------
 LLL      | FR          | 0.20000000000000000000
 BAR      | ES          | 1.90000000000000000000
 PAR      | FR          |     2.2000000000000000
 LYO      | FR          | 0.50000000000000000000
 ZAR      | ES          | 0.70000000000000000000
 AMN      | FR          | 0.10000000000000000000
 MAD      | ES          |     3.3000000000000000

La requête est non standard, même si c'est logiquement acceptable, car il n'y a qu'un countrycode par citycode ici, city étant une clé.

Certains SGBD comme Postgres accepte donc cette syntaxe, en ajoutant implicitement l'attribut qui manque au GROUP BY, car il n'y a pas d’ambiguïté si l'on analyse le graphe des DF : citycode → countrycode. Mais le standard impose d'être explicite, car le SGBD a le droit de ne pas le deviner.

Tolérance partielle

Requête légale

num. | Ligne | texte | copie |
CTRL+C pour copier, CTRL+V pour coller
1
SELECT ci.countrycode, co.name, count(*)
2
FROM city ci JOIN country co
3
ON ci.countrycode=co.countrycode
4
GROUP BY ci.countrycode, co.name;
SELECT ci.countrycode, co.name, count(*)  
FROM city ci JOIN country co 
ON ci.countrycode=co.countrycode 
GROUP BY ci.countrycode, co.name;
Ligne | num. | copie |
CTRL+C pour copier, CTRL+V pour coller
1
 countrycode |  name  | count
2
-------------+--------+-------
3
 FR          | France |     4
4
 ES          | Spain  |     3
 countrycode |  name  | count 
-------------+--------+-------
 FR          | France |     4
 ES          | Spain  |     3

Requête non standard tolérée par certains SGBD

num. | Ligne | texte | copie |
CTRL+C pour copier, CTRL+V pour coller
1
SELECT co.countrycode, co.name, count(*)
2
FROM city ci JOIN country co
3
ON ci.countrycode=co.countrycode
4
GROUP BY co.countrycode;
SELECT co.countrycode, co.name, count(*)  
FROM city ci JOIN country co 
ON ci.countrycode=co.countrycode 
GROUP BY co.countrycode;

La requête est non standard, car co.name est dans le SELECT mais pas dans le GROUP BY. Comme dans l'exemple précédent certains SGBD accepteront cette requête grâce à la DF évidente : co.countrycode → co.name

AttentionRequête non standard non tolérée

Mais si l'on utilise à présent ci.countrycode à la place de co.countrycode...

num. | Ligne | texte | copie |
CTRL+C pour copier, CTRL+V pour coller
1
SELECT ci.countrycode, co.name, count(*)
2
FROM city ci JOIN country co
3
ON ci.countrycode=co.countrycode
4
GROUP BY ci.countrycode;
SELECT ci.countrycode, co.name, count(*)  
FROM city ci JOIN country co 
ON ci.countrycode=co.countrycode 
GROUP BY ci.countrycode;
Ligne | num. | copie |
CTRL+C pour copier, CTRL+V pour coller
1
ERROR:  column "co.name" must appear in the GROUP BY clause or be used in an aggregate function
ERROR:  column "co.name" must appear in the GROUP BY clause or be used in an aggregate function

La requête est non standard, co.name doit être mentionné dans la clause GROUP BY. Logiquement on pourrait juger cela superflu, car par transitivité ci.countrycode → co.name, mais le SGBD (ici Postgres) ne va pas jusque là.

Conseil

Il est donc conseillé d'appliquer le Single-Value Rule et de toujours expliciter tous les attributs dans le GROUP BY, pour éviter la dépendance au SGBD ou bien les erreurs liées à des variations mineures de syntaxe.