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

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

VotersWatch issueWatchersLinkCloneUpdate Comment AuthorReplace String in CommentUpdate Comment VisibilityDelete Comments
    XMLWordPrintableJSON

Details

    • Bug
    • Status: Closed
    • Major
    • Resolution: Fixed
    • None
    • 1.6.0
    • None
    • 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

      .

      Attachments

        Issue Links

        Activity

          This comment will be Viewable by All Users Viewable by All Users
          Cancel

          People

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

            Dates

              Created:
              Updated:
              Resolved:

              Slack

                Issue deployment