From: | Andrew Gierth <andrew(at)tao11(dot)riddles(dot)org(dot)uk> |
---|---|
To: | Олег Самойлов <splarv(at)ya(dot)ru> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: lost "left join" |
Date: | 2019-01-16 12:24:37 |
Message-ID: | 87h8e8ls2y.fsf@news-spur.riddles.org.uk |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
>>>>> "Олег" == Олег Самойлов <splarv(at)ya(dot)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)
From | Date | Subject | |
---|---|---|---|
Next Message | Guillaume Lelarge | 2019-01-16 12:51:27 | Weird behaviour of ROLLUP/GROUPING |
Previous Message | Andreas Kretschmer | 2019-01-16 12:00:00 | Re: Can anyone please provide me list of customers using postgreSQL |