Unexpected results in query with rownum condition I have found old query in application that behaves unexpectally after Oracle update. I have found out that problematic part of this query is condition: 

rownum &gt;= 1


When I apply this condition, resultset contains 0 rows, otherwise it returns 1 row (which is same in older version of Oracle).

If I change condition to rownum = 1 it returns one row. It also returns one row for AND(rownum &gt; 1 OR rownum = 1) and oddly for AND(rownum &gt;= 1 OR rownum &gt;= 1)

How is it possible?

EDIT, added SQL:

SELECT DISTINCT
    *
FROM
  NI_TABLE ni
LEFT JOIN NI_TO_BOPER nibo
ON
  ni.ni_id     = nibo.ni_id
AND ni.company = nibo.company
LEFT JOIN CHOPER co
ON
  nibo.operation_id = co.operation_id
AND nibo.company    = co.company
WHERE
  (
    ni.IDENTITY_NUMBER  = '123456789'
  OR co.IDENTITY_NUMBER = '123456789'
  )
AND NI.company   ='K'
AND rownum &gt;= 1
AND 200 &gt;= rownum
AND ni.NI_TYPE   ='PRIV'
ORDER BY
  ni.identityNo;


Note I haven't created this code, so I changed names for security reasons.
The pseudocolumn rownum is assigned AFTER all the result rows from the query are retrieved, and so the correct way using rownum to get say 10 rows, is to use &lt;= as pointed out in the comments.  The rownum can never be greater than the number of rows returned.  Example:

SYS@ecs03&gt; select table_name from dba_tables where rownum &lt;= 10;

TABLE_NAME
--------------------------------
CCOL$
FILE$
CLU$
SEG$
ICOL$
OBJ$
TAB$
CON$
UNDO$
TS$

10 rows selected.

SYS@ecs03&gt;


And better yet as Kevin pointed out, use FETCH FIRST *n* ROWS clause, as you can add additional parameters to get deterministic results. 


  For consistent results, specify the order_by_clause to ensure a deterministic sort order.


That old code may have worked years ago, but not in the last several years. I got bit on an upgrade from Oracle 9i to 10c when we assumed that group by implied ORDER BY by the same columns in the group by clause.  Wrong!