"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
> Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Consider
>>
>> select '1' union select '1 ';
>>
>> How many rows does that produce? You cannot answer without
>> imputing a data type to the columns. "text" will give a different
>> answer than "integer" or "bpchar".
> Well, if we were to assign both to type unknown initially, we would
> clearly need to resolve that before execution, But I'm not
> expecting that such execution would happen before analyzing the rest
> of the query. If the above is on the left side of a union with
> select 1;
> the unknown could then be resolved to integer. I expect that all of
> this should happen before any of the unions is *executed*. Perhaps
> I'm arguing for the same thing you were, but just have my head
> tilted at a different angle?
Yes, I think you are saying the exact same thing I am, just phrased
differently: you wish that in
(select '1' union select '2') union select 3
the fact that the third value is clearly integer would influence
the choice of the resolved type of the first UNION. My vision of
how to implement that is different than what you seem to have in
mind, but it would come out with the same answer. 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.
For those following along at home, there is another issue involved
here, which is our choice to treat string-literal constants the same
as NULL constants --- they're both UNKNOWN so far as the type resolution
rules go. It's not that surprising, perhaps, that (select '1' union
select '2') is resolved as text, but newbies tend to not think that
NULL ought to act like that. However, so far as I can see the spec
simply disallows a not-explicitly-cast NULL constant in cases like
this, which seems if anything even less friendly than what we're doing.
regards, tom lane