Thread: subquery column error causes data destroying equality

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA256

When updating my table, my query was this:

update comic.panels set medium = x'2a958'::int where page = (
select page from comic.pages where comic = x'1db'::int and which = 0);

Unfortunately, I forgot that the column in the "pages" table was named "id" not "page".
This would have been the correct query:

update comic.panels set medium = x'2a958'::int where page = (
select id from comic.pages where comic = x'1db'::int and which = 0);

By itself, the bogus select causes an error:

select page from pages where comic = x'1db'::int and which = 0;
=>
ERROR:  column "page" does not exist

When used in a subquery, the bogus select does not cause any error, and acts as a value
that is always equal to every other value. So "page = (SELECT page FROM pages WHERE ...)"
is always true for every value of "page", and now all 12,972 panels from every one of my
meticulously collected comics are nothing but image 2a958.

So... please have errors in subqueries cause the surrounding query to fail. That happens
for syntax errors, but for erroneous column errors it just silently updates all records in
the table to the same value. And... having a transactionless savepoint would be nice, so
I can test the database update in my other processes, then undo the catastrophic failure.

I'm using version 13devel, git commit 9f87ae38eaffcc7f72c45bfeb79e09dd6e8c2f48 so if the
bug has been fixed since then, feel free to ignore me.
-----BEGIN PGP SIGNATURE-----

iHQEAREIAB0WIQQ+4EYCR2lOGPl0mIHk9gahCsfaVgUCXxultgAKCRDk9gahCsfa
VjUjAP9l2HkceA2M/0z3qxjISRzDQBDgbtnyPx2LlydC6zJo3wD4tnar2sqXWxgT
d47a5XtzV9Orud7edYxDhHoJYFWMdA==
=q+wb
-----END PGP SIGNATURE-----

Cy <frompostgres@cy1.allowed.org> writes:
> When updating my table, my query was this:

> update comic.panels set medium = x'2a958'::int where page = (
> select page from comic.pages where comic = x'1db'::int and which = 0);

> Unfortunately, I forgot that the column in the "pages" table was named "id" not "page".
> This would have been the correct query:

> update comic.panels set medium = x'2a958'::int where page = (
> select id from comic.pages where comic = x'1db'::int and which = 0);

Yup.  This is an absolutely standard gotcha.  Unfortunately, that's
"standard" as in "SQL standard": the spec defines that reference to
"page" as being a valid outer reference.

> So... please have errors in subqueries cause the surrounding query to fail.

This is not an error.  Removing the ability to have outer references
in subqueries would be contrary to spec and would greatly weaken this
language feature.

The usual recommendation to avoid such mistakes is to table-qualify
every column reference in a subquery.

            regards, tom lane