Thread: UNION Types Mismatch

Thank you very much for the good product!

v7.3.2.

CREATE TABLE ta (c1 CHAR(1));
CREATE TABLE tb (c1 CHAR(1));
CREATE TABLE tc (c1 SMALLINT);

INSERT INTO ta VALUES('a');
INSERT INTO tb VALUES('b');
INSERT INTO tc VALUES(1);

SELECT c1 AS a,NULL AS b,NULL AS c FROM ta
UNION ALL
SELECT NULL,c1,NULL FROM tb
UNION ALL
SELECT NULL,NULL,c1 FROM tc;

ERROR:  UNION types 'text' and 'smallint' not matched

One union is fine:

SELECT c1 AS a,NULL AS c FROM ta
UNION ALL
SELECT NULL,c1 FROM tc;

It's very difficult for me to cast types because this SQL is dynamically
generated by scripts rather than hard coded:

SELECT c1 AS a,NULL AS b,NULL AS c FROM ta
UNION ALL
SELECT NULL,c1,NULL::SMALLINT FROM tb
UNION ALL
SELECT NULL,NULL,c1 FROM tc;

Can postgreSQL be improved to be smarter with such UNION?

CN

--
http://www.fastmail.fm - A no graphics, no pop-ups email service

"CN" <cnliou9@fastmail.fm> writes:
> SELECT c1 AS a,NULL AS b,NULL AS c FROM ta
> UNION ALL
> SELECT NULL,c1,NULL FROM tb
> UNION ALL
> SELECT NULL,NULL,c1 FROM tc;

> ERROR:  UNION types 'text' and 'smallint' not matched

Yeah.  The problem basically is that you have
    (null UNION null) UNION smallint
and the inner union type gets resolved to text by default.

I have a TODO note about this that questions whether we can improve
the behavior without violating spec.  Consider
    (numeric UNION numeric) UNION float8
Presently this will be performed as a numeric UNION, then cast the
output to float8 and do a float8 UNION.  The datatype is a critical
part of the semantics because it determines what will be considered
duplicate values.  If we chose a common datatype across all three
union arms to start with, it'd be float8, and we'd possibly get a
different result from the query.

It may be that the spec will let us get away with doing it anyway,
but some close study is needed.

            regards, tom lane