Re: [BUGS] Small bug in union - Mailing list pgsql-hackers

>
> 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@candle.pha.pa.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)