Forums PostgreSQL.fr

i20

Membre

Comportement EXCEPT entre 2 vues

Tout d'abord bonjour à tous vu que c'est mon premier message sur le forum.

Voilà mon problème est le suivant: j'ai 2 vues dont la requête diffère (l'une imbriquée et pas l'autre) mais qui sont sensées renvoyer le même résultat.
Ces vues associent un user_id à un id de package (trial, basic, plus ou privilege), chaque user_id n'ayant qu'une entrée dans la vue. Appelons ces vues
"user_packages1" (sans sous requête) et "user_packages2" (imbriquée).

Voulant vérifier l'égalité de ces 2 vues, je fais un EXCEPT et je m'attend à un résultat vide. Mais il ne l'est pas.
Ma question est: pourquoi le premier EXCEPT renvoie une ligne qui est pourtant bien présente dans "user_packages2". Et également pourquoi le deuxième EXCEPT renvoie une ligne qui n'est dans aucune des 2 vues.

Voici les requêtes de comparaison:
----------------------------------------------------------------------------------------------------------------------------

(SELECT * FROM "user_packages1")
EXCEPT
(SELECT * FROM "user_packages2")

  +--------------------- user_id ------------------+------------------ package_id -----------------+
=|"51d16541-33b8-415d-983e-1b0f256c8bb9"|"51cd6779-852c-4403-b1a7-0308256c8bb9"| (usertest;basic)
  +-------------------------------------------------+--------------------------------------------------+

(SELECT * FROM "user_packages2")
EXCEPT
(SELECT * FROM "user_packages1")

  +--------------------- user_id ------------------+------------------ package_id ----------------+
=|"51d16541-33b8-415d-983e-1b0f256c8bb9"|"51cd6779-15b8-47d0-8f64-0308256c8bb9"| (usertest;trial)
  +-------------------------------------------------+-------------------------------------------------+

Sachant que l'entrée pour cet user particulier est la même dans les 2 vues:
----------------------------------------------------------------------------------------------------------------------------

(SELECT * FROM "user_packages1" WHERE user_id = '51d16541-33b8-415d-983e-1b0f256c8bb9')

  +--------------------- user_id ------------------+------------------ package_id -----------------+
=|"51d16541-33b8-415d-983e-1b0f256c8bb9"|"51cd6779-852c-4403-b1a7-0308256c8bb9"| (usertest;basic)
  +-------------------------------------------------+-------------------------------------------------+

(SELECT * FROM "user_packages2" WHERE user_id = '51d16541-33b8-415d-983e-1b0f256c8bb9')

  +--------------------- user_id ------------------+------------------ package_id -----------------+
=|"51d16541-33b8-415d-983e-1b0f256c8bb9"|"51cd6779-852c-4403-b1a7-0308256c8bb9"| (usertest;basic)
  +-------------------------------------------------+-------------------------------------------------+

Voici le code des 2 vues (elles ont exactement le même nombre de lignes):
----------------------------------------------------------------------------------------------------------------------------

CREATE VIEW "user_packages1" AS 
SELECT
    DISTINCT ON (UI.user_id) user_id, 
    P.id AS package_id 
FROM
    "user_items" AS UI,
    "packages" AS P,
    "package_types" AS PT
WHERE
    UI.model = 'Package' AND
    UI.model_foreign_key = P.id AND
    P.package_type_id = PT.id
ORDER BY
    UI.user_id,
    PT.weight DESC;

----------------------------------------------------------------------------------------------------------------------------

CREATE VIEW "user_packages2" AS 
SELECT DISTINCT ON (UAP.user_id) user_id, UAP.model_foreign_key AS package_id 
FROM (
  SELECT UI.user_id, UI.model_foreign_key, PT.name
  FROM "user_items" UI
  INNER JOIN "packages" P
  ON P.id=UI.model_foreign_key
  INNER JOIN "package_types" PT
  ON PT.id=P.package_type_id
  WHERE UI.model='Package'
  ORDER BY PT.weight DESC
) AS UAP;

----------------------------------------------------------------------------------------------------------------------------

Ce que je ne comprend pas c'est d'où peux sortir la ligne renvoyée par le second EXCEPT étant donné qu'elle n'est présente dans aucune des deux vues?!

Après m'être cassé la tête pendant un "petit" moment, j'en suis venu à la conclusion que EXCEPT ne devait pas traiter les requêtes séparément avant d'exclure mais plutôt tenter une optimisation (qui aurait foiré ici?) car sinon il n'y a aucune chance que le résultat de la seconde comparaison soit ce qu'il est.

A savoir, ces vues récupèrent pour chaque user le meilleur package qui lui est attaché, plus explicitement dans la table sur laquelle les vues sont calculées le user "usertest" ('51d16541-33b8-415d-983e-1b0f256c8bb9') a 2 entrées, une avec le package "trial" ('51cd6779-15b8-47d0-8f64-0308256c8bb9'), l'autre avec le package "basic" ('51cd6779-852c-4403-b1a7-0308256c8bb9') soit:

+--------------------- user_id ------------------+------------------ package_id -----------------+
|                              ...                              |                             ...                              |
|"51d16541-33b8-415d-983e-1b0f256c8bb9"|"51cd6779-852c-4403-b1a7-0308256c8bb9"| (usertest;basic)
|"51d16541-33b8-415d-983e-1b0f256c8bb9"|"51cd6779-15b8-47d0-8f64-0308256c8bb9"| (usertest;trial)
|                              ...                              |                             ...                              |
+-------------------------------------------------+-------------------------------------------------+

Il n'y a donc que de là que pourrait venir le résultat de la seconde comparaison à priori ... Est-ce un bug ou ai-je simplement mal fait quelque chose? Je vous remercie par avance pour toute aide que vous pourrez m'apporter car là j'abandonne ^^

Dernière modification par i20 (06/08/2013 16:52:00)

SAS

Membre

Re : Comportement EXCEPT entre 2 vues

Bonjour,

La clause EXCEPT n'est pas optimisée.

La première requête est évaluée, et le résultat de la seconde lui est soustrait.

Dans votre premier cas, on obtient le résultat de la vue 1 moins le résultat de la vue 2.
Dans le second cas on évalue d'abord la seconde vue, puis la première.

C'est de la théorie des ensembles, pas de l'algèbre.

Il n'y a a priori pas de raison d'espérer que l'opération soit commutative.

---

Stéphane Schildknecht
Conseil, formations et support PostgreSQL
http://www.loxodata.com

i20

Membre

Re : Comportement EXCEPT entre 2 vues

Merci pour la réponse.

Ceci dit, oui j'ai bien compris que EXCEPT n'était pas commutatif c'est logique, ce que je ne comprend pas c'est comment la 2e comparaison arrive à me renvoyer une ligne qui n'est dans aucune des 2 vues?

i20

Membre

Re : Comportement EXCEPT entre 2 vues

UP S'il vous plaît

i20

Membre

Re : Comportement EXCEPT entre 2 vues

Quelqu'un s'il vous plaît :'( J'aimerais vraiment comprendre! Pour info je suis avec Postgre 8.4 est-ce que ça peut venir de là (ça m'étonnerait) ? ...

gleu

Administrateur

Re : Comportement EXCEPT entre 2 vues

Si vous pouviez faire un jeu de tests complet (deux tables, avec peu de données mais qui montrent le problème), vous auriez plus de chances d'avoir une réponse.

---

Guillaume.

SQLpro

Membre

Re : Comportement EXCEPT entre 2 vues

Ce que vous voulez, je présume, c'est savoir si vos deux requêtes donnent des résultats identiques ?
Si tel est le cas, la vérification se fait à l'aide de la formule algébrique suivante :
A - B U B - A = Ø
Soit en SQL la requête suivante :

WITH 
T0 AS (requête SELECT 1),
T1 AS (requête SELECT 2)
SELECT * FROM T0
EXCEPT
SELECT * FROM T1
UNION ALL
SELECT * FROM T1
EXCEPT
SELECT * FROM T0

A +

---

Frédéric Brouard, alias SQLpro,  ARCHITECTE DE DONNÉES,  Expert langage SQL
Le site sur les SGBD relationnel et langage SQL   : http://sqlpro.developpez.com/
Modélisation de données, conseil, expertise, audit, optimisation, tuning, formation
* * * * *  Enseignant CNAM PACA, ISEN Toulon,  CESI Aix en Provence  * * * * *

i20

Membre

Re : Comportement EXCEPT entre 2 vues

Oui, ceci dit pourquoi aller jusqu'à faire une union étant donné qu'on voit bien que rien que sur un except le résultat n'est pas vide?