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

On Thu, Jul 14, 2016 at 9:52 AM, <jason@signalvine.com> wrote:

> The following bug has been logged on the website:
>
> Bug reference:      14250
> Logged by:          jason turim
> Email address:      jason@signalvine.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);
> =E2=80=8B[...]
>
> -- 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 t=
he
> child table are deleted
> delete from child where parent_id in (select parent_id from parent where =
id
> =3D 1);
>

=E2=80=8BA 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))
=E2=80=8Bwhich 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 =3D 1)

The above will provoke the error you wish to see.

=E2=80=8B=E2=80=8BDavid J.