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

"GROUP BY constant" on empty relation should return 0 rows

    Details

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

      Description

      What should "GROUP BY 1" return on an empty table? Calcite currently returns 0 rows. Does that comply with the SQL standard?

      Here is what Oracle 11.2.0.2.0 does.

      SQL> select count(*) from emp where 1 = 0;
      
        COUNT(*)
      ----------
      	 0
      
      SQL> select count(*) from emp where 1 = 0 group by ();
      
      no rows selected
      
      SQL> select count(*) from emp where 1 = 0 group by 1;
      
      no rows selected
      
      SQL> select count(*) from emp;
      
        COUNT(*)
      ----------
      	14
      
      SQL> select count(*) from emp group by ();
      
        COUNT(*)
      ----------
      	14
      
      SQL> select count(*) from emp group by 1;
      
        COUNT(*)
      ----------
      	14
      

      I had expected

      select count(*) from emp where 1 = 0 group by ()

      would return 1 row, but it returns 0, like

      group by 1

      .

        Issue Links

          Activity

          Hide
          jpullokkaran Laljo John Pullokkaran added a comment - - edited

          Julian Hyde Group by constant on an empty table should return no rows.

          hive> select count(*) from gbt1;
          Total MapReduce CPU Time Spent: 0 msec
          OK
          0
          Time taken: 4.846 seconds, Fetched: 1 row(s)
          
          hive> select count(*) from gbt1 group by 1;
          OK
          Time taken: 1.503 seconds
          
          Show
          jpullokkaran Laljo John Pullokkaran added a comment - - edited Julian Hyde Group by constant on an empty table should return no rows. hive> select count(*) from gbt1; Total MapReduce CPU Time Spent: 0 msec OK 0 Time taken: 4.846 seconds, Fetched: 1 row(s) hive> select count(*) from gbt1 group by 1; OK Time taken: 1.503 seconds
          Hide
          julianhyde Julian Hyde added a comment - - edited

          Laljo John Pullokkaran, Got it.

          Should select count('1') from gbt1 group by () return no rows? My understanding is that it should, but Oracle returns 1 row.

          You should correct the first paragraph of HIVE-12640. It contradicts what you said above.

          Show
          julianhyde Julian Hyde added a comment - - edited Laljo John Pullokkaran , Got it. Should select count('1') from gbt1 group by () return no rows? My understanding is that it should, but Oracle returns 1 row. You should correct the first paragraph of HIVE-12640 . It contradicts what you said above.
          Hide
          julianhyde Julian Hyde added a comment -

          MySQL agrees with Oracle:

          mysql> select count(*) from days where 1=0;
          +----------+
          | count(*) |
          +----------+
          |        0 |
          +----------+
          1 row in set (0.00 sec)
          
          mysql> select count(*) from days where 1=0 group by '1';
          Empty set (0.00 sec)
          

          (MySQL does not support 'group by 1' or 'group by ()'.)

          Show
          julianhyde Julian Hyde added a comment - MySQL agrees with Oracle: mysql> select count(*) from days where 1=0; +----------+ | count(*) | +----------+ | 0 | +----------+ 1 row in set (0.00 sec) mysql> select count(*) from days where 1=0 group by '1'; Empty set (0.00 sec) (MySQL does not support 'group by 1' or 'group by ()'.)
          Hide
          vladimirsitnikov Vladimir Sitnikov added a comment -

          7.9 <group by clause>
          2) Case:
          a) If there are no grouping columns, then the result of the <group by clause> is the grouped
          table consisting of T as its only group

          7.11 <query specification>
          b) If T is a grouped table, then
          Case:
          i) If T has 0 (zero) groups, then the result of the <query specification> is an empty table

          "I'm not a lawyer, but" it sounds like "as soon as you have a group_by_clause, empty input results in empty output".

          Show
          vladimirsitnikov Vladimir Sitnikov added a comment - 7.9 <group by clause> 2) Case: a) If there are no grouping columns, then the result of the <group by clause> is the grouped table consisting of T as its only group 7.11 <query specification> b) If T is a grouped table, then Case: i) If T has 0 (zero) groups, then the result of the <query specification> is an empty table "I'm not a lawyer, but" it sounds like "as soon as you have a group_by_clause, empty input results in empty output".
          Hide
          julianhyde Julian Hyde added a comment - - edited

          Vladimir Sitnikov, I'd correct that to "as soon as you have a non-empty group_by_clause, empty input results in empty output".

          So, Oracle does not comply with the standard for the query select count( * ) from emp where 1 = 0 group by (). Per the standard, it should return 1 row.

          Show
          julianhyde Julian Hyde added a comment - - edited Vladimir Sitnikov , I'd correct that to "as soon as you have a non-empty group_by_clause, empty input results in empty output". So, Oracle does not comply with the standard for the query select count( * ) from emp where 1 = 0 group by () . Per the standard, it should return 1 row.
          Hide
          julianhyde Julian Hyde added a comment -

          Also, per the standard, select '1' from emp where 1 = 0 group by '1' should return zero rows.

          Show
          julianhyde Julian Hyde added a comment - Also, per the standard, select '1' from emp where 1 = 0 group by '1' should return zero rows.
          Hide
          vladimirsitnikov Vladimir Sitnikov added a comment -

          Per the standard, it should return 1 row.

          Why?

          Show
          vladimirsitnikov Vladimir Sitnikov added a comment - Per the standard, it should return 1 row. Why?
          Hide
          vladimirsitnikov Vladimir Sitnikov added a comment - - edited

          select '1' from emp where 1 = 0 group by '1'

          Do you really expect

          select '1' from emp where 1 = 0 group by '1'

          to differ from

          select x from (select '1' as x from emp where 1 = 0) as tmp group by x

          ?

          The latter gives no rows in PostgreSQL.
          PG forbids to group by on constant value (it treats constant as a positioned column reference)

          Show
          vladimirsitnikov Vladimir Sitnikov added a comment - - edited select '1' from emp where 1 = 0 group by '1' Do you really expect select '1' from emp where 1 = 0 group by '1' to differ from select x from ( select '1' as x from emp where 1 = 0) as tmp group by x ? The latter gives no rows in PostgreSQL. PG forbids to group by on constant value (it treats constant as a positioned column reference)
          Hide
          julianhyde Julian Hyde added a comment -

          Vladimir Sitnikov, Yes, both those statements are equivalent to select '1' from emp where 1 = 0 group by 0+1, which also gives no rows.

          Regarding select count( * ) from emp where 1 = 0 group by (). There are no grouping columns, so by case (a) the result of the group by clause is the grouped table consisting of select * from emp where 1 = 0 as its only group.

          Read that sentence again. There is one group. It contains 0 rows.

          Case ( i ) does not apply, because the grouped table has 1 group. By case (ii), it emits one summary row per group. That is, one row.

          Show
          julianhyde Julian Hyde added a comment - Vladimir Sitnikov , Yes, both those statements are equivalent to select '1' from emp where 1 = 0 group by 0+1 , which also gives no rows. Regarding select count( * ) from emp where 1 = 0 group by () . There are no grouping columns, so by case (a) the result of the group by clause is the grouped table consisting of select * from emp where 1 = 0 as its only group. Read that sentence again. There is one group. It contains 0 rows. Case ( i ) does not apply, because the grouped table has 1 group. By case (ii), it emits one summary row per group. That is, one row.
          Hide
          julianhyde Julian Hyde added a comment -

          It seems that Calcite already complies with the standard. To fix this issue, we need to add some tests to agg.oq.

          Show
          julianhyde Julian Hyde added a comment - It seems that Calcite already complies with the standard. To fix this issue, we need to add some tests to agg.oq.
          Hide
          julianhyde Julian Hyde added a comment -

          This was already working; added test in http://git-wip-us.apache.org/repos/asf/calcite/commit/02e7ad61.

          Show
          julianhyde Julian Hyde added a comment - This was already working; added test in http://git-wip-us.apache.org/repos/asf/calcite/commit/02e7ad61 .
          Hide
          julianhyde Julian Hyde added a comment -

          Resolved in release 1.6.0 (2016-01-22).

          Show
          julianhyde Julian Hyde added a comment - Resolved in release 1.6.0 (2016-01-22).

            People

            • Assignee:
              julianhyde Julian Hyde
              Reporter:
              julianhyde Julian Hyde
            • Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Development