Weird behavior using a deferrable constraint (bug?)

martin.tanler

Reproducable on the latest express version.

The following steps lead to different results of one and the same query (during transaction, uncommited data):

1. Create Table containing a deferrable (INITIALLY IMMEDIATE) constraint
2. Set the constraint deferred
3. Insert 2 rows violating the constraint (no commit)
4. Execute query X resulting in 0 rows (instead of 2)
5. Delete all rows from table
6. Drop deferrable ( INITIALLY IMMEDIATE) constraint from table (and index)
7. Add deferrable ( INITIALLY DEFERRABLE) constraint to table
8. Insert 2 rows violating the constraint (no commit)
9. Execute query X resulting in 2 rows (hurray!!!)

Why does initially immediate/deferrable lead to different results of one and the same query?

-- 1.Create Table containing a deferrable (INITIALLY IMMEDIATE) constraint
create table TDEFERED_UK_BUG(
    PK  NUMBER(3) NOT NULL
    , A NUMBER(3) NOT NULL
    , B NUMBER(3) NOT NULL
    , constraint UK_DEFERED_UK_BUG UNIQUE(A,B) DEFERRABLE INITIALLY IMMEDIATE USING INDEX (CREATE INDEX UK_DEFERED_UK_BUG ON TDEFERED_UK_BUG(A,B) )
);


-- 2. Set the constraint deferred
set constraint UK_DEFERED_UK_BUG deferred;
-- 3. Insert 2 rows violating the constraint
insert into TDEFERED_UK_BUG(PK, A,B) values(0, 1, 2);
insert into TDEFERED_UK_BUG(PK, A,B) values(1, 1, 2);


-- 4. Execute query X resulting in 0 rows (instead of 2)
select root.* from TDEFERED_UK_BUG root 
    where root.A = 1 and exists(
        select * from TDEFERED_UK_BUG ex where 
            ex.PK != root.PK
            and ex.A = root.A
            and ex.B = root.B
    );
-- What is going on there? No Results?


-- 5. Delete all rows from table
delete from TDEFERED_UK_BUG;
-- 6. Drop deferrable ( INITIALLY IMMEDIATE) constraint from table
alter table TDEFERED_UK_BUG drop constraint UK_DEFERED_UK_BUG;
drop index UK_DEFERED_UK_BUG;
-- 7. Add deferrable ( INITIALLY DEFERRABLE) constraint to table
alter table TDEFERED_UK_BUG add (
    constraint UK_DEFERED_UK_BUG UNIQUE(A,B) DEFERRABLE INITIALLY DEFERRED USING INDEX (CREATE INDEX UK_DEFERED_UK_BUG ON TDEFERED_UK_BUG(A,B) )
    );
-- 8. Insert 2 rows violating the constraint
insert into TDEFERED_UK_BUG(PK, A,B) values(0, 1, 2);
insert into TDEFERED_UK_BUG(PK, A,B) values(1, 1, 2);


-- 9. Execute query X resulting in 2 rows (hurray!!!)
select root.* from TDEFERED_UK_BUG root 
    where root.A = 1 and exists(
        select * from TDEFERED_UK_BUG ex where 
            ex.PK != root.PK
            and ex.A = root.A
            and ex.B = root.B
    );
    

mathguy

I don't know if it can be called a bug; is the correctness of results promised (guaranteed) when reading data in the middle of a transaction with uncommitted changes? (As a mathematician, not an IT professional, I would object to such a promise; I just don't know what the Oracle documentation says about this, if anything.)

First, I can confirm that I see the same behavior, and the result is wrong in the first case. It can also be reproduced in a smaller example.

While playing around with it, I noticed another thing I wasn't aware of. Since SQL Developer will run queries on uncommitted data, but it will not run an Explain Plan on it (at least in my version of sqldev, 18.1), I took a look at explain plans in SQL*Plus - which, then, is a different session (even if the user is the same). Of course, that SQL*Plus session can't see the uncommitted inserts. But, oddly, the Explain Plan shows the number of rows in indexes (for the uncommitted table rows!)

What is happening is that the Optimizer takes advantage of the index to access the data; the structure of the query allows it to do so.

And, for reasons I do not know, in the first case the two inserts result in a single index entry (as reported by the Explain Plan in the SQL*Plus session). In your second example, at the same point in the process, the index contains entries for both rows. This fully explains the output from the two queries (in the first case, since the index points to a single row in the table, the condition on PK in the semi-join results in no rows).

So, the question reduces to, why does the index behave differently between the two situations you described. The question has really nothing to do with the specific queries you run - it's really about what gets inserted in the index during the transaction.

The index is created with the CREATE TABLE statement, and the statements are indeed different in the two cases. Changing the constraint to DEFERRED in the first case comes after the index is already created. However, I wasn't able to figure out WHAT is different. They look identical in USER_INDEXES and show the same statistics when analyzed.

It may be interesting to understand this difference, but as I said, I don't think it should be considered a bug - based on theoretical principles. Whether Oracle would consider it a bug, I wouldn't know.

martin.tanler

Hi mahguy. Thank you for confirming my findings. The correctness of results in the middle of a transaction should be given. See https://docs.oracle.com/cd/E25054_01/server.1111/e25789/consist.htm (Transaction-Level Read Consistency).

It seems that the inserted (flushed but not commited) data is NOT considered during execution of the query described at 4. As you found, that is the case due to the index. Though changing the query at 4. syntactically (but not semantically) results in 2 rows (as expected):

select root.* from TDEFERED_UK_BUG root 
    where root.A = 1 and exists(
        select * from TDEFERED_UK_BUG ex where 
            ex.PK != root.PK
            and ex.A = root.A
            and ex.B = root.B
			group by ex.PK, ex.A, ex.B having count(*) > 0

I am looking forward to your response. Would you still not consider that to be a bug?

Jonathan Lewis

This looks like a variation on a bug I described about 3 years ago: https://jonathanlewis.wordpress.com/2017/08/14/join-elimination-bug/

The problem is that Oracle is using "join elimination" when it shouldn't. You can confirm this in two ways - first simply run the test after executing

alter session set "_optimizer_join_elimination_enabled" = false;

Alternatively just before each of the critical queries execute

set autotrace on explain

and then afterwards

set autotrace off

You'll see that when you get the correct answer you probably get some sort of semi-join, but for the incorrect answer only one occurrence of the table appears in the execution plan.

Regards

Jonathan Lewis

mathguy

https://community.oracle.com/tech/developers/discussion/comment/16787609#Comment_16787609

What the documentation talks about in the context of transaction-level read consistency is the fact that a query you run in the middle of a transaction is able to see the uncommitted changes. It doesn't technically say that the result of the query will be correct (although perhaps one may argue that correctness is implied). In any case, if Oracle - rightly or wrongly - promises that the result should be correct, then yes, it's a bug.

Let me explain my objection to this whole thing. Constraints are a core part of relational theory. Committed transactions take a table (or a schema, or an entire db) from one consistent state to another. In the middle of a transaction, when constraints are not enforced yet, the table (or schema, etc.) is not in a consistent state (meaning: it doesn't obey all the requirements for the specified relation), and therefore any query based on that data is by definition garbage.

This goes hand-in-hand with another thing I don't like (as a mathematician). Constraints should always be "deferred" - the table should satisfy all the constraints only at the commit point, not after each individual DML statement. I understand there are good practical considerations for what Oracle does, but logically there should be no enforcement of constraints between commit points.

So, back to your question - "bug" or "not bug" depends only on what Oracle promises, not on the purely mathematical ideal. If they promise correct results based on the data as-is (even though it violates constraints at that point), then it's a bug.

martin.tanler

Well, I see some kind of agreement that this is indeed a bug. Unfortunately I coud not figure out how to report it without having a customer account (is there some kind of public bug tracking system out there?). Can any of you guys (John or mathguy) report that bug instead of me in case I am not elegable?

mathguy

I certainly can't - I am not a paying customer of Oracle. I don't work in the field - I am learning Oracle SQL and PL/SQL simply for my amusement, so I am allowed to use the product for free, but I don't have access to support. Same boat as you, perhaps!