Finding a SQL bug in 9.2.0.8
DG problem
This query (has been simplified)
select a,b
from t1
where (a,b) not in (
-- select * from (
select a,b
from t1
where a in (1,2)
-- )
);
returns a different number of rows to this query
select a,b
from t1
where (a,b) not in (
select * from (
select a,b
from t1
where a in (1,2)
)
);
The last query returns the correct number of rows.
The sub query has a complex select part which includes
Case When ... in (8,10) and Lag(...,1,null) over (order by ..., ...)
= ...
This seems like a bug to me, but has anyone got any tips on finding
the bug in metalink?
ora...@msn.com
You really need to set up a repeatable test case that anyone could run
(table ddl, sample data) using a query as similar to the actual
production code as possible. Having such a test case would then allow
you to submit an SR with Oracle regarding this issue, although since
9.2.0.8 is now in extended support I wouldn't expect much more than an
'upgrade to at least 10.2.0.3' directive from Oracle Support. Also,
having such a test case would allow those of us using 10.2.0.x and
11.1 releases to test and verify these versions are not affected.
Given what little you've posted I doubt you, or anyone else, will have
much success searching MetaLink for this issue.
David Fitzjarrell
DA Morgan
SQL> create table t1 (
2 a number,
3 b number);
Table created.
SQL> insert into t1 values (1,1);
1 row created.
SQL> insert into t1 values (1,2);
1 row created.
SQL> insert into t1 values (2,1);
1 row created.
SQL> insert into t1 values (2,2);
1 row created.
SQL> insert into t1 values (3,3);
1 row created.
SQL> select a,b
2 from t1
3 where (a,b) not in (
4 -- select * from (
5 select a,b
6 from t1
7 where a in (1,2)
8 -- )
9 );
A B
---------- ----------
3 3
SQL> select a,b
2 from t1
3 where (a,b) not in (
4 select * from (
5 select a,b
6 from t1
7 where a in (1,2)
8 )
9 );
A B
---------- ----------
3 3
SQL>
If the problem exists there is a simple solution ... move to a
currently supported version of the product.
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damo...@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
ora...@msn.com
> Puget Sound Oracle Users Groupwww.psoug.org- Hide quoted text -
>
> - Show quoted text -
A nice example, but I expect it doesn't reflect the reality of th OP's
situation, judging by the code snippet he did provide:
Case When ... in (8,10) and Lag(...,1,null) over (order by ..., ...)
I think a better representation of the actual query, including table
ddl and sample data, would provide a better platform from which to
'launch' advice.
My two cents.
David Fitzjarrell
Michel Cadot
"DG problem" <skat...@gmail.com> a écrit dans le message de news:
1cdd426f-cb03-41e5...@l43g2000hsh.googlegroups.com...
Post a simple test case that reproduces the problem.
Regards
Michel
joel garry
My crystal balls appear to be swollen today, and they say you have a
situation where b is null that is behaving different than you expect
when compared in a not-IN, or perhaps the out-of-window default of
null is doing that. If you want to break my balls, follow the
suggestions of a test case, and see if your actual plans have any
clues.
Of course, I have no idea what I'm talking about with non-relational
analytic functions. It's just my balls talking.
jg
--
@home.com is bogus.
Cox overloads poles: http://www.signonsandiego.com/uniontrib/20080905/news_1n5puc.html
DA Morgan
I agree but if the OP isn't going to post the actual SQL then he/she
can not expect us to comment on what was withheld from view.
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damo...@x.washington.edu (replace x with u to respond)
DG problem
Hi All,
Firstly, thanks for your time.
I realise that to get this fixed will require several hours of time to
simplify and then isolate the problem so that it can be reproduced.
This is not really worth it for me given that there is a simple work
around which is the second example.
I was hoping, on the off chance, that someone may have experienced a
similar problem and thus could point me to the metalink bug. I find
searching for SQL bugs quite difficult due to the large amount of
noise due to the large amount of common keywords, eg, SELECT, ORDER
BY, LAG and so on.
Thanks once again.