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

Re: Bug or expected behaviour

From: Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com>
To: Ravinder Bhalla <Ravinder(dot)Bhalla(at)ipix(dot)com>
Cc: sfpug(at)postgresql(dot)org
Subject: Re: Bug or expected behaviour
Date: 2003-12-15 22:20:44
Message-ID: 20031215141623.R91640@megazone.bigpanda.com (view raw or flat)
Thread:
Lists: sfpug
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.

In response to

sfpug by date

Next:From: Ravinder BhallaDate: 2003-12-15 22:31:34
Subject: Re: Bug or expected behaviour
Previous:From: Patrick HatcherDate: 2003-12-15 20:47:06
Subject: Re: Bug or expected behaviour

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