Re: Confusing results with lateral references

On Fri, Dec 4, 2015 at 10:58 AM, Amit Langote <Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp
> wrote:

> On 2015/12/03 21:26, Ashutosh Bapat wrote:
> > 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?
>
> Maybe it is. Because the other table still has two (1, 1) rows, LockRows's
> subplan would still produce two rows in result, no?
>
>
Documentation at
http://www.postgresql.org/docs/9.4/static/queries-table-expressions.html
says
(T1) LEFT OUTER JOIN (T2)

First, an inner join is performed. Then, *for each row in T1* that does not
satisfy the join condition with any row in T2, *a joined row is added* with
null values in columns of T2. Thus, the joined table always has at least
one row for each row in T1.
So there should be only one row for each row of outer table that didn't
join with the inner table. IOW a join with no joining rows should have same
number of rows as outer table.

> Thanks,
> Amit
>
>
>

--
Best Wishes,
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database Company