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)