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

DENSE_RANK is not calculated correctly

    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

              • Assignee:
                varun Varun Gupta
                Reporter:
                Gofman Yekaterina
              • Votes:
                0 Vote for this issue
                Watchers:
                4 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: