SQLite

View Ticket
Login
2009-11-16
12:43 Fixed ticket [29ab7be9]: Hitting NOT NULL constraint does not roll back statement transaction plus 1 other change (artifact: ba930fde user: drh)
2009-09-09
11:47 Closed ticket [29ab7be9]. (artifact: 2e3bbe70 user: dan)
11:43
Add a test case to show that 29ab7be99f is fixed. (check-in: 135d656a user: dan tags: trunk)
10:31 New ticket [29ab7be9] Hitting NOT NULL constraint does not roll back statement transaction. (artifact: b37d2263 user: dan)

Ticket Hash: 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:
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}

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.


dan added on 2009-09-09 11:47:56:
Introduced here feccad8d0d (sqlite version 3.6.11). Fixed as part of trigger-related changes for 3.6.18.