Thread: Why is a union of two null-results automatically casted to type text ?

Hi all,

Boiling down a problem in one of my queries, I noticed this behaviour.


# select version();
                                version
------------------------------------------------------------------------
 PostgreSQL 7.4.2 on i586-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2.3
(1 row)


# select 1 union all select '2';
 ?column?
----------
        1
        2
(2 rows)

# select 1 union select null;
 ?column?
----------
        1

(2 rows)

# select 1 union select * from (select null union select null) as foo;
ERROR:  UNION types integer and text cannot be matched


I guess the last one fails because the second union of two unknown(?) types
gets casted to text, which in turn cannot be processed by the UNION while the
left part is of type integer.

I'm wondering about the reason this cast to text takes place, is this simply
because SQL specs say so?





--
Best,




Frank.

Frank van Vugt <ftm.van.vugt@foxi.nl> writes:
> # select 1 union select * from (select null union select null) as foo;
> ERROR:  UNION types integer and text cannot be matched

> I'm wondering about the reason this cast to text takes place,

UNION requires assignment of a definite type to the inputs, because
otherwise there's no certainty that we know how to identify distinct
and non-distinct values.  The alternative to assigning TEXT is to
reject the inner UNION outright :-(

            regards, tom lane

> > I'm wondering about the reason this cast to text takes place,
>
> UNION requires assignment of a definite type to the inputs, because
> otherwise there's no certainty that we know how to identify distinct
> and non-distinct values.  The alternative to assigning TEXT is to
> reject the inner UNION outright :-(

Ah, thanks Tom.

No, I think I'll go for the current implementation instead ;)

But in a UNION ALL the distinctiveness isn't an issue, is it?

So why is this failing as well:
    select 1 union select * from (select null union all select null) as foo;

I strolled through chapters 8 and 10 of the docs ('data types' and 'type
conversion') earlier, is there some additional source of information that
describes the way PostgreSQL handles typing, specifically things like what
you're describing here? Other than the source that is...





--
Best,




Frank.

Frank van Vugt <ftm.van.vugt@foxi.nl> writes:
>> UNION requires assignment of a definite type to the inputs, because
>> otherwise there's no certainty that we know how to identify distinct
>> and non-distinct values.  The alternative to assigning TEXT is to
>> reject the inner UNION outright :-(

> But in a UNION ALL the distinctiveness isn't an issue, is it?

True.  We do not currently distinguish UNION from UNION ALL as far as
datatype assignment rules go (INTERSECT/EXCEPT also act just the same).
In theory we could allow an output column of UNION ALL to remain
"unknown".  I'm not sure if it'd be a good idea to do so or not.  It'd
make this particular example work the way you want, but otherwise it
seems like making UNION ALL a special case would be a bit of a wart on
the type system.

            regards, tom lane

> > But in a UNION ALL the distinctiveness isn't an issue, is it?

> True.  We do not currently distinguish UNION from UNION ALL as far as
> datatype assignment rules go

<cut>

> I'm not sure if it'd be a good idea to do so or not.  It'd
> make this particular example work the way you want, but otherwise it
> seems like making UNION ALL a special case would be a bit of a wart on
> the type system.

Well, in my case there's no situation where I don't know in advance where the
problem could occur, so it's easily avoided by proper typing of the first
null in the union all sequence.


Thanks for the explanation.






--
Best,




Frank.