Re: BUG #5974: UNION construct type cast gives poor error message

"Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov> writes:
> Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> The sticking point is just that in purely syntactic terms this is
>> action-at-a-distance, and so it's hard to square with the spec. I
>> think that our current reading (in which the '1' and '2' get
>> resolved as text) is actually closer to what the spec says.

> Would the approach you have in mind accept a query which is valid
> under the spec yet return different results? If not, we can
> legitimately call it an extension.

Well, the case that is bothering me is stuff like

(select '1' union select '1 ') union all select 2;

The first union produces 1 row if you resolve the constants as integers,
but 2 rows if you resolve as text, which I think is what the spec would
expect here. And since the second union has ALL, that makes a
difference to the final output. Now in this particular case we'd fail
with "UNION types text and integer cannot be matched" so you never get
as far as noticing what the runtime behavior is.

[ experiments a bit... ] You can show a difference in results with
this:

regression=# (select '1' union select '1 ') union all select '2'::bpchar;
?column?
----------
1
1
2
(3 rows)

This produces 3 rows because the UNION resolves as text, but what we're
discussing here would allow it to resolve as bpchar, which would have
different behavior:

regression=# (select '1' union select '1 '::bpchar) union all select '2'::bpchar;
?column?
----------
1
2
(2 rows)

It's debatable about how important this is, and one could also say that
the behavior of our bpchar is not entirely standards compliant in the
first place, so maybe this isn't a compelling example. But I'm worried
that there may be related cases where it's a bigger deal.

regards, tom lane