[CALCITE-232] Sum and avg of empty set should be null as per SQL specification

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Labels: github-import
• Assignee: Unassigned
• Reporter: GitHub Import
• Created: 18/Jun/14 08:43
• Updated: 18/Jun/14 08:43
• Resolved: 18/Jun/14 08:43

Description

Specification: http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt
```
Scalar expressions 125
b) If AVG, MAX, MIN, or SUM is specified, then
Case:
i) If TXA is empty, then the result is the null value.
```

Optiq:
MAX, MIN – OK

SUM – FAIL – returns 0
```sql
jdbc:optiq> select sum(u) from (select 1 u from (values(1)) where 1=2);
------------

EXPR$0

------------

0

------------
```

AVG – FAIL – division by zero
```sql
select avg(u) m from (select 1 u from (values(1)) where 1=2)
```

```java
public static class Record2_0 implements java.io.Serializable {
public int f0;
public long f1;
public Record2_0(int f0, long f1)

{ this.f0 = f0; this.f1 = f1; }

public boolean equals(Object o) {
if (this == o)

{ return true; }

if (!(o instanceof Record2_0))

{ return false; }

final Record2_0 that = (Record2_0) o;
return this.f0 == that.f0 && this.f1 == that.f1;
}

public int hashCode()

{ int h = 0; h = net.hydromatic.optiq.runtime.Utilities.hash(h, this.f0); h = net.hydromatic.optiq.runtime.Utilities.hash(h, this.f1); return h; }

public int compareTo(Record2_0 that) {
int c;
c = net.hydromatic.optiq.runtime.Utilities.compare(this.f0, that.f0);
if (c != 0)

{ return c; }
c = net.hydromatic.optiq.runtime.Utilities.compare(this.f1, that.f1);
if (c != 0) { return c; }

return 0;
}

public String toString() {
return "

{f0=" + this.f0 + ", f1=" + this.f1 + "}

";
}

}

net.hydromatic.optiq.DataContext root;

public net.hydromatic.linq4j.Enumerable bind(final net.hydromatic.optiq.DataContext root0) {
root = root0;
final net.hydromatic.linq4j.Enumerable _inputEnumerable = net.hydromatic.linq4j.Linq4j.asEnumerable(new Integer[]

{ 1}

);
final net.hydromatic.linq4j.AbstractEnumerable child = new net.hydromatic.linq4j.AbstractEnumerable(){
public net.hydromatic.linq4j.Enumerator enumerator() {
return new net.hydromatic.linq4j.Enumerator(){
public final net.hydromatic.linq4j.Enumerator inputEnumerator = _inputEnumerable.enumerator();
public void reset()

{ inputEnumerator.reset(); }

public boolean moveNext() {
while (inputEnumerator.moveNext()) {
if (1 == 2)

{ return true; }

}
return false;
}

public void close()

{ inputEnumerator.close(); }

public Object current()

{ return 1; }

};
}

};
final net.hydromatic.linq4j.Enumerable _inputEnumerable0 = net.hydromatic.linq4j.Linq4j.singletonEnumerable(child.aggregate(new net.hydromatic.linq4j.function.Function0() {
public Object apply()

{ return new Record2_0( 0, 0L); }

}
.apply(), new net.hydromatic.linq4j.function.Function2() {
public Record2_0 apply(Record2_0 acc, int in)

{ acc.f0 = acc.f0 + in; acc.f1 = acc.f1 + 1L; return acc; }

public Record2_0 apply(Record2_0 acc, Integer in)

{ return apply( acc, in.intValue()); }

public Record2_0 apply(Object acc, Object in)

{ return apply( (Record2_0) acc, (Integer) in); }

}
, new net.hydromatic.linq4j.function.Function1() {
public Object[] apply(Record2_0 acc) {
return new Object[]

{ acc.f0, acc.f1}

;
}
public Object apply(Object acc)

{ return apply( (Record2_0) acc); }

}
));
return new net.hydromatic.linq4j.AbstractEnumerable(){
public net.hydromatic.linq4j.Enumerator enumerator() {
return new net.hydromatic.linq4j.Enumerator(){
public final net.hydromatic.linq4j.Enumerator inputEnumerator = _inputEnumerable0.enumerator();
public void reset()

{ inputEnumerator.reset(); }

public boolean moveNext()

{ return inputEnumerator.moveNext(); }

public void close()

{ inputEnumerator.close(); }

public Object current()

{ final Object[] current0 = (Object[]) inputEnumerator.current(); return (int) ((long) net.hydromatic.optiq.runtime.SqlFunctions.toInt(current0[0]) / net.hydromatic.optiq.runtime.SqlFunctions.toLong(current0[1])); }

};
}

};
}

public java.lang.reflect.Type getElementType() {
return int.class;
}

java.lang.RuntimeException: exception while executing [select avg(u) m from (select 1 u from (values(1)) where 1=2)]
at net.hydromatic.optiq.test.OptiqAssert$AssertQuery.returns(OptiqAssert.java:777)
at net.hydromatic.optiq.test.OptiqAssert$AssertQuery.returns(OptiqAssert.java:764)
at net.hydromatic.optiq.test.JdbcTest.testUserDefinedFunction(JdbcTest.java:2786)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:47)
at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:44)
at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:271)
at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:70)
at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:50)
at org.junit.runners.ParentRunner$3.run(ParentRunner.java:238)
at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:63)
at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:236)
at org.junit.runners.ParentRunner.access$000(ParentRunner.java:53)
at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:229)
at org.junit.runners.ParentRunner.run(ParentRunner.java:309)
at org.junit.runner.JUnitCore.run(JUnitCore.java:160)
at com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:74)
at com.intellij.rt.execution.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:202)
at com.intellij.rt.execution.junit.JUnitStarter.main(JUnitStarter.java:65)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
at com.intellij.rt.execution.application.AppMain.main(AppMain.java:120)
Caused by: java.lang.RuntimeException: With materializationsEnabled=false, limit=0
at net.hydromatic.optiq.test.OptiqAssert.assertQuery(OptiqAssert.java:343)
at net.hydromatic.optiq.test.OptiqAssert$AssertQuery.returns(OptiqAssert.java:773)
... 28 more
Caused by: java.lang.ArithmeticException: / by zero
at Baz$5$1.current(Unknown Source)
at net.hydromatic.optiq.runtime.ObjectEnumeratorCursor$ObjectEnumeratorGetter.getObject(ObjectEnumeratorCursor.java:60)
at net.hydromatic.optiq.runtime.AbstractCursor$AccessorImpl.getObject(AbstractCursor.java:278)
at net.hydromatic.optiq.runtime.AbstractCursor$AccessorImpl.getString(AbstractCursor.java:221)
at net.hydromatic.avatica.AvaticaResultSet.getString(AvaticaResultSet.java:190)
at net.hydromatic.optiq.test.OptiqAssert.toString(OptiqAssert.java:354)
at net.hydromatic.optiq.test.OptiqAssert$3.apply(OptiqAssert.java:166)
at net.hydromatic.optiq.test.OptiqAssert$3.apply(OptiqAssert.java:163)
at net.hydromatic.optiq.test.OptiqAssert.assertQuery(OptiqAssert.java:337)
... 29 more
```

---------------- Imported from GitHub ----------------
Url: https://github.com/julianhyde/optiq/issues/232
Created by: vlsi
Labels: bug,
Created at: Sat Apr 05 16:19:11 CEST 2014
State: closed