Re: [HACKERS] Optimizer cleanup to avoid redundant work on joins

Bruce Momjian <pgman@candle.pha.pa.us> writes:
> I thought "t1.v1 = t2.v2" would be in t1 RelOptInfo and t2 RelOptInfo. 
> Of course, this is a join info restriction, not a restrict info
> restriction.

Right, it would appear in t1's joininfo list (showing t2 as unjoined_relids)
and in t2's joininfo list (showing t1 as unjoined_relids).  Then when
we make a join rel from t1 + t2, the clause would be put into that rel's
restrictinfo list, since it's no longer a joining clause for the
joinrel; but it does need to be implemented at the time of the join.
(The bug is probably only visible for auxiliary quals that are not
being used as the driving clause of the join method; they need to show
up in the qpquals of the final plan, or they won't get enforced.)

The trouble comes when there are more rels in the picture.  If we make
a joinrel from t1 + t3, this clause will still appear in that joinrel's
joininfo list, since it's still a joinclause for that rel.  Then when
we make t1+t2+t3 from {t1 t3} and {t2}, the clause propagates up to
become a restrict clause of that rel, and that's where the buck stops
(and where the clause gets enforced at runtime).

*BUT*, if we make {t1 t2 t3} from {t1 t2} and {t3}, it will *not* show
this clause as a restrictclause, because in that path it gets handled at
the {t1 t2} join.  So a joinpath for {t1 t3} against {t2}, which needs
this clause to appear as a restrictclause, loses if it is copied into a
{t1 t2 t3} RelOptInfo that was made from the other pair of
sub-relations.

I find that I can exhibit the bug very easily in current sources:

create table t1 (k1 int, d1 int);
create table t2 (k2 int, d2 int);
create table t3 (k3 int, d3 int);
create table t4 (k4 int, d4 int);

insert into t1 values (1, 1);
insert into t1 values (2, 2);
insert into t1 values (3, 3);

insert into t2 values (1, 11);
insert into t2 values (2, 22);
insert into t2 values (3, 33);

insert into t3 values (1, 111);
insert into t3 values (2, 222);
insert into t3 values (3, 333);

insert into t4 values (1, 1111);
insert into t4 values (2, 2222);
insert into t4 values (3, 3333);

select * from t1,t2,t3,t4 where k1 = k2 and k1 = k3 and k2=k4
and d1<d2 and d1<d3 and d1<d4 and d2<d3 and d2<d4 and d3>d4;
k1 | d1 | k2 | d2 | k3 | d3  | k4 |  d4
----+----+----+----+----+-----+----+------ 1 |  1 |  1 | 11 |  1 | 111 |  1 | 1111 2 |  2 |  2 | 22 |  2 | 222 |  2 |
22223 |  3 |  3 | 33 |  3 | 333 |  3 | 3333
 
(3 rows)

which is obviously not meeting the restriction d3>d4.  So we have
a problem.

I haven't been able to make 6.5.3 fail similarly, but I do not
understand why not --- it certainly looks like it *ought* to fail given
the right combination of circumstances.  I think it may be escaping a
failure by sheer luck having to do with the order that RelOptInfos get
inserted into the query's join_rel_list.  Our changes since 6.5 may have
exposed a problem that was only latent before.  (Or maybe I just haven't
hit the right combination to trip up 6.5.3 ... but it does seem that
current sources fail more easily.)

Anyway, in the current sources things are certainly broken, and I don't
see any real alternative except to press forward with moving join
restrictinfos into JoinPaths.  Even if we figure out exactly why 6.5.*
is somehow failing to fail, I am pretty certain that it must be a
non-robust coincidence rather than a solution that we want to keep using.
        regards, tom lane