Re: Anyone have more information about metalink note 362585.1 or bug 4622729?
- From: Nigel Thomas <nigel_cl_thomas@xxxxxxxxx>
- To: sacrophyte@xxxxxxxxx, oracle-l <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 10 Jan 2007 08:13:37 -0800 (PST)
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
Other related posts:
