Lists: | pgsql-bugs |
---|
From: | "Julien Vallet" <julien(dot)vallet(at)density-tech(dot)com> |
---|---|
To: | pgsql-bugs(at)postgresql(dot)org |
Subject: | BUG #1677: Bug in select with Union |
Date: | 2005-05-23 11:49:12 |
Message-ID: | 20050523114912.B34B0F0B06@svr2.postgresql.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-bugs |
The following bug has been logged online:
Bug reference: 1677
Logged by: Julien Vallet
Email address: julien(dot)vallet(at)density-tech(dot)com
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.
From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | "Julien Vallet" <julien(dot)vallet(at)density-tech(dot)com> |
Cc: | pgsql-bugs(at)postgresql(dot)org |
Subject: | Re: BUG #1677: Bug in select with Union |
Date: | 2005-05-23 13:54:38 |
Message-ID: | 7306.1116856478@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-bugs |
"Julien Vallet" <julien(dot)vallet(at)density-tech(dot)com> 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
From: | Russell Smith <mr-russ(at)pws(dot)com(dot)au> |
---|---|
To: | pgsql-bugs(at)postgresql(dot)org |
Subject: | Re: BUG #1677: Bug in select with Union |
Date: | 2005-05-24 08:56:22 |
Message-ID: | 200505241856.22936.mr-russ@pws.com.au |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-bugs |
On Mon, 23 May 2005 11:54 pm, Tom Lane wrote:
> "Julien Vallet" <julien(dot)vallet(at)density-tech(dot)com> 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
>
>