From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: jiangshan.liu@tju.edu.cn
Subject: BUG #17859: Suspected collation conflict when using recursive query
Date: Tue, 21 Mar 2023 09:16:56 +0000
Message-ID: <17859-c530b7716e786d04@postgresql.org> (raw)
The following bug has been logged on the website:
Bug reference: 17859
Logged by: Jiangshan Liu
Email address: jiangshan.liu@tju.edu.cn
PostgreSQL version: 15.2
Operating system: Ubuntu 18.04.6 LTS
Description:
Dear PostgreSQL developers,
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.
In fact, the collation of column "tableowner" in table "pg_tables" is "C".
SELECT column_name, collation_name
FROM information_schema.columns
WHERE table_name = 'pg_tables' AND column_name = 'tableowner';
column_name | collation_name
-------------+----------------
tableowner | C
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]. In this case, since the collation of
column 2 is "C", the collation of the result should also be "C". However,
the error suggests otherwise.
Furthermore, I noticed that the same behavior works in a non-recursive
query:
SELECT 1, ''::text
UNION ALL
SELECT 2, (SELECT (select tableowner FROM pg_tables LIMIT 1)::text);
The documentation mentions that the collation of the input expression is
used when the database system has to perform an ordering or a character
classification[2]. However, the recursive query does not use any additional
ordering or character classification on the second column, and I couldn't
find any relevant information in the documentation.
I appreciate your attention to this matter and apologize if I made any
mistakes in my analysis. Thank you for your time and effort in maintaining
PostgreSQL.
Best regards, Jiangshan Liu
[1]
https://www.postgresql.org/docs/15/collation.html#:~:text=If%20any%20non%2Ddefault%20collation%20is%...
[2]
https://www.postgresql.org/docs/15/collation.html#:~:text=When%20the%20database%20system%20has%20to%...
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: noreply@postgresql.org, pgsql-bugs@lists.postgresql.org, jiangshan.liu@tju.edu.cn
Subject: Re: BUG #17859: Suspected collation conflict when using recursive query
In-Reply-To: <17859-c530b7716e786d04@postgresql.org>
* 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