Re: Non-trivial condition is only propagated to one side of JOIN

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