From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Tobias Hoffmann <ldev-list(at)thax(dot)hardliners(dot)org> |
Cc: | "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com>, "pgsql-hackers(at)postgresql(dot)org" <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: Non-trivial condition is only propagated to one side of JOIN |
Date: | 2024-08-25 17:28:13 |
Message-ID: | 2592657.1724606893@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Pavel Stehule | 2024-08-25 18:57:30 | Re: [PATCH] Add CANONICAL option to xmlserialize |
Previous Message | Greg Sabino Mullane | 2024-08-25 17:14:36 | Re: Better error message when --single is not the first arg to postgres executable |