Re: Finding a SQL bug in 9.2.0.8

From: DA Morgan <damorgan_at_psoug.org>
Date: Fri, 05 Sep 2008 19:53:07 -0700
Message-ID: <1220669586.88165@bubbleator.drizzle.com>

fitzjarrell_at_cox.net wrote:

> On Sep 5, 9:29 am, DA Morgan <damor..._at_psoug.org> wrote:

>> DG problem wrote:

>>> On a HP-UX machine running Oracle Enterprize 9208
>>> 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?

>> 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
>> damor..._at_x.washington.edu (replace x with u to respond)
>> 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

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
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Received on Fri Sep 05 2008 - 21:53:07 CDT