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

RelToSql converter generates GROUP BY clause with window expression

Attach filesAttach ScreenshotAdd voteVotersWatch issueWatchersCreate sub-taskLinkCloneUpdate Comment AuthorReplace String in CommentUpdate Comment VisibilityDelete Comments
    XMLWordPrintableJSON

Details

    Description

      Wrong SQL code is generated when aggregation is done on the field being a result of window expression evaluation.

      Example can be demonstrated by adding following code to RelToSqlConverterTest.java:

      @Test void testConvertWindowGroupByToSql() {
        String query = "SELECT * FROM ("
            + "SELECT rank() over (order by \"hire_date\") \"rank\" FROM \"employee\""
            + ") GROUP BY \"rank\"";
        String expected ="SELECT * FROM ("
            + "SELECT rank() over (order by \"hire_date\") AS \"$0\" FROM \"employee\""
            + ") GROUP BY \"$0\"";
      
        sql(query).ok(expected);
      }
      

      Generated SQL code will look like:

      SELECT RANK() OVER (ORDER BY hire_date) AS rank
      FROM foodmart.employee
      GROUP BY RANK() OVER (ORDER BY hire_date)

      This is incorrect - window expressions are not allowed in GROUP BY clause by SQL standard and Calcite itself would produce following error message if this SQL code would be passed as input: 

      Windowed aggregate expression is illegal in GROUP BY clause 

      Attachments

        Issue Links

        Activity

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

          People

            jiajunbernoulli Jiajun Xie
            lchistov1987 Leonid Chistov

            Dates

              Created:
              Updated:

              Slack

                Issue deployment