På onsdag 16. januar 2019 kl. 11:54:21, skrev Олег Самойлов <splarv@xxxxx>:
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
