Currently, the CTE patch assumes (without checking) that the output
rowtype of a recursive WITH item is the same as the output rowtype
determined by inspecting its non-recursive term. Unfortunately
this is not always the case. Consider
WITH RECURSIVE q AS ( SELECT int4_col FROM sometable UNION ALL SELECT int8_col FROM ... something referencing
q...);
The output of this UNION will in fact be int8. However I see no way to
determine that without performing parse analysis of the recursive term,
and we can't do that without having assigned an output rowtype for q
(else we have no idea what to do with the recursive reference to q).
So it seems like we have to throw an error for this, and insist that
the user explicitly do
WITH RECURSIVE q AS ( SELECT int4_col::int8 FROM sometable UNION ALL SELECT int8_col FROM ... something
referencingq ...);
Can anyone see a way around that?
I'm inclined to go a bit further and have the code assume that the
output typmods are all -1, even if some more-specific typmod can be
determined from the non-recursive term. Otherwise you'd need to
explicitly cast in situations like
WITH RECURSIVE q AS ( SELECT varchar_10_col FROM sometable UNION ALL SELECT varchar_12_col FROM ... something
referencingq ...);
On the other hand this rule would lose typmod information even in cases
where both UNION arms emit the same typmod, so maybe it's debatable.
Comments?
Another point is that the patch assumes that the non-recursive term
must be the left child of the topmost UNION operator. In SQL2008,
7.13 syntax rule 2.g.i.3 appears to allow either child to be the
non-recursive term. However, rule 2.g.v.3 seems to say that a recursive
query is considered "expandable" only if the left child is the
non-recursive term, and in any case it'd be a bit odd to write a
recursive query the other way; it seems more likely to be a mistake
than intentional. Is everybody happy with making this restriction?
If we don't make it, then we also have some issues with the output
column names of the UNION not necessarily being what we derive from
inspecting just the non-recursive term.
regards, tom lane
