CTE patch versus UNION type determination rules

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Tatsuo Ishii <t-ishii(at)sra(dot)co(dot)jp>
Cc: pgsql-hackers(at)postgreSQL(dot)org
Subject: CTE patch versus UNION type determination rules
Date: 2008-09-29 16:06:43
Message-ID: 13103.1222704403@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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 referencing q ...
);

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 referencing q ...
);

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

Browse pgsql-hackers by date

  From Date Subject
Next Message David E. Wheeler 2008-09-29 16:08:09 Re: Ad-hoc table type?
Previous Message Simon Riggs 2008-09-29 15:54:55 Re: [PATCHES] Infrastructure changes for recovery