From: | "Mark, Terry" <tmark(at)amgen(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | pgsql-novice(at)postgresql(dot)org |
Subject: | RE: Weird (?) problem with order of conditions in SELECT |
Date: | 2001-03-20 17:12:00 |
Message-ID: | 548152BB0AD9D2119C400008C7CFE8C805AFCBD5@gold-exch.amgen.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
Many thanks to Tom for his reply. My intention was to have the subquery
treated as a completely independent query.
Should I understand, then, that by explicitly naming all the involved table
portions in the subequery, that the subquery is guaranteed to be treated
independently ?
terry
-----Original Message-----
From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
Sent: Tuesday, March 20, 2001 11:42 AM
To: Mark, Terry
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: [NOVICE] Weird (?) problem with order of conditions in
SELECT
"Mark, Terry" <tmark(at)amgen(dot)com> writes:
> I have encountered a weird problem I can't seem to understand. It
involves
> a correlated subquery, where the rows returned seem to depend upon the
order
> I specify my conditions. I can't see why the order should be important
> (except maybe for performance)
> SELECT c.score FROM c
> WHERE c.score >= (SELECT MAX(score) AS score FROM c
> WHERE a.name='nugget'
> AND a.job='programmer'
> AND a.a_id=b.a_id AND c.b_id = b.b_id)
> AND a.name='nugget'
> AND a.job='programmer'
> AND a.a_id=b.a_id
> AND c.b_id = b.b_id;
> SELECT c.score FROM c
> WHERE a.name='nugget'
> AND a.job='programmer'
> AND a.a_id=b.a_id
> AND c.b_id = b.b_id
> AND c.score >= (SELECT MAX(score) AS score FROM c
> WHERE a.name='nugget'
> AND a.job='programmer'
> AND a.a_id=b.a_id
> AND c.b_id = b.b_id);
This is a little less mysterious if you run it under 7.1, because 7.1
emits some warning notices:
NOTICE: Adding missing FROM-clause entry in subquery for table "a"
NOTICE: Adding missing FROM-clause entry in subquery for table "b"
NOTICE: Adding missing FROM-clause entry for table "a"
NOTICE: Adding missing FROM-clause entry for table "b"
score
-------
2500
(1 row)
NOTICE: Adding missing FROM-clause entry for table "a"
NOTICE: Adding missing FROM-clause entry for table "b"
score
-------
100
2500
(2 rows)
From this we can infer that Postgres is actually interpreting the first
query as
SELECT c.score FROM a,b,c
WHERE c.score >= (SELECT MAX(score) AS score FROM a,b,c
WHERE a.name='nugget'
AND a.job='programmer'
AND a.a_id=b.a_id AND c.b_id = b.b_id)
AND a.name='nugget'
AND a.job='programmer'
AND a.a_id=b.a_id
AND c.b_id = b.b_id;
whereas the second one is being interpreted as
SELECT c.score FROM a,b,c
WHERE a.name='nugget'
AND a.job='programmer'
AND a.a_id=b.a_id
AND c.b_id = b.b_id
AND c.score >= (SELECT MAX(score) AS score FROM c
WHERE a.name='nugget'
AND a.job='programmer'
AND a.a_id=b.a_id
AND c.b_id = b.b_id);
That is, in the second case the sub-select's references to A and B are
being taken as outer references to the current A and B rows of the outer
query, whereas in the first case the sub-select is interpreted as a
completely independent query.
I am not sure which interpretation you were actually intending.
This example shows one reason why the "implicit FROM item" feature of
Postgres is confusing and has come to be deprecated: it's not always
clear which FROM list an implicit item should be added to. We've
started to emit a warning about use of this feature in 7.1, and perhaps
someday it will be removed entirely.
regards, tom lane
From | Date | Subject | |
---|---|---|---|
Next Message | Lars Forseth | 2001-03-20 21:08:47 | Postgres installataion in SUSE 7.0, lacking dirs i ~postgres/data |
Previous Message | Tom Lane | 2001-03-20 16:41:45 | Re: Weird (?) problem with order of conditions in SELECT |