[
https://issues.apache.org/jira/browse/CALCITE-4958?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17488315#comment-17488315
]
Julian Hyde commented on CALCITE-4958:
--------------------------------------
[~sleep1661], That is expected behavior. A Sarg is a constant, and is composed
of constants (literals). A dynamic parameter is not a constant: its value is
known at execute time but not prepare time.
That's why I suggested that "x IN (?, 1, 2, 3)" should be treated as if the
user wrote "x = ? OR x IN (1, 2, 3)" and therefore become "x = ? OR SEARCH(x,
Sarg(1, 2, 3))".
To make the dynamic parameter part of the Sarg would require you to do query
planning at execute time. On the first execute the parameter might have value 7
and therefore the plan would be "SEARCH(x, Sarg(1, 2, 3, 7))" but on the second
execute the parameter might have a different value and would therefore require
a new plan. This isn't a totally crazy idea, but it does defeat the purpose of
a dynamic parameter.
> Poor performance execute plan when use dynamic parameters in IN-list clause
> ---------------------------------------------------------------------------
>
> Key: CALCITE-4958
> URL: https://issues.apache.org/jira/browse/CALCITE-4958
> Project: Calcite
> Issue Type: Bug
> Reporter: hujiahua
> Priority: Minor
>
> When we using IN-list predicate in where clause and setting the
> DEFAULT_IN_SUB_QUERY_THRESHOLD less than the IN-list elements size, IN-list
> predicate will converted to Join. And also using dynamic parameters in a
> VALUES clause, I found each dynamic parameter converted to a LogicalValues
> (e.g. "x IN (?, ?, ... ?)"). Too many dynamic parameters will lead to poor
> performance.
> 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}
> Then Logical plan will like this:
> {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}
--
This message was sent by Atlassian Jira
(v8.20.1#820001)
