[
https://issues.apache.org/jira/browse/CALCITE-4958?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17465071#comment-17465071
]
Julian Hyde commented on CALCITE-4958:
--------------------------------------
Good catch. I am surprised that this query turns into VALUES in the physical
plan. That would only ever have benefits if the IN list is solely literals.
Can you refine the subject, indicating that the issue relates to dynamic
parameters in a VALUES clause.
> 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
> Priority: Minor
>
> First I set SqlToRelConverter.DEFAULT_IN_SUB_QUERY_THRESHOLD = 2
> And When I use dynamic parameters in query like this:
> {noformat}
> select * from DEPTS where NAME in ( ?, ?, ?)
> {noformat}
> The IN-list will convert to union three project.
> {noformat}
> 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 }]])
> {noformat}
> But if I not use dynamic parameters in query like this:
> {noformat}
> select * from DEPTS where NAME in ( 'a', 'b', 'c')
> {noformat}
> The IN-list will a LogicalValues, this is what I wanted.
> {noformat}
> LogicalProject(DEPTNO=[$0], NAME=[$1])
> LogicalJoin(condition=[=($1, $2)], joinType=[inner])
> LogicalTableScan(table=[[TEST, DEPTS]])
> LogicalAggregate(group=[{0}])
> LogicalValues(tuples=[[{ 'a' }, { 'b' }, { 'c' }]])
> {noformat}
> 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}
--
This message was sent by Atlassian Jira
(v8.20.1#820001)
