>>>>> "Олег" == Олег Самойлов <splarv@ya.ru> 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)
