[jira] [Updated] (CALCITE-6088) SqlItemOperator fails in RelToSqlConverter

2023-11-02 Thread Jira 


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

Guillaume Massé updated CALCITE-6088:
-
Description: 
We have an expression that looks like:
{code:java}
foo[1].bar {code}
When we run RelToSqlConverter we get the following strack trace: (Calcite 
1.35.0)

 
{code:java}
[info]   java.lang.ClassCastException: class 
org.apache.calcite.sql.SqlBasicCall cannot be cast to class 
org.apache.calcite.sql.SqlIdentifier (org.apache.calcite.sql.SqlBasicCall and 
org.apache.calcite.sql.SqlIdentifier are in unnamed module of loader 
sbt.internal.FlatLoader @3de98cb6)
[info]   at 
org.apache.calcite.rel.rel2sql.SqlImplementor$Context.toSql(SqlImplementor.java:676)
[info]   at 
org.apache.calcite.rel.rel2sql.RelToSqlConverter.visit(RelToSqlConverter.java:458)
[info]   at 
java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
[info]   at 
java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77)
[info]   at 
java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
[info]   at java.base/java.lang.reflect.Method.invoke(Method.java:568)
[info]   at org.apache.calcite.util.ReflectUtil$2.invoke(ReflectUtil.java:532)
[info]   at 
org.apache.calcite.rel.rel2sql.RelToSqlConverter.dispatch(RelToSqlConverter.java:143)
[info]   at 
org.apache.calcite.rel.rel2sql.RelToSqlConverter.visitInput(RelToSqlConverter.java:151)
[info]   at 
org.apache.calcite.rel.rel2sql.SqlImplementor.visitInput(SqlImplementor.java:220)
 {code}
 

This is because there is a missing case for the function
{code:java}
public SqlNode toSql(@Nullable RexProgram program, RexNode rex) { 
// ...

        switch (rex.getKind()){
// ...
case ITEM:
// << missing
default:
sqlIdentifier = (SqlIdentifier) toSql(program, referencedExpr);
}{code}
 

```

The rex kind is ITEM

 

  was:
We have an expression that looks like:

 

```

foo[1].bar

```

When we run RelToSqlConverter we get the following strack trace: (Calcite 
1.35.0)

 

```

[info]   java.lang.ClassCastException: class 
org.apache.calcite.sql.SqlBasicCall cannot be cast to class 
org.apache.calcite.sql.SqlIdentifier (org.apache.calcite.sql.SqlBasicCall and 
org.apache.calcite.sql.SqlIdentifier are in unnamed module of loader 
sbt.internal.FlatLoader @3de98cb6)
[info]   at 
org.apache.calcite.rel.rel2sql.SqlImplementor$Context.toSql(SqlImplementor.java:676)
[info]   at 
org.apache.calcite.rel.rel2sql.RelToSqlConverter.visit(RelToSqlConverter.java:458)
[info]   at 
java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
[info]   at 
java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77)
[info]   at 
java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
[info]   at java.base/java.lang.reflect.Method.invoke(Method.java:568)
[info]   at org.apache.calcite.util.ReflectUtil$2.invoke(ReflectUtil.java:532)
[info]   at 
org.apache.calcite.rel.rel2sql.RelToSqlConverter.dispatch(RelToSqlConverter.java:143)
[info]   at 
org.apache.calcite.rel.rel2sql.RelToSqlConverter.visitInput(RelToSqlConverter.java:151)
[info]   at 
org.apache.calcite.rel.rel2sql.SqlImplementor.visitInput(SqlImplementor.java:220)

```

 

This is because there is a missing case for the function

`public SqlNode toSql(@Nullable RexProgram program, RexNode rex) {`

 

```

        switch (rex.getKind())

{         case ITEM:           // << missing         default:           
System.out.println();           System.out.println("kind: ");           
System.out.println(referencedExpr.getKind());           sqlIdentifier = 
(SqlIdentifier) toSql(program, referencedExpr);         }

```

The rex kind is `ITEM`

 


> SqlItemOperator fails in RelToSqlConverter
> --
>
> Key: CALCITE-6088
> URL: https://issues.apache.org/jira/browse/CALCITE-6088
> Project: Calcite
>  Issue Type: Bug
>  Components: core
>Affects Versions: 1.35.0
>Reporter: Guillaume Massé
>Priority: Major
>
> We have an expression that looks like:
> {code:java}
> foo[1].bar {code}
> When we run RelToSqlConverter we get the following strack trace: (Calcite 
> 1.35.0)
>  
> {code:java}
> [info]   java.lang.ClassCastException: class 
> org.apache.calcite.sql.SqlBasicCall cannot be cast to class 
> org.apache.calcite.sql.SqlIdentifier (org.apache.calcite.sql.SqlBasicCall and 
> org.apache.calcite.sql.SqlIdentifier are in unnamed module of loader 
> sbt.internal.FlatLoader @3de98cb6)
> [info]   at 
> org.apache.calcite.rel.rel2sql.SqlImplementor$Context.toSql(SqlImplementor.java:676)
> [info]   at 
> org.apache.calcite.rel.rel2sql.RelToSqlConverter.visit(RelToSqlConverter.java:458)
> [info]  

[jira] [Updated] (CALCITE-6088) SqlItemOperator fails in RelToSqlConverter

2023-11-02 Thread Jira 


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

Guillaume Massé updated CALCITE-6088:
-
Description: 
We have an expression that looks like:
{code:java}
foo[1].bar {code}
When we run RelToSqlConverter we get the following strack trace: (Calcite 
1.35.0)

 
{code:java}
[info]   java.lang.ClassCastException: class 
org.apache.calcite.sql.SqlBasicCall cannot be cast to class 
org.apache.calcite.sql.SqlIdentifier (org.apache.calcite.sql.SqlBasicCall and 
org.apache.calcite.sql.SqlIdentifier are in unnamed module of loader 
sbt.internal.FlatLoader @3de98cb6)
[info]   at 
org.apache.calcite.rel.rel2sql.SqlImplementor$Context.toSql(SqlImplementor.java:676)
[info]   at 
org.apache.calcite.rel.rel2sql.RelToSqlConverter.visit(RelToSqlConverter.java:458)
[info]   at 
java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
[info]   at 
java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77)
[info]   at 
java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
[info]   at java.base/java.lang.reflect.Method.invoke(Method.java:568)
[info]   at org.apache.calcite.util.ReflectUtil$2.invoke(ReflectUtil.java:532)
[info]   at 
org.apache.calcite.rel.rel2sql.RelToSqlConverter.dispatch(RelToSqlConverter.java:143)
[info]   at 
org.apache.calcite.rel.rel2sql.RelToSqlConverter.visitInput(RelToSqlConverter.java:151)
[info]   at 
org.apache.calcite.rel.rel2sql.SqlImplementor.visitInput(SqlImplementor.java:220)
 {code}
 

This is because there is a missing case for the function
{code:java}
public SqlNode toSql(@Nullable RexProgram program, RexNode rex) { 
// ...

        switch (rex.getKind()){
// ...
case ITEM: // << missing
// missing impl         
default:
sqlIdentifier = (SqlIdentifier) toSql(program, referencedExpr);
}{code}
 

```

The rex kind is ITEM

 

  was:
We have an expression that looks like:
{code:java}
foo[1].bar {code}
When we run RelToSqlConverter we get the following strack trace: (Calcite 
1.35.0)

 
{code:java}
[info]   java.lang.ClassCastException: class 
org.apache.calcite.sql.SqlBasicCall cannot be cast to class 
org.apache.calcite.sql.SqlIdentifier (org.apache.calcite.sql.SqlBasicCall and 
org.apache.calcite.sql.SqlIdentifier are in unnamed module of loader 
sbt.internal.FlatLoader @3de98cb6)
[info]   at 
org.apache.calcite.rel.rel2sql.SqlImplementor$Context.toSql(SqlImplementor.java:676)
[info]   at 
org.apache.calcite.rel.rel2sql.RelToSqlConverter.visit(RelToSqlConverter.java:458)
[info]   at 
java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
[info]   at 
java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77)
[info]   at 
java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
[info]   at java.base/java.lang.reflect.Method.invoke(Method.java:568)
[info]   at org.apache.calcite.util.ReflectUtil$2.invoke(ReflectUtil.java:532)
[info]   at 
org.apache.calcite.rel.rel2sql.RelToSqlConverter.dispatch(RelToSqlConverter.java:143)
[info]   at 
org.apache.calcite.rel.rel2sql.RelToSqlConverter.visitInput(RelToSqlConverter.java:151)
[info]   at 
org.apache.calcite.rel.rel2sql.SqlImplementor.visitInput(SqlImplementor.java:220)
 {code}
 

This is because there is a missing case for the function
{code:java}
public SqlNode toSql(@Nullable RexProgram program, RexNode rex) { 
// ...

        switch (rex.getKind()){
// ...
case ITEM:
// << missing
default:
sqlIdentifier = (SqlIdentifier) toSql(program, referencedExpr);
}{code}
 

```

The rex kind is ITEM

 


> SqlItemOperator fails in RelToSqlConverter
> --
>
> Key: CALCITE-6088
> URL: https://issues.apache.org/jira/browse/CALCITE-6088
> Project: Calcite
>  Issue Type: Bug
>  Components: core
>Affects Versions: 1.35.0
>Reporter: Guillaume Massé
>Priority: Major
>
> We have an expression that looks like:
> {code:java}
> foo[1].bar {code}
> When we run RelToSqlConverter we get the following strack trace: (Calcite 
> 1.35.0)
>  
> {code:java}
> [info]   java.lang.ClassCastException: class 
> org.apache.calcite.sql.SqlBasicCall cannot be cast to class 
> org.apache.calcite.sql.SqlIdentifier (org.apache.calcite.sql.SqlBasicCall and 
> org.apache.calcite.sql.SqlIdentifier are in unnamed module of loader 
> sbt.internal.FlatLoader @3de98cb6)
> [info]   at 
> org.apache.calcite.rel.rel2sql.SqlImplementor$Context.toSql(SqlImplementor.java:676)
> [info]   at 
> org.apache.calcite.rel.rel2sql.RelToSqlConverter.visit(RelToSqlConverter.java:458)
> [info]   at 
> java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native

[jira] [Created] (CALCITE-6088) SqlItemOperator fails in RelToSqlConverter

2023-11-02 Thread Jira 
Guillaume Massé created CALCITE-6088:


 Summary: SqlItemOperator fails in RelToSqlConverter
 Key: CALCITE-6088
 URL: https://issues.apache.org/jira/browse/CALCITE-6088
 Project: Calcite
  Issue Type: Bug
  Components: core
Affects Versions: 1.35.0
Reporter: Guillaume Massé


We have an expression that looks like:

 

```

foo[1].bar

```

When we run RelToSqlConverter we get the following strack trace: (Calcite 
1.35.0)

 

```

[info]   java.lang.ClassCastException: class 
org.apache.calcite.sql.SqlBasicCall cannot be cast to class 
org.apache.calcite.sql.SqlIdentifier (org.apache.calcite.sql.SqlBasicCall and 
org.apache.calcite.sql.SqlIdentifier are in unnamed module of loader 
sbt.internal.FlatLoader @3de98cb6)
[info]   at 
org.apache.calcite.rel.rel2sql.SqlImplementor$Context.toSql(SqlImplementor.java:676)
[info]   at 
org.apache.calcite.rel.rel2sql.RelToSqlConverter.visit(RelToSqlConverter.java:458)
[info]   at 
java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
[info]   at 
java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77)
[info]   at 
java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
[info]   at java.base/java.lang.reflect.Method.invoke(Method.java:568)
[info]   at org.apache.calcite.util.ReflectUtil$2.invoke(ReflectUtil.java:532)
[info]   at 
org.apache.calcite.rel.rel2sql.RelToSqlConverter.dispatch(RelToSqlConverter.java:143)
[info]   at 
org.apache.calcite.rel.rel2sql.RelToSqlConverter.visitInput(RelToSqlConverter.java:151)
[info]   at 
org.apache.calcite.rel.rel2sql.SqlImplementor.visitInput(SqlImplementor.java:220)

```

 

This is because there is a missing case for the function

`public SqlNode toSql(@Nullable RexProgram program, RexNode rex) {`

 

```

        switch (rex.getKind())

{         case ITEM:           // << missing         default:           
System.out.println();           System.out.println("kind: ");           
System.out.println(referencedExpr.getKind());           sqlIdentifier = 
(SqlIdentifier) toSql(program, referencedExpr);         }

```

The rex kind is `ITEM`

 



--
This message was sent by Atlassian Jira
(v8.20.10#820010)

[jira] [Assigned] (CALCITE-5957) Valid DATE '1945-2-2' is not accepted due to regression

2023-11-02 Thread Jira 


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

Guillaume Massé reassigned CALCITE-5957:


Assignee: Guillaume Massé  (was: Runkang He)

> Valid DATE '1945-2-2' is not accepted due to regression
> ---
>
> Key: CALCITE-5957
> URL: https://issues.apache.org/jira/browse/CALCITE-5957
> Project: Calcite
>  Issue Type: Bug
>  Components: core
>Affects Versions: 1.35.0
>Reporter: Runkang He
>Assignee: Guillaume Massé
>Priority: Blocker
>  Labels: pull-request-available
> Fix For: avatica-1.24.0
>
> Attachments: image-2023-08-27-19-09-33-284.png
>
>  Time Spent: 0.5h
>  Remaining Estimate: 0h
>
> DATE '1945-2-2' is a valid date. In CALCITE-5923 when we turn on the result 
> check of `testCastStringToDateTime`, we find that Calcite accepted DATE 
> '1945-2-2' before CALCITE-5678 but not afterwards, so this is a regression 
> that we need to fix.



--
This message was sent by Atlassian Jira
(v8.20.10#820010)

[jira] [Commented] (CALCITE-6080) The simplified form after applying AggregateReduceFunctionsRule is giving wrong results for STDDEV, Covariance with double and decimal types.

2023-11-02 Thread Stamatis Zampetakis (Jira) 


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

Stamatis Zampetakis commented on CALCITE-6080:
--

STDDEV_POP is defined based on other primitive functions so from my perspective 
the expanded and not expanded alternative should both return the same result no 
matter which runtime is used. If the results are not the same then it looks 
more like a runtime problem rather than a rule problem.

> The simplified form after applying AggregateReduceFunctionsRule is giving 
> wrong results for STDDEV, Covariance with double and decimal types.
> -
>
> Key: CALCITE-6080
> URL: https://issues.apache.org/jira/browse/CALCITE-6080
> Project: Calcite
>  Issue Type: Bug
>Reporter: Dayakar M
>Assignee: Dayakar M
>Priority: Major
>
> The simplified form after applying AggregateReduceFunctionsRule is giving 
> wrong results for STDDEV, Covariance with double and decimal types.
> For example, after applying AggregateReduceFunctionsRule 
> {noformat}
> STDDEV_POP(x) -> SQRT((SUM(x * x) - SUM(x) * SUM(x) / COUNT(x)) / COUNT(x))
> {noformat}
> for x as double/decimal, it is giving wrong result which can be easily 
> reproducible with below simple java code
>  
> {code:java}
> double input1 = 23.79d;
>     double o1 = input1 * input1;
>     System.out.println("ip*ip=" + o1);
>     double sum = o1 + o1 + o1;
>     System.out.println("Sum(ip*ip)="+sum);    double sum1 = input1 + input1 + 
> input1;
>     System.out.println("Sum(ip)="+sum1);
>     double sum2 = sum1 * sum1;
>     System.out.println("Sum(ip)*Sum(ip)="+ sum2);
>     double fin = sum2/3d;
>     System.out.println("Sum(ip)*Sum(ip)/3="+fin);
>     double fin1 = sum - fin;
>     System.out.println("Sum(ip*ip)-Sum(ip)*Sum(ip)/3=" + fin1); 
>     System.out.println("SQRT((Sum(ip*ip)-Sum(ip)*Sum(ip)/3)/3)=" + 
> Math.sqrt(fin1/3));{code}
> The output is
> {code:java}
> ip*ip=565.96409
> Sum(ip*ip)=1697.89228
> Sum(ip)=71.37
> Sum(ip)*Sum(ip)=5093.6769
> Sum(ip)*Sum(ip)/3=1697.89232
> Sum(ip*ip)-Sum(ip)*Sum(ip)/3=-4.547473508864641E-13
> SQRT((Sum(ip*ip)-Sum(ip)*Sum(ip)/3)/3)=NaN {code}
> The final output should be *0.0* but here it is coming as {*}NaN{*}.
> So for double and decimal type data we should not simplify STDDEV, Covariance 
> functions as it leads to wrong results.
>  
>  



--
This message was sent by Atlassian Jira
(v8.20.10#820010)

[jira] [Updated] (CALCITE-6087) EnumerableSortedAggregate returns incorrect result when input is empty

2023-11-02 Thread Ruben Q L (Jira) 


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

Ruben Q L updated CALCITE-6087:
---
Description: 
Performing a MAX on an empty table (or on a table where we apply a filter which 
does not return any value) shall return NULL, we can verify this with our 
"standard" {{EnumerableAggregate}} operator:
{code:java}
  @Test void enumerableAggregateOnEmptyInput() {
tester(false, new HrSchema())
.query("select max(deptno) as m from emps where deptno>100")
.explainContains(
"EnumerableAggregate(group=[{}], m=[MAX($1)])\n"
+ "  EnumerableCalc(expr#0..4=[{inputs}], expr#5=[100], 
expr#6=[>($t1, $t5)], proj#0..4=[{exprs}], $condition=[$t6])\n"
+ "EnumerableTableScan(table=[[s, emps]])")
.returnsOrdered("m=null");
  }
{code}
However, if we use {{Hook.PLANNER}} to force the aggregation to be implemented 
via {{EnumerableSortedAggregate}} on the same query:
{code:java}
  @Test void enumerableSortedAggregateOnEmptyInput() {
tester(false, new HrSchema())
.query("select max(deptno) as m from emps where deptno>100")
.withHook(Hook.PLANNER, (Consumer) planner -> {
  planner.removeRule(EnumerableRules.ENUMERABLE_AGGREGATE_RULE);
  planner.addRule(EnumerableRules.ENUMERABLE_SORTED_AGGREGATE_RULE);
})
.explainContains(
"EnumerableSortedAggregate(group=[{}], m=[MAX($1)])\n"
+ "  EnumerableCalc(expr#0..4=[{inputs}], expr#5=[100], 
expr#6=[>($t1, $t5)], proj#0..4=[{exprs}], $condition=[$t6])\n"
+ "EnumerableTableScan(table=[[s, emps]])")
.returnsOrdered("m=null");
  }
{code}
It fails, because the {{EnumerableSortedAggregate}} returns an empty result set 
rather than NULL:
{noformat}
java.lang.AssertionError: 
Expected: "m=null"
 but: was ""
{noformat}

  was:
Performing a MAX on an empty table (or on a table where we apply a filter which 
does not return any value) shall return NULL, we can verify this with our 
"standard" {{EnumerableAggregate}} operator:
{code:java}
  @Test void enumerableAggregateOnEmptyInput() {
tester(false, new HrSchema())
.query("select max(deptno) as m from emps where deptno>100")
.explainContains(
"EnumerableAggregate(group=[{}], m=[MAX($1)])\n"
+ "  EnumerableCalc(expr#0..4=[{inputs}], expr#5=[100], 
expr#6=[>($t1, $t5)], proj#0..4=[{exprs}], $condition=[$t6])\n"
+ "EnumerableTableScan(table=[[s, emps]])")
.returnsOrdered("m=null");
  }
{code}
However, if we use {{Hook.PLANNER}} to force the aggregation to be implemented 
via {{EnumerableSortedAggregate}} on the same query:
{code:java}
  @Test void enumerableSortedAggregateOnEmptyInput() {
tester(false, new HrSchema())
.query("select max(deptno) as m from emps where deptno>100")
.withHook(Hook.PLANNER, (Consumer) planner -> {
  planner.removeRule(EnumerableRules.ENUMERABLE_AGGREGATE_RULE);
  planner.addRule(EnumerableRules.ENUMERABLE_SORTED_AGGREGATE_RULE);
})
.explainContains(
"EnumerableSortedAggregate(group=[{}], m=[MAX($1)])\n"
+ "  EnumerableCalc(expr#0..4=[{inputs}], expr#5=[100], 
expr#6=[>($t1, $t5)], proj#0..4=[{exprs}], $condition=[$t6])\n"
+ "EnumerableTableScan(table=[[s, emps]])")
.returnsOrdered("m=null");
  }
{code}
It fails, because the EnumerableSortedAggregate returns an empty result set 
rather than NULL:
{noformat}
java.lang.AssertionError: 
Expected: "m=null"
 but: was ""
{noformat}


> EnumerableSortedAggregate returns incorrect result when input is empty
> --
>
> Key: CALCITE-6087
> URL: https://issues.apache.org/jira/browse/CALCITE-6087
> Project: Calcite
>  Issue Type: Bug
>  Components: core
>Affects Versions: 1.35.0
>Reporter: Ruben Q L
>Priority: Major
>
> Performing a MAX on an empty table (or on a table where we apply a filter 
> which does not return any value) shall return NULL, we can verify this with 
> our "standard" {{EnumerableAggregate}} operator:
> {code:java}
>   @Test void enumerableAggregateOnEmptyInput() {
> tester(false, new HrSchema())
> .query("select max(deptno) as m from emps where deptno>100")
> .explainContains(
> "EnumerableAggregate(group=[{}], m=[MAX($1)])\n"
> + "  EnumerableCalc(expr#0..4=[{inputs}], expr#5=[100], 
> expr#6=[>($t1, $t5)], proj#0..4=[{exprs}], $condition=[$t6])\n"
> + "EnumerableTableScan(table=[[s, emps]])")
> .returnsOrdered("m=null");
>   }
> {code}
> However, if we use {{Hook.PLANNER}} to force the aggregation to be 
> implemented via {{EnumerableSortedAggregate}} on the same query:
> {code:java}
>   @Test void

[jira] [Commented] (CALCITE-6087) EnumerableSortedAggregate returns incorrect result when input is empty

2023-11-02 Thread Ruben Q L (Jira) 


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

Ruben Q L commented on CALCITE-6087:


Same thing happens if we perform a COUNT on an empty input (which shall return 
0):
{code}
  @Test void enumerableAggregateOnEmptyInput() {
tester(false, new HrSchema())
.query("select count(1) as c from emps where deptno>100")
.explainContains(
"EnumerableAggregate(group=[{}], c=[COUNT()])\n"
+ "  EnumerableCalc(expr#0..4=[{inputs}], expr#5=[100], 
expr#6=[>($t1, $t5)], proj#0..4=[{exprs}], $condition=[$t6])\n"
+ "EnumerableTableScan(table=[[s, emps]])")
.returnsOrdered("c=0");  // ok
  }

  @Test void enumerableSortedAggregateOnEmptyInput() {
tester(false, new HrSchema())
.query("select count(1) as c from emps where deptno>100")
.withHook(Hook.PLANNER, (Consumer) planner -> {
  planner.removeRule(EnumerableRules.ENUMERABLE_AGGREGATE_RULE);
  planner.addRule(EnumerableRules.ENUMERABLE_SORTED_AGGREGATE_RULE);
})
.explainContains(
"EnumerableSortedAggregate(group=[{}], c=[COUNT()])\n"
+ "  EnumerableCalc(expr#0..4=[{inputs}], expr#5=[100], 
expr#6=[>($t1, $t5)], proj#0..4=[{exprs}], $condition=[$t6])\n"
+ "EnumerableTableScan(table=[[s, emps]])")
.returnsOrdered("c=0");  // KO!!! Returns empty!!
  }
{code}

The second test (EnumerableSortedAggregate) will fail with:
{noformat}
java.lang.AssertionError: 
Expected: "c=0"
 but: was ""
{noformat}

> EnumerableSortedAggregate returns incorrect result when input is empty
> --
>
> Key: CALCITE-6087
> URL: https://issues.apache.org/jira/browse/CALCITE-6087
> Project: Calcite
>  Issue Type: Bug
>  Components: core
>Affects Versions: 1.35.0
>Reporter: Ruben Q L
>Priority: Major
>
> Performing a MAX on an empty table (or on a table where we apply a filter 
> which does not return any value) shall return NULL, we can verify this with 
> our "standard" {{EnumerableAggregate}} operator:
> {code:java}
>   @Test void enumerableAggregateOnEmptyInput() {
> tester(false, new HrSchema())
> .query("select max(deptno) as m from emps where deptno>100")
> .explainContains(
> "EnumerableAggregate(group=[{}], m=[MAX($1)])\n"
> + "  EnumerableCalc(expr#0..4=[{inputs}], expr#5=[100], 
> expr#6=[>($t1, $t5)], proj#0..4=[{exprs}], $condition=[$t6])\n"
> + "EnumerableTableScan(table=[[s, emps]])")
> .returnsOrdered("m=null");
>   }
> {code}
> However, if we use {{Hook.PLANNER}} to force the aggregation to be 
> implemented via {{EnumerableSortedAggregate}} on the same query:
> {code:java}
>   @Test void enumerableSortedAggregateOnEmptyInput() {
> tester(false, new HrSchema())
> .query("select max(deptno) as m from emps where deptno>100")
> .withHook(Hook.PLANNER, (Consumer) planner -> {
>   planner.removeRule(EnumerableRules.ENUMERABLE_AGGREGATE_RULE);
>   planner.addRule(EnumerableRules.ENUMERABLE_SORTED_AGGREGATE_RULE);
> })
> .explainContains(
> "EnumerableSortedAggregate(group=[{}], m=[MAX($1)])\n"
> + "  EnumerableCalc(expr#0..4=[{inputs}], expr#5=[100], 
> expr#6=[>($t1, $t5)], proj#0..4=[{exprs}], $condition=[$t6])\n"
> + "EnumerableTableScan(table=[[s, emps]])")
> .returnsOrdered("m=null");
>   }
> {code}
> It fails, because the {{EnumerableSortedAggregate}} returns an empty result 
> set rather than NULL:
> {noformat}
> java.lang.AssertionError: 
> Expected: "m=null"
>  but: was ""
> {noformat}



--
This message was sent by Atlassian Jira
(v8.20.10#820010)

[jira] [Created] (CALCITE-6087) EnumerableSortedAggregate returns incorrect result when input is empty

2023-11-02 Thread Ruben Q L (Jira) 
Ruben Q L created CALCITE-6087:
--

 Summary: EnumerableSortedAggregate returns incorrect result when 
input is empty
 Key: CALCITE-6087
 URL: https://issues.apache.org/jira/browse/CALCITE-6087
 Project: Calcite
  Issue Type: Bug
  Components: core
Affects Versions: 1.35.0
Reporter: Ruben Q L


Performing a MAX on an empty table (or on a table where we apply a filter which 
does not return any value) shall return NULL, we can verify this with our 
"standard" {{EnumerableAggregate}} operator:
{code:java}
  @Test void enumerableAggregateOnEmptyInput() {
tester(false, new HrSchema())
.query("select max(deptno) as m from emps where deptno>100")
.explainContains(
"EnumerableAggregate(group=[{}], m=[MAX($1)])\n"
+ "  EnumerableCalc(expr#0..4=[{inputs}], expr#5=[100], 
expr#6=[>($t1, $t5)], proj#0..4=[{exprs}], $condition=[$t6])\n"
+ "EnumerableTableScan(table=[[s, emps]])")
.returnsOrdered("m=null");
  }
{code}
However, if we use {{Hook.PLANNER}} to force the aggregation to be implemented 
via {{EnumerableSortedAggregate}} on the same query:
{code:java}
  @Test void enumerableSortedAggregateOnEmptyInput() {
tester(false, new HrSchema())
.query("select max(deptno) as m from emps where deptno>100")
.withHook(Hook.PLANNER, (Consumer) planner -> {
  planner.removeRule(EnumerableRules.ENUMERABLE_AGGREGATE_RULE);
  planner.addRule(EnumerableRules.ENUMERABLE_SORTED_AGGREGATE_RULE);
})
.explainContains(
"EnumerableSortedAggregate(group=[{}], m=[MAX($1)])\n"
+ "  EnumerableCalc(expr#0..4=[{inputs}], expr#5=[100], 
expr#6=[>($t1, $t5)], proj#0..4=[{exprs}], $condition=[$t6])\n"
+ "EnumerableTableScan(table=[[s, emps]])")
.returnsOrdered("m=null");
  }
{code}
It fails, because the EnumerableSortedAggregate returns an empty result set 
rather than NULL:
{noformat}
java.lang.AssertionError: 
Expected: "m=null"
 but: was ""
{noformat}



--
This message was sent by Atlassian Jira
(v8.20.10#820010)

[jira] [Assigned] (CALCITE-5863) Calcite rejects valid query with multiple ORDER BY columns and constant RANGE bounds in window functions

2023-11-02 Thread Benchao Li (Jira) 


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

Benchao Li reassigned CALCITE-5863:
---

Assignee: Itiel Sadeh

> Calcite rejects valid query with multiple ORDER BY columns and constant RANGE 
> bounds in window functions
> 
>
> Key: CALCITE-5863
> URL: https://issues.apache.org/jira/browse/CALCITE-5863
> Project: Calcite
>  Issue Type: Bug
>Affects Versions: 1.34.0
>Reporter: Itiel Sadeh
>Assignee: Itiel Sadeh
>Priority: Major
>  Labels: pull-request-available
> Fix For: 1.36.0
>
>
> Usually, it is not valid to specify multiple ORDER BY columns with RANGE 
> bounds in window functions:
> {code:java}
> SELECT sum(x) OVER (ORDER BY x,y RANGE BETWEEN 3 preceding and 4 following) 
> from t;{code}
> However, it is valid if both bounds are "constant" bound (CURRENT ROW, 
> UNBOUNDED PRECEDING and UNBOUNDED FOLLOWING), i.e:
> {code:java}
> SELECT sum(x) OVER (ORDER BY x,y RANGE BETWEEN UNBOUNDED PRECEDING and 
> CURRENT ROW) from t;{code}
> (tested on PostgreSQL and SQL Server)
> Calcite will incorrectly reject it. 



--
This message was sent by Atlassian Jira
(v8.20.10#820010)