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