sqloracletoad

Oracle SQL: CTE Query gives different results in TOAD, depending on how executed

I have what appears to be a rather straight-forward CTE query:

with cte1 as 
( 
    Select row_number () over (
         order by id1 desc
         )rn,
         T.*         
from   schema1.table1 T
)
select * from cte1
WHERE (rn < 11)  
ORDER BY rn, id1 desc
;

However, it gives different results when executed, using the "TOAD" database tool.

If I use the execute button ("play"), it gives what I think are the correct results, including a row I inserted recently.

If I use the "Execute as Script" button, it gives a different result, omitting the recently inserted row.

For security reasons, I've changed details such as schema name, table name, and column name. Also, I can't provide the actual data used.

Is there a valid reason why one gets different results, depending on how the query is run?

To me, this is deeply troubling, and I would find it a "bug", not a "feature".

NOTE: Per the accepted answer, the problem was in the (unshown) insert script [not committing the insert], NOT with the CTE code or with TOAD.

Solution

  • Make sure you COMMIT your data.

    If you do not COMMIT then only the session where the the data was created can see the uncommitted data and other sessions, even those belonging to the same user, will never see that uncommitted data.

    For example, the behaviour can be explained: