From: Tom Lane <tgl@sss.pgh.pa.us>
To: Tobias Hoffmann <ldev-list@thax.hardliners.org>
cc: "David G. Johnston" <david.g.johnston@gmail.com>,
        "pgsql-hackers@postgresql.org" <pgsql-hackers@postgresql.org>
Subject: Re: Non-trivial condition is only propagated to one side of JOIN
In-reply-to: <13ebf00f-2991-43c5-5d11-a8216cb3217a@thax.hardliners.org>
References: <8520950a-272c-9cc6-de29-2993b4227bf3@thax.hardliners.org> <CAKFQuwZ3jMCfy=cMS5BrzVR7M-ZN2N_2Ocgh+6Rj89f6=an-ZA@mail.gmail.com> <13ebf00f-2991-43c5-5d11-a8216cb3217a@thax.hardliners.org>
Comments: In-reply-to Tobias Hoffmann <ldev-list@thax.hardliners.org>
	message dated "Sun, 25 Aug 2024 19:04:32 +0200"
MIME-Version: 1.0
Content-Type: text/plain; charset="UTF-8"
Content-ID: <2592656.1724606893.1@sss.pgh.pa.us>
Content-Transfer-Encoding: 8bit
Date: Sun, 25 Aug 2024 13:28:13 -0400
Message-ID: <2592657.1724606893@sss.pgh.pa.us>
Archived-At: <https://www.postgresql.org/message-id/2592657.1724606893%40sss.pgh.pa.us>
Precedence: bulk

Tobias Hoffmann <ldev-list@thax.hardliners.org> writes:
> 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);

For this particular case, you could probably get somewhere by
writing

SELECT * FROM view1 WHERE site_id = 1
UNION ALL
SELECT * FROM view1 WHERE site_id IS NULL;

since the sets of rows satisfying those two WHERE conditions
must be disjoint.  (I recall working on a patch that essentially
tried to do that transformation automatically, but it eventually
failed because things get too messy if the row sets might not
be disjoint.)

			regards, tom lane