Details

      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

            Activity

              People

              • Assignee:
                cvicentiu Vicentiu Ciorbaru
                Reporter:
                wfong Will Fong
              • Votes:
                0 Vote for this issue
                Watchers:
                5 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: