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 |
Subject: | Re: Bug or expected behaviour |
Date: | 2003-12-15 22:31:34 |
Message-ID: | 7BF4AEDBD413334CB37BD66D4DBB9636821BC7@sr-strongbad |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | sfpug |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Josh Berkus | 2003-12-15 22:31:49 | Re: Bug or expected behaviour |
Previous Message | Stephan Szabo | 2003-12-15 22:20:44 | Re: Bug or expected behaviour |