Re: union bug
От | Ivan |
---|---|
Тема | Re: union bug |
Дата | |
Msg-id | 348120930.20051020114111@mail.ru обсуждение исходный текст |
Ответ на | Re: union bug (Tom Lane <tgl@sss.pgh.pa.us>) |
Список | pgsql-bugs |
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@mail.ru
В списке pgsql-bugs по дате отправления:
Предыдущее
От: Tom LaneДата:
Сообщение: Re: BUG #1976: steps to reproduce BUG #1438: Non UTF-8 client encoding problem