Reproducable on the latest express version.
The following steps lead to different results of one and the same query (during transaction, uncommited data):
Create Table containing a deferrable (INITIALLY IMMEDIATE) constraint
Set the constraint deferred
Insert 2 rows violating the constraint (no commit)
Execute query X resulting in 0 rows (instead of 2)
Delete all rows from table
Drop deferrable ( INITIALLY IMMEDIATE) constraint from table (and index)
Add deferrable ( INITIALLY DEFERRABLE) constraint to table
Insert 2 rows violating the constraint (no commit)
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
);