Repository navigation
[BUG] SQLite: INSERT OR IGNORE / ABORT / FAIL / ROLLBACK and UPDATE OR ... do not parse #2852
Description
SQLite accepts a conflict resolution algorithm after INSERT OR and UPDATE OR: ABORT, FAIL, IGNORE, REPLACE and ROLLBACK (lang_insert, lang_update, lang_conflict). Only INSERT OR REPLACE parses.
JSqlParser: current master (90ac3a7).
| Statement | Result |
|---|---|
INSERT OR REPLACE INTO t VALUES (1) |
parses |
INSERT OR IGNORE INTO t VALUES (1) |
Encountered: <K_INSERT> / "INSERT" |
INSERT OR ABORT INTO t VALUES (1) |
same |
INSERT OR FAIL INTO t VALUES (1) |
same |
INSERT OR ROLLBACK INTO t VALUES (1) |
same |
UPDATE OR IGNORE t SET a = 1 |
Encountered: <K_UPDATE> / "UPDATE" |
UPDATE OR REPLACE t SET a = 1 |
same |
INSERT OR IGNORE is the usual SQLite form of "insert if it does not exist yet", so it is common in application code. The grammar handles only <K_INSERT> <K_OR> <K_REPLACE> as an Upsert (UpsertType.INSERT_OR_REPLACE).
Related in the same dialect: CREATE TABLE t (a INT) WITHOUT ROWID fails with Expected OIDS but found ROWID, and ... STRICT fails with Encountered: <K_STRICT>.
Activity
feat: parse INSERT OR ABORT, FAIL, IGNORE and ROLLBACK (SQLite)
feat: parse INSERT OR ABORT, FAIL, IGNORE and ROLLBACK (SQLite)
feat: parse INSERT OR ABORT, FAIL, IGNORE and ROLLBACK (SQLite)
feat: parse INSERT OR ABORT, FAIL, IGNORE and ROLLBACK (SQLite) (#2853)
feat: parse UPDATE OR conflict algorithms in the SQLite dialect
feat: parse UPDATE OR conflict algorithms in the SQLite dialect (#2885)
feat: parse SQLite WITHOUT ROWID and STRICT table options
feat: parse SQLite WITHOUT ROWID and STRICT table options (#2903)
manticore-projects commentedon Nov 13, 2026
I assume this has been completed and can be closed? Please verify and confirm. Thank you!
ItsDeadlyProgrammer commentedon Nov 13, 2026
Verified on
masterbccb1c8— built the grammar withjjtree+javacc8.1.4.1944, compiledsrc/main/javaagainst it, and parsed each statement withCCJSqlParserandwithDialect(Dialect.SQLITE).Everything reported here parses and round-trips now:
INSERT OR IGNORE INTO t VALUES (1)INSERT OR ABORT INTO t VALUES (1)INSERT OR FAIL INTO t VALUES (1)INSERT OR ROLLBACK INTO t VALUES (1)INSERT OR REPLACE INTO t VALUES (1)UPDATE OR IGNORE t SET a = 1conflictAlgorithm=IGNOREUPDATE OR ABORT / FAIL / ROLLBACK / REPLACE t SET a = 1CREATE TABLE t (a INT) WITHOUT ROWIDTableOption.Kind.WITHOUT_ROWIDCREATE TABLE t (a INT) STRICTTableOption.Kind.STRICTCREATE TABLE t (a INT) STRICT, WITHOUT ROWIDandWITHOUT ROWID, STRICTAlso checked green: lowercase forms, multi-row
VALUES,INSERT OR IGNORE ... SELECT,UPDATE OR ... FROM/WHERE/ORDER BY/LIMIT ... RETURNING, and no collateral damage to otherdialects (PostgreSQL
WITHOUT OIDSandWITH (...), MySQLENGINE=,SELECT * FROM t strictstays an alias).
The dialect guard behaves as intended: without a dialect,
INSERT OR IGNORE INTO t VALUES (1)fails with
INSERT OR IGNORE needs Dialect.SQLITEinstead of the old generic syntax error, whileINSERT OR REPLACEstill parses everywhere.So the reported cases are fixed — this can be closed for the conflict algorithms and the table
options. Two things worth a line in the release notes or a follow-up:
Dialect.SQLITEis a new constant on a public enum, so downstream code with an exhaustiveswitch (dialect)and nodefaultbranch stops compiling. It is also master-only:jsqlparser 5.4has noSQLITEconstant and none of the statements above parse there.The body of the OR-form is still parsed by
Upsert(), not byInsert(), so it does not getthe INSERT clauses the plain form supports. These all still fail with
Dialect.SQLITEonmaster:INSERT OR IGNORE INTO t (a) VALUES (1) ON CONFLICT DO NOTHINGfailsINSERT INTO t (a) VALUES (1) ON CONFLICT DO NOTHINGparsesINSERT OR IGNORE INTO t (a) VALUES (1) RETURNING afailsINSERT INTO t (a) VALUES (1) RETURNING aparsesINSERT OR IGNORE INTO t DEFAULT VALUESfailsINSERT INTO t DEFAULT VALUESparsesINSERT OR IGNORE INTO t AS alias VALUES (1)failsINSERT INTO t AS alias VALUES (1)parsesREPLACE INTO t (a) VALUES (1) RETURNING a,... ON CONFLICT ...andREPLACE ... DEFAULT VALUESfail the same way, so this is pre-existing on that path and not a regression of feat: parse INSERT OR ABORT, FAIL, IGNORE and ROLLBACK (SQLite) #2853,feat: parse UPDATE OR conflict algorithms in the SQLite dialect #2885 or feat: parse SQLite WITHOUT ROWID and STRICT table options #2903 — but
INSERT OR IGNORE ... RETURNINGand bareREPLACE ... RETURNINGarecommon application code, and
SQLiteTestonly pins the three shapes from this issue, so aclosure based on those tests would not show the gap.
Suggested follow-up (separate issue): give
InsertanINSERT OR <algorithm>prefix field,rendered in
Insert.appendInsertPrefixandInsertDeParser, i.e.(INSERT | REPLACE) * [OR algorithm] INTO ..., and parse the SQLite keywords there. The OR-form then inheritsON CONFLICT,RETURNING,DEFAULT VALUES, table aliases and row aliases for free, whileUpsert/UpsertDeParserkeep servingREPLACEandUPSERTunchanged.Filed as a follow-up issue with the full reproduction matrix: #2923,
feat: parse SQLite INSERT OR <algorithm> with the INSERT clauses
feat: parse SQLite INSERT OR <algorithm> with the INSERT clauses
ItsDeadlyProgrammer commentedon Nov 13, 2026
Verified on master bccb1c8: every statement in the report parses and round-trips — INSERT OR ABORT/FAIL/IGNORE/ROLLBACK, UPDATE OR , WITHOUT ROWID and STRICT (#2853, #2885, #2903). Closing as completed.
The one thing this issue surfaced but does not cover — the OR-form not inheriting ON CONFLICT, RETURNING, DEFAULT VALUES and table aliases — is tracked separately in #2923, with #2924 open for it. Thanks for the nudge.
feat: parse SQLite INSERT OR <algorithm> with the INSERT clauses (#2924)