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.g.johnston@gmail.com>
wrote:
> 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
>> the
>> 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 t=
o be
> learned.
>
> The reference to parent_id in the subquery comes from the child - which i=
n
> 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 b=
e
> 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.
>
>