[
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)
