[
https://issues.apache.org/jira/browse/CALCITE-4958?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
hujiahua updated CALCITE-4958:
------------------------------
Description:
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.
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.
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:
{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}
after optimized:
{noformat}
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 }]])
{noformat}
was:
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 }]])
> 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.
> 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.
> 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:
> {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}
> after optimized:
> {noformat}
> 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 }]])
> {noformat}
--
This message was sent by Atlassian Jira
(v8.20.1#820001)
