[jira] [Created] (CALCITE-4958) bad performance execute plan when use dynamic parameters in query

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

hujiahua created CALCITE-4958:
---------------------------------

             Summary: bad performance execute plan when use dynamic parameters in query
                 Key: CALCITE-4958
                 URL: https://issues.apache.org/jira/browse/CALCITE-4958
             Project: Calcite
          Issue Type: Bug
            Reporter: hujiahua


First I set SqlToRelConverter.DEFAULT_IN_SUB_QUERY_THRESHOLD = 2
And When I use dynamic parameters in query like this:
" select * from DEPTS where NAME in ( ?, ?, ?) "  , The IN-list will convert to union three project.
But if I not use dynamic parameters in query like this:
" select * from DEPTS where NAME in ( 'a', 'b', 'c') "  , The IN-list will a LogicalValues which is what I wanted.

Here is my test:   // I set DEFAULT_IN_SUB_QUERY_THRESHOLD = 2
{code:java}
      final String sql = "select * from \"TEST\".\"DEPTS\" where \"NAME\" in ( ?, ?, ?)";
      final PreparedStatement statement2 =
              calciteConnection.prepareStatement(sql);

      statement2.setString(1, "Sales");
      statement2.setString(2, "Sales2");
      statement2.setString(3, "Sales3");
      final ResultSet resultSet1 = statement2.executeQuery();
{code}

before optimized:
LogicalProject(DEPTNO=[$0], NAME=[$1])
  LogicalJoin(condition=[=($1, $2)], joinType=[inner])
    LogicalTableScan(table=[[TEST, DEPTS]])
    LogicalAggregate(group=[{0}])
      LogicalUnion(all=[true])
        LogicalProject(EXPR$0=[?0])
          LogicalValues(tuples=[[{ 0 }]])
        LogicalProject(EXPR$0=[?1])
          LogicalValues(tuples=[[{ 0 }]])
        LogicalProject(EXPR$0=[?2])
          LogicalValues(tuples=[[{ 0 }]])

after optimized:
EnumerableHashJoin(condition=[=($1, $2)], joinType=[semi])
  EnumerableTableScan(table=[[TEST, DEPTS]])
  EnumerableUnion(all=[true])
    EnumerableCalc(expr#0=[{inputs}], expr#1=[?0], EXPR$0=[$t1])
      EnumerableValues(tuples=[[{ 0 }]])
    EnumerableCalc(expr#0=[{inputs}], expr#1=[?1], EXPR$0=[$t1])
      EnumerableValues(tuples=[[{ 0 }]])
    EnumerableCalc(expr#0=[{inputs}], expr#1=[?2], EXPR$0=[$t1])
      EnumerableValues(tuples=[[{ 0 }]])





--
This message was sent by Atlassian Jira
(v8.20.1#820001)