Skip site navigation (1) Skip section navigation (2)

Re: Weird (?) problem with order of conditions in SELECT

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Mark, Terry" <tmark(at)amgen(dot)com>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: Weird (?) problem with order of conditions in SELECT
Date: 2001-03-20 16:41:45
Message-ID: 11254.985106505@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-novice
"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

In response to

pgsql-novice by date

Next:From: Mark, TerryDate: 2001-03-20 17:12:00
Subject: RE: Weird (?) problem with order of conditions in SELECT
Previous:From: Mark, TerryDate: 2001-03-20 16:06:02
Subject: Weird (?) problem with order of conditions in SELECT

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group