unrelated to this, but while working on generated cols in turso i observed sqlite gives different output depending on whether xfer optimization is enabled or not, is there any place where i can read about all quirks regarding xfer optimization?

thanks

sqlite gives different output depending on whether xfer optimization is enabled or not,

Do you have a test case you can share?

Hi, yes - sorry for not including it earlier, i assumed it is the expected behaviour since there was a comment about this explicitly - https://github.com/sqlite/sqlite/blob/97901bbd69b61f614a050dfa877ab5ac6dc598f8/src/insert.c#L3129-L3153

    CREATE TABLE s(a, b INT GENERATED ALWAYS AS (a+1) STORED);
    CREATE TABLE d(a, b INT GENERATED ALWAYS AS (a+1) STORED);
    INSERT INTO s(a) VALUES(1);

    INSERT INTO d SELECT * FROM s;
    SELECT count(*), min(a), max(b) FROM d;

With the default build, this succeeds and returns:

  1|1|2

But if xfer is disabled, for example by compiling with -DSQLITE_OMIT_XFER_OPT, the same INSERT fails with:

  Parse error: table d has 1 columns but 2 values were supplied

The same failure can be observed without a custom build by making xfer ineligible:

  PRAGMA count_changes=ON;
  INSERT INTO d SELECT * FROM s;

i just wanted to know if there are any other quirks regarding xfer optimization i should be aware of, thanks.

Tentative documentation added at https://sqlite.org/draft/lang_insert.html#xferopt. Probably will be improved and enhanced in the coming days.

Maybe mention that the vacuum statement uses it internally.

another issue i suppose is with check constraints

CREATE TABLE s(a CHECK(a>0));
CREATE TABLE d(a CHECK(a>0));

PRAGMA ignore_check_constraints=ON;
INSERT INTO s VALUES(-1);
PRAGMA ignore_check_constraints=OFF;

INSERT INTO d SELECT * FROM s;
SELECT a FROM d;
PRAGMA integrity_check;

outputs

╭──────────────────────────────╮
│       integrity_check        │
╞══════════════════════════════╡
│ CHECK constraint failed in d │
│ CHECK constraint failed in s │
╰──────────────────────────────╯
SQLite-3.54 memory-> 

The database is already corrupt before you invoke the XFer optimization. GIGO. Not a bug.

sorry that was not a good example, i see the same behaviour with rowid

  CREATE TABLE s(a CHECK(rowid=1));
  CREATE TABLE d(a CHECK(rowid=1));

  INSERT INTO s(rowid,a) VALUES(1,'src-ok');
  INSERT INTO d(rowid,a) VALUES(1,'dst-ok');

  PRAGMA integrity_check;

  INSERT INTO d SELECT * FROM s;
  SELECT rowid,a FROM d ORDER BY rowid;
  PRAGMA integrity_check;

outputs:

──────────╮
│ integrity_check │
╞═════════════════╡
│ ok              │
╰─────────────────╯

│ rowid │   a    │
╞═══════╪════════╡
│     1 │ dst-ok │
│     2 │ src-ok │
╰───────┴────────╯

│       integrity_check        │
╞══════════════════════════════╡
│ CHECK constraint failed in d │
╰──────────────────────────────╯

This bug goes back to before version 3.5.1 (2007-10-04). I have checked in a fix for at 2026-05-11T16:58:05.251Z, which will appear in the 3.54 release. Because this bug has been in the code for 20 years and nobody has noticed and because it is not a vulnerability, I won't both cherrypicking the fix onto branch-3.53.

totally unrelated and really really small cli inconvenience - is this due to my shell?

when i copy paste a chunk of sql at once into cli

in 3.54(trunk) i see this series of sqlite> prompts at end at once.. in system sqlite 3.51 its clean and nice.

https://github.com/Pavan-Nambi/Pavan-Nambi/issues/1

sorry github issue cuz for some reason imagur isn't working.

That's due to the command-line editing library. Multi-line pastes work great if you build with editline, but goes goofy if you build with readline or linenoise. With readline, all lines are coalesced into a single line, which works great for raw SQL, but does not work if there are interspersed dot-commands. Linenoise only sees the first line of the paste and completely ignores the second and subsequent lines.

Somebody please offer suggestions on how I can fix that...

For readline, adding

set enable-bracketed-paste off

to ~/.inputrc makes multi-line paste behave more reasonably, though this might be terminal dependent.