SQLite
View Ticket
Not logged in
Ticket UUID: 29ab7be99f2cfe0b04466dd95b80ace87e9fc1b2
Title: Hitting NOT NULL constraint does not roll back statement transaction
Status: Fixed Type: Code_Defect
Severity: Important Priority: Immediate
Subsystem: Code_Generator Resolution: Fixed
Last Modified: 2009-11-16 12:43:18
Version Found In: 3.6.17
Description:
Running the following in 3.6.17:

<verbatim>
do_test haltifnull-1.1 {
  execsql {
    CREATE TABLE t1(a, b NOT NULL);
    CREATE TABLE t2(c, d);
    INSERT INTO t2 VALUES(3, 4);
    INSERT INTO t2 VALUES(5, NULL);
  }
} {}

do_test haltifnull-1.2 {
  catchsql {
    BEGIN;
    INSERT INTO t1 VALUES(1, 2);
    INSERT INTO t1 SELECT * FROM t2;
  }
} {1 {t1.b may not be NULL}}

do_test haltifnull-1.3 {
  execsql { SELECT * FROM t1 }
} {1 2}
</verbatim>

The SELECT in test case 1.3 returns two rows - "1 2" and "3 4". It should only return 1 - "1 2".

The problem is that the "INSERT INTO t1 SELECT * FROM t2" is not opening a
statement transaction. So change made prior to hitting the NOT NULL constraint
(inserting the "3 4" row) is not being rolled back as it should be.

<hr><i>dan added on 2009-09-09 11:47:56:</i><br>
Introduced here [/info/feccad8d0d|feccad8d0d] (sqlite version 3.6.11). Fixed as part of trigger-related changes for 3.6.18.