RE: Questions re subqueries

  • From: "Koppelaars, Toon" <T.Koppelaars@xxxxxxxxxxxxxxxxxxxx>
  • To: <wjwagman@xxxxxxxxxxx>
  • Date: Wed, 20 Jun 2007 09:56:40 +0200

[Lex would have loved this one...]

Bill,

I'm assuming you are confused over the fact that the result set of the
second query (with the NOT exists) holds the t1 tuple with the NULL
"value"?

The result of your second query is correct... (doesn't Oracle support
confirm this?)

Remember NULL = NULL evaluates to FALSE in SQL's 3-valued logic.
Therefore it is true that for the tuple in t1 that holds the NULL
"value", the predicate (in the where clause) "not exists (select 'x'
from oracle.t1@bill_testx a where a.c1 = t1.c1)" evaluates to TRUE. And
therefore it (the NULL-tuple) shows up in the result set (next to the
tuple with value 3 for c1).

This has nothing to do with database links, TDE, or whatever.

The fact that the alter session command changes the result set of the
2nd query to a incorrect result set, is obviously a bug which probably
*does* have to do with db-links, TDE, etc.

Toon


-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of William Wagman
Sent: woensdag 20 juni 2007 1:32
To: oracle-l@xxxxxxxxxxxxx
Subject: Questions re subqueries

Greetings,

I am struggling to get my head wrapped around an issue which is
confusing me greatly. I hope I can articulate this properly but given my
confusion I may muddy the waters here. I have two 10.2.0.3.0 databases,
the local one on Linux and a remote one on Solaris. I don't think
platform is involved but one never knows. On each database I have a
simple table with one column, varchar2 encrypted using TDE on the local
node, unencrypted (TDE not implemented) on the remote node. Each table
has one row with null values.

Local...
SQL> desc t1
 Name                                      Null?    Type
 ----------------------------------------- --------
----------------------------
 C1                                                 VARCHAR2(2) ENCRYPT
SQL> select count (*) from t1;
  COUNT(*)
----------
         4
SQL> select * from t1; 
C1
--
1
2
3

Remote...
SQL> desc oracle.t1@bill_testx
 Name                                      Null?    Type
 ----------------------------------------- --------
----------------------------
 C1                                                 VARCHAR2(2)
SQL> select count (*) from oracle.t1@bill_testx;
  COUNT(*)
----------
         4
SQL> select * from oracle.t1@bill_testx;
C1
--

1
2
5

Now I execute the following selects, both from the local database...

SQL> select * from t1 where exists
  2  (select 'x' from oracle.t1@bill_testx a where a.c1 = t1.c1);

C1
--
1
2

2 rows selected.

SQL> select * from t1 where not exists
  2  (select 'x' from oracle.t1@bill_testx a where a.c1 = t1.c1);

C1
--

3

2 rows selected.

The second one is not really what I would expect but this is where I
start to get confused and am hoping someone can shed some light on the
situation for me. The issues are...

1) How does TDE encrypt nulls and does this have any bearing on what I
am seeing?
2) If I remove the nulls from each table the query behaves correctly,
how does having nulls effect the results of my join and subquery?
3) I have been playing with setting _unnest_subquery=FALSE at Oracle's
suggestion (I have an SR open on this) and the results get even
stranger...
SQL> alter session set "_unnest_subquery"=false;
Session altered.
SQL> select * from t1 where not exists
  2  (select 'x' from oracle.t1@bill_testx a where a.c1 = t1.c1);

C1
--
1
2
3

4 rows selected.

What I don't understand is the relation between the variables, TDE, the
nulls and the _unnest_subquery parameter. I am quite confused. If anyone
can shed some light on this and explain to me what is going on I would
greatly appreciate it. 

Thank you.

Bill Wagman
Univ. of California at Davis
IET Campus Data Center
wjwagman@xxxxxxxxxxx
(530) 754-6208
--
//www.freelists.org/webpage/oracle-l




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


Other related posts: