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

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:

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

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

Mike Porter wrote
> On Thu, 28 Aug 2014,

> m.woehling@

>  wrote:
>
>> The following bug has been logged on the website:
>>
>> Bug reference:      11304
>> Logged by:          Markus Woehling
>> Email address:

> m.woehling@

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

Do you have a conclusion/observation to make?

NULL + NOT-NULL => NULL

Furthermore, since null is valid input for any type, explicit casting
between types, if such a cast exists, will always succeed and will always
result in null.

I agree there is potential for compatibility improvement here but I don't
see where your observation fits in.

David J.





--
View this message in context:
http://postgresql.1045698.n5.nabble.com/BUG-11304-UNION-query-with-NULL-values-fails-tp5816830p5816904.html
Sent from the PostgreSQL - bugs mailing list archive at Nabble.com.

I want to clarify what's my problem with the "untyped" NULL values.

I have to build SQL queries dynamically. Basically it's something like merging different queries (or query parts) into
abigger query. 
I don't know the data types of the "source" columns und I have to add NULL columns to the resulting query.
This combination is causing the trouble, and for me there's no way to work around this issue in another way.

Hope that helps.
Markus