Hi,
I'm analyzing testJoinMaterialization5 which times out after my recent
Calcite fixes.
I'm inclined that the bug is with MaterializedViewJoinRule(Filter).
The case is as follows:
materialized view:
select cast("empid" as BIGINT) from "emps"
join "depts" using ("deptno")
input SQL:
select "empid" "deptno" from "emps"
join "depts" using ("deptno") where "empid" > 1
empid has JavaType(int) not null.
Materialized view has cast(empid as BIGINT), and I expect the materialized
view must not be used.
However, MaterializedViewJoinRule(Filter) does not compare data types, and
it registers equivalence like rootRel :==: Project(input=mview, cast($0:
int)).
Do you think it is allowed to use materialization in this case?
It creates later an optimization cycle where ProjectMergeRule tries to
merge Projects:
Project(cast($0: bigint)) (it comes from mview tree) +
Project(cast($0:int)) (it is created by MaterializedViewJoinRule)
It results in creating projects like
Project(cast(cast(cast(cast($0:int):bigint):int:...), and the optimizer
can't really simplify those casts as the cast is
changing data types.
I see two options here:
1) Verify column datatypes in MaterializedViewJoinRule and skip using
materialization when the types do not match
2) Make RexSimplify aware of cast(cast(intExpression:bigint):int) pattern
I'm inclined to #1.
WDYT?
Vladimir
