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
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.

Responses

Browse sfpug by date

  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