Re: Document behaviour of failed sub queries

On Mon, Jun 28, 2021 at 8:40 AM David G. Johnston <
david(dot)g(dot)johnston(at)gmail(dot)com> wrote:

> On Mon, Jun 28, 2021 at 8:34 AM PG Doc comments form <
> noreply(at)postgresql(dot)org> wrote:
>
>> For example:
>> UPDATE table1 SET status='expired' WHERE id in (SELECT wrong_id IN table2)
>>
>> This will update every row in table1if wrong_id doesn't exist, ignoring
>> the
>> ERROR: column "wrong_id" does not exist from the subquery.
>>
>
> The subquery never provokes that error by virtue of the fact it is a
> subquery. It's only if you run that as a standalone query do you see the
> error. This is because correlated subqueries are a thing (and, yes, they
> are documented).
>
>
> https://wiki.postgresql.org/wiki/FAQ#Why_doesn.27t_PostgreSQL_report_a_column_not_found_error_when_using_the_wrong_name_in_a_subquery.3F
>
>
I may have mis-read your email...the behavior I describe is usually what
prompts these kinds of questions but your example doesn't actually fit the
pattern. I find it hard to believe that what you describe is really
happening...though usually with IN clauses the presence of NULLs can
confound things.

You should put together a self-contained reproducer script and post it as a
bug report once you've confirmed it produces the problem you describe while
using psql and a current version of PostgreSQL.

David J.