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

Window functions not working in ONLY_FULL_GROUP_BY mode

    XMLWordPrintable

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

            varun Varun Gupta (Inactive)
            staudenmeir Jonas Staudenmeir
            Votes:
            2 Vote for this issue
            Watchers:
            7 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.