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

Tobias Hoffmann <ldev-list(at)thax(dot)hardliners(dot)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