Invalid outer joins with subquery

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(at)ice(dot)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