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

Error when executing query with GROUP BY constant via JDBC adapter

VotersWatch issueWatchersLinkCloneUpdate Comment AuthorReplace String in CommentUpdate Comment VisibilityDelete Comments
    XMLWordPrintableJSON

Details

    Description

      The following functionally equivalent SQL queries are accepted by Calcite and produce a valid plan

      select avg(salary) from employee group by true
      select avg(salary) from employee group by 'a'
      

      but they may fail if they are executed via the JDBC adapter since not all DBMS allow grouping by constants expressions. Moreover, what works for one may not work for the other.

      Examples
      The GROUP BY TRUE query works in Postgres, and MySQL but fails in Redshift with the following exception:

      com.amazon.redshift.util.RedshiftException: ERROR: non-integer constant in GROUP BY
      

      The GROUP BY 'a' query works in MySQL but fails in Postgres with the following exception:

      ERROR:  non-integer constant in GROUP BY
      

      Edit:
      The GROUP BY constant is similar to GROUP BY () "nothing" but as shown in the discussion below they are not equivalent. There is a nice blog post listing some limitations of various DBMS when it comes to GROUP BY ().

      Attachments

        Issue Links

        Activity

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

          People

            soumyakanti.das Soumyakanti Das
            zabetak Stamatis Zampetakis
            Votes:
            0 Vote for this issue
            Watchers:
            8 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Time Tracking

                Estimated:
                Original Estimate - Not Specified
                Not Specified
                Remaining:
                Remaining Estimate - 0h
                0h
                Logged:
                Time Spent - 1h 40m
                1h 40m

                Slack

                  Issue deployment