SQLite

Within a trigger program, if match for a column reference does not match any column in the outermost context and the table name is not "new" or "old", name resolution may malfunction. If assert() is enabled, an assert() may fail:

sqlite>  CREATE TABLE x(a);
sqlite> CREATE TRIGGER t AFTER INSERT ON x BEGIN
   ...> SELECT * FROM x WHERE abc.a = 1;
     ...>   END;
sqlite>   INSERT INTO x VALUES('assert');
sqlite3: /home/dan/work/sqlite/trunk/src/expr.c:2869: sqlite3ExprCodeTarget: Assertion `pExpr->iTable==0 || pExpr->iTable==1' failed.

Or, if assert() is not enabled:

sqlite> CREATE TABLE w(a);
sqlite> CREATE TABLE x(a);
sqlite> CREATE TABLE y(a);
sqlite> CREATE TABLE z(a);
sqlite> INSERT INTO x(a) VALUES(5);
sqlite> INSERT INTO y(a) VALUES(10);
sqlite> CREATE TRIGGER t AFTER INSERT ON w BEGIN
   ...> INSERT INTO z SELECT (SELECT x.a + y.a FROM y) FROM x;
   ...> END;
sqlite> INSERT INTO w VALUES('incorrect');
sqlite> SELECT * FROM z;

sqlite> SELECT typeof(a) FROM z;
null
sqlite> -- should be an integer value 15 

Here is the test script from above with the command-line shell output elided, for clarity of presentation and simplified copy/paste:

CREATE TABLE w(a);
CREATE TABLE x(a);
CREATE TABLE y(a);
CREATE TABLE z(a);
INSERT INTO x(a) VALUES(5);
INSERT INTO y(a) VALUES(10);
CREATE TRIGGER t AFTER INSERT ON w BEGIN
  INSERT INTO z SELECT (SELECT x.a+y.a FROM y) FROM x;
END;
INSERT INTO w VALUES('incorrect');
SELECT a, typeof(a) FROM z; -- should be 15, integer

Bisecting shows that this problem was introduced with the cut-over of support for WITHOUT ROWID tables by check-in [c80e229dd9c12] on 2013-11-07. The problem appears in releases 3.8.2, 3.8.3, and 3.8.3.1.