RE: Questions re subqueries

Toon,

Thanks for the update. But I have refined my test and I think the problems I am seeing have something to do with TDE. The mothraids table is the local database with TDE enabled and the ssn column encrypted. The db link bill_test points to a remote database with TDE enabled and spbpers_ssn encrypted. The db link bill_testx points to a remote database which is a copy of the database bill_test points to with the exception that TDE is not enabled thus spbpers_ssn is not encrypted. It appears that when joining an encrypted column with an encrypted column correct results are returned however when joining an encrypted column with a non-encrypted column incorrect results are returned. An additional piece of information is that in the local database _unnest_subquery=TRUE, the default but in both remote databases _unnest_subquery=FALSE at the direction of the application vendor. I have not had an opportunity to set it = TRUE in the remote databases and rerun the test. So, I am confused. Again, if you have any thoughts on this I would greatly appreciate them. Thanks.

SQL> select count (*) from mothra.mothraids m where m.ssn = 'xxxxxxxxx';

  COUNT(*)

         1

Incorrect...

SQL> select count (*) from mothra.mothraids m where m.ssn = '565643494'   2 and exists
  3 (select 'x' from spbpers_at_bill_test a where a.spbpers_ssn = m.ssn);

  COUNT(*)

         1

SQL> select count (*) from mothra.mothraids m where m.ssn = 'xxxxxxxxx'   2 and not exists
  3 (select 'x' from spbpers_at_bill_test a where a.spbpers_ssn = m.ssn);

  COUNT(*)

         0

Correct...

SQL> select count (*) from mothra.mothraids m where m.ssn = 'xxxxxxxxx'   2 and exists
  3 (select 'x' from spbpers_at_bill_testx a where a.spbpers_ssn = m.ssn);

  COUNT(*)

         1

SQL> select count (*) from mothra.mothraids m where m.ssn = 'xxxxxxxxx'   2 and not exists
  3 (select 'x' from spbpers_at_bill_testx a where a.spbpers_ssn = m.ssn);

  COUNT(*)

         1

Bill Wagman
Univ. of California at Davis
IET Campus Data Center
wjwagman_at_ucdavis.edu
(530) 754-6208
-----Original Message-----

From: Koppelaars, Toon [mailto:T.Koppelaars_at_centraal.boekhuis.nl] Sent: Wednesday, June 20, 2007 12:57 AM
To: William Wagman
Cc: oracle-l_at_freelists.org
Subject: RE: Questions re subqueries

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

http://www.freelists.org/webpage/oracle-l Received on Wed Jun 20 2007 - 18:09:47 CDT