| From: | Ashutosh Bapat <ashutosh(dot)bapat(at)enterprisedb(dot)com> | 
|---|---|
| To: | pgsql-hackers <pgsql-hackers(at)postgresql(dot)org> | 
| Subject: | Confusing results with lateral references | 
| Date: | 2015-12-03 10:19:54 | 
| Message-ID: | CAFjFpRdexAuu6aX=PtY-K-nT2RjmjFJ0PjGQHJOZXzWbABWLnA@mail.gmail.com | 
| Views: | Whole Thread | Raw Message | Download mbox | Resend email | 
| Thread: | |
| Lists: | pgsql-hackers | 
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
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Daniel Verite | 2015-12-03 10:48:26 | Re: psql: add \pset true/false | 
| Previous Message | Kyotaro HORIGUCHI | 2015-12-03 10:05:19 | Re: [PROPOSAL] VACUUM Progress Checker. |