union bug

Hello,

PostgreSQL 8.1beta3 on i686-pc-mingw32, compiled by GCC gcc.exe (GCC) 3.4.2 (mingw-special)
Windows XP SP2

The following query

CREATE DOMAIN test_domain
AS varchar(64)
NOT NULL;

CREATE TYPE test_type AS
("Id" int4,
"Data" test_domain);

CREATE OR REPLACE FUNCTION union_test()
RETURNS SETOF test_type AS
$BODY$
select 1 as "Id", 'string1'::test_domain as "Data"
union all
select 2 as "Id", 'string2'::test_domain as "Data"
$BODY$
LANGUAGE 'sql' VOLATILE;

generates error message

ERROR: return type mismatch in function declared to return test_type
DETAIL: Final SELECT returns character varying instead of test_domain at column 2.
CONTEXT: SQL function "union_test"

but this one is not

CREATE OR REPLACE FUNCTION union_test2()
RETURNS SETOF test_type AS
$BODY$
select "Id"::int4, "Data"::test_domain
from (
select 1 as "Id", 'string1' as "Data"
union all
select 2 as "Id", 'string2' as "Data"
) as q1;
$BODY$
LANGUAGE 'sql' VOLATILE;

--
Best regards,
Ivan mailto:Ivan-Sun1(at)mail(dot)ru

Ivan <Ivan-Sun1(at)mail(dot)ru> writes:
> CREATE DOMAIN test_domain
> AS varchar(64)
> NOT NULL;

> CREATE TYPE test_type AS
> ("Id" int4,
> "Data" test_domain);

> CREATE OR REPLACE FUNCTION union_test()
> RETURNS SETOF test_type AS
> $BODY$
> select 1 as "Id", 'string1'::test_domain as "Data"
> union all
> select 2 as "Id", 'string2'::test_domain as "Data"
> $BODY$
> LANGUAGE 'sql' VOLATILE;

> generates error message

> ERROR: return type mismatch in function declared to return test_type
> DETAIL: Final SELECT returns character varying instead of test_domain at column 2.
> CONTEXT: SQL function "union_test"

The reason this happens is that select_common_type() smashes all its
inputs down to base types. I'm a bit hesitant to change this behavior
without thinking about all the possible consequences. There are clearly
some cases where it's the right thing --- for instance, if the inputs
are two different domains over the same base type, selecting the base
type seems the most reasonable behavior. Also, at least some of the
routine's callers seem to be relying on the assumption that the result
won't be a domain type.

regards, tom lane

Hello,

>> CREATE DOMAIN test_domain
>> AS varchar(64)
>> NOT NULL;

>> CREATE TYPE test_type AS
>> ("Id" int4,
>> "Data" test_domain);

>> CREATE OR REPLACE FUNCTION union_test()
>> RETURNS SETOF test_type AS
>> $BODY$
>> select 1 as "Id", 'string1'::test_domain as "Data"
>> union all
>> select 2 as "Id", 'string2'::test_domain as "Data"
>> $BODY$
>> LANGUAGE 'sql' VOLATILE;

>> generates error message

>> ERROR: return type mismatch in function declared to return test_type
>> DETAIL: Final SELECT returns character varying instead of test_domain at column 2.
>> CONTEXT: SQL function "union_test"

TL> The reason this happens is that select_common_type() smashes all its
TL> inputs down to base types. I'm a bit hesitant to change this behavior
TL> without thinking about all the possible consequences. There are clearly
TL> some cases where it's the right thing --- for instance, if the inputs
TL> are two different domains over the same base type, selecting the base
TL> type seems the most reasonable behavior. Also, at least some of the
TL> routine's callers seem to be relying on the assumption that the result
TL> won't be a domain type.

I'd like to offer following solution:

for given column of the union check if the types of all parts ot the
union for that column are !exactly! the same, then resulting column
type of the union is left to that type, otherwise it casts to the base
type.

In this case users can explicitly cast column types of union parts to
whatever they want to get that type in the result, i.e.

CREATE DOMAIN test_domain
AS varchar(64)
NOT NULL;

CREATE DOMAIN test_domain2
AS varchar(64)
NOT NULL
CHECK (length(trim(value)) > 0);

CREATE TYPE test_type AS
("Id" int4,
"Data" test_domain);

CREATE OR REPLACE FUNCTION union_test()
RETURNS SETOF test_type AS
$BODY$
select 1 as "Id", 'string1'::test_domain as "Data"
union all
select 2 as "Id", ('string2'::test_domain2)::test_domain as "Data"
$BODY$
LANGUAGE 'sql' VOLATILE;

If i understand correctly current workaround is to use outer select
with type cast (as i note in previous message). But as i see it takes
extra processing (as query plans below shows)

select "Id", "Data"::test_domain from
(
select 1 as "Id", 'string1'::test_domain as "Data"
union all
select 2 as "Id", ('string2'::test_domain2)::test_domain as "Data"
) as q1;

Subquery Scan q1 (cost=0.00..0.07 rows=2 width=36)
-> Append (cost=0.00..0.04 rows=2 width=0)
-> Subquery Scan "*SELECT* 1" (cost=0.00..0.02 rows=1 width=0)
-> Result (cost=0.00..0.01 rows=1 width=0)
-> Subquery Scan "*SELECT* 2" (cost=0.00..0.02 rows=1 width=0)
-> Result (cost=0.00..0.01 rows=1 width=0)

select 1 as "Id", 'string1'::test_domain as "Data"
union all
select 2 as "Id", ('string2'::test_domain2)::test_domain as "Data"

Append (cost=0.00..0.04 rows=2 width=0)
-> Subquery Scan "*SELECT* 1" (cost=0.00..0.02 rows=1 width=0)
-> Result (cost=0.00..0.01 rows=1 width=0)
-> Subquery Scan "*SELECT* 2" (cost=0.00..0.02 rows=1 width=0)
-> Result (cost=0.00..0.01 rows=1 width=0)


Thank you for support.
--
Best regards,
Ivan mailto:Ivan-Sun1(at)mail(dot)ru