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

            i5on9i namh added a comment - - edited

            This issue is showed on latest mariadb versions.

            my test query :

            SELECT 
                   DENSE_RANK () OVER (ORDER BY `mytable`.`id` ASC) AS RANK33,
                   DENSE_RANK() over (ORDER BY `mytable`.`id` DESC) AS RANK44 ,
                   (DENSE_RANK () OVER (ORDER BY `mytable`.`id` ASC) + 1 AS total
            FROM `mytable`
            

            I've run a query with calculation DENSE_RANK. I got an right answer on version, '10.6.10-MariaDB-1:10.6.10+maria~ubu2004'

            I've used the Podman/Windows WSL

            The versions i've tested

            • 10.6.10 : toatl is 114
            • 10.6.15 : total is 0
            • lts(maybe v11) : total is 0

            I've tested with windows version MariaDB (MSI installer), too.

            only works well in 10.6.10
            failed on 10.6.15

            i5on9i namh added a comment - - edited This issue is showed on latest mariadb versions. my test query : SELECT DENSE_RANK () OVER ( ORDER BY `mytable`.`id` ASC ) AS RANK33, DENSE_RANK() over ( ORDER BY `mytable`.`id` DESC ) AS RANK44 , (DENSE_RANK () OVER ( ORDER BY `mytable`.`id` ASC ) + 1 AS total FROM `mytable` I've run a query with calculation DENSE_RANK. I got an right answer on version, '10.6.10-MariaDB-1:10.6.10+maria~ubu2004' I've used the Podman/Windows WSL The versions i've tested 10.6.10 : toatl is 114 10.6.15 : total is 0 lts(maybe v11) : total is 0 I've tested with windows version MariaDB (MSI installer), too. only works well in 10.6.10 failed on 10.6.15
            alice Alice Sherepa added a comment -

            i5on9i Could you please open a separate bug report for that? Please add also CREATE TABLE... and also I guess there is a lack of a bracket here:"(DENSE_RANK() OVER (ORDER BY `mytable`.`id` ASC) + 1 AS total" - what do you mean there?

            alice Alice Sherepa added a comment - i5on9i Could you please open a separate bug report for that? Please add also CREATE TABLE... and also I guess there is a lack of a bracket here:"(DENSE_RANK() OVER (ORDER BY `mytable`.`id` ASC) + 1 AS total" - what do you mean there?
            i5on9i namh added a comment -

            Could you please open a separate bug report for that?
            --> OK, I will try

            I guess there is a lack of a bracket here:"(DENSE_RANK() OVER (ORDER BY `mytable`.`id` ASC) + 1 AS total"
            --> sorry, I made a mistake during copy it from our project's code.
            the correct query is like the below.

            (DENSE_RANK () OVER (ORDER BY `mytable`.`id` ASC) + 1) AS total

            i5on9i namh added a comment - Could you please open a separate bug report for that? --> OK, I will try I guess there is a lack of a bracket here:"(DENSE_RANK() OVER (ORDER BY `mytable`.`id` ASC) + 1 AS total" --> sorry, I made a mistake during copy it from our project's code. the correct query is like the below. (DENSE_RANK () OVER (ORDER BY `mytable`.`id` ASC) + 1) AS total
            i5on9i namh added a comment -

            @alice.sherepa

            I've tested again, in my computer again(windows + podman)

            I tried to reproduce it, but it looks just works well.

            tested versions:

            • 10.6.10+maria~ubu2004
            • 10.6.14+maria~ubu2004
            • 10.6.15+maria~ubu2004
            • lts

            Sorry for bothering.

            My poor guess is that something was wrong with my windows PC because that was in pre-update state.

            take care.

            i5on9i namh added a comment - @alice.sherepa I've tested again, in my computer again(windows + podman) I tried to reproduce it, but it looks just works well. tested versions: 10.6.10+maria~ubu2004 10.6.14+maria~ubu2004 10.6.15+maria~ubu2004 lts Sorry for bothering. My poor guess is that something was wrong with my windows PC because that was in pre-update state. take care.
            alice Alice Sherepa added a comment -

            i5on9i I’m glad to hear that it works well!

            alice Alice Sherepa added a comment - i5on9i I’m glad to hear that it works well!

            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.