| Why is the same query returning the output differently on different databases. [message #295218] |
Mon, 21 January 2008 15:37  |
ziggy25
Messages: 206
Registered: July 2005
|
Senior Member |
|
|
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
from prod_price
where product_id='productA'
and start_date<=to_date('01OCT07')
and (end_Date is null or end_date is >= sysdate)
order by start_date desc
The data on both tables is the same shown below
start_date product_id end_date High Price
01-APR-03 12:00:00 ProductA [null] 100 0.28
01-APR-03 12:00:00 ProductA [null] 101 0.35
If i run the above query against the data shown above this is what i get on both database;
Query run on a 10g database
rownum start_date product_id end_date High Price
1 01-APR-03 12:00:00 ProductA [null] 100 0.28
2 01-APR-03 12:00:00 ProductA [null] 101 0.35
Query run on a 9i database
rownum start_date product_id end_date High Price
2 01-APR-03 12:00:00 ProductA [null] 101 0.35
1 01-APR-03 12:00:00 ProductA [null] 100 0.28
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..
thanks
|
|
|
|
| Re: Why is the same query returning the output differently on different databases. [message #295221 is a reply to message #295218] |
Mon, 21 January 2008 15:45  |
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
|
Senior Member |
|
|
You still don't understand?
First, that is not the correct way to use a DATE. Ignorance of the proper use of the syntax will give you garbage results and you'll have to live with it.
Second, you don't have a second column to order by. Both rows have the same value so you will get your results in a random order. And that can change over time too.
If you code properly then you will not have these problems, but you have been told quite a few times before that unless you explicitly use correct syntax and ORDER BY clauses, you will get unexpected results.
It sounds harsh, but I really really didn't mean it to sound that way. Please forgive me.
|
|
|
|
| Re: Why is the same query returning the output differently on different databases. [message #295223 is a reply to message #295221] |
Mon, 21 January 2008 15:55 |
ziggy25
Messages: 206
Registered: July 2005
|
Senior Member |
|
|
| joy_division wrote on Mon, 21 January 2008 15:45 | You still don't understand?
First, that is not the correct way to use a DATE. Ignorance of the proper use of the syntax will give you garbage results and you'll have to live with it.
Second, you don't have a second column to order by. Both rows have the same value so you will get your results in a random order. And that can change over time too.
If you code properly then you will not have these problems, but you have been told quite a few times before that unless you explicitly use correct syntax and ORDER BY clauses, you will get unexpected results.
It sounds harsh, but I really really didn't mean it to sound that way. Please forgive me.
|
Lol i didnt actually right this query. It was written back in 2000 and its now my job to fix it.
What i am trying to find out is how did the query return the correct row (i.e. the one in the 9i database) for almost 8 years. The row with the price of 0.28 has always been the one returned first that is why the result has always been correct because that was the row that was expected.
As i mentioned earlier, i would have expected the rows returned to be different everytime but the thing is the same row was always returned first for 8yrs. It only changed after i upgraded the database to 10g. Now the wrong row is always being returned as the first row 
any ideas?
[Updated on: Mon, 21 January 2008 16:17] Report message to a moderator |
|
|
|
| Re: Why is the same query returning the output differently on different databases. [message #295243 is a reply to message #295223] |
Mon, 21 January 2008 20:21 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
The sequence of the rows returned (of equal ranking in the ORDER BY, or in the abscence of an ORDER BY) will depend on a number of things, but the most important factor is the physical layout on disk. If using an index, it could be the first row encountered in the index; if using a full table scan, it could be the first row encountered in the table.
All being equal (ie. the data doesn't change), this is unlikely to change over time unless you:
- Rearrange your data (as you did upgrading to 10g)
- Enable a new feature (such as parallel query)
- Oracle releases a new algorithm (such as HASH GROUP BY and HASH DISTINCT in 10.2)
Ross Leishman
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
