Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Not a Bug
-
10.2.5
-
None
Description
Hi,
When using ROW_NUMBER() OVER(), I expected the row numbers to be based on the result set, independent of the actual data (please let me know if my assumption is incorrect):
MariaDB [test]> SELECT ROW_NUMBER() OVER(), course, mark, name FROM student;
|
+---------------------+---------+------+---------+
|
| ROW_NUMBER() OVER() | course | mark | name |
|
+---------------------+---------+------+---------+
|
| 1 | Maths | 60 | Thulile |
|
| 2 | Maths | 60 | Pritha |
|
| 3 | Maths | 70 | Voitto |
|
| 4 | Biology | 60 | Bilal |
|
| 5 | Biology | 70 | Roger |
|
+---------------------+---------+------+---------+
|
5 rows in set (0.00 sec)
|
|
|
MariaDB [test]> SELECT ROW_NUMBER() OVER(), course, mark, name FROM student ORDER BY RAND();
|
+---------------------+---------+------+---------+
|
| ROW_NUMBER() OVER() | course | mark | name |
|
+---------------------+---------+------+---------+
|
| 1 | Maths | 70 | Voitto |
|
| 2 | Maths | 60 | Thulile |
|
| 3 | Biology | 60 | Bilal |
|
| 4 | Biology | 70 | Roger |
|
| 5 | Maths | 60 | Pritha |
|
+---------------------+---------+------+---------+
|
5 rows in set (0.00 sec)
|
|
|
MariaDB [test]>
|
However, I seem to be able to hit this edge state where future results don't show that:
MariaDB [test]> SELECT ROW_NUMBER() OVER(), course, mark, name FROM student;
|
+---------------------+---------+------+---------+
|
| ROW_NUMBER() OVER() | course | mark | name |
|
+---------------------+---------+------+---------+
|
| 3 | Maths | 60 | Thulile |
|
| 4 | Maths | 60 | Pritha |
|
| 5 | Maths | 70 | Voitto |
|
| 1 | Biology | 60 | Bilal |
|
| 2 | Biology | 70 | Roger |
|
+---------------------+---------+------+---------+
|
5 rows in set (0.01 sec)
|
|
|
MariaDB [test]>
|
Before this happened, I was running a similar test, but on a much larger table:
MariaDB [test]> SELECT COUNT(*) FROM clients;
|
+----------+
|
| COUNT(*) |
|
+----------+
|
| 3028479 |
|
+----------+
|
1 row in set (0.66 sec)
|
|
|
MariaDB [test]> SELECT ROW_NUMBER() OVER(), id, fname FROM clients ORDER BY RAND() LIMIT 10;
|
+---------------------+---------+-------+
|
| ROW_NUMBER() OVER() | id | fname |
|
+---------------------+---------+-------+
|
| 1 | 2506765 | jryox |
|
| 2 | 1568989 | vvhoq |
|
| 3 | 2316944 | arrac |
|
| 4 | 2524368 | xlqfo |
|
| 7 | 2704060 | adpzd |
|
| 6 | 2612860 | bqbaq |
|
| 5 | 1746460 | cchis |
|
| 8 | 2444152 | ryhvv |
|
| 9 | 1986334 | brbwt |
|
| 10 | 1176920 | yvrje |
|
+---------------------+---------+-------+
|
10 rows in set (22.37 sec)
|
|
|
MariaDB [test]>
|
And in the above example, 5,6,6 are in the wrong order.
Restarting MariaDB seems to help resolve this, but it will eventually return.
This was tested with 10.2.5 and bb-10.2-compatibility/build-14023.
Attachments
Issue Links
- relates to
-
MDEV-12779
Oracle/DB2 Compatibility Implicit Ordering for ROW_NUMBER OVER
-
- Closed
-