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.
Consider an example:
select * from ot
from it
where (it.a=111 and func1(it.c)) or (it.b=222 and func2(it.c))
)
where (ot.a, ot.b) in (select it.a, it.b
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 equal(inherited (ot.a, it.a), 111) and func1(it.c) or inherited( ot.b, it.b), 222) and func2(it.c) equal(inherited (ot.a, it.a), 333) or func3(ot.c))
02 multi-equal(ot.b, it.b) and
03 (multi-
04 multi-equal(
05 ) and
06 (multi-
Suppose the chosen join order is ion(it)
ot, sj-materializat
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.