[MDEV-26329] Equality operator does not work with ROWNUM() function after first row Created: 2021-08-09  Updated: 2021-08-10  Resolved: 2021-08-10

Status: Closed
Project: MariaDB Server
Component/s: N/A
Affects Version/s: 10.6.4
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Geoff Montee (Inactive) Assignee: Ralf Gebhardt
Resolution: Not a Bug Votes: 0
Labels: None

Issue Links:
Problem/Incident
is caused by MDEV-24089 support oracle syntax: rownum Closed
Relates

 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;



 Comments   
Comment by Geoff Montee (Inactive) [ 2021-08-09 ]

The equality operator does seem to work when the ROWNUM() result is aliased inside a CTE or subquery.

Subquery:

MariaDB [test]> SELECT * FROM (
 
    ->   SELECT *, ROWNUM() AS row_num
 
    ->   FROM test_values
 
    ->   ORDER BY a
 
    -> ) t
 
    -> WHERE row_num = 7;
 
+------+------+---------+
 
| a    | b    | row_num |
 
+------+------+---------+
 
|    6 |    4 |       7 |
 
+------+------+---------+

CTE:

MariaDB [test]> WITH t AS (
 
    ->   SELECT *, ROWNUM() AS row_num
 
    ->   FROM test_values
 
    -> )
 
    -> SELECT *, ROWNUM()
 
    -> FROM t
 
    -> WHERE row_num=7;
 
+------+------+---------+----------+
 
| a    | b    | row_num | ROWNUM() |
 
+------+------+---------+----------+
 
|    6 |    4 |       7 |        1 |
 
+------+------+---------+----------+
 
1 row in set (0.001 sec)

But an alias does not work with a flat query:

MariaDB [test]> SELECT *, ROWNUM() AS row_num
 
    -> FROM test_values 
    -> WHERE row_num = 7;
 
ERROR 1054 (42S22): Unknown column 'row_num' in 'where clause'

Comment by Ralf Gebhardt [ 2021-08-10 ]

Hi GeoffMontee, you cannot use the equality operator (=) with ROWNUM, an exception is 1. Also Conditions testing for ROWNUM values greater than a positive integer will always have an empty result set. This is "as designed" and also works this way with Oracle. Your working example generate a result set first, which is then evaluated. This is fine.
rownum will create a number based on the result set. So the first result set will always be 1, so will not fit your =7 condition. Then the next row would used, rownum would be 1 again, ...

Generated at Thu Feb 08 09:44:28 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.