Re: subquery column error causes data destroying equality

Cy <frompostgres(at)cy1(dot)allowed(dot)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