[sqlite] Update unique column

[Marc-Andre Gosselin]

Hi,

        I discovered a behavior in SQLite 2.8.16 that doesn't conform to the SQL
standard, here's an example :

CREATE TABLE tbUpdateUnique (a INTEGER PRIMARY KEY, b INTEGER UNIQUE, c
VARCHAR(100));
INSERT INTO tbUpdateUnique VALUES('', 1, "Test 1");
INSERT INTO tbUpdateUnique VALUES('', 2, "Test 2");
INSERT INTO tbUpdateUnique VALUES('', 3, "Test 3");

        Now when I try the following update, I get a constraint error :

UPDATE tbUpdateUnique SET b = b + 1 WHERE b >= 2;

        In the SQL Standard and NIST SQL test suite they say than an update 
should
be considered atomic, and verify unique constraints only after the operation has
updated all rows. From what I experienced with SQLite, constraints are verified
after each row has been updated, resulting in a constraint error. I also tried
these with no success :

BEGIN TRANSACTION;
UPDATE tbUpdateUnique SET b = b + 1 WHERE b >= 2;
COMMIT TRANSACTION;

and

UPDATE tbUpdateUnique SET b = b + 1 WHERE a IN (SELECT a FROM tbUpdateUnique
WHERE b >= 2 ORDER BY b DESC);

        This is the content of the NIST test suite file dml027.sql :

----------------START--------------
-- MODULE DML027

-- SQL Test Suite, V6.0, Interactive SQL, dml027.sql
-- 59-byte ID
-- TEd Version #

-- AUTHORIZATION HU

   SELECT USER FROM HU.ECCO;
-- RERUN if USER value does not match preceding AUTHORIZATION comment

-- date_time print

-- TEST:0124 UPDATE UNIQUE column (key = key + 1) interim conflict!

-- setup
     UPDATE UPUNIQ
          SET NUMKEY = NUMKEY + 1;
-- PASS:0124 If 6 rows updated?

      SELECT COUNT(*),SUM(NUMKEY)
           FROM UPUNIQ;
-- PASS:0124 If count = 6 and SUM(NUMKEY) = 30?

-- restore
     ROLLBACK WORK;

-- END TEST >>> 0124 <<< END TEST
-- ********************************************************

-- TEST:0125 UPDATE UNIQUE column (key = key + 1) no interim conflit!

-- setup
     UPDATE UPUNIQ
          SET NUMKEY = NUMKEY + 1
          WHERE NUMKEY >= 4;
-- PASS:0125 If 3 rows are updated?

      SELECT COUNT(*),SUM(NUMKEY)
           FROM UPUNIQ;
-- PASS:0125 If count = 6 and SUM(NUMKEY) = 27?

-- restore
     ROLLBACK WORK;

-- END TEST >>> 0125 <<< END TEST
-- *************************************************////END-OF-MODULE
----------------END--------------

        I would like to know if this will be corrected or if it's too time
consuming to even bother. I would like to keep my column unique but I can
manage without if I need to.

Best regards,

Marc-Andre Gosselin
[EMAIL PROTECTED]