Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Anyone have more information about metalink note 362585.1 or bug 4622729?

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

From: Charles Schultz <sacrophyte_at_gmail.com>
Date: Wed, 10 Jan 2007 10:19:51 -0600
Message-ID: <7b8774110701100819s7e9c9a74s6260fb48fbf257ee@mail.gmail.com>


Actually, the metalink note does not say anything about cursor_sharing - Oracle Support discovered that alter cursor_sharing from SIMILAR to FORCE or EXACT corrects the below problem. But why?!?

I can understand that c='' is always going to be false, but my logic stops there. If you say "not in" against an empty set, would not the result be always a full set?

On 1/10/07, Nigel Thomas <nigel_cl_thomas_at_yahoo.com> wrote:
>
> Charles
>
>
> 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
>
>
>
>
> Controversially, Oracle treats the empty string '' as NULL
>
> $ sqlplus xxxxxxxx
>
> SQL*Plus: Release 9.2.0.7.0 - Production on Wed Jan 10 16:04:55 2007
> Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
>
> Connected to:
> Oracle9i Enterprise Edition Release 9.2.0.7.0 - 64bit Production
> JServer Release 9.2.0.7.0 - Production
>
> SQL> select count(*) from dual where '' = ''
> 2 /
> COUNT(*)
> ----------
> 0
>
> (and I get the same behaviour on XE 10.2.0.1)
>
> So when you say WHERE C = '' you have a predicate that can *never* be true
> (because if you set C to '', it is actually null).
>
> I don't see why cursor sharing would be relevant (sorry, can't read the
> Metalink note, as the Support ID here is a top secret not available to
> actual Oracle cognoscenti...)
>
> Cheers Nigel
>
>
>
>

-- 
Charles Schultz

--
http://www.freelists.org/webpage/oracle-l
Received on Wed Jan 10 2007 - 10:19:51 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US