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

VGhlIGZvbGxvd2luZyBidWcgaGFzIGJlZW4gbG9nZ2VkIG9uIHRoZSB3ZWJz
aXRlOgoKQnVnIHJlZmVyZW5jZTogICAgICAxNDI1MApMb2dnZWQgYnk6ICAg
ICAgICAgIGphc29uIHR1cmltCkVtYWlsIGFkZHJlc3M6ICAgICAgamFzb25A
c2lnbmFsdmluZS5jb20KUG9zdGdyZVNRTCB2ZXJzaW9uOiA5LjUuMwpPcGVy
YXRpbmcgc3lzdGVtOiAgIFVidW50dSAxNC4wNCAvIE1hYyAxMC4xMS41ICgx
NUYzNCkKRGVzY3JpcHRpb246ICAgICAgICAKCmNyZWF0ZSB0YWJsZSBwYXJl
bnQgKGlkIHNlcmlhbCBwcmltYXJ5IGtleSwgbmFtZSB0ZXh0KTsNCmNyZWF0
ZSB0YWJsZSBjaGlsZCAoaWQgc2VyaWFsIHByaW1hcnkga2V5LCBwYXJlbnRf
aWQgaW50LCBuYW1lIHRleHQpOw0KDQppbnNlcnQgaW50byBwYXJlbnQgKG5h
bWUpIHZhbHVlcyAoJ2EnKTsNCmluc2VydCBpbnRvIHBhcmVudCAobmFtZSkg
dmFsdWVzICgnYicpOw0KaW5zZXJ0IGludG8gcGFyZW50IChuYW1lKSB2YWx1
ZXMgKCdjJyk7DQoNCmluc2VydCBpbnRvIGNoaWxkIChuYW1lLCBwYXJlbnRf
aWQpIHZhbHVlcyAoJ3gnLCAxKTsNCmluc2VydCBpbnRvIGNoaWxkIChuYW1l
LCBwYXJlbnRfaWQpIHZhbHVlcyAoJ3knLCAxKTsNCmluc2VydCBpbnRvIGNo
aWxkIChuYW1lLCBwYXJlbnRfaWQpIHZhbHVlcyAoJ3onLCAxKTsNCg0KaW5z
ZXJ0IGludG8gY2hpbGQgKG5hbWUsIHBhcmVudF9pZCkgdmFsdWVzICgncics
IDIpOw0KaW5zZXJ0IGludG8gY2hpbGQgKG5hbWUsIHBhcmVudF9pZCkgdmFs
dWVzICgncycsIDIpOw0KaW5zZXJ0IGludG8gY2hpbGQgKG5hbWUsIHBhcmVu
dF9pZCkgdmFsdWVzICgndCcsIDIpOw0KDQppbnNlcnQgaW50byBjaGlsZCAo
bmFtZSwgcGFyZW50X2lkKSB2YWx1ZXMgKCdtJywgMyk7DQppbnNlcnQgaW50
byBjaGlsZCAobmFtZSwgcGFyZW50X2lkKSB2YWx1ZXMgKCduJywgMyk7DQpp
bnNlcnQgaW50byBjaGlsZCAobmFtZSwgcGFyZW50X2lkKSB2YWx1ZXMgKCdv
JywgMyk7DQoNCi0tIHRoZSBzdWItcXVlcnkgY29udGFpbnMgYW4gZXJyb3Is
IHRoZXJlIGlzIG5vIHBhcmVudF9pZCBpbiB0aGUgcGFyZW50CnRhYmxlDQot
LSB3ZSdkIGV4cGVjdCB0aGUgZm9sbG93aW5nIHF1ZXJ5IHRvIGZhaWwsIGhv
d2V2ZXIsIGFsbCB0aGUgcmVjb3JkcyBpbiB0aGUKY2hpbGQgdGFibGUgYXJl
IGRlbGV0ZWQNCmRlbGV0ZSBmcm9tIGNoaWxkIHdoZXJlIHBhcmVudF9pZCBp
biAoc2VsZWN0IHBhcmVudF9pZCBmcm9tIHBhcmVudCB3aGVyZSBpZAo9IDEp
Ow0KCgo=

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.

On Thu, Jul 14, 2016 at 6:40 PM, Jason Turim <jason@signalvine.com> wrote:

> I see, thanks.  Have you all considered making it an error to execute
> correlated queries without table qualifying the column names?
>
=E2=80=8BWill never happen.  I'm not even sure it would be desirable in a
greenfield situation let alone in an established product.

One automated way to handle this would be for a schema-aware static
analyzer to be run on the query in question and point out the discrepancy.
If we ever got something like that into core people could choose this
behavior as part of its configuration.  There is quite a bit of room
outside of core for tools of this nature to be of value.

David J.
=E2=80=8B

David G. Johnston wrote:
> On Thu, Jul 14, 2016 at 6:40 PM, Jason Turim <jason@signalvine.com> wrote:
>
> > I see, thanks.  Have you all considered making it an error to execute
> > correlated queries without table qualifying the column names?
> >
> ​Will never happen.  I'm not even sure it would be desirable in a
> greenfield situation let alone in an established product.

The problem is that this is defined by the SQL standard, so we're not at
liberty to change it.  The opinion of several people is that it would be
safer to require the qualification.  If this were a green field I'm sure
we'd do it differently.

--
Álvaro Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

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.
>
>

On Thu, Jul 14, 2016 at 6:57 PM, Alvaro Herrera <alvherre@2ndquadrant.com>
wrote:

> David G. Johnston wrote:
> > On Thu, Jul 14, 2016 at 6:40 PM, Jason Turim <jason@signalvine.com>
> wrote:
> >
> > > I see, thanks.  Have you all considered making it an error to execute
> > > correlated queries without table qualifying the column names?
> > >
> > =E2=80=8BWill never happen.  I'm not even sure it would be desirable in=
 a
> > greenfield situation let alone in an established product.
>
> The problem is that this is defined by the SQL standard, so we're not at
> liberty to change it.


=E2=80=8B=E2=80=8BWe as a product are at liberty to make the change.=E2=80=
=8B


> The opinion of several people is that it would be
> safer to require the qualification.  If this were a green field I'm sure
> we'd do it differently.
>
> =E2=80=8B
=E2=80=8BThe fact that it is standard is my point.  A brand new database pr=
oduct
today would likely choose to adhere to the standard and/or prevailing
convention on this topic instead of going it alone and requiring the
qualification.

David J.

Thanks again, it's very clear now

On Jul 15, 2016 7:57 AM, "Alvaro Herrera" <alvherre@2ndquadrant.com> wrote:

> David G. Johnston wrote:
> > On Thu, Jul 14, 2016 at 6:40 PM, Jason Turim <jason@signalvine.com>
> wrote:
> >
> > > I see, thanks.  Have you all considered making it an error to execute
> > > correlated queries without table qualifying the column names?
> > >
> > =E2=80=8BWill never happen.  I'm not even sure it would be desirable in=
 a
> > greenfield situation let alone in an established product.
>
> The problem is that this is defined by the SQL standard, so we're not at
> liberty to change it.  The opinion of several people is that it would be
> safer to require the qualification.  If this were a green field I'm sure
> we'd do it differently.
>
> --
> =C3=81lvaro Herrera                http://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>

David G. Johnston wrote:
> On Thu, Jul 14, 2016 at 6:57 PM, Alvaro Herrera <alvherre@2ndquadrant.com>
> wrote:
>
> > David G. Johnston wrote:
> > > On Thu, Jul 14, 2016 at 6:40 PM, Jason Turim <jason@signalvine.com>
> > wrote:
> > >
> > > > I see, thanks.  Have you all considered making it an error to execute
> > > > correlated queries without table qualifying the column names?
> > > >
> > > ​Will never happen.  I'm not even sure it would be desirable in a
> > > greenfield situation let alone in an established product.
> >
> > The problem is that this is defined by the SQL standard, so we're not at
> > liberty to change it.
>
> ​​We as a product are at liberty to make the change.​

Sure, if we were open to the option of going against the standard.

> > The opinion of several people is that it would be
> > safer to require the qualification.  If this were a green field I'm sure
> > we'd do it differently.
> >
> ​The fact that it is standard is my point.  A brand new database product
> today would likely choose to adhere to the standard and/or prevailing
> convention on this topic instead of going it alone and requiring the
> qualification.

I meant "if the standard was being written today they would probably
choose to do differently, seeing how the initial choice is so prone to
causing trouble."

--
Álvaro Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services