Skip to content

Question: how closely should unquoted UPDATE SET / INSERT VALUES expressions match SQLite? (options 1/2/3) #525

Open

Description

@CastleSoft

Question

How closely should unquoted values in UPDATE … SET and INSERT … VALUES match SQLite? Pick option 1, 2 or 3 below. Every option changes stored values for some unquoted input, so the choice also needs a CHANGELOG note.

Background

In SQLite, every value in an UPDATE SET list or an INSERT VALUES list is an expression:

  • 2 * 3 is evaluated to 6, and a TEXT column then stores it as the text '6'.
  • An unquoted word is a column name: SET name = active fails with "no such column".
  • Text has to be quoted: 'active'.

SharpCoreDB reads some unquoted values as literal text instead. The table below was measured on the current PR stack (#518–#524):

Statement SQLite SharpCoreDB today
UPDATE t SET n = 2 * 3 (INTEGER) 6 6
UPDATE t SET name = 2 * 3 (TEXT) '6' '2 * 3'
UPDATE t SET d = 2024-01-15 (DATETIME) 2008 (2024 − 1 − 15) 15 Jan 2024
UPDATE t SET name = active error: no such column 'active'
INSERT … VALUES (…, 2 * 3, …) into TEXT '6' '2 * 3'
INSERT … VALUES (…, 2 * 3, …) into INTEGER 6 error: "Invalid value '2 * 3'"
INSERT … VALUES (…, upper('abc'), …) 'ABC' "upper('abc')" (the text of the call)

Why UPDATE behaves this way

Before #518, every SET value was parsed as a literal of the column's type, and a TEXT column accepted the raw text.

#518 added expression support (SET n = n + 1, SET name = upper(name)). It treats a value as an expression only when it reads a column or calls a function. Constant arithmetic keeps the old literal reading, and is evaluated only when that reading fails. This was deliberate, to avoid silently changing what existing apps store:

Unquoted input Stored today Would become
SET d = 2024-01-15 a date 2008
SET sku = 10-4 '10-4' 6
SET status = active 'active' an error

Why INSERT is a separate gap

INSERT … VALUES never evaluates expressions:

  • a function call is stored as its own text;
  • arithmetic into a numeric column fails.

That is wider than constant arithmetic, and it is a SQLite incompatibility whichever option is chosen.

Callers that pass parameters (EF Core, linq2db, Dapper, ADO.NET with @p) are not affected by any of this. Only hand-written SQL with unquoted values is.

Options

Option 1 — Match SQLite fully

Every unquoted value in UPDATE SET and INSERT VALUES is evaluated as an expression, and an unknown bare word is rejected as an unknown column.

  • ✅ Exact SQLite behaviour; satisfies the "never less than SQLite" policy without exceptions.
  • ⚠️ Riskiest for existing hand-written SQL:
    • unquoted dates become arithmetic;
    • unquoted words start failing;
    • TEXT columns store the evaluated value.

Option 2 — Match SQLite wherever SQLite would succeed (recommended)

Anything that parses as an expression is evaluated, as in SQLite: arithmetic, function calls, CASE, comparisons.

The lenient literal reading stays only where SQLite would raise an error anyway: a bare word that names no column (SET status = active).

  • ✅ Every statement SQLite accepts gives SQLite's result. Unquoted dates and constant arithmetic match SQLite.
  • ✅ Statements SQLite rejects keep working as before (an extension, which the policy allows).
  • ⚠️ Unquoted dates and arithmetic-looking text change meaning, as in option 1.

Option 3 — Minimal change

UPDATE stays as it is. INSERT VALUES only gains evaluation of values that read a column or call a function, mirroring UPDATE today.

  • ✅ Lowest risk; existing stored values don't change.
  • ⚠️ The TEXT constant-arithmetic difference stays ('2 * 3' instead of '6'), and INSERT arithmetic into numeric columns keeps failing. This remains a SQLite incompatibility.

Recommendation

Option 2. It closes the incompatibility for everything SQLite accepts, doesn't touch parameterized callers, and keeps backward compatibility only where SQLite itself would fail.

What happens after a decision

  • The chosen option is implemented as a PR on top of the current stack.
  • Tests cover every row of the table above.
  • The CHANGELOG notes the inputs whose stored value changes.

Please reply with 1, 2 or 3, plus any exceptions you want kept (for example, unquoted dates in DATETIME columns).

Activity

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Labels

    questionFurther information is requested

    Projects

    No projects

      Milestone

      No milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions