Discussion :

[cogreen]

Bonjour,

Je développe pour une société un logiciel qui traduit une requête d'un langage de requête propriétaire en une requête SQL.

Mon problème est qu'une des requêtes SQL générée N'EST PAS éxecutable dans Oracle alors qu'elle l'est dans PostgreSQL.
Il me semble qu'oracle me dit qu'il y a une erreur de syntaxe et que je fais référence à une table qui n'a pas été déclarée dans une clause from.

J'utilise Oracle 11.1.0 et PostgreSQL 8.3.

J'ai simplifié le model de BD pour expliquer mon problème : on représente ici des couples de personnes (un organisateur et un participant) qui sont impliqués dans des évènements (mariage, séminaire, sortie mondaine, etc...)

On a donc deux tables Person et Event dans le modèle.
La table Person a un attribut person_id (et d'autres attributs qu'on n'a pas besoin de connaitre pour mon problème).
La table Event a 4 attributs : event_id, event_type, organizer_id et participant_id.

La requête suivante est exécutable dans PostgreSQL mais pas dans Oracle :

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
 
	select t2.c1 as c1
	from (select t1.person_id as c1 from person t1) t2
	where 
		not exists 
			(
				select t3.person_id as c2 
				from 
					person t3,
					(
						select t5.event_id as c3, t5.organizer_id as c4
						from event t5
						where (t5.participant_id = t2.c1)
					) t4
				where (t4.c4 = t3.person_id)
			)

Le message d'erreur est ORA-00904: "T2"."C1" : identificateur non valide.

Pour la rendre exécutable dans Oracle, je modifie la requête en faisant "remonter" la référence "t2.c1" au premier niveau des imbrications :

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
 
	select t2.c1 as c1
	from (select t1.person_id as c1 from person t1) t2
	where 
		not exists 
			(
				select t3.person_id as c2 
				from 
					person t3,
					(
						select t5.event_id as c3, t5.organizer_id as c4, t5.participant_id as c5
						from event t5
					) t4
				where ((t4.c4 = t3.person_id) and (t4.c5 = t2.c1))
			)

Mais cela ne résout pas le problème pour le cas général car : pas toutes les "mauvaises" références de ce type sont "remontables".
En fait, il me semble que Oracle lève une erreur lorsqu'une référence à une table déclarée "plus haut" est faite dans un from à un niveau d'imbrication égal ou supérieur à 2. Or on peut toujours se débrouiller pour construire des requêtes dont les "mauvaises références" restent coincées à un niveau d'imbrication supérieur ou égal à 2 et ne sont pas "remontables" jusqu'au premier niveau.

Par exemple, on considère une requête avec un double niveau de "not exists" : un "not exists" placé dans une requête placée dans un from lui même participant à une requête qui est placée dans un autre "not exists". On place une référence - comme celle faite à "t2.c1" - dans le "not exists" de plus bas niveau, et la "remontée" de cette référence s'arrête au "not exists" de plus bas niveau. On ne peut pas faire remonter cette référence au dessus du "not exists" sous laquelle elle a été placée. Et entre la déclaration de la table et la référence à cette table il y a toujours un niveau d'imbrication supérieur ou égal à 2. Et oracle est perdu...

Ce genre de requête n'est pas exécutable dans Oracle alors qu'elle l'est toujours dans PostgreSQL (j'en ai testé plusieurs)...

Quelqu'un a-t-il connaissance de ce problème ?
Y-a-t il des solutions ? Peut être que ce bug est-il corrigé dans une version plus récente d'Oracle (si c'est un bug) ?

Olivier

[SQLpro]

1) votre requête est incomplète : on ne sait pas ce qu'il y a avant la parenthèse. postez la requête complète.
2) Oracle est largement anormatif et contraignant, contrairement à PostGreSQL ou SQL Server qui sont plus respectueux de la norme SQL

Je suis en train d'écrire un article à ce sujet sur les différences qui rendent incompatible les requêtes entre les différents SGBDR à des niveaux basiques.

Petit exemple :

"
3 - Typage des données dans les calculs

La norme spécifie que la précision et le type de données résultant d'une expression arithmétique est laissé à l'appréciation des éditeurs dans les limites suivantes :
o lorsque les opérandes sont un type numérique exact, le résultat doit être un type numérique exact.
o dans le cas d'une addition, l'échelle doit être l'échelle maximum des opérandes
o dans le cas d'une multiplication, l'échelle doit être l'adition des échelles des opérandes

Problème : Oracle ne reconnait pas les types numériques entier (même s'il le fait croire) et renvoie systématiquement un type de données propriétaire "NUMBER" qui s'apparente au type DECIMAL de la norme SQL.

Exemple :

Code :

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

1
2
3
4
5
	           |  ORACLE   |  SQL Server
--------------------------------------------
SELECT 1 / 2 AS N  |  N        |  N
FROM   Table1      |  ----     |  ---
                   |  0.5      |  0

Mutualisation du code : impossible, récrire les requêtes au cas par cas.

"

En conclusion il n'est pas possible de faire des requêtes SQL qui passent sur toutes les SGBDR !

A +

[cogreen]

Je ne suis pas sur de comprendre... de quelle parenthèse parlez vous ?
Il ne faut pas tenir compte du premier niveau de parenthèse (si vous faites référence à celle ci).

Ma requête est :

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
 
SELECT t2.c1 AS c1
FROM (SELECT t1.person_id AS c1 FROM person t1) t2
WHERE 
	NOT EXISTS 
		(
			SELECT t3.person_id AS c2 
			FROM 
				person t3,
				(
					SELECT t5.event_id AS c3, t5.organizer_id AS c4
					FROM event t5
					WHERE (t5.participant_id = t2.c1)
				) t4
				WHERE (t4.c4 = t3.person_id)
		)

J'ai mis à jour le premier post en enlevant le 1er niveau de parenthèse

[Waldar]

SQLPro

Préambule : à cause des précisions limitées, les nombres réels peuvent tous être apparentés à des décimaux dans les SGBD.

Au niveau des types de nombre chez Oracle :
NUMBER ce sont des réels (voir préambule).
NUMBER(p, s) sont des décimaux tant que s ne vaut pas zéro
NUMBER(p) ou NUMBER(p, 0) sont des entiers.

Les entiers ne formant qu'un sous-ensemble des décimaux, Oracle considère - à juste titre en ce qui me concerne - qu'une division retournera un décimal plutôt qu'un entier.

cogreen

Effectivement Oracle ne s'arrête qu'à un seul niveau de profondeur en ce qui concerne les références.
Je ne pense pas que ce soit une erreur mais un choix, après ce dernier est-il correctement motivé je ne sais pas.

Maintenant quand je regarde vos deux requêtes alors que seulement deux tables sont en jeu, je suis quand même effaré !

Quoi que vous vouliez faire, le SQL généré est trop abscons pour que les performances (et la compréhension) soient au rendez-vous.
D'avance je plains celui qui devra débugger tout ce code.

[cogreen]

Merci pour votre réponse

Oui effectivement pour la présente requête le SQL généré est compliqué mais c'est la contrepartie pour avoir une méthode de traduction en SQL qui puisse traiter l'ensemble du langage de requête propriétaire que je dois traduire (et qui est somme toute très riche).

J'avais besoin qu'on me confirme que Oracle ne gère les références qu'à un seul niveau d'imbrication. Donc merci pour ce point.

Vous me confirmez aussi qu'il n'y a pas d'autres alternatives avec Oracle ? Si je veux absolument executer une requête SQL avec de telles imbrications je dois utiliser un autre outil (comme postgreSQL ou autre) ? N'y-a-t-il pas une possibilité de configuration/paramétrage ?

Merci beaucoup

[SQLpro]

SQLProLes entiers ne formant qu'un sous-ensemble des décimaux, Oracle considère - à juste titre en ce qui me concerne - qu'une division retournera un décimal plutôt qu'un entier.

Ce qui n'est pas conforme à la norme qui considère qu'en cas de pluralité d'opérande, le résultat se fait sur le type le plus "large". Or pour deux entiers, le type le plus "large" reste l'entier !

A +

[CinePhil]

J'ai du mal à comprendre la requête !

Code :

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

1
2
3
SELECT t5.event_id AS c3, t5.organizer_id AS c4
                    FROM event t5
                    WHERE (t5.participant_id = t2.c1)

Cette partie, dans laquelle t2.c1 fait référence à l'identifiant de la personne qu'on veut sélectionner au final, semble signifier :
"Sélectionner l'identifiant de l'événement et l'identifiant de son organisateur pour lequel l'identifiant du participant est l'identifiant de la personne qu'on souhaite sélectionner (c1)."

Cette sous-requête a pour alias t4.

Code :

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

1
2
3
4
5
6
7
8
9
SELECT t3.person_id AS c2 
            FROM 
                person t3,
                (
                    SELECT t5.event_id AS c3, t5.organizer_id AS c4
                    FROM event t5
                    WHERE (t5.participant_id = t2.c1)
                ) t4
            WHERE (t4.c4 = t3.person_id)

Ici est fait une jointure (avec la syntaxe obsolète depuis 1992 ! ) entre la sous-requête et la table person pour n'extraire que l'identifiant de la personne que l'on connait déjà puisqu'il s'agit de l'identifiant de l'organisateur de l'événement. La jointure est donc inutile !

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
WHERE 
    NOT EXISTS 
        (
            SELECT t3.person_id AS c2 
            FROM 
                person t3,
                (
                    SELECT t5.event_id AS c3, t5.organizer_id AS c4
                    FROM event t5
                    WHERE (t5.participant_id = t2.c1)
                ) t4
            WHERE (t4.c4 = t3.person_id)
        )

D'autant plus inutile qu'il est inutile de préciser quelle colonne extraire dans une sous requête figurant dans une clause WHERE NOT EXISTS.
Cette partie peut se lire :
"Où il n'existe pas de ligne de table personne jointe à la sous-requête d'alias t4."
Généralement, derrière un NOT EXISTS, on se contente d'un SELECT * ou SELECT 1. Personnellement, je préfère SELECT * qui signifie bien 'toute la ligne' plutôt que SELECT 1 qui est une valeur arbitraire.

Donc je récris cette partie :

Code :

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

1
2
3
4
5
6
7
8
9
10
WHERE NOT EXISTS 
        (
            SELECT * 
            FROM person t3
            INNER JOIN (
                SELECT t5.event_id AS c3, t5.organizer_id AS c4
                FROM event t5
                WHERE (t5.participant_id = t2.c1)
            ) t4 ON t4.c4 = t3.person_id
        )

Traduction :
"Où il n'existe pas de personne organisatrice d'un événement dont le participant est la personne sélectionnée par la requête générale."

Dès lors se pose la question : "Quelle est cette personne recherchée ?"

Au final, qu'est censée chercher cette requête ?
- Les personnes qui ne sont pas à la fois participant et organisateur d'un événement ?
- Les personnes qui participent à au moins un événement mais n'en organisent aucun ?