SQLite
View Ticket
Not logged in
Ticket UUID: 4ef7e3cfcaa452e3978ca8ff7ad7adb7d25dd521
Title: Name resolution problem in sub-selects within triggers
Status: Closed Type: Code_Defect
Severity: Critical Priority: Immediate
Subsystem: Unknown Resolution: Fixed
Last Modified: 2014-03-04 13:03:50
Version Found In: trunk
User Comments:
nobody added on 2014-03-04 11:21:50: (text/x-fossil-wiki)
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:

<verbatim>
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.
</verbatim>

Or, if assert() is not enabled:

<verbatim>
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 
</verbatim>

drh added on 2014-03-04 13:03:50: (text/x-fossil-wiki)
Here is the test script from above with the command-line shell output
elided, for clarity of presentation and simplified copy/paste:

<blockquote><verbatim>
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
</verbatim></blockquote>

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.