RE: Questions re subqueries

From: Koppelaars, Toon <T.Koppelaars_at_centraal.boekhuis.nl>
Date: Wed, 20 Jun 2007 09:56:40 +0200
Message-ID: <1247DEDC2684644C93827EB6FDF47F9A018EDB77@SRVEVS1.boekhuis.nl>

[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_at_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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of William Wagman Sent: woensdag 20 juni 2007 1:32
To: oracle-l_at_freelists.org
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_at_bill_testx

 Name                                      Null?    Type
 ----------------------------------------- --------


----------------------------

 C1                                                 VARCHAR2(2)

SQL> select count (*) from oracle.t1_at_bill_testx; COUNT(*)

4
SQL> select * from oracle.t1_at_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_at_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_at_bill_testx a where a.c1 = t1.c1);

C1
--

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...

How does TDE encrypt nulls and does this have any bearing on what I am seeing?
If I remove the nulls from each table the query behaves correctly, how does having nulls effect the results of my join and subquery?
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_at_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_at_ucdavis.edu
(530) 754-6208
--

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

--

http://www.freelists.org/webpage/oracle-l Received on Wed Jun 20 2007 - 02:56:40 CDT