I'm not sure why bug #7926 didn't get any love when filed,
but the issue came up again today:
https://www.postgresql.org/message-id/264036359.6712710.1501784552013@mail.yahoo.com
and it does seem like this is pretty curious behavior.
A minimal reproducer is
regression=# create table base (f1 numeric(7,3));
CREATE TABLE
regression=# with recursive foo as (
select f1 from base
zunion all
select f1+1 from foo
) select * from foo;
ERROR: recursive query "foo" column 1 has type numeric(7,3) in non-recursive term but type numeric overall
LINE 2: select f1 from base ^
HINT: Cast the output of the non-recursive term to the correct type.
Now the thing about that is that the HINT's advice doesn't work:
regression=# with recursive foo as (
select f1::numeric from base
union all
select f1+1 from foo
) select * from foo;
ERROR: recursive query "foo" column 1 has type numeric(7,3) in non-recursive term but type numeric overall
LINE 2: select f1::numeric from base ^
HINT: Cast the output of the non-recursive term to the correct type.
The reason for this is that parse_coerce.c treats casting a value that's
already of the required type to typmod -1 as a complete no-op (see first
check in coerce_type_typmod). So the result is still just a Var for "f1".
We could imagine fixing this by insisting that a RelabelType with typmod
-1 should be plastered atop the expression in such cases. But I'm worried
about the potential side-effects of that, and anyway I'm not convinced
that parse_coerce.c is wrong to be doing it this way: typmod -1 generally
means "unspecified typmod", so the bare Var seems like it ought to be
considered to satisfy the typmod spec. Besdies, if you just do this:
select f1 from base
union all
select f1+1 from base;
it works, producing a UNION result deemed to have typmod -1, and there's
no extra decoration added to the Var in the first leaf SELECT.
In short, therefore, it's looking to me like analyzeCTE() is wrong here.
It should allow the case where the recursive result has typmod -1 while
the non-recursive output column has some more-specific typmod, so long
as they match on type OID. That would correspond to what we do in
regular non-recursive UNION situations.
regards, tom lane
