SQL Challenge – Submit Update Statements, Updated Values are Reversed on Successful Commit

September 21, 2010

Is it possible to construct an Oracle Database test case such that:

1. An UPDATE statement is issued to change a DATE column value from NULL to the current date (SYSDATE) for two rows.
2. The data source is queried and the result set returned by the database shows that the DATE column is set to the current date for the two rows.
3. A COMMIT is successfully executed.
4. The data source is queried and the result set returned by the database shows that the DATE column is again NULL for the two rows.

—————–

For example, something like this:

CREATE TABLE T1 (
  C1 NUMBER,
  C2 DATE,
  C3 DATE,
  CONSTRAINT "CHECK_DATE" CHECK (
    NVL(C2,TO_DATE('01-JAN-2000','DD-MON-YYYY')) < NVL(C3,TO_DATE('01-JAN-2000','DD-MON-YYYY')))
    INITIALLY DEFERRED DEFERRABLE);

INSERT INTO T1 VALUES (1,NULL,TO_DATE('31-DEC-2000','DD-MON-YYYY'));
INSERT INTO T1 VALUES (2,NULL,TO_DATE('31-DEC-2000','DD-MON-YYYY'));
INSERT INTO T1 VALUES (3,NULL,TO_DATE('31-DEC-2000','DD-MON-YYYY'));

COMMIT;

There are now three rows in the table T1:

SELECT
  *
FROM
  T1;

 C1 C2        C3
--- --------- ---------
  1           31-DEC-00
  2           31-DEC-00
  3           31-DEC-00

We are able to update the column C2 to the current date (SYSDATE) with the following SQL statement:

UPDATE
  T1
SET
  C2=SYSDATE
WHERE
  C1 IN (1,2);

2 rows updated.

The update was successful, and we are able to confirm that the update was successful:

SELECT
  *
FROM
  T1;

C1 C2        C3
-- --------- ---------
 1 21-SEP-10 31-DEC-00
 2 21-SEP-10 31-DEC-00
 3           31-DEC-00

Now we will issue a COMMIT:

COMMIT;

Then query the table again to find the original value of column C2 was restored:

SELECT
  *
FROM
  T1;

 C1 C2        C3
--- --------- ---------
  1           31-DEC-00
  2           31-DEC-00
  3           31-DEC-00

The only catch is that “Commit complete.” must appear after the COMMIT, rather than something like the following:

COMMIT
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-02290: check constraint (TESTUSER.CHECK_DATE) violated

Be creative.  How is it possible to update a set of rows, select the rows to prove that the rows were updated, COMMIT, receive a confirmation that the COMMIT was successful, and then find that the original values were restored?  In case you are wondering, this OTN thread was the inspiration for this blog article (but don’t look at forum thread yet).

Is the data source that makes this possible a table, or is it something else?  Is there possibly a corrupt index involved?  Is there an ON COMMIT trigger involved (as far as I am aware, there is no such trigger available on Oracle Database).  VPD (virtual private database) tricks?  View tricks?  Magic?