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 explainSQL> select a from t1 where a not in (select b from t2 where b is not null and c ='');no rows selectedSQL> 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 Corporationwww.centrexcc.com
______________________________________________________________________ This email has been scanned by the MessageLabs Email Security System.For more information please visit http://www.messagelabs.com/email ______________________________________________________________________
