Has anyone else run into an issue where a simple view that encapsulates two tables joined by left outer join and selection criteria in a where clause that only pertains to the left column is returning different results via the view as opposed to just running the SQL from the view?
I tried to reproduce what you described:
drop table t1,t2; create table t1 as select n, n as v1 from generate_series(0,6) n; create table t2 as select n, n as v2 from generate_series(3,9) n; create view v as select n, v1, v2 from t1 left outer join t2 using(n); select * from v where v1<5; select n, v1, v2 from t1 left outer join t2 using(n) where v1<5;
Both queries return:
n | v1 | v2 ---+----+---- 0 | 0 | 1 | 1 | 2 | 2 | 3 | 3 | 3 4 | 4 | 4 (5 rows)