Re: BUG #17859: Suspected collation conflict when using recursive query

PG Bug reporting form <noreply(at)postgresql(dot)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