Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Not a Bug
-
10.6.4
-
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
- is caused by
-
MDEV-24089 support oracle syntax: rownum
-
- Closed
-
Activity
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, ...
The equality operator does seem to work when the ROWNUM() result is aliased inside a CTE or subquery.
Subquery:
-> ) t| a | b | row_num || 6 | 4 | 7 |CTE:
-> )| a | b | row_num | ROWNUM() || 6 | 4 | 7 | 1 |But an alias does not work with a flat query: