Unanswered: Why is the same query returning a different output depending on database version?
I just upgraded my database from 9i to 10g and noticed something interesting.
As part of the testing i found out that one of the queries returned an incorrect result. I tested this query on the same table on a 9i database and a 10g database. The data in the two tables is the same.
select rownum, price
and (end_Date is null or end_date is >= sysdate)
order by start_date desc
At first i thought that this was just a random thing but everytime i re-run the query on either database the result is as shown above. How is this possible? Why is the 1st price row always returned as the first row in the 9i database but its returned in the second row in the 10g database?
I also thought that it might have something to do with the order in which the price rows are entered in the table but as you can see via the rownum colums the order is the same.
The only difference that i can think of is that on the 9i database the data was entered via oracle forms back in 2003 but in the 10g database the database was entered via a database link from the 9i database.
Can someone please explain how this is possible..
By the way i know that the query is wrong as its not selecting a specific row. I will fix that but i dont understand why the results were not random for the 8 years its been running on the 9i database.
I think you got it wrong; ROWNUM is NOT assigned to a row permanently. Read something more about this pseudocolumn in this Tom Kyte's article on Oracle Technology Network pages. This is just an excerpt:
Originally Posted by Tom Kyte
ROWNUM is a pseudocolumn (not a real column) that is available in a query. ROWNUM will be assigned the numbers 1, 2, 3, 4, ... N, where N is the number of rows in the set ROWNUM is used with. A ROWNUM value is not assigned permanently to a row (this is a common misconception). A row in a table does not have a number; you cannot ask for row 5 from a table—there is no such thing.
Also confusing to many people is when a ROWNUM value is actually assigned. A ROWNUM value is assigned to a row after it passes the predicate phase of the query but before the query does any sorting or aggregation. Also, a ROWNUM value is incremented only after it is assigned, which is why the following query will never return a row:
I believe you meant to say ROWID, not ROWNUM; but, it is you know knows exactly what you meant:
SQL> with test as
2 (select 'ABC' fc, dbms_random.value sc from dual
4 select 'ABC' fc, dbms_random.value sc from dual
6 select rownum, fc, sc
7 from test
8 order by fc;
ROWNUM FC SC
---------- --- ----------
2 ABC .351163464
1 ABC .182577448
Or, even more simplified, this:
SQL> select fc from (select 'ABC' fc from dual
2 union all
3 select 'ABC' from dual
5 order by fc;
My question is: which 'ABC' is the first one? You can't tell because they are THE SAME!
If you want to get an exact order, then use the correct ORDER BY clause (i.e. add another column(s) to it).
In my opinion, there's nothing wrong with either of your databases. If you don't care which 'start_date' comes first (and they are the same), Oracle doesn't either.
Your original query is returning the lowest price first, how about
select rownum, START_DATE,product_id,end_date,High,Price
and nvl(end_Date,sysdate+1) >= sysdate
order by start_date desc,PRICE ASC);
Last edited by beilstwh; 01-22-08 at 15:31.
You do not need a parachute to skydive. You only need a parachute to skydive twice.