SQLite Forum

Query triggers Segmentation Fault
Login
The segfault is due to recent, unreleased changes.  Specifically, the
[removal of "rowid" from VIEWs][1].  So, you shouldn't be able to segfault
an official release version of SQLite using the example above.

[1]: src:/timeline?c=a2ddb89b206c1387

However, this example does point to a name resolution problem with
common table expressions.  In particular, the following SQL results
in an incorrect answer:

> ~~~~
CREATE TABLE t1(k);
CREATE TABLE log(k, cte_map, main_map);
CREATE TABLE map(k, v);
INSERT INTO map VALUES(1, 'main1'), (2, 'main2');
CREATE TRIGGER tr1 AFTER INSERT ON t1 BEGIN
  INSERT INTO log
      WITH map(k,v) AS (VALUES(1,'cte1'),(2,'cte2'))
      SELECT
        new.k,
        (SELECT v FROM map WHERE k=new.k),
        (SELECT v FROM main.map WHERE k=new.k);
END;
INSERT INTO t1 VALUES(1);
INSERT INTO t1 VALUES(2);
SELECT * FROM log;
~~~~

The output here should be:

~~~~
┌───┬─────────┬──────────┐
│ k │ cte_map │ main_map │
├───┼─────────┼──────────┤
│ 1 │ cte1    │ main1    │
│ 2 │ cte2    │ main2    │
└───┴─────────┴──────────┘
~~~~

But instead we get:

~~~~
┌───┬─────────┬──────────┐
│ k │ cte_map │ main_map │
├───┼─────────┼──────────┤
│ 1 │ cte1    │ cte1     │
│ 2 │ cte2    │ cte2     │
└───┴─────────┴──────────┘
~~~~

The problem is that "main.map" in the trigger is getting bound
to the common-table expression called "map" rather than to the
table called "map".  This name resolution problem has existed
ever since common-table expression support was added to SQLite
with version 3.8.3 (2014-02-03).

[Check-in 0f0959c6f95046e8][2] should resolve the segfault,
or at least the specific example you provide above.  But
consider that check-in to be an interim fix.  The deeper
problem of name resolution inside of common table expressions
will take a bit more work, and might not be fixed for another
day or two.

[2]: src:/timeline?c=0f0959c6f95046e8