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

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

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

Julian Hyde edited comment on CALCITE-4958 at 12/24/21, 7:26 PM:
-----------------------------------------------------------------

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.

Maybe a hybrid IN list (e.g. "x IN (?, 1, 2, 3)") could be converted (depending on the threshold) as if the user wrote "x = ? OR x IN (VALUES (1), (2), (3))". Just a thought.

Can you refine the subject, indicating that the issue relates to dynamic parameters in a VALUES clause. 


was (Author: julianhyde):
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)