Обсуждение: BUG #1677: Bug in select with Union
The following bug has been logged online: Bug reference: 1677 Logged by: Julien Vallet Email address: julien.vallet@density-tech.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.
"Julien Vallet" <julien.vallet@density-tech.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
On Mon, 23 May 2005 11:54 pm, Tom Lane wrote: > "Julien Vallet" <julien.vallet@density-tech.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 > >