SQLite

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.