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

Equality operator does not work with ROWNUM() function after first row

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Not a Bug
    • Affects Version/s: 10.6.4
    • Fix Version/s: N/A
    • Component/s: N/A
    • Labels:
      None

      Description

      The equality operator (=) does not work with the ROWNUM() function after the first row.

      Let's say that we have the following table:

      CREATE TABLE test_values (
        a INT,
        b INT
      );
       
      INSERT INTO test_values VALUES
        (1,3), (1,5), (8,2), (5,7), (5,6),
        (10,1), (6,4), (3,9), (3,9), (7,2),
        (7,5), (2,6), (9,10), (9,5), (4,8);
      

      The ROWNUM() function works with the following range query:

      SELECT *, ROWNUM() 
      FROM test_values 
      WHERE ROWNUM() <= 7 
      ORDER BY ROWNUM();
      

      Results:

      +------+------+----------+
      | a    | b    | ROWNUM() |
      +------+------+----------+
      |    1 |    3 |        1 |
      |    1 |    5 |        2 |
      |    8 |    2 |        3 |
      |    5 |    7 |        4 |
      |    5 |    6 |        5 |
      |   10 |    1 |        6 |
      |    6 |    4 |        7 |
      +------+------+----------+
      

      And it works with the following query that uses the equality operator (=):

      SELECT *, ROWNUM() 
      FROM test_values 
      WHERE ROWNUM() = 1;
      

      Results:

      +------+------+----------+
      | a    | b    | ROWNUM() |
      +------+------+----------+
      |    1 |    3 |        1 |
      +------+------+----------+
      1 row in set (0.000 sec)
      

      However, when you use the equality operator on any other row, the query returns an empty result set.

      This applies to the following queries:

      SELECT *, ROWNUM() 
      FROM test_values 
      WHERE ROWNUM() = 7;
      

      SELECT * FROM (
        SELECT *
        FROM test_values
      ) t
      WHERE ROWNUM() = 7;
      

      SELECT *, ROWNUM() FROM (
        SELECT *
        FROM test_values
      ) t
      WHERE ROWNUM() = 7;
      

      SELECT * FROM (
        SELECT *, ROWNUM()
        FROM test_values
      ) t
      WHERE ROWNUM() = 7;
      

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              ralf.gebhardt@mariadb.com Ralf Gebhardt
              Reporter:
              GeoffMontee Geoff Montee
              Votes:
              0 Vote for this issue
              Watchers:
              3 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.