Home » SQL & PL/SQL » SQL & PL/SQL » Why is the same query returning the output differently on different databases.
Why is the same query returning the output differently on different databases. [message #295218] Mon, 21 January 2008 15:37 Go to next message
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 Go to previous messageGo to next message
joy_division
Messages: 4643
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 Go to previous messageGo to next message
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 Smile

any ideas? Smile

[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 Go to previous messageGo to next message
rleishman
Messages: 3724
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
Re: Why is the same query returning the output differently on different databases. [message #295269 is a reply to message #295243] Mon, 21 January 2008 23:26 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Oracle releases a new algorithm (such as HASH GROUP BY and HASH DISTINCT in 10.2)

Even if the name of the access path is the same, the code itself may change with versions... and it changes.

Regards
Michel
Re: Why is the same query returning the output differently on different databases. [message #295522 is a reply to message #295269] Tue, 22 January 2008 09:38 Go to previous messageGo to next message
ziggy25
Messages: 206
Registered: July 2005
Senior Member
Hi,

Is there a way i can tell which of the rows was entered first? Can i look at the start_Date column in a format that will show the exact difference in the timestamp?
Re: Why is the same query returning the output differently on different databases. [message #295528 is a reply to message #295522] Tue, 22 January 2008 10:11 Go to previous message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No there is no way unless you have a column that gives this information.

Regards
Michel
Previous Topic: update telephone type
Next Topic: which query will be having better performance
Goto Forum:
  


Current Time: Fri Dec 09 02:25:47 CST 2016

Total time taken to generate the page: 0.07562 seconds