[jira] [Comment Edited] (CALCITE-3644) Calc on the Intersect in target is not being matched

["xzh_dz (Jira)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/CALCITE-3644?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17005216#comment-17005216 ] 

xzh_dz edited comment on CALCITE-3644 at 12/31/19 6:25 AM:
-----------------------------------------------------------

Add ProjectSetOpTransposeRule to normalize materialized view, try to fix it 。there is a unit test not passed.
error:
{code:java}
@Test public void testFilterToProject0() {
    String union =
        "select * from \"emps\" where \"empid\" > 300\n"
            + "union all select * from \"emps\" where \"empid\" < 200";
    String mv = "select *, \"empid\" * 2 from (" + union + ")";
    String query = "select * from (" + union + ") where (\"empid\" * 2) > 3";
    checkMaterialize(mv, query);
  }

error:
java.lang.AssertionError: type mismatch:
type1:
JavaType(class java.lang.String)
type2:
VARCHAR

	at org.apache.calcite.util.Litmus$1.fail(Litmus.java:31)
	at org.apache.calcite.plan.RelOptUtil.eq(RelOptUtil.java:2121)
	at org.apache.calcite.rex.RexProgramBuilder$RegisterInputShuttle.visitInputRef(RexProgramBuilder.java:948)
	at org.apache.calcite.rex.RexProgramBuilder$RegisterInputShuttle.visitInputRef(RexProgramBuilder.java:926)
{code}

root cause is the result has different RelDataTypes.
{code:java}
final RexProgram compenRexProgram = RexProgram.create(
            target.rowType, projectExprs, queryInputExplained0.left,
            query.rowType, rexBuilder);
{code}



was (Author: xzh_dz):
Add ProjectSetOpTransposeRule to normalize materialized view, try to fix it 。there is a unit test not passed.
error:
{code:java}
@Test public void testFilterToProject0() {
    String union =
        "select * from \"emps\" where \"empid\" > 300\n"
            + "union all select * from \"emps\" where \"empid\" < 200";
    String mv = "select *, \"empid\" * 2 from (" + union + ")";
    String query = "select * from (" + union + ") where (\"empid\" * 2) > 3";
    checkMaterialize(mv, query);
  }

error:
java.lang.AssertionError: type mismatch:
type1:
JavaType(class java.lang.String)
type2:
VARCHAR

	at org.apache.calcite.util.Litmus$1.fail(Litmus.java:31)
	at org.apache.calcite.plan.RelOptUtil.eq(RelOptUtil.java:2121)
	at org.apache.calcite.rex.RexProgramBuilder$RegisterInputShuttle.visitInputRef(RexProgramBuilder.java:948)
	at org.apache.calcite.rex.RexProgramBuilder$RegisterInputShuttle.visitInputRef(RexProgramBuilder.java:926)
{code}

root case is the result has different RelDataTypes.
{code:java}
final RexProgram compenRexProgram = RexProgram.create(
            target.rowType, projectExprs, queryInputExplained0.left,
            query.rowType, rexBuilder);
{code}


> Calc on the Intersect in target is not being matched
> ----------------------------------------------------
>
>                 Key: CALCITE-3644
>                 URL: https://issues.apache.org/jira/browse/CALCITE-3644
>             Project: Calcite
>          Issue Type: Wish
>            Reporter: xzh_dz
>            Priority: Major
>              Labels: pull-request-available
>          Time Spent: 1h 20m
>  Remaining Estimate: 0h
>
> {code:java}
> @Test public void testIntersectToCalcOnIntersect() {
>     final String mv = ""
>         + "select \"deptno\",\"name\" from \"emps\"\n"
>         + "intersect all\n"
>         + "select \"deptno\",\"name\" from \"depts\"";
>     String mv1 = "select \"name\", \"deptno\" from (" + mv + ")";
>     final String query = ""
>         + "select \"name\",\"deptno\" from \"depts\"\n"
>         + "intersect all\n"
>         + "select \"name\",\"deptno\" from \"emps\"";
>     checkMaterialize(mv1, query, true);
>   }
> {code}
> error:
> {code:java}
> java.lang.AssertionError: 
> Expected: a string containing "EnumerableTableScan(table=[[hr, m0]])"
>      but: was "PLAN=EnumerableIntersect(all=[true])\n  EnumerableCalc(expr#0..3=[{inputs}], name=[$t1], deptno=[$t0])\n    EnumerableTableScan(table=[[hr, depts]])\n  EnumerableCalc(expr#0..4=[{inputs}], name=[$t2], deptno=[$t1])\n    EnumerableTableScan(table=[[hr, emps]])\n\n"
> <Click to see difference>
> 	at org.hamcrest.MatcherAssert.assertThat(MatcherAssert.java:18)
> 	at org.hamcrest.MatcherAssert.assertThat(MatcherAssert.java:6)
> 	at org.apache.calcite.test.CalciteAssert.lambda$checkResultContains$7(CalciteAssert.java:429)
> 	at org.apache.calcite.test.CalciteAssert.assertQuery(CalciteAssert.java:544)
> 	at org.apache.calcite.test.CalciteAssert$AssertQuery.lambda$returns$1(CalciteAssert.java:1514)
> {code}



--
This message was sent by Atlassian Jira
(v8.3.4#803005)