Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2008
    Posts
    14

    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.

    Code:
    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
    Code:
    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
    Code:
    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
    Code:
    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..

    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.

    thanks
    Last edited by @ziggy; 01-21-08 at 18:52.

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    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:
    Quote 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:
    Code:
    SQL> select rownum, rowid, dname from dept;
    
        ROWNUM ROWID              DNAME
    ---------- ------------------ --------------
             1 AAAcl2AAEAAAP48AAA ACCOUNTING
             2 AAAcl2AAEAAAP48AAB RESEARCH
             3 AAAcl2AAEAAAP48AAC SALES
             4 AAAcl2AAEAAAP48AAD OPERATIONS
    
    SQL>
    As of your query: this is what you've done:
    Code:
    SQL> with test as
      2  (select 'ABC' fc, dbms_random.value sc from dual
      3   union
      4   select 'ABC' fc, dbms_random.value sc from dual
      5  )
      6   select rownum, fc, sc
      7   from test
      8   order by fc;
    
        ROWNUM FC          SC
    ---------- --- ----------
             2 ABC .351163464
             1 ABC .182577448
    
    SQL>
    Or, even more simplified, this:
    Code:
    SQL> select fc from (select 'ABC' fc from dual
      2                  union all
      3                  select 'ABC' from dual
      4                 )
      5  order by fc;
    
    FC
    ---
    ABC
    ABC
    
    SQL>
    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.

  3. #3
    Join Date
    Jan 2008
    Posts
    14
    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?

  4. #4
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Your original query is returning the lowest price first, how about

    Code:
    select rownum, START_DATE,product_id,end_date,High,Price
    FROM 
    (SELECT START_DATE,product_id,end_date,High,Price
    from prod_price
    where product_id='productA'
    and start_date<=to_date('01OCT07','DDMONRR')
    and nvl(end_Date,sysdate+1) >= sysdate
    order by start_date desc,PRICE ASC);
    Last edited by beilstwh; 01-22-08 at 15:31.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  5. #5
    Join Date
    Feb 2008
    Posts
    1
    Hi ,
    Visit the following site for reason in difference in output

    http://forums.oracle.com/forums/thre...hreadID=373236

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •