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

[Frank van Vugt]

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 ma*******@postg resql.org

[Tom Lane]

Frank van Vugt <ft**********@f oxi.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

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

[Tom Lane]

Frank van Vugt <ft**********@f oxi.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

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

[Frank van Vugt]

> > 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.
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

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

[Frank van Vugt]

> > 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.
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

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

[Tom Lane]

Frank van Vugt <ft**********@f oxi.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

---------------------------(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

[Tom Lane]

Frank van Vugt <ft**********@f oxi.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

---------------------------(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]

> > 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.
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

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

[Frank van Vugt]

> > 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.
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

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