Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.3.11, 10.2(EOL), 10.3(EOL), 10.4(EOL)
-
None
-
Linux 3.10.0-862.el7.x86_64 #1
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
- duplicates
-
MDEV-15298 Wrong result with aggregation function, used inside window function
- Closed
- relates to
-
MDEV-15837 Assertion `item1->type() == Item::FIELD_ITEM && item2->type() == Item::FIELD_ITEM' failed in compare_order_elements function
- Closed