Thread

Invalid outer joins with subquery

Victor Wagner <vitus@ice.ru> — 2001-04-27T13:27:47Z

Your name               :
Your email address      :


System Configuration
---------------------
  Architecture (example: Intel Pentium)         : Intel Pentium

  Operating System (example: Linux 2.0.26 ELF)  : Solaris 8, Linux
                                           (various versions)

  PostgreSQL version (example: PostgreSQL-7.1):   PostgreSQL-7.1

  Compiler used (example:  gcc 2.8.0)           : various gcc


Please enter a FULL description of your problem:
------------------------------------------------

I've found that PostgreSQL 7.1 incorrectly handles outer joins
when second table is subquery, which returns constant as one of columns.

Here is an example SQL script which demonstartes the problem


create table foo (
  key_fld varchar(20),
  value_fld varchar(20));
insert into foo values ('a','a');
insert into foo values ('b','b');
insert into foo values ('c','c');
insert into foo values ('d','d');

create table bar (
   key_fld varchar(20),
   unused varchar(20));
insert into bar values ('a','true');
insert into bar values ('c','true');

create view baz as
  select key_fld, 'true' as flag from bar;

select value_fld,flag from
  foo left join (select key_fld, 'true' as flag from bar) a
  on foo.key_fld = a.key_fld
;

select value_fld,flag from
  foo left join (select key_fld, unused as flag from bar) a
 on foo.key_fld = a.key_fld;

select value_fld, flag from
  foo left join baz on
   foo.key_fld = baz.key_fld;


In my opinion, all three queries should return same result

 value_fld | flag
-----------+------
 a         | true
 b         |
 c         | true
 d         |

But both queries where constant is used in either subquery or view
definition, return

 value_fld | flag
-----------+------
 a         | true
 b         | true
 c         | true
 d         | true


In Oracle these queries, (rewirtten according to Oracle outer join syntax)
return same result.

-- 
Victor Wagner			vitus@ice.ru
Chief Technical Officer		Office:7-(095)-748-53-88
Communiware.Net 		Home: 7-(095)-135-46-61
http://www.communiware.net      http://www.ice.ru/~vitus