Re: [BUGS] Small bug in union

>
> The basic problem is that PostgreSQL doesn't understand that Null match any
> datatype.
>
> Please describe a way to repeat the problem. Please try to provide a
> concise reproducible example, if at all possible:
> ----------------------------------------------------------------------
>
> If you have two table created this way:
>
> create table test00
> (
> posizione int4 not null primary key,
> testo varchar(50),
> campo float8,
> dataeora datetime
> );
>
> create table test01
> (
> posizione int4 not null primary key,
> testo varchar(50),
> campo float8,
> dataeora datetime
> );
>
> and you try to implement an outer join (not yet supported) using the union
> clause this way:
>
> SELECT
> test00.posizione,
> test01.posizione
> FROM
> test00,
> test01
> WHERE
> test00.posizione = test01.posizione
> UNION
> SELECT
> test00.posizione,
> Null
> FROM test00
> WHERE
> NOT EXISTS (SELECT * FROM test01 WHERE test01.posizione = test00.posizione);
>
> postgres reports the following error:
>
> ERROR: Each UNION query must have identical target types.
>
> If you replace Null with an integer everything works well, so the datatype
> mismatch is detected on the Null.
>
>
> If you know how this problem might be fixed, list the solution below:
> ---------------------------------------------------------------------
>
> The problem is in src/backend/parser/parse_clause.c in function:
>
> List * transformUnionClause(List *unionClause, List *targetlist)
>
> Near the end there's a check on data types that looks like:
>
> if (((TargetEntry *)lfirst(prev_target))->resdom->restype !=
> ((TargetEntry *)lfirst(next_target))->resdom->restype)
> elog(ERROR,"Each UNION query must have identical target types.");
>
> this check should be performed only when both entry are not a Null costant,
> else it should be ignored because Null should match any datatype. I don't
> know how PostgreSQL handles Null internally else I had changed the code
> myself. Anyway I'm sure you PostgreSQL gurus will know how to do it in few
> seconds.
>
> Hope it helps !
>
> P.S. My compliments to all the development staff. Just few more
> enhancements (outer join support, slightly better optimizer and few things
> more) and PostgreSQL will compare to (and sometimes beat) most commercial
> high quality DBMS.
>
> Dr. Sbragion Denis
> InfoTecna
> Tel, Fax: +39 39 2324054
> URL: http://space.tin.it/internet/dsbragio
>
>

Thomas, we now get:

select usesysid from pg_user union select null ;
ERROR: type id lookup of 0 failed

which not good either. Can you address this issue?

--
Bruce Momjian | 830 Blythe Avenue
maillist(at)candle(dot)pha(dot)pa(dot)us | Drexel Hill, Pennsylvania 19026
+ If your life is a hard drive, | (610) 353-9879(w)
+ Christ can be your backup. | (610) 853-3000(h)