Comment 9 for bug 951937

Sergey Petrunia (sergefp) wrote :

Consider an example:

select * from ot
where (ot.a, ot.b) in (select it.a, it.b
                        from it
                        where (it.a=111 and func1(it.c)) or (it.b=222 and func2(it.c))
                        )
       and (ot.a=333 or func3(ot.c))

convert_subq_to_sj(), followed by equality propagation, will produce this WHERE
clause:

01 multi-equal(ot.a, it.a) and
02 multi-equal(ot.b, it.b) and
03 (multi-equal(inherited(ot.a, it.a), 111) and func1(it.c) or
04 multi-equal(inherited(ot.b, it.b), 222) and func2(it.c)
05 ) and
06 (multi-equal(inherited(ot.a, it.a), 333) or func3(ot.c))

Suppose the chosen join order is
   ot, sj-materialization(it)
or the other way around, but using materialization.

Now, we do equality substitution:
- in line 03, we must not generate equalities that include ot.a, because the
  other part of OR uses func1(it.c) can only be checked when doing
  materialization.

- in line 06, we must not generate qualities that include it.a, because the
  other part of uses "func3(ot.c)", which cannot be checked when doing
  materialization.

This shows the problem. Suppose we're doing equality substitution for an OR
clause and its first child element is:

  multi-equal(inherited(ot.a, it.a), 111)

is this a case like in #03, or like in #06? There is no way to tell.