public inbox for [email protected]
help / color / mirror / Atom feedFrom: Tom Lane <[email protected]>
To: Tobias Hoffmann <[email protected]>
Cc: 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 13:28:13 -0400
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
<CAKFQuwZ3jMCfy=cMS5BrzVR7M-ZN2N_2Ocgh+6Rj89f6=an-ZA@mail.gmail.com>
<[email protected]>
Tobias Hoffmann <[email protected]> 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
view thread (5+ messages)
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], [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