Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-12743

Wrong results with ROW_NUMBER OVER

    XMLWordPrintable

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

              cvicentiu Vicențiu Ciorbaru
              wfong Will Fong
              Votes:
              0 Vote for this issue
              Watchers:
              5 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.