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
-