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

Window functions not working in ONLY_FULL_GROUP_BY mode

    Details

      Description

      Window functions don't seem to work in ONLY_FULL_GROUP_BY mode.

      Using the example queries from the documentation:

      CREATE TABLE student (name CHAR(10), test CHAR(10), score TINYINT); 
       
      INSERT INTO student VALUES 
        ('Chun', 'SQL', 75), ('Chun', 'Tuning', 73), 
        ('Esben', 'SQL', 43), ('Esben', 'Tuning', 31), 
        ('Kaolin', 'SQL', 56), ('Kaolin', 'Tuning', 88), 
        ('Tatiana', 'SQL', 87), ('Tatiana', 'Tuning', 83);
       
      SET sql_mode = 'ONLY_FULL_GROUP_BY';
       
      SELECT name, test, score,
        AVG(score) OVER (PARTITION BY test) AS average_by_test
      FROM student;
      

      The SELECT query fails with an error:

      ERROR 1055 (42000): 'database.student.name' isn't in GROUP BY

      From my understanding, this is a valid query. It works on MySQL 8.0 (fiddle) and PostgreSQL (fiddle).

        Attachments

          Activity

            People

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

              Dates

              • Created:
                Updated:
                Resolved: