Thread: BUG #1847: Error in some kind of UNION query.

The following bug has been logged online:

Bug reference:      1847
Logged by:          THIBAULT Jean-Jacques
Email address:      jjt4@wanadoo.fr
PostgreSQL version: 8.0.3
Operating system:   Linux (Mandrake)
Description:        Error in some kind of UNION query.
Details:

this query work :
select null UNION 1;

this query doesn't work :
select null UNION null UNION 1;

ERROR:  UNION types text and integer cannot be matched

I use Hibernate API, that generates this kind of query in "one table for
each concret class" mapping strategy. I allways have real value in a union
member that justify this kind of query.

Thank-you.

Jean-Jacques THIBAULT (France)

On Thu, Aug 25, 2005 at 09:53:26AM +0100, THIBAULT Jean-Jacques wrote:
>
> this query work :
> select null UNION 1;

Please show the *exact* query.  The above produces a syntax error:

test=> select null UNION 1;
ERROR:  syntax error at or near "1" at character 19
LINE 1: select null UNION 1;
                          ^

I suspect this is the actual query:

test=> select null UNION select 1;
 ?column?
----------
        1

(2 rows)

> this query doesn't work :
> select null UNION null UNION 1;
>
> ERROR:  UNION types text and integer cannot be matched

Again, the query as written causes a syntax error.  This is probably
the actual query:

test=> select null UNION select null UNION select 1;
ERROR:  UNION types text and integer cannot be matched

To understand what's happening, see "UNION, CASE, and ARRAY Type
Resolution" in the "Type Conversion" chapter of the documentation:

http://www.postgresql.org/docs/8.0/static/typeconv-union-case.html

--
Michael Fuhr

THIBAULT Jean-Jacques wrote:
> The following bug has been logged online:
>
> Bug reference:      1847
> Logged by:          THIBAULT Jean-Jacques
> Email address:      jjt4@wanadoo.fr
> PostgreSQL version: 8.0.3
> Operating system:   Linux (Mandrake)
> Description:        Error in some kind of UNION query.
> Details:
>
> this query work :
> select null UNION 1;
>
> this query doesn't work :
> select null UNION null UNION 1;
>
> ERROR:  UNION types text and integer cannot be matched

You probably mean
SELECT NULL
UNION
SELECT NULL
UNION
SELECT 1

Proper casting will help, insuring the correct type can be determined
from the first SELECT. Changing the order to have SELECT 1 does the job too.

Regards,
Andreas

Andreas Pflug <pgadmin@pse-consulting.de> writes:
> You probably mean
> SELECT NULL
> UNION
> SELECT NULL
> UNION
> SELECT 1

This has come up a couple of times before.  It'd probably be possible to
resolve the type across all the union'd selects at once, using the same
rules as we do for CASE.  The thing that bothers me is that I think
doing so would violate the SQL spec.  The spec defines UNION as a
two-at-a-time operation; it is perfectly clear that the above must mean
    (SELECT NULL UNION SELECT NULL) UNION SELECT 1
and in turn that means that a CASE-like approach would cause the "SELECT
1" to change the semantics of the other parenthesized operation.  Which
is surely wrong.

For UNION ALL this might not matter a lot, because the end result
datatype would be the same anyway.  But for UNION it matters a whole
lot, because the assigned datatype determines what "equality" means
and therefore which rows are going to be eliminated as duplicates.
Here is an example:

regression=# (select '1.0' union select '1');
 ?column?
----------
 1
 1.0
(2 rows)

By default, the literals are resolved as type TEXT, and so they are
considered different.  But try this:

regression=# select '1.0' union (select '1' union select 1.0);
 ?column?
----------
      1.0
(1 row)

The unquoted 1.0 is numeric, and so that choice propagates to the
untyped literals, and we end up with just one row because 1 == 1.0.
This example proves that the parenthesization of a nest of UNIONs
does make a difference.

So the short answer is: I don't think we can change this without
breaking spec.  Better put some explicit casts on your nulls, eg

SELECT NULL::INTEGER UNION SELECT NULL::INTEGER UNION SELECT 1

(BTW, I'm fairly sure that "SELECT NULL" is illegal per the spec,
anyway.  You'd have to CAST it to some specific type to be valid SQL.)

            regards, tom lane