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

Window aggregates invalid error/error messages in some cases

    Details

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

      Description

      Queries which fail (or fail with inconsistent errors)
      Q1:

      select count( * ) over () from emp group by deptno 
      AssertionError: star should have been expanded.
      

      Q2:

      SELECT sum(empno), max(empno) OVER (order by deptno) 
      FROM emp 
      GROUP BY empno 
      gives error: Expression 'DEPTNO' is not being grouped
      
      SELECT sum(empno), max(empno) OVER w 
      FROM emp 
      GROUP BY empno 
      WINDOW w as (order by deptno)
      gives no error:
      

      Q3:

      select cume_dist() over w , rank() 
      from emp 
      window w as (partition by deptno order by deptno)
      Assertion Error: Expression 'DEPTNO' is not being grouped
      instead of 
      Assertion Error: OVER clause is necessary for window functions
      

        Issue Links

          Activity

          Hide
          gparai Gautam Kumar Parai added a comment - - edited

          Q4 occurs when Q3 is fixed. Otherwise, it does not occur since we miss to check the WINDOW clause.

          Show
          gparai Gautam Kumar Parai added a comment - - edited Q4 occurs when Q3 is fixed. Otherwise, it does not occur since we miss to check the WINDOW clause.
          Hide
          zfong Zelaine Fong added a comment -

          Your Q2 and Q3 look like the same queries. Typo?

          Perhaps you meant for Q3 to be the second problem you noted in your comment on 8/1 in CALCITE-1327?

          Show
          zfong Zelaine Fong added a comment - Your Q2 and Q3 look like the same queries. Typo? Perhaps you meant for Q3 to be the second problem you noted in your comment on 8/1 in CALCITE-1327 ?
          Hide
          gparai Gautam Kumar Parai added a comment -

          It was a typo. Thanks for noticing it Zelaine!

          Show
          gparai Gautam Kumar Parai added a comment - It was a typo. Thanks for noticing it Zelaine!
          Hide
          julianhyde Julian Hyde added a comment -

          Regarding how you fix Q3. Obviously you need to validate that RANK has a window, but you don't need to do it as the first step in validation. It would be acceptable if you skipped the RANK function and did not regard the query as an aggregate query. Then the "OVER clause is necessary for window functions" error will happen in due course. (It should be a validation error, not an AssertionError.)

          Show
          julianhyde Julian Hyde added a comment - Regarding how you fix Q3. Obviously you need to validate that RANK has a window, but you don't need to do it as the first step in validation. It would be acceptable if you skipped the RANK function and did not regard the query as an aggregate query. Then the "OVER clause is necessary for window functions" error will happen in due course. (It should be a validation error, not an AssertionError.)
          Show
          julianhyde Julian Hyde added a comment - Fixed in http://git-wip-us.apache.org/repos/asf/calcite/commit/69839c37 ; thanks for the PR, Gautam Kumar Parai !
          Hide
          jcamachorodriguez Jesus Camacho Rodriguez added a comment -

          Resolved in release 1.9.0 (2016-09-22)

          Show
          jcamachorodriguez Jesus Camacho Rodriguez added a comment - Resolved in release 1.9.0 (2016-09-22)

            People

            • Assignee:
              julianhyde Julian Hyde
              Reporter:
              gparai Gautam Kumar Parai
            • Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Development