pg.ddx.io pgsql-docs@postgresql.org mailing list archive
help / color / mirror / Atom feedDocument behaviour of failed sub queries
3+ messages / 2 participants
[nested] [flat]
* Document behaviour of failed sub queries
@ 2021-06-28 13:55 PG Doc comments form <noreply@postgresql.org>
2021-06-28 15:40 ` Re: Document behaviour of failed sub queries David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: PG Doc comments form @ 2021-06-28 13:55 UTC (permalink / raw)
To: pgsql-docs@lists.postgresql.org; +Cc: jackbicknell@gmail.com
The following documentation comment has been logged on the website:
Page: https://www.postgresql.org/docs/13/functions-subquery.html
Description:
I have found that when a sub query fails, the preceeding IN will ignore the
failure and execute as if the subquery did not exist.
I think this should either be stated explicitly in the documentation or it
should fail the main the query.
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.
If you feel like the documentation explains this thoroughly enough already
then please point me in the direction. At the moment it makes refernece to
queries not completing but it doesn't mention failures.
Thanks
Jack
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Document behaviour of failed sub queries
2021-06-28 13:55 Document behaviour of failed sub queries PG Doc comments form <noreply@postgresql.org>
@ 2021-06-28 15:40 ` David G. Johnston <david.g.johnston@gmail.com>
2021-06-28 15:46 ` Re: Document behaviour of failed sub queries David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: David G. Johnston @ 2021-06-28 15:40 UTC (permalink / raw)
To: jackbicknell@gmail.com, Pg Docs <pgsql-docs@lists.postgresql.org>
On Mon, Jun 28, 2021 at 8:34 AM PG Doc comments form <noreply@postgresql.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_u...
David J.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Document behaviour of failed sub queries
2021-06-28 13:55 Document behaviour of failed sub queries PG Doc comments form <noreply@postgresql.org>
2021-06-28 15:40 ` Re: Document behaviour of failed sub queries David G. Johnston <david.g.johnston@gmail.com>
@ 2021-06-28 15:46 ` David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 0 replies; 3+ messages in thread
From: David G. Johnston @ 2021-06-28 15:46 UTC (permalink / raw)
To: jackbicknell@gmail.com, Pg Docs <pgsql-docs@lists.postgresql.org>
On Mon, Jun 28, 2021 at 8:40 AM David G. Johnston <
david.g.johnston@gmail.com> wrote:
> On Mon, Jun 28, 2021 at 8:34 AM PG Doc comments form <
> noreply@postgresql.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_u...
>
>
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.
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2021-06-28 15:46 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-06-28 13:55 Document behaviour of failed sub queries PG Doc comments form <noreply@postgresql.org>
2021-06-28 15:40 ` David G. Johnston <david.g.johnston@gmail.com>
2021-06-28 15:46 ` David G. Johnston <david.g.johnston@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox