Thread: 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/