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%20present%2C%20that%20is%20the%20result%20of%20the%20collation%20combination
[2]
https://www.postgresql.org/docs/15/collation.html#:~:text=When%20the%20database%20system%20has%20to%20perform%20an%20ordering%20or%20a%20character%20classification%2C%20it%20uses%20the%20collation%20of%20the%20input%20expression