Re: BUG #14250: Error in subquery fails silently and parent query continues to execute

I see, thanks. Have you all considered making it an error to execute
correlated queries without table qualifying the column names?

On Jul 14, 2016 11:15 PM, "David G. Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com>
wrote:

> On Thu, Jul 14, 2016 at 9:52 AM, <jason(at)signalvine(dot)com> wrote:
>
>> The following bug has been logged on the website:
>>
>> Bug reference: 14250
>> Logged by: jason turim
>> Email address: jason(at)signalvine(dot)com
>> PostgreSQL version: 9.5.3
>> Operating system: Ubuntu 14.04 / Mac 10.11.5 (15F34)
>> Description:
>>
>> create table parent (id serial primary key, name text);
>> create table child (id serial primary key, parent_id int, name text);
>> ​[...]
>>
>> -- the sub-query contains an error, there is no parent_id in the parent
>> table
>> -- we'd expect the following query to fail, however, all the records in
>> the
>> child table are deleted
>> delete from child where parent_id in (select parent_id from parent where
>> id
>> = 1);
>>
>
> ​A common complaint but unfortunately something that simply has to be
> learned.
>
> The reference to parent_id in the subquery comes from the child - which in
> this case makes the where clause (child.parent_id IN (child.parent_id))
> ​which will always evaluate to true.
>
> This is termed (though not in our docs) a "correlated subquery" and can be
> very useful when used correctly. Its presence is also why it is
> recommended to table-qualify columns when dealing with subqueries.
>
> WHERE child.parent_id IN (SELECT parent.parent_id FROM parent WHERE
> parent.id = 1)
>
> The above will provoke the error you wish to see.
>
> ​​David J.
>
>