[jira] [Assigned] (CALCITE-4292) Remove exist condition from Not equals criteria

[Bill Neil (Jira)]

 [ 
https://issues.apache.org/jira/browse/CALCITE-4292?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

Bill Neil reassigned CALCITE-4292:
--

Assignee: Bill Neil

> Remove exist condition from Not equals criteria
> ---
>
> Key: CALCITE-4292
> URL: https://issues.apache.org/jira/browse/CALCITE-4292
> Project: Calcite
>  Issue Type: Bug
>  Components: elasticsearch-adapter
>Reporter: Shlok Srivastava
>Assignee: Bill Neil
>Priority: Major
>  Labels: ElasticSearch, NotEquals, QueryBuilder, calcite
>
> Remove the exists condition from Not equals Query Expression. Elasticsearch 
> doesn't put this condition therefore keeping queries in sync. 
> [Code|https://github.com/apache/calcite/blob/1050b36cafbb0c487b7a2ade3efd12850609717e/elasticsearch/src/main/java/org/apache/calcite/adapter/elasticsearch/PredicateAnalyzer.java#L782]



--
This message was sent by Atlassian Jira
(v8.3.4#803005)

[jira] [Resolved] (CALCITE-4333) The Sort rel should be decorrelated even though it has fetch or limit when its parent is not a Correlate

[Danny Chen (Jira)]

 [ 
https://issues.apache.org/jira/browse/CALCITE-4333?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

Danny Chen resolved CALCITE-4333.
-
Resolution: Fixed

Fixed in 
[74785aa|https://github.com/apache/calcite/commit/74785aae40cdb48b02e4b8ef885e897c74bf629f]
 ~

> The Sort rel should be decorrelated even though it has fetch or limit when 
> its parent is not a Correlate
> 
>
> Key: CALCITE-4333
> URL: https://issues.apache.org/jira/browse/CALCITE-4333
> Project: Calcite
>  Issue Type: Bug
>  Components: core
>Affects Versions: 1.26.0
>Reporter: Danny Chen
>Assignee: Danny Chen
>Priority: Major
>  Labels: pull-request-available
> Fix For: 1.27.0
>
>  Time Spent: 1h 10m
>  Remaining Estimate: 0h
>
> Check this test in SqlToRelConverterTest:
> {code:java}
> @Test void testSortLimitWithCorrelateInput() {
> final String sql = "" +
> "SELECT deptno, ename\n" +
> "FROM\n" +
> "(SELECT DISTINCT deptno FROM emp) t1,\n" +
> "  LATERAL (\n" +
> "SELECT ename, sal\n" +
> "FROM emp\n" +
> "WHERE deptno = t1.deptno)\n" +
> "ORDER BY ename DESC\n" +
> "LIMIT 3";
> sql(sql).ok();
>   }
> {code}
> The current plan is:
> {code:xml}
> LogicalSort(sort0=[$1], dir0=[DESC], fetch=[3])
>   LogicalProject(DEPTNO=[$0], ENAME=[$1])
> LogicalCorrelate(correlation=[$cor0], joinType=[inner], 
> requiredColumns=[{0}])
>   LogicalAggregate(group=[{0}])
> LogicalProject(DEPTNO=[$7])
>   LogicalTableScan(table=[[CATALOG, SALES, EMP]])
>   LogicalProject(ENAME=[$1], SAL=[$5])
> LogicalFilter(condition=[=($7, $cor0.DEPTNO)])
>   LogicalTableScan(table=[[CATALOG, SALES, EMP]])
> {code}
> It actually can be decorrelated to:
> {code:xml}
> LogicalSort(sort0=[$1], dir0=[DESC], fetch=[3])
>   LogicalProject(DEPTNO=[$0], ENAME=[$1])
> LogicalJoin(condition=[=($0, $3)], joinType=[inner])
>   LogicalAggregate(group=[{0}])
> LogicalProject(DEPTNO=[$7])
>   LogicalTableScan(table=[[CATALOG, SALES, EMP]])
>   LogicalProject(ENAME=[$1], SAL=[$5], DEPTNO=[$7])
> LogicalTableScan(table=[[CATALOG, SALES, EMP]])
> {code}



--
This message was sent by Atlassian Jira
(v8.3.4#803005)

[jira] [Updated] (CALCITE-4333) The Sort rel should be decorrelated even though it has fetch or limit when its parent is not a Correlate

[Danny Chen (Jira)]

 [ 
https://issues.apache.org/jira/browse/CALCITE-4333?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

Danny Chen updated CALCITE-4333:

Description: 
Check this test in SqlToRelConverterTest:
{code:java}
@Test void testSortLimitWithCorrelateInput() {
final String sql = "" +
"SELECT deptno, ename\n" +
"FROM\n" +
"(SELECT DISTINCT deptno FROM emp) t1,\n" +
"  LATERAL (\n" +
"SELECT ename, sal\n" +
"FROM emp\n" +
"WHERE deptno = t1.deptno)\n" +
"ORDER BY ename DESC\n" +
"LIMIT 3";
sql(sql).ok();
  }
{code}

The current plan is:

{code:xml}
LogicalSort(sort0=[$1], dir0=[DESC], fetch=[3])
  LogicalProject(DEPTNO=[$0], ENAME=[$1])
LogicalCorrelate(correlation=[$cor0], joinType=[inner], 
requiredColumns=[{0}])
  LogicalAggregate(group=[{0}])
LogicalProject(DEPTNO=[$7])
  LogicalTableScan(table=[[CATALOG, SALES, EMP]])
  LogicalProject(ENAME=[$1], SAL=[$5])
LogicalFilter(condition=[=($7, $cor0.DEPTNO)])
  LogicalTableScan(table=[[CATALOG, SALES, EMP]])
{code}

It actually can be decorrelated to:
{code:xml}
LogicalSort(sort0=[$1], dir0=[DESC], fetch=[3])
  LogicalProject(DEPTNO=[$0], ENAME=[$1])
LogicalJoin(condition=[=($0, $3)], joinType=[inner])
  LogicalAggregate(group=[{0}])
LogicalProject(DEPTNO=[$7])
  LogicalTableScan(table=[[CATALOG, SALES, EMP]])
  LogicalProject(ENAME=[$1], SAL=[$5], DEPTNO=[$7])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
{code}


  was:
Check this test in SqlToRelConverterTest:
{code:java}
@Test void testSortLimitWithCorrelateInput() {
final String sql = "" +
"SELECT deptno, ename\n" +
"FROM\n" +
"(SELECT DISTINCT deptno FROM emp) t1,\n" +
"  LATERAL (\n" +
"SELECT ename, sal\n" +
"FROM emp\n" +
"WHERE deptno = t1.deptno)\n" +
"ORDER BY ename DESC\n" +
"LIMIT 3";
sql(sql).ok();
  }
{code}



> The Sort rel should be decorrelated even though it has fetch or limit when 
> its parent is not a Correlate
> 
>
> Key: CALCITE-4333
> URL: https://issues.apache.org/jira/browse/CALCITE-4333
> Project: Calcite
>  Issue Type: Bug
>  Components: core
>Affects Versions: 1.26.0
>Reporter: Danny Chen
>Assignee: Danny Chen
>Priority: Major
>  Labels: pull-request-available
> Fix For: 1.27.0
>
>  Time Spent: 1h
>  Remaining Estimate: 0h
>
> Check this test in SqlToRelConverterTest:
> {code:java}
> @Test void testSortLimitWithCorrelateInput() {
> final String sql = "" +
> "SELECT deptno, ename\n" +
> "FROM\n" +
> "(SELECT DISTINCT deptno FROM emp) t1,\n" +
> "  LATERAL (\n" +
> "SELECT ename, sal\n" +
> "FROM emp\n" +
> "WHERE deptno = t1.deptno)\n" +
> "ORDER BY ename DESC\n" +
> "LIMIT 3";
> sql(sql).ok();
>   }
> {code}
> The current plan is:
> {code:xml}
> LogicalSort(sort0=[$1], dir0=[DESC], fetch=[3])
>   LogicalProject(DEPTNO=[$0], ENAME=[$1])
> LogicalCorrelate(correlation=[$cor0], joinType=[inner], 
> requiredColumns=[{0}])
>   LogicalAggregate(group=[{0}])
> LogicalProject(DEPTNO=[$7])
>   LogicalTableScan(table=[[CATALOG, SALES, EMP]])
>   LogicalProject(ENAME=[$1], SAL=[$5])
> LogicalFilter(condition=[=($7, $cor0.DEPTNO)])
>   LogicalTableScan(table=[[CATALOG, SALES, EMP]])
> {code}
> It actually can be decorrelated to:
> {code:xml}
> LogicalSort(sort0=[$1], dir0=[DESC], fetch=[3])
>   LogicalProject(DEPTNO=[$0], ENAME=[$1])
> LogicalJoin(condition=[=($0, $3)], joinType=[inner])
>   LogicalAggregate(group=[{0}])
> LogicalProject(DEPTNO=[$7])
>   LogicalTableScan(table=[[CATALOG, SALES, EMP]])
>   LogicalProject(ENAME=[$1], SAL=[$5], DEPTNO=[$7])
> LogicalTableScan(table=[[CATALOG, SALES, EMP]])
> {code}



--
This message was sent by Atlassian Jira
(v8.3.4#803005)

[jira] [Commented] (CALCITE-4333) The Sort rel should be decorrelated even though it has fetch or limit when its parent is not a Correlate

[Danny Chen (Jira)]

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

Danny Chen commented on CALCITE-4333:
-

Thanks, description updated ~

> The Sort rel should be decorrelated even though it has fetch or limit when 
> its parent is not a Correlate
> 
>
> Key: CALCITE-4333
> URL: https://issues.apache.org/jira/browse/CALCITE-4333
> Project: Calcite
>  Issue Type: Bug
>  Components: core
>Affects Versions: 1.26.0
>Reporter: Danny Chen
>Assignee: Danny Chen
>Priority: Major
>  Labels: pull-request-available
> Fix For: 1.27.0
>
>  Time Spent: 1h
>  Remaining Estimate: 0h
>
> Check this test in SqlToRelConverterTest:
> {code:java}
> @Test void testSortLimitWithCorrelateInput() {
> final String sql = "" +
> "SELECT deptno, ename\n" +
> "FROM\n" +
> "(SELECT DISTINCT deptno FROM emp) t1,\n" +
> "  LATERAL (\n" +
> "SELECT ename, sal\n" +
> "FROM emp\n" +
> "WHERE deptno = t1.deptno)\n" +
> "ORDER BY ename DESC\n" +
> "LIMIT 3";
> sql(sql).ok();
>   }
> {code}
> The current plan is:
> {code:xml}
> LogicalSort(sort0=[$1], dir0=[DESC], fetch=[3])
>   LogicalProject(DEPTNO=[$0], ENAME=[$1])
> LogicalCorrelate(correlation=[$cor0], joinType=[inner], 
> requiredColumns=[{0}])
>   LogicalAggregate(group=[{0}])
> LogicalProject(DEPTNO=[$7])
>   LogicalTableScan(table=[[CATALOG, SALES, EMP]])
>   LogicalProject(ENAME=[$1], SAL=[$5])
> LogicalFilter(condition=[=($7, $cor0.DEPTNO)])
>   LogicalTableScan(table=[[CATALOG, SALES, EMP]])
> {code}
> It actually can be decorrelated to:
> {code:xml}
> LogicalSort(sort0=[$1], dir0=[DESC], fetch=[3])
>   LogicalProject(DEPTNO=[$0], ENAME=[$1])
> LogicalJoin(condition=[=($0, $3)], joinType=[inner])
>   LogicalAggregate(group=[{0}])
> LogicalProject(DEPTNO=[$7])
>   LogicalTableScan(table=[[CATALOG, SALES, EMP]])
>   LogicalProject(ENAME=[$1], SAL=[$5], DEPTNO=[$7])
> LogicalTableScan(table=[[CATALOG, SALES, EMP]])
> {code}



--
This message was sent by Atlassian Jira
(v8.3.4#803005)

[jira] [Commented] (CALCITE-3780) SESSION Table-valued Function

[Jark Wu (Jira)]

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

Jark Wu commented on CALCITE-3780:
--

This can also help the optimizer know how to add an {{Exchange}} RelNode. 

> SESSION Table-valued Function
> -
>
> Key: CALCITE-3780
> URL: https://issues.apache.org/jira/browse/CALCITE-3780
> Project: Calcite
>  Issue Type: Sub-task
>Reporter: Rui Wang
>Assignee: Rui Wang
>Priority: Major
> Fix For: 1.23.0
>
>
> We can create SESSION table-valued function to replace GROUP BY SESSION for 
> inactive gap session functionality:
> {code:sql}
> SELECT *
> FROM TABLE SESSION (
>   data => TABLE Bid ,
>   timecol => DESCRIPTOR ( bidtime ) ,
>   keycol => DESCRIPTOR(key),
>   inactive_gap => INTERVAL '10' MINUTES )
> {code}
>  
>  



--
This message was sent by Atlassian Jira
(v8.3.4#803005)

[jira] [Resolved] (CALCITE-4297) Babel should accept CURRENT_DATE and CURRENT_DATETIME, with and without parentheses

[Danny Chen (Jira)]

 [ 
https://issues.apache.org/jira/browse/CALCITE-4297?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

Danny Chen resolved CALCITE-4297.
-
Fix Version/s: 1.27.0
 Assignee: Danny Chen
   Resolution: Fixed

Fixed in 
[ac96eb8|https://github.com/apache/calcite/commit/ac96ebc5009e781a31503288adf292000b80],
 thanks for the PR, [~jswett] ~

> Babel should accept CURRENT_DATE and CURRENT_DATETIME, with and without 
> parentheses
> ---
>
> Key: CALCITE-4297
> URL: https://issues.apache.org/jira/browse/CALCITE-4297
> Project: Calcite
>  Issue Type: Bug
>  Components: babel
>Affects Versions: 1.25.0
>Reporter: Justin Swett
>Assignee: Danny Chen
>Priority: Major
>  Labels: pull-request-available
> Fix For: 1.27.0
>
>  Time Spent: 10m
>  Remaining Estimate: 0h
>
> The following BigQuery SQL contains CURRENT_DATE and CURRENT_DATETIME without 
> parentheses. Babel parser should accept it. (It should also accept 
> CURRENT_TIME and CURRENT_TIMESTAMP with parentheses.)
>  
> The following SQL should trigger *CalciteContextException: No match found for 
> function signature CURRENT_TIMESTAMP()* for the date functions, or if you add 
> parens to the current_time.
>  
> {code:java}
> WITH THE_TIMES_ARE_STATIC AS
> (SELECT
> CURRENT_DATE as a_date,
> CURRENT_DATETIME() as a_datetime,
> CURRENT_TIME as a_time,
> CURRENT_TIMESTAMP() as a_timestamp
> )
> SELECT * FROM THE_TIMES_ARE_STATIC 
> {code}



--
This message was sent by Atlassian Jira
(v8.3.4#803005)

[jira] [Commented] (CALCITE-3780) SESSION Table-valued Function

[Jark Wu (Jira)]

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

Jark Wu commented on CALCITE-3780:
--

Hi [~amaliujia], I just think the PARTITION BY clause is more SQL standard 
compliant. Because the input table of SESSION window PTF is SET semantic and 
data needs to be partitioned, PARTITION BY clause is introduced in SQL standard 
on this purpose. 

> SESSION Table-valued Function
> -
>
> Key: CALCITE-3780
> URL: https://issues.apache.org/jira/browse/CALCITE-3780
> Project: Calcite
>  Issue Type: Sub-task
>Reporter: Rui Wang
>Assignee: Rui Wang
>Priority: Major
> Fix For: 1.23.0
>
>
> We can create SESSION table-valued function to replace GROUP BY SESSION for 
> inactive gap session functionality:
> {code:sql}
> SELECT *
> FROM TABLE SESSION (
>   data => TABLE Bid ,
>   timecol => DESCRIPTOR ( bidtime ) ,
>   keycol => DESCRIPTOR(key),
>   inactive_gap => INTERVAL '10' MINUTES )
> {code}
>  
>  



--
This message was sent by Atlassian Jira
(v8.3.4#803005)

[jira] [Updated] (CALCITE-4297) Babel should accept CURRENT_DATE and CURRENT_DATETIME, with and without parentheses

[ASF GitHub Bot (Jira)]

 [ 
https://issues.apache.org/jira/browse/CALCITE-4297?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

ASF GitHub Bot updated CALCITE-4297:

Labels: pull-request-available  (was: )

> Babel should accept CURRENT_DATE and CURRENT_DATETIME, with and without 
> parentheses
> ---
>
> Key: CALCITE-4297
> URL: https://issues.apache.org/jira/browse/CALCITE-4297
> Project: Calcite
>  Issue Type: Bug
>  Components: babel
>Affects Versions: 1.25.0
>Reporter: Justin Swett
>Priority: Major
>  Labels: pull-request-available
>  Time Spent: 10m
>  Remaining Estimate: 0h
>
> The following BigQuery SQL contains CURRENT_DATE and CURRENT_DATETIME without 
> parentheses. Babel parser should accept it. (It should also accept 
> CURRENT_TIME and CURRENT_TIMESTAMP with parentheses.)
>  
> The following SQL should trigger *CalciteContextException: No match found for 
> function signature CURRENT_TIMESTAMP()* for the date functions, or if you add 
> parens to the current_time.
>  
> {code:java}
> WITH THE_TIMES_ARE_STATIC AS
> (SELECT
> CURRENT_DATE as a_date,
> CURRENT_DATETIME() as a_datetime,
> CURRENT_TIME as a_time,
> CURRENT_TIMESTAMP() as a_timestamp
> )
> SELECT * FROM THE_TIMES_ARE_STATIC 
> {code}



--
This message was sent by Atlassian Jira
(v8.3.4#803005)

[jira] [Resolved] (CALCITE-4302) Improve cost propagation in volcano to avoid re-propagation

[Danny Chen (Jira)]

 [ 
https://issues.apache.org/jira/browse/CALCITE-4302?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

Danny Chen resolved CALCITE-4302.
-
Fix Version/s: 1.27.0
 Assignee: Danny Chen
   Resolution: Fixed

Fixed in 
[c7fdae2|https://github.com/apache/calcite/commit/c7fdae22fb0e6b220152f05a7343ce5569283a83],
 thanks for your PR, [~botong] ~

> Improve cost propagation in volcano to avoid re-propagation
> ---
>
> Key: CALCITE-4302
> URL: https://issues.apache.org/jira/browse/CALCITE-4302
> Project: Calcite
>  Issue Type: Improvement
>Reporter: Botong Huang
>Assignee: Danny Chen
>Priority: Major
>  Labels: pull-request-available
> Fix For: 1.27.0
>
>  Time Spent: 5h 40m
>  Remaining Estimate: 0h
>
> CALCITE-3330 changed the cost propagation in volcano from DFS to BFS. 
> However, there is still room for improvement. A subset can be updated more 
> than once in a cost propagation process. For instance, A -> D, A -> B -> C -> 
> D. When subset A has an update, using BFS subset D (and thus all subsets 
> above/after D) can be updated twice, first via A -> D and then C -> D. We can 
> further improve the BFS by always popping the relNode with the smallest cost 
> from the queue, similar to the Dijkstra algorithm. So that whenever a relNode 
> is popped from the queue, its current best cannot be further deceased any 
> more. As a result, all subsets will only be propagated at most once. 



--
This message was sent by Atlassian Jira
(v8.3.4#803005)

[jira] [Commented] (CALCITE-3780) SESSION Table-valued Function

[Rui Wang (Jira)]

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

Rui Wang commented on CALCITE-3780:
---

[~jark] I don't have a plan to support PARTITION BY syntax because I think that 
syntax is supported by the DESCRIPTOR(key) parameter for SESSION window. Do you 
have a case that PARTITION BY syntax can support but DESCRIPTOR(key) cannot?

> SESSION Table-valued Function
> -
>
> Key: CALCITE-3780
> URL: https://issues.apache.org/jira/browse/CALCITE-3780
> Project: Calcite
>  Issue Type: Sub-task
>Reporter: Rui Wang
>Assignee: Rui Wang
>Priority: Major
> Fix For: 1.23.0
>
>
> We can create SESSION table-valued function to replace GROUP BY SESSION for 
> inactive gap session functionality:
> {code:sql}
> SELECT *
> FROM TABLE SESSION (
>   data => TABLE Bid ,
>   timecol => DESCRIPTOR ( bidtime ) ,
>   keycol => DESCRIPTOR(key),
>   inactive_gap => INTERVAL '10' MINUTES )
> {code}
>  
>  



--
This message was sent by Atlassian Jira
(v8.3.4#803005)

[jira] [Commented] (CALCITE-3780) SESSION Table-valued Function

[Jark Wu (Jira)]

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

Jark Wu commented on CALCITE-3780:
--

Hi all, what's the plan to support PARTITION BY syntax for SESSION window? Do 
we have an issue to track this?

> SESSION Table-valued Function
> -
>
> Key: CALCITE-3780
> URL: https://issues.apache.org/jira/browse/CALCITE-3780
> Project: Calcite
>  Issue Type: Sub-task
>Reporter: Rui Wang
>Assignee: Rui Wang
>Priority: Major
> Fix For: 1.23.0
>
>
> We can create SESSION table-valued function to replace GROUP BY SESSION for 
> inactive gap session functionality:
> {code:sql}
> SELECT *
> FROM TABLE SESSION (
>   data => TABLE Bid ,
>   timecol => DESCRIPTOR ( bidtime ) ,
>   keycol => DESCRIPTOR(key),
>   inactive_gap => INTERVAL '10' MINUTES )
> {code}
>  
>  



--
This message was sent by Atlassian Jira
(v8.3.4#803005)

[jira] [Commented] (CALCITE-4332) Improve error when planning rule produces a relational expression with wrong row type

[Chunwei Lei (Jira)]

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

Chunwei Lei commented on CALCITE-4332:
--

[~vladimirsitnikov] if you don'mind, I would like to take over this issue.

> Improve error when planning rule produces a relational expression with wrong 
> row type
> -
>
> Key: CALCITE-4332
> URL: https://issues.apache.org/jira/browse/CALCITE-4332
> Project: Calcite
>  Issue Type: Improvement
>  Components: core
>Affects Versions: 1.26.0
>Reporter: Vladimir Sitnikov
>Priority: Major
>
> Current code in {{VolcanoPlanner}}:
> {code:java}
>   assert RelOptUtil.equal(
>   "rel rowtype",
>   rel.getRowType(),
>   "equivRel rowtype",
>   equivRel.getRowType(),
>   Litmus.THROW);
>   equivRel = ensureRegistered(equivRel, null);
> {code}
> The suggested changes:
> 1) Use regular error {{IllegalArgumentException}} rather than an assertion
> 2) Make sure the exception message contains fine-grained information on what 
> went wrong. For instance: {{name: non nullable -> nullable; description: 
> character -> int; ...}}



--
This message was sent by Atlassian Jira
(v8.3.4#803005)

[jira] [Commented] (CALCITE-2935) Add BOOL_OR, BOOL_AND aggregate functions

[Julian Hyde (Jira)]

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

Julian Hyde commented on CALCITE-2935:
--

Assigning to myself to review the PR. Also to add LOGICAL_AND, LOGICAL_OR as 
synonyms in the BigQuery dialect.

> Add BOOL_OR, BOOL_AND aggregate functions
> -
>
> Key: CALCITE-2935
> URL: https://issues.apache.org/jira/browse/CALCITE-2935
> Project: Calcite
>  Issue Type: New Feature
>  Components: core
>Reporter: Haisheng Yuan
>Assignee: Julian Hyde
>Priority: Major
>  Labels: pull-request-available
>  Time Spent: 3h 20m
>  Remaining Estimate: 0h
>
> Support ANY, SOME, EVERY (also known as BOOL_OR, BOOL_AND) aggregate 
> functions.
> ANY, SOME is equivalent with bool_or. EVERY is equivalent with bool_and. 
> Parser needs to be changed to support these aggregate functions.
> [https://blog.jooq.org/2014/12/18/a-true-sql-gem-you-didnt-know-yet-the-every-aggregate-function/]
>  
> [https://mysqlserverteam.com/using-the-aggregate-functions-any-some-every-with-mysql/]
> Note that if ANY or SOME aggregate function is placed on the right side of 
> comparison operation and argument of this function is a subquery additional 
> parentheses around aggregate function are required, otherwise it will be 
> parsed as quantified comparison predicate.
> Example:
> ANY(NAME LIKE 'W%')
>  A = (ANY((SELECT B FROM T)))



--
This message was sent by Atlassian Jira
(v8.3.4#803005)

[jira] [Assigned] (CALCITE-2935) Add BOOL_OR, BOOL_AND aggregate functions

[Julian Hyde (Jira)]

 [ 
https://issues.apache.org/jira/browse/CALCITE-2935?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

Julian Hyde reassigned CALCITE-2935:


Assignee: Julian Hyde  (was: Danny Chen)

> Add BOOL_OR, BOOL_AND aggregate functions
> -
>
> Key: CALCITE-2935
> URL: https://issues.apache.org/jira/browse/CALCITE-2935
> Project: Calcite
>  Issue Type: New Feature
>  Components: core
>Reporter: Haisheng Yuan
>Assignee: Julian Hyde
>Priority: Major
>  Labels: pull-request-available
>  Time Spent: 3h 20m
>  Remaining Estimate: 0h
>
> Support ANY, SOME, EVERY (also known as BOOL_OR, BOOL_AND) aggregate 
> functions.
> ANY, SOME is equivalent with bool_or. EVERY is equivalent with bool_and. 
> Parser needs to be changed to support these aggregate functions.
> [https://blog.jooq.org/2014/12/18/a-true-sql-gem-you-didnt-know-yet-the-every-aggregate-function/]
>  
> [https://mysqlserverteam.com/using-the-aggregate-functions-any-some-every-with-mysql/]
> Note that if ANY or SOME aggregate function is placed on the right side of 
> comparison operation and argument of this function is a subquery additional 
> parentheses around aggregate function are required, otherwise it will be 
> parsed as quantified comparison predicate.
> Example:
> ANY(NAME LIKE 'W%')
>  A = (ANY((SELECT B FROM T)))



--
This message was sent by Atlassian Jira
(v8.3.4#803005)

[jira] [Comment Edited] (CALCITE-2935) Add BOOL_OR, BOOL_AND aggregate functions

[Julian Hyde (Jira)]

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

Julian Hyde edited comment on CALCITE-2935 at 10/13/20, 11:02 PM:
--

+Note that BOOL_OR and BOOL_AND will essentially be aliases for MAX and MIN.+ 

Yep. But BOOL_OR/BOOL_AND only support a `boolean` type input which it's 
different with MIN/MAX.

There are duplicated issues: CALCITE-3616, CALCITE-3617.


was (Author: lishuming):
+Note that BOOL_OR and BOOL_AND will essentially be aliases for MAX and MIN.+ 

Yep. But BOOL_OR/BOOL_AND only support a `boolean` type input which it's 
different with MIN/MAX.

There are duplicated issues:

https://issues.apache.org/jira/browse/CALCITE-3616

https://issues.apache.org/jira/browse/CALCITE-3617

> Add BOOL_OR, BOOL_AND aggregate functions
> -
>
> Key: CALCITE-2935
> URL: https://issues.apache.org/jira/browse/CALCITE-2935
> Project: Calcite
>  Issue Type: New Feature
>  Components: core
>Reporter: Haisheng Yuan
>Assignee: Danny Chen
>Priority: Major
>  Labels: pull-request-available
>  Time Spent: 3h 20m
>  Remaining Estimate: 0h
>
> Support ANY, SOME, EVERY (also known as BOOL_OR, BOOL_AND) aggregate 
> functions.
> ANY, SOME is equivalent with bool_or. EVERY is equivalent with bool_and. 
> Parser needs to be changed to support these aggregate functions.
> [https://blog.jooq.org/2014/12/18/a-true-sql-gem-you-didnt-know-yet-the-every-aggregate-function/]
>  
> [https://mysqlserverteam.com/using-the-aggregate-functions-any-some-every-with-mysql/]
> Note that if ANY or SOME aggregate function is placed on the right side of 
> comparison operation and argument of this function is a subquery additional 
> parentheses around aggregate function are required, otherwise it will be 
> parsed as quantified comparison predicate.
> Example:
> ANY(NAME LIKE 'W%')
>  A = (ANY((SELECT B FROM T)))



--
This message was sent by Atlassian Jira
(v8.3.4#803005)

[jira] [Updated] (CALCITE-2935) Support ANY, SOME, EVERY (also known as BOOL_OR, BOOL_AND) aggregate functions

[Julian Hyde (Jira)]

 [ 
https://issues.apache.org/jira/browse/CALCITE-2935?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

Julian Hyde updated CALCITE-2935:
-
Description: 
Support ANY, SOME, EVERY (also known as BOOL_OR, BOOL_AND) aggregate functions.

ANY, SOME is equivalent with bool_or. EVERY is equivalent with bool_and. Parser 
needs to be changed to support these aggregate functions.

[https://blog.jooq.org/2014/12/18/a-true-sql-gem-you-didnt-know-yet-the-every-aggregate-function/]
 
[https://mysqlserverteam.com/using-the-aggregate-functions-any-some-every-with-mysql/]

Note that if ANY or SOME aggregate function is placed on the right side of 
comparison operation and argument of this function is a subquery additional 
parentheses around aggregate function are required, otherwise it will be parsed 
as quantified comparison predicate.

Example:

ANY(NAME LIKE 'W%')
 A = (ANY((SELECT B FROM T)))

  was:
ANY, SOME is equivalent with bool_or. EVERY is equivalent with bool_and. Parser 
needs to be changed to support these aggregate functions.

https://blog.jooq.org/2014/12/18/a-true-sql-gem-you-didnt-know-yet-the-every-aggregate-function/
https://mysqlserverteam.com/using-the-aggregate-functions-any-some-every-with-mysql/

Note that if ANY or SOME aggregate function is placed on the right side of 
comparison operation and argument of this function is a subquery additional 
parentheses around aggregate function are required, otherwise it will be parsed 
as quantified comparison predicate.

Example:

ANY(NAME LIKE 'W%')
A = (ANY((SELECT B FROM T)))



> Support  ANY, SOME, EVERY (also known as BOOL_OR, BOOL_AND) aggregate 
> functions
> ---
>
> Key: CALCITE-2935
> URL: https://issues.apache.org/jira/browse/CALCITE-2935
> Project: Calcite
>  Issue Type: New Feature
>  Components: core
>Reporter: Haisheng Yuan
>Assignee: Danny Chen
>Priority: Major
>  Labels: pull-request-available
>  Time Spent: 3h 20m
>  Remaining Estimate: 0h
>
> Support ANY, SOME, EVERY (also known as BOOL_OR, BOOL_AND) aggregate 
> functions.
> ANY, SOME is equivalent with bool_or. EVERY is equivalent with bool_and. 
> Parser needs to be changed to support these aggregate functions.
> [https://blog.jooq.org/2014/12/18/a-true-sql-gem-you-didnt-know-yet-the-every-aggregate-function/]
>  
> [https://mysqlserverteam.com/using-the-aggregate-functions-any-some-every-with-mysql/]
> Note that if ANY or SOME aggregate function is placed on the right side of 
> comparison operation and argument of this function is a subquery additional 
> parentheses around aggregate function are required, otherwise it will be 
> parsed as quantified comparison predicate.
> Example:
> ANY(NAME LIKE 'W%')
>  A = (ANY((SELECT B FROM T)))



--
This message was sent by Atlassian Jira
(v8.3.4#803005)

[jira] [Updated] (CALCITE-2935) Add BOOL_OR, BOOL_AND aggregate functions

[Julian Hyde (Jira)]

 [ 
https://issues.apache.org/jira/browse/CALCITE-2935?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

Julian Hyde updated CALCITE-2935:
-
Summary: Add BOOL_OR, BOOL_AND aggregate functions  (was: Support  ANY, 
SOME, EVERY (also known as BOOL_OR, BOOL_AND) aggregate functions)

> Add BOOL_OR, BOOL_AND aggregate functions
> -
>
> Key: CALCITE-2935
> URL: https://issues.apache.org/jira/browse/CALCITE-2935
> Project: Calcite
>  Issue Type: New Feature
>  Components: core
>Reporter: Haisheng Yuan
>Assignee: Danny Chen
>Priority: Major
>  Labels: pull-request-available
>  Time Spent: 3h 20m
>  Remaining Estimate: 0h
>
> Support ANY, SOME, EVERY (also known as BOOL_OR, BOOL_AND) aggregate 
> functions.
> ANY, SOME is equivalent with bool_or. EVERY is equivalent with bool_and. 
> Parser needs to be changed to support these aggregate functions.
> [https://blog.jooq.org/2014/12/18/a-true-sql-gem-you-didnt-know-yet-the-every-aggregate-function/]
>  
> [https://mysqlserverteam.com/using-the-aggregate-functions-any-some-every-with-mysql/]
> Note that if ANY or SOME aggregate function is placed on the right side of 
> comparison operation and argument of this function is a subquery additional 
> parentheses around aggregate function are required, otherwise it will be 
> parsed as quantified comparison predicate.
> Example:
> ANY(NAME LIKE 'W%')
>  A = (ANY((SELECT B FROM T)))



--
This message was sent by Atlassian Jira
(v8.3.4#803005)

[jira] [Commented] (CALCITE-4323) View with ORDER BY throws AssertionError during view expansion

[Julian Hyde (Jira)]

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

Julian Hyde commented on CALCITE-4323:
--

I don't know. I don't have time right now to get my head into the code.

> View with ORDER BY throws AssertionError during view expansion
> --
>
> Key: CALCITE-4323
> URL: https://issues.apache.org/jira/browse/CALCITE-4323
> Project: Calcite
>  Issue Type: Bug
>Reporter: Julian Hyde
>Assignee: Jiatao Tao
>Priority: Major
> Attachments: image-2020-10-10-23-58-39-822.png, 
> image-2020-10-10-23-59-22-297.png
>
>
> If you have a view that has an ORDER BY clause, and try to execute a query on 
> that view, SqlToRelConverter throws AssertionError during view expansion. For 
> example,
> {noformat}
> create view v as select * from "EMPLOYEES" order by "deptno";
> select * from V;
> java.lang.AssertionError
>   at 
> org.apache.calcite.sql2rel.SqlToRelConverter.requiredCollation(SqlToRelConverter.java:634)
>   at 
> org.apache.calcite.sql2rel.SqlToRelConverter.requiredCollation(SqlToRelConverter.java:629)
>   at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertQuery(SqlToRelConverter.java:578)
>   at 
> org.apache.calcite.prepare.CalcitePrepareImpl$CalcitePreparingStmt.expandView(CalcitePrepareImpl.java:1072)
>   at 
> org.apache.calcite.plan.ViewExpanders$1.expandView(ViewExpanders.java:52)
>   at 
> org.apache.calcite.schema.impl.ViewTable.expandView(ViewTable.java:127)
>   at org.apache.calcite.schema.impl.ViewTable.toRel(ViewTable.java:120)
>   at 
> org.apache.calcite.prepare.RelOptTableImpl.toRel(RelOptTableImpl.java:285)
>   at 
> org.apache.calcite.sql2rel.SqlToRelConverter.toRel(SqlToRelConverter.java:3605)
>   at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertIdentifier(SqlToRelConverter.java:2522)
>   at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertFrom(SqlToRelConverter.java:2160)
>   at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertFrom(SqlToRelConverter.java:2109)
>   at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertFrom(SqlToRelConverter.java:2066)
>   at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertSelectImpl(SqlToRelConverter.java:662)
>   at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertSelect(SqlToRelConverter.java:643)
>   at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertQueryRecursive(SqlToRelConverter.java:3458)
>   at 
> org.apache.calcite.sql2rel.SqlToRelConverter.convertQuery(SqlToRelConverter.java:569)
>   at org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:242)
> {noformat}
> I suspected that it would be a problem for when the view does not project the 
> sort column(s), but it seems to be a problem even if the columns are 
> projected.



--
This message was sent by Atlassian Jira
(v8.3.4#803005)

[jira] [Commented] (CALCITE-4333) The Sort rel should be decorrelated even though it has fetch or limit when its parent is not a Correlate

[Julian Hyde (Jira)]

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

Julian Hyde commented on CALCITE-4333:
--

Can you please adjust your description and explain what is happening and what 
you think should happen?

> The Sort rel should be decorrelated even though it has fetch or limit when 
> its parent is not a Correlate
> 
>
> Key: CALCITE-4333
> URL: https://issues.apache.org/jira/browse/CALCITE-4333
> Project: Calcite
>  Issue Type: Bug
>  Components: core
>Affects Versions: 1.26.0
>Reporter: Danny Chen
>Assignee: Danny Chen
>Priority: Major
>  Labels: pull-request-available
> Fix For: 1.27.0
>
>  Time Spent: 1h
>  Remaining Estimate: 0h
>
> Check this test in SqlToRelConverterTest:
> {code:java}
> @Test void testSortLimitWithCorrelateInput() {
> final String sql = "" +
> "SELECT deptno, ename\n" +
> "FROM\n" +
> "(SELECT DISTINCT deptno FROM emp) t1,\n" +
> "  LATERAL (\n" +
> "SELECT ename, sal\n" +
> "FROM emp\n" +
> "WHERE deptno = t1.deptno)\n" +
> "ORDER BY ename DESC\n" +
> "LIMIT 3";
> sql(sql).ok();
>   }
> {code}



--
This message was sent by Atlassian Jira
(v8.3.4#803005)

[jira] [Updated] (CALCITE-4325) RexSimplify/Sarg incorrectly simplifies complex expressions with NULL

[ASF GitHub Bot (Jira)]

 [ 
https://issues.apache.org/jira/browse/CALCITE-4325?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

ASF GitHub Bot updated CALCITE-4325:

Labels: pull-request-available  (was: )

> RexSimplify/Sarg incorrectly simplifies complex expressions with NULL
> -
>
> Key: CALCITE-4325
> URL: https://issues.apache.org/jira/browse/CALCITE-4325
> Project: Calcite
>  Issue Type: Bug
>  Components: core
>Affects Versions: 1.26.0
> Environment: All environments
>Reporter: Igor Lozynskyi
>Assignee: Julian Hyde
>Priority: Major
>  Labels: pull-request-available
> Attachments: rex-simplify-issue-tests-2.patch
>
>  Time Spent: 10m
>  Remaining Estimate: 0h
>
> In Calcite 1.26, the Rex expressions like follows:
> {code:java}
> (deptno = 20 OR deptno IS NULL) AND deptno = 10{code}
> Are simplified to:
> {code:java}
> deptno IS NULL{code}
> Instead of:
> {code:java}
> FALSE{code}
> Similarly, the following expression:
> {code:java}
> (deptno <> 20 OR deptno IS NULL) AND deptno = 10{code}
> Is simplified to:
> {code:java}
> deptno = 10 OR deptno IS NULL{code}
> Instead of:
> {code:java}
> deptno = 10{code}
> A diff with related unit tests (for RelBuilderTest.java) is in the attachment.



--
This message was sent by Atlassian Jira
(v8.3.4#803005)

[jira] [Commented] (CALCITE-4321) JDBC adapter omits FILTER (WHERE ...) expressions when generating SQL

[Julian Hyde (Jira)]

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

Julian Hyde commented on CALCITE-4321:
--

I find that {{testJps}} fails when I use macOS's built-in JDK 
(\{{/usr/bin/java}}) rather than a real JDK. I think it's unavoidable.

I'd be curious to know what's happening with {{testShift}}; maybe something 
else JDK-specific.

> JDBC adapter omits FILTER (WHERE ...) expressions when generating SQL
> -
>
> Key: CALCITE-4321
> URL: https://issues.apache.org/jira/browse/CALCITE-4321
> Project: Calcite
>  Issue Type: Bug
>  Components: jdbc-adapter
> Environment: MacOS 10.15, Java 11, PostgreSQL 12
>Reporter: Jeremiah Rhoads Hall
>Assignee: Julian Hyde
>Priority: Major
> Fix For: 1.27.0
>
>
> I tried out the PIVOT feature added in CALCITE-3752 and found what I think is 
> a bug where the JDBC adapter will not generate `FILTER (WHERE ...)` 
> expressions from relational expressions.
> Initially, I noticed that the SQL that was generated when trying out the 
> pivot featue was missing the filter-where, but even when included directly in 
> the Calcite SQL it's not in the SQL issued by the JDBC adapter.
> For example if I try to execute the following with Calcite:
> {code}
> select sum(amount) filter (where some_date_column = '2020-10-01') from 
> my_table{code}
> The SQL issued by the adapter will be:
> {code}
> select sum(amount) from my_table
> {code}
> I have a small change here with a test case and a fix: 
> https://github.com/apache/calcite/pull/2204



--
This message was sent by Atlassian Jira
(v8.3.4#803005)

[jira] [Updated] (CALCITE-3504) Allow empty ARRAY[] constructors

[Julian Hyde (Jira)]

 [ 
https://issues.apache.org/jira/browse/CALCITE-3504?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

Julian Hyde updated CALCITE-3504:
-
Summary: Allow empty ARRAY[] constructors  (was: allow empty ARRAY[] 
literals)

> Allow empty ARRAY[] constructors
> 
>
> Key: CALCITE-3504
> URL: https://issues.apache.org/jira/browse/CALCITE-3504
> Project: Calcite
>  Issue Type: Wish
>Affects Versions: 1.21.0
>Reporter: Pressenna
>Priority: Major
>
> Currently an ARRAY expression requires at least one element.
> Please allow empty ARRAY expressions.
> {code:sql}
> SELECT ARRAY[] FROM foo;
> -- or more concise for updates:
> UPDATE food set array_column = ARRAY[];
> {code}



--
This message was sent by Atlassian Jira
(v8.3.4#803005)

[jira] [Commented] (CALCITE-3504) Allow empty ARRAY[] constructors

[Julian Hyde (Jira)]

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

Julian Hyde commented on CALCITE-3504:
--

I've re-opened.

> Allow empty ARRAY[] constructors
> 
>
> Key: CALCITE-3504
> URL: https://issues.apache.org/jira/browse/CALCITE-3504
> Project: Calcite
>  Issue Type: Wish
>Affects Versions: 1.21.0
>Reporter: Pressenna
>Priority: Major
>
> Currently an ARRAY expression requires at least one element.
> Please allow empty ARRAY expressions.
> {code:sql}
> SELECT ARRAY[] FROM foo;
> -- or more concise for updates:
> UPDATE food set array_column = ARRAY[];
> {code}



--
This message was sent by Atlassian Jira
(v8.3.4#803005)

[jira] [Reopened] (CALCITE-3504) allow empty ARRAY[] literals

[Julian Hyde (Jira)]

 [ 
https://issues.apache.org/jira/browse/CALCITE-3504?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

Julian Hyde reopened CALCITE-3504:
--

> allow empty ARRAY[] literals
> 
>
> Key: CALCITE-3504
> URL: https://issues.apache.org/jira/browse/CALCITE-3504
> Project: Calcite
>  Issue Type: Wish
>Affects Versions: 1.21.0
>Reporter: Pressenna
>Priority: Major
>
> Currently an ARRAY expression requires at least one element.
> Please allow empty ARRAY expressions.
> {code:sql}
> SELECT ARRAY[] FROM foo;
> -- or more concise for updates:
> UPDATE food set array_column = ARRAY[];
> {code}



--
This message was sent by Atlassian Jira
(v8.3.4#803005)

[jira] [Commented] (CALCITE-3504) allow empty ARRAY[] literals

[Walaa Eldin Moustafa (Jira)]

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

Walaa Eldin Moustafa commented on CALCITE-3504:
---

[~zabetak], [~julianhyde], can we re-open this ticket?

> allow empty ARRAY[] literals
> 
>
> Key: CALCITE-3504
> URL: https://issues.apache.org/jira/browse/CALCITE-3504
> Project: Calcite
>  Issue Type: Wish
>Affects Versions: 1.21.0
>Reporter: Pressenna
>Priority: Major
>
> Currently an ARRAY expression requires at least one element.
> Please allow empty ARRAY expressions.
> {code:sql}
> SELECT ARRAY[] FROM foo;
> -- or more concise for updates:
> UPDATE food set array_column = ARRAY[];
> {code}



--
This message was sent by Atlassian Jira
(v8.3.4#803005)

[jira] [Updated] (CALCITE-4317) Some rules fail to handle Aggregate node if RelFieldTrimmer trims all the fields

[ASF GitHub Bot (Jira)]

 [ 
https://issues.apache.org/jira/browse/CALCITE-4317?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

ASF GitHub Bot updated CALCITE-4317:

Labels: pull-request-available  (was: )

> Some rules fail to handle Aggregate node if RelFieldTrimmer trims all the 
> fields
> 
>
> Key: CALCITE-4317
> URL: https://issues.apache.org/jira/browse/CALCITE-4317
> Project: Calcite
>  Issue Type: Bug
>  Components: core
>Reporter: Rafay A
>Priority: Major
>  Labels: pull-request-available
>  Time Spent: 10m
>  Remaining Estimate: 0h
>
> Consider this query:
> {code:java}
> select o_orderkey from 
> (SELECT count(*) cnt_star, count(l_orderkey) cnt_ok FROM lineitem)
> cross join orders
> limit 10
> {code}
>  which generates this plan:
> {code:java}
> LogicalSort(fetch=[10])
>   LogicalProject(o_orderkey=[$2])
> LogicalJoin(condition=[true], joinType=[inner])
>   LogicalAggregate(group=[{}], cnt_star=[COUNT()], cnt_ok=[COUNT($0)])
> LogicalProject(l_orderkey=[$0])
>   ScanCrel(table=[lineitem], columns=[`l_orderkey`, `l_partkey`, 
> `l_suppkey`, `l_linenumber`, `l_quantity`, `l_extendedprice`, `l_discount`, 
> `l_tax`, `l_returnflag`, `l_linestatus`, `l_shipdate`, `l_commitdate`, 
> `l_receiptdate`, `l_shipinstruct`, `l_shipmode`, `l_comment`])
>   ScanCrel(table=[orders], columns=[`o_orderkey`, `o_custkey`, 
> `o_orderstatus`, `o_totalprice`, `o_orderdate`, `o_orderpriority`, `o_clerk`, 
> `o_shippriority`, `o_comment`])
> {code}
> and after we apply RelFieldTrimmer:
> {code:java}
> LogicalSort(fetch=[10])
>   LogicalProject(o_orderkey=[$0])
> LogicalJoin(condition=[true], joinType=[inner])
>   LogicalAggregate(group=[{}])
> LogicalProject(l_orderkey=[$0])
>   ScanCrel(table=[lineitem], columns=[`l_orderkey`])
>   ScanCrel(table=[orders], columns=[`o_orderkey`])
> {code}
>  
> We see that the LogicalAggregate has no groups/functions after applying the 
> trimmer. When we apply ProjectJoinTransposeRule or LoptOptimizeJoinRule after 
> trimming, we are seeing these exceptions:
> For LoptOptimizeJoinRule:
> {code:java}
>   (java.lang.ArrayIndexOutOfBoundsException) 0
> com.google.common.collect.RegularImmutableList.get():75
> org.apache.calcite.rel.metadata.RelMdColumnOrigins.getColumnOrigins():77
> sun.reflect.NativeMethodAccessorImpl.invoke0():-2
> sun.reflect.NativeMethodAccessorImpl.invoke():62
> sun.reflect.DelegatingMethodAccessorImpl.invoke():43
> java.lang.reflect.Method.invoke():498
> 
> org.apache.calcite.rel.metadata.ReflectiveRelMetadataProvider$1$1.invoke():178
> com.sun.proxy.$Proxy175.getColumnOrigins():-1
> sun.reflect.GeneratedMethodAccessor144.invoke():-1
> sun.reflect.DelegatingMethodAccessorImpl.invoke():43
> java.lang.reflect.Method.invoke():498
> 
> org.apache.calcite.rel.metadata.ChainedRelMetadataProvider$ChainedInvocationHandler.invoke():139
> com.sun.proxy.$Proxy175.getColumnOrigins():-1
> sun.reflect.GeneratedMethodAccessor144.invoke():-1
> sun.reflect.DelegatingMethodAccessorImpl.invoke():43
> java.lang.reflect.Method.invoke():498
> com.sun.proxy.$Proxy175.getColumnOrigins():-1
> sun.reflect.GeneratedMethodAccessor144.invoke():-1
> sun.reflect.DelegatingMethodAccessorImpl.invoke():43
> java.lang.reflect.Method.invoke():498
> 
> org.apache.calcite.rel.metadata.ChainedRelMetadataProvider$ChainedInvocationHandler.invoke():139
> com.sun.proxy.$Proxy175.getColumnOrigins():-1
> sun.reflect.GeneratedMethodAccessor144.invoke():-1
> sun.reflect.DelegatingMethodAccessorImpl.invoke():43
> java.lang.reflect.Method.invoke():498
> com.sun.proxy.$Proxy175.getColumnOrigins():-1
> sun.reflect.GeneratedMethodAccessor144.invoke():-1
> sun.reflect.DelegatingMethodAccessorImpl.invoke():43
> java.lang.reflect.Method.invoke():498
> 
> org.apache.calcite.rel.metadata.RelMetadataQuery$MetadataInvocationHandler.invoke():110
> com.sun.proxy.$Proxy175.getColumnOrigins():-1
> org.apache.calcite.rel.metadata.RelMetadataQuery.getColumnOrigins():293
> org.apache.calcite.rel.metadata.RelMetadataQuery.getTableOrigin():348
> org.apache.calcite.rel.rules.LoptOptimizeJoinRule.getSimpleFactors():377
> 
> org.apache.calcite.rel.rules.LoptOptimizeJoinRule.findRemovableSelfJoins():297
> org.apache.calcite.rel.rules.LoptOptimizeJoinRule.onMatch():126
> org.apache.calcite.plan.AbstractRelOptPlanner.fireRule():317
> org.apache.calcite.plan.hep.HepPlanner.applyRule():556
> org.apache.calcite.plan.hep.HepPlanner.applyRules():415
> org.apache.calcite.plan.hep.HepPlanner.executeInstruction():280
> org.apache.calcite.plan.hep.HepInstruction$RuleCollection.execute():74
> 

[jira] [Commented] (CALCITE-4317) Some rules fail to handle Aggregate node if RelFieldTrimmer trims all the fields

[Rafay A (Jira)]

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

Rafay A commented on CALCITE-4317:
--

I created a PR for my initial patch for this: 
[https://github.com/apache/calcite/pull/2211]

Is this something we are planning to do?

> Some rules fail to handle Aggregate node if RelFieldTrimmer trims all the 
> fields
> 
>
> Key: CALCITE-4317
> URL: https://issues.apache.org/jira/browse/CALCITE-4317
> Project: Calcite
>  Issue Type: Bug
>  Components: core
>Reporter: Rafay A
>Priority: Major
>  Labels: pull-request-available
>  Time Spent: 10m
>  Remaining Estimate: 0h
>
> Consider this query:
> {code:java}
> select o_orderkey from 
> (SELECT count(*) cnt_star, count(l_orderkey) cnt_ok FROM lineitem)
> cross join orders
> limit 10
> {code}
>  which generates this plan:
> {code:java}
> LogicalSort(fetch=[10])
>   LogicalProject(o_orderkey=[$2])
> LogicalJoin(condition=[true], joinType=[inner])
>   LogicalAggregate(group=[{}], cnt_star=[COUNT()], cnt_ok=[COUNT($0)])
> LogicalProject(l_orderkey=[$0])
>   ScanCrel(table=[lineitem], columns=[`l_orderkey`, `l_partkey`, 
> `l_suppkey`, `l_linenumber`, `l_quantity`, `l_extendedprice`, `l_discount`, 
> `l_tax`, `l_returnflag`, `l_linestatus`, `l_shipdate`, `l_commitdate`, 
> `l_receiptdate`, `l_shipinstruct`, `l_shipmode`, `l_comment`])
>   ScanCrel(table=[orders], columns=[`o_orderkey`, `o_custkey`, 
> `o_orderstatus`, `o_totalprice`, `o_orderdate`, `o_orderpriority`, `o_clerk`, 
> `o_shippriority`, `o_comment`])
> {code}
> and after we apply RelFieldTrimmer:
> {code:java}
> LogicalSort(fetch=[10])
>   LogicalProject(o_orderkey=[$0])
> LogicalJoin(condition=[true], joinType=[inner])
>   LogicalAggregate(group=[{}])
> LogicalProject(l_orderkey=[$0])
>   ScanCrel(table=[lineitem], columns=[`l_orderkey`])
>   ScanCrel(table=[orders], columns=[`o_orderkey`])
> {code}
>  
> We see that the LogicalAggregate has no groups/functions after applying the 
> trimmer. When we apply ProjectJoinTransposeRule or LoptOptimizeJoinRule after 
> trimming, we are seeing these exceptions:
> For LoptOptimizeJoinRule:
> {code:java}
>   (java.lang.ArrayIndexOutOfBoundsException) 0
> com.google.common.collect.RegularImmutableList.get():75
> org.apache.calcite.rel.metadata.RelMdColumnOrigins.getColumnOrigins():77
> sun.reflect.NativeMethodAccessorImpl.invoke0():-2
> sun.reflect.NativeMethodAccessorImpl.invoke():62
> sun.reflect.DelegatingMethodAccessorImpl.invoke():43
> java.lang.reflect.Method.invoke():498
> 
> org.apache.calcite.rel.metadata.ReflectiveRelMetadataProvider$1$1.invoke():178
> com.sun.proxy.$Proxy175.getColumnOrigins():-1
> sun.reflect.GeneratedMethodAccessor144.invoke():-1
> sun.reflect.DelegatingMethodAccessorImpl.invoke():43
> java.lang.reflect.Method.invoke():498
> 
> org.apache.calcite.rel.metadata.ChainedRelMetadataProvider$ChainedInvocationHandler.invoke():139
> com.sun.proxy.$Proxy175.getColumnOrigins():-1
> sun.reflect.GeneratedMethodAccessor144.invoke():-1
> sun.reflect.DelegatingMethodAccessorImpl.invoke():43
> java.lang.reflect.Method.invoke():498
> com.sun.proxy.$Proxy175.getColumnOrigins():-1
> sun.reflect.GeneratedMethodAccessor144.invoke():-1
> sun.reflect.DelegatingMethodAccessorImpl.invoke():43
> java.lang.reflect.Method.invoke():498
> 
> org.apache.calcite.rel.metadata.ChainedRelMetadataProvider$ChainedInvocationHandler.invoke():139
> com.sun.proxy.$Proxy175.getColumnOrigins():-1
> sun.reflect.GeneratedMethodAccessor144.invoke():-1
> sun.reflect.DelegatingMethodAccessorImpl.invoke():43
> java.lang.reflect.Method.invoke():498
> com.sun.proxy.$Proxy175.getColumnOrigins():-1
> sun.reflect.GeneratedMethodAccessor144.invoke():-1
> sun.reflect.DelegatingMethodAccessorImpl.invoke():43
> java.lang.reflect.Method.invoke():498
> 
> org.apache.calcite.rel.metadata.RelMetadataQuery$MetadataInvocationHandler.invoke():110
> com.sun.proxy.$Proxy175.getColumnOrigins():-1
> org.apache.calcite.rel.metadata.RelMetadataQuery.getColumnOrigins():293
> org.apache.calcite.rel.metadata.RelMetadataQuery.getTableOrigin():348
> org.apache.calcite.rel.rules.LoptOptimizeJoinRule.getSimpleFactors():377
> 
> org.apache.calcite.rel.rules.LoptOptimizeJoinRule.findRemovableSelfJoins():297
> org.apache.calcite.rel.rules.LoptOptimizeJoinRule.onMatch():126
> org.apache.calcite.plan.AbstractRelOptPlanner.fireRule():317
> org.apache.calcite.plan.hep.HepPlanner.applyRule():556
> org.apache.calcite.plan.hep.HepPlanner.applyRules():415
> 

[jira] [Commented] (CALCITE-4321) JDBC adapter omits FILTER (WHERE ...) expressions when generating SQL

[Jeremiah Rhoads Hall (Jira)]

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

Jeremiah Rhoads Hall commented on CALCITE-4321:
---

I've updated my branch with the dialect support method, the filter-where to 
case transform, and some test cases. I've noticed some test failures locally 
that seem unrelated: ImmutableBitSetTest's `testShift` and OsAdapterTest's 
`testJps`. I'll also check in on CI output on the PR. 

> JDBC adapter omits FILTER (WHERE ...) expressions when generating SQL
> -
>
> Key: CALCITE-4321
> URL: https://issues.apache.org/jira/browse/CALCITE-4321
> Project: Calcite
>  Issue Type: Bug
>  Components: jdbc-adapter
> Environment: MacOS 10.15, Java 11, PostgreSQL 12
>Reporter: Jeremiah Rhoads Hall
>Assignee: Julian Hyde
>Priority: Major
> Fix For: 1.27.0
>
>
> I tried out the PIVOT feature added in CALCITE-3752 and found what I think is 
> a bug where the JDBC adapter will not generate `FILTER (WHERE ...)` 
> expressions from relational expressions.
> Initially, I noticed that the SQL that was generated when trying out the 
> pivot featue was missing the filter-where, but even when included directly in 
> the Calcite SQL it's not in the SQL issued by the JDBC adapter.
> For example if I try to execute the following with Calcite:
> {code}
> select sum(amount) filter (where some_date_column = '2020-10-01') from 
> my_table{code}
> The SQL issued by the adapter will be:
> {code}
> select sum(amount) from my_table
> {code}
> I have a small change here with a test case and a fix: 
> https://github.com/apache/calcite/pull/2204



--
This message was sent by Atlassian Jira
(v8.3.4#803005)

[jira] [Commented] (CALCITE-4034) Implement a MySQL InnoDB adapter

[neoremind (Jira)]

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

neoremind commented on CALCITE-4034:


I do noticed the newly introduced Sargs, I am willing to step out to try to 
leverage Sargs. Please log a jira.

I just transferred to storage engine team of AnalyticDB, Alibaba and adapt the 
new environment, so a little busy recently, I will work on it if I have time. 
Many thanks for providing the opportunity to do more. :)

> Implement a MySQL InnoDB adapter
> 
>
> Key: CALCITE-4034
> URL: https://issues.apache.org/jira/browse/CALCITE-4034
> Project: Calcite
>  Issue Type: New Feature
>  Components: innodb-adapter
>Affects Versions: 1.23.0
>Reporter: neoremind
>Assignee: Julian Hyde
>Priority: Minor
>  Labels: pull-request-available
> Fix For: 1.27.0
>
>  Time Spent: 2h 50m
>  Remaining Estimate: 0h
>
> Calcite’s InnoDB adapter allows you to query the data based on InnoDB data 
> files directy, data files are also known as .ibd files, this adapter is 
> different from JDBC adapter which requires a MySQL server to serve response. 
> With .ibd files and the corresponding DDLs, InnoDB adapter is able to work 
> like a simple "MySQL server", it accepts SQL query and attempts to compile 
> the query based on InnoDB file accessing APIs provided by 
> [innodb-java-reader|[https://github.com/alibaba/innodb-java-reader]] (I am 
> the author of the library, which is well-tested and production-ready), it 
> will exploit projecting, filtering and sorting directly in InnoDB data file 
> where possible. What’s more, with DDLs, the adapter is "index aware", it 
> leverages rules to choose the right index to scan, for example, using primary 
> key or secondary keys to look up data, then it tries to push down some 
> conditions into storage engine. Also, the adapter leaves option to provide 
> hint as well, so that user can indicate the optimizer to force use one 
> specific index.
> The InnoDB adapter can,
> 1) Full scan a table.
> 2) Scan by index such as primary key or secondary key, single column or 
> composite keys.
> 3) Push down filtering condition to storage engine, support both point query 
> and range query.
> 4) Push down projection.
> 5) Push down ordering if it matches the natural collation of an index.
> 6) Support almost all the commonly used data types.
> 7) Work as a simple MySQL server being able to read .ibd data files directly 
> through file system, this can be a tool to query or dump table data by 
> offloading from MySQL process under some conditions.
> Before I describe the adapter and its design, I was wondering if it is 
> appropriate to deliver the work by contributing the codebase back to Calcite, 
> or if it would be better to stay in another project, probably being referred 
> from Calcite adapter link. Looking forward to Calcite developer's advice.
>  
> Below is the first version of the usage.
> --
> [MySQL]([https://www.mysql.com/]) is the most popular Open Source SQL 
> database management system, is developed, distributed, and supported by 
> Oracle Corporation. InnoDB is a general-purpose storage engine that balances 
> high reliability and high performance in MySQL, since 5.6 InnoDB has become 
> the default MySQL storage engine.
> Calcite’s InnoDB adapter allows you to query the data based on InnoDB data 
> files directy, data files are also known as .ibd files. It leverages 
> [innodb-java-reader]([https://github.com/alibaba/innodb-java-reader]). This 
> adapter is different from JDBC adapter which maps a schema in a JDBC data 
> source and requires a MySQL server to serve response. With .ibd files and the 
> corresponding DDLs, InnoDB adapter is able to work like a simple "MySQL 
> server", it accepts SQL query and attempts to compile the query based on 
> InnoDB file accessing APIs provided by innodb-java-reader, it will exploit 
> projecting, filtering and sorting directly in InnoDB data file where possible.
> What’s more, with DDLs, the adapter is "index aware", it leverages rules to 
> choose the right index to scan, for example, using primary key or secondary 
> keys to look up data, then it tries to push down some conditions into storage 
> engine. Also, the adapter leaves option to provide hint as well, so that user 
> can indicate the optimizer to force use one specific index.
> A basic example of a model file is given below, this schema reads from a 
> MySQL "scott" database:
> {code:java}
> {
>   "version": "1.0",
>   "defaultSchema": "scott",
>   "schemas": [
> {
>   "name": "scott",
>   "type": "custom",
>   "factory": "org.apache.calcite.adapter.innodb.InnodbSchemaFactory",
>   "operand": {
> "sqlFilePath": [ "/path/scott.sql" ],
>   

[jira] [Created] (CALCITE-4336) Add cancel-workflow-runs to automatically cancel GitHub stale workflows

[Vladimir Sitnikov (Jira)]

Vladimir Sitnikov created CALCITE-4336:
--

 Summary: Add cancel-workflow-runs to automatically cancel GitHub 
stale workflows
 Key: CALCITE-4336
 URL: https://issues.apache.org/jira/browse/CALCITE-4336
 Project: Calcite
  Issue Type: Improvement
Affects Versions: 1.26.0
Reporter: Vladimir Sitnikov


GitHub Actions CI jobs keep running even in case developer pushes one more 
commit to the PR branch, however, in most of the cases we do not need that.

We should cancel stale workflows to save resources and limit CI usage (there's 
overall concurrency limit for all ASF jobs in GitHub Actions CI)

See https://github.com/potiuk/cancel-workflow-runs




--
This message was sent by Atlassian Jira
(v8.3.4#803005)

[jira] [Commented] (CALCITE-4325) RexSimplify/Sarg incorrectly simplifies complex expressions with NULL

[Julian Hyde (Jira)]

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

Julian Hyde commented on CALCITE-4325:
--

I have a fix almost complete in 
[julianhyde/4325-sarg|https://github.com/julianhyde/calcite/tree/4325-sarg]. It 
fixes this issue and is a big improvement to how Sarg handles {{IS NULL}} and 
{{IS NOT NULL}} conditions. (They are now handled symmetrically, which is a 
good sign.) I should be able to merge soon.

> RexSimplify/Sarg incorrectly simplifies complex expressions with NULL
> -
>
> Key: CALCITE-4325
> URL: https://issues.apache.org/jira/browse/CALCITE-4325
> Project: Calcite
>  Issue Type: Bug
>  Components: core
>Affects Versions: 1.26.0
> Environment: All environments
>Reporter: Igor Lozynskyi
>Assignee: Julian Hyde
>Priority: Major
> Attachments: rex-simplify-issue-tests-2.patch
>
>
> In Calcite 1.26, the Rex expressions like follows:
> {code:java}
> (deptno = 20 OR deptno IS NULL) AND deptno = 10{code}
> Are simplified to:
> {code:java}
> deptno IS NULL{code}
> Instead of:
> {code:java}
> FALSE{code}
> Similarly, the following expression:
> {code:java}
> (deptno <> 20 OR deptno IS NULL) AND deptno = 10{code}
> Is simplified to:
> {code:java}
> deptno = 10 OR deptno IS NULL{code}
> Instead of:
> {code:java}
> deptno = 10{code}
> A diff with related unit tests (for RelBuilderTest.java) is in the attachment.



--
This message was sent by Atlassian Jira
(v8.3.4#803005)

[jira] [Commented] (CALCITE-4034) Implement a MySQL InnoDB adapter

[Julian Hyde (Jira)]

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

Julian Hyde commented on CALCITE-4034:
--

You're welcome! And I appreciate your offer to maintain this new component.

As I mentioned, I think there is an opportunity to improve how Sarg expressions 
are handled. I think InnoDB can handle multiple ranges in a single scan (e.g. 
\{{x between 10 and 20 or x between 50 and 100}}) and if you rework how the 
InnoDB adapter uses Sargs I think you can take advantage of this to generate 
better plans. If you agree, log a new Jira case.

> Implement a MySQL InnoDB adapter
> 
>
> Key: CALCITE-4034
> URL: https://issues.apache.org/jira/browse/CALCITE-4034
> Project: Calcite
>  Issue Type: New Feature
>  Components: innodb-adapter
>Affects Versions: 1.23.0
>Reporter: neoremind
>Assignee: Julian Hyde
>Priority: Minor
>  Labels: pull-request-available
> Fix For: 1.27.0
>
>  Time Spent: 2h 50m
>  Remaining Estimate: 0h
>
> Calcite’s InnoDB adapter allows you to query the data based on InnoDB data 
> files directy, data files are also known as .ibd files, this adapter is 
> different from JDBC adapter which requires a MySQL server to serve response. 
> With .ibd files and the corresponding DDLs, InnoDB adapter is able to work 
> like a simple "MySQL server", it accepts SQL query and attempts to compile 
> the query based on InnoDB file accessing APIs provided by 
> [innodb-java-reader|[https://github.com/alibaba/innodb-java-reader]] (I am 
> the author of the library, which is well-tested and production-ready), it 
> will exploit projecting, filtering and sorting directly in InnoDB data file 
> where possible. What’s more, with DDLs, the adapter is "index aware", it 
> leverages rules to choose the right index to scan, for example, using primary 
> key or secondary keys to look up data, then it tries to push down some 
> conditions into storage engine. Also, the adapter leaves option to provide 
> hint as well, so that user can indicate the optimizer to force use one 
> specific index.
> The InnoDB adapter can,
> 1) Full scan a table.
> 2) Scan by index such as primary key or secondary key, single column or 
> composite keys.
> 3) Push down filtering condition to storage engine, support both point query 
> and range query.
> 4) Push down projection.
> 5) Push down ordering if it matches the natural collation of an index.
> 6) Support almost all the commonly used data types.
> 7) Work as a simple MySQL server being able to read .ibd data files directly 
> through file system, this can be a tool to query or dump table data by 
> offloading from MySQL process under some conditions.
> Before I describe the adapter and its design, I was wondering if it is 
> appropriate to deliver the work by contributing the codebase back to Calcite, 
> or if it would be better to stay in another project, probably being referred 
> from Calcite adapter link. Looking forward to Calcite developer's advice.
>  
> Below is the first version of the usage.
> --
> [MySQL]([https://www.mysql.com/]) is the most popular Open Source SQL 
> database management system, is developed, distributed, and supported by 
> Oracle Corporation. InnoDB is a general-purpose storage engine that balances 
> high reliability and high performance in MySQL, since 5.6 InnoDB has become 
> the default MySQL storage engine.
> Calcite’s InnoDB adapter allows you to query the data based on InnoDB data 
> files directy, data files are also known as .ibd files. It leverages 
> [innodb-java-reader]([https://github.com/alibaba/innodb-java-reader]). This 
> adapter is different from JDBC adapter which maps a schema in a JDBC data 
> source and requires a MySQL server to serve response. With .ibd files and the 
> corresponding DDLs, InnoDB adapter is able to work like a simple "MySQL 
> server", it accepts SQL query and attempts to compile the query based on 
> InnoDB file accessing APIs provided by innodb-java-reader, it will exploit 
> projecting, filtering and sorting directly in InnoDB data file where possible.
> What’s more, with DDLs, the adapter is "index aware", it leverages rules to 
> choose the right index to scan, for example, using primary key or secondary 
> keys to look up data, then it tries to push down some conditions into storage 
> engine. Also, the adapter leaves option to provide hint as well, so that user 
> can indicate the optimizer to force use one specific index.
> A basic example of a model file is given below, this schema reads from a 
> MySQL "scott" database:
> {code:java}
> {
>   "version": "1.0",
>   "defaultSchema": "scott",
>   "schemas": [
> {
>   "name": "scott",
>   "type": "custom",
>   "factory": 

[jira] [Commented] (CALCITE-4317) Some rules fail to handle Aggregate node if RelFieldTrimmer trims all the fields

[Julian Hyde (Jira)]

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

Julian Hyde commented on CALCITE-4317:
--

[~Rafay], No, you don't need to wait for CALCITE-4334 and {{TRUE_AGG}}. Nor do 
you need the optimization to {{Values}}.

I logged CALCITE-4334 but I don't plan to work on it anytime soon.

You should create ensure that you never ask for an Aggregate with zero fields - 
leaving an aggregate function, say {{COUNT(*)}} - and I think that will solve 
this bug.

> Some rules fail to handle Aggregate node if RelFieldTrimmer trims all the 
> fields
> 
>
> Key: CALCITE-4317
> URL: https://issues.apache.org/jira/browse/CALCITE-4317
> Project: Calcite
>  Issue Type: Bug
>  Components: core
>Reporter: Rafay A
>Priority: Major
>
> Consider this query:
> {code:java}
> select o_orderkey from 
> (SELECT count(*) cnt_star, count(l_orderkey) cnt_ok FROM lineitem)
> cross join orders
> limit 10
> {code}
>  which generates this plan:
> {code:java}
> LogicalSort(fetch=[10])
>   LogicalProject(o_orderkey=[$2])
> LogicalJoin(condition=[true], joinType=[inner])
>   LogicalAggregate(group=[{}], cnt_star=[COUNT()], cnt_ok=[COUNT($0)])
> LogicalProject(l_orderkey=[$0])
>   ScanCrel(table=[lineitem], columns=[`l_orderkey`, `l_partkey`, 
> `l_suppkey`, `l_linenumber`, `l_quantity`, `l_extendedprice`, `l_discount`, 
> `l_tax`, `l_returnflag`, `l_linestatus`, `l_shipdate`, `l_commitdate`, 
> `l_receiptdate`, `l_shipinstruct`, `l_shipmode`, `l_comment`])
>   ScanCrel(table=[orders], columns=[`o_orderkey`, `o_custkey`, 
> `o_orderstatus`, `o_totalprice`, `o_orderdate`, `o_orderpriority`, `o_clerk`, 
> `o_shippriority`, `o_comment`])
> {code}
> and after we apply RelFieldTrimmer:
> {code:java}
> LogicalSort(fetch=[10])
>   LogicalProject(o_orderkey=[$0])
> LogicalJoin(condition=[true], joinType=[inner])
>   LogicalAggregate(group=[{}])
> LogicalProject(l_orderkey=[$0])
>   ScanCrel(table=[lineitem], columns=[`l_orderkey`])
>   ScanCrel(table=[orders], columns=[`o_orderkey`])
> {code}
>  
> We see that the LogicalAggregate has no groups/functions after applying the 
> trimmer. When we apply ProjectJoinTransposeRule or LoptOptimizeJoinRule after 
> trimming, we are seeing these exceptions:
> For LoptOptimizeJoinRule:
> {code:java}
>   (java.lang.ArrayIndexOutOfBoundsException) 0
> com.google.common.collect.RegularImmutableList.get():75
> org.apache.calcite.rel.metadata.RelMdColumnOrigins.getColumnOrigins():77
> sun.reflect.NativeMethodAccessorImpl.invoke0():-2
> sun.reflect.NativeMethodAccessorImpl.invoke():62
> sun.reflect.DelegatingMethodAccessorImpl.invoke():43
> java.lang.reflect.Method.invoke():498
> 
> org.apache.calcite.rel.metadata.ReflectiveRelMetadataProvider$1$1.invoke():178
> com.sun.proxy.$Proxy175.getColumnOrigins():-1
> sun.reflect.GeneratedMethodAccessor144.invoke():-1
> sun.reflect.DelegatingMethodAccessorImpl.invoke():43
> java.lang.reflect.Method.invoke():498
> 
> org.apache.calcite.rel.metadata.ChainedRelMetadataProvider$ChainedInvocationHandler.invoke():139
> com.sun.proxy.$Proxy175.getColumnOrigins():-1
> sun.reflect.GeneratedMethodAccessor144.invoke():-1
> sun.reflect.DelegatingMethodAccessorImpl.invoke():43
> java.lang.reflect.Method.invoke():498
> com.sun.proxy.$Proxy175.getColumnOrigins():-1
> sun.reflect.GeneratedMethodAccessor144.invoke():-1
> sun.reflect.DelegatingMethodAccessorImpl.invoke():43
> java.lang.reflect.Method.invoke():498
> 
> org.apache.calcite.rel.metadata.ChainedRelMetadataProvider$ChainedInvocationHandler.invoke():139
> com.sun.proxy.$Proxy175.getColumnOrigins():-1
> sun.reflect.GeneratedMethodAccessor144.invoke():-1
> sun.reflect.DelegatingMethodAccessorImpl.invoke():43
> java.lang.reflect.Method.invoke():498
> com.sun.proxy.$Proxy175.getColumnOrigins():-1
> sun.reflect.GeneratedMethodAccessor144.invoke():-1
> sun.reflect.DelegatingMethodAccessorImpl.invoke():43
> java.lang.reflect.Method.invoke():498
> 
> org.apache.calcite.rel.metadata.RelMetadataQuery$MetadataInvocationHandler.invoke():110
> com.sun.proxy.$Proxy175.getColumnOrigins():-1
> org.apache.calcite.rel.metadata.RelMetadataQuery.getColumnOrigins():293
> org.apache.calcite.rel.metadata.RelMetadataQuery.getTableOrigin():348
> org.apache.calcite.rel.rules.LoptOptimizeJoinRule.getSimpleFactors():377
> 
> org.apache.calcite.rel.rules.LoptOptimizeJoinRule.findRemovableSelfJoins():297
> org.apache.calcite.rel.rules.LoptOptimizeJoinRule.onMatch():126
> org.apache.calcite.plan.AbstractRelOptPlanner.fireRule():317
> 

[jira] [Comment Edited] (CALCITE-4317) Some rules fail to handle Aggregate node if RelFieldTrimmer trims all the fields

[Julian Hyde (Jira)]

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

Julian Hyde edited comment on CALCITE-4317 at 10/13/20, 7:48 AM:
-

[~Rafay], No, you don't need to wait for CALCITE-4334 and {{TRUE_AGG}}. Nor do 
you need the optimization to {{Values}}.

I logged CALCITE-4334 but I don't plan to work on it anytime soon.

You should make sure that you never ask for an Aggregate with zero fields - 
leaving an aggregate function, say {{COUNT\(*)}} - and I think that will solve 
this bug.


was (Author: julianhyde):
[~Rafay], No, you don't need to wait for CALCITE-4334 and {{TRUE_AGG}}. Nor do 
you need the optimization to {{Values}}.

I logged CALCITE-4334 but I don't plan to work on it anytime soon.

You should create ensure that you never ask for an Aggregate with zero fields - 
leaving an aggregate function, say {{COUNT(*)}} - and I think that will solve 
this bug.

> Some rules fail to handle Aggregate node if RelFieldTrimmer trims all the 
> fields
> 
>
> Key: CALCITE-4317
> URL: https://issues.apache.org/jira/browse/CALCITE-4317
> Project: Calcite
>  Issue Type: Bug
>  Components: core
>Reporter: Rafay A
>Priority: Major
>
> Consider this query:
> {code:java}
> select o_orderkey from 
> (SELECT count(*) cnt_star, count(l_orderkey) cnt_ok FROM lineitem)
> cross join orders
> limit 10
> {code}
>  which generates this plan:
> {code:java}
> LogicalSort(fetch=[10])
>   LogicalProject(o_orderkey=[$2])
> LogicalJoin(condition=[true], joinType=[inner])
>   LogicalAggregate(group=[{}], cnt_star=[COUNT()], cnt_ok=[COUNT($0)])
> LogicalProject(l_orderkey=[$0])
>   ScanCrel(table=[lineitem], columns=[`l_orderkey`, `l_partkey`, 
> `l_suppkey`, `l_linenumber`, `l_quantity`, `l_extendedprice`, `l_discount`, 
> `l_tax`, `l_returnflag`, `l_linestatus`, `l_shipdate`, `l_commitdate`, 
> `l_receiptdate`, `l_shipinstruct`, `l_shipmode`, `l_comment`])
>   ScanCrel(table=[orders], columns=[`o_orderkey`, `o_custkey`, 
> `o_orderstatus`, `o_totalprice`, `o_orderdate`, `o_orderpriority`, `o_clerk`, 
> `o_shippriority`, `o_comment`])
> {code}
> and after we apply RelFieldTrimmer:
> {code:java}
> LogicalSort(fetch=[10])
>   LogicalProject(o_orderkey=[$0])
> LogicalJoin(condition=[true], joinType=[inner])
>   LogicalAggregate(group=[{}])
> LogicalProject(l_orderkey=[$0])
>   ScanCrel(table=[lineitem], columns=[`l_orderkey`])
>   ScanCrel(table=[orders], columns=[`o_orderkey`])
> {code}
>  
> We see that the LogicalAggregate has no groups/functions after applying the 
> trimmer. When we apply ProjectJoinTransposeRule or LoptOptimizeJoinRule after 
> trimming, we are seeing these exceptions:
> For LoptOptimizeJoinRule:
> {code:java}
>   (java.lang.ArrayIndexOutOfBoundsException) 0
> com.google.common.collect.RegularImmutableList.get():75
> org.apache.calcite.rel.metadata.RelMdColumnOrigins.getColumnOrigins():77
> sun.reflect.NativeMethodAccessorImpl.invoke0():-2
> sun.reflect.NativeMethodAccessorImpl.invoke():62
> sun.reflect.DelegatingMethodAccessorImpl.invoke():43
> java.lang.reflect.Method.invoke():498
> 
> org.apache.calcite.rel.metadata.ReflectiveRelMetadataProvider$1$1.invoke():178
> com.sun.proxy.$Proxy175.getColumnOrigins():-1
> sun.reflect.GeneratedMethodAccessor144.invoke():-1
> sun.reflect.DelegatingMethodAccessorImpl.invoke():43
> java.lang.reflect.Method.invoke():498
> 
> org.apache.calcite.rel.metadata.ChainedRelMetadataProvider$ChainedInvocationHandler.invoke():139
> com.sun.proxy.$Proxy175.getColumnOrigins():-1
> sun.reflect.GeneratedMethodAccessor144.invoke():-1
> sun.reflect.DelegatingMethodAccessorImpl.invoke():43
> java.lang.reflect.Method.invoke():498
> com.sun.proxy.$Proxy175.getColumnOrigins():-1
> sun.reflect.GeneratedMethodAccessor144.invoke():-1
> sun.reflect.DelegatingMethodAccessorImpl.invoke():43
> java.lang.reflect.Method.invoke():498
> 
> org.apache.calcite.rel.metadata.ChainedRelMetadataProvider$ChainedInvocationHandler.invoke():139
> com.sun.proxy.$Proxy175.getColumnOrigins():-1
> sun.reflect.GeneratedMethodAccessor144.invoke():-1
> sun.reflect.DelegatingMethodAccessorImpl.invoke():43
> java.lang.reflect.Method.invoke():498
> com.sun.proxy.$Proxy175.getColumnOrigins():-1
> sun.reflect.GeneratedMethodAccessor144.invoke():-1
> sun.reflect.DelegatingMethodAccessorImpl.invoke():43
> java.lang.reflect.Method.invoke():498
> 
> org.apache.calcite.rel.metadata.RelMetadataQuery$MetadataInvocationHandler.invoke():110
> com.sun.proxy.$Proxy175.getColumnOrigins():-1
> 

[jira] [Commented] (CALCITE-4317) Some rules fail to handle Aggregate node if RelFieldTrimmer trims all the fields

[Julian Hyde (Jira)]

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

Julian Hyde commented on CALCITE-4317:
--

{quote}To me it seems that a RelNode with zero fields is perfectly valid.
{quote}
Maybe it *should* be valid, but it is a foolish thing to do in Calcite at 
present. For reasons I have explained above. Let's write code that works 
against Calcite as it is, not as we wish it to be.

> Some rules fail to handle Aggregate node if RelFieldTrimmer trims all the 
> fields
> 
>
> Key: CALCITE-4317
> URL: https://issues.apache.org/jira/browse/CALCITE-4317
> Project: Calcite
>  Issue Type: Bug
>  Components: core
>Reporter: Rafay A
>Priority: Major
>
> Consider this query:
> {code:java}
> select o_orderkey from 
> (SELECT count(*) cnt_star, count(l_orderkey) cnt_ok FROM lineitem)
> cross join orders
> limit 10
> {code}
>  which generates this plan:
> {code:java}
> LogicalSort(fetch=[10])
>   LogicalProject(o_orderkey=[$2])
> LogicalJoin(condition=[true], joinType=[inner])
>   LogicalAggregate(group=[{}], cnt_star=[COUNT()], cnt_ok=[COUNT($0)])
> LogicalProject(l_orderkey=[$0])
>   ScanCrel(table=[lineitem], columns=[`l_orderkey`, `l_partkey`, 
> `l_suppkey`, `l_linenumber`, `l_quantity`, `l_extendedprice`, `l_discount`, 
> `l_tax`, `l_returnflag`, `l_linestatus`, `l_shipdate`, `l_commitdate`, 
> `l_receiptdate`, `l_shipinstruct`, `l_shipmode`, `l_comment`])
>   ScanCrel(table=[orders], columns=[`o_orderkey`, `o_custkey`, 
> `o_orderstatus`, `o_totalprice`, `o_orderdate`, `o_orderpriority`, `o_clerk`, 
> `o_shippriority`, `o_comment`])
> {code}
> and after we apply RelFieldTrimmer:
> {code:java}
> LogicalSort(fetch=[10])
>   LogicalProject(o_orderkey=[$0])
> LogicalJoin(condition=[true], joinType=[inner])
>   LogicalAggregate(group=[{}])
> LogicalProject(l_orderkey=[$0])
>   ScanCrel(table=[lineitem], columns=[`l_orderkey`])
>   ScanCrel(table=[orders], columns=[`o_orderkey`])
> {code}
>  
> We see that the LogicalAggregate has no groups/functions after applying the 
> trimmer. When we apply ProjectJoinTransposeRule or LoptOptimizeJoinRule after 
> trimming, we are seeing these exceptions:
> For LoptOptimizeJoinRule:
> {code:java}
>   (java.lang.ArrayIndexOutOfBoundsException) 0
> com.google.common.collect.RegularImmutableList.get():75
> org.apache.calcite.rel.metadata.RelMdColumnOrigins.getColumnOrigins():77
> sun.reflect.NativeMethodAccessorImpl.invoke0():-2
> sun.reflect.NativeMethodAccessorImpl.invoke():62
> sun.reflect.DelegatingMethodAccessorImpl.invoke():43
> java.lang.reflect.Method.invoke():498
> 
> org.apache.calcite.rel.metadata.ReflectiveRelMetadataProvider$1$1.invoke():178
> com.sun.proxy.$Proxy175.getColumnOrigins():-1
> sun.reflect.GeneratedMethodAccessor144.invoke():-1
> sun.reflect.DelegatingMethodAccessorImpl.invoke():43
> java.lang.reflect.Method.invoke():498
> 
> org.apache.calcite.rel.metadata.ChainedRelMetadataProvider$ChainedInvocationHandler.invoke():139
> com.sun.proxy.$Proxy175.getColumnOrigins():-1
> sun.reflect.GeneratedMethodAccessor144.invoke():-1
> sun.reflect.DelegatingMethodAccessorImpl.invoke():43
> java.lang.reflect.Method.invoke():498
> com.sun.proxy.$Proxy175.getColumnOrigins():-1
> sun.reflect.GeneratedMethodAccessor144.invoke():-1
> sun.reflect.DelegatingMethodAccessorImpl.invoke():43
> java.lang.reflect.Method.invoke():498
> 
> org.apache.calcite.rel.metadata.ChainedRelMetadataProvider$ChainedInvocationHandler.invoke():139
> com.sun.proxy.$Proxy175.getColumnOrigins():-1
> sun.reflect.GeneratedMethodAccessor144.invoke():-1
> sun.reflect.DelegatingMethodAccessorImpl.invoke():43
> java.lang.reflect.Method.invoke():498
> com.sun.proxy.$Proxy175.getColumnOrigins():-1
> sun.reflect.GeneratedMethodAccessor144.invoke():-1
> sun.reflect.DelegatingMethodAccessorImpl.invoke():43
> java.lang.reflect.Method.invoke():498
> 
> org.apache.calcite.rel.metadata.RelMetadataQuery$MetadataInvocationHandler.invoke():110
> com.sun.proxy.$Proxy175.getColumnOrigins():-1
> org.apache.calcite.rel.metadata.RelMetadataQuery.getColumnOrigins():293
> org.apache.calcite.rel.metadata.RelMetadataQuery.getTableOrigin():348
> org.apache.calcite.rel.rules.LoptOptimizeJoinRule.getSimpleFactors():377
> 
> org.apache.calcite.rel.rules.LoptOptimizeJoinRule.findRemovableSelfJoins():297
> org.apache.calcite.rel.rules.LoptOptimizeJoinRule.onMatch():126
> org.apache.calcite.plan.AbstractRelOptPlanner.fireRule():317
> org.apache.calcite.plan.hep.HepPlanner.applyRule():556
> 

[jira] [Commented] (CALCITE-4317) Some rules fail to handle Aggregate node if RelFieldTrimmer trims all the fields

[Viliam Durina (Jira)]

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

Viliam Durina commented on CALCITE-4317:


To me it seems that a {{RelNode}} with zero fields is perfectly valid. In case 
of Aggregate you can replace with VALUES with a single row, but that single row 
still should have zero fields.

The SQL standard doesn't allow {{SELECT /* nothing */ FROM my_table}}, but that 
doesn't mean it doesn't make sense internally. I think it would be much simpler 
than hacks with {{TRUE_AGG}} that produce a single row with a field that would 
be removed anyway.

> Some rules fail to handle Aggregate node if RelFieldTrimmer trims all the 
> fields
> 
>
> Key: CALCITE-4317
> URL: https://issues.apache.org/jira/browse/CALCITE-4317
> Project: Calcite
>  Issue Type: Bug
>  Components: core
>Reporter: Rafay A
>Priority: Major
>
> Consider this query:
> {code:java}
> select o_orderkey from 
> (SELECT count(*) cnt_star, count(l_orderkey) cnt_ok FROM lineitem)
> cross join orders
> limit 10
> {code}
>  which generates this plan:
> {code:java}
> LogicalSort(fetch=[10])
>   LogicalProject(o_orderkey=[$2])
> LogicalJoin(condition=[true], joinType=[inner])
>   LogicalAggregate(group=[{}], cnt_star=[COUNT()], cnt_ok=[COUNT($0)])
> LogicalProject(l_orderkey=[$0])
>   ScanCrel(table=[lineitem], columns=[`l_orderkey`, `l_partkey`, 
> `l_suppkey`, `l_linenumber`, `l_quantity`, `l_extendedprice`, `l_discount`, 
> `l_tax`, `l_returnflag`, `l_linestatus`, `l_shipdate`, `l_commitdate`, 
> `l_receiptdate`, `l_shipinstruct`, `l_shipmode`, `l_comment`])
>   ScanCrel(table=[orders], columns=[`o_orderkey`, `o_custkey`, 
> `o_orderstatus`, `o_totalprice`, `o_orderdate`, `o_orderpriority`, `o_clerk`, 
> `o_shippriority`, `o_comment`])
> {code}
> and after we apply RelFieldTrimmer:
> {code:java}
> LogicalSort(fetch=[10])
>   LogicalProject(o_orderkey=[$0])
> LogicalJoin(condition=[true], joinType=[inner])
>   LogicalAggregate(group=[{}])
> LogicalProject(l_orderkey=[$0])
>   ScanCrel(table=[lineitem], columns=[`l_orderkey`])
>   ScanCrel(table=[orders], columns=[`o_orderkey`])
> {code}
>  
> We see that the LogicalAggregate has no groups/functions after applying the 
> trimmer. When we apply ProjectJoinTransposeRule or LoptOptimizeJoinRule after 
> trimming, we are seeing these exceptions:
> For LoptOptimizeJoinRule:
> {code:java}
>   (java.lang.ArrayIndexOutOfBoundsException) 0
> com.google.common.collect.RegularImmutableList.get():75
> org.apache.calcite.rel.metadata.RelMdColumnOrigins.getColumnOrigins():77
> sun.reflect.NativeMethodAccessorImpl.invoke0():-2
> sun.reflect.NativeMethodAccessorImpl.invoke():62
> sun.reflect.DelegatingMethodAccessorImpl.invoke():43
> java.lang.reflect.Method.invoke():498
> 
> org.apache.calcite.rel.metadata.ReflectiveRelMetadataProvider$1$1.invoke():178
> com.sun.proxy.$Proxy175.getColumnOrigins():-1
> sun.reflect.GeneratedMethodAccessor144.invoke():-1
> sun.reflect.DelegatingMethodAccessorImpl.invoke():43
> java.lang.reflect.Method.invoke():498
> 
> org.apache.calcite.rel.metadata.ChainedRelMetadataProvider$ChainedInvocationHandler.invoke():139
> com.sun.proxy.$Proxy175.getColumnOrigins():-1
> sun.reflect.GeneratedMethodAccessor144.invoke():-1
> sun.reflect.DelegatingMethodAccessorImpl.invoke():43
> java.lang.reflect.Method.invoke():498
> com.sun.proxy.$Proxy175.getColumnOrigins():-1
> sun.reflect.GeneratedMethodAccessor144.invoke():-1
> sun.reflect.DelegatingMethodAccessorImpl.invoke():43
> java.lang.reflect.Method.invoke():498
> 
> org.apache.calcite.rel.metadata.ChainedRelMetadataProvider$ChainedInvocationHandler.invoke():139
> com.sun.proxy.$Proxy175.getColumnOrigins():-1
> sun.reflect.GeneratedMethodAccessor144.invoke():-1
> sun.reflect.DelegatingMethodAccessorImpl.invoke():43
> java.lang.reflect.Method.invoke():498
> com.sun.proxy.$Proxy175.getColumnOrigins():-1
> sun.reflect.GeneratedMethodAccessor144.invoke():-1
> sun.reflect.DelegatingMethodAccessorImpl.invoke():43
> java.lang.reflect.Method.invoke():498
> 
> org.apache.calcite.rel.metadata.RelMetadataQuery$MetadataInvocationHandler.invoke():110
> com.sun.proxy.$Proxy175.getColumnOrigins():-1
> org.apache.calcite.rel.metadata.RelMetadataQuery.getColumnOrigins():293
> org.apache.calcite.rel.metadata.RelMetadataQuery.getTableOrigin():348
> org.apache.calcite.rel.rules.LoptOptimizeJoinRule.getSimpleFactors():377
> 
> org.apache.calcite.rel.rules.LoptOptimizeJoinRule.findRemovableSelfJoins():297
> org.apache.calcite.rel.rules.LoptOptimizeJoinRule.onMatch():126
> 

[jira] [Commented] (CALCITE-4317) Some rules fail to handle Aggregate node if RelFieldTrimmer trims all the fields

[Rafay A (Jira)]

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

Rafay A commented on CALCITE-4317:
--

I saw the description there. Are we planning to create a new type of 
SqlAggFunction? Should I wait until CALCITE-4334 is done so I can work on this 
issue after CALCITE-4334 using TRUE_AGG aggregate function, or is the plan is 
to create TRUE_AGG as a part of this ticket?

> Some rules fail to handle Aggregate node if RelFieldTrimmer trims all the 
> fields
> 
>
> Key: CALCITE-4317
> URL: https://issues.apache.org/jira/browse/CALCITE-4317
> Project: Calcite
>  Issue Type: Bug
>  Components: core
>Reporter: Rafay A
>Priority: Major
>
> Consider this query:
> {code:java}
> select o_orderkey from 
> (SELECT count(*) cnt_star, count(l_orderkey) cnt_ok FROM lineitem)
> cross join orders
> limit 10
> {code}
>  which generates this plan:
> {code:java}
> LogicalSort(fetch=[10])
>   LogicalProject(o_orderkey=[$2])
> LogicalJoin(condition=[true], joinType=[inner])
>   LogicalAggregate(group=[{}], cnt_star=[COUNT()], cnt_ok=[COUNT($0)])
> LogicalProject(l_orderkey=[$0])
>   ScanCrel(table=[lineitem], columns=[`l_orderkey`, `l_partkey`, 
> `l_suppkey`, `l_linenumber`, `l_quantity`, `l_extendedprice`, `l_discount`, 
> `l_tax`, `l_returnflag`, `l_linestatus`, `l_shipdate`, `l_commitdate`, 
> `l_receiptdate`, `l_shipinstruct`, `l_shipmode`, `l_comment`])
>   ScanCrel(table=[orders], columns=[`o_orderkey`, `o_custkey`, 
> `o_orderstatus`, `o_totalprice`, `o_orderdate`, `o_orderpriority`, `o_clerk`, 
> `o_shippriority`, `o_comment`])
> {code}
> and after we apply RelFieldTrimmer:
> {code:java}
> LogicalSort(fetch=[10])
>   LogicalProject(o_orderkey=[$0])
> LogicalJoin(condition=[true], joinType=[inner])
>   LogicalAggregate(group=[{}])
> LogicalProject(l_orderkey=[$0])
>   ScanCrel(table=[lineitem], columns=[`l_orderkey`])
>   ScanCrel(table=[orders], columns=[`o_orderkey`])
> {code}
>  
> We see that the LogicalAggregate has no groups/functions after applying the 
> trimmer. When we apply ProjectJoinTransposeRule or LoptOptimizeJoinRule after 
> trimming, we are seeing these exceptions:
> For LoptOptimizeJoinRule:
> {code:java}
>   (java.lang.ArrayIndexOutOfBoundsException) 0
> com.google.common.collect.RegularImmutableList.get():75
> org.apache.calcite.rel.metadata.RelMdColumnOrigins.getColumnOrigins():77
> sun.reflect.NativeMethodAccessorImpl.invoke0():-2
> sun.reflect.NativeMethodAccessorImpl.invoke():62
> sun.reflect.DelegatingMethodAccessorImpl.invoke():43
> java.lang.reflect.Method.invoke():498
> 
> org.apache.calcite.rel.metadata.ReflectiveRelMetadataProvider$1$1.invoke():178
> com.sun.proxy.$Proxy175.getColumnOrigins():-1
> sun.reflect.GeneratedMethodAccessor144.invoke():-1
> sun.reflect.DelegatingMethodAccessorImpl.invoke():43
> java.lang.reflect.Method.invoke():498
> 
> org.apache.calcite.rel.metadata.ChainedRelMetadataProvider$ChainedInvocationHandler.invoke():139
> com.sun.proxy.$Proxy175.getColumnOrigins():-1
> sun.reflect.GeneratedMethodAccessor144.invoke():-1
> sun.reflect.DelegatingMethodAccessorImpl.invoke():43
> java.lang.reflect.Method.invoke():498
> com.sun.proxy.$Proxy175.getColumnOrigins():-1
> sun.reflect.GeneratedMethodAccessor144.invoke():-1
> sun.reflect.DelegatingMethodAccessorImpl.invoke():43
> java.lang.reflect.Method.invoke():498
> 
> org.apache.calcite.rel.metadata.ChainedRelMetadataProvider$ChainedInvocationHandler.invoke():139
> com.sun.proxy.$Proxy175.getColumnOrigins():-1
> sun.reflect.GeneratedMethodAccessor144.invoke():-1
> sun.reflect.DelegatingMethodAccessorImpl.invoke():43
> java.lang.reflect.Method.invoke():498
> com.sun.proxy.$Proxy175.getColumnOrigins():-1
> sun.reflect.GeneratedMethodAccessor144.invoke():-1
> sun.reflect.DelegatingMethodAccessorImpl.invoke():43
> java.lang.reflect.Method.invoke():498
> 
> org.apache.calcite.rel.metadata.RelMetadataQuery$MetadataInvocationHandler.invoke():110
> com.sun.proxy.$Proxy175.getColumnOrigins():-1
> org.apache.calcite.rel.metadata.RelMetadataQuery.getColumnOrigins():293
> org.apache.calcite.rel.metadata.RelMetadataQuery.getTableOrigin():348
> org.apache.calcite.rel.rules.LoptOptimizeJoinRule.getSimpleFactors():377
> 
> org.apache.calcite.rel.rules.LoptOptimizeJoinRule.findRemovableSelfJoins():297
> org.apache.calcite.rel.rules.LoptOptimizeJoinRule.onMatch():126
> org.apache.calcite.plan.AbstractRelOptPlanner.fireRule():317
> org.apache.calcite.plan.hep.HepPlanner.applyRule():556
> org.apache.calcite.plan.hep.HepPlanner.applyRules():415
>   

[jira] [Updated] (CALCITE-4334) TRUE_AGG, an aggregate function that always returns TRUE

[Julian Hyde (Jira)]

 [ 
https://issues.apache.org/jira/browse/CALCITE-4334?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

Julian Hyde updated CALCITE-4334:
-
Description: 
It would be useful to have an (internal) aggregate function that has no 
arguments and returns a constant value, regardless of how many rows are in the 
group. We suggest {{TRUE_AGG}}, which always returns the {{BOOLEAN}} value 
{{TRUE}}.

If there are reasons why it would be better to return other constants (such as 
0, 1, FALSE or the empty string) we will consider them.

For example, when rewriting sub-queries (see SubQueryRemoveRule) we introduce 
add "true as indicator" to the SELECT clause of sub-queries. It can be used to 
detect rows generated by an outer join. If it is an aggregate query, we would 
have to write "min(true) as indicator", which necessitates an extra {{Project}} 
below the {{Aggregate}} to provide the  "true" value. A TRUE_AGG aggregate 
function allows us to avoid the extra {{Project}}.

Another example came up during CALCITE-4317. We would like to make 
{{RelBuilder.aggregate(groupKey())}} throw when given an empty group key and no 
aggregate calls. (Because it would create an {{Aggregate}} that has zero 
fields, and that is problematic elsewhere in Calcite.) But we would also like a 
pattern where an aggregate with an empty group key becomes a constant 
single-row relational expression. So, {{RelBulder.aggregate(groupKey(), 
aggregateCall(TRUE_AGG))}} should generate {{VALUES TRUE}}.

  was:
It would be useful to have an (internal) aggregate function that has no 
arguments and returns a constant value, regardless of how many rows are in the 
group. We suggest {{TRUE_AGG}}, which always returns the {{BOOLEAN}} value 
{{TRUE}}.

If there are reasons why it would be better to return other constants (such as 
0, 1, FALSE or the empty string) we will consider them.

For example, when rewriting sub-queries (see SubQueryRemoveRule) we introduce 
add "true as indicator" to the SELECT clause of sub-queries. It can be used to 
detect rows generated by an outer join. If it is an aggregate query, we would 
have to write "min(true) as indicator", which necessitates an extra {{Project}} 
below the {{Aggregate}} to provide the  "true" value.

Another example. We would like to make {{RelBuilder.aggregate(groupKey())}} 
throw when given an empty group key and no aggregate calls. (Because it would 
create an {{Aggregate}} that has zero fields, and that is problematic elsewhere 
in Calcite.) But we would also like a pattern to generate a constant single-row 
relational expression. So, {{RelBulder.aggregate(groupKey(), 
aggregateCall(TRUE_AGG))}} should generate {{VALUES TRUE}}. 


> TRUE_AGG, an aggregate function that always returns TRUE
> 
>
> Key: CALCITE-4334
> URL: https://issues.apache.org/jira/browse/CALCITE-4334
> Project: Calcite
>  Issue Type: Bug
>Reporter: Julian Hyde
>Priority: Major
>
> It would be useful to have an (internal) aggregate function that has no 
> arguments and returns a constant value, regardless of how many rows are in 
> the group. We suggest {{TRUE_AGG}}, which always returns the {{BOOLEAN}} 
> value {{TRUE}}.
> If there are reasons why it would be better to return other constants (such 
> as 0, 1, FALSE or the empty string) we will consider them.
> For example, when rewriting sub-queries (see SubQueryRemoveRule) we introduce 
> add "true as indicator" to the SELECT clause of sub-queries. It can be used 
> to detect rows generated by an outer join. If it is an aggregate query, we 
> would have to write "min(true) as indicator", which necessitates an extra 
> {{Project}} below the {{Aggregate}} to provide the  "true" value. A TRUE_AGG 
> aggregate function allows us to avoid the extra {{Project}}.
> Another example came up during CALCITE-4317. We would like to make 
> {{RelBuilder.aggregate(groupKey())}} throw when given an empty group key and 
> no aggregate calls. (Because it would create an {{Aggregate}} that has zero 
> fields, and that is problematic elsewhere in Calcite.) But we would also like 
> a pattern where an aggregate with an empty group key becomes a constant 
> single-row relational expression. So, {{RelBulder.aggregate(groupKey(), 
> aggregateCall(TRUE_AGG))}} should generate {{VALUES TRUE}}.



--
This message was sent by Atlassian Jira
(v8.3.4#803005)