Pourquoi pas de variable dans SELECT ?

**alassanediakite** · 15/06/2017, 18h05

Salut
Juste une question...
Pourquoi le SELECT ne peut pas prendre de variable?

Une idée de la forme...

Code :

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

1
2
3
4
5
SELECT colx, coly, varx, vary,...
VAR varx=expression, vary=expression2
FROM...
WHERE <prédicat sur colonnes>
HAVING <prédicat sur variable>

dans laquelle VAR serait une clause optionnelle de l'instruction SELECT.

Le code que je donne est assez simpliste, mais pensez aux requêtes complexes dans lesquelles on a souvent des répétitions d'expression qu'on voudrais factoriser.
Au passage, MySQL accepte un alias dans le having du genre...

Code :

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

SELECT left(prenom,2) AS initial FROM personne HAVING initial='AL'

Alors pourquoi pas?

@+

**al1_24** · 15/06/2017, 19h11

A ma connaissance, cette notation n'est pas prévue dans le standard SQL.
J'ai rencontré ce concept dans Teradata (<expresssion> (named <alias>)) qui précise bien que c'est une spécificité.
Après s'il s'agit juste de factoriser, tu as aussi beaucoup de capacités avec les tables dérivées et les expressions de table.

**alassanediakite** · 15/06/2017, 22h14

Salut
Merci de la contribution.
Personnellement, je remplacerai ...

Envoyé par al1_24

A ma connaissance, cette notation n'est pas prévue dans le standard SQL.

par...

Ce concept n'est pas encore dans le standard SQL.

Qui sait, peut-être que la pertinence se présentera un jour.

Envoyé par al1_24

J'ai rencontré ce concept dans Teradata (<expresssion> (named <alias>)) qui précise bien que c'est une spécificité.

Ce sont les spécificités qui enrichissent le standard.

Envoyé par al1_24

Après s'il s'agit juste de factoriser, tu as aussi beaucoup de capacités avec les tables dérivées et les expressions de table.

Les tables dérivées (ou sous-requêtes) et les expressions de tables induisent forcement plus d'une boucle de lecture. Alors que le concept de variable est utilisé à chaque ligne.
@+

**al1_24** · 16/06/2017, 09h05

Avec un bon optimiseur, il n'est pas obligatoire que l'usage d''une table dérivée ou une expression de table se traduise en boucle.

**aieeeuuuuu** · 16/06/2017, 14h10

Bonjour,

Par ailleurs, HAVING engendre un GROUP BY, (ce qui peut être couteux, donc pas forcément idéal non plus pour les performances lorsqu'il n'est pas nécessaire) mais surtout change la sémantique de la requete.

D'ailleurs, votre requête ne sera pas valide si l'une des variables fait référence a l'une des colonnes de la table sans fonction d'agregation, justement en raison de l'absence de clause GROUP BY explicite.

Enfin, pour ce qui est de MySQL qui accepte cette syntaxe, quand on voit comment il gère la dite clause GROUP BY, je ne pense pas que ce soit une référence à prendre en compte pour ce débat...

**alassanediakite** · 16/06/2017, 20h42

Salut

Envoyé par al1_24

Avec un bon optimiseur, il n'est pas obligatoire que l'usage d''une table dérivée ou une expression de table se traduise en boucle.

Veux-tu dire qu'une requête de la forme...

Code :

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

1
2
3
SELECT ...
FROM (SELECT ... FROM <liste_from_interne>) AS r
WHERE <prédicat>

peut être transformé par un optimiseur sous la forme...

Code :

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

SELECT ... FROM <liste_from_interne> WHERE <prédicat>

Envoyé par aieeeuuuuu

Bonjour,

Par ailleurs, HAVING engendre un GROUP BY, (ce qui peut être couteux, donc pas forcément idéal non plus pour les performances lorsqu'il n'est pas nécessaire) mais surtout change la sémantique de la requete.

D'ailleurs, votre requête ne sera pas valide si l'une des variables fait référence a l'une des colonnes de la table sans fonction d'agregation, justement en raison de l'absence de clause GROUP BY explicite.

Enfin, pour ce qui est de MySQL qui accepte cette syntaxe, quand on voit comment il gère la dite clause GROUP BY, je ne pense pas que ce soit une référence à prendre en compte pour ce débat...

admettons cela dans la définition actuelle des choses.
D'où le fait que j'ai separé le WHERE et le HAVING.
L'idée est de ...

laisser intact le fonctionnement de WHERE i.e au niveau du moteur de stockage (accès aux blocs binaires de table et d'index)
exécuter le HAVING au niveau du moteur de requête (l'espace mémoire de la requête) sans qu'il ne soit forcement lié à un GROUP BY

Merci de vos contributions
@+

**SQLpro** · 18/06/2017, 21h54

Envoyé par alassanediakite

Salut
Juste une question...
Pourquoi le SELECT ne peut pas prendre de variable?:

Parce que c'est en pure opposition à la logique ensembliste. Il ne faudrait pas une variables scalaire, mais tout simplement un ensemble de données, donc une table; C'est pourquoi le SQL a statuer en proposant les tables temporaires.
Pour info Chris Date a bien indiqué qu'il y avait aussi confusion entre le contenant et le contenu. Car en algèbre relationnelle le même terme désigne les deux.
Dans les langages itératifs, il y a la notion de variable qui est le conteneur et la notion de valeur qui est le contenu.
Chris Date à alors proposer qu'il y ait deux termes : la relation (le contenu) et la "relvar" (variable relationnelle, donc le conteneur).

A +

**alassanediakite** · 19/06/2017, 00h45

Salut
SQLpro, la santé ça va?
Que "SQL travaille sur des ensembles", je suis d'accord.
Que "SQL soit un langage déclaratif", je suis d'accord.
Mais que "SQL soit opposé à la réutilisation d'expression déjà calculée" je trouve que c'est mettre un frein au SGBDR.
Merci pour la contribution et 1000% de santé pour toi.
@+

**aieeeuuuuu** · 20/06/2017, 14h53

Envoyé par alassanediakite

exécuter le HAVING au niveau du moteur de requête (l'espace mémoire de la requête) sans qu'il ne soit forcement lié à un GROUP BY

Justement, le HAVING est forcément lié à un GROUP BY, qu'il soit explicite, ou implicite.

**Mat.M** · 20/06/2017, 15h43

Envoyé par alassanediakite

Salut
Juste une question...
Pourquoi le SELECT ne peut pas prendre de variable?

parce que c'est tout simplement l'intérêt d'utiliser des langages de procédures stockées comme le PL-SQL (Oracle) ou le Transact SQL (MS SQL-SERVER).
Faire des procédures stockées ça permet d'accéler des traitements sur des bases de données de manière puissance n...

**alassanediakite** · 20/06/2017, 20h03

Salut

Envoyé par aieeeuuuuu

Justement, le HAVING est forcément lié à un GROUP BY, qu'il soit explicite, ou implicite.

J'ai pas dit le contraire. Je propose une évolution du SELECT (qui je pense l'améliore en temps): permettre au SELECT de réutiliser une expression déjà calculée. Une amélioration qui modifiera le comportement de HAVING.

Envoyé par Mat.M

parce que c'est tout simplement l'intérêt d'utiliser des langages de procédures stockées comme le PL-SQL (Oracle) ou le Transact SQL (MS SQL-SERVER).
Faire des procédures stockées ça permet d'accéler des traitements sur des bases de données de manière puissance n...

Je ne parle ni de boucle, ni de curseur, ni de gestion d'erreur, ni ...
Juste permettre au SELECT de réutiliser une expression déjà calculée puisque c'est cela dont il s'agit!!!.
Si vous voulez on peut poser la question autrement: Pourquoi ne pas permettre au SELECT de réutiliser une expression déjà calculée.
Merci pour vos contributions.
@+

**Mat.M** · 20/06/2017, 22h32

Envoyé par alassanediakite

Pourquoi ne pas permettre au SELECT de réutiliser une expression déjà calculée

un SELECT ne permet que de retourner les données de colonnes et non de calculer des expressions.
Sauf de calculer des expressions mathématiques et retourner le résultat par exemple on peut faire SELECT 1+1 FROM table..
Ou bien alors je n'ai pas du tout compris ta question il faudrait être plus explicite...

Envoyé par alassanediakite

Pourquoi le SELECT ne peut pas prendre de variable?
Une idée de la forme...

pour que le moteur d'interprétation des commandes SQL soit en mesure de faire cela il faudrait qu'il crée une pile de variables dynamique.
Exactement comme lorsqu'on appelle une fonction dans un langage informatique procédural on passe des paramètres à la fonction
Maintenant pourquoi les moteurs d'interprétation SQL ne peuvent pas faire ça je ne sais pas sans doute pour raison de rétro-compatibilité ;
encore une fois il faut utiliser les langages de procédures stockées

Envoyé par alassanediakite

dans laquelle VAR serait une clause optionnelle de l'instruction SELECT.

clause optionnelle ? Il faudrait être plus précis

**alassanediakite** · 20/06/2017, 22h53

Salut

Envoyé par Mat.M

...encore une fois il faut utiliser les langages de procédures stockées

Ce n'est pas un problème auquel je suis confronté et que cherche à résoudre.
C'est un thème que je propose à débattre: faisabilité et pertinence

Envoyé par Mat.M

...Maintenant pourquoi les moteurs d'interprétation SQL ne peuvent pas faire ça je ne sais pas sans doute pour raison de rétro-compatibilité ;

Là tu es dans le débat et il faut (stp) clarifier ton propos.
@+

**Mat.M** · 20/06/2017, 22h58

Envoyé par alassanediakite

Là tu es dans le débat et il faut (stp) clarifier ton propos.
@+

??

ce n'est pas faire un débat je ne peux pas faire de débat parce que j'ai précisément écris que je ne savais pas et al1_24 a parlé de compatibilité ; et si tu veux que l'on réponde avec pertinence à tes questions je t'ai posé des questions précisément
Auxquelles tu n'as pas répondu

**alassanediakite** · 21/06/2017, 13h09

Salut
A force de chercher à vous convaincre de la pertinence, j'ai découvert une solution magique avec la jointure latérale (JOIN LATERAL).
L'exemple est fait sur la résolution d'équation du second degré.
Solution avec PostgreSQL
La table contenant les coefficients...

Code :

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

create table seconddegre(id serial primary key, a int, b int, c int);

Insertion de trois lignes...

Code :

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

insert into seconddegre(a, b, c) values(1,1,-1),(1,0,-1),(1,-1,-2),(1,1,1);

La requête standard...

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
select b*b-4*a*c as delta, 
	(case 
	when b*b-4*a*c>=0 then
	(-b-sqrt(b*b-4*a*c))/2*a
	else null
	end)  as x1, 
	(case 
	when b*b-4*a*c>=0 then
	(-b+sqrt(b*b-4*a*c))/2*a
	else null
	end)  as x2
from seconddegre

Le résultat...

delta	x1	x2
5	-1.61803398874989	0.618033988749895
4	-1	1
9	-1	2
-3

La requête avec JOIN LATERAL...

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
select delta, 
	(case 
	when delta>=0 then
	(-b-rdelta)/2*a
	else null
	end)  as x1, 
	(case 
	when delta>=0 then
	(-b+rdelta)/2*a
	else null
	end)  as x2
from seconddegre as sd
join lateral (select sd.b*sd.b-4*sd.a*sd.c as delta, sqrt(abs(sd.b*sd.b-4*sd.a*sd.c)) as rdelta) as d on true

Le résultat...

delta	x1	x2
5	-1.61803398874989	0.618033988749895
4	-1	1
9	-1	2
-3

Bonus..., on peut même filtrer avec WHERE...
Les équations qui on une solution...

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
select delta, 
	(case 
	when delta>=0 then
	(-b-rdelta)/2*a
	else null
	end)  as x1, 
	(case 
	when delta>=0 then
	(-b+rdelta)/2*a
	else null
	end)  as x2
from seconddegre as sd
join lateral (select sd.b*sd.b-4*sd.a*sd.c as delta, sqrt(abs(sd.b*sd.b-4*sd.a*sd.c)) as rdelta) as d on true
where delta>=0

Il reste à chercher pour les agrégations.
@+ et grand merci de vos contributions

**aieeeuuuuu** · 21/06/2017, 14h06

il n'y a pas plus de variable dans cette requete.

Si le débat porte uniquement sur la possibilité de réutiliser dans le WHERE des calculs (complexes a écrire, sinon aucune intérêt) définis dans le SELECT alors c'est différent.

Et dans ce cas, on peut simplement utiliser des sous requêtes ou CTE, ce qui résout par la même occasion le problème des agrégats, ainsi que d'autres non évoqués comme l'utilisation de fonctions fenêtrées, qui ne sont pas non plus admises dans la clause WHERE.

**alassanediakite** · 21/06/2017, 15h41

Salut
aieeeuuuuu, chapeau

Avec PostgreSQL, j'ai comparé les plans d’exécutions des différentes formes. Il en résulte que le JOIN LATERAL et la sous-requête donne des plans identiques (même avec jointure!).
JOIN LATERAL

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
select delta, 
	(case 
	when delta>=0 then
	(-b-rdelta)/2*a
	else null
	end)  as x1, 
	(case 
	when delta>=0 then
	(-b+rdelta)/2*a
	else null
	end)  as x2
from seconddegre as sd 
join lateral (select sd.b*sd.b-4*sd.a*sd.c as delta, sqrt(abs(sd.b*sd.b-4*sd.a*sd.c)) as rdelta) as d on true

Nom : v1.png
Affichages : 487
Taille : 1,8 Ko

SOUS REQUÊTE

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
select delta, 
	(case 
	when delta>=0 then
	(-b-rdelta)/2*a
	else null
	end)  as x1, 
	(case 
	when delta>=0 then
	(-b+rdelta)/2*a
	else null
	end)  as x2
from (select a, b, c, b*b-4*a*c as delta, sqrt(abs(b*b-4*a*c)) as rdelta from seconddegre) as r

CTE

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
with r as(select a, b, c, b*b-4*a*c as delta, sqrt(abs(b*b-4*a*c)) as rdelta from seconddegre)
select delta, 
	(case 
	when delta>=0 then
	(-b-rdelta)/2*a
	else null
	end)  as x1, 
	(case 
	when delta>=0 then
	(-b+rdelta)/2*a
	else null
	end)  as x2
from r

Nom : v2.png
Affichages : 358
Taille : 3,8 Ko

En fait, j'ai toujours pensé que la sous-requête est entièrement exécutée (comme cte dans ce cas) avant la requête principale.

@+ et encore merci pour vos contribution

**SQLpro** · 21/06/2017, 16h38

Plusieurs choses à dire sur votre exemple :

1) SERIAL n'existe pas c'est spécifique à PostGreSQL. les mécanismes normalisés de génération des auto-incréments sont IDENTITY (interne à la table, donc proche du SERIAL) et SEQUENCE.

2) l'opérateur d'intra-jointure LATERAL a été introduit par la norme SQL:1999 mais a été violemment critiqué car donnant des résultats potentiellement faux. Et d'ailleurs votre exemple donne un résultat faux car la ligne contenant des NULLs n'a pas de sens dans ce cas. De ce fait Microsoft à proposé de remplacer cet opérateur par l'opérateur APPLY décliné en deux versions : CROSS (produit cartésien) et OUTER (qui rajoute une ligne en cas de NULL dans les paramètres d'application). Oracle à d'ailleurs repris l'opérateur APPLY et quasiment abandonné l'opérateur LATERAL. APPLY a été proposé au comité de normalisation en remplacement de LATERAL, mais le mal est fait

Si vous voulez utiliser un SGBDR qui respecte mieux la norme SQL (PostGreSQL s'en éloigne de plus en plus alors qu'ils y avaient été très proche) prenez SQL Server (vous avez même une version linux).
https://www.microsoft.com/en-us/sql-...2017#resources

En l'occurrence, voici ce qui se passe avec MS SQL Server :

Code :

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

1
2
create table seconddegre(id int identity primary key, a int, b int, c int);
insert into seconddegre(a, b, c) values(1,1,-1),(1,0,-1),(1,-1,-2),(1,1,1);

Code :

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

1
2
3
4
5
6
select delta, 
	case When delta>=0 then (-b-rdelta)/2*a else null end  as x1, 
	case when delta>=0 then (-b+rdelta)/2*a else null end  as x2
from seconddegre as sd
CROSS APPLY (select sd.b*sd.b-4*sd.a*sd.c as delta, sqrt(abs(sd.b*sd.b-4*sd.a*sd.c)) as rdelta) as d 
where delta>=0;

Et le résultat :

Code :

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

1
2
3
4
5
delta       x1                     x2
----------- ---------------------- ----------------------
5           -1,61803398874989      0,618033988749895
4           -1                     1
9           -1                     2

ne montre pas la ligne avec les NULLs qui n'a aucun sens !

Alors que la solution en CTE :

Code :

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

1
2
3
4
5
with r as(select a, b, c, b*b-4*a*c as delta, sqrt(abs(b*b-4*a*c)) as rdelta from seconddegre)
select delta, 
	case When delta>=0 then (-b-rdelta)/2*a else null end  as x1, 
	case when delta>=0 then (-b+rdelta)/2*a else null end  as x2
from r;

Code :

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

1
2
3
4
5
6
delta       x1                     x2
----------- ---------------------- ----------------------
5           -1,61803398874989      0,618033988749895
4           -1                     1
9           -1                     2
-3          NULL                   NULL

Sur les plans de requête... Il n'y a évidemment pas assez de données pour obtenir une différence sensible dans les plans. En effet en cas de faible cardinalité, l'opérateur de jointure à privilégier sera la boucle imbriquée. Néanmoins, SQL Server montre que la CTE est légèrement plus couteuses et c'est normal car légèrement plus complexe.

A +

**alassanediakite** · 21/06/2017, 17h08

Salut

Envoyé par SQLpro

Code :

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

1
2
3
4
5
6
select delta, 
	case When delta>=0 then (-b-rdelta)/2*a else null end  as x1, 
	case when delta>=0 then (-b+rdelta)/2*a else null end  as x2
from seconddegre as sd
CROSS APPLY (select sd.b*sd.b-4*sd.a*sd.c as delta, sqrt(abs(sd.b*sd.b-4*sd.a*sd.c)) as rdelta) as d 
where delta>=0;

Enlevez le WHERE vous obtiendrez les NULL!!!
@+

**alassanediakite** · 22/06/2017, 12h45

Salut
Je ne comprend pas vraiment le + et le - des postes précédents.

Envoyé par SQLpro

l'opérateur d'intra-jointure LATERAL a été introduit par la norme SQL:1999 mais a été violemment critiqué car donnant des résultats potentiellement faux. Et d'ailleurs votre exemple donne un résultat faux car la ligne contenant des NULLs n'a pas de sens dans ce cas. De ce fait Microsoft à proposé de remplacer cet opérateur par l'opérateur APPLY décliné en deux versions : CROSS (produit cartésien) et OUTER (qui rajoute une ligne en cas de NULL dans les paramètres d'application). Oracle à d'ailleurs repris l'opérateur APPLY et quasiment abandonné l'opérateur LATERAL. APPLY a été proposé au comité de normalisation en remplacement de LATERAL, mais le mal est fait

pourquoi cette position inexplicable sur SQL SERVER: quand il respecte la norme on le félicite, sinon c'est la norme qui est critiquée?
J'ai montré (en parlant du where) que (dans la situation actuelle) apply et lateral donnent le même résultat. Ce qui m'a valu (-1)!!!
D'ailleurs pourquoi la ligne du null serait un non sens?
Je rappelle que nous somme sur un forum scientifique et non politique ou religieux!!!
@+

Pourquoi pas de variable dans SELECT ?

Langage SQL

Discussions similaires

Partager

Partager