Re: Anyone have more information about metalink note 362585.1 or bug 4622729?

From: Wolfgang Breitling <breitliw_at_centrexcc.com>
Date: Wed, 10 Jan 2007 09:08:57 -0700
Message-Id: <20070110160914.ADF4B594B64@turing.freelists.org>

Aside from the potential bug, I think this is a case of sloppy/faulty coding based on the (unfortunate) fact that Oracle supposedly treats an empty string as a NULL - most of the time. Apparently not in this case. If you change the first sql in the test case to select a from t1 where a not in (select b from t2 where b is not null and c is null)

then it returns the correct result.

At 08:29 AM 1/10/2007, Charles Schultz wrote:
>We applied the patch for <http://10.2.0.2>10.2.0.2, but we are still
>seeing the same problem as documented in the metalink note. We have
>already raised the issue with Oracle Support, but since I expect a
>less-than-complete answer from them about the internal details, I
>was hoping to pose a few questions here.
>
>Given the scenario in the note, exactly why are the filter
>conditions different for the two select statements? Which filter,
>specifically, is "NULL IS NOT NULL"? And why would cursor_sharing
>play a role in the filter operation? The more details the better, I
>am eager to learn. *grin*
>
>create table t1 (a number not null);
>create table t2 (b number,c varchar2(1));
>insert into t1 values (1);
>commit;
>
>set autotrace on explain
>
>SQL> select a from t1 where a not in (select b from t2 where b is
>not null and c ='');
>no rows selected
>
>SQL> select a from t1 where a not in (select b from t2 where b is
>not null and c =' ');
>
>A
>----------
>1

Regards

Wolfgang Breitling
Centrex Consulting Corporation
www.centrexcc.com

---

This email has been scanned by the MessageLabs Email Security System. For more information please visit http://www.messagelabs.com/email

---

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Jan 10 2007 - 10:08:57 CST