Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-17785

Window functions not working in ONLY_FULL_GROUP_BY mode II

    Details

      Description

      This is a follow-up to MDEV-17525.

      The patch fixed the AVG() function from the example query, but other window functions still don't work in ONLY_FULL_GROUP_BY mode.

      Using a different example from the documentation:

      CREATE TABLE student(course VARCHAR(10), mark int, name varchar(10));
       
      INSERT INTO student VALUES 
        ('Maths', 60, 'Thulile'),
        ('Maths', 60, 'Pritha'),
        ('Maths', 70, 'Voitto'),
        ('Maths', 55, 'Chun'),
        ('Biology', 60, 'Bilal'),
         ('Biology', 70, 'Roger');
       
      SET sql_mode = 'ONLY_FULL_GROUP_BY';
       
      SELECT 
        RANK() OVER (PARTITION BY course ORDER BY mark DESC) AS rank, 
        DENSE_RANK() OVER (PARTITION BY course ORDER BY mark DESC) AS dense_rank, 
        ROW_NUMBER() OVER (PARTITION BY course ORDER BY mark DESC) AS row_num, 
        course, mark, name 
      FROM student ORDER BY course, mark DESC;
      

      The SELECT query fails with an error:

      Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause

        Attachments

          Activity

            People

            • Assignee:
              varun Varun Gupta
              Reporter:
              staudenmeir Jonas Staudenmeir
            • Votes:
              2 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

              • Created:
                Updated: