[#MDEV-12743] Wrong results with ROW

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.

[MDEV-12743] Wrong results with ROW_NUMBER OVER Created: 2017-05-09 Updated: 2020-08-25 Resolved: 2017-05-09
Status:	Closed
Project:	MariaDB Server
Component/s:	Optimizer - Window functions
Affects Version/s:	10.2.5
Fix Version/s:	10.2.5

[MDEV-12743] Wrong results with ROW_NUMBER OVER Created: 2017-05-09 Updated: 2020-08-25 Resolved: 2017-05-09