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

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.