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.


---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

Frank van Vugt <ftm.van.vugt@foxi.nl> writes:[color=blue]
> # select 1 union select * from (select null union select null) as foo;
> ERROR: UNION types integer and text cannot be matched[/color]
[color=blue]
> I'm wondering about the reason this cast to text takes place,[/color]

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

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Frank van Vugt <ftm.van.vugt@foxi.nl> writes:[color=blue]
> # select 1 union select * from (select null union select null) as foo;
> ERROR: UNION types integer and text cannot be matched[/color]
[color=blue]
> I'm wondering about the reason this cast to text takes place,[/color]

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

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

> > I'm wondering about the reason this cast to text takes place,[color=blue]
>
> 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 :-([/color]

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.


---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

> > I'm wondering about the reason this cast to text takes place,[color=blue]
>
> 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 :-([/color]

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.


---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Frank van Vugt <ftm.van.vugt@foxi.nl> writes:[color=blue][color=green]
>> 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 :-([/color][/color]
[color=blue]
> But in a UNION ALL the distinctiveness isn't an issue, is it?[/color]

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

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Frank van Vugt <ftm.van.vugt@foxi.nl> writes:[color=blue][color=green]
>> 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 :-([/color][/color]
[color=blue]
> But in a UNION ALL the distinctiveness isn't an issue, is it?[/color]

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

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

> > But in a UNION ALL the distinctiveness isn't an issue, is it?
[color=blue]
> True. We do not currently distinguish UNION from UNION ALL as far as
> datatype assignment rules go[/color]

<cut>
[color=blue]
> 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.[/color]

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.


---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

> > But in a UNION ALL the distinctiveness isn't an issue, is it?
[color=blue]
> True. We do not currently distinguish UNION from UNION ALL as far as
> datatype assignment rules go[/color]

<cut>
[color=blue]
> 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.[/color]

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.


---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html