Uploaded image for project: 'Calcite'
  1. Calcite
  2. CALCITE-232

Sum and avg of empty set should be null as per SQL specification

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: None
    • Component/s: None
    • Labels:

      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

        Activity

        Hide
        github-import GitHub Import added a comment -

        [Date: Sat Apr 05 20:39:25 CEST 2014, Author: julianhyde]

        Agreed.

        Also, sum and avg should return null if all the columns they are applied to are null. This applies with and without a GROUP BY clause.

        Show
        github-import GitHub Import added a comment - [Date: Sat Apr 05 20:39:25 CEST 2014, Author: julianhyde ] Agreed. Also, sum and avg should return null if all the columns they are applied to are null. This applies with and without a GROUP BY clause.

          People

          • Assignee:
            Unassigned
            Reporter:
            github-import GitHub Import
          • Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved:

              Development