public inbox for [email protected]  

help / color / mirror / Atom feed

From: Tobias Hoffmann <[email protected]>
To: David G. Johnston <[email protected]>
Cc: [email protected] <[email protected]>
Subject: Re: Non-trivial condition is only propagated to one side of JOIN
Date: Sun, 25 Aug 2024 19:04:32 +0200
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAKFQuwZ3jMCfy=cMS5BrzVR7M-ZN2N_2Ocgh+6Rj89f6=an-ZA@mail.gmail.com>
References: <[email protected]>
	<CAKFQuwZ3jMCfy=cMS5BrzVR7M-ZN2N_2Ocgh+6Rj89f6=an-ZA@mail.gmail.com>

On 25/08/2024 17:35, David G. Johnston wrote:
> On Sunday, August 25, 2024, Tobias Hoffmann 
> <[email protected]> wrote:
>
>
>     3) Problematic example:
>
>     # EXPLAIN SELECT * FROM tbl1 JOIN tbl2 ON tbl2.site_id =
>     tbl1.site_id WHERE tbl1.site_id = 1 OR tbl1.site_id IS NULL; 
>
>
> The “is null” predicate in this query is doing nothing as your next 
> comment alludes to; you will produce no rows out of the join with a 
> null site_id due to the use of the equals operator in the join.


Well, that's why I said: "keep in mind that this example is as 
simplified as possible"...
Even though `tbl1.site_id = 1` is – in this case – completely equivalent 
to `tbl1.site_id = 1 OR tbl1.site_id IS NULL` – the first one is 
completely pushed down, but the second is not.

A more complete example might look more like this:

CREATE VIEW "subview1" AS
   SELECT tbl1.site_id, ... JOIN ... ON tbl1.site_id = tbl2.site_id 
WHERE ...;

CREATE VIEW "view1" AS
   SELECT site_id, ... FROM subview1  -- maybe even: WHERE site_id IS 
NOT NULL
   UNION ALL
   SELECT null, ...;

SELECT * FROM view1 WHERE (site_id = 1 OR site_id IS NULL);

The reason, why the outer query would have a more complicated condition 
might have nothing to do with the subquery containing the JOIN.
(This is also not a `UNION ALL` special case: `site_id IS NULL` could 
also be generated by a LEFT JOIN, e.g.)

But not pushing down the condition has the grave impact, that those - 
otherwise working VIEWs (i.e. subview1) become "unfixably" broken, for 
certain WHERE-conditions on the outside.

Another reason why I said `site_id INTEGER NOT NULL` and `IS NOT 
DISTINCT FROM`, is that there might be some mathematical reason I'm not 
yet aware of where the propagation would not be sound, but which would 
not apply for arbitrary site_id [nullable, ...].
My primary goal is to find at least *some* way to get the condition 
pushed further in to avoid the full table scan, and to not have to 
completely rewrite all the VIEWs into a single big query, where I could 
inject the site_id parameter (e.g. "$1") in multiple places as needed...

   Tobias

view thread (5+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected]
  Subject: Re: Non-trivial condition is only propagated to one side of JOIN
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox