[BUGS] BUG #1677: Bug in select with Union

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

[BUGS] BUG #1677: Bug in select with Union

Julien Vallet

The following bug has been logged online:

Bug reference:      1677
Logged by:          Julien Vallet
Email address:      [hidden email]
PostgreSQL version: 8.0 & 8.0.3
Operating system:   Windows XP
Description:        Bug in select with Union
Details:

I think there is a problem with the select statement with Union.

Firstable, I create 3 tables (tuserprofileopp, tprofileopp and
tgroupprofileopp).

CREATE TABLE tuserprofileopp
(
  internalkey int8 NOT NULL,
  CONSTRAINT tuserprofileopp_pkey PRIMARY KEY (internalkey)
);

CREATE TABLE tprofileopp
(
  internalkey int8 NOT NULL,
  CONSTRAINT tprofileopp_pkey PRIMARY KEY (internalkey)
);

CREATE TABLE tgroupprofileopp
(
  internalkey int8 NOT NULL,
  manager_id int8,
  CONSTRAINT tgroupprofileopp_pkey PRIMARY KEY (internalkey)
);

Then, I write this request:
select *
from
(
select
internalKey, null as manager_id, 0 as clazz_
from TProfileOPP
union select
internalKey, null as manager_id, 1 as clazz_
from TUserProfileOPP
union select
internalKey, manager_id, 2 as clazz_
from TGroupProfileOPP
)
this_;

The result is "ERROR:  UNION types text and bigint cannot be matched"

If I do
"select *
from
(
select
internalKey, null as manager_id, 0 as clazz_
from TProfileOPP
union select
internalKey, manager_id, 2 as clazz_
from TGroupProfileOPP
)
this_;", It works.

Thank you.

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to [hidden email]
Reply | Threaded
Open this post in threaded view
|

Re: [BUGS] BUG #1677: Bug in select with Union

Tom Lane-2
"Julien Vallet" <[hidden email]> writes:
> The result is "ERROR:  UNION types text and bigint cannot be matched"

This is the identical issue reported here:
http://archives.postgresql.org/pgsql-bugs/2005-02/msg00008.php
See also this thread:
http://archives.postgresql.org/pgsql-general/2005-02/msg00693.php

The short answer is that what you wrote is illegal per the SQL spec
(which doesn't allow NULL to be used in ways where a type can't be
ascribed immediately).  While we'd like to accept it, we have
not figured out a rule that doesn't break the spec in other ways.
For now I'd recommend casting the NULLs explicitly.

                        regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings
Reply | Threaded
Open this post in threaded view
|

Re: [BUGS] BUG #1677: Bug in select with Union

Russell Smith
On Mon, 23 May 2005 11:54 pm, Tom Lane wrote:

> "Julien Vallet" <[hidden email]> writes:
> > The result is "ERROR:  UNION types text and bigint cannot be matched"
>
> This is the identical issue reported here:
> http://archives.postgresql.org/pgsql-bugs/2005-02/msg00008.php
> See also this thread:
> http://archives.postgresql.org/pgsql-general/2005-02/msg00693.php
>
> The short answer is that what you wrote is illegal per the SQL spec
> (which doesn't allow NULL to be used in ways where a type can't be
> ascribed immediately).  While we'd like to accept it, we have
> not figured out a rule that doesn't break the spec in other ways.
> For now I'd recommend casting the NULLs explicitly.

Can we spit out an error that is slightly more relevant?  Maybe print
a warning/error that NULL was used without a type?

Regards

Russell Smith
>    regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
>
>

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings