Home » SQL & PL/SQL » SQL & PL/SQL » Weird behaviour of Rownum
Weird behaviour of Rownum [message #296710] Mon, 28 January 2008 16:52 Go to next message
dasgupta.amitava@gmail
Messages: 32
Registered: November 2007
Member
Hi,

Version : 10g
Platform: Unix
I am running a query simillar to this, to get top-500 rows:
select *
from (select *
from t
where join_date > (7200 + birth_date) --some logic involbing dates
order by 1)
where ROWNUM <= 500;

When I run the query I get 3 rows.
But when I run the same query with commenting out the rownum clause, I get 2 rows.
Actually the inner query gives 2 rows if ran separately. We moved to 10g last week and now I am getting this error. I am not sure that it has somthing to do with the version change, or this error was there from the begining. For anything please provide me with some more ideas of rownum behavior.



Thanks in advance
Amitava................

[Updated on: Mon, 28 January 2008 16:54]

Report message to a moderator

Re: Weird behaviour of Rownum [message #296714 is a reply to message #296710] Mon, 28 January 2008 18:16 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Quote:
I am running a query simillar to this, to get top-500 rows:

Then you are doing it wrong (sounds like my wife!) either you have posted an inaccurate copy of your query or you are working with the wrong code
Re: Weird behaviour of Rownum [message #296742 is a reply to message #296710] Mon, 28 January 2008 22:58 Go to previous messageGo to next message
mshrkshl
Messages: 247
Registered: September 2006
Location: New Delhi
Senior Member
Quote:
When I run the query I get 3 rows.
But when I run the same query with commenting out the rownum clause, I get 2 rows.


As I think one row has not included due to rownum condtion (where ROWNUM <= 500)
but you have written reverse.

Re: Weird behaviour of Rownum [message #296745 is a reply to message #296710] Mon, 28 January 2008 23:04 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Show the output seperately for Innerquery and Full query .

Thumbs Up
Rajuvan
Re: Weird behaviour of Rownum [message #296763 is a reply to message #296745] Tue, 29 January 2008 00:16 Go to previous messageGo to next message
Michel Cadot
Messages: 64098
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And format both query and output: read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code.
Use the "Preview Message" button to verify.

Regards
Michel
Re: Weird behaviour of Rownum [message #296934 is a reply to message #296714] Tue, 29 January 2008 09:29 Go to previous messageGo to next message
dasgupta.amitava@gmail
Messages: 32
Registered: November 2007
Member
Dear pablolee,
Quote:
Then you are doing it wrong (sounds like my wife!) either you have posted an inaccurate copy of your query or you are working with the wrong code

Could you please let me know why you feel that I am doing wrong. Surprised I query that I sent was a pseudocde, because the actual one is too big. So please tell me what wrong did you find in the code?? Why it will not give top-500 rows??????


Amitava.......

[Updated on: Tue, 29 January 2008 10:31] by Moderator

Report message to a moderator

Re: Weird behaviour of Rownum [message #296936 is a reply to message #296742] Tue, 29 January 2008 09:35 Go to previous messageGo to next message
dasgupta.amitava@gmail
Messages: 32
Registered: November 2007
Member
Dear mshrkshl,

As I think one row has not included due to rownum condtion (where ROWNUM <= 500)
but you have written reverse.


I have written exactly what is happing. I know what and how the rownum is supposed to work. If without the rownum cond. I get only 2 rows, then how come with the extra condition of rownum gets me 3 rows??? Where from this extra row comes from. I have tested another cond. where without rownum the query fetches 2, but with rownum it fetches 36 rows. It's totally crazy.....



[Updated on: Tue, 29 January 2008 09:38]

Report message to a moderator

Re: Weird behaviour of Rownum [message #296938 is a reply to message #296710] Tue, 29 January 2008 09:42 Go to previous messageGo to next message
dasgupta.amitava@gmail
Messages: 32
Registered: November 2007
Member
Dear All,

Instead of
Quote:
select *
from (select *
from t
where join_date > (7200 + birth_date) --some logic involbing dates
order by 1)
where ROWNUM <= 500;


if I rewrite the query as below it works fine:
Quote:
select *
from (select rownum as row_num, t.*
from t
where join_date > (7200 + birth_date) --some logic involbing dates
order by 1)
where row_num <= 500;



But I am still confused as what is wrong with the first query!! Confused Confused


Amitava..

Re: Weird behaviour of Rownum [message #296941 is a reply to message #296938] Tue, 29 January 2008 10:00 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Quote:
I query that I sent was a pseudocde, because the actual one is too big

Somehow I have a feeling the query you gave us and the actual query are running is different.

Regards

Raj
Re: Weird behaviour of Rownum [message #296942 is a reply to message #296938] Tue, 29 January 2008 10:01 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
As long as you refuse to show us the real query, with real output and you stick to translating your query in pseudocode, we will never be able to help you out.
Who knows, you might have made a mistake translating the query into what you THINK it resembles..
Re: Weird behaviour of Rownum [message #296949 is a reply to message #296942] Tue, 29 January 2008 10:52 Go to previous messageGo to next message
dasgupta.amitava@gmail
Messages: 32
Registered: November 2007
Member
Dear All,
I know it's hard to believe...so please see the attached file where there is the queries and the corresponding output.

Thanks
Amitava..........
Re: Weird behaviour of Rownum [message #296971 is a reply to message #296949] Tue, 29 January 2008 13:32 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
This is different from your first post.
The inner query now returns no rows (vs unknown with rownum clause) instead of 2 vs 3 as stated in your original post.
But I agree, this is strange behavior, which very much smells like a bug.

Do you get the same results from sqlplus?
Re: Weird behaviour of Rownum [message #296973 is a reply to message #296971] Tue, 29 January 2008 14:15 Go to previous messageGo to next message
dasgupta.amitava@gmail
Messages: 32
Registered: November 2007
Member
Dear Frank,
This is different from your first post.

I purposely made some changes in the code to keep it simple, with the "crazy behavior of rownum alive".

Do you get the same results from sqlplus? 

Yes I got the same result from the sqlplus.

And if I comment out the date condn., then seems working as expected.

Amitava..........

[Updated on: Tue, 29 January 2008 14:36]

Report message to a moderator

Re: Weird behaviour of Rownum [message #296986 is a reply to message #296949] Tue, 29 January 2008 17:32 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Quote:
And if I comment out the date condn., then seems working as expected.
I feel it should do something with the data. To start with what I will suggest is try harcoding the date value instead of using sysdate.
Also, I am not able to understand why do you add NULL ?
AND  ((WSUMM.SLA_Start_TS - sysdate)*24 +  (Decode(SLA_Override_User_NM, NULL, SLA_VAL, Null)) + 
           (Select Count(1)*24 From DOC_MGMT.SLA_HOLIDAY Where DATED Between WSUMM.SLA_Start_TS AND sysdate))  Between 0 and 2
In the decode statement it says if SLA_Override_user_nm is populated then add null to the value. I am not sure whether it is done deliberately or been missed.

Regards

Raj
Re: Weird behaviour of Rownum [message #296987 is a reply to message #296986] Tue, 29 January 2008 17:48 Go to previous messageGo to next message
dasgupta.amitava@gmail
Messages: 32
Registered: November 2007
Member
Dear Raj,
You are right. There are some bad data in the column "Received_Date", which is supposed to be a Date field. But since it is a varchar field, there is no restriction on the format of the data. And in the DB I find there are a lot of data with different format. So the issue is identified; but my problem is that I cannot understand why should the query behave like that??? It might have given some errors, like "invalid month" or so...But without giving any errors it comes up eith errorenous results. To my more suprize I saw that if I comment the order by clause in the inner qry, the query is working absolutely fine. I am totally confused Confused Sad Mad by the behaviour of this query..........

And the decode stmt is perfectly ok...No problem with that.
Anyway thanks to you and all of the member who tried to help me.

Thanks
Amitava...........
Re: Weird behaviour of Rownum [message #296992 is a reply to message #296987] Tue, 29 January 2008 19:53 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Post the Explain Plan. I think I've heard of a case where Oracle mistakenly pushes a ROWNUM predicate into a complex view.

Ross Leishman
Re: Weird behaviour of Rownum [message #297187 is a reply to message #296992] Wed, 30 January 2008 09:31 Go to previous messageGo to next message
dasgupta.amitava@gmail
Messages: 32
Registered: November 2007
Member
Dear Ross Leishman,
I am sorry for giving the Explain Plan in this way, but currently I cannot get the direct access of the DB. So through SQL Developer I am running the Execution Plan and posting it. I can’t find how to export it to a text file.

Thanks Amitava
Re: Weird behaviour of Rownum [message #297191 is a reply to message #297187] Wed, 30 January 2008 09:40 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
I don't think anybody (atleast myself) won't open any .doc files. So please upload the same in a .txt file.

Thanks

Raj
Re: Weird behaviour of Rownum [message #297252 is a reply to message #297191] Wed, 30 January 2008 19:57 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
I normally don't, but took a chance. Here is how you should have posted it:
./fa/3839/0/
  • Attachment: plan.GIF
    (Size: 35.37KB, Downloaded 574 times)
Re: Weird behaviour of Rownum [message #297253 is a reply to message #297252] Wed, 30 January 2008 20:06 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
That plan is as I would have expected - the STOPKEY is applied last.

We are missing a critical piece of information. You have two queries: one with a ROWNUM predicate and one without. They are producing different results but we don't know which one is right.

We are looking for a bug in the ROWNUM case, but the bug might be with the non-ROWNUM case.

You say that the ROWNUM case produces more rows. Take one of the rows produced that is NOT reported in the non-ROWNUM case and check it. Should it be there? If so, the non-ROWNUM is WRONG.

This may not solve your problem, but it moves you one step further down the diagnostic process.

Ross Leishman
Re: Weird behaviour of Rownum [message #297256 is a reply to message #297253] Wed, 30 January 2008 21:31 Go to previous messageGo to next message
dasgupta.amitava@gmail
Messages: 32
Registered: November 2007
Member
Dear Ross Leishman,
Thanks for finding sometime to look into the plan.
The output for the query without the rownum(i.e. the one giving 2 rows) is the correct one. The one with rownum, is giving wrong results.

Here I would like to mention once more that, there are some bad data in the Table(a varchar column storing dates in multiple formats). I will try to clean up the data to test the same with some proper data. But my confusion still remains as I would expect Oracle to throw error if it finds bad data, rather than give wrong output as it is giving now.


Thanks
Amitava.................
Re: Weird behaviour of Rownum [message #297298 is a reply to message #297256] Thu, 31 January 2008 01:17 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
OK, so the ROWNUM query is returning the wrong results. Since it is returning MORE rows than the correct query, the must exist a row that it returns that is not supposed to be there.

Concentrate on this row. Why do you think it should not be there? There must be one of the WHERE predicates that it is supposed to fail. Which one? Show us the raw data and demonstrate why it should not display.

Ross Leishman
Re: Weird behaviour of Rownum [message #297444 is a reply to message #297298] Thu, 31 January 2008 11:35 Go to previous messageGo to next message
dasgupta.amitava@gmail
Messages: 32
Registered: November 2007
Member
Dear Ross Leishman,
Please see the attached txt file where there is a query(further simplified). I have also attached the output in the excel file for your easy understanding in the next post. This file will be enough for you to understand why the xtra rows should not come in the query.

Thanks
Amitava.......

[Updated on: Thu, 31 January 2008 11:36]

Report message to a moderator

Re: Weird behaviour of Rownum [message #297445 is a reply to message #297298] Thu, 31 January 2008 11:37 Go to previous messageGo to next message
dasgupta.amitava@gmail
Messages: 32
Registered: November 2007
Member
Dear Ross Leishman,
Please see the attached excel file where there is the output of the query of the prev post.


Thanks
Amitava.......
Re: Weird behaviour of Rownum [message #297464 is a reply to message #297445] Thu, 31 January 2008 19:43 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
I'm sorry if I gave the impression that I was going to do the work for you. This is very much NOT the case.

I am trying to show you a methodical process by which you can - if not solve the problem yourself - at least diagnose it properly so that you can raise an SR with Oracle.

Your SQL contains the following joins and predicates that will result in rows being filtered:

From doc_mgmt.worklist_summary WS
join DOC_MGMT.SLA_WORKLIST_SUMMARY WSUMM on WS.WL_ID = WSUMM.wl_id


Where WSUMM.WL_STAT_TYPE_NUM IN (3, 10, 14, 15, 32, 16)


AND (
      (WSUMM.SLA_Start_TS - sysdate)*24 +
      (Decode(SLA_Override_User_NM, NULL, SLA_VAL, Null)) + 
      (Select Count(1)*24 From DOC_MGMT.SLA_HOLIDAY Where DATED Between WSUMM.SLA_Start_TS AND sysdate)
    )  Between 0 and 2


The rows that the ROWNUM query returns are NOT SUPPOSED TO BE THERE, so they MUST fail one of these predicates. Which one are they supposed to fail?

Ross Leishman
Previous Topic: Privilege issue while trying to delete record
Next Topic: Need a small help in query
Goto Forum:
  


Current Time: Fri Dec 02 12:06:40 CST 2016

Total time taken to generate the page: 0.11447 seconds