ERROR: failed to build any 4-way joins
Test suite (as simple as I can produce):
CREATE TABLE foo (a int, b int);
INSERT INTO foo VALUES (1,2);
INSERT INTO foo VALUES (2,3);
INSERT INTO foo VALUES (3,3);
CREATE VIEW fooview AS
SELECT
f1.a AS a_for_max_b
FROM
( SELECT MAX(foo.b) AS MaxB FROM foo ) f2
INNER JOIN foo f1
ON f2.MaxB = f1.b;
And this query fails:
SELECT
*
FROM
fooview fv1
LEFT OUTER JOIN fooview fv2
ON TRUE = TRUE;
It also fails with RIGHT join, but it doesn't fail with INNER or FULL joins.
If view is defined (essentially the same) as
CREATE VIEW fooview AS
SELECT
f1.a AS a_for_max_b
FROM
foo f1
WHERE
f1.b = (SELECT MAX(f2.b) FROM foo f2);
then all is ok.
--
Teodor Sigaev E-mail: teodor@sigaev.ru
WWW: http://www.sigaev.ru/
Sorry, versions are 8.2 & 8.3. 8.1 works well
--
Teodor Sigaev E-mail: teodor@sigaev.ru
WWW: http://www.sigaev.ru/
Teodor Sigaev <teodor@sigaev.ru> writes:
Test suite (as simple as I can produce):
Mmm, sweet :-(. There is only one legal way to form the outer join, but
make_rels_by_joins() doesn't try it because have_relevant_joinclause()
says there is no relevant joinclause ... as indeed there is not, the
"true = true" thing having been optimized away. I guess we need a hack
to treat empty outer join conditions specially.
Sorry, versions are 8.2 & 8.3. 8.1 works well
Right, not a problem before 8.2 because outer join order was driven by
the syntax instead of by searching for a good join order. Also, you
need at least two base relations on each side of the outer join, else
the "last ditch" case in make_rels_by_joins() finds the join.
regards, tom lane
I wrote:
I guess we need a hack
to treat empty outer join conditions specially.
Actually, it can happen with non-empty join conditions too, if the join
condition doesn't mention the outer side; for instance, using your
example
explain SELECT * from fooview fv1
LEFT OUTER JOIN fooview fv2 on fv2.a_for_max_b < 10;
So my original thoughts of a narrow special case for "OUTER JOIN ON TRUE"
went up in smoke, and I ended up just having have_relevant_joinclause()
troll for relevant outer joins all the time. This probably isn't going
to cost enough planning time to matter, anyway.
regards, tom lane
went up in smoke, and I ended up just having have_relevant_joinclause()
Thank you a lot, I was near around it :)
--
Teodor Sigaev E-mail: teodor@sigaev.ru
WWW: http://www.sigaev.ru/