Crash when applying INSERT to ill-formed view.

(1) By Yu Liang (LY1598773890) on 2023-03-08 21:47:49 [source]

The latest trunk version (fossil commit: ac7359b263, unreleased) and released version 3.41.0 of SQLite crash when executing the following query:

CREATE TABLE v0 (c1 INT);
CREATE VIEW view_2 (c1) AS SELECT CASE WHEN c1 COLLATE TRUE THEN TRUE ELSE TRUE END FROM v0;
INSERT INTO view_2 DEFAULT VALUES RETURNING *;

Crashing stack trace from released version 3.41.0 (fossil commit: 05941c2a04):

(gdb) bt
#0  0x0000aaaaaabffca0 in codeReturningTrigger (pParse=0xffffffffd0b0, pTrigger=0xaaaaaad762d0, pTab=0xaaaaaad7d520, regIn=0) at sqlite3.c:147631
#1  0x0000aaaaaac00f98 in sqlite3CodeRowTrigger (pParse=0xffffffffd0b0, pTrigger=0xaaaaaad762d0, op=127, pChanges=0x0, tr_tm=2, pTab=0xaaaaaad7d520, reg=0, orconf=11,
    ignoreJump=-1) at sqlite3.c:148051
#2  0x0000aaaaaabd9184 in sqlite3Insert (pParse=0xffffffffd0b0, pTabList=0xaaaaaad781f0, pSelect=0x0, pColumn=0x0, onError=11, pUpsert=0x0) at sqlite3.c:130070
#3  0x0000aaaaaac32e4c in yy_reduce (yypParser=0xffffffffc6c0, yyruleno=163, yyLookahead=1, yyLookaheadToken=..., pParse=0xffffffffd0b0) at sqlite3.c:170984
#4  0x0000aaaaaac36dc8 in sqlite3Parser (yyp=0xffffffffc6c0, yymajor=1, yyminor=...) at sqlite3.c:172030
#5  0x0000aaaaaac3a830 in sqlite3RunParser (pParse=0xffffffffd0b0, zSql=0xaaaaaad6810d ";") at sqlite3.c:173329
#6  0x0000aaaaaabe6f1c in sqlite3Prepare (db=0xaaaaaad68170, zSql=0xaaaaaad680e0 "INSERT INTO view_2 DEFAULT VALUES RETURNING *;", nBytes=-1, prepFlags=128,
    pReprepare=0x0, ppStmt=0xffffffffd348, pzTail=0xffffffffd350) at sqlite3.c:137928
#7  0x0000aaaaaabe71f0 in sqlite3LockAndPrepare (db=0xaaaaaad68170, zSql=0xaaaaaad680e0 "INSERT INTO view_2 DEFAULT VALUES RETURNING *;", nBytes=-1, prepFlags=128,
    pOld=0x0, ppStmt=0xffffffffd348, pzTail=0xffffffffd350) at sqlite3.c:138003
#8  0x0000aaaaaabe75e0 in sqlite3_prepare_v2 (db=0xaaaaaad68170, zSql=0xaaaaaad680e0 "INSERT INTO view_2 DEFAULT VALUES RETURNING *;", nBytes=-1, ppStmt=0xffffffffd348,
    pzTail=0xffffffffd350) at sqlite3.c:138089
#9  0x0000aaaaaaae92ec in shell_exec (pArg=0xffffffffdcf8, zSql=0xaaaaaad680e0 "INSERT INTO view_2 DEFAULT VALUES RETURNING *;", pzErrMsg=0xffffffffd3d0) at shell.c:19233
#10 0x0000aaaaaaafcfd4 in runOneSqlLine (p=0xffffffffdcf8, zSql=0xaaaaaad680e0 "INSERT INTO view_2 DEFAULT VALUES RETURNING *;", in=0xfffff7e3b8d0 <_IO_2_1_stdin_>,
    startline=3) at shell.c:26443
#11 0x0000aaaaaaafd6e8 in process_input (p=0xffffffffdcf8) at shell.c:26609
#12 0x0000aaaaaaaff704 in main (argc=1, argv=0xfffffffff368) at shell.c:27503

Bisecting result:

bisect complete
  1 BAD     2023-02-21 18:09:37 05941c2a04037fc3
  6 BAD     2023-01-28 05:09:26 7b168ee2af09f04b
  7 BAD     2023-01-14 19:09:26 eac135fd6a5dd220
 11 BAD     2023-01-13 19:29:46 0819a1869a39d54a
 12 BAD     2023-01-13 18:20:40 9b8dcd79050f9bfa
 13 BAD     2023-01-13 15:54:25 bd8fa10e59f58886 CURRENT
 10 GOOD    2023-01-12 19:28:34 c5d958eb942a8d6e
  9 GOOD    2023-01-11 16:25:55 f884224578e549c7
  8 GOOD    2023-01-05 14:41:18 a6251d72894f9c2e
  5 GOOD    2022-12-27 14:03:04 e3776796f55574f3
  4 GOOD    2022-12-02 17:52:52 b57e3c3db00a6bc6
  3 GOOD    2022-08-23 20:11:01 34b8ea31877ae8b4
  2 GOOD    2021-12-31 22:53:15 e654b57a9fc32021

From the PoC, the "COLLATE TRUE" is syntactically incorrect, so the expected behavior is to output a parsing syntax error.

(2) By Keith Medcalf (kmedcalf) on 2023-03-08 23:43:03 in reply to 1 [link] [source]

I should think it matters not what the view is. If an attempt is made to insert into a view and there is no INSTEAD OF INSERT trigger then an error that insertion into a view is not permitted should be thrown. No examination of the view itself is required.

Examination of the view may only be required if there is an INSTEAD OF INSERT trigger and then only as much inspection as is needed to determine the column names.

Mutatis mutandis UPDATE and DELETE as well.

(3.1) By Larry Brasfield (larrybr) on 2023-03-08 23:56:57 edited from 3.0 in reply to 1 [link] [source]

This bug was fixed earlier this hour. Thanks for your clear report.

That SQL should have failed at semantic analysis of the view definition. Now, it does.

(4) By Yu Liang (LY1598773890) on 2023-03-09 04:33:19 in reply to 3.1 [link] [source]

Thank you for the quick fix! The patch has been tested and the query execution correctly outputs the parse error now.