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

Re: Bug or expected behaviour

From: Ravinder Bhalla <Ravinder(dot)Bhalla(at)ipix(dot)com>
To: 'Stephan Szabo' <sszabo(at)megazone(dot)bigpanda(dot)com>
Cc: "'sfpug(at)postgresql(dot)org'" <sfpug(at)postgresql(dot)org>
Subject: Re: Bug or expected behaviour
Date: 2003-12-15 22:35:37
Message-ID: 7BF4AEDBD413334CB37BD66D4DBB9636821BC8@sr-strongbad (view raw or flat)
Thread:
Lists: sfpug
My bad, (assuming "empname" also DOESN't exists in Dept)

Ravinder

-----Original Message-----
From: Ravinder Bhalla 
Sent: Monday, December 15, 2003 2:32 PM
To: 'Stephan Szabo'
Cc: sfpug(at)postgresql(dot)org
Subject: RE: [sfpug] Bug or expected behaviour


Does that mean 

Both the following query will throw same result (assuming empname aslo exist
in Dept)

select * from emp where empid in ( select empname from dept);

select * from emp where empid in ( select empid from dept) ; 

Or Could u pls tell me exactly how the Optimizer transform the above
query(ies)

Thanks again
Ravinder

-----Original Message-----
From: Stephan Szabo [mailto:sszabo(at)megazone(dot)bigpanda(dot)com] 
Sent: Monday, December 15, 2003 2:21 PM
To: Ravinder Bhalla
Cc: sfpug(at)postgresql(dot)org
Subject: Re: [sfpug] Bug or expected behaviour



On Fri, 12 Dec 2003, Ravinder Bhalla wrote:

> billdb=# select * from emp where empid in ( select empid from dept);
> empid | empname | dept_no | age
> -------+---------+---------+-----
>      1 | Abhay   |     100 |  24
>      2 | AB      |     200 |  26
> (2 rows)
>
>
> Why the query is returning rows when empid column doesn't exists in
> dept table. Should throw an error.

No, it shouldn't.  Basically, if a name can't be resolved in the subselect,
it will be tried as an outer reference (in this case to the emp row being
checked). Since there is an empid in the outer scope, it resolves to that.
IIRC, that's a spec mandated behavior, even if it's often unwanted.

sfpug by date

Next:From: Stephan SzaboDate: 2003-12-16 01:23:26
Subject: Re: Bug or expected behaviour
Previous:From: Josh BerkusDate: 2003-12-15 22:31:49
Subject: Re: Bug or expected behaviour

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