Re: Confusing results with lateral references
| От | Ashutosh Bapat |
|---|---|
| Тема | Re: Confusing results with lateral references |
| Дата | |
| Msg-id | CAFjFpRe9cok4rnxuOUzpfUfaAqBZ_5U0BZ5RXSKPdXOp+F5ELg@mail.gmail.com обсуждение исходный текст |
| Ответ на | Confusing results with lateral references (Ashutosh Bapat <ashutosh.bapat@enterprisedb.com>) |
| Ответы |
Re: Confusing results with lateral references
(Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>)
Re: Confusing results with lateral references (Tom Lane <tgl@sss.pgh.pa.us>) |
| Список | pgsql-hackers |
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 2postgres=# 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
postgres=# select * from t1 left join t2 on (t1.val = t2.val) for update of t1;
postgres=# commit;
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)
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.bapat@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.Session 2 gives results of the query
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
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
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database Company
В списке pgsql-hackers по дате отправления: