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

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)

--

Andreas Joseph Krogh

CTO / Partner - Visena AS

Mobile: +47 909 56 963

andreas@visena.com

www.visena.com