>>>>> "Олег" == Олег Самойлов <splarv@xxxxx> writes: Олег> Hi, all. Олег> I got some mystic behaviour of PostgreSQL, perhaps this is a bug. Feature, actually. Олег> 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; The problem here is that (for example) child.valid is null if there was no matching child row in the join, and the planner knows that x <@ NULL is not true (since the chosen <@ operator is defined as strict), and therefore it knows that the left join is unnecessary and can be reduced to an inner join. At least 90% of the time when you refer to values from the nullable side of a join in a WHERE clause, you're making a mistake (e.g. the condition should have been in the ON clause instead). The other 10% or less of the time, you have to make sure you use non-strict conditions, i.e. take account of the fact that the values might be null. -- Andrew (irc:RhodiumToad)
