[jira] [Commented] (CALCITE-3952) Improve SortRemoveRule to remove Sort based on rowcount
[
https://issues.apache.org/jira/browse/CALCITE-3952?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17092313#comment-17092313
]
Vineet Garg commented on CALCITE-3952:
--
I have updated the pull request. Appreciate your feedback.
> Improve SortRemoveRule to remove Sort based on rowcount
> ---
>
> Key: CALCITE-3952
> URL: https://issues.apache.org/jira/browse/CALCITE-3952
> Project: Calcite
> Issue Type: Improvement
> Components: core
>Reporter: Vineet Garg
>Assignee: Vineet Garg
>Priority: Major
> Time Spent: 10m
> Remaining Estimate: 0h
>
> If a query is guaranteed to produce maximum one row it is safe to remove Sort
> (along with limit).
> Example:
> {code:sql}
> select count(*) cs from store_sales where ss_ext_sales_price > 100.00 order
> by cs limit 100
> {code}
> Although logically equivalent this can greatly benefit physical plans by
> removing extra operator and avoiding unnecessary data transfer.
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Commented] (CALCITE-3952) Improve SortRemoveRule to remove Sort based on rowcount
[
https://issues.apache.org/jira/browse/CALCITE-3952?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17090994#comment-17090994
]
Vineet Garg commented on CALCITE-3952:
--
bq. Given a rel which emit at most 1 row (RelMetadataQuery.getRowCount<=1),
should RelMetadataQuery.collations(rel) match the Sort order
[~jinxing6...@126.com] I don't think it is necessary in this case for sort
order to match. If input is producing at most 1 row there is no need to sort.
> Improve SortRemoveRule to remove Sort based on rowcount
> ---
>
> Key: CALCITE-3952
> URL: https://issues.apache.org/jira/browse/CALCITE-3952
> Project: Calcite
> Issue Type: Improvement
> Components: core
>Reporter: Vineet Garg
>Assignee: Vineet Garg
>Priority: Major
> Time Spent: 10m
> Remaining Estimate: 0h
>
> If a query is guaranteed to produce maximum one row it is safe to remove Sort
> (along with limit).
> Example:
> {code:sql}
> select count(*) cs from store_sales where ss_ext_sales_price > 100.00 order
> by cs limit 100
> {code}
> Although logically equivalent this can greatly benefit physical plans by
> removing extra operator and avoiding unnecessary data transfer.
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Commented] (CALCITE-3952) Improve SortRemoveRule to remove Sort based on rowcount
[
https://issues.apache.org/jira/browse/CALCITE-3952?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17090992#comment-17090992
]
Vineet Garg commented on CALCITE-3952:
--
Thanks for the feedback [~julianhyde]. I have few follow-up questions
bq. When we generate the baseline xml file we insert tests in approximately
alphabetical order. Reduces merge conflicts. Better than editing manually.
How do I regenerate these files automatically? Unlike mvn running the test with
gradle doesn't seem to generate xml file under target/surefire.
bq. be sure to check for OFFSET. If there is an offset you can’t safely remove
the Sort
Can you provide an example where it will not be safe? The patch currently (pull
request is open) checks if input has atmost single row and removes the sort
only if LIMIT >= 1. I am not sure why offset will change things here.
bq. I noticed that RelBuilder skips Aggregate if getMaxRowCount <= 1. Maybe it
could do the same for Sort. And then maybe you wouldn’t need to modify
SortRemoveRule.
The pull request I have opened has made changes to SortRemoveRule. May be it is
worthwhile having this logic in RelBuilder as well? Let me know what are your
thoughts on this.
> Improve SortRemoveRule to remove Sort based on rowcount
> ---
>
> Key: CALCITE-3952
> URL: https://issues.apache.org/jira/browse/CALCITE-3952
> Project: Calcite
> Issue Type: Improvement
> Components: core
>Reporter: Vineet Garg
>Assignee: Vineet Garg
>Priority: Major
> Time Spent: 10m
> Remaining Estimate: 0h
>
> If a query is guaranteed to produce maximum one row it is safe to remove Sort
> (along with limit).
> Example:
> {code:sql}
> select count(*) cs from store_sales where ss_ext_sales_price > 100.00 order
> by cs limit 100
> {code}
> Although logically equivalent this can greatly benefit physical plans by
> removing extra operator and avoiding unnecessary data transfer.
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Commented] (CALCITE-3952) Improve SortRemoveRule to remove Sort based on rowcount
[
https://issues.apache.org/jira/browse/CALCITE-3952?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17090238#comment-17090238
]
Vineet Garg commented on CALCITE-3952:
--
No problem [~hyuan] :)
It looks like calcite doesn't allow query with an aggregate without group by
but with order by. Queries like following is running into validation error:
{code:sql}
select count(*) as c\n"
+ "from sales.emp\n"
+ "where deptno = 10\n"
+ "order by deptno desc limit 10"
{code}
{noformat}
org.apache.calcite.runtime.CalciteContextException: From line 4, column 10 to
line 4, column 15: Expression 'DEPTNO' is not being grouped
{noformat}
> Improve SortRemoveRule to remove Sort based on rowcount
> ---
>
> Key: CALCITE-3952
> URL: https://issues.apache.org/jira/browse/CALCITE-3952
> Project: Calcite
> Issue Type: Improvement
> Components: core
>Reporter: Vineet Garg
>Assignee: Vineet Garg
>Priority: Major
>
> If a query is guaranteed to produce maximum one row it is safe to remove Sort
> (along with limit).
> Example:
> {code:sql}
> select count(*) cs from store_sales where ss_ext_sales_price > 100.00 order
> by cs limit 100
> {code}
> Although logically equivalent this can greatly benefit physical plans by
> removing extra operator and avoiding unnecessary data transfer.
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Commented] (CALCITE-3952) Improve SortRemoveRule to remove Sort based on rowcount
[
https://issues.apache.org/jira/browse/CALCITE-3952?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17090222#comment-17090222
]
Vineet Garg commented on CALCITE-3952:
--
BTW do you know how can I add test to {{RelOptRulesTest}}? Now that calcite is
using gradle i am unable to figure out how to generate baseline xml file.
> Improve SortRemoveRule to remove Sort based on rowcount
> ---
>
> Key: CALCITE-3952
> URL: https://issues.apache.org/jira/browse/CALCITE-3952
> Project: Calcite
> Issue Type: Improvement
> Components: core
>Reporter: Vineet Garg
>Assignee: Vineet Garg
>Priority: Major
>
> If a query is guaranteed to produce maximum one row it is safe to remove Sort
> (along with limit).
> Example:
> {code:sql}
> select count(*) cs from store_sales where ss_ext_sales_price > 100.00 order
> by cs limit 100
> {code}
> Although logically equivalent this can greatly benefit physical plans by
> removing extra operator and avoiding unnecessary data transfer.
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Commented] (CALCITE-3952) Improve SortRemoveRule to remove Sort based on rowcount
[
https://issues.apache.org/jira/browse/CALCITE-3952?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17090219#comment-17090219
]
Vineet Garg commented on CALCITE-3952:
--
[~hyuan] In that case we shouldn't remove the sort. In general we can remove
sort if input is producing at most one row and either there is no limit or
limit is greater than 0.
> Improve SortRemoveRule to remove Sort based on rowcount
> ---
>
> Key: CALCITE-3952
> URL: https://issues.apache.org/jira/browse/CALCITE-3952
> Project: Calcite
> Issue Type: Improvement
> Components: core
>Reporter: Vineet Garg
>Assignee: Vineet Garg
>Priority: Major
>
> If a query is guaranteed to produce maximum one row it is safe to remove Sort
> (along with limit).
> Example:
> {code:sql}
> select count(*) cs from store_sales where ss_ext_sales_price > 100.00 order
> by cs limit 100
> {code}
> Although logically equivalent this can greatly benefit physical plans by
> removing extra operator and avoiding unnecessary data transfer.
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Created] (CALCITE-3952) Improve SortRemoveRule to remove Sort based on rowcount
Vineet Garg created CALCITE-3952:
Summary: Improve SortRemoveRule to remove Sort based on rowcount
Key: CALCITE-3952
URL: https://issues.apache.org/jira/browse/CALCITE-3952
Project: Calcite
Issue Type: Improvement
Components: core
Reporter: Vineet Garg
Assignee: Vineet Garg
If a query is guaranteed to produce maximum one row it is safe to remove Sort
(along with limit).
Example:
{code:sql}
select count(*) cs from store_sales where ss_ext_sales_price > 100.00 order by
cs limit 100
{code}
Although logically equivalent this can greatly benefit physical plans by
removing extra operator and avoiding unnecessary data transfer.
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Updated] (CALCITE-3928) Canonicalization doesn't do field trimming before materialized view matching
[
https://issues.apache.org/jira/browse/CALCITE-3928?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Vineet Garg updated CALCITE-3928:
-
Labels: materializedviews (was: )
> Canonicalization doesn't do field trimming before materialized view matching
>
>
> Key: CALCITE-3928
> URL: https://issues.apache.org/jira/browse/CALCITE-3928
> Project: Calcite
> Issue Type: Bug
>Reporter: Jin Xing
>Priority: Major
> Labels: materializedviews
>
> If we have query and materialized view as below:
> {code:java}
> query:
> LogicalAggregate(group=[{0}], EXPR$1=[afunc($1, $1)])
> LogicalProject(a=$0, b=[bfunc($1)])
> LogicalTableScan(table=[[default, user_table]])
> mv:
> LogicalAggregate(group=[{0}], EXPR$1=[afunc($1, $2)])
> LogicalProject(a=$0, b=[bfunc($1)], c=[bfunc($1)])
> LogicalTableScan(table=[[default, user_table]])
> {code}
> The semantics of query and mv logic are the same. Materialized view matching
> failed, because field trimming is not done when canonicalizing the plans.
> Currently Calcite does field trimming when convert sql to rel. But my
> company's internal system does materialization detection – – generates &
> transforms & stores the RelNode.
> Shall we add the field trimming when canonicalizing materialized view logic?
>
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Commented] (CALCITE-3928) Canonicalization doesn't do field trimming before materialized view matching
[
https://issues.apache.org/jira/browse/CALCITE-3928?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17087322#comment-17087322
]
Vineet Garg commented on CALCITE-3928:
--
[~jinxing6...@126.com] Can you provide the actual query or test case? We might
be missing a case for rewriting but I am not sure if I understand this fully.
> Canonicalization doesn't do field trimming before materialized view matching
>
>
> Key: CALCITE-3928
> URL: https://issues.apache.org/jira/browse/CALCITE-3928
> Project: Calcite
> Issue Type: Bug
>Reporter: Jin Xing
>Priority: Major
>
> If we have query and materialized view as below:
> {code:java}
> query:
> LogicalAggregate(group=[{0}], EXPR$1=[afunc($1, $1)])
> LogicalProject(a=$0, b=[bfunc($1)])
> LogicalTableScan(table=[[default, user_table]])
> mv:
> LogicalAggregate(group=[{0}], EXPR$1=[afunc($1, $2)])
> LogicalProject(a=$0, b=[bfunc($1)], c=[bfunc($1)])
> LogicalTableScan(table=[[default, user_table]])
> {code}
> The semantics of query and mv logic are the same. Materialized view matching
> failed, because field trimming is not done when canonicalizing the plans.
> Currently Calcite does field trimming when convert sql to rel. But my
> company's internal system does materialization detection – – generates &
> transforms & stores the RelNode.
> Shall we add the field trimming when canonicalizing materialized view logic?
>
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Commented] (CALCITE-3918) SubQueryFilterRemoveRule failed to decorrelate subquery for TPCH q17
[
https://issues.apache.org/jira/browse/CALCITE-3918?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17087319#comment-17087319
]
Vineet Garg commented on CALCITE-3918:
--
SubQueryFilterRemoveRule can not decorrelate the plan. It basically removes
subquery (RexSubQuery node) by rewriting queries into join and it may introduce
Correlate expressions if there is any correlation in the query. RelDecorrelator
is then suppose to be used to remove this correlation.
> SubQueryFilterRemoveRule failed to decorrelate subquery for TPCH q17
>
>
> Key: CALCITE-3918
> URL: https://issues.apache.org/jira/browse/CALCITE-3918
> Project: Calcite
> Issue Type: Bug
> Components: core
>Reporter: Haisheng Yuan
>Priority: Major
>
> Disable RelDecorrelator and run TpchTest.testQuery17(),
> SubQueryFilterRemoveRule generates plan with Correlate, which is not expected.
> {code:java}
> EnumerableProject(AVG_YEARLY=[/($0, 7.0:DECIMAL(2, 1))])
> EnumerableAggregate(group=[{}], agg#0=[SUM($2)])
> EnumerableFilter(condition=[AND(=($3, $0), =(CAST($4):VARCHAR,
> 'Brand#13'), =(CAST($5):VARCHAR, 'JUMBO CAN'), <($1, $6))])
> EnumerableCorrelate(correlation=[$cor0], joinType=[left],
> requiredColumns=[{3}])
> EnumerableNestedLoopJoin(condition=[true], joinType=[inner])
> EnumerableProject(L_PARTKEY=[$1], L_QUANTITY=[$4],
> L_EXTENDEDPRICE=[$5])
> EnumerableTableScan(table=[[TPCH_01, LINEITEM]])
> EnumerableProject(P_PARTKEY=[$0], P_BRAND=[$3], P_CONTAINER=[$6])
> EnumerableTableScan(table=[[TPCH_01, PART]])
> EnumerableProject($f0=[*(0.2:DECIMAL(2, 1), CAST(/(CASE(=($1, 0),
> null:JavaType(class java.lang.Long), $0), $1)):JavaType(class
> java.lang.Long))])
> EnumerableAggregate(group=[{}], agg#0=[$SUM0($4)],
> agg#1=[COUNT($4)])
> EnumerableFilter(condition=[=($1, $cor0.P_PARTKEY)])
> EnumerableTableScan(table=[[TPCH_01, LINEITEM]])
> {code}
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Commented] (CALCITE-3918) SubQueryFilterRemoveRule failed to decorrelate subquery for TPCH q17
[
https://issues.apache.org/jira/browse/CALCITE-3918?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17087316#comment-17087316
]
Vineet Garg commented on CALCITE-3918:
--
Thanks [~hyuan]. I am confused about this. If you disable RelDecorrelator how
can you expect to remove Correlate expressions from the plan? What are the
expectations here?
> SubQueryFilterRemoveRule failed to decorrelate subquery for TPCH q17
>
>
> Key: CALCITE-3918
> URL: https://issues.apache.org/jira/browse/CALCITE-3918
> Project: Calcite
> Issue Type: Bug
> Components: core
>Reporter: Haisheng Yuan
>Priority: Major
>
> Disable RelDecorrelator and run TpchTest.testQuery17(),
> SubQueryFilterRemoveRule generates plan with Correlate, which is not expected.
> {code:java}
> EnumerableProject(AVG_YEARLY=[/($0, 7.0:DECIMAL(2, 1))])
> EnumerableAggregate(group=[{}], agg#0=[SUM($2)])
> EnumerableFilter(condition=[AND(=($3, $0), =(CAST($4):VARCHAR,
> 'Brand#13'), =(CAST($5):VARCHAR, 'JUMBO CAN'), <($1, $6))])
> EnumerableCorrelate(correlation=[$cor0], joinType=[left],
> requiredColumns=[{3}])
> EnumerableNestedLoopJoin(condition=[true], joinType=[inner])
> EnumerableProject(L_PARTKEY=[$1], L_QUANTITY=[$4],
> L_EXTENDEDPRICE=[$5])
> EnumerableTableScan(table=[[TPCH_01, LINEITEM]])
> EnumerableProject(P_PARTKEY=[$0], P_BRAND=[$3], P_CONTAINER=[$6])
> EnumerableTableScan(table=[[TPCH_01, PART]])
> EnumerableProject($f0=[*(0.2:DECIMAL(2, 1), CAST(/(CASE(=($1, 0),
> null:JavaType(class java.lang.Long), $0), $1)):JavaType(class
> java.lang.Long))])
> EnumerableAggregate(group=[{}], agg#0=[$SUM0($4)],
> agg#1=[COUNT($4)])
> EnumerableFilter(condition=[=($1, $cor0.P_PARTKEY)])
> EnumerableTableScan(table=[[TPCH_01, LINEITEM]])
> {code}
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Commented] (CALCITE-3918) SubQueryFilterRemoveRule failed to decorrelate subquery for TPCH q17
[
https://issues.apache.org/jira/browse/CALCITE-3918?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17087313#comment-17087313
]
Vineet Garg commented on CALCITE-3918:
--
[~hyuan] How do I disable the RelDecorrelator for this test?
> SubQueryFilterRemoveRule failed to decorrelate subquery for TPCH q17
>
>
> Key: CALCITE-3918
> URL: https://issues.apache.org/jira/browse/CALCITE-3918
> Project: Calcite
> Issue Type: Bug
> Components: core
>Reporter: Haisheng Yuan
>Priority: Major
>
> Disable RelDecorrelator and run TpchTest.testQuery17(),
> SubQueryFilterRemoveRule generates plan with Correlate, which is not expected.
> {code:java}
> EnumerableProject(AVG_YEARLY=[/($0, 7.0:DECIMAL(2, 1))])
> EnumerableAggregate(group=[{}], agg#0=[SUM($2)])
> EnumerableFilter(condition=[AND(=($3, $0), =(CAST($4):VARCHAR,
> 'Brand#13'), =(CAST($5):VARCHAR, 'JUMBO CAN'), <($1, $6))])
> EnumerableCorrelate(correlation=[$cor0], joinType=[left],
> requiredColumns=[{3}])
> EnumerableNestedLoopJoin(condition=[true], joinType=[inner])
> EnumerableProject(L_PARTKEY=[$1], L_QUANTITY=[$4],
> L_EXTENDEDPRICE=[$5])
> EnumerableTableScan(table=[[TPCH_01, LINEITEM]])
> EnumerableProject(P_PARTKEY=[$0], P_BRAND=[$3], P_CONTAINER=[$6])
> EnumerableTableScan(table=[[TPCH_01, PART]])
> EnumerableProject($f0=[*(0.2:DECIMAL(2, 1), CAST(/(CASE(=($1, 0),
> null:JavaType(class java.lang.Long), $0), $1)):JavaType(class
> java.lang.Long))])
> EnumerableAggregate(group=[{}], agg#0=[$SUM0($4)],
> agg#1=[COUNT($4)])
> EnumerableFilter(condition=[=($1, $cor0.P_PARTKEY)])
> EnumerableTableScan(table=[[TPCH_01, LINEITEM]])
> {code}
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Created] (CALCITE-3914) Improve SubsitutionVisitor to consider RexCall of type PLUS and TIMES for canonicalization
Vineet Garg created CALCITE-3914: Summary: Improve SubsitutionVisitor to consider RexCall of type PLUS and TIMES for canonicalization Key: CALCITE-3914 URL: https://issues.apache.org/jira/browse/CALCITE-3914 Project: Calcite Issue Type: Improvement Components: core Reporter: Vineet Garg Assignee: Vineet Garg -- This message was sent by Atlassian Jira (v8.3.4#803005)
[jira] [Commented] (CALCITE-3890) Infer IS NOT NULL predicate from join
[
https://issues.apache.org/jira/browse/CALCITE-3890?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17073268#comment-17073268
]
Vineet Garg commented on CALCITE-3890:
--
As [~kgyrtkirk] pointed out we have doing this in Hive for a while and it is
very useful rule. I will be more than glad to work on contributing this code to
calcite.
> Infer IS NOT NULL predicate from join
> -
>
> Key: CALCITE-3890
> URL: https://issues.apache.org/jira/browse/CALCITE-3890
> Project: Calcite
> Issue Type: Improvement
> Components: core
>Reporter: Chunwei Lei
>Assignee: Chunwei Lei
>Priority: Major
>
> We can infer IS NOT NULL predicate from join which implies some columns may
> not be null. For instance,
>
> {code:java}
> select * from a join b on a.id = b.id;
> {code}
> we can infer a.id is not null and b.id is not null.
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Updated] (CALCITE-3862) Rewriting for materialized view consisting of group by on join keys with aggregate fails
[
https://issues.apache.org/jira/browse/CALCITE-3862?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Vineet Garg updated CALCITE-3862:
-
Description:
*Repro*
{code:sql}
+sql("select \"deptno\", \"empid\", \"salary\", sum(1) "
++ "from \"emps\"\n"
++ "group by \"deptno\", \"empid\", \"salary\"",
+"select sum(1) "
++ "from \"emps\"\n"
++ "join \"depts\" on \"depts\".\"deptno\" = \"empid\" group by
\"empid\", \"depts\".\"deptno\"")
+.withResultContains(
+"EnumerableCalc(expr#0..1=[{inputs}], EXPR$0=[$t1])\n"
++ " EnumerableAggregate(group=[{1}], EXPR$0=[$SUM0($3)])\n"
++ "EnumerableHashJoin(condition=[=($1, $4)],
joinType=[inner])\n"
++ " EnumerableTableScan(table=[[hr, m0]])")
+.ok();
{code}
*Error*
{code}
Next exception 1: [CIRCULAR REFERENCE SQLException]
Next exception 2: [CIRCULAR REFERENCE RuntimeException]
Next exception 3: java.lang.IndexOutOfBoundsException: index (2)
must be less than size (2)
at
com.google.common.base.Preconditions.checkElementIndex(Preconditions.java:310)
at
com.google.common.base.Preconditions.checkElementIndex(Preconditions.java:293)
at
com.google.common.collect.RegularImmutableList.get(RegularImmutableList.java:67)
at
org.apache.calcite.rex.RexBuilder.makeInputRef(RexBuilder.java:853)
at
org.apache.calcite.rel.rules.materialize.MaterializedViewRule$3.visitInputRef(MaterializedViewRule.java:1217)
at
org.apache.calcite.rel.rules.materialize.MaterializedViewRule$3.visitInputRef(MaterializedViewRule.java:1181)
at
org.apache.calcite.rex.RexInputRef.accept(RexInputRef.java:112)
at org.apache.calcite.rex.RexShuttle.apply(RexShuttle.java:277)
at
org.apache.calcite.rel.rules.materialize.MaterializedViewRule.shuttleReferences(MaterializedViewRule.java:1242)
at
org.apache.calcite.rel.rules.materialize.MaterializedViewAggregateRule.rewriteView(MaterializedViewAggregateRule.java:728)
at
org.apache.calcite.rel.rules.materialize.MaterializedViewRule.perform(MaterializedViewRule.java:485)
at
org.apache.calcite.rel.rules.materialize.MaterializedViewOnlyAggregateRule.onMatch(MaterializedViewOnlyAggregateRule.java:63)
at
org.apache.calcite.plan.volcano.VolcanoRuleCall.onMatch(VolcanoRuleCall.java:238)
at
org.apache.calcite.plan.volcano.VolcanoPlanner.findBestExp(VolcanoPlanner.java:540)
at
org.apache.calcite.tools.Programs.lambda$standard$3(Programs.java:286)
at
org.apache.calcite.tools.Programs$SequenceProgram.run(Programs.java:346)
at org.apache.calcite.prepare.Prepare.optimize(Prepare.java:165)
at
org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:290)
at
org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:207)
at
org.apache.calcite.prepare.CalcitePrepareImpl.prepare2_(CalcitePrepareImpl.java:634)
at
org.apache.calcite.prepare.CalcitePrepareImpl.prepare_(CalcitePrepareImpl.java:498)
at
org.apache.calcite.prepare.CalcitePrepareImpl.prepareSql(CalcitePrepareImpl.java:468)
at
org.apache.calcite.jdbc.CalciteConnectionImpl.parseQuery(CalciteConnectionImpl.java:231)
at
org.apache.calcite.jdbc.CalciteMetaImpl.prepareAndExecute(CalciteMetaImpl.java:550)
at
org.apache.calcite.avatica.AvaticaConnection.prepareAndExecuteInternal(AvaticaConnection.java:675)
at
org.apache.calcite.avatica.AvaticaStatement.executeInternal(AvaticaStatement.java:156)
{code}
was:
*Repro*
{code:sql}
+sql("select \"deptno\", \"empid\", \"salary\", sum(1) "
++ "from \"emps\"\n"
++ "group by \"deptno\", \"empid\", \"salary\"",
+"select sum(1) "
++ "from \"emps\"\n"
++ "join \"depts\" on \"depts\".\"deptno\" = \"empid\" group by
\"empid\", \"depts\".\"deptno\"")
+.withResultContains(
+"EnumerableCalc(expr#0..1=[{inputs}], EXPR$0=[$t1])\n"
++ " EnumerableAggregate(group=[{1}], EXPR$0=[$SUM0($3)])\n"
++ "EnumerableHashJoin(condition=[=($1, $4)],
joinType=[inner])\n"
++ " EnumerableTableScan(table=[[hr, m0]])")
+.ok();
{code}
> Rewriting for materialized view consisting of group by on join keys with
> aggregate fails
>
>
> Key: CALCITE-3862
> URL: https://issues.apache.org/jira/browse/CALCITE-3862
> Project: Calcite
> Issue Type: Bug
> Components:
[jira] [Created] (CALCITE-3862) Rewriting for materialized view consisting of group by on join keys with aggregate fails
Vineet Garg created CALCITE-3862:
Summary: Rewriting for materialized view consisting of group by on
join keys with aggregate fails
Key: CALCITE-3862
URL: https://issues.apache.org/jira/browse/CALCITE-3862
Project: Calcite
Issue Type: Bug
Components: core
Reporter: Vineet Garg
Assignee: Vineet Garg
*Repro*
{code:sql}
+sql("select \"deptno\", \"empid\", \"salary\", sum(1) "
++ "from \"emps\"\n"
++ "group by \"deptno\", \"empid\", \"salary\"",
+"select sum(1) "
++ "from \"emps\"\n"
++ "join \"depts\" on \"depts\".\"deptno\" = \"empid\" group by
\"empid\", \"depts\".\"deptno\"")
+.withResultContains(
+"EnumerableCalc(expr#0..1=[{inputs}], EXPR$0=[$t1])\n"
++ " EnumerableAggregate(group=[{1}], EXPR$0=[$SUM0($3)])\n"
++ "EnumerableHashJoin(condition=[=($1, $4)],
joinType=[inner])\n"
++ " EnumerableTableScan(table=[[hr, m0]])")
+.ok();
{code}
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Commented] (CALCITE-3848) Materialized view rewriting fails for mv consisting of group by on join keys
[
https://issues.apache.org/jira/browse/CALCITE-3848?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17055459#comment-17055459
]
Vineet Garg commented on CALCITE-3848:
--
[~jcamachorodriguez] can you take a look please?
> Materialized view rewriting fails for mv consisting of group by on join keys
>
>
> Key: CALCITE-3848
> URL: https://issues.apache.org/jira/browse/CALCITE-3848
> Project: Calcite
> Issue Type: Bug
> Components: core
>Reporter: Vineet Garg
>Assignee: Vineet Garg
>Priority: Major
> Labels: pull-request-available
> Time Spent: 0.5h
> Remaining Estimate: 0h
>
> Test case
> {code:java}
> + @Test public void testAggregateOnJoinKeys() {
> +checkMaterialize(
> +"select \"deptno\", \"empid\", \"salary\"\n"
> ++ "from \"emps\"\n"
> ++ "group by \"deptno\", \"empid\", \"salary\"",
> + "select \"empid\", \"depts\".\"deptno\" \n"
> ++ "from \"emps\"\n"
> ++ "join \"depts\" on \"depts\".\"deptno\" = \"empid\" group by
> \"empid\", \"depts\".\"deptno\"",
> +HR_FKUK_MODEL,
> +CalciteAssert.checkResultContains(
> +"EnumerableCalc(expr#0=[{inputs}], empid=[$t0], empid0=[$t0])\n"
> + + " EnumerableAggregate(group=[{1}])\n"
> ++ "EnumerableHashJoin(condition=[=($1, $3)],
> joinType=[inner])\n"
> ++ " EnumerableTableScan(table=[[hr, m0]])"));
> + }
> +
> {code}
> Error:
> {code}
> Caused by: java.lang.RuntimeException: Error while applying rule
> MaterializedViewAggregateRule(Aggregate), args
> [rel#64476:EnumerableAggregate.ENUMERABLE.[](input=RelSubset#64475,group={0,
> 1})]
> at
> org.apache.calcite.plan.volcano.VolcanoRuleCall.onMatch(VolcanoRuleCall.java:260)
> at
> org.apache.calcite.plan.volcano.VolcanoPlanner.findBestExp(VolcanoPlanner.java:634)
> at
> org.apache.calcite.tools.Programs.lambda$standard$3(Programs.java:286)
> at
> org.apache.calcite.tools.Programs$SequenceProgram.run(Programs.java:346)
> at
> org.apache.calcite.prepare.Prepare.optimize(Prepare.java:165)
> at
> org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:290)
> at
> org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:207)
> at
> org.apache.calcite.prepare.CalcitePrepareImpl.prepare2_(CalcitePrepareImpl.java:634)
> at
> org.apache.calcite.prepare.CalcitePrepareImpl.prepare_(CalcitePrepareImpl.java:498)
> at
> org.apache.calcite.prepare.CalcitePrepareImpl.prepareSql(CalcitePrepareImpl.java:468)
> at
> org.apache.calcite.jdbc.CalciteConnectionImpl.parseQuery(CalciteConnectionImpl.java:231)
> at
> org.apache.calcite.jdbc.CalciteMetaImpl.prepareAndExecute(CalciteMetaImpl.java:550)
> at
> org.apache.calcite.avatica.AvaticaConnection.prepareAndExecuteInternal(AvaticaConnection.java:675)
> at
> org.apache.calcite.avatica.AvaticaStatement.executeInternal(AvaticaStatement.java:156)
> ... 16 more
> Next exception 1: [CIRCULAR REFERENCE SQLException]
> Next exception 2: [CIRCULAR REFERENCE RuntimeException]
> Next exception 3:
> org.apache.calcite.util.mapping.Mappings$NoElementException: source #0 has no
> target in mapping [size=1, sourceCount=2, targetCount=7, elements=[1:1]]
> at
> org.apache.calcite.util.mapping.Mappings$AbstractMapping.getTarget(Mappings.java:881)
> at
> org.apache.calcite.rel.rules.materialize.MaterializedViewAggregateRule.rewriteView(MaterializedViewAggregateRule.java:677)
> at
> org.apache.calcite.rel.rules.materialize.MaterializedViewRule.perform(MaterializedViewRule.java:485)
> at
> org.apache.calcite.rel.rules.materialize.MaterializedViewOnlyAggregateRule.onMatch(MaterializedViewOnlyAggregateRule.java:63)
> at
> org.apache.calcite.plan.volcano.VolcanoRuleCall.onMatch(VolcanoRuleCall.java:233)
> at
> org.apache.calcite.plan.volcano.VolcanoPlanner.findBestExp(VolcanoPlanner.java:634)
> at
> org.apache.calcite.tools.Programs.lambda$standard$3(Programs.java:286)
> at
> org.apache.calcite.tools.Programs$SequenceProgram.run(Programs.java:346)
> at
> org.apache.calcite.prepare.Prepare.optimize(Prepare.java:165)
> at
> org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:290)
> at
> org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:207)
> at
>
[jira] [Commented] (CALCITE-3848) Materialized view rewriting fails for mv consisting of group by on join keys
[
https://issues.apache.org/jira/browse/CALCITE-3848?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17055457#comment-17055457
]
Vineet Garg commented on CALCITE-3848:
--
Please ignore the first pull request #1851
> Materialized view rewriting fails for mv consisting of group by on join keys
>
>
> Key: CALCITE-3848
> URL: https://issues.apache.org/jira/browse/CALCITE-3848
> Project: Calcite
> Issue Type: Bug
> Components: core
>Reporter: Vineet Garg
>Assignee: Vineet Garg
>Priority: Major
> Labels: pull-request-available
> Time Spent: 0.5h
> Remaining Estimate: 0h
>
> Test case
> {code:java}
> + @Test public void testAggregateOnJoinKeys() {
> +checkMaterialize(
> +"select \"deptno\", \"empid\", \"salary\"\n"
> ++ "from \"emps\"\n"
> ++ "group by \"deptno\", \"empid\", \"salary\"",
> + "select \"empid\", \"depts\".\"deptno\" \n"
> ++ "from \"emps\"\n"
> ++ "join \"depts\" on \"depts\".\"deptno\" = \"empid\" group by
> \"empid\", \"depts\".\"deptno\"",
> +HR_FKUK_MODEL,
> +CalciteAssert.checkResultContains(
> +"EnumerableCalc(expr#0=[{inputs}], empid=[$t0], empid0=[$t0])\n"
> + + " EnumerableAggregate(group=[{1}])\n"
> ++ "EnumerableHashJoin(condition=[=($1, $3)],
> joinType=[inner])\n"
> ++ " EnumerableTableScan(table=[[hr, m0]])"));
> + }
> +
> {code}
> Error:
> {code}
> Caused by: java.lang.RuntimeException: Error while applying rule
> MaterializedViewAggregateRule(Aggregate), args
> [rel#64476:EnumerableAggregate.ENUMERABLE.[](input=RelSubset#64475,group={0,
> 1})]
> at
> org.apache.calcite.plan.volcano.VolcanoRuleCall.onMatch(VolcanoRuleCall.java:260)
> at
> org.apache.calcite.plan.volcano.VolcanoPlanner.findBestExp(VolcanoPlanner.java:634)
> at
> org.apache.calcite.tools.Programs.lambda$standard$3(Programs.java:286)
> at
> org.apache.calcite.tools.Programs$SequenceProgram.run(Programs.java:346)
> at
> org.apache.calcite.prepare.Prepare.optimize(Prepare.java:165)
> at
> org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:290)
> at
> org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:207)
> at
> org.apache.calcite.prepare.CalcitePrepareImpl.prepare2_(CalcitePrepareImpl.java:634)
> at
> org.apache.calcite.prepare.CalcitePrepareImpl.prepare_(CalcitePrepareImpl.java:498)
> at
> org.apache.calcite.prepare.CalcitePrepareImpl.prepareSql(CalcitePrepareImpl.java:468)
> at
> org.apache.calcite.jdbc.CalciteConnectionImpl.parseQuery(CalciteConnectionImpl.java:231)
> at
> org.apache.calcite.jdbc.CalciteMetaImpl.prepareAndExecute(CalciteMetaImpl.java:550)
> at
> org.apache.calcite.avatica.AvaticaConnection.prepareAndExecuteInternal(AvaticaConnection.java:675)
> at
> org.apache.calcite.avatica.AvaticaStatement.executeInternal(AvaticaStatement.java:156)
> ... 16 more
> Next exception 1: [CIRCULAR REFERENCE SQLException]
> Next exception 2: [CIRCULAR REFERENCE RuntimeException]
> Next exception 3:
> org.apache.calcite.util.mapping.Mappings$NoElementException: source #0 has no
> target in mapping [size=1, sourceCount=2, targetCount=7, elements=[1:1]]
> at
> org.apache.calcite.util.mapping.Mappings$AbstractMapping.getTarget(Mappings.java:881)
> at
> org.apache.calcite.rel.rules.materialize.MaterializedViewAggregateRule.rewriteView(MaterializedViewAggregateRule.java:677)
> at
> org.apache.calcite.rel.rules.materialize.MaterializedViewRule.perform(MaterializedViewRule.java:485)
> at
> org.apache.calcite.rel.rules.materialize.MaterializedViewOnlyAggregateRule.onMatch(MaterializedViewOnlyAggregateRule.java:63)
> at
> org.apache.calcite.plan.volcano.VolcanoRuleCall.onMatch(VolcanoRuleCall.java:233)
> at
> org.apache.calcite.plan.volcano.VolcanoPlanner.findBestExp(VolcanoPlanner.java:634)
> at
> org.apache.calcite.tools.Programs.lambda$standard$3(Programs.java:286)
> at
> org.apache.calcite.tools.Programs$SequenceProgram.run(Programs.java:346)
> at
> org.apache.calcite.prepare.Prepare.optimize(Prepare.java:165)
> at
> org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:290)
> at
> org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:207)
> at
>
[jira] [Updated] (CALCITE-3848) Materialized view rewriting fails for mv consisting of group by on join keys
[
https://issues.apache.org/jira/browse/CALCITE-3848?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Vineet Garg updated CALCITE-3848:
-
Description:
Test case
{code:java}
+ @Test public void testAggregateOnJoinKeys() {
+checkMaterialize(
+"select \"deptno\", \"empid\", \"salary\"\n"
++ "from \"emps\"\n"
++ "group by \"deptno\", \"empid\", \"salary\"",
+ "select \"empid\", \"depts\".\"deptno\" \n"
++ "from \"emps\"\n"
++ "join \"depts\" on \"depts\".\"deptno\" = \"empid\" group by
\"empid\", \"depts\".\"deptno\"",
+HR_FKUK_MODEL,
+CalciteAssert.checkResultContains(
+"EnumerableCalc(expr#0=[{inputs}], empid=[$t0], empid0=[$t0])\n"
+ + " EnumerableAggregate(group=[{1}])\n"
++ "EnumerableHashJoin(condition=[=($1, $3)],
joinType=[inner])\n"
++ " EnumerableTableScan(table=[[hr, m0]])"));
+ }
+
{code}
Error:
{code}
Caused by: java.lang.RuntimeException: Error while applying rule
MaterializedViewAggregateRule(Aggregate), args
[rel#64476:EnumerableAggregate.ENUMERABLE.[](input=RelSubset#64475,group={0,
1})]
at
org.apache.calcite.plan.volcano.VolcanoRuleCall.onMatch(VolcanoRuleCall.java:260)
at
org.apache.calcite.plan.volcano.VolcanoPlanner.findBestExp(VolcanoPlanner.java:634)
at
org.apache.calcite.tools.Programs.lambda$standard$3(Programs.java:286)
at
org.apache.calcite.tools.Programs$SequenceProgram.run(Programs.java:346)
at org.apache.calcite.prepare.Prepare.optimize(Prepare.java:165)
at
org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:290)
at
org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:207)
at
org.apache.calcite.prepare.CalcitePrepareImpl.prepare2_(CalcitePrepareImpl.java:634)
at
org.apache.calcite.prepare.CalcitePrepareImpl.prepare_(CalcitePrepareImpl.java:498)
at
org.apache.calcite.prepare.CalcitePrepareImpl.prepareSql(CalcitePrepareImpl.java:468)
at
org.apache.calcite.jdbc.CalciteConnectionImpl.parseQuery(CalciteConnectionImpl.java:231)
at
org.apache.calcite.jdbc.CalciteMetaImpl.prepareAndExecute(CalciteMetaImpl.java:550)
at
org.apache.calcite.avatica.AvaticaConnection.prepareAndExecuteInternal(AvaticaConnection.java:675)
at
org.apache.calcite.avatica.AvaticaStatement.executeInternal(AvaticaStatement.java:156)
... 16 more
Next exception 1: [CIRCULAR REFERENCE SQLException]
Next exception 2: [CIRCULAR REFERENCE RuntimeException]
Next exception 3:
org.apache.calcite.util.mapping.Mappings$NoElementException: source #0 has no
target in mapping [size=1, sourceCount=2, targetCount=7, elements=[1:1]]
at
org.apache.calcite.util.mapping.Mappings$AbstractMapping.getTarget(Mappings.java:881)
at
org.apache.calcite.rel.rules.materialize.MaterializedViewAggregateRule.rewriteView(MaterializedViewAggregateRule.java:677)
at
org.apache.calcite.rel.rules.materialize.MaterializedViewRule.perform(MaterializedViewRule.java:485)
at
org.apache.calcite.rel.rules.materialize.MaterializedViewOnlyAggregateRule.onMatch(MaterializedViewOnlyAggregateRule.java:63)
at
org.apache.calcite.plan.volcano.VolcanoRuleCall.onMatch(VolcanoRuleCall.java:233)
at
org.apache.calcite.plan.volcano.VolcanoPlanner.findBestExp(VolcanoPlanner.java:634)
at
org.apache.calcite.tools.Programs.lambda$standard$3(Programs.java:286)
at
org.apache.calcite.tools.Programs$SequenceProgram.run(Programs.java:346)
at org.apache.calcite.prepare.Prepare.optimize(Prepare.java:165)
at
org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:290)
at
org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:207)
at
org.apache.calcite.prepare.CalcitePrepareImpl.prepare2_(CalcitePrepareImpl.java:634)
at
org.apache.calcite.prepare.CalcitePrepareImpl.prepare_(CalcitePrepareImpl.java:498)
at
org.apache.calcite.prepare.CalcitePrepareImpl.prepareSql(CalcitePrepareImpl.java:468)
at
org.apache.calcite.jdbc.CalciteConnectionImpl.parseQuery(CalciteConnectionImpl.java:231)
at
org.apache.calcite.jdbc.CalciteMetaImpl.prepareAndExecute(CalciteMetaImpl.java:550)
at
org.apache.calcite.avatica.AvaticaConnection.prepareAndExecuteInternal(AvaticaConnection.java:675)
at
org.apache.calcite.avatica.AvaticaStatement.executeInternal(AvaticaStatement.java:156)
... 16 more
Caused by: [CIRCULAR REFERENCE PlaceholderException]
{code}
was:
Test case
[jira] [Created] (CALCITE-3848) Materialized view rewriting fails for mv consisting of group by on join keys
Vineet Garg created CALCITE-3848:
Summary: Materialized view rewriting fails for mv consisting of
group by on join keys
Key: CALCITE-3848
URL: https://issues.apache.org/jira/browse/CALCITE-3848
Project: Calcite
Issue Type: Bug
Components: core
Reporter: Vineet Garg
Assignee: Vineet Garg
Test case
{code:java}
+ @Test public void testAggregateOnJoinKeys() {
+checkMaterialize(
+"select \"deptno\", \"empid\", \"salary\"\n"
++ "from \"emps\"\n"
++ "group by \"deptno\", \"empid\", \"salary\"",
+ "select \"empid\", \"depts\".\"deptno\" \n"
++ "from \"emps\"\n"
++ "join \"depts\" on \"depts\".\"deptno\" = \"empid\" group by
\"empid\", \"depts\".\"deptno\"",
+HR_FKUK_MODEL,
+CalciteAssert.checkResultContains(
+"EnumerableCalc(expr#0=[{inputs}], empid=[$t0], empid0=[$t0])\n"
+ + " EnumerableAggregate(group=[{1}])\n"
++ "EnumerableHashJoin(condition=[=($1, $3)],
joinType=[inner])\n"
++ " EnumerableTableScan(table=[[hr, m0]])"));
+ }
+
{code}
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Commented] (CALCITE-3824) JoinProjectTransposeRule should skip Projects containing windowing expression
[
https://issues.apache.org/jira/browse/CALCITE-3824?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17047901#comment-17047901
]
Vineet Garg commented on CALCITE-3824:
--
That sounds ok to me, we can keep the pull request closed.
> JoinProjectTransposeRule should skip Projects containing windowing expression
> -
>
> Key: CALCITE-3824
> URL: https://issues.apache.org/jira/browse/CALCITE-3824
> Project: Calcite
> Issue Type: Bug
> Components: core
>Reporter: Vineet Garg
>Assignee: Vineet Garg
>Priority: Major
> Labels: pull-request-available
> Time Spent: 20m
> Remaining Estimate: 0h
>
> This rule could push windowing expressions within join condition which
> doesn't make sense.
> For example
> {code:sql}
> select * from dept a
> join (select rank() over (order by name) as r, 1 + 1 from dept) as b
> on a.name = b.r
> {code}
> Above query produces following plan after the rule
> {code}
> LogicalProject(DEPTNO=[$0], NAME=[$1], R=[$3], EXPR$1=[$4])
> LogicalProject(DEPTNO=[$0], NAME=[$1], NAME0=[CAST($1):BIGINT NOT NULL],
> R=[RANK() OVER (ORDER BY $3 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT
> ROW)], EXPR$1=[+(1, 1)])
> LogicalJoin(condition=[=(CAST($1):BIGINT NOT NULL, RANK() OVER (ORDER BY
> $3 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW))], joinType=[inner])
> LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
> LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
> {code}
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Commented] (CALCITE-3824) JoinProjectTransposeRule should skip Projects containing windowing expression
[
https://issues.apache.org/jira/browse/CALCITE-3824?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17047860#comment-17047860
]
Vineet Garg commented on CALCITE-3824:
--
[~jcamachorodriguez] Pull request for this jira is closed, since this jira is
reopened does that pull request need to be reopened as well?
> JoinProjectTransposeRule should skip Projects containing windowing expression
> -
>
> Key: CALCITE-3824
> URL: https://issues.apache.org/jira/browse/CALCITE-3824
> Project: Calcite
> Issue Type: Bug
> Components: core
>Reporter: Vineet Garg
>Assignee: Vineet Garg
>Priority: Major
> Labels: pull-request-available
> Time Spent: 20m
> Remaining Estimate: 0h
>
> This rule could push windowing expressions within join condition which
> doesn't make sense.
> For example
> {code:sql}
> select * from dept a
> join (select rank() over (order by name) as r, 1 + 1 from dept) as b
> on a.name = b.r
> {code}
> Above query produces following plan after the rule
> {code}
> LogicalProject(DEPTNO=[$0], NAME=[$1], R=[$3], EXPR$1=[$4])
> LogicalProject(DEPTNO=[$0], NAME=[$1], NAME0=[CAST($1):BIGINT NOT NULL],
> R=[RANK() OVER (ORDER BY $3 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT
> ROW)], EXPR$1=[+(1, 1)])
> LogicalJoin(condition=[=(CAST($1):BIGINT NOT NULL, RANK() OVER (ORDER BY
> $3 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW))], joinType=[inner])
> LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
> LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
> {code}
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Commented] (CALCITE-3824) JoinProjectTransposeRule should skip Projects containing windowing expression
[
https://issues.apache.org/jira/browse/CALCITE-3824?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17045077#comment-17045077
]
Vineet Garg commented on CALCITE-3824:
--
[~jcamachorodriguez] Did you mean to close CALCITE-3734 instead of this one?
> JoinProjectTransposeRule should skip Projects containing windowing expression
> -
>
> Key: CALCITE-3824
> URL: https://issues.apache.org/jira/browse/CALCITE-3824
> Project: Calcite
> Issue Type: Bug
> Components: core
>Reporter: Vineet Garg
>Assignee: Vineet Garg
>Priority: Major
> Fix For: 1.23.0
>
>
> This rule could push windowing expressions within join condition which
> doesn't make sense.
> For example
> {code:sql}
> select * from dept a
> join (select rank() over (order by name) as r, 1 + 1 from dept) as b
> on a.name = b.r
> {code}
> Above query produces following plan after the rule
> {code}
> LogicalProject(DEPTNO=[$0], NAME=[$1], R=[$3], EXPR$1=[$4])
> LogicalProject(DEPTNO=[$0], NAME=[$1], NAME0=[CAST($1):BIGINT NOT NULL],
> R=[RANK() OVER (ORDER BY $3 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT
> ROW)], EXPR$1=[+(1, 1)])
> LogicalJoin(condition=[=(CAST($1):BIGINT NOT NULL, RANK() OVER (ORDER BY
> $3 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW))], joinType=[inner])
> LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
> LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
> {code}
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Created] (CALCITE-3824) JoinProjectTransposeRule should skip Projects containing windowing expression
Vineet Garg created CALCITE-3824:
Summary: JoinProjectTransposeRule should skip Projects containing
windowing expression
Key: CALCITE-3824
URL: https://issues.apache.org/jira/browse/CALCITE-3824
Project: Calcite
Issue Type: Bug
Components: core
Reporter: Vineet Garg
Assignee: Vineet Garg
This rule could push windowing expressions within join condition which doesn't
make sense.
For example
{code:sql}
select * from dept a
join (select rank() over (order by name) as r, 1 + 1 from dept) as b
on a.name = b.r
{code}
Above query produces following plan after the rule
{code}
LogicalProject(DEPTNO=[$0], NAME=[$1], R=[$3], EXPR$1=[$4])
LogicalProject(DEPTNO=[$0], NAME=[$1], NAME0=[CAST($1):BIGINT NOT NULL],
R=[RANK() OVER (ORDER BY $3 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT
ROW)], EXPR$1=[+(1, 1)])
LogicalJoin(condition=[=(CAST($1):BIGINT NOT NULL, RANK() OVER (ORDER BY $3
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW))], joinType=[inner])
LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
{code}
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Commented] (CALCITE-3734) MySQL JDBC rewrite is producing queries with CHAR with range beyond 255
[ https://issues.apache.org/jira/browse/CALCITE-3734?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17044870#comment-17044870 ] Vineet Garg commented on CALCITE-3734: -- bq. let's add a mechanism to add per-provider, per-datatype limits. Looks like this mechanism is already added with CALCITE-2713 > MySQL JDBC rewrite is producing queries with CHAR with range beyond 255 > --- > > Key: CALCITE-3734 > URL: https://issues.apache.org/jira/browse/CALCITE-3734 > Project: Calcite > Issue Type: Bug > Components: jdbc-adapter >Reporter: Vineet Garg >Assignee: Vineet Garg >Priority: Major > Labels: pull-request-available > Time Spent: 10m > Remaining Estimate: 0h > > Queries containing cast to varchar/string is rewritten into cast to CHAR with > range beyond 255 causing query failure. This range/precision should be > limited to 255. > I will provide a test case later. -- This message was sent by Atlassian Jira (v8.3.4#803005)
[jira] [Created] (CALCITE-3734) MySQL JDBC rewrite is producing queries with CHAR with range beyond 255
Vineet Garg created CALCITE-3734: Summary: MySQL JDBC rewrite is producing queries with CHAR with range beyond 255 Key: CALCITE-3734 URL: https://issues.apache.org/jira/browse/CALCITE-3734 Project: Calcite Issue Type: Bug Components: jdbc-adapter Reporter: Vineet Garg Assignee: Vineet Garg Queries containing cast to varchar/string is rewritten into cast to CHAR with range beyond 255 causing query failure. This range/precision should be limited to 255. I will provide a test case later. -- This message was sent by Atlassian Jira (v8.3.4#803005)
[jira] [Created] (CALCITE-3733) MySQL JDBC rewrite should rewrite CAST as TIMESTAMP to CAST as DATETIME
Vineet Garg created CALCITE-3733: Summary: MySQL JDBC rewrite should rewrite CAST as TIMESTAMP to CAST as DATETIME Key: CALCITE-3733 URL: https://issues.apache.org/jira/browse/CALCITE-3733 Project: Calcite Issue Type: Bug Components: jdbc-adapter Reporter: Vineet Garg Assignee: Vineet Garg Queries containing cast timestamp literal is rewritten into cast as TIMESTAMP. This syntax is not supported with MySql. Since TIMESTAMP is equivalent to DATETIME it should be rewritten into CAST as DATETIME. I will provide a test case later. -- This message was sent by Atlassian Jira (v8.3.4#803005)
[jira] [Commented] (CALCITE-3031) Support for correlated ANY/SOME/ALL sub-query
[
https://issues.apache.org/jira/browse/CALCITE-3031?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16897627#comment-16897627
]
Vineet Garg commented on CALCITE-3031:
--
[~julianhyde] I have created CALCITE-3222 to fix these issues. Pull request
[#1345|https://github.com/apache/calcite/pull/1345/] should fix these issues.
Let me know if you have any comments/suggestions.
> Support for correlated ANY/SOME/ALL sub-query
> -
>
> Key: CALCITE-3031
> URL: https://issues.apache.org/jira/browse/CALCITE-3031
> Project: Calcite
> Issue Type: Bug
> Components: core
>Reporter: Haisheng Yuan
>Assignee: Vineet Garg
>Priority: Major
> Labels: pull-request-available
> Fix For: 1.21.0
>
> Time Spent: 50m
> Remaining Estimate: 0h
>
> Repro:
> {code:java}
> @Test public void testSelectAnyCorrelated() {
> final String sql = "select empno > any(select deptno from dept where
> emp.job = dept.name) from emp\n"
> ;
> checkSubQuery(sql).withLateDecorrelation(true).check();
> }
> {code}
> Error:
> {code:java}
> java.lang.AssertionError: correlation id $cor0 not found in correlation list
> []
> at org.apache.calcite.util.Litmus$1.fail(Litmus.java:31)
> at
> org.apache.calcite.rex.RexChecker.visitCorrelVariable(RexChecker.java:174)
> at
> org.apache.calcite.rex.RexChecker.visitCorrelVariable(RexChecker.java:57)
> at
> org.apache.calcite.rex.RexCorrelVariable.accept(RexCorrelVariable.java:47)
> at
> org.apache.calcite.rex.RexVisitorImpl.visitFieldAccess(RexVisitorImpl.java:98)
> at
> org.apache.calcite.rex.RexChecker.visitFieldAccess(RexChecker.java:149)
> at
> org.apache.calcite.rex.RexChecker.visitFieldAccess(RexChecker.java:57)
> at org.apache.calcite.rex.RexFieldAccess.accept(RexFieldAccess.java:81)
> at org.apache.calcite.rex.RexChecker.visitCall(RexChecker.java:140)
> at org.apache.calcite.rex.RexChecker.visitCall(RexChecker.java:57)
> at org.apache.calcite.rex.RexCall.accept(RexCall.java:191)
> at org.apache.calcite.rel.core.Filter.isValid(Filter.java:120)
> at
> org.apache.calcite.test.SqlToRelConverterTest$RelValidityChecker.visit(SqlToRelConverterTest.java:3312)
> at org.apache.calcite.rel.SingleRel.childrenAccept(SingleRel.java:72)
> at org.apache.calcite.rel.RelVisitor.visit(RelVisitor.java:44)
> {code}
> The plan after SubQueryRemoveRule is:
> {code:xml}
> LogicalProject(EXPR$0=[CAST(OR(AND(IS TRUE(>($0, $9)), <>($10, 0)),
> AND(>($10, $11), null, <>($10, 0), IS NOT TRUE(>($0, $9))), AND(>($0, $9),
> <>($10, 0), IS NOT TRUE(>($0, $9)), <=($10, $11:BOOLEAN NOT NULL])
> LogicalJoin(condition=[true], joinType=[inner])
> LogicalTableScan(table=[[CATALOG, SALES, EMP]])
> LogicalAggregate(group=[{}], m=[MIN($0)], c=[COUNT()], d=[COUNT($0)])
> LogicalProject(DEPTNO=[$0])
> LogicalFilter(condition=[=($cor0.JOB, $1)])
> LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
> {code}
> It should be a Correlate, instead of a Join.
--
This message was sent by Atlassian JIRA
(v7.6.14#76016)
[jira] [Created] (CALCITE-3222) Fix code style issues introduced by CALCITE-3031
Vineet Garg created CALCITE-3222: Summary: Fix code style issues introduced by CALCITE-3031 Key: CALCITE-3222 URL: https://issues.apache.org/jira/browse/CALCITE-3222 Project: Calcite Issue Type: Task Reporter: Vineet Garg Assignee: Vineet Garg -- This message was sent by Atlassian JIRA (v7.6.14#76016)
[jira] [Commented] (CALCITE-3031) Support for correlated ANY/SOME/ALL sub-query
[
https://issues.apache.org/jira/browse/CALCITE-3031?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16889587#comment-16889587
]
Vineet Garg commented on CALCITE-3031:
--
[~hyuan] [~julianhyde] Can you take a look please?
> Support for correlated ANY/SOME/ALL sub-query
> -
>
> Key: CALCITE-3031
> URL: https://issues.apache.org/jira/browse/CALCITE-3031
> Project: Calcite
> Issue Type: Bug
> Components: core
>Reporter: Haisheng Yuan
>Assignee: Vineet Garg
>Priority: Major
>
> Repro:
> {code:java}
> @Test public void testSelectAnyCorrelated() {
> final String sql = "select empno > any(select deptno from dept where
> emp.job = dept.name) from emp\n"
> ;
> checkSubQuery(sql).withLateDecorrelation(true).check();
> }
> {code}
> Error:
> {code:java}
> java.lang.AssertionError: correlation id $cor0 not found in correlation list
> []
> at org.apache.calcite.util.Litmus$1.fail(Litmus.java:31)
> at
> org.apache.calcite.rex.RexChecker.visitCorrelVariable(RexChecker.java:174)
> at
> org.apache.calcite.rex.RexChecker.visitCorrelVariable(RexChecker.java:57)
> at
> org.apache.calcite.rex.RexCorrelVariable.accept(RexCorrelVariable.java:47)
> at
> org.apache.calcite.rex.RexVisitorImpl.visitFieldAccess(RexVisitorImpl.java:98)
> at
> org.apache.calcite.rex.RexChecker.visitFieldAccess(RexChecker.java:149)
> at
> org.apache.calcite.rex.RexChecker.visitFieldAccess(RexChecker.java:57)
> at org.apache.calcite.rex.RexFieldAccess.accept(RexFieldAccess.java:81)
> at org.apache.calcite.rex.RexChecker.visitCall(RexChecker.java:140)
> at org.apache.calcite.rex.RexChecker.visitCall(RexChecker.java:57)
> at org.apache.calcite.rex.RexCall.accept(RexCall.java:191)
> at org.apache.calcite.rel.core.Filter.isValid(Filter.java:120)
> at
> org.apache.calcite.test.SqlToRelConverterTest$RelValidityChecker.visit(SqlToRelConverterTest.java:3312)
> at org.apache.calcite.rel.SingleRel.childrenAccept(SingleRel.java:72)
> at org.apache.calcite.rel.RelVisitor.visit(RelVisitor.java:44)
> {code}
> The plan after SubQueryRemoveRule is:
> {code:xml}
> LogicalProject(EXPR$0=[CAST(OR(AND(IS TRUE(>($0, $9)), <>($10, 0)),
> AND(>($10, $11), null, <>($10, 0), IS NOT TRUE(>($0, $9))), AND(>($0, $9),
> <>($10, 0), IS NOT TRUE(>($0, $9)), <=($10, $11:BOOLEAN NOT NULL])
> LogicalJoin(condition=[true], joinType=[inner])
> LogicalTableScan(table=[[CATALOG, SALES, EMP]])
> LogicalAggregate(group=[{}], m=[MIN($0)], c=[COUNT()], d=[COUNT($0)])
> LogicalProject(DEPTNO=[$0])
> LogicalFilter(condition=[=($cor0.JOB, $1)])
> LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
> {code}
> It should be a Correlate, instead of a Join.
--
This message was sent by Atlassian JIRA
(v7.6.14#76016)
[jira] [Commented] (CALCITE-3031) Support for correlated ANY/SOME/ALL sub-query
[
https://issues.apache.org/jira/browse/CALCITE-3031?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16887416#comment-16887416
]
Vineet Garg commented on CALCITE-3031:
--
[~julianhyde] I have updated the Jira title as well as commit message to make
it more clear. Let me know if this is still not clear.
> Support for correlated ANY/SOME/ALL sub-query
> -
>
> Key: CALCITE-3031
> URL: https://issues.apache.org/jira/browse/CALCITE-3031
> Project: Calcite
> Issue Type: Bug
> Components: core
>Reporter: Haisheng Yuan
>Assignee: Vineet Garg
>Priority: Major
>
> Repro:
> {code:java}
> @Test public void testSelectAnyCorrelated() {
> final String sql = "select empno > any(select deptno from dept where
> emp.job = dept.name) from emp\n"
> ;
> checkSubQuery(sql).withLateDecorrelation(true).check();
> }
> {code}
> Error:
> {code:java}
> java.lang.AssertionError: correlation id $cor0 not found in correlation list
> []
> at org.apache.calcite.util.Litmus$1.fail(Litmus.java:31)
> at
> org.apache.calcite.rex.RexChecker.visitCorrelVariable(RexChecker.java:174)
> at
> org.apache.calcite.rex.RexChecker.visitCorrelVariable(RexChecker.java:57)
> at
> org.apache.calcite.rex.RexCorrelVariable.accept(RexCorrelVariable.java:47)
> at
> org.apache.calcite.rex.RexVisitorImpl.visitFieldAccess(RexVisitorImpl.java:98)
> at
> org.apache.calcite.rex.RexChecker.visitFieldAccess(RexChecker.java:149)
> at
> org.apache.calcite.rex.RexChecker.visitFieldAccess(RexChecker.java:57)
> at org.apache.calcite.rex.RexFieldAccess.accept(RexFieldAccess.java:81)
> at org.apache.calcite.rex.RexChecker.visitCall(RexChecker.java:140)
> at org.apache.calcite.rex.RexChecker.visitCall(RexChecker.java:57)
> at org.apache.calcite.rex.RexCall.accept(RexCall.java:191)
> at org.apache.calcite.rel.core.Filter.isValid(Filter.java:120)
> at
> org.apache.calcite.test.SqlToRelConverterTest$RelValidityChecker.visit(SqlToRelConverterTest.java:3312)
> at org.apache.calcite.rel.SingleRel.childrenAccept(SingleRel.java:72)
> at org.apache.calcite.rel.RelVisitor.visit(RelVisitor.java:44)
> {code}
> The plan after SubQueryRemoveRule is:
> {code:xml}
> LogicalProject(EXPR$0=[CAST(OR(AND(IS TRUE(>($0, $9)), <>($10, 0)),
> AND(>($10, $11), null, <>($10, 0), IS NOT TRUE(>($0, $9))), AND(>($0, $9),
> <>($10, 0), IS NOT TRUE(>($0, $9)), <=($10, $11:BOOLEAN NOT NULL])
> LogicalJoin(condition=[true], joinType=[inner])
> LogicalTableScan(table=[[CATALOG, SALES, EMP]])
> LogicalAggregate(group=[{}], m=[MIN($0)], c=[COUNT()], d=[COUNT($0)])
> LogicalProject(DEPTNO=[$0])
> LogicalFilter(condition=[=($cor0.JOB, $1)])
> LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
> {code}
> It should be a Correlate, instead of a Join.
--
This message was sent by Atlassian JIRA
(v7.6.14#76016)
[jira] [Updated] (CALCITE-3031) Support for correlated ANY/SOME/ALL sub-query
[
https://issues.apache.org/jira/browse/CALCITE-3031?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Vineet Garg updated CALCITE-3031:
-
Summary: Support for correlated ANY/SOME/ALL sub-query (was:
AssertionError: correlation id $cor0 not found in correlation list [])
> Support for correlated ANY/SOME/ALL sub-query
> -
>
> Key: CALCITE-3031
> URL: https://issues.apache.org/jira/browse/CALCITE-3031
> Project: Calcite
> Issue Type: Bug
> Components: core
>Reporter: Haisheng Yuan
>Assignee: Vineet Garg
>Priority: Major
>
> Repro:
> {code:java}
> @Test public void testSelectAnyCorrelated() {
> final String sql = "select empno > any(select deptno from dept where
> emp.job = dept.name) from emp\n"
> ;
> checkSubQuery(sql).withLateDecorrelation(true).check();
> }
> {code}
> Error:
> {code:java}
> java.lang.AssertionError: correlation id $cor0 not found in correlation list
> []
> at org.apache.calcite.util.Litmus$1.fail(Litmus.java:31)
> at
> org.apache.calcite.rex.RexChecker.visitCorrelVariable(RexChecker.java:174)
> at
> org.apache.calcite.rex.RexChecker.visitCorrelVariable(RexChecker.java:57)
> at
> org.apache.calcite.rex.RexCorrelVariable.accept(RexCorrelVariable.java:47)
> at
> org.apache.calcite.rex.RexVisitorImpl.visitFieldAccess(RexVisitorImpl.java:98)
> at
> org.apache.calcite.rex.RexChecker.visitFieldAccess(RexChecker.java:149)
> at
> org.apache.calcite.rex.RexChecker.visitFieldAccess(RexChecker.java:57)
> at org.apache.calcite.rex.RexFieldAccess.accept(RexFieldAccess.java:81)
> at org.apache.calcite.rex.RexChecker.visitCall(RexChecker.java:140)
> at org.apache.calcite.rex.RexChecker.visitCall(RexChecker.java:57)
> at org.apache.calcite.rex.RexCall.accept(RexCall.java:191)
> at org.apache.calcite.rel.core.Filter.isValid(Filter.java:120)
> at
> org.apache.calcite.test.SqlToRelConverterTest$RelValidityChecker.visit(SqlToRelConverterTest.java:3312)
> at org.apache.calcite.rel.SingleRel.childrenAccept(SingleRel.java:72)
> at org.apache.calcite.rel.RelVisitor.visit(RelVisitor.java:44)
> {code}
> The plan after SubQueryRemoveRule is:
> {code:xml}
> LogicalProject(EXPR$0=[CAST(OR(AND(IS TRUE(>($0, $9)), <>($10, 0)),
> AND(>($10, $11), null, <>($10, 0), IS NOT TRUE(>($0, $9))), AND(>($0, $9),
> <>($10, 0), IS NOT TRUE(>($0, $9)), <=($10, $11:BOOLEAN NOT NULL])
> LogicalJoin(condition=[true], joinType=[inner])
> LogicalTableScan(table=[[CATALOG, SALES, EMP]])
> LogicalAggregate(group=[{}], m=[MIN($0)], c=[COUNT()], d=[COUNT($0)])
> LogicalProject(DEPTNO=[$0])
> LogicalFilter(condition=[=($cor0.JOB, $1)])
> LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
> {code}
> It should be a Correlate, instead of a Join.
--
This message was sent by Atlassian JIRA
(v7.6.14#76016)
[jira] [Commented] (CALCITE-3031) AssertionError: correlation id $cor0 not found in correlation list []
[
https://issues.apache.org/jira/browse/CALCITE-3031?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16886652#comment-16886652
]
Vineet Garg commented on CALCITE-3031:
--
Pull request is available at https://github.com/apache/calcite/pull/1320
> AssertionError: correlation id $cor0 not found in correlation list []
> -
>
> Key: CALCITE-3031
> URL: https://issues.apache.org/jira/browse/CALCITE-3031
> Project: Calcite
> Issue Type: Bug
> Components: core
>Reporter: Haisheng Yuan
>Assignee: Vineet Garg
>Priority: Major
>
> Repro:
> {code:java}
> @Test public void testSelectAnyCorrelated() {
> final String sql = "select empno > any(select deptno from dept where
> emp.job = dept.name) from emp\n"
> ;
> checkSubQuery(sql).withLateDecorrelation(true).check();
> }
> {code}
> Error:
> {code:java}
> java.lang.AssertionError: correlation id $cor0 not found in correlation list
> []
> at org.apache.calcite.util.Litmus$1.fail(Litmus.java:31)
> at
> org.apache.calcite.rex.RexChecker.visitCorrelVariable(RexChecker.java:174)
> at
> org.apache.calcite.rex.RexChecker.visitCorrelVariable(RexChecker.java:57)
> at
> org.apache.calcite.rex.RexCorrelVariable.accept(RexCorrelVariable.java:47)
> at
> org.apache.calcite.rex.RexVisitorImpl.visitFieldAccess(RexVisitorImpl.java:98)
> at
> org.apache.calcite.rex.RexChecker.visitFieldAccess(RexChecker.java:149)
> at
> org.apache.calcite.rex.RexChecker.visitFieldAccess(RexChecker.java:57)
> at org.apache.calcite.rex.RexFieldAccess.accept(RexFieldAccess.java:81)
> at org.apache.calcite.rex.RexChecker.visitCall(RexChecker.java:140)
> at org.apache.calcite.rex.RexChecker.visitCall(RexChecker.java:57)
> at org.apache.calcite.rex.RexCall.accept(RexCall.java:191)
> at org.apache.calcite.rel.core.Filter.isValid(Filter.java:120)
> at
> org.apache.calcite.test.SqlToRelConverterTest$RelValidityChecker.visit(SqlToRelConverterTest.java:3312)
> at org.apache.calcite.rel.SingleRel.childrenAccept(SingleRel.java:72)
> at org.apache.calcite.rel.RelVisitor.visit(RelVisitor.java:44)
> {code}
> The plan after SubQueryRemoveRule is:
> {code:xml}
> LogicalProject(EXPR$0=[CAST(OR(AND(IS TRUE(>($0, $9)), <>($10, 0)),
> AND(>($10, $11), null, <>($10, 0), IS NOT TRUE(>($0, $9))), AND(>($0, $9),
> <>($10, 0), IS NOT TRUE(>($0, $9)), <=($10, $11:BOOLEAN NOT NULL])
> LogicalJoin(condition=[true], joinType=[inner])
> LogicalTableScan(table=[[CATALOG, SALES, EMP]])
> LogicalAggregate(group=[{}], m=[MIN($0)], c=[COUNT()], d=[COUNT($0)])
> LogicalProject(DEPTNO=[$0])
> LogicalFilter(condition=[=($cor0.JOB, $1)])
> LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
> {code}
> It should be a Correlate, instead of a Join.
--
This message was sent by Atlassian JIRA
(v7.6.14#76016)
[jira] [Commented] (CALCITE-3132) Simply expressions in LogicalFilter generated for subqueries with quantified predicate
[
https://issues.apache.org/jira/browse/CALCITE-3132?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16866077#comment-16866077
]
Vineet Garg commented on CALCITE-3132:
--
[~hyuan] You are correct. Since this happens to be COUNT in this case we don't
really need <>($10,0)
> Simply expressions in LogicalFilter generated for subqueries with quantified
> predicate
> --
>
> Key: CALCITE-3132
> URL: https://issues.apache.org/jira/browse/CALCITE-3132
> Project: Calcite
> Issue Type: Bug
> Components: core
>Reporter: Vineet Garg
>Assignee: Vineet Garg
>Priority: Major
> Labels: sub-query
>
> Following test in RelOptRulesTest generate plan with LogicalFilter
> {code}
> @Test public void testSome() {
> final String sql = "select * from emp e1\n"
> + " where e1.empno > SOME (select deptno from dept)";
> checkSubQuery(sql).withLateDecorrelation(true).check();
> }
> {code}
> {code}
> LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4],
> SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
> LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4],
> SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
> LogicalFilter(condition=[OR(AND(IS TRUE(>($0, $9)), <>($10, 0)),
> AND(>($10, $11), null, <>($10, 0), IS NOT TRUE(>($0, $9))), AND(>($0, $9),
> <>($10, 0), IS NOT TRUE(>($0, $9)), <=($10, $11)))])
> LogicalJoin(condition=[true], joinType=[inner])
> LogicalTableScan(table=[[CATALOG, SALES, EMP]])
> LogicalAggregate(group=[{}], m=[MIN($0)], c=[COUNT()], d=[COUNT($0)])
> LogicalProject(DEPTNO=[$0])
> LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
> {code}
> Note that LogicalFilter has condition which should be further simplified
> {code}
> (condition=[OR(AND(IS TRUE(>($0, $9)), <>($10, 0)), AND(>($10, $11), null,
> <>($10, 0), IS NOT TRUE(>($0, $9))), AND(>($0, $9), <>($10, 0), IS NOT
> TRUE(>($0, $9)), <=($10, $11)))])
> {code}
> If {{AND(IS TRUE(>($0, $9)), <>($10, 0))}} is true so will be {{AND(IS
> TRUE(>($0, $9)), <>($10, 0))}}
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
[jira] [Commented] (CALCITE-3132) Simply expressions in LogicalFilter generated for subqueries with quantified predicate
[
https://issues.apache.org/jira/browse/CALCITE-3132?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16866063#comment-16866063
]
Vineet Garg commented on CALCITE-3132:
--
[~hyuan] I believe since this condition is part of LogicalFilter {{AND(>($0,
$9), <>($10, 0))}} should be enough for this case.
> Simply expressions in LogicalFilter generated for subqueries with quantified
> predicate
> --
>
> Key: CALCITE-3132
> URL: https://issues.apache.org/jira/browse/CALCITE-3132
> Project: Calcite
> Issue Type: Bug
> Components: core
>Reporter: Vineet Garg
>Assignee: Vineet Garg
>Priority: Major
> Labels: sub-query
>
> Following test in RelOptRulesTest generate plan with LogicalFilter
> {code}
> @Test public void testSome() {
> final String sql = "select * from emp e1\n"
> + " where e1.empno > SOME (select deptno from dept)";
> checkSubQuery(sql).withLateDecorrelation(true).check();
> }
> {code}
> {code}
> LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4],
> SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
> LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4],
> SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
> LogicalFilter(condition=[OR(AND(IS TRUE(>($0, $9)), <>($10, 0)),
> AND(>($10, $11), null, <>($10, 0), IS NOT TRUE(>($0, $9))), AND(>($0, $9),
> <>($10, 0), IS NOT TRUE(>($0, $9)), <=($10, $11)))])
> LogicalJoin(condition=[true], joinType=[inner])
> LogicalTableScan(table=[[CATALOG, SALES, EMP]])
> LogicalAggregate(group=[{}], m=[MIN($0)], c=[COUNT()], d=[COUNT($0)])
> LogicalProject(DEPTNO=[$0])
> LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
> {code}
> Note that LogicalFilter has condition which should be further simplified
> {code}
> (condition=[OR(AND(IS TRUE(>($0, $9)), <>($10, 0)), AND(>($10, $11), null,
> <>($10, 0), IS NOT TRUE(>($0, $9))), AND(>($0, $9), <>($10, 0), IS NOT
> TRUE(>($0, $9)), <=($10, $11)))])
> {code}
> If {{AND(IS TRUE(>($0, $9)), <>($10, 0))}} is true so will be {{AND(IS
> TRUE(>($0, $9)), <>($10, 0))}}
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
[jira] [Created] (CALCITE-3132) Simply expressions in LogicalFilter generated for subqueries with quantified predicate
Vineet Garg created CALCITE-3132:
Summary: Simply expressions in LogicalFilter generated for
subqueries with quantified predicate
Key: CALCITE-3132
URL: https://issues.apache.org/jira/browse/CALCITE-3132
Project: Calcite
Issue Type: Bug
Components: core
Reporter: Vineet Garg
Assignee: Vineet Garg
Following test in RelOptRulesTest generate plan with LogicalFilter
{code}
@Test public void testSome() {
final String sql = "select * from emp e1\n"
+ " where e1.empno > SOME (select deptno from dept)";
checkSubQuery(sql).withLateDecorrelation(true).check();
}
{code}
{code}
LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4],
SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4],
SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
LogicalFilter(condition=[OR(AND(IS TRUE(>($0, $9)), <>($10, 0)), AND(>($10,
$11), null, <>($10, 0), IS NOT TRUE(>($0, $9))), AND(>($0, $9), <>($10, 0), IS
NOT TRUE(>($0, $9)), <=($10, $11)))])
LogicalJoin(condition=[true], joinType=[inner])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
LogicalAggregate(group=[{}], m=[MIN($0)], c=[COUNT()], d=[COUNT($0)])
LogicalProject(DEPTNO=[$0])
LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
{code}
Note that LogicalFilter has condition which should be further simplified
{code}
(condition=[OR(AND(IS TRUE(>($0, $9)), <>($10, 0)), AND(>($10, $11), null,
<>($10, 0), IS NOT TRUE(>($0, $9))), AND(>($0, $9), <>($10, 0), IS NOT
TRUE(>($0, $9)), <=($10, $11)))])
{code}
If {{AND(IS TRUE(>($0, $9)), <>($10, 0))}} is true so will be {{AND(IS
TRUE(>($0, $9)), <>($10, 0))}}
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
[jira] [Commented] (CALCITE-3028) Support FULL OUTER JOIN with AggregateJoinTransposeRule
[ https://issues.apache.org/jira/browse/CALCITE-3028?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16841568#comment-16841568 ] Vineet Garg commented on CALCITE-3028: -- Thanks [~hyuan] > Support FULL OUTER JOIN with AggregateJoinTransposeRule > --- > > Key: CALCITE-3028 > URL: https://issues.apache.org/jira/browse/CALCITE-3028 > Project: Calcite > Issue Type: Improvement >Reporter: Vineet Garg >Assignee: Vineet Garg >Priority: Major > Labels: pull-request-available > Fix For: 1.20.0 > > Time Spent: 20m > Remaining Estimate: 0h > > This is continuation of CALCITE-3011, which supported LEFT OUTER and RIGHT > OUTER joins without aggregate functions. > FULL OUTER JOIN was not supported at the time due to CALCITE-3012 -- This message was sent by Atlassian JIRA (v7.6.3#76005)
[jira] [Commented] (CALCITE-3031) AssertionError: correlation id $cor0 not found in correlation list []
[
https://issues.apache.org/jira/browse/CALCITE-3031?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16829819#comment-16829819
]
Vineet Garg commented on CALCITE-3031:
--
[~hyuan] Support for correlated quantified predicates was on my todo list. I am
not sure if you are currently working on this one but I have a quick patch to
fix this at
[https://github.com/vineetgarg02/calcite/commit/45e15aebd6084883276b833a80c16996e59ab5a4]
This needs more testing of course and is not really ready to be merged.
Plan generated for this particular query with the patch is as follow. As far as
I could tell this looks correct to me but I'll appreciate your input.
{code:sql}
LogicalProject(EXPR$0=[CAST(OR(AND(IS TRUE(>($0, $10)), IS NOT TRUE(=($11,
0))), AND(IS TRUE(>($11, $12)), null, IS NOT TRUE(=($11, 0)), IS NOT TRUE(>($0,
$10))), AND(>($0, $10), IS NOT TRUE(=($11, 0)), IS NOT TRUE(>($0, $10)), IS NOT
TRUE(>($11, $12):BOOLEAN NOT NULL])
LogicalJoin(condition=[=($2, $9)], joinType=[left])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
LogicalAggregate(group=[{0}], m=[MIN($1)], c=[COUNT()], d=[COUNT($1)])
LogicalProject(NAME=[$1], DEPTNO=[$0])
LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
{code}
Please let me know if you haven't started working on this, I'll take this one.
Otherwise feel free to ignore my patch/comment.
> AssertionError: correlation id $cor0 not found in correlation list []
> -
>
> Key: CALCITE-3031
> URL: https://issues.apache.org/jira/browse/CALCITE-3031
> Project: Calcite
> Issue Type: Bug
> Components: core
>Reporter: Haisheng Yuan
>Assignee: Haisheng Yuan
>Priority: Major
>
> Repro:
> {code:java}
> @Test public void testSelectAnyCorrelated() {
> final String sql = "select empno > any(select deptno from dept where
> emp.job = dept.name) from emp\n"
> ;
> checkSubQuery(sql).withLateDecorrelation(true).check();
> }
> {code}
> Error:
> {code:java}
> java.lang.AssertionError: correlation id $cor0 not found in correlation list
> []
> at org.apache.calcite.util.Litmus$1.fail(Litmus.java:31)
> at
> org.apache.calcite.rex.RexChecker.visitCorrelVariable(RexChecker.java:174)
> at
> org.apache.calcite.rex.RexChecker.visitCorrelVariable(RexChecker.java:57)
> at
> org.apache.calcite.rex.RexCorrelVariable.accept(RexCorrelVariable.java:47)
> at
> org.apache.calcite.rex.RexVisitorImpl.visitFieldAccess(RexVisitorImpl.java:98)
> at
> org.apache.calcite.rex.RexChecker.visitFieldAccess(RexChecker.java:149)
> at
> org.apache.calcite.rex.RexChecker.visitFieldAccess(RexChecker.java:57)
> at org.apache.calcite.rex.RexFieldAccess.accept(RexFieldAccess.java:81)
> at org.apache.calcite.rex.RexChecker.visitCall(RexChecker.java:140)
> at org.apache.calcite.rex.RexChecker.visitCall(RexChecker.java:57)
> at org.apache.calcite.rex.RexCall.accept(RexCall.java:191)
> at org.apache.calcite.rel.core.Filter.isValid(Filter.java:120)
> at
> org.apache.calcite.test.SqlToRelConverterTest$RelValidityChecker.visit(SqlToRelConverterTest.java:3312)
> at org.apache.calcite.rel.SingleRel.childrenAccept(SingleRel.java:72)
> at org.apache.calcite.rel.RelVisitor.visit(RelVisitor.java:44)
> {code}
> The plan after SubQueryRemoveRule is:
> {code:xml}
> LogicalProject(EXPR$0=[CAST(OR(AND(IS TRUE(>($0, $9)), <>($10, 0)),
> AND(>($10, $11), null, <>($10, 0), IS NOT TRUE(>($0, $9))), AND(>($0, $9),
> <>($10, 0), IS NOT TRUE(>($0, $9)), <=($10, $11:BOOLEAN NOT NULL])
> LogicalJoin(condition=[true], joinType=[inner])
> LogicalTableScan(table=[[CATALOG, SALES, EMP]])
> LogicalAggregate(group=[{}], m=[MIN($0)], c=[COUNT()], d=[COUNT($0)])
> LogicalProject(DEPTNO=[$0])
> LogicalFilter(condition=[=($cor0.JOB, $1)])
> LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
> {code}
> It should be a Correlate, instead of a Join.
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
[jira] [Created] (CALCITE-3028) Support FULL OUTER JOIN with AggregateJoinTransposeRule
Vineet Garg created CALCITE-3028: Summary: Support FULL OUTER JOIN with AggregateJoinTransposeRule Key: CALCITE-3028 URL: https://issues.apache.org/jira/browse/CALCITE-3028 Project: Calcite Issue Type: Improvement Reporter: Vineet Garg Assignee: Vineet Garg This is continuation of CALCITE-3011, which supported LEFT OUTER and RIGHT OUTER joins without aggregate functions. FULL OUTER JOIN was not supported at the time due to CALCITE-3012 -- This message was sent by Atlassian JIRA (v7.6.3#76005)
[jira] [Commented] (CALCITE-1624) Inefficient plan for NOT IN correlated subqueries
[
https://issues.apache.org/jira/browse/CALCITE-1624?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16822248#comment-16822248
]
Vineet Garg commented on CALCITE-1624:
--
[~danny0405] The plan looks correct to me. {{IS NOT TRUE($12)}} will be true
for cases where there is no match for the left outer join, which is what NOT IN
is suppose to produce.
{{IS NOT TRUE}} will be true for following two cases:
* Correlated {{emp.job=dept.name}} is false. It means the set of values to
compare are empty for NOT IN making NOT IN predicate true.
* Correlated {{emp.job=dept.name}} is true but {{empno=deptno}} is false. It
means for the given correlated value none of the value matches for NOT IN
making NOT IN predicate true.
Hope this makes sense.
> Inefficient plan for NOT IN correlated subqueries
> -
>
> Key: CALCITE-1624
> URL: https://issues.apache.org/jira/browse/CALCITE-1624
> Project: Calcite
> Issue Type: Bug
> Components: core
>Reporter: Vineet Garg
>Assignee: Danny Chan
>Priority: Major
>
> I just noticed that {{NOT IN}} correlated subqueries produces an extra
> un-neccessary join after de-correlation (this is an addition to un-necessary
> joins reported in CALCITE-1494)
> Query
> {code:SQL}
> select sal from emp
> where empno NOT IN (
> select deptno from dept
> where emp.job = dept.name)
> {code}
> Plan after subquery remove rule:
> {code}
> LogicalProject(SAL=[$5])
> LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4],
> SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
> LogicalFilter(condition=[NOT(CASE(=($9, 0), false, IS NOT NULL($12),
> true, <($10, $9), true, false))])
> LogicalCorrelate(correlation=[$cor0], joinType=[LEFT],
> requiredColumns=[{2}])
> LogicalCorrelate(correlation=[$cor0], joinType=[LEFT],
> requiredColumns=[{2}])
> LogicalTableScan(table=[[CATALOG, SALES, EMP]])
> LogicalAggregate(group=[{}], c=[COUNT()], ck=[COUNT($0)])
> LogicalProject(DEPTNO=[$0])
> LogicalFilter(condition=[=($cor0.JOB, $1)])
> LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
> LogicalFilter(condition=[=($cor0.EMPNO, $0)])
> LogicalAggregate(group=[{0, 1}])
> LogicalProject(DEPTNO=[$0], i=[true])
> LogicalProject(DEPTNO=[$0])
> LogicalFilter(condition=[=($cor0.JOB, $1)])
> LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
> {code}
> Plan after de-correlation
> {code}
> LogicalProject(SAL=[$5])
> LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4],
> SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
> LogicalFilter(condition=[NOT(CASE(=($10, 0), false, IS NOT NULL($14),
> true, <($11, $10), true, false))])
> LogicalJoin(condition=[AND(=($0, $15), =($2, $13))], joinType=[left])
> LogicalJoin(condition=[=($2, $9)], joinType=[left])
> LogicalTableScan(table=[[CATALOG, SALES, EMP]])
> LogicalAggregate(group=[{0}], c=[COUNT()], ck=[COUNT($1)])
> LogicalProject(JOB=[$1], DEPTNO=[$0])
> LogicalProject(DEPTNO=[$0], JOB=[$2])
> LogicalJoin(condition=[=($2, $1)], joinType=[inner])
> LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
> LogicalAggregate(group=[{0}])
> LogicalProject(JOB=[$2])
> LogicalTableScan(table=[[CATALOG, SALES, EMP]])
> LogicalJoin(condition=[=($3, $0)], joinType=[inner]) // <==
> Un-necessary join
> LogicalProject(DEPTNO=[$0], JOB=[$1], $f2=[true])
> LogicalAggregate(group=[{0, 1}])
> LogicalProject(DEPTNO=[$0], JOB=[$2], i=[$1])
> LogicalProject(DEPTNO=[$0], i=[true], JOB=[$1])
> LogicalProject(DEPTNO=[$0], JOB=[$2])
> LogicalJoin(condition=[=($2, $1)], joinType=[inner])
> LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
> LogicalAggregate(group=[{0}])
> LogicalProject(JOB=[$2])
> LogicalTableScan(table=[[CATALOG, SALES, EMP]])
> LogicalAggregate(group=[{0}])
> LogicalProject(EMPNO=[$0])
> LogicalTableScan(table=[[CATALOG, SALES, EMP]])
> {code}
> As you can see in plan after de-correlation there is an un-necessary inner
> join.
> This is not reproducible on CALCITE-1494's branch. But since this is a
> separate issue from CALCITE-1494 I decided to open a separate JIRA.
> Feel free to mark is duplicate or close it if you think otherwise.
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
[jira] [Commented] (CALCITE-3011) Support outer joins with AggregateJoinTransposeRule
[
https://issues.apache.org/jira/browse/CALCITE-3011?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16822147#comment-16822147
]
Vineet Garg commented on CALCITE-3011:
--
[~jcamachorodriguez], [~hyuan] I have updated the pull request to add more
cases for outer join. I also found a bug in current logic for FULL OUTER JOIN
which is fixed in the pull request. But the issue is still not fixed due to
CALCITE-3012. The relevant test case is disabled until CALCITE-3012 is fixed.
> Support outer joins with AggregateJoinTransposeRule
> ---
>
> Key: CALCITE-3011
> URL: https://issues.apache.org/jira/browse/CALCITE-3011
> Project: Calcite
> Issue Type: Improvement
> Components: core
>Reporter: Vineet Garg
>Assignee: Vineet Garg
>Priority: Major
> Labels: pull-request-available
> Time Spent: 50m
> Remaining Estimate: 0h
>
> Currently {{AggregateJoinTransposeRule}} only support INNER join. Aggregates
> (at least the ones without aggregate functions) could be pushed through OUTER
> joins with current logic.
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
[jira] [Created] (CALCITE-3012) areColumnsUnique for FULL OUTER JOIN could return wrong answer when ignoreNulls is false
Vineet Garg created CALCITE-3012:
Summary: areColumnsUnique for FULL OUTER JOIN could return wrong
answer when ignoreNulls is false
Key: CALCITE-3012
URL: https://issues.apache.org/jira/browse/CALCITE-3012
Project: Calcite
Issue Type: Bug
Components: core
Reporter: Vineet Garg
Assignee: Vineet Garg
Let's say set of columns passed to this API are join keys and there is one NULL
key coming from both the inputs. Following code will return true which is wrong
because the result of FULL. OUTER JOIN with NULL key on both side will produce
two rows with NULL. Even though this value in respective input is unique the
result of join may not be unique.
{code:java}
Boolean leftUnique = mq.areColumnsUnique(left, leftColumns, ignoreNulls);
Boolean rightUnique = mq.areColumnsUnique(right, rightColumns, ignoreNulls);
if ((leftColumns.cardinality() > 0)
&& (rightColumns.cardinality() > 0)) {
if ((leftUnique == null) || (rightUnique == null)) {
return null;
} else {
return leftUnique && rightUnique;
}
}
{code}
{code:sql}
create table trepro(i int);
insert into trepro values(null);
select * from trepro t1 full outer join trepro t2 on t1.i=t2.i;
null, null
null, null
{code}
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
[jira] [Commented] (CALCITE-3011) Support outer joins with AggregateJoinTransposeRule
[
https://issues.apache.org/jira/browse/CALCITE-3011?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16821473#comment-16821473
]
Vineet Garg commented on CALCITE-3011:
--
Fixing this issue in {{AggregateJoinTransposeRule}} doesn't fully fix the wrong
plan. It looks like {{areColumnsUnique}} is returning true for FULL OUTER JOIN
which seems wrong.
> Support outer joins with AggregateJoinTransposeRule
> ---
>
> Key: CALCITE-3011
> URL: https://issues.apache.org/jira/browse/CALCITE-3011
> Project: Calcite
> Issue Type: Improvement
> Components: core
>Reporter: Vineet Garg
>Assignee: Vineet Garg
>Priority: Major
> Labels: pull-request-available
> Time Spent: 40m
> Remaining Estimate: 0h
>
> Currently {{AggregateJoinTransposeRule}} only support INNER join. Aggregates
> (at least the ones without aggregate functions) could be pushed through OUTER
> joins with current logic.
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
[jira] [Commented] (CALCITE-3011) Support outer joins with AggregateJoinTransposeRule
[
https://issues.apache.org/jira/browse/CALCITE-3011?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16821331#comment-16821331
]
Vineet Garg commented on CALCITE-3011:
--
Found a bug with full outer join.
{code:sql}
select e.mgr, d.mgr
from sales.emp as e
full outer join sales.emp as d on e.mgr = d.mgr
group by d.mgr, e.mgr
{code}
{noformat}
LogicalProject(MGR=[$1], MGR0=[$0])
LogicalProject(MGR0=[$1], MGR=[$0])
LogicalJoin(condition=[=($0, $1)], joinType=[full])
LogicalAggregate(group=[{3}])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
LogicalAggregate(group=[{3}])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
{noformat}
This is missing {{LogicalAggregate}} on top. This can produce wrong results if
both join keys have at least one NULL.
> Support outer joins with AggregateJoinTransposeRule
> ---
>
> Key: CALCITE-3011
> URL: https://issues.apache.org/jira/browse/CALCITE-3011
> Project: Calcite
> Issue Type: Improvement
> Components: core
>Reporter: Vineet Garg
>Assignee: Vineet Garg
>Priority: Major
> Labels: pull-request-available
> Time Spent: 40m
> Remaining Estimate: 0h
>
> Currently {{AggregateJoinTransposeRule}} only support INNER join. Aggregates
> (at least the ones without aggregate functions) could be pushed through OUTER
> joins with current logic.
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
[jira] [Commented] (CALCITE-1624) Inefficient plan for NOT IN correlated subqueries
[
https://issues.apache.org/jira/browse/CALCITE-1624?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16821194#comment-16821194
]
Vineet Garg commented on CALCITE-1624:
--
[~danny0405] Sure go ahead. You might want to confirm if this is still an issue.
> Inefficient plan for NOT IN correlated subqueries
> -
>
> Key: CALCITE-1624
> URL: https://issues.apache.org/jira/browse/CALCITE-1624
> Project: Calcite
> Issue Type: Bug
> Components: core
>Reporter: Vineet Garg
>Priority: Major
>
> I just noticed that {{NOT IN}} correlated subqueries produces an extra
> un-neccessary join after de-correlation (this is an addition to un-necessary
> joins reported in CALCITE-1494)
> Query
> {code:SQL}
> select sal from emp
> where empno NOT IN (
> select deptno from dept
> where emp.job = dept.name)
> {code}
> Plan after subquery remove rule:
> {code}
> LogicalProject(SAL=[$5])
> LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4],
> SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
> LogicalFilter(condition=[NOT(CASE(=($9, 0), false, IS NOT NULL($12),
> true, <($10, $9), true, false))])
> LogicalCorrelate(correlation=[$cor0], joinType=[LEFT],
> requiredColumns=[{2}])
> LogicalCorrelate(correlation=[$cor0], joinType=[LEFT],
> requiredColumns=[{2}])
> LogicalTableScan(table=[[CATALOG, SALES, EMP]])
> LogicalAggregate(group=[{}], c=[COUNT()], ck=[COUNT($0)])
> LogicalProject(DEPTNO=[$0])
> LogicalFilter(condition=[=($cor0.JOB, $1)])
> LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
> LogicalFilter(condition=[=($cor0.EMPNO, $0)])
> LogicalAggregate(group=[{0, 1}])
> LogicalProject(DEPTNO=[$0], i=[true])
> LogicalProject(DEPTNO=[$0])
> LogicalFilter(condition=[=($cor0.JOB, $1)])
> LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
> {code}
> Plan after de-correlation
> {code}
> LogicalProject(SAL=[$5])
> LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4],
> SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
> LogicalFilter(condition=[NOT(CASE(=($10, 0), false, IS NOT NULL($14),
> true, <($11, $10), true, false))])
> LogicalJoin(condition=[AND(=($0, $15), =($2, $13))], joinType=[left])
> LogicalJoin(condition=[=($2, $9)], joinType=[left])
> LogicalTableScan(table=[[CATALOG, SALES, EMP]])
> LogicalAggregate(group=[{0}], c=[COUNT()], ck=[COUNT($1)])
> LogicalProject(JOB=[$1], DEPTNO=[$0])
> LogicalProject(DEPTNO=[$0], JOB=[$2])
> LogicalJoin(condition=[=($2, $1)], joinType=[inner])
> LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
> LogicalAggregate(group=[{0}])
> LogicalProject(JOB=[$2])
> LogicalTableScan(table=[[CATALOG, SALES, EMP]])
> LogicalJoin(condition=[=($3, $0)], joinType=[inner]) // <==
> Un-necessary join
> LogicalProject(DEPTNO=[$0], JOB=[$1], $f2=[true])
> LogicalAggregate(group=[{0, 1}])
> LogicalProject(DEPTNO=[$0], JOB=[$2], i=[$1])
> LogicalProject(DEPTNO=[$0], i=[true], JOB=[$1])
> LogicalProject(DEPTNO=[$0], JOB=[$2])
> LogicalJoin(condition=[=($2, $1)], joinType=[inner])
> LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
> LogicalAggregate(group=[{0}])
> LogicalProject(JOB=[$2])
> LogicalTableScan(table=[[CATALOG, SALES, EMP]])
> LogicalAggregate(group=[{0}])
> LogicalProject(EMPNO=[$0])
> LogicalTableScan(table=[[CATALOG, SALES, EMP]])
> {code}
> As you can see in plan after de-correlation there is an un-necessary inner
> join.
> This is not reproducible on CALCITE-1494's branch. But since this is a
> separate issue from CALCITE-1494 I decided to open a separate JIRA.
> Feel free to mark is duplicate or close it if you think otherwise.
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
[jira] [Assigned] (CALCITE-3007) Type mismatch for ANY subquery in project
[
https://issues.apache.org/jira/browse/CALCITE-3007?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Vineet Garg reassigned CALCITE-3007:
Assignee: Vineet Garg
> Type mismatch for ANY subquery in project
> -
>
> Key: CALCITE-3007
> URL: https://issues.apache.org/jira/browse/CALCITE-3007
> Project: Calcite
> Issue Type: Bug
> Components: core
>Reporter: Haisheng Yuan
>Assignee: Vineet Garg
>Priority: Major
> Labels: pull-request-available, sub-query
> Time Spent: 10m
> Remaining Estimate: 0h
>
> Repro:
> {code:java}
> @Test public void testGtAnyInProject() {
> final String sql = "select name, \n"
> + " deptno > ANY (\n"
> + " select deptno from emp) \n"
> + " from dept";
> checkSubQuery(sql).withLateDecorrelation(true).check();
> }
> {code}
> Exception:
> {code:java}
> java.lang.AssertionError: Cannot add expression of different type to set:
> set type is RecordType(VARCHAR(10) NOT NULL NAME, BOOLEAN NOT NULL EXPR$1)
> NOT NULL
> expression type is RecordType(VARCHAR(10) NOT NULL NAME, BOOLEAN EXPR$1) NOT
> NULL
> set is rel#5:LogicalProject(input=HepRelVertex#4,NAME=$1,EXPR$1=> SOME($0, {
> LogicalProject(DEPTNO=[$7])
> LogicalTableScan(table=[[CATALOG, SALES, EMP]])
> }))
> expression is LogicalProject(NAME=[$1], EXPR$1=[OR(AND(IS TRUE(>($0, $2)),
> <>($3, 0)), AND(>($3, $4), null, <>($3, 0), IS NOT TRUE(>($0, $2))),
> AND(>($0, $2), <>($3, 0), IS NOT TRUE(>($0, $2)), <=($3, $4)))])
> LogicalJoin(condition=[true], joinType=[inner])
> LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
> LogicalAggregate(group=[{}], m=[MIN($0)], c=[COUNT()], d=[COUNT($0)])
> LogicalProject(DEPTNO=[$7])
> LogicalTableScan(table=[[CATALOG, SALES, EMP]])
> at
> org.apache.calcite.plan.RelOptUtil.verifyTypeEquivalence(RelOptUtil.java:382)
> at
> org.apache.calcite.plan.hep.HepRuleCall.transformTo(HepRuleCall.java:57)
> at
> org.apache.calcite.plan.RelOptRuleCall.transformTo(RelOptRuleCall.java:236)
> at
> org.apache.calcite.rel.rules.SubQueryRemoveRule$SubQueryProjectRemoveRule.onMatch(SubQueryRemoveRule.java:519)
> {code}
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
[jira] [Updated] (CALCITE-3011) Support outer joins with AggregateJoinTransposeRule
[
https://issues.apache.org/jira/browse/CALCITE-3011?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Vineet Garg updated CALCITE-3011:
-
Summary: Support outer joins with AggregateJoinTransposeRule (was: Support
for outer joins with AggregateJoinTransposeRule)
> Support outer joins with AggregateJoinTransposeRule
> ---
>
> Key: CALCITE-3011
> URL: https://issues.apache.org/jira/browse/CALCITE-3011
> Project: Calcite
> Issue Type: Improvement
> Components: core
>Reporter: Vineet Garg
>Assignee: Vineet Garg
>Priority: Major
>
> Currently {{AggregateJoinTransposeRule}} only support INNER join. Aggregates
> (at least the ones without aggregate functions) could be pushed through OUTER
> joins with current logic.
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
[jira] [Created] (CALCITE-3011) Support for outer joins with AggregateJoinTransposeRule
Vineet Garg created CALCITE-3011:
Summary: Support for outer joins with AggregateJoinTransposeRule
Key: CALCITE-3011
URL: https://issues.apache.org/jira/browse/CALCITE-3011
Project: Calcite
Issue Type: Improvement
Components: core
Reporter: Vineet Garg
Assignee: Vineet Garg
Currently {{AggregateJoinTransposeRule}} only support INNER join. Aggregates
(at least the ones without aggregate functions) could be pushed through OUTER
joins with current logic.
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
[jira] [Commented] (CALCITE-3007) Type mismatch for ANY subquery in project
[
https://issues.apache.org/jira/browse/CALCITE-3007?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16819588#comment-16819588
]
Vineet Garg commented on CALCITE-3007:
--
I can take a look if you aren't already looking at it [~hyuan]
> Type mismatch for ANY subquery in project
> -
>
> Key: CALCITE-3007
> URL: https://issues.apache.org/jira/browse/CALCITE-3007
> Project: Calcite
> Issue Type: Bug
> Components: core
>Reporter: Haisheng Yuan
>Priority: Major
> Labels: sub-query
>
> Repro:
> {code:java}
> @Test public void testGtAnyInProject() {
> final String sql = "select name, \n"
> + " deptno > ANY (\n"
> + " select deptno from emp) \n"
> + " from dept";
> checkSubQuery(sql).withLateDecorrelation(true).check();
> }
> {code}
> Exception:
> {code:java}
> java.lang.AssertionError: Cannot add expression of different type to set:
> set type is RecordType(VARCHAR(10) NOT NULL NAME, BOOLEAN NOT NULL EXPR$1)
> NOT NULL
> expression type is RecordType(VARCHAR(10) NOT NULL NAME, BOOLEAN EXPR$1) NOT
> NULL
> set is rel#5:LogicalProject(input=HepRelVertex#4,NAME=$1,EXPR$1=> SOME($0, {
> LogicalProject(DEPTNO=[$7])
> LogicalTableScan(table=[[CATALOG, SALES, EMP]])
> }))
> expression is LogicalProject(NAME=[$1], EXPR$1=[OR(AND(IS TRUE(>($0, $2)),
> <>($3, 0)), AND(>($3, $4), null, <>($3, 0), IS NOT TRUE(>($0, $2))),
> AND(>($0, $2), <>($3, 0), IS NOT TRUE(>($0, $2)), <=($3, $4)))])
> LogicalJoin(condition=[true], joinType=[inner])
> LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
> LogicalAggregate(group=[{}], m=[MIN($0)], c=[COUNT()], d=[COUNT($0)])
> LogicalProject(DEPTNO=[$7])
> LogicalTableScan(table=[[CATALOG, SALES, EMP]])
> at
> org.apache.calcite.plan.RelOptUtil.verifyTypeEquivalence(RelOptUtil.java:382)
> at
> org.apache.calcite.plan.hep.HepRuleCall.transformTo(HepRuleCall.java:57)
> at
> org.apache.calcite.plan.RelOptRuleCall.transformTo(RelOptRuleCall.java:236)
> at
> org.apache.calcite.rel.rules.SubQueryRemoveRule$SubQueryProjectRemoveRule.onMatch(SubQueryRemoveRule.java:519)
> {code}
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
[jira] [Commented] (CALCITE-2986) Wrong results with =ANY subquery
[
https://issues.apache.org/jira/browse/CALCITE-2986?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16819265#comment-16819265
]
Vineet Garg commented on CALCITE-2986:
--
[~hyuan] Right I was talking about <>ANY in general case.
bq. Subquery in project and subquery in where should be treated differently.
More generally it should be treated differently based on logic (same way as
IN/EXIST are treated). Technically you can have <>ANY in WHERE clause which
requires three valued logic e.g. WHERE ( (col1 <>ANY(select col2 from table))
IS NULL).
> Wrong results with =ANY subquery
>
>
> Key: CALCITE-2986
> URL: https://issues.apache.org/jira/browse/CALCITE-2986
> Project: Calcite
> Issue Type: Bug
> Components: core
>Reporter: Vineet Garg
>Assignee: Vineet Garg
>Priority: Major
> Labels: pull-request-available, sub-query
> Time Spent: 1h 10m
> Remaining Estimate: 0h
>
> ANY/SOME subqueries are rewritten using MAX/MIN and cross-join. This is wrong
> transformation for {{=ANY}} and {{<>ANY}} (and therefore {{=ALL}} and
> {{<>ALL}}).
> Query
> {code:sql}
> select * from "scott".emp where empno = any (select empno from "scott".emp);
> {code}
> Expected output for above query is all rows from {{scott.emp}} but actual is
> only one row
> Test case: e.g.
> https://github.com/apache/calcite/compare/master...vineetgarg02:CALCITE-2986
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
[jira] [Commented] (CALCITE-2986) Wrong results with =ANY subquery
[
https://issues.apache.org/jira/browse/CALCITE-2986?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16818502#comment-16818502
]
Vineet Garg commented on CALCITE-2986:
--
[~hyuan] Let's say you have {code:sql}select col1, col2 <> ANY(select null from
table2) from table1{code}
This roughly will be rewritten into left semi join as {code} select col1, col2
from table1 left semi join left2 on col2<>null{code} . This will generate zero
result but we want {{col1, null}}
Does that make sense?
> Wrong results with =ANY subquery
>
>
> Key: CALCITE-2986
> URL: https://issues.apache.org/jira/browse/CALCITE-2986
> Project: Calcite
> Issue Type: Bug
> Components: core
>Reporter: Vineet Garg
>Assignee: Vineet Garg
>Priority: Major
> Labels: pull-request-available, sub-query
> Time Spent: 1h 10m
> Remaining Estimate: 0h
>
> ANY/SOME subqueries are rewritten using MAX/MIN and cross-join. This is wrong
> transformation for {{=ANY}} and {{<>ANY}} (and therefore {{=ALL}} and
> {{<>ALL}}).
> Query
> {code:sql}
> select * from "scott".emp where empno = any (select empno from "scott".emp);
> {code}
> Expected output for above query is all rows from {{scott.emp}} but actual is
> only one row
> Test case: e.g.
> https://github.com/apache/calcite/compare/master...vineetgarg02:CALCITE-2986
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
[jira] [Commented] (CALCITE-2986) Wrong results with =ANY subquery
[
https://issues.apache.org/jira/browse/CALCITE-2986?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16818396#comment-16818396
]
Vineet Garg commented on CALCITE-2986:
--
bq. I think the <> ANY must be taken into the join condition, and the physical
join must be a NestedLoopJoin, each time the join left side take a loop look up
for the eight side, if left side found there is any right join keys <> the left
keys for the whole right table, it should returns early. Or the whole right
side must be probed.
This physical implementation will also have to keep track of the presence of
NULLs, if there is no match and there existed a NULL it has to return NULL from
left side. BUT if there was no NULL it has to return FALSE.
I suppose there you could add/tweak physical implementation to support <>ANY
but the problem at hand is how to transform it at logical level which is still
unclear to me.
> Wrong results with =ANY subquery
>
>
> Key: CALCITE-2986
> URL: https://issues.apache.org/jira/browse/CALCITE-2986
> Project: Calcite
> Issue Type: Bug
> Components: core
>Reporter: Vineet Garg
>Assignee: Vineet Garg
>Priority: Major
> Labels: pull-request-available, sub-query
> Time Spent: 1h 10m
> Remaining Estimate: 0h
>
> ANY/SOME subqueries are rewritten using MAX/MIN and cross-join. This is wrong
> transformation for {{=ANY}} and {{<>ANY}} (and therefore {{=ALL}} and
> {{<>ALL}}).
> Query
> {code:sql}
> select * from "scott".emp where empno = any (select empno from "scott".emp);
> {code}
> Expected output for above query is all rows from {{scott.emp}} but actual is
> only one row
> Test case: e.g.
> https://github.com/apache/calcite/compare/master...vineetgarg02:CALCITE-2986
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
[jira] [Commented] (CALCITE-2986) Wrong results with =ANY subquery
[
https://issues.apache.org/jira/browse/CALCITE-2986?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16817092#comment-16817092
]
Vineet Garg commented on CALCITE-2986:
--
bq. It's not true that IN will be rewritten to LEFT join, actually it will
rewrite to a INNER join[1], the left is a table scan and the right is an agg.
[~danny0405] I meant IN within SELECT. IN is rewritten into INNER where logic
is TRUE (i.e. IN in WHERE) but for IN in SELECT where logic could be TRUE/FALSE
or TRUE/FALSE/UNKNOWN it is transformed into LEFT OUTER JOIN
e.g.
{code:sql}
select sal,
empno IN (
select deptno from dept
where dept.name <> 'Engineering')
from emp
{code}
{code}
LogicalProject(SAL=[$5], EXPR$1=[IS NOT NULL($10)])
LogicalJoin(condition=[=($0, $9)], joinType=[left])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
LogicalProject(DEPTNO=[$0], i=[true])
LogicalFilter(condition=[<>(CAST($1):VARCHAR(11) NOT NULL,
'Engineering')])
LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
{code}
bq. For <> ANY, i think it can also be interpreted as a
NestedLoopJoin(physical) and INNER join for logical.
I am not sure how this will work. Can you provide few examples? What would be
the transformation for <>ANY in WHERE and <>ANY in SELECT?
> Wrong results with =ANY subquery
>
>
> Key: CALCITE-2986
> URL: https://issues.apache.org/jira/browse/CALCITE-2986
> Project: Calcite
> Issue Type: Bug
> Components: core
>Reporter: Vineet Garg
>Assignee: Vineet Garg
>Priority: Major
> Labels: pull-request-available, sub-query
> Time Spent: 1h 10m
> Remaining Estimate: 0h
>
> ANY/SOME subqueries are rewritten using MAX/MIN and cross-join. This is wrong
> transformation for {{=ANY}} and {{<>ANY}} (and therefore {{=ALL}} and
> {{<>ALL}}).
> Query
> {code:sql}
> select * from "scott".emp where empno = any (select empno from "scott".emp);
> {code}
> Expected output for above query is all rows from {{scott.emp}} but actual is
> only one row
> Test case: e.g.
> https://github.com/apache/calcite/compare/master...vineetgarg02:CALCITE-2986
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
[jira] [Comment Edited] (CALCITE-2986) Wrong results with =ANY subquery
[
https://issues.apache.org/jira/browse/CALCITE-2986?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16816716#comment-16816716
]
Vineet Garg edited comment on CALCITE-2986 at 4/12/19 10:38 PM:
bq. <>ANY should probably be transformed in same way as IN is transformed in
SELECT.
I realized that is not really true since IN corresponds to = and is rewritten
using LEFT OUTER JOIN which will not work with <>ANY
was (Author: vgarg):
bq <>ANY should probably be transformed in same way as IN is transformed in
SELECT.
I realized that is not really true since IN corresponds to = and is rewritten
using LEFT OUTER JOIN which will not work with <>ANY
> Wrong results with =ANY subquery
>
>
> Key: CALCITE-2986
> URL: https://issues.apache.org/jira/browse/CALCITE-2986
> Project: Calcite
> Issue Type: Bug
> Components: core
>Reporter: Vineet Garg
>Assignee: Vineet Garg
>Priority: Major
> Labels: sub-query
>
> ANY/SOME subqueries are rewritten using MAX/MIN and cross-join. This is wrong
> transformation for {{=ANY}} and {{<>ANY}} (and therefore {{=ALL}} and
> {{<>ALL}}).
> Query
> {code:sql}
> select * from "scott".emp where empno = any (select empno from "scott".emp);
> {code}
> Expected output for above query is all rows from {{scott.emp}} but actual is
> only one row
> Test case: e.g.
> https://github.com/apache/calcite/compare/master...vineetgarg02:CALCITE-2986
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
[jira] [Commented] (CALCITE-2986) Wrong results with =ANY subquery
[
https://issues.apache.org/jira/browse/CALCITE-2986?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16816716#comment-16816716
]
Vineet Garg commented on CALCITE-2986:
--
bq <>ANY should probably be transformed in same way as IN is transformed in
SELECT.
I realized that is not really true since IN corresponds to = and is rewritten
using LEFT OUTER JOIN which will not work with <>ANY
> Wrong results with =ANY subquery
>
>
> Key: CALCITE-2986
> URL: https://issues.apache.org/jira/browse/CALCITE-2986
> Project: Calcite
> Issue Type: Bug
> Components: core
>Reporter: Vineet Garg
>Assignee: Vineet Garg
>Priority: Major
> Labels: sub-query
>
> ANY/SOME subqueries are rewritten using MAX/MIN and cross-join. This is wrong
> transformation for {{=ANY}} and {{<>ANY}} (and therefore {{=ALL}} and
> {{<>ALL}}).
> Query
> {code:sql}
> select * from "scott".emp where empno = any (select empno from "scott".emp);
> {code}
> Expected output for above query is all rows from {{scott.emp}} but actual is
> only one row
> Test case: e.g.
> https://github.com/apache/calcite/compare/master...vineetgarg02:CALCITE-2986
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
[jira] [Commented] (CALCITE-2986) Wrong results with =ANY subquery
[
https://issues.apache.org/jira/browse/CALCITE-2986?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16816715#comment-16816715
]
Vineet Garg commented on CALCITE-2986:
--
I have opened a pull request for {{=ANY}} rewrite. I still do not know how to
rewrite {{<>ANY. }}
> Wrong results with =ANY subquery
>
>
> Key: CALCITE-2986
> URL: https://issues.apache.org/jira/browse/CALCITE-2986
> Project: Calcite
> Issue Type: Bug
> Components: core
>Reporter: Vineet Garg
>Assignee: Vineet Garg
>Priority: Major
> Labels: sub-query
>
> ANY/SOME subqueries are rewritten using MAX/MIN and cross-join. This is wrong
> transformation for {{=ANY}} and {{<>ANY}} (and therefore {{=ALL}} and
> {{<>ALL}}).
> Query
> {code:sql}
> select * from "scott".emp where empno = any (select empno from "scott".emp);
> {code}
> Expected output for above query is all rows from {{scott.emp}} but actual is
> only one row
> Test case: e.g.
> https://github.com/apache/calcite/compare/master...vineetgarg02:CALCITE-2986
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
[jira] [Commented] (CALCITE-2991) getMaxRowCount should return rowcount 1 for an aggregate with constant keys
[
https://issues.apache.org/jira/browse/CALCITE-2991?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16815869#comment-16815869
]
Vineet Garg commented on CALCITE-2991:
--
[~hyuan] I am not sure if I understand what you said.
{quote}I would prefer to do the simplification of removing constant grouping
keys before running into optimization
{quote}
This improvement is not removing grouping keys. Grouping keys although constant
couldn't really be removed because that would change the semantics.
> getMaxRowCount should return rowcount 1 for an aggregate with constant keys
> ---
>
> Key: CALCITE-2991
> URL: https://issues.apache.org/jira/browse/CALCITE-2991
> Project: Calcite
> Issue Type: Improvement
> Components: core
>Reporter: Vineet Garg
>Assignee: Vineet Garg
>Priority: Major
> Labels: pull-request-available
> Time Spent: 10m
> Remaining Estimate: 0h
>
> Aggregate with constant keys are guaranteed to produce at most one row
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
[jira] [Created] (CALCITE-2991) getMaxRowCount should return rowcount 1 for an aggregate with constant keys
Vineet Garg created CALCITE-2991: Summary: getMaxRowCount should return rowcount 1 for an aggregate with constant keys Key: CALCITE-2991 URL: https://issues.apache.org/jira/browse/CALCITE-2991 Project: Calcite Issue Type: Improvement Components: core Reporter: Vineet Garg Assignee: Vineet Garg Aggregate with constant keys are guaranteed to produce at most one row -- This message was sent by Atlassian JIRA (v7.6.3#76005)
[jira] [Comment Edited] (CALCITE-2986) Wrong results with =ANY subquery
[
https://issues.apache.org/jira/browse/CALCITE-2986?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16812867#comment-16812867
]
Vineet Garg edited comment on CALCITE-2986 at 4/8/19 11:23 PM:
---
[~hyuan] I have given it some thought and as you noticed <>ANY is not
equivalent to NOT IN.
Problem with semi join transformation is that it will evaluate to only TRUE or
FALSE (SJ will output rows where ON condition is true and will not have rows
where ON condition is not true). But <>ANY requires it to be evaluated to
TRUE, FALSE or UNKNOWN.
<>ANY should probably be transformed in same way as IN is transformed in
SELECT. (e.g. select p_partkey, ( p_partkey IN (select p_partkey from part))
from part)
was (Author: vgarg):
[~hyuan] I have given it some thought and as you noticed <>ANY is not
equivalent to NOT IN.
Problem with semi join transformation is that it will evaluate to only TRUE or
FALSE (SJ with have rows where ON condition is true and will not have rows
where ON condition is not true). But <>ANY requires it to be evaluated to
TRUE, FALSE or UNKNOWN.
<>ANY should probably be transformed in same way as IN is transformed in
SELECT. (e.g. select p_partkey, ( p_partkey IN (select p_partkey from part))
from part)
> Wrong results with =ANY subquery
>
>
> Key: CALCITE-2986
> URL: https://issues.apache.org/jira/browse/CALCITE-2986
> Project: Calcite
> Issue Type: Bug
> Components: core
>Reporter: Vineet Garg
>Assignee: Vineet Garg
>Priority: Major
> Labels: sub-query
>
> ANY/SOME subqueries are rewritten using MAX/MIN and cross-join. This is wrong
> transformation for {{=ANY}} and {{<>ANY}} (and therefore {{=ALL}} and
> {{<>ALL}}).
> Query
> {code:sql}
> select * from "scott".emp where empno = any (select empno from "scott".emp);
> {code}
> Expected output for above query is all rows from {{scott.emp}} but actual is
> only one row
> Test case: e.g.
> https://github.com/apache/calcite/compare/master...vineetgarg02:CALCITE-2986
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
[jira] [Commented] (CALCITE-2986) Wrong results with =ANY subquery
[
https://issues.apache.org/jira/browse/CALCITE-2986?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16812867#comment-16812867
]
Vineet Garg commented on CALCITE-2986:
--
[~hyuan] I have given it some thought and as you noticed <>ANY is not
equivalent to NOT IN.
Problem with semi join transformation is that it will evaluate to only TRUE or
FALSE (SJ with have rows where ON condition is true and will not have rows
where ON condition is not true). But <>ANY requires it to be evaluated to
TRUE, FALSE or UNKNOWN.
<>ANY should probably be transformed in same way as IN is transformed in
SELECT. (e.g. select p_partkey, ( p_partkey IN (select p_partkey from part))
from part)
> Wrong results with =ANY subquery
>
>
> Key: CALCITE-2986
> URL: https://issues.apache.org/jira/browse/CALCITE-2986
> Project: Calcite
> Issue Type: Bug
> Components: core
>Reporter: Vineet Garg
>Assignee: Vineet Garg
>Priority: Major
> Labels: sub-query
>
> ANY/SOME subqueries are rewritten using MAX/MIN and cross-join. This is wrong
> transformation for {{=ANY}} and {{<>ANY}} (and therefore {{=ALL}} and
> {{<>ALL}}).
> Query
> {code:sql}
> select * from "scott".emp where empno = any (select empno from "scott".emp);
> {code}
> Expected output for above query is all rows from {{scott.emp}} but actual is
> only one row
> Test case: e.g.
> https://github.com/apache/calcite/compare/master...vineetgarg02:CALCITE-2986
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
[jira] [Commented] (CALCITE-2986) Wrong results with =ANY subquery
[
https://issues.apache.org/jira/browse/CALCITE-2986?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16812799#comment-16812799
]
Vineet Garg commented on CALCITE-2986:
--
{{=ANY}} is equivalent to IN therefore such queries should be rewritten into
IN. I am not what is the correct transformation for {{<>ANY}}.
> Wrong results with =ANY subquery
>
>
> Key: CALCITE-2986
> URL: https://issues.apache.org/jira/browse/CALCITE-2986
> Project: Calcite
> Issue Type: Bug
> Components: core
>Reporter: Vineet Garg
>Assignee: Vineet Garg
>Priority: Major
> Labels: sub-query
>
> ANY/SOME subqueries are rewritten using MAX/MIN and cross-join. This is wrong
> transformation for {{=ANY}} and {{<>ANY}} (and therefore {{=ALL}} and
> {{<>ALL}}).
> Query
> {code:sql}
> select * from "scott".emp where empno = any (select empno from "scott".emp);
> {code}
> Expected output for above query is all rows from {{scott.emp}} but actual is
> only one row
> Test case: e.g.
> https://github.com/apache/calcite/compare/master...vineetgarg02:CALCITE-2986
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
[jira] [Comment Edited] (CALCITE-2986) Wrong results with =ANY subquery
[
https://issues.apache.org/jira/browse/CALCITE-2986?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16812799#comment-16812799
]
Vineet Garg edited comment on CALCITE-2986 at 4/8/19 9:22 PM:
--
{{=ANY}} is equivalent to IN therefore such queries should be rewritten into
IN. I am not sure what is the correct transformation for {{<>ANY}}.
was (Author: vgarg):
{{=ANY}} is equivalent to IN therefore such queries should be rewritten into
IN. I am not what is the correct transformation for {{<>ANY}}.
> Wrong results with =ANY subquery
>
>
> Key: CALCITE-2986
> URL: https://issues.apache.org/jira/browse/CALCITE-2986
> Project: Calcite
> Issue Type: Bug
> Components: core
>Reporter: Vineet Garg
>Assignee: Vineet Garg
>Priority: Major
> Labels: sub-query
>
> ANY/SOME subqueries are rewritten using MAX/MIN and cross-join. This is wrong
> transformation for {{=ANY}} and {{<>ANY}} (and therefore {{=ALL}} and
> {{<>ALL}}).
> Query
> {code:sql}
> select * from "scott".emp where empno = any (select empno from "scott".emp);
> {code}
> Expected output for above query is all rows from {{scott.emp}} but actual is
> only one row
> Test case: e.g.
> https://github.com/apache/calcite/compare/master...vineetgarg02:CALCITE-2986
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
[jira] [Updated] (CALCITE-2986) Wrong results with =ANY subquery
[
https://issues.apache.org/jira/browse/CALCITE-2986?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Vineet Garg updated CALCITE-2986:
-
Labels: sub-query (was: )
> Wrong results with =ANY subquery
>
>
> Key: CALCITE-2986
> URL: https://issues.apache.org/jira/browse/CALCITE-2986
> Project: Calcite
> Issue Type: Bug
> Components: core
>Reporter: Vineet Garg
>Assignee: Vineet Garg
>Priority: Major
> Labels: sub-query
>
> ANY/SOME subqueries are rewritten using MAX/MIN and cross-join. This is wrong
> transformation for {{=ANY}} and {{<>ANY}} (and therefore {{=ALL}} and
> {{<>ALL}}).
> Query
> {code:sql}
> select * from "scott".emp where empno = any (select empno from "scott".emp);
> {code}
> Expected output for above query is all rows from {{scott.emp}} but actual is
> only one row
> Test case: e.g.
> https://github.com/apache/calcite/compare/master...vineetgarg02:CALCITE-2986
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
[jira] [Updated] (CALCITE-2986) Wrong results with =ANY subquery
[
https://issues.apache.org/jira/browse/CALCITE-2986?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Vineet Garg updated CALCITE-2986:
-
Description:
ANY/SOME subqueries are rewritten using MAX/MIN and cross-join. This is wrong
transformation for {{=ANY}} and {{<>ANY}} (and therefore {{=ALL}} and
{{<>ALL}}).
Query
{code:sql}
select * from "scott".emp where empno = any (select empno from "scott".emp);
{code}
Expected output for above query is all rows from {{scott.emp}} but actual is
only one row
Test case: e.g.
https://github.com/apache/calcite/compare/master...vineetgarg02:CALCITE-2986
was:ANY/SOME subqueries are rewritten using MAX/MIN and cross-join. This is
wrong transformation for {{=ANY}} and {{<>ANY}} (and therefore {{=ALL}} and
{{<>ALL}}).
> Wrong results with =ANY subquery
>
>
> Key: CALCITE-2986
> URL: https://issues.apache.org/jira/browse/CALCITE-2986
> Project: Calcite
> Issue Type: Bug
> Components: core
>Reporter: Vineet Garg
>Assignee: Vineet Garg
>Priority: Major
>
> ANY/SOME subqueries are rewritten using MAX/MIN and cross-join. This is wrong
> transformation for {{=ANY}} and {{<>ANY}} (and therefore {{=ALL}} and
> {{<>ALL}}).
> Query
> {code:sql}
> select * from "scott".emp where empno = any (select empno from "scott".emp);
> {code}
> Expected output for above query is all rows from {{scott.emp}} but actual is
> only one row
> Test case: e.g.
> https://github.com/apache/calcite/compare/master...vineetgarg02:CALCITE-2986
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
[jira] [Created] (CALCITE-2986) Wrong results with =ANY subquery
Vineet Garg created CALCITE-2986:
Summary: Wrong results with =ANY subquery
Key: CALCITE-2986
URL: https://issues.apache.org/jira/browse/CALCITE-2986
Project: Calcite
Issue Type: Bug
Components: core
Reporter: Vineet Garg
Assignee: Vineet Garg
ANY/SOME subqueries are rewritten using MAX/MIN and cross-join. This is wrong
transformation for {{=ANY}} and {{<>ANY}} (and therefore {{=ALL}} and
{{<>ALL}}).
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
[jira] [Updated] (CALCITE-2971) Correlated sub-query in JOIN is failing
[
https://issues.apache.org/jira/browse/CALCITE-2971?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Vineet Garg updated CALCITE-2971:
-
Summary: Correlated sub-query in JOIN is failing (was: Correlated subquery
in JOIN is failing)
> Correlated sub-query in JOIN is failing
> ---
>
> Key: CALCITE-2971
> URL: https://issues.apache.org/jira/browse/CALCITE-2971
> Project: Calcite
> Issue Type: Bug
> Components: core
>Reporter: Vineet Garg
>Priority: Major
> Labels: sub-query
>
> Following query if added in RelOptRulesTest tests throws an exception:
> {code:sql}
> select empno from sales.emp as r left join sales.dept as s on exists (select
> deptno from sales.emp where empno = s.deptno+20)
> {code}
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
[jira] [Created] (CALCITE-2971) Correlated subquery in JOIN is failing
Vineet Garg created CALCITE-2971:
Summary: Correlated subquery in JOIN is failing
Key: CALCITE-2971
URL: https://issues.apache.org/jira/browse/CALCITE-2971
Project: Calcite
Issue Type: Bug
Components: core
Reporter: Vineet Garg
Following query if added in RelOptRulesTest tests throws an exception:
{code:sql}
select empno from sales.emp as r left join sales.dept as s on exists (select
deptno from sales.emp where empno = s.deptno+20)
{code}
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
[jira] [Commented] (CALCITE-2954) SubQueryJoinRemoveRule and SubQueryProjectRemoveRule passing on empty set instead of set of correlation id
[
https://issues.apache.org/jira/browse/CALCITE-2954?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16807047#comment-16807047
]
Vineet Garg commented on CALCITE-2954:
--
Logged CALCITE-2971 to track the issue with correlated subquery in JOIN.
> SubQueryJoinRemoveRule and SubQueryProjectRemoveRule passing on empty set
> instead of set of correlation id
> --
>
> Key: CALCITE-2954
> URL: https://issues.apache.org/jira/browse/CALCITE-2954
> Project: Calcite
> Issue Type: Bug
> Components: core
>Reporter: Vineet Garg
>Assignee: Vineet Garg
>Priority: Major
> Labels: pull-request-available, sub-query
> Time Spent: 10m
> Remaining Estimate: 0h
>
> Call to {{apply}} takes set of {{CorrelationId}} but both
> {{SubQueryJoinRemoveRule}} and {{SubQueryProjectRemoveRule}} are passing
> empty set for whatever reason.
> I believe the correct thing to do here is to pass on the actual set of
> {{CorrelationId}} like {{SubQueryFilterRemoveRule}}
> Ref:
> {code:java}
> final RexNode target = apply(e, ImmutableSet.of(),
> logic, builder, 2, fieldCount);
> {code}
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
[jira] [Commented] (CALCITE-2954) SubQueryJoinRemoveRule and SubQueryProjectRemoveRule passing on empty set instead of set of correlation id
[
https://issues.apache.org/jira/browse/CALCITE-2954?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16806272#comment-16806272
]
Vineet Garg commented on CALCITE-2954:
--
[~hyuan] This test case fails even with the patch. I'll open a separate Jira
for this
> SubQueryJoinRemoveRule and SubQueryProjectRemoveRule passing on empty set
> instead of set of correlation id
> --
>
> Key: CALCITE-2954
> URL: https://issues.apache.org/jira/browse/CALCITE-2954
> Project: Calcite
> Issue Type: Bug
> Components: core
>Reporter: Vineet Garg
>Assignee: Vineet Garg
>Priority: Major
> Labels: pull-request-available, sub-query
> Time Spent: 10m
> Remaining Estimate: 0h
>
> Call to {{apply}} takes set of {{CorrelationId}} but both
> {{SubQueryJoinRemoveRule}} and {{SubQueryProjectRemoveRule}} are passing
> empty set for whatever reason.
> I believe the correct thing to do here is to pass on the actual set of
> {{CorrelationId}} like {{SubQueryFilterRemoveRule}}
> Ref:
> {code:java}
> final RexNode target = apply(e, ImmutableSet.of(),
> logic, builder, 2, fieldCount);
> {code}
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
[jira] [Commented] (CALCITE-2954) SubQueryJoinRemoveRule and SubQueryProjectRemoveRule passing on empty set instead of set of correlation id
[
https://issues.apache.org/jira/browse/CALCITE-2954?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16805469#comment-16805469
]
Vineet Garg commented on CALCITE-2954:
--
Thanks for providing the feedback [~hyuan]. I have updated the pull request to
add the test case.
> SubQueryJoinRemoveRule and SubQueryProjectRemoveRule passing on empty set
> instead of set of correlation id
> --
>
> Key: CALCITE-2954
> URL: https://issues.apache.org/jira/browse/CALCITE-2954
> Project: Calcite
> Issue Type: Bug
> Components: core
>Reporter: Vineet Garg
>Assignee: Vineet Garg
>Priority: Major
> Labels: pull-request-available, sub-query
> Time Spent: 10m
> Remaining Estimate: 0h
>
> Call to {{apply}} takes set of {{CorrelationId}} but both
> {{SubQueryJoinRemoveRule}} and {{SubQueryProjectRemoveRule}} are passing
> empty set for whatever reason.
> I believe the correct thing to do here is to pass on the actual set of
> {{CorrelationId}} like {{SubQueryFilterRemoveRule}}
> Ref:
> {code:java}
> final RexNode target = apply(e, ImmutableSet.of(),
> logic, builder, 2, fieldCount);
> {code}
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
[jira] [Commented] (CALCITE-2954) SubQueryJoinRemoveRule and SubQueryProjectRemoveRule passing on empty set instead of set of correlation id
[
https://issues.apache.org/jira/browse/CALCITE-2954?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16803529#comment-16803529
]
Vineet Garg commented on CALCITE-2954:
--
[~hyuan] Would you mind taking a look at the PR?
> SubQueryJoinRemoveRule and SubQueryProjectRemoveRule passing on empty set
> instead of set of correlation id
> --
>
> Key: CALCITE-2954
> URL: https://issues.apache.org/jira/browse/CALCITE-2954
> Project: Calcite
> Issue Type: Bug
> Components: core
>Reporter: Vineet Garg
>Assignee: Vineet Garg
>Priority: Major
> Labels: pull-request-available, sub-query
> Time Spent: 10m
> Remaining Estimate: 0h
>
> Call to {{apply}} takes set of {{CorrelationId}} but both
> {{SubQueryJoinRemoveRule}} and {{SubQueryProjectRemoveRule}} are passing
> empty set for whatever reason.
> I believe the correct thing to do here is to pass on the actual set of
> {{CorrelationId}} like {{SubQueryFilterRemoveRule}}
> Ref:
> {code:java}
> final RexNode target = apply(e, ImmutableSet.of(),
> logic, builder, 2, fieldCount);
> {code}
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
[jira] [Updated] (CALCITE-2954) SubQueryJoinRemoveRule and SubQueryProjectRemoveRule passing on empty set instead of set of correlation id
[
https://issues.apache.org/jira/browse/CALCITE-2954?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Vineet Garg updated CALCITE-2954:
-
Labels: sub-query (was: )
> SubQueryJoinRemoveRule and SubQueryProjectRemoveRule passing on empty set
> instead of set of correlation id
> --
>
> Key: CALCITE-2954
> URL: https://issues.apache.org/jira/browse/CALCITE-2954
> Project: Calcite
> Issue Type: Bug
> Components: core
>Reporter: Vineet Garg
>Assignee: Vineet Garg
>Priority: Major
> Labels: sub-query
>
> Call to {{apply}} takes set of {{CorrelationId}} but both
> {{SubQueryJoinRemoveRule}} and {{SubQueryProjectRemoveRule}} are passing
> empty set for whatever reason.
> I believe the correct thing to do here is to pass on the actual set of
> {{CorrelationId}} like {{SubQueryFilterRemoveRule}}
> Ref:
> {code:java}
> final RexNode target = apply(e, ImmutableSet.of(),
> logic, builder, 2, fieldCount);
> {code}
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
[jira] [Created] (CALCITE-2954) SubQueryJoinRemoveRule and SubQueryProjectRemoveRule passing on empty set instead of set of correlation id
Vineet Garg created CALCITE-2954:
Summary: SubQueryJoinRemoveRule and SubQueryProjectRemoveRule
passing on empty set instead of set of correlation id
Key: CALCITE-2954
URL: https://issues.apache.org/jira/browse/CALCITE-2954
Project: Calcite
Issue Type: Bug
Components: core
Reporter: Vineet Garg
Assignee: Vineet Garg
Call to {{apply}} takes set of {{CorrelationId}} but both
{{SubQueryJoinRemoveRule}} and {{SubQueryProjectRemoveRule}} are passing empty
set for whatever reason.
I believe the correct thing to do here is to pass on the actual set of
{{CorrelationId}} like {{SubQueryFilterRemoveRule}}
Ref:
{code:java}
final RexNode target = apply(e, ImmutableSet.of(),
logic, builder, 2, fieldCount);
{code}
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
[jira] [Commented] (CALCITE-2948) SqlToRelConverter generates complicated logical plan for subquery
[
https://issues.apache.org/jira/browse/CALCITE-2948?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16801292#comment-16801292
]
Vineet Garg commented on CALCITE-2948:
--
Although this and CALCITE-1624 are different problems but I believe both of
them are due to decorrelation logic (specifically while generating value
generator).
> SqlToRelConverter generates complicated logical plan for subquery
> -
>
> Key: CALCITE-2948
> URL: https://issues.apache.org/jira/browse/CALCITE-2948
> Project: Calcite
> Issue Type: Bug
> Components: core
>Reporter: Haisheng Yuan
>Priority: Major
>
> Repro:
> Add the following test to SqlToRelConverterTest.java.
> {code:java}
> @Test public void testSubQueryIN() {
> final String sql = "select deptno\n"
> + "from EMP e\n"
> + "where deptno in (select deptno\n"
> + "from EMP where empno=e.empno+1)";
> sql(sql).ok();
> }
> {code}
> Plan:
> {code:java}
> LogicalProject(DEPTNO=[$7])
> LogicalJoin(condition=[AND(=($0, $10), =($7, $9))], joinType=[inner])
> LogicalTableScan(table=[[CATALOG, SALES, EMP]])
> LogicalAggregate(group=[{0, 1}])
> LogicalProject(DEPTNO=[$7], EMPNO0=[$9])
> LogicalJoin(condition=[=($0, +($9, 1))], joinType=[inner])
> LogicalTableScan(table=[[CATALOG, SALES, EMP]])
> LogicalProject(EMPNO=[$0])
> LogicalTableScan(table=[[CATALOG, SALES, EMP]])
> {code}
> One join would suffice.
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
[jira] [Updated] (CALCITE-2948) SqlToRelConverter generates complicated logical plan for subquery
[
https://issues.apache.org/jira/browse/CALCITE-2948?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Vineet Garg updated CALCITE-2948:
-
Labels: sub-query (was: )
> SqlToRelConverter generates complicated logical plan for subquery
> -
>
> Key: CALCITE-2948
> URL: https://issues.apache.org/jira/browse/CALCITE-2948
> Project: Calcite
> Issue Type: Bug
> Components: core
>Reporter: Haisheng Yuan
>Priority: Major
> Labels: sub-query
>
> Repro:
> Add the following test to SqlToRelConverterTest.java.
> {code:java}
> @Test public void testSubQueryIN() {
> final String sql = "select deptno\n"
> + "from EMP e\n"
> + "where deptno in (select deptno\n"
> + "from EMP where empno=e.empno+1)";
> sql(sql).ok();
> }
> {code}
> Plan:
> {code:java}
> LogicalProject(DEPTNO=[$7])
> LogicalJoin(condition=[AND(=($0, $10), =($7, $9))], joinType=[inner])
> LogicalTableScan(table=[[CATALOG, SALES, EMP]])
> LogicalAggregate(group=[{0, 1}])
> LogicalProject(DEPTNO=[$7], EMPNO0=[$9])
> LogicalJoin(condition=[=($0, +($9, 1))], joinType=[inner])
> LogicalTableScan(table=[[CATALOG, SALES, EMP]])
> LogicalProject(EMPNO=[$0])
> LogicalTableScan(table=[[CATALOG, SALES, EMP]])
> {code}
> One join would suffice.
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
[jira] [Commented] (CALCITE-2857) SemiJoin extends Join directly instead of EquiJoin to support non-equi condition
[
https://issues.apache.org/jira/browse/CALCITE-2857?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16790255#comment-16790255
]
Vineet Garg commented on CALCITE-2857:
--
{quote}You're probably right. So, would we split into SemiJoin and
EquiSemiJoin? (Or would that be SemiEquiJoin?)
{quote}
Why do we need to split? Can we not change SemiJoin to extend Join and have
join condition cover all kind of conditions? Sorry if this is a stupid question
but I am trying to understand the reasoning behind the split suggestion.
> SemiJoin extends Join directly instead of EquiJoin to support non-equi
> condition
>
>
> Key: CALCITE-2857
> URL: https://issues.apache.org/jira/browse/CALCITE-2857
> Project: Calcite
> Issue Type: Improvement
> Components: core
>Reporter: godfrey he
>Priority: Major
>
> SemiJoin extends Join directly instead of EquiJoin to support non-equi
> condition,
> e.g.
> {code:sql}
> select * from (select * from dept where dept.deptno in (select emp.deptno
> from emp where emp.job <> dept.name))R where R.deptno <= 10
> {code}
> Currently, this query will be converted to
> {code}
> SemiJoin(condition=[AND(=($1, $3), =($0, $2))], joinType=[inner])
> LogicalFilter(condition=[<=($0, 10)])
> LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
> LogicalProject(DEPTNO=[$7], NAME=[$9])
> LogicalJoin(condition=[<>($2, $9)], joinType=[inner])
> LogicalTableScan(table=[[CATALOG, SALES, EMP]])
> LogicalAggregate(group=[{0}])
> LogicalProject(NAME=[$1])
> LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
> {code}
> however the following plan is more efficient than the above one:
> {code}
> LogicalProject(DEPTNO=[$0], NAME=[$1])
> LogicalFilter(condition=[<=($0, 10)])
> SemiJoin(condition=[=AND(=($0, $3), <>($1, $2))], joinType=[inner])
> LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
> LogicalProject(JOB=[$2], DEPTNO=[$7])
> LogicalTableScan(table=[[CATALOG, SALES, EMP]])
> {code}
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
[jira] [Commented] (CALCITE-2857) SemiJoin extends Join directly instead of EquiJoin to support non-equi condition
[
https://issues.apache.org/jira/browse/CALCITE-2857?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16787523#comment-16787523
]
Vineet Garg commented on CALCITE-2857:
--
bq. Agree, either IN or NOT IN, a.k.a semi-join and anti-semi-join
Can a semi-join not have {{>, <}}? e.g. following query could be rewritten into
semi join (LEFT)
{code:sql}
select * from dept where dept.deptno in (select emp.deptno from emp where
emp.job > dept.name) R
{code}
{code:sql}
select * from dept semi join emp on dept.deptno=emp.deptno AND emp.job >
dept.name
{code}
> SemiJoin extends Join directly instead of EquiJoin to support non-equi
> condition
>
>
> Key: CALCITE-2857
> URL: https://issues.apache.org/jira/browse/CALCITE-2857
> Project: Calcite
> Issue Type: Improvement
> Components: core
>Reporter: godfrey he
>Priority: Major
>
> SemiJoin extends Join directly instead of EquiJoin to support non-equi
> condition,
> e.g.
> {code:sql}
> select * from (select * from dept where dept.deptno in (select emp.deptno
> from emp where emp.job <> dept.name))R where R.deptno <= 10
> {code}
> Currently, this query will be converted to
> {code}
> SemiJoin(condition=[AND(=($1, $3), =($0, $2))], joinType=[inner])
> LogicalFilter(condition=[<=($0, 10)])
> LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
> LogicalProject(DEPTNO=[$7], NAME=[$9])
> LogicalJoin(condition=[<>($2, $9)], joinType=[inner])
> LogicalTableScan(table=[[CATALOG, SALES, EMP]])
> LogicalAggregate(group=[{0}])
> LogicalProject(NAME=[$1])
> LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
> {code}
> however the following plan is more efficient than the above one:
> {code}
> LogicalProject(DEPTNO=[$0], NAME=[$1])
> LogicalFilter(condition=[<=($0, 10)])
> SemiJoin(condition=[=AND(=($0, $3), <>($1, $2))], joinType=[inner])
> LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
> LogicalProject(JOB=[$2], DEPTNO=[$7])
> LogicalTableScan(table=[[CATALOG, SALES, EMP]])
> {code}
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
[jira] [Commented] (CALCITE-1513) Correlated NOT IN query throws AssertionError
[
https://issues.apache.org/jira/browse/CALCITE-1513?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16777572#comment-16777572
]
Vineet Garg commented on CALCITE-1513:
--
[~julianhyde] Can you please merge the PR?
Opened CALCITE-2874 to capture issue of inefficient plan with NOT IN correlated
sub query.
> Correlated NOT IN query throws AssertionError
> -
>
> Key: CALCITE-1513
> URL: https://issues.apache.org/jira/browse/CALCITE-1513
> Project: Calcite
> Issue Type: Bug
>Reporter: Julian Hyde
>Assignee: Vineet Garg
>Priority: Major
> Labels: pull-request-available, sub-query
> Fix For: 1.19.0
>
> Time Spent: 10m
> Remaining Estimate: 0h
>
> A correlated NOT IN query throws AssertionError during decorrelation. Here is
> a patch that reproduces the case:
> {noformat}
> diff --git a/core/src/test/resources/sql/subquery.iq
> b/core/src/test/resources/sql/subquery.iq
> index ad71655..975aae1 100644
> --- a/core/src/test/resources/sql/subquery.iq
> +++ b/core/src/test/resources/sql/subquery.iq
> @@ -370,6 +370,22 @@ where e.job not in (
> !plan
> !}
>
> +# Correlated condition in NOT IN.
> +# Tested on Oracle.
> +select count(*) as c
> +from "scott".emp as e
> +where sal + 100 not in (
> + select comm
> + from "scott".emp
> + where job = e.job);
> + EMPNO ENAME JOB MGR HIREDATE SAL COMM
> DEPTNO
> +-- -- - -- - -- --
> --
> + 7499 ALLEN SALESMAN7698 20-FEB-81 1600300
> 30
> + 7521 WARD SALESMAN7698 22-FEB-81 1250500
> 30
> + 7654 MARTIN SALESMAN7698 28-SEP-81 1250 1400
> 30
> + 7844 TURNER SALESMAN7698 08-SEP-81 1500 0
> 30
> +!ok
> +
> # [CALCITE-864] Correlation variable has incorrect row type if it is
> populated
> # by right side of a Join
> select *
> {noformat}
> And here is the stack:
> {noformat}
> java.lang.AssertionError: Internal error: While invoking method 'public
> org.apache.calcite.sql2rel.RelDecorrelator$Frame
> org.apache.calcite.sql2rel.RelDecorrelator.decorrelateRel(org.apache.calcite.rel.logical.LogicalAggregate)'
> at org.apache.calcite.util.Util.newInternal(Util.java:792)
> at org.apache.calcite.util.ReflectUtil$2.invoke(ReflectUtil.java:534)
> at
> org.apache.calcite.sql2rel.RelDecorrelator.getInvoke(RelDecorrelator.java:601)
> at
> org.apache.calcite.sql2rel.RelDecorrelator.decorrelate(RelDecorrelator.java:242)
> at
> org.apache.calcite.sql2rel.RelDecorrelator.decorrelateQuery(RelDecorrelator.java:211)
> at
> org.apache.calcite.tools.Programs$DecorrelateProgram.run(Programs.java:370)
> at
> org.apache.calcite.tools.Programs$SequenceProgram.run(Programs.java:351)
> at org.apache.calcite.prepare.Prepare.optimize(Prepare.java:155)
> at org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:286)
> at org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:195)
> at
> org.apache.calcite.prepare.CalcitePrepareImpl.prepare2_(CalcitePrepareImpl.java:748)
> at
> org.apache.calcite.prepare.CalcitePrepareImpl.prepare_(CalcitePrepareImpl.java:610)
> at
> org.apache.calcite.prepare.CalcitePrepareImpl.prepareSql(CalcitePrepareImpl.java:580)
> Caused by: java.lang.reflect.InvocationTargetException
> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
> at
> sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
> at
> sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
> at java.lang.reflect.Method.invoke(Method.java:498)
> at org.apache.calcite.util.ReflectUtil$2.invoke(ReflectUtil.java:531)
> ... 29 more
> Caused by: java.lang.AssertionError
> at
> org.apache.calcite.sql2rel.RelDecorrelator.decorrelateRel(RelDecorrelator.java:454)
> ... 34 more{noformat}
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
[jira] [Updated] (CALCITE-2874) NOT IN correlated subquery has un-necessary join
[
https://issues.apache.org/jira/browse/CALCITE-2874?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Vineet Garg updated CALCITE-2874:
-
Labels: sub-query (was: )
> NOT IN correlated subquery has un-necessary join
>
>
> Key: CALCITE-2874
> URL: https://issues.apache.org/jira/browse/CALCITE-2874
> Project: Calcite
> Issue Type: Improvement
>Reporter: Vineet Garg
>Assignee: Vineet Garg
>Priority: Major
> Labels: sub-query
>
> Query:
> {code:sql}
> select count(*) as c
> from "scott".emp as e
> where sal + 100 not in (
> select deptno
> from dept
> where dname = e.ename);
> {code}
> Plan:
> {code}
> EnumerableAggregate(group=[{}], C=[COUNT()])
> EnumerableCalc(expr#0..9=[{inputs}], expr#10=[IS NOT NULL($t7)],
> expr#11=[<($t5, $t4)], expr#12=[OR($t10, $t11)], expr#13=[IS NOT TRUE($t12)],
> expr#14=[0], expr#15=[=($t4, $t14)], expr#16=[IS TRUE($t15)], expr#17=[IS
> NULL($t2)], expr#18=[OR($t13, $t16, $t17)], proj#0..9=[{exprs}],
> $condition=[$t18])
> EnumerableJoin(condition=[AND(=($1, $8), =($2, $9))], joinType=[left])
> EnumerableCalc(expr#0..5=[{inputs}], expr#6=[IS NOT NULL($t2)],
> expr#7=[0], expr#8=[=($t4, $t7)], expr#9=[IS TRUE($t8)], expr#10=[OR($t6,
> $t9)], proj#0..5=[{exprs}], $condition=[$t10])
> EnumerableJoin(condition=[=($1, $3)], joinType=[left])
> EnumerableCalc(expr#0..7=[{inputs}], proj#0..1=[{exprs}], SAL=[$t5])
> EnumerableTableScan(table=[[scott, EMP]])
> EnumerableAggregate(group=[{1}], c=[COUNT()], ck=[COUNT($0)])
> EnumerableCalc(expr#0..2=[{inputs}], expr#3=[IS NOT NULL($t1)],
> proj#0..2=[{exprs}], $condition=[$t3])
> EnumerableTableScan(table=[[scott, DEPT]])
> EnumerableCalc(expr#0..4=[{inputs}], DEPTNO=[$t2], i=[$t3],
> DNAME=[$t4], SAL=[$t0])
> EnumerableJoin(condition=[=($1, $2)], joinType=[inner])
> EnumerableCalc(expr#0=[{inputs}], expr#1=[100], expr#2=[+($t0,
> $t1)], SAL=[$t0], $f1=[$t2])
> EnumerableAggregate(group=[{5}])
> EnumerableTableScan(table=[[scott, EMP]])
> EnumerableCalc(expr#0..2=[{inputs}], expr#3=[true], expr#4=[IS NOT
> NULL($t1)], DEPTNO=[$t0], i=[$t3], DNAME=[$t1], $condition=[$t4])
> EnumerableTableScan(table=[[scott, DEPT]])
> {code}
> Bottom inner join b/w EMP and DEPT could be removed (only DEPT scan +
> project is required) and top join can have sal+100 = dept.deptno condition.
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
[jira] [Created] (CALCITE-2874) NOT IN correlated subquery has un-necessary join
Vineet Garg created CALCITE-2874:
Summary: NOT IN correlated subquery has un-necessary join
Key: CALCITE-2874
URL: https://issues.apache.org/jira/browse/CALCITE-2874
Project: Calcite
Issue Type: Improvement
Reporter: Vineet Garg
Query:
{code:sql}
select count(*) as c
from "scott".emp as e
where sal + 100 not in (
select deptno
from dept
where dname = e.ename);
{code}
Plan:
{code}
EnumerableAggregate(group=[{}], C=[COUNT()])
EnumerableCalc(expr#0..9=[{inputs}], expr#10=[IS NOT NULL($t7)],
expr#11=[<($t5, $t4)], expr#12=[OR($t10, $t11)], expr#13=[IS NOT TRUE($t12)],
expr#14=[0], expr#15=[=($t4, $t14)], expr#16=[IS TRUE($t15)], expr#17=[IS
NULL($t2)], expr#18=[OR($t13, $t16, $t17)], proj#0..9=[{exprs}],
$condition=[$t18])
EnumerableJoin(condition=[AND(=($1, $8), =($2, $9))], joinType=[left])
EnumerableCalc(expr#0..5=[{inputs}], expr#6=[IS NOT NULL($t2)],
expr#7=[0], expr#8=[=($t4, $t7)], expr#9=[IS TRUE($t8)], expr#10=[OR($t6,
$t9)], proj#0..5=[{exprs}], $condition=[$t10])
EnumerableJoin(condition=[=($1, $3)], joinType=[left])
EnumerableCalc(expr#0..7=[{inputs}], proj#0..1=[{exprs}], SAL=[$t5])
EnumerableTableScan(table=[[scott, EMP]])
EnumerableAggregate(group=[{1}], c=[COUNT()], ck=[COUNT($0)])
EnumerableCalc(expr#0..2=[{inputs}], expr#3=[IS NOT NULL($t1)],
proj#0..2=[{exprs}], $condition=[$t3])
EnumerableTableScan(table=[[scott, DEPT]])
EnumerableCalc(expr#0..4=[{inputs}], DEPTNO=[$t2], i=[$t3], DNAME=[$t4],
SAL=[$t0])
EnumerableJoin(condition=[=($1, $2)], joinType=[inner])
EnumerableCalc(expr#0=[{inputs}], expr#1=[100], expr#2=[+($t0, $t1)],
SAL=[$t0], $f1=[$t2])
EnumerableAggregate(group=[{5}])
EnumerableTableScan(table=[[scott, EMP]])
EnumerableCalc(expr#0..2=[{inputs}], expr#3=[true], expr#4=[IS NOT
NULL($t1)], DEPTNO=[$t0], i=[$t3], DNAME=[$t1], $condition=[$t4])
EnumerableTableScan(table=[[scott, DEPT]])
{code}
Bottom inner join b/w EMP and DEPT could be removed (only DEPT scan + project
is required) and top join can have sal+100 = dept.deptno condition.
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
[jira] [Assigned] (CALCITE-2874) NOT IN correlated subquery has un-necessary join
[
https://issues.apache.org/jira/browse/CALCITE-2874?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Vineet Garg reassigned CALCITE-2874:
Assignee: Vineet Garg
> NOT IN correlated subquery has un-necessary join
>
>
> Key: CALCITE-2874
> URL: https://issues.apache.org/jira/browse/CALCITE-2874
> Project: Calcite
> Issue Type: Improvement
>Reporter: Vineet Garg
>Assignee: Vineet Garg
>Priority: Major
>
> Query:
> {code:sql}
> select count(*) as c
> from "scott".emp as e
> where sal + 100 not in (
> select deptno
> from dept
> where dname = e.ename);
> {code}
> Plan:
> {code}
> EnumerableAggregate(group=[{}], C=[COUNT()])
> EnumerableCalc(expr#0..9=[{inputs}], expr#10=[IS NOT NULL($t7)],
> expr#11=[<($t5, $t4)], expr#12=[OR($t10, $t11)], expr#13=[IS NOT TRUE($t12)],
> expr#14=[0], expr#15=[=($t4, $t14)], expr#16=[IS TRUE($t15)], expr#17=[IS
> NULL($t2)], expr#18=[OR($t13, $t16, $t17)], proj#0..9=[{exprs}],
> $condition=[$t18])
> EnumerableJoin(condition=[AND(=($1, $8), =($2, $9))], joinType=[left])
> EnumerableCalc(expr#0..5=[{inputs}], expr#6=[IS NOT NULL($t2)],
> expr#7=[0], expr#8=[=($t4, $t7)], expr#9=[IS TRUE($t8)], expr#10=[OR($t6,
> $t9)], proj#0..5=[{exprs}], $condition=[$t10])
> EnumerableJoin(condition=[=($1, $3)], joinType=[left])
> EnumerableCalc(expr#0..7=[{inputs}], proj#0..1=[{exprs}], SAL=[$t5])
> EnumerableTableScan(table=[[scott, EMP]])
> EnumerableAggregate(group=[{1}], c=[COUNT()], ck=[COUNT($0)])
> EnumerableCalc(expr#0..2=[{inputs}], expr#3=[IS NOT NULL($t1)],
> proj#0..2=[{exprs}], $condition=[$t3])
> EnumerableTableScan(table=[[scott, DEPT]])
> EnumerableCalc(expr#0..4=[{inputs}], DEPTNO=[$t2], i=[$t3],
> DNAME=[$t4], SAL=[$t0])
> EnumerableJoin(condition=[=($1, $2)], joinType=[inner])
> EnumerableCalc(expr#0=[{inputs}], expr#1=[100], expr#2=[+($t0,
> $t1)], SAL=[$t0], $f1=[$t2])
> EnumerableAggregate(group=[{5}])
> EnumerableTableScan(table=[[scott, EMP]])
> EnumerableCalc(expr#0..2=[{inputs}], expr#3=[true], expr#4=[IS NOT
> NULL($t1)], DEPTNO=[$t0], i=[$t3], DNAME=[$t1], $condition=[$t4])
> EnumerableTableScan(table=[[scott, DEPT]])
> {code}
> Bottom inner join b/w EMP and DEPT could be removed (only DEPT scan +
> project is required) and top join can have sal+100 = dept.deptno condition.
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
[jira] [Commented] (CALCITE-1513) Correlated NOT IN query throws AssertionError
[
https://issues.apache.org/jira/browse/CALCITE-1513?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16775551#comment-16775551
]
Vineet Garg commented on CALCITE-1513:
--
[~julianhyde] I have created a PR to add the test at
https://github.com/apache/calcite/pull/1063
> Correlated NOT IN query throws AssertionError
> -
>
> Key: CALCITE-1513
> URL: https://issues.apache.org/jira/browse/CALCITE-1513
> Project: Calcite
> Issue Type: Bug
>Reporter: Julian Hyde
>Assignee: Vineet Garg
>Priority: Major
> Labels: pull-request-available, sub-query
> Time Spent: 10m
> Remaining Estimate: 0h
>
> A correlated NOT IN query throws AssertionError during decorrelation. Here is
> a patch that reproduces the case:
> {noformat}
> diff --git a/core/src/test/resources/sql/subquery.iq
> b/core/src/test/resources/sql/subquery.iq
> index ad71655..975aae1 100644
> --- a/core/src/test/resources/sql/subquery.iq
> +++ b/core/src/test/resources/sql/subquery.iq
> @@ -370,6 +370,22 @@ where e.job not in (
> !plan
> !}
>
> +# Correlated condition in NOT IN.
> +# Tested on Oracle.
> +select count(*) as c
> +from "scott".emp as e
> +where sal + 100 not in (
> + select comm
> + from "scott".emp
> + where job = e.job);
> + EMPNO ENAME JOB MGR HIREDATE SAL COMM
> DEPTNO
> +-- -- - -- - -- --
> --
> + 7499 ALLEN SALESMAN7698 20-FEB-81 1600300
> 30
> + 7521 WARD SALESMAN7698 22-FEB-81 1250500
> 30
> + 7654 MARTIN SALESMAN7698 28-SEP-81 1250 1400
> 30
> + 7844 TURNER SALESMAN7698 08-SEP-81 1500 0
> 30
> +!ok
> +
> # [CALCITE-864] Correlation variable has incorrect row type if it is
> populated
> # by right side of a Join
> select *
> {noformat}
> And here is the stack:
> {noformat}
> java.lang.AssertionError: Internal error: While invoking method 'public
> org.apache.calcite.sql2rel.RelDecorrelator$Frame
> org.apache.calcite.sql2rel.RelDecorrelator.decorrelateRel(org.apache.calcite.rel.logical.LogicalAggregate)'
> at org.apache.calcite.util.Util.newInternal(Util.java:792)
> at org.apache.calcite.util.ReflectUtil$2.invoke(ReflectUtil.java:534)
> at
> org.apache.calcite.sql2rel.RelDecorrelator.getInvoke(RelDecorrelator.java:601)
> at
> org.apache.calcite.sql2rel.RelDecorrelator.decorrelate(RelDecorrelator.java:242)
> at
> org.apache.calcite.sql2rel.RelDecorrelator.decorrelateQuery(RelDecorrelator.java:211)
> at
> org.apache.calcite.tools.Programs$DecorrelateProgram.run(Programs.java:370)
> at
> org.apache.calcite.tools.Programs$SequenceProgram.run(Programs.java:351)
> at org.apache.calcite.prepare.Prepare.optimize(Prepare.java:155)
> at org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:286)
> at org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:195)
> at
> org.apache.calcite.prepare.CalcitePrepareImpl.prepare2_(CalcitePrepareImpl.java:748)
> at
> org.apache.calcite.prepare.CalcitePrepareImpl.prepare_(CalcitePrepareImpl.java:610)
> at
> org.apache.calcite.prepare.CalcitePrepareImpl.prepareSql(CalcitePrepareImpl.java:580)
> Caused by: java.lang.reflect.InvocationTargetException
> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
> at
> sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
> at
> sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
> at java.lang.reflect.Method.invoke(Method.java:498)
> at org.apache.calcite.util.ReflectUtil$2.invoke(ReflectUtil.java:531)
> ... 29 more
> Caused by: java.lang.AssertionError
> at
> org.apache.calcite.sql2rel.RelDecorrelator.decorrelateRel(RelDecorrelator.java:454)
> ... 34 more{noformat}
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
[jira] [Assigned] (CALCITE-1513) Correlated NOT IN query throws AssertionError
[
https://issues.apache.org/jira/browse/CALCITE-1513?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Vineet Garg reassigned CALCITE-1513:
Assignee: Vineet Garg
> Correlated NOT IN query throws AssertionError
> -
>
> Key: CALCITE-1513
> URL: https://issues.apache.org/jira/browse/CALCITE-1513
> Project: Calcite
> Issue Type: Bug
>Reporter: Julian Hyde
>Assignee: Vineet Garg
>Priority: Major
> Labels: sub-query
>
> A correlated NOT IN query throws AssertionError during decorrelation. Here is
> a patch that reproduces the case:
> {noformat}
> diff --git a/core/src/test/resources/sql/subquery.iq
> b/core/src/test/resources/sql/subquery.iq
> index ad71655..975aae1 100644
> --- a/core/src/test/resources/sql/subquery.iq
> +++ b/core/src/test/resources/sql/subquery.iq
> @@ -370,6 +370,22 @@ where e.job not in (
> !plan
> !}
>
> +# Correlated condition in NOT IN.
> +# Tested on Oracle.
> +select count(*) as c
> +from "scott".emp as e
> +where sal + 100 not in (
> + select comm
> + from "scott".emp
> + where job = e.job);
> + EMPNO ENAME JOB MGR HIREDATE SAL COMM
> DEPTNO
> +-- -- - -- - -- --
> --
> + 7499 ALLEN SALESMAN7698 20-FEB-81 1600300
> 30
> + 7521 WARD SALESMAN7698 22-FEB-81 1250500
> 30
> + 7654 MARTIN SALESMAN7698 28-SEP-81 1250 1400
> 30
> + 7844 TURNER SALESMAN7698 08-SEP-81 1500 0
> 30
> +!ok
> +
> # [CALCITE-864] Correlation variable has incorrect row type if it is
> populated
> # by right side of a Join
> select *
> {noformat}
> And here is the stack:
> {noformat}
> java.lang.AssertionError: Internal error: While invoking method 'public
> org.apache.calcite.sql2rel.RelDecorrelator$Frame
> org.apache.calcite.sql2rel.RelDecorrelator.decorrelateRel(org.apache.calcite.rel.logical.LogicalAggregate)'
> at org.apache.calcite.util.Util.newInternal(Util.java:792)
> at org.apache.calcite.util.ReflectUtil$2.invoke(ReflectUtil.java:534)
> at
> org.apache.calcite.sql2rel.RelDecorrelator.getInvoke(RelDecorrelator.java:601)
> at
> org.apache.calcite.sql2rel.RelDecorrelator.decorrelate(RelDecorrelator.java:242)
> at
> org.apache.calcite.sql2rel.RelDecorrelator.decorrelateQuery(RelDecorrelator.java:211)
> at
> org.apache.calcite.tools.Programs$DecorrelateProgram.run(Programs.java:370)
> at
> org.apache.calcite.tools.Programs$SequenceProgram.run(Programs.java:351)
> at org.apache.calcite.prepare.Prepare.optimize(Prepare.java:155)
> at org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:286)
> at org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:195)
> at
> org.apache.calcite.prepare.CalcitePrepareImpl.prepare2_(CalcitePrepareImpl.java:748)
> at
> org.apache.calcite.prepare.CalcitePrepareImpl.prepare_(CalcitePrepareImpl.java:610)
> at
> org.apache.calcite.prepare.CalcitePrepareImpl.prepareSql(CalcitePrepareImpl.java:580)
> Caused by: java.lang.reflect.InvocationTargetException
> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
> at
> sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
> at
> sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
> at java.lang.reflect.Method.invoke(Method.java:498)
> at org.apache.calcite.util.ReflectUtil$2.invoke(ReflectUtil.java:531)
> ... 29 more
> Caused by: java.lang.AssertionError
> at
> org.apache.calcite.sql2rel.RelDecorrelator.decorrelateRel(RelDecorrelator.java:454)
> ... 34 more{noformat}
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
[jira] [Commented] (CALCITE-1513) Correlated NOT IN query throws AssertionError
[
https://issues.apache.org/jira/browse/CALCITE-1513?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16774829#comment-16774829
]
Vineet Garg commented on CALCITE-1513:
--
Although correct the above plan is slightly inefficient. The bottom inner join
b/w EMP and DEPT could be removed (only DEPT scan + project is required) and
top join can have {{sal+100 = dept.deptno}} condition.
> Correlated NOT IN query throws AssertionError
> -
>
> Key: CALCITE-1513
> URL: https://issues.apache.org/jira/browse/CALCITE-1513
> Project: Calcite
> Issue Type: Bug
>Reporter: Julian Hyde
>Priority: Major
> Labels: sub-query
>
> A correlated NOT IN query throws AssertionError during decorrelation. Here is
> a patch that reproduces the case:
> {noformat}
> diff --git a/core/src/test/resources/sql/subquery.iq
> b/core/src/test/resources/sql/subquery.iq
> index ad71655..975aae1 100644
> --- a/core/src/test/resources/sql/subquery.iq
> +++ b/core/src/test/resources/sql/subquery.iq
> @@ -370,6 +370,22 @@ where e.job not in (
> !plan
> !}
>
> +# Correlated condition in NOT IN.
> +# Tested on Oracle.
> +select count(*) as c
> +from "scott".emp as e
> +where sal + 100 not in (
> + select comm
> + from "scott".emp
> + where job = e.job);
> + EMPNO ENAME JOB MGR HIREDATE SAL COMM
> DEPTNO
> +-- -- - -- - -- --
> --
> + 7499 ALLEN SALESMAN7698 20-FEB-81 1600300
> 30
> + 7521 WARD SALESMAN7698 22-FEB-81 1250500
> 30
> + 7654 MARTIN SALESMAN7698 28-SEP-81 1250 1400
> 30
> + 7844 TURNER SALESMAN7698 08-SEP-81 1500 0
> 30
> +!ok
> +
> # [CALCITE-864] Correlation variable has incorrect row type if it is
> populated
> # by right side of a Join
> select *
> {noformat}
> And here is the stack:
> {noformat}
> java.lang.AssertionError: Internal error: While invoking method 'public
> org.apache.calcite.sql2rel.RelDecorrelator$Frame
> org.apache.calcite.sql2rel.RelDecorrelator.decorrelateRel(org.apache.calcite.rel.logical.LogicalAggregate)'
> at org.apache.calcite.util.Util.newInternal(Util.java:792)
> at org.apache.calcite.util.ReflectUtil$2.invoke(ReflectUtil.java:534)
> at
> org.apache.calcite.sql2rel.RelDecorrelator.getInvoke(RelDecorrelator.java:601)
> at
> org.apache.calcite.sql2rel.RelDecorrelator.decorrelate(RelDecorrelator.java:242)
> at
> org.apache.calcite.sql2rel.RelDecorrelator.decorrelateQuery(RelDecorrelator.java:211)
> at
> org.apache.calcite.tools.Programs$DecorrelateProgram.run(Programs.java:370)
> at
> org.apache.calcite.tools.Programs$SequenceProgram.run(Programs.java:351)
> at org.apache.calcite.prepare.Prepare.optimize(Prepare.java:155)
> at org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:286)
> at org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:195)
> at
> org.apache.calcite.prepare.CalcitePrepareImpl.prepare2_(CalcitePrepareImpl.java:748)
> at
> org.apache.calcite.prepare.CalcitePrepareImpl.prepare_(CalcitePrepareImpl.java:610)
> at
> org.apache.calcite.prepare.CalcitePrepareImpl.prepareSql(CalcitePrepareImpl.java:580)
> Caused by: java.lang.reflect.InvocationTargetException
> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
> at
> sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
> at
> sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
> at java.lang.reflect.Method.invoke(Method.java:498)
> at org.apache.calcite.util.ReflectUtil$2.invoke(ReflectUtil.java:531)
> ... 29 more
> Caused by: java.lang.AssertionError
> at
> org.apache.calcite.sql2rel.RelDecorrelator.decorrelateRel(RelDecorrelator.java:454)
> ... 34 more{noformat}
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
[jira] [Comment Edited] (CALCITE-1513) Correlated NOT IN query throws AssertionError
[
https://issues.apache.org/jira/browse/CALCITE-1513?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16774826#comment-16774826
]
Vineet Garg edited comment on CALCITE-1513 at 2/22/19 6:21 AM:
---
The query doesn't throw assertion anymore. Following is the plan generated by
this query which looks correct to me:
{code:sql}
EnumerableAggregate(group=[{}], C=[COUNT()])
EnumerableCalc(expr#0..9=[{inputs}], expr#10=[IS NOT NULL($t7)],
expr#11=[<($t5, $t4)], expr#12=[OR($t10, $t11)], expr#13=[IS NOT TRUE($t12)],
expr#14=[0], expr#15=[=($t4, $t14)], expr#16=[IS TRUE($t15)], expr#17=[IS
NULL($t2)], expr#18=[OR($t13, $t16, $t17)], proj#0..9=[{exprs}],
$condition=[$t18])
EnumerableJoin(condition=[AND(=($1, $8), =($2, $9))], joinType=[left])
EnumerableCalc(expr#0..5=[{inputs}], expr#6=[IS NOT NULL($t2)],
expr#7=[0], expr#8=[=($t4, $t7)], expr#9=[IS TRUE($t8)], expr#10=[OR($t6,
$t9)], proj#0..5=[{exprs}], $condition=[$t10])
EnumerableJoin(condition=[=($1, $3)], joinType=[left])
EnumerableCalc(expr#0..7=[{inputs}], proj#0..1=[{exprs}], SAL=[$t5])
EnumerableTableScan(table=[[scott, EMP]])
EnumerableAggregate(group=[{1}], c=[COUNT()], ck=[COUNT($0)])
EnumerableCalc(expr#0..2=[{inputs}], expr#3=[IS NOT NULL($t1)],
proj#0..2=[{exprs}], $condition=[$t3])
EnumerableTableScan(table=[[scott, DEPT]])
EnumerableCalc(expr#0..4=[{inputs}], DEPTNO=[$t2], i=[$t3], DNAME=[$t4],
SAL=[$t0])
EnumerableJoin(condition=[=($1, $2)], joinType=[inner])
EnumerableCalc(expr#0=[{inputs}], expr#1=[100], expr#2=[+($t0, $t1)],
SAL=[$t0], $f1=[$t2])
EnumerableAggregate(group=[{5}])
EnumerableTableScan(table=[[scott, EMP]])
EnumerableCalc(expr#0..2=[{inputs}], expr#3=[true], expr#4=[IS NOT
NULL($t1)], DEPTNO=[$t0], i=[$t3], DNAME=[$t1], $condition=[$t4])
EnumerableTableScan(table=[[scott, DEPT]])
{code}
I'll create a pull request with the test case.
Edit: The query I used for above plan is a bit different from the jira:
{code:sql}
select count(*) as c
from "scott".emp as e
where sal + 100 not in (
select deptno
from dept
where dname = e.ename);
{code}
was (Author: vgarg):
The query doesn't throw assertion anymore. Following is the plan generated by
this query which looks correct to me:
{code:sql}
EnumerableAggregate(group=[{}], C=[COUNT()])
EnumerableCalc(expr#0..9=[{inputs}], expr#10=[IS NOT NULL($t7)],
expr#11=[<($t5, $t4)], expr#12=[OR($t10, $t11)], expr#13=[IS NOT TRUE($t12)],
expr#14=[0], expr#15=[=($t4, $t14)], expr#16=[IS TRUE($t15)], expr#17=[IS
NULL($t2)], expr#18=[OR($t13, $t16, $t17)], proj#0..9=[{exprs}],
$condition=[$t18])
EnumerableJoin(condition=[AND(=($1, $8), =($2, $9))], joinType=[left])
EnumerableCalc(expr#0..5=[{inputs}], expr#6=[IS NOT NULL($t2)],
expr#7=[0], expr#8=[=($t4, $t7)], expr#9=[IS TRUE($t8)], expr#10=[OR($t6,
$t9)], proj#0..5=[{exprs}], $condition=[$t10])
EnumerableJoin(condition=[=($1, $3)], joinType=[left])
EnumerableCalc(expr#0..7=[{inputs}], proj#0..1=[{exprs}], SAL=[$t5])
EnumerableTableScan(table=[[scott, EMP]])
EnumerableAggregate(group=[{1}], c=[COUNT()], ck=[COUNT($0)])
EnumerableCalc(expr#0..2=[{inputs}], expr#3=[IS NOT NULL($t1)],
proj#0..2=[{exprs}], $condition=[$t3])
EnumerableTableScan(table=[[scott, DEPT]])
EnumerableCalc(expr#0..4=[{inputs}], DEPTNO=[$t2], i=[$t3], DNAME=[$t4],
SAL=[$t0])
EnumerableJoin(condition=[=($1, $2)], joinType=[inner])
EnumerableCalc(expr#0=[{inputs}], expr#1=[100], expr#2=[+($t0, $t1)],
SAL=[$t0], $f1=[$t2])
EnumerableAggregate(group=[{5}])
EnumerableTableScan(table=[[scott, EMP]])
EnumerableCalc(expr#0..2=[{inputs}], expr#3=[true], expr#4=[IS NOT
NULL($t1)], DEPTNO=[$t0], i=[$t3], DNAME=[$t1], $condition=[$t4])
EnumerableTableScan(table=[[scott, DEPT]])
{code}
I'll create a pull request with the test case.
> Correlated NOT IN query throws AssertionError
> -
>
> Key: CALCITE-1513
> URL: https://issues.apache.org/jira/browse/CALCITE-1513
> Project: Calcite
> Issue Type: Bug
>Reporter: Julian Hyde
>Priority: Major
> Labels: sub-query
>
> A correlated NOT IN query throws AssertionError during decorrelation. Here is
> a patch that reproduces the case:
> {noformat}
> diff --git a/core/src/test/resources/sql/subquery.iq
> b/core/src/test/resources/sql/subquery.iq
> index ad71655..975aae1 100644
> --- a/core/src/test/resources/sql/subquery.iq
> +++ b/core/src/test/resources/sql/subquery.iq
> @@ -370,6 +370,22 @@ where e.job not in (
> !plan
> !}
>
> +# Correlated condition in NOT IN.
> +# Tested on Oracle.
> +select count(*) as
[jira] [Comment Edited] (CALCITE-1513) Correlated NOT IN query throws AssertionError
[
https://issues.apache.org/jira/browse/CALCITE-1513?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16774826#comment-16774826
]
Vineet Garg edited comment on CALCITE-1513 at 2/22/19 6:18 AM:
---
The query doesn't throw assertion anymore. Following is the plan generated by
this query which looks correct to me:
{code:sql}
EnumerableAggregate(group=[{}], C=[COUNT()])
EnumerableCalc(expr#0..9=[{inputs}], expr#10=[IS NOT NULL($t7)],
expr#11=[<($t5, $t4)], expr#12=[OR($t10, $t11)], expr#13=[IS NOT TRUE($t12)],
expr#14=[0], expr#15=[=($t4, $t14)], expr#16=[IS TRUE($t15)], expr#17=[IS
NULL($t2)], expr#18=[OR($t13, $t16, $t17)], proj#0..9=[{exprs}],
$condition=[$t18])
EnumerableJoin(condition=[AND(=($1, $8), =($2, $9))], joinType=[left])
EnumerableCalc(expr#0..5=[{inputs}], expr#6=[IS NOT NULL($t2)],
expr#7=[0], expr#8=[=($t4, $t7)], expr#9=[IS TRUE($t8)], expr#10=[OR($t6,
$t9)], proj#0..5=[{exprs}], $condition=[$t10])
EnumerableJoin(condition=[=($1, $3)], joinType=[left])
EnumerableCalc(expr#0..7=[{inputs}], proj#0..1=[{exprs}], SAL=[$t5])
EnumerableTableScan(table=[[scott, EMP]])
EnumerableAggregate(group=[{1}], c=[COUNT()], ck=[COUNT($0)])
EnumerableCalc(expr#0..2=[{inputs}], expr#3=[IS NOT NULL($t1)],
proj#0..2=[{exprs}], $condition=[$t3])
EnumerableTableScan(table=[[scott, DEPT]])
EnumerableCalc(expr#0..4=[{inputs}], DEPTNO=[$t2], i=[$t3], DNAME=[$t4],
SAL=[$t0])
EnumerableJoin(condition=[=($1, $2)], joinType=[inner])
EnumerableCalc(expr#0=[{inputs}], expr#1=[100], expr#2=[+($t0, $t1)],
SAL=[$t0], $f1=[$t2])
EnumerableAggregate(group=[{5}])
EnumerableTableScan(table=[[scott, EMP]])
EnumerableCalc(expr#0..2=[{inputs}], expr#3=[true], expr#4=[IS NOT
NULL($t1)], DEPTNO=[$t0], i=[$t3], DNAME=[$t1], $condition=[$t4])
EnumerableTableScan(table=[[scott, DEPT]])
{code}
I'll create a pull request with the test case.
was (Author: vgarg):
The query doesn't throw assertion anymore. Following is the plan generated by
this query which looks correct to me:
{code:sql}
EnumerableAggregate(group=[{}], C=[COUNT()])
EnumerableCalc(expr#0..9=[{inputs}], expr#10=[IS NOT NULL($t7)],
expr#11=[<($t5, $t4)], expr#12=[OR($t10, $t11)], expr#13=[IS NOT TRUE($t12)],
expr#14=[0], expr#15=[=($t4, $t14)], expr#16=[IS TRUE($t15)], expr#17=[IS
NULL($t2)], expr#18=[OR($t13, $t16, $t17)], proj#0..9=[{exprs}],
$condition=[$t18])
EnumerableJoin(condition=[AND(=($1, $8), =($2, $9))], joinType=[left])
EnumerableCalc(expr#0..5=[{inputs}], expr#6=[IS NOT NULL($t2)],
expr#7=[0], expr#8=[=($t4, $t7)], expr#9=[IS TRUE($t8)], expr#10=[OR($t6,
$t9)], proj#0..5=[{exprs}], $condition=[$t10])
EnumerableJoin(condition=[=($1, $3)], joinType=[left])
EnumerableCalc(expr#0..7=[{inputs}], proj#0..1=[{exprs}], SAL=[$t5])
EnumerableTableScan(table=[[scott, EMP]])
EnumerableAggregate(group=[{1}], c=[COUNT()], ck=[COUNT($0)])
EnumerableCalc(expr#0..2=[{inputs}], expr#3=[IS NOT NULL($t1)],
proj#0..2=[{exprs}], $condition=[$t3])
EnumerableTableScan(table=[[scott, DEPT]])
EnumerableCalc(expr#0..4=[{inputs}], DEPTNO=[$t2], i=[$t3], DNAME=[$t4],
SAL=[$t0])
EnumerableJoin(condition=[=($1, $2)], joinType=[inner])
EnumerableCalc(expr#0=[{inputs}], expr#1=[100], expr#2=[+($t0, $t1)],
SAL=[$t0], $f1=[$t2])
EnumerableAggregate(group=[{5}])
EnumerableTableScan(table=[[scott, EMP]])
EnumerableCalc(expr#0..2=[{inputs}], expr#3=[true], expr#4=[IS NOT
NULL($t1)], DEPTNO=[$t0], i=[$t3], DNAME=[$t1], $condition=[$t4])
EnumerableTableScan(table=[[scott, DEPT]])
{code}
> Correlated NOT IN query throws AssertionError
> -
>
> Key: CALCITE-1513
> URL: https://issues.apache.org/jira/browse/CALCITE-1513
> Project: Calcite
> Issue Type: Bug
>Reporter: Julian Hyde
>Priority: Major
> Labels: sub-query
>
> A correlated NOT IN query throws AssertionError during decorrelation. Here is
> a patch that reproduces the case:
> {noformat}
> diff --git a/core/src/test/resources/sql/subquery.iq
> b/core/src/test/resources/sql/subquery.iq
> index ad71655..975aae1 100644
> --- a/core/src/test/resources/sql/subquery.iq
> +++ b/core/src/test/resources/sql/subquery.iq
> @@ -370,6 +370,22 @@ where e.job not in (
> !plan
> !}
>
> +# Correlated condition in NOT IN.
> +# Tested on Oracle.
> +select count(*) as c
> +from "scott".emp as e
> +where sal + 100 not in (
> + select comm
> + from "scott".emp
> + where job = e.job);
> + EMPNO ENAME JOB MGR HIREDATE SAL COMM
> DEPTNO
> +-- -- - --
[jira] [Commented] (CALCITE-1513) Correlated NOT IN query throws AssertionError
[
https://issues.apache.org/jira/browse/CALCITE-1513?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16774826#comment-16774826
]
Vineet Garg commented on CALCITE-1513:
--
The query doesn't throw assertion anymore. Following is the plan generated by
this query which looks correct to me:
{code:sql}
EnumerableAggregate(group=[{}], C=[COUNT()])
EnumerableCalc(expr#0..9=[{inputs}], expr#10=[IS NOT NULL($t7)],
expr#11=[<($t5, $t4)], expr#12=[OR($t10, $t11)], expr#13=[IS NOT TRUE($t12)],
expr#14=[0], expr#15=[=($t4, $t14)], expr#16=[IS TRUE($t15)], expr#17=[IS
NULL($t2)], expr#18=[OR($t13, $t16, $t17)], proj#0..9=[{exprs}],
$condition=[$t18])
EnumerableJoin(condition=[AND(=($1, $8), =($2, $9))], joinType=[left])
EnumerableCalc(expr#0..5=[{inputs}], expr#6=[IS NOT NULL($t2)],
expr#7=[0], expr#8=[=($t4, $t7)], expr#9=[IS TRUE($t8)], expr#10=[OR($t6,
$t9)], proj#0..5=[{exprs}], $condition=[$t10])
EnumerableJoin(condition=[=($1, $3)], joinType=[left])
EnumerableCalc(expr#0..7=[{inputs}], proj#0..1=[{exprs}], SAL=[$t5])
EnumerableTableScan(table=[[scott, EMP]])
EnumerableAggregate(group=[{1}], c=[COUNT()], ck=[COUNT($0)])
EnumerableCalc(expr#0..2=[{inputs}], expr#3=[IS NOT NULL($t1)],
proj#0..2=[{exprs}], $condition=[$t3])
EnumerableTableScan(table=[[scott, DEPT]])
EnumerableCalc(expr#0..4=[{inputs}], DEPTNO=[$t2], i=[$t3], DNAME=[$t4],
SAL=[$t0])
EnumerableJoin(condition=[=($1, $2)], joinType=[inner])
EnumerableCalc(expr#0=[{inputs}], expr#1=[100], expr#2=[+($t0, $t1)],
SAL=[$t0], $f1=[$t2])
EnumerableAggregate(group=[{5}])
EnumerableTableScan(table=[[scott, EMP]])
EnumerableCalc(expr#0..2=[{inputs}], expr#3=[true], expr#4=[IS NOT
NULL($t1)], DEPTNO=[$t0], i=[$t3], DNAME=[$t1], $condition=[$t4])
EnumerableTableScan(table=[[scott, DEPT]])
{code}
> Correlated NOT IN query throws AssertionError
> -
>
> Key: CALCITE-1513
> URL: https://issues.apache.org/jira/browse/CALCITE-1513
> Project: Calcite
> Issue Type: Bug
>Reporter: Julian Hyde
>Priority: Major
> Labels: sub-query
>
> A correlated NOT IN query throws AssertionError during decorrelation. Here is
> a patch that reproduces the case:
> {noformat}
> diff --git a/core/src/test/resources/sql/subquery.iq
> b/core/src/test/resources/sql/subquery.iq
> index ad71655..975aae1 100644
> --- a/core/src/test/resources/sql/subquery.iq
> +++ b/core/src/test/resources/sql/subquery.iq
> @@ -370,6 +370,22 @@ where e.job not in (
> !plan
> !}
>
> +# Correlated condition in NOT IN.
> +# Tested on Oracle.
> +select count(*) as c
> +from "scott".emp as e
> +where sal + 100 not in (
> + select comm
> + from "scott".emp
> + where job = e.job);
> + EMPNO ENAME JOB MGR HIREDATE SAL COMM
> DEPTNO
> +-- -- - -- - -- --
> --
> + 7499 ALLEN SALESMAN7698 20-FEB-81 1600300
> 30
> + 7521 WARD SALESMAN7698 22-FEB-81 1250500
> 30
> + 7654 MARTIN SALESMAN7698 28-SEP-81 1250 1400
> 30
> + 7844 TURNER SALESMAN7698 08-SEP-81 1500 0
> 30
> +!ok
> +
> # [CALCITE-864] Correlation variable has incorrect row type if it is
> populated
> # by right side of a Join
> select *
> {noformat}
> And here is the stack:
> {noformat}
> java.lang.AssertionError: Internal error: While invoking method 'public
> org.apache.calcite.sql2rel.RelDecorrelator$Frame
> org.apache.calcite.sql2rel.RelDecorrelator.decorrelateRel(org.apache.calcite.rel.logical.LogicalAggregate)'
> at org.apache.calcite.util.Util.newInternal(Util.java:792)
> at org.apache.calcite.util.ReflectUtil$2.invoke(ReflectUtil.java:534)
> at
> org.apache.calcite.sql2rel.RelDecorrelator.getInvoke(RelDecorrelator.java:601)
> at
> org.apache.calcite.sql2rel.RelDecorrelator.decorrelate(RelDecorrelator.java:242)
> at
> org.apache.calcite.sql2rel.RelDecorrelator.decorrelateQuery(RelDecorrelator.java:211)
> at
> org.apache.calcite.tools.Programs$DecorrelateProgram.run(Programs.java:370)
> at
> org.apache.calcite.tools.Programs$SequenceProgram.run(Programs.java:351)
> at org.apache.calcite.prepare.Prepare.optimize(Prepare.java:155)
> at org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:286)
> at org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:195)
> at
> org.apache.calcite.prepare.CalcitePrepareImpl.prepare2_(CalcitePrepareImpl.java:748)
> at
> org.apache.calcite.prepare.CalcitePrepareImpl.prepare_(CalcitePrepareImpl.java:610)
>
[jira] [Commented] (CALCITE-1726) Subquery in FILTER is left untransformed
[
https://issues.apache.org/jira/browse/CALCITE-1726?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16763306#comment-16763306
]
Vineet Garg commented on CALCITE-1726:
--
Created a pull request to fix this at
[https://github.com/apache/calcite/pull/1035]
[~julianhyde] [~jcamachorodriguez] Would you mind taking a look?
> Subquery in FILTER is left untransformed
> -
>
> Key: CALCITE-1726
> URL: https://issues.apache.org/jira/browse/CALCITE-1726
> Project: Calcite
> Issue Type: Bug
>Reporter: Vineet Garg
>Assignee: Julian Hyde
>Priority: Major
> Labels: pull-request-available, sub-query
> Time Spent: 10m
> Remaining Estimate: 0h
>
> Reproducer
> Query:
> {code:SQL}
> select * from emp
> where empno IN (select (select max(sal) from emp) from dept)
> {code}
> Plan after {{SubqueryRemoveRule}}
> {code:SQL}
> LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4],
> SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
> LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4],
> SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
> LogicalJoin(condition=[=($0, $9)], joinType=[inner])
> LogicalTableScan(table=[[CATALOG, SALES, EMP]])
> LogicalAggregate(group=[{0}])
> LogicalProject(EXPR$0=[$SCALAR_QUERY({
> LogicalAggregate(group=[{}], EXPR$0=[MAX($0)])
> LogicalProject(SAL=[$5])
> LogicalTableScan(table=[[CATALOG, SALES, EMP]])
> })])
> LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
> {code}
> As you can notice scalar query in LogicalProject is left as it is
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
[jira] [Commented] (CALCITE-1726) Subquery in FILTER is left untransformed
[
https://issues.apache.org/jira/browse/CALCITE-1726?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16763303#comment-16763303
]
Vineet Garg commented on CALCITE-1726:
--
I took a look into this. Issue is that planner (hepplanner) executes subquery
rules (FILTER, PROJECT, JOIN) one by one. Rule Subquery:Project is invoked
first, this rule doesn't match because top RexSubquery node (corresponding to
FILTER) has yet to be removed/flattened. Next Subquery:Filter is invoked which
transforms the top RexSubquery node but leaves the bottom RexSubquery node
corresponding to Project untransformed.
Fix for this I believe is to create subquery hep planner program with
collection of rules instead of one by one.
> Subquery in FILTER is left untransformed
> -
>
> Key: CALCITE-1726
> URL: https://issues.apache.org/jira/browse/CALCITE-1726
> Project: Calcite
> Issue Type: Bug
>Reporter: Vineet Garg
>Assignee: Julian Hyde
>Priority: Major
> Labels: sub-query
>
> Reproducer
> Query:
> {code:SQL}
> select * from emp
> where empno IN (select (select max(sal) from emp) from dept)
> {code}
> Plan after {{SubqueryRemoveRule}}
> {code:SQL}
> LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4],
> SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
> LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4],
> SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
> LogicalJoin(condition=[=($0, $9)], joinType=[inner])
> LogicalTableScan(table=[[CATALOG, SALES, EMP]])
> LogicalAggregate(group=[{0}])
> LogicalProject(EXPR$0=[$SCALAR_QUERY({
> LogicalAggregate(group=[{}], EXPR$0=[MAX($0)])
> LogicalProject(SAL=[$5])
> LogicalTableScan(table=[[CATALOG, SALES, EMP]])
> })])
> LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
> {code}
> As you can notice scalar query in LogicalProject is left as it is
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
[jira] [Commented] (CALCITE-2329) Enhance SubQueryRemoveRule to rewrite IN operator with the constant from the left side more optimally
[
https://issues.apache.org/jira/browse/CALCITE-2329?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16757643#comment-16757643
]
Vineet Garg commented on CALCITE-2329:
--
bq. The "more optimal plan" pushes the "deptno=2" filter further towards the
table scan, whereas the original plan has it as a join condition
That make sense. I was looking at the overall plan which should be same as
EXISTS (without aggregate) according to "Since join condition is actually a
filter condition, it will be pushed into the filter during further planning
stages and join with the true condition is left."
> Enhance SubQueryRemoveRule to rewrite IN operator with the constant from the
> left side more optimally
> -
>
> Key: CALCITE-2329
> URL: https://issues.apache.org/jira/browse/CALCITE-2329
> Project: Calcite
> Issue Type: Improvement
>Reporter: Volodymyr Vysotskyi
>Assignee: Julian Hyde
>Priority: Major
> Fix For: 1.17.0
>
>
> Currently, for queries like this:
> {code:sql}
> select sal from emp where 2 IN (select deptno from dept)
> {code}
> SubQueryRemoveRule rules expand query plan in such a way:
> {noformat}
> LogicalProject(SAL=[$5])
> LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4],
> SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
> LogicalJoin(condition=[=(2, $9)], joinType=[inner])
> LogicalTableScan(table=[[CATALOG, SALES, EMP]])
> LogicalProject(DEPTNO=[$0])
> LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
> {noformat}
> Since join condition is actually a filter condition, it will be pushed into
> the filter during further planning stages and join with the true condition is
> left.
> But these types of the queries may be rewritten in the same way as EXISTS
> queries:
> {code:sql}
> select sal from emp where EXISTS (select deptno from dept where deptno=2)
> {code}
> with the more optimal plan:
> {noformat}
> LogicalProject(SAL=[$5])
> LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4],
> SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
> LogicalJoin(condition=[true], joinType=[inner])
> LogicalTableScan(table=[[CATALOG, SALES, EMP]])
> LogicalAggregate(group=[{0}])
> LogicalProject(i=[true])
> LogicalFilter(condition=[=($0, 2)])
> LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
> {noformat}
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
[jira] [Commented] (CALCITE-2329) Enhance SubQueryRemoveRule to rewrite IN operator with the constant from the left side more optimally
[
https://issues.apache.org/jira/browse/CALCITE-2329?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16756776#comment-16756776
]
Vineet Garg commented on CALCITE-2329:
--
[~vvysotskyi] Thanks, I guess it is correct now because of your fix.
[~julianhyde] If that is the case then isn't the original plan for IN better
than the rewrite to EXISTS one? Since original one skips aggregate.
I am struggling to figure out in what way is the new plan (with EXISTS) better
than the original one.
> Enhance SubQueryRemoveRule to rewrite IN operator with the constant from the
> left side more optimally
> -
>
> Key: CALCITE-2329
> URL: https://issues.apache.org/jira/browse/CALCITE-2329
> Project: Calcite
> Issue Type: Improvement
>Reporter: Volodymyr Vysotskyi
>Assignee: Julian Hyde
>Priority: Major
> Fix For: 1.17.0
>
>
> Currently, for queries like this:
> {code:sql}
> select sal from emp where 2 IN (select deptno from dept)
> {code}
> SubQueryRemoveRule rules expand query plan in such a way:
> {noformat}
> LogicalProject(SAL=[$5])
> LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4],
> SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
> LogicalJoin(condition=[=(2, $9)], joinType=[inner])
> LogicalTableScan(table=[[CATALOG, SALES, EMP]])
> LogicalProject(DEPTNO=[$0])
> LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
> {noformat}
> Since join condition is actually a filter condition, it will be pushed into
> the filter during further planning stages and join with the true condition is
> left.
> But these types of the queries may be rewritten in the same way as EXISTS
> queries:
> {code:sql}
> select sal from emp where EXISTS (select deptno from dept where deptno=2)
> {code}
> with the more optimal plan:
> {noformat}
> LogicalProject(SAL=[$5])
> LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4],
> SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
> LogicalJoin(condition=[true], joinType=[inner])
> LogicalTableScan(table=[[CATALOG, SALES, EMP]])
> LogicalAggregate(group=[{0}])
> LogicalProject(i=[true])
> LogicalFilter(condition=[=($0, 2)])
> LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
> {noformat}
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
[jira] [Commented] (CALCITE-2329) Enhance SubQueryRemoveRule to rewrite IN operator with the constant from the left side more optimally
[
https://issues.apache.org/jira/browse/CALCITE-2329?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16756630#comment-16756630
]
Vineet Garg commented on CALCITE-2329:
--
Sorry to bring up the old issues but I just noticed it. Following plan which is
posted in the jira for IN doesn't look correct to me
{noformat}
LogicalProject(SAL=[$5])
LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4],
SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
LogicalJoin(condition=[=(2, $9)], joinType=[inner])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
LogicalProject(DEPTNO=[$0]) <==missing
LogicalAggregate
LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
{noformat}
This plan is missing LogicalAggregate on the right side (on subquery). This
plan as it will produce incorrect result. The correct plan will have a
LogicalAggregate (group by deptno) which should further be optimized to be
{{group by }} due to deptno=2 filter. As a result this plan should
have actually been equivalent to plan with EXISTS in the first place.
> Enhance SubQueryRemoveRule to rewrite IN operator with the constant from the
> left side more optimally
> -
>
> Key: CALCITE-2329
> URL: https://issues.apache.org/jira/browse/CALCITE-2329
> Project: Calcite
> Issue Type: Improvement
>Reporter: Volodymyr Vysotskyi
>Assignee: Julian Hyde
>Priority: Major
> Fix For: 1.17.0
>
>
> Currently, for queries like this:
> {code:sql}
> select sal from emp where 2 IN (select deptno from dept)
> {code}
> SubQueryRemoveRule rules expand query plan in such a way:
> {noformat}
> LogicalProject(SAL=[$5])
> LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4],
> SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
> LogicalJoin(condition=[=(2, $9)], joinType=[inner])
> LogicalTableScan(table=[[CATALOG, SALES, EMP]])
> LogicalProject(DEPTNO=[$0])
> LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
> {noformat}
> Since join condition is actually a filter condition, it will be pushed into
> the filter during further planning stages and join with the true condition is
> left.
> But these types of the queries may be rewritten in the same way as EXISTS
> queries:
> {code:sql}
> select sal from emp where EXISTS (select deptno from dept where deptno=2)
> {code}
> with the more optimal plan:
> {noformat}
> LogicalProject(SAL=[$5])
> LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4],
> SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
> LogicalJoin(condition=[true], joinType=[inner])
> LogicalTableScan(table=[[CATALOG, SALES, EMP]])
> LogicalAggregate(group=[{0}])
> LogicalProject(i=[true])
> LogicalFilter(condition=[=($0, 2)])
> LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
> {noformat}
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
[jira] [Commented] (CALCITE-2295) Correlated SubQuery with Project will generate error plan
[
https://issues.apache.org/jira/browse/CALCITE-2295?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16469749#comment-16469749
]
Vineet Garg commented on CALCITE-2295:
--
[~julianhyde] Does *SqlToRelConverter* not support correlated subquery in
project? I looked at code *SqlToRelConverter::convertSelectList* and I don't
see Calcite handling case for correlation subquery expressions.
> Correlated SubQuery with Project will generate error plan
> -
>
> Key: CALCITE-2295
> URL: https://issues.apache.org/jira/browse/CALCITE-2295
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.15.0
>Reporter: godfrey he
>Assignee: Julian Hyde
>Priority: Critical
>
> test case in *RelOptRulesTest.java*:
> {code:java}
> @Test public void testDecorrelateWithProject() throws Exception {
> final String sql = "select sal,\n"
>+ "exists (select * from emp_b where emp.deptno =
> emp_b.deptno)\n"
>+ "from sales.emp";
> checkSubQuery(sql).withLateDecorrelation(true).check();
> }
> {code}
> after *SqlToRelConverter*, the plan is
> {code:java}
> LogicalProject(SAL=[$5], EXPR$1=[EXISTS({
> LogicalFilter(condition=[=($cor0.DEPTNO, $7)])
> LogicalTableScan(table=[[CATALOG, SALES, EMP_B]])
> })])
> LogicalTableScan(table=[[CATALOG, SALES, EMP]])
> {code}
> NOTES: *LogicalProject* does not have any *_variablesSet_* attributes.
> the correct plan should be:
> {code:java}
> LogicalProject(SAL=[$5], EXPR$1=[EXISTS({
> LogicalFilter(condition=[=($cor0.DEPTNO, $7)])
> LogicalTableScan(table=[[CATALOG, SALES, EMP_B]])
> })], variablesSet=[[$cor0]])
> LogicalTableScan(table=[[CATALOG, SALES, EMP]])
> {code}
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
[jira] [Commented] (CALCITE-2071) Query with IN and OR in WHERE clause returns wrong result
[
https://issues.apache.org/jira/browse/CALCITE-2071?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16291572#comment-16291572
]
Vineet Garg commented on CALCITE-2071:
--
Thanks [~julianhyde]. That makes sense to me.
> Query with IN and OR in WHERE clause returns wrong result
> -
>
> Key: CALCITE-2071
> URL: https://issues.apache.org/jira/browse/CALCITE-2071
> Project: Calcite
> Issue Type: Bug
>Reporter: Volodymyr Vysotskyi
>Assignee: Julian Hyde
> Fix For: 1.16.0
>
>
> Query in this test returns the wrong result (it returns empty result):
> {code:java}
> @Test
> @Ignore
> public void testWhereInOr() throws Exception {
> CalciteAssert.hr()
> .query("select \"empid\"\n"
> + "from \"hr\".\"emps\" t\n"
> + "where (\"empid\" in (select \"empid\" from
> \"hr\".\"emps\") \n"
> + "or \"empid\" in (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, "
> + " 12, 13, 14, 15, 16, 17, 18, 19, 20, 21,
> 22, 23, 24, 25) )\n"
> + " and \"empid\" in (100, 200, 150)")
> .returns("empid=100\n"
> + "empid=200\n"
> + "empid=150\n");
> }
> {code}
> Without condition in {{or}} operator
> {code:sql}
> \"empid\" in (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18,
> 19, 20, 21, 22, 23, 24, 25)
> {code}
> it returns the right result.
--
This message was sent by Atlassian JIRA
(v6.4.14#64029)
[jira] [Commented] (CALCITE-2071) Query with IN and OR in WHERE clause returns wrong result
[
https://issues.apache.org/jira/browse/CALCITE-2071?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16290056#comment-16290056
]
Vineet Garg commented on CALCITE-2071:
--
I wonder why changing the tests to quidem lead to this error? Does calcite go
through different code path in this case to do mergeJoin?
Also going forward should we add tests as quidem?
> Query with IN and OR in WHERE clause returns wrong result
> -
>
> Key: CALCITE-2071
> URL: https://issues.apache.org/jira/browse/CALCITE-2071
> Project: Calcite
> Issue Type: Bug
>Reporter: Volodymyr Vysotskyi
>Assignee: Julian Hyde
> Fix For: 1.16.0
>
>
> Query in this test returns the wrong result (it returns empty result):
> {code:java}
> @Test
> @Ignore
> public void testWhereInOr() throws Exception {
> CalciteAssert.hr()
> .query("select \"empid\"\n"
> + "from \"hr\".\"emps\" t\n"
> + "where (\"empid\" in (select \"empid\" from
> \"hr\".\"emps\") \n"
> + "or \"empid\" in (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, "
> + " 12, 13, 14, 15, 16, 17, 18, 19, 20, 21,
> 22, 23, 24, 25) )\n"
> + " and \"empid\" in (100, 200, 150)")
> .returns("empid=100\n"
> + "empid=200\n"
> + "empid=150\n");
> }
> {code}
> Without condition in {{or}} operator
> {code:sql}
> \"empid\" in (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18,
> 19, 20, 21, 22, 23, 24, 25)
> {code}
> it returns the right result.
--
This message was sent by Atlassian JIRA
(v6.4.14#64029)
[jira] [Commented] (CALCITE-2071) Query with IN and OR in WHERE clause returns wrong result
[
https://issues.apache.org/jira/browse/CALCITE-2071?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16288206#comment-16288206
]
Vineet Garg commented on CALCITE-2071:
--
[~julianhyde] [~vvysotskyi] I have updated the patch to look for AND
expressions. You can see the change at
https://github.com/apache/calcite/compare/master...vineetgarg02:CALCITE-2071-my-attempt.
Can you take a look?
> Query with IN and OR in WHERE clause returns wrong result
> -
>
> Key: CALCITE-2071
> URL: https://issues.apache.org/jira/browse/CALCITE-2071
> Project: Calcite
> Issue Type: Bug
>Reporter: Volodymyr Vysotskyi
>Assignee: Julian Hyde
> Fix For: 1.16.0
>
>
> Query in this test returns the wrong result (it returns empty result):
> {code:java}
> @Test
> @Ignore
> public void testWhereInOr() throws Exception {
> CalciteAssert.hr()
> .query("select \"empid\"\n"
> + "from \"hr\".\"emps\" t\n"
> + "where (\"empid\" in (select \"empid\" from
> \"hr\".\"emps\") \n"
> + "or \"empid\" in (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, "
> + " 12, 13, 14, 15, 16, 17, 18, 19, 20, 21,
> 22, 23, 24, 25) )\n"
> + " and \"empid\" in (100, 200, 150)")
> .returns("empid=100\n"
> + "empid=200\n"
> + "empid=150\n");
> }
> {code}
> Without condition in {{or}} operator
> {code:sql}
> \"empid\" in (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18,
> 19, 20, 21, 22, 23, 24, 25)
> {code}
> it returns the right result.
--
This message was sent by Atlassian JIRA
(v6.4.14#64029)
[jira] [Commented] (CALCITE-2071) Query with IN and OR in WHERE clause returns wrong result
[
https://issues.apache.org/jira/browse/CALCITE-2071?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16288103#comment-16288103
]
Vineet Garg commented on CALCITE-2071:
--
[~julianhyde] Thanks for looking at it. This change (looking for AND) causes 8
failures which need to be investigated. I'll update the patch soon once I
finish investigating the failures.
> Query with IN and OR in WHERE clause returns wrong result
> -
>
> Key: CALCITE-2071
> URL: https://issues.apache.org/jira/browse/CALCITE-2071
> Project: Calcite
> Issue Type: Bug
>Reporter: Volodymyr Vysotskyi
>Assignee: Julian Hyde
> Fix For: 1.16.0
>
>
> Query in this test returns the wrong result (it returns empty result):
> {code:java}
> @Test
> @Ignore
> public void testWhereInOr() throws Exception {
> CalciteAssert.hr()
> .query("select \"empid\"\n"
> + "from \"hr\".\"emps\" t\n"
> + "where (\"empid\" in (select \"empid\" from
> \"hr\".\"emps\") \n"
> + "or \"empid\" in (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, "
> + " 12, 13, 14, 15, 16, 17, 18, 19, 20, 21,
> 22, 23, 24, 25) )\n"
> + " and \"empid\" in (100, 200, 150)")
> .returns("empid=100\n"
> + "empid=200\n"
> + "empid=150\n");
> }
> {code}
> Without condition in {{or}} operator
> {code:sql}
> \"empid\" in (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18,
> 19, 20, 21, 22, 23, 24, 25)
> {code}
> it returns the right result.
--
This message was sent by Atlassian JIRA
(v6.4.14#64029)
[jira] [Commented] (CALCITE-2071) Query with IN operators in WHERE clause returns wrong result
[
https://issues.apache.org/jira/browse/CALCITE-2071?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16286874#comment-16286874
]
Vineet Garg commented on CALCITE-2071:
--
I took a look at it. I believe the issue is in findSubqueries which figure out
logic for each subquery. In this case it assign TRUE logic to each query which
is wrong since OR should change the logic to TRUE_FALSE_UNKNOWN. I attempted a
fix which is at
https://github.com/apache/calcite/compare/master...vineetgarg02:CALCITE-2071-my-attempt
, this passes all the tests as well. This also avoid bringing back
{{subQueryNeedsOuterJoin}} flag.
[~julianhyde] [~vvysotskyi] Let me know what do you think of this fix.
> Query with IN operators in WHERE clause returns wrong result
>
>
> Key: CALCITE-2071
> URL: https://issues.apache.org/jira/browse/CALCITE-2071
> Project: Calcite
> Issue Type: Bug
>Reporter: Volodymyr Vysotskyi
>Assignee: Julian Hyde
>
> Query in this test returns the wrong result (it returns empty result):
> {code:java}
> @Test
> @Ignore
> public void testWhereInOr() throws Exception {
> CalciteAssert.hr()
> .query("select \"empid\"\n"
> + "from \"hr\".\"emps\" t\n"
> + "where (\"empid\" in (select \"empid\" from
> \"hr\".\"emps\") \n"
> + "or \"empid\" in (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, "
> + " 12, 13, 14, 15, 16, 17, 18, 19, 20, 21,
> 22, 23, 24, 25) )\n"
> + " and \"empid\" in (100, 200, 150)")
> .returns("empid=100\n"
> + "empid=200\n"
> + "empid=150\n");
> }
> {code}
> Without condition in {{or}} operator
> {code:sql}
> \"empid\" in (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18,
> 19, 20, 21, 22, 23, 24, 25)
> {code}
> it returns the right result.
--
This message was sent by Atlassian JIRA
(v6.4.14#64029)
[jira] [Commented] (CALCITE-2071) Query with IN operators in WHERE clause returns wrong result
[
https://issues.apache.org/jira/browse/CALCITE-2071?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16283098#comment-16283098
]
Vineet Garg commented on CALCITE-2071:
--
Sorry I haven't gotten time to look at it yet. I'll look at it soon.
> Query with IN operators in WHERE clause returns wrong result
>
>
> Key: CALCITE-2071
> URL: https://issues.apache.org/jira/browse/CALCITE-2071
> Project: Calcite
> Issue Type: Bug
>Reporter: Volodymyr Vysotskyi
>Assignee: Julian Hyde
>
> Query in this test returns the wrong result (it returns empty result):
> {code:java}
> @Test
> @Ignore
> public void testWhereInOr() throws Exception {
> CalciteAssert.hr()
> .query("select \"empid\"\n"
> + "from \"hr\".\"emps\" t\n"
> + "where (\"empid\" in (select \"empid\" from
> \"hr\".\"emps\") \n"
> + "or \"empid\" in (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, "
> + " 12, 13, 14, 15, 16, 17, 18, 19, 20, 21,
> 22, 23, 24, 25) )\n"
> + " and \"empid\" in (100, 200, 150)")
> .returns("empid=100\n"
> + "empid=200\n"
> + "empid=150\n");
> }
> {code}
> Without condition in {{or}} operator
> {code:sql}
> \"empid\" in (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18,
> 19, 20, 21, 22, 23, 24, 25)
> {code}
> it returns the right result.
--
This message was sent by Atlassian JIRA
(v6.4.14#64029)
[jira] [Commented] (CALCITE-1851) Decorrelation should get rid of correlated predicates while decorrelation filter condition
[
https://issues.apache.org/jira/browse/CALCITE-1851?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16055014#comment-16055014
]
Vineet Garg commented on CALCITE-1851:
--
I am trying to write a test case. I'll attach it once I have it.
> Decorrelation should get rid of correlated predicates while decorrelation
> filter condition
> --
>
> Key: CALCITE-1851
> URL: https://issues.apache.org/jira/browse/CALCITE-1851
> Project: Calcite
> Issue Type: Improvement
> Components: core
>Reporter: Vineet Garg
>Assignee: Julian Hyde
>
> For queries such as
> {code:sql}
> select sal from emp
> where empno IN (
> select deptno from dept
> where emp.job = dept.name)
> {code}
> Filter condition for correlated predicate e.g. {{=($cor0.JOB, $1)}} is
> transformed into equality predicate on same column e.g. {{$1=$1}} after
> calling {{decorrelateExpr}} (if value generator is not generated). This is
> further simplified into {{$1 is not null}}.
> We do not need to generate and simplify such predicate expression, instead it
> could be completely removed. This will help support cases other than equality
> correlated predicates for which value generated is not required.
--
This message was sent by Atlassian JIRA
(v6.4.14#64029)
