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

DENSE_RANK is not calculated correctly

    XMLWordPrintable

Details

    Description

      We encountered a case when DENSE_RANK() window function does not work correctly.
      See the repro below and in the attached file.

      CREATE TABLE student(course VARCHAR(10), mark int, name varchar(10), score int);
       
      INSERT INTO student VALUES 
        ('Maths', 60, 'Thulile', 1515),
        ('Maths', 60, 'Pritha', 2000),
        ('Maths', 70, 'Voitto', 2000),
        ('Maths', 55, 'Chun', 1600),
        ('Biology', 60, 'Bilal', 3000),
        ('Biology', 70, 'Roger', 3000),
        (NULL, 80, 'Johnson', 2000);
      

      case 1 - Correct results:

      SELECT course,
      DENSE_RANK() OVER (PARTITION BY course ORDER BY SUM(mark) DESC) AS dense_rank, mark, name, score
      FROM student GROUP BY course, score;
      +---------+------------+------+---------+-------+
      | course  | dense_rank | mark | name    | score |
      +---------+------------+------+---------+-------+
      | NULL    |         1 |   80 | Johnson |  2000 |
      | Biology |          1 |   60 | Bilal        |  3000 |
      | Maths   |          2 |   60 | Thulile    |  1515 |
      | Maths   |          3 |   55 | Chun      |  1600 |
      | Maths   |          1 |   60 | Pritha     |  2000 |
      +---------+------------+------+---------+-------+
      5 rows in set (0.001 sec)
      

      case 2 - Wrong results:

      SELECT course,
      DENSE_RANK() OVER (PARTITION BY course ORDER BY (SUM(mark) * 2) DESC) AS dense_rank, mark, name, score
      FROM student GROUP BY course, score;
      +---------+------------+------+---------+-------+
      | course  | dense_rank | mark | name    | score |
      +---------+------------+------+---------+-------+
      | NULL    |         1 |   80 | Johnson |  2000 |
      | Biology |          1 |   60 | Bilal        |  3000 |
      | Maths   |          1 |   60 | Thulile   |  1515 |
      | Maths   |          1 |   55 | Chun     |  1600 |
      | Maths   |          1 |   60 | Pritha    |  2000 |
      +---------+------------+------+---------+-------+
      5 rows in set (0.000 sec)
      
      

      Attachments

        Issue Links

          Activity

            People

              varun Varun Gupta (Inactive)
              Gofman Yekaterina
              Votes:
              0 Vote for this issue
              Watchers:
              5 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.