> 
> 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)

Mon, 15 Jun 1998 22:58:27 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [BUGS] Small bug in union