Document behaviour of failed sub queries
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
Re: Document behaviour of failed sub queries
On Mon, Jun 28, 2021 at 8:34 AM PG Doc comments form 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 David J.
Re: Document behaviour of failed sub queries
On Mon, Jun 28, 2021 at 8:40 AM David G. Johnston < david.g.johns...@gmail.com> wrote: > On Mon, Jun 28, 2021 at 8:34 AM PG Doc comments form < > nore...@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_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.
SETOF specification and default behavior (CREATE FUNCTION)
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/13/sql-createfunction.html Description: It seems like an oversight that the synopsis for CREATE FUNCTION doesn't actually show the modifier "SETOF" as a optional literal part of the syntax but only describes it as a modifier to the data type identifier in the "rettype" description. Additionally, its unclear whether there is any other way to specify that a function returns a set other than using RETURNS SETOF (which is otherwise an optional clause since you can use OUT arguments instead). Putting the two together, it is unclear whether writing only "RETURNS SETOF" is allowed when using OUT arguments. I'm pretty sure it is not due to the "its a modifier" clause in the description.
