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

Re: BUG #5898: Nested "in" clauses hide bad column names

From: Eric Schwarzenbach <subscriber(at)blackbrook(dot)org>
To: Scott Dunbar <scott(at)xigole(dot)com>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: BUG #5898: Nested "in" clauses hide bad column names
Date: 2011-02-22 21:28:39
Message-ID: 4D642A87.5090405@blackbrook.org (view raw or flat)
Thread:
Lists: pgsql-bugs
Using table aliases prevents such problems by disambiguating the column 
names. For example, if you had table aliases in just one part of this 
query as in the below, you would have gotten an error instead of 
deleting all those rows:

select respondent_id from respondent where respondent_id in (select
cs.respondent_id from chat_session cs where cs.project_id in (select project_id from
project where company_id = 4));


Cheers,

Eric



On 2/22/2011 1:07 PM, Scott Dunbar wrote:
> Yes, you're correct.  I guess this makes sense but it does seem 
> strange that I can enter garbage in a query but it still runs.  And in 
> my case the output from this (the entire table) was then used in a 
> delete statement that toasted the entire table.  Allowing bogus SQL 
> just seems "wrong" but I do understand what's going on.
>
> Thanks for your help.
>
>
> On 02/22/2011 10:45 AM, Tom Lane wrote:
>> "Scott Dunbar"<scott(at)xigole(dot)com>  writes:
>>> I have a nested in clause like:
>>> select respondent_id from respondent where respondent_id in (select
>>> respondent_id from chat_session where project_id in (select project_id from
>>> project where company_id = 4));
>>> However, in this example, there is no column named respondent_id in the
>>> chat_session table.
>> Probably there is one in respondent, though?  This behavior is not a bug
>> --- what you have there is an outer reference, and it is working exactly
>> as specified by the SQL standard.  Sub-selects would be a whole lot less
>> useful if they couldn't refer to variables of the outer query.
>>
>> 			regards, tom lane
>
>
> -- 
> Scott Dunbar
> Xigole Systems, Inc.
> Enterprise software consulting, development, and hosting
> 303·667·6343

In response to

pgsql-bugs by date

Next:From: Bruce MomjianDate: 2011-02-23 04:33:43
Subject: Re: [BUGS] issue about information_schema REFERENTIAL_CONSTRAINTS
Previous:From: Mark KirkwoodDate: 2011-02-22 21:20:13
Subject: Re: Hung Vacuum in 8.3

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