There's another seemingly wrong result, not with lateral, but with FOR UPDATE.
postgres=# select * from t1; val | val2 -----+------ 1 | 1 (1 row) postgres=# select * from t2; val | val2 -----+------ 1 | 1 2 | 2 1 | 1 (3 rows) Session 1 postgres=# begin; BEGIN postgres=# update t1 set val = 2 where val2 = 1; UPDATE 1 Session 2 postgres=# select * from t1 left join t2 on (t1.val = t2.val) for update of t1; query waits Session 1 postgres=# commit; COMMIT Session 2 query returns two rows select * from t1 left join t2 on (t1.val = t2.val) for update of t1; val | val2 | val | val2 -----+------+-----+------ 2 | 1 | | 2 | 1 | | (2 rows) It's confusing to see two rows from left join result when the table really has only a single row. Is this behaviour expected? On Thu, Dec 3, 2015 at 3:49 PM, Ashutosh Bapat < ashutosh.ba...@enterprisedb.com> wrote: > Hi, > > I am seeing different results with two queries which AFAIU have same > semantics and hence are expected to give same results. > > postgres=# \d t1 > Table "public.t1" > Column | Type | Modifiers > --------+---------+----------- > val | integer | > val2 | integer | > > postgres=# \d t2 > Table "public.t2" > Column | Type | Modifiers > --------+---------+----------- > val | integer | > val2 | integer | > > There's no data in the table to start with. > > postgres=# insert into t1 values (1, 1); > postgres=# insert into t2 values (1, 1), (2, 2); > > Session 1 > postgres=# begin; > BEGIN > postgres=# update t1 set val = 2 where val2 = 1; > UPDATE 1 > > Session 2 > postgres=# select * from t1, (select distinct val, val2 from t2) t2 where > t1.val = t2.val for update of t1; > > query waits here because of FOR UPDATE clause > > Session 1 > postgres=# commit; > COMMIT > > Session 2 gives no rows > postgres=# select * from t1, (select distinct val, val2 from t2) t2 where > t1.val = t2.val for update of t1; > val | val2 | val | val2 > -----+------+-----+------ > (0 rows) > > > Reset values of t1 > postgres=# update t1 set val = 1 where val2 = 1; > UPDATE 1 > > Session 1 > postgres=# begin; > BEGIN > postgres=# update t1 set val = 2 where val2 = 1; > UPDATE 1 > > Session 2 > postgres=# select * from t1, lateral (select distinct val, val2 from t2 > where t2.val = t1.val) t2 for update of t1; > > query waits here > > Session 1 > postgres=# commit; > COMMIT > > Session 2 gives results of the query > postgres=# select * from t1, lateral (select distinct val, val2 from t2 > where t2.val = t1.val) t2 for update of t1; > val | val2 | val | val2 > -----+------+-----+------ > 2 | 1 | 1 | 1 > (1 row) > > AFAIU, both the queries > > select * from t1, (select distinct val, val2 from t2) t2 where t1.val = > t2.val for update of t1; > > AND > > select * from t1, lateral (select distinct val, val2 from t2 where t2.val > = t1.val) t2 for update of t1; > > have same semantic and should give same results. > > Is seeing different results expected behaviour? > -- > Best Wishes, > Ashutosh Bapat > EnterpriseDB Corporation > The Postgres Database Company > -- Best Wishes, Ashutosh Bapat EnterpriseDB Corporation The Postgres Database Company
