Sv: lost "left join"

På onsdag 16. januar 2019 kl. 11:54:21, skrev Олег Самойлов <splarv(at)ya(dot)ru
<mailto:splarv(at)ya(dot)ru>>:
Hi, all.

I got some mystic behaviour of PostgreSQL, perhaps this is a bug.
 
[snip]

But things begin be strange if I add validation by time.

=> explain select * from node as parent left join link on
parent.node_id=link.parent left join node as child on link.child=child.node_id
where parent.node_id=1 and current_date <@ parent.valid and current_date <@
link.valid and current_date <@ child.valid;
                                        QUERY PLAN

------------------------------------------------------------------------------------------
 Nested Loop  (cost=4.50..32.35 rows=1 width=112)
   ->  Nested Loop  (cost=4.35..21.88 rows=1 width=76)
         ->  Index Scan using node_pkey on node parent  (cost=0.15..8.18
rows=1 width=36)
               Index Cond: (node_id = 1)
               Filter: (CURRENT_DATE <@ valid)
         ->  Bitmap Heap Scan on link  (cost=4.20..13.70 rows=1 width=40)
               Recheck Cond: (parent = 1)
               Filter: (CURRENT_DATE <@ valid)
               ->  Bitmap Index Scan on link_pkey  (cost=0.00..4.20 rows=6
width=0)
                     Index Cond: (parent = 1)
   ->  Index Scan using node_pkey on node child  (cost=0.15..8.18 rows=1
width=36)
         Index Cond: (node_id = link.child)
         Filter: (CURRENT_DATE <@ valid)
(13 rows)

«Left Join»’s are lost. And in the result too:

=> select * from node as parent left join link on parent.node_id=link.parent
left join node as child on link.child=child.node_id where parent.node_id=1 and
current_date <@ parent.valid and current_date <@ link.valid and current_date <@
child.valid;
 node_id | valid | parent | child | valid | node_id | valid
---------+-------+--------+-------+-------+---------+-------
(0 rows)
 
The moment you involve columns on "left joined" relations this way in the
WHERE-clause, it effectively becomes a right join.
 
-- Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andreas(at)visena(dot)com <mailto:andreas(at)visena(dot)com>
www.visena.com <https://www.visena.com>
<https://www.visena.com>