[MDEV-18373] DENSE_RANK is not calculated correctly Created: 2019-01-24  Updated: 2023-10-31  Resolved: 2019-05-04

Status: Closed
Project: MariaDB Server
Component/s: Optimizer - Window functions
Affects Version/s: 10.3.11, 10.2, 10.3, 10.4
Fix Version/s: 10.2.24, 10.3.15, 10.4.5

Type: Bug Priority: Major
Reporter: Yekaterina Assignee: Varun Gupta (Inactive)
Resolution: Fixed Votes: 0
Labels: None
Environment:

Linux 3.10.0-862.el7.x86_64 #1

Attachments: Text File dense_rank.txt    
Issue Links:
Duplicate
duplicates MDEV-15298 Wrong result with aggregation functio... Closed
Relates
relates to MDEV-15837 Assertion `item1->type() == Item::FIE... Closed

 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)



 Comments   
Comment by Alice Sherepa [ 2019-01-25 ]

Thanks a lot for the report! Reproducible on 10.2-10.4

create table t1 (a int, b int);
 
insert into t1 values (60, 1515),(60, 2000),(70, 2000),(55, 1600);
 
 
 
select dense_rank() over (order by (sum(a))) from t1 group by  b;
 
select dense_rank() over (order by (sum(a)+1)) from t1 group by  b;
 
 
 
select dense_rank() over (order by (sum(a)+1)), row_number() over (order by (sum(a)+1)), rank() over (order by (sum(a)+1))  from t1 group by  b; #server crashes, because of MDEV-15837


MariaDB [test]> select dense_rank() over (order by (sum(a))) from t1 group by  b;
 
+---------------------------------------+
 
| dense_rank() over (order by (sum(a))) |
 
+---------------------------------------+
 
|                                     2 |
 
|                                     1 |
 
|                                     3 |
 
+---------------------------------------+
 
3 rows in set (0.001 sec)
 
 
 
MariaDB [test]> select dense_rank() over (order by (sum(a)+1)) from t1 group by  b;
 
+-----------------------------------------+
 
| dense_rank() over (order by (sum(a)+1)) |
 
+-----------------------------------------+
 
|                                       1 |
 
|                                       1 |
 
|                                       1 |
 
+-----------------------------------------+
 
3 rows in set (0.001 sec)

Comment by Varun Gupta (Inactive) [ 2019-03-08 ]

Patch
http://lists.askmonty.org/pipermail/commits/2019-March/013514.html

Comment by Igor Babaev [ 2019-05-01 ]

Varun,
It's hard to see that the result set now is correct without values of b in the output. Please add b to the select lists.
Besides it looks like you have problem with alignment in your patch.

Comment by Varun Gupta (Inactive) [ 2019-05-03 ]

Patch (after addressing the above requests)
http://lists.askmonty.org/pipermail/commits/2019-May/013734.html

Comment by Igor Babaev [ 2019-05-04 ]

Ok to push into 10.2

Comment by namh [ 2023-10-05 ]

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

Comment by Alice Sherepa [ 2023-10-17 ]

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?

Comment by namh [ 2023-10-30 ]

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

Comment by namh [ 2023-10-30 ]

@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.

Comment by Alice Sherepa [ 2023-10-31 ]

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

Generated at Thu Feb 08 08:43:36 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.