Sv: lost "left join" - Mailing list pgsql-general

From Andreas Joseph Krogh
Subject Sv: lost "left join"
Date
Msg-id VisenaEmail.33.56c7bb359a8edd8f.16856543de4@tc7-visena
Whole thread Raw
In response to lost "left join"  (Олег Самойлов <splarv@ya.ru>)
List pgsql-general
På onsdag 16. januar 2019 kl. 11:54:21, skrev Олег Самойлов <splarv@ya.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
 
Attachment

pgsql-general by date:

Previous
From: Олег Самойлов
Date:
Subject: lost "left join"
Next
From: Andreas Kretschmer
Date:
Subject: Re: Can anyone please provide me list of customers using postgreSQL