[ 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<RelOptPlanner>) 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<RelOptPlanner>) 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 enumerableSortedAggregateOnEmptyInput() { > tester(false, new HrSchema()) > .query("select max(deptno) as m from emps where deptno>100") > .withHook(Hook.PLANNER, (Consumer<RelOptPlanner>) 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)