Re: BUG #11304: UNION query with NULL values fails

On Thu, 28 Aug 2014, m.woehling@barthauer.de wrote:

> The following bug has been logged on the website:
>
> Bug reference:      11304
> Logged by:          Markus Woehling
> Email address:      m.woehling@barthauer.de
> PostgreSQL version: 9.3.5
> Operating system:   Windows Server 2008
> Description:
>

select c1 :: integer from (select null as c1 union all select null as c1 union all select 1 :: text as c1) x;
  c1
----


   1
(3 rows)

select c1 :: integer +1 from (select null as c1 union all select null as c1 union all select 1 :: text as c1) x;
  ?column?
----------


         2
(3 rows)


(I hate NULL.  Just felt like I needed to say that.)

The above example was run on a 9.2.3 server.

> I want to "warm up" the bug reports "BUG #1453"
> [http://permalink.gmane.org/gmane.comp.db.postgresql.bugs/7383] and "BUG
> #5974"
> [http://www.postgresql.org/message-id/201104122018.p3CKIlWR042915@wwwmaster.postgresql.org].
>
> I need to build UNION queries with NULL values dynamically like in this
> sample:
> select null union all select null union all select 1;
>
> This could be workaround:
> select null union all (select null union all select 1);
>
> But this workaround fails in this case:
> select 1 union all (select null union all select null);
>
> Because I don't know the "real" data type of the NULL values, I can't
> specify the type like in this sample:
> select 1 union all (select null::int union all select null::int);
>
> I know that MSSQL, Oracle, MySQL, DB2 and Firebird don't have this problem.
> So I'd really like to see this working in PostgreSQL as well.
> Currently this problem hinders me from using PostgreSQL as DBMS in our
> products.
>
> Markus
>
>
> --
> Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs
>

-
Mike Porter
PGP Fingerprint: F4 AE E1 9F 67 F7 DA EA  2F D2 37 F3 99 ED D1 C2