From: Tom Lane <tgl@sss.pgh.pa.us>
To: jiangshan.liu@tju.edu.cn
Cc: pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #17859: Suspected collation conflict when using recursive query
Date: Tue, 21 Mar 2023 10:17:53 -0400
Message-ID: <1215829.1679408273@sss.pgh.pa.us> (raw)
In-Reply-To: <17859-c530b7716e786d04@postgresql.org>
References: <17859-c530b7716e786d04@postgresql.org>
PG Bug reporting form <noreply@postgresql.org> writes:
> I encountered an error while executing a recursive query in PostgreSQL and
> would like to report it. The query is as follows:
> WITH RECURSIVE run(n, t) AS (
> SELECT 1, ''::text
> UNION ALL
> SELECT n + 1, (SELECT (select tableowner FROM pg_tables LIMIT 1)::text)
> FROM run
> WHERE n < 5
> )
> SELECT * FROM run;
> The error message is
>> ERROR: recursive query "run" column 2 has collation "default" in
> non-recursive term but collation "C" overall
> LINE 2: SELECT 1, ''::text
> ^
> HINT: Use the COLLATE clause to set the collation of the non-recursive
> term.
This is not a bug, and the HINT is telling you what to do to fix it:
SELECT 1, ''::text COLLATE "C"
UNION ALL
...
> I would like to inquire if this error is caused by a bug in the recursive
> query implementation. According to the PostgreSQL documentation, the
> collation of the result should be the non-default collation if any
> non-default collation is present[1].
You are interpreting "present" far too broadly. The type and
collation of the result are deduced from the non-recursive term, and
then we parse the recursive term *using that information* to assign
type info to the recursive references. Then the resolved output type
details of the UNION have to match what we assumed, or we throw this
error. Any other procedure would be circular logic, since the
conclusion about what's the output type of the recursive term might
well depend on what we assumed about the recursive references.
> Furthermore, I noticed that the same behavior works in a non-recursive
> query:
Non-recursive cases don't require making assumptions about the types
of recursive references.
regards, tom lane
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: tgl@sss.pgh.pa.us, jiangshan.liu@tju.edu.cn, pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #17859: Suspected collation conflict when using recursive query
In-Reply-To: <1215829.1679408273@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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