An embarrassing " feature" in Oracle RDBMS (part 2)

In my previous post "An embarrassing " feature" in Oracle RDBMS (part 1)" I showed a way of  breaking the referential integrity of the database. The fact that this is possible in Oracle made me think about the consequences of such a feature.

If the metadata says that a FK-constraint is valid and enabled, then my understanding of how the optimizer works tells me that it potentially can pick an execution plan, which actually returns the wrong result.  And by creating an example, which did exactly that,  I hoped to get  Oracle to see the seriousness of this "feature".  (It's an common knowledge that if it something Oracle Corp. is serious about, it's to get their flagship product to return the correct result. Wrong results can never be anything but a bug.)

In December  I attended  "Joze Senegacnik" presentation "Query Transformations"at UKOUG-2007. In this presentation he had an example of how the Query Transformer sometimes eliminates a join. By using that idea it's possible to create a situation where Oracle would return the wrong result.  Check these queries:

SELECT child_tab.*
  FROM parent_tab,
       child_tab
WHERE parent_tab.parent_id= child_tab.parent_id;

SELECT *
  FROM child_tab
 WHERE EXISTS (SELECT 'X'
          FROM parent_tab
         WHERE parent_tab.parent_id = child_tab.parent_id);

The both happen to have the same execution plan:

------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 4 | 104 | 3 (0)| 00:00:01 | | 1 | TABLE ACCESS FULL| CHILD_TAB | 4 | 104 | 3 (0)| 00:00:01 | -------------------------------------------------------------------------------

The query transformer is aware of the valid FK constraint and uses this knowledge to eliminate access to parent_tab. Such a  plan would of course cause wrong result if the state of the FK constraint was wrong. So this proves that that this "feature" is nothing but a bug (see below):

dwdemo@SID> select * from child_tab; CHILD_ID PARENT_ID ---------- ---------- 1 1 2 1 3 2 4 2 dwdemo@SID> select * from parent_tab; PARENT_ID ---------- 2 dwdemo@SID> SELECT * 2 FROM child_tab 3 WHERE EXISTS (SELECT 'X' 4 FROM parent_tab 5 WHERE parent_tab.parent_id = child_tab.parent_id); CHILD_ID PARENT_ID ---------- ---------- 1 1 2 1 3 2 4 2

WRONG RESULT!

So I guess Oracle has to correct this feature. But what could be the options:

1. Don't allow DML on parent tables when FK:s on child tables are set to DISABLE VALIDATE.



2. Always re-validate FK constraints when they go from DISABLE VALIDATE to ENABLE VALIDATE.



3. Don't even allow FK:s to go to the state DISABLE VALIDATE.

Anyone with a better idea?