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

XMLWordPrintable

#### Details

• Bug
• Status: Closed
• 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; ```

#### People Ralf Gebhardt Geoff Montee (Inactive)