## Description
closes https://github.com/tursodatabase/turso/issues/4142
<!--
Please include a summary of the changes and the related issue.
-->
## Motivation and context
compatibility, we were wrongly rewriting table qualified cols, also
added trigger.test to all.test and expect correct values in a test
<!--
Please include relevant motivation and context.
Link relevant issues here.
-->
## AI Disclosure
None
<!--
Please disclose if any LLM's were used in the creation of this PR and to
what extent,
to help maintainers properly review.
-->
Closes#4206
## Description
Does solve #4154, but I don't want to close it with this PR, because it
does not solve the Affinity issue.
We can only use an index to iterate over if the column collation in the
order by clause matches the index collation
<!--
Please include a summary of the changes and the related issue.
-->
## Motivation and context
Fix a bug in the optimizer
<!--
Please include relevant motivation and context.
Link relevant issues here.
-->
## Description of AI Usage
Used AI to write tests, fuzzers, and help me understand the optimizer
code.
Test prompt:
<details>
can you write tests in tcl that test that the correct collation sequence
is properly maintained.
```
CREATE TABLE "t1" ("c1" TEXT COLLATE RTRIM);
INSERT INTO "t1" VALUES (' ');
CREATE INDEX "i1" ON "t1" ("c1" COLLATE RTRIM DESC);
INSERT INTO "t1" VALUES (1025.1655084065987);
SELECT "c1", typeof(c1) FROM "t1" ORDER BY "c1" COLLATE BINARY DESC, rowid ASC;
```
this is an example of a query that returned incorrect results because of
this
</details>
<!--
Please disclose how AI was used to help create this PR. For example, you
can share prompts,
specific tools, or ways of working that you took advantage of. You can
also share whether the
creation of the PR was mainly driven by AI, or whether it was used for
assistance.
This is a good way of sharing knowledge to other contributors about how
we can work more efficiently with
AI tools. Note that the use of AI is encouraged, but the committer is
still fully responsible for understanding
and reviewing the output.
-->
Reviewed-by: Pere Diaz Bou <pere-altea@homail.com>
Reviewed-by: Jussi Saurio <jussi.saurio@gmail.com>
Closes#4248
- not all systems has cmath functions which we import from system
libraries
- let's use external implementation only in tests in order to eliminate
precision errors in the differential tests
- https://discord.com/channels/933071162680958986/933071163184283651/145
0476358005293147
Reviewed-by: Pere Diaz Bou <pere-altea@homail.com>
Closes#4246
## Description
This PR fixes the JSON serialization of infinite floating-point values,
as reported in #4196. Previously, converting `f64::INFINITY` or
`f64::NEG_INFINITY` to JSON produced a parse error because the `ryu`
crate formats infinity as `"inf"`, which is not valid JSON.
The implementation now correctly serializes infinite values to match
SQLite's behavior, which varies depending on the JSON function used:
- For `json_array()` and `json_object()` with float arguments:
```
turso> create table t(a);
turso> insert into t values (1e309);
turso> insert into t values (-1e309);
turso> select json_object('k', a) from t;
┌────────────────────────┐
│ json_object ('k', t.a) │
├────────────────────────┤
│ {"k":9.0e+999} │
├────────────────────────┤
│ {"k":-9.0e+999} │
└────────────────────────┘
turso> select json_array(a) from t;
┌──────────────────┐
│ json_array (t.a) │
├──────────────────┤
│ [9.0e+999] │
├──────────────────┤
│ [-9.0e+999] │
└──────────────────┘
```
- For `json()` with bare infinity or JSON5 text containing infinity:
```
turso> select json(a) from t;
┌────────────┐
│ json (t.a) │
├────────────┤
│ 9e999 │
├────────────┤
│ -9e999 │
└────────────┘
turso> SELECT json('{x: Infinity}');
┌────────────────────────┐
│ json ('{x: Infinity}') │
├────────────────────────┤
│ {"x":9e999} │
└────────────────────────┘
```
## Motivation and context
Fixes#4196.
Currently, Turso produces a parse error when serializing infinite
values:
```
turso> create table t(a);
turso> insert into t values (1e309);
turso> insert into t values (-1e309);
turso> select json_object('k', a) from t;
× Parse error: malformed JSON
turso> select json_array(a) from t;
× Parse error: malformed JSON
turso> select json(a) from t;
× Parse error: malformed JSON
turso> SELECT json('{x: Infinity}');
┌────────────────────────┐
│ json ('{x: Infinity}') │
├────────────────────────┤
│ {"x":9e999} │
└────────────────────────┘
```
SQLite handles this differently, infinite floating-point values are
serialized using the notations `9.0e+999` and `9e999`, depending on the
JSON function:
```
sqlite> create table t(a);
sqlite> insert into t values (1e309);
sqlite> insert into t values (-1e309);
sqlite> select json_object('k', a) from t;
{"k":9.0e+999}
{"k":-9.0e+999}
sqlite> select json_array(a) from t;
[9.0e+999]
[-9.0e+999]
sqlite> select json(a) from t;
9e999
-9e999
sqlite> SELECT json('{x: Infinity}');
{"x":9e999}
```
## AI Disclosure
This PR was developed with assistance from GitHub Copilot (Claude Sonnet
4.5). The AI helped identify the root cause and assisted in writing the
unit tests.
Reviewed-by: Pere Diaz Bou <pere-altea@homail.com>
Closes#4209
- for x = +-y the value of mod depends a lot on the calculation precision
- for example, atanh(tanh(-1.0)) should be evaluated to -1 but due to precision issues it can be approximately 1
- due to the, value of mod(atanh(tanh(-1.0)), 1.0) will be either approximately -1 or 0 - which is tremendous difference which affect the tests
## Description
This PR adds hash matching for equivalent integer and real values in
hash joins. This is achieved by ensuring that integer/real equivalents
(including signed zeros) share the same hash in internal bloom filters
and hash tables.
```
turso> CREATE TABLE IF NOT EXISTS t1 (a INTEGER, b INTEGER);
CREATE TABLE IF NOT EXISTS t2 (a INTEGER, c REAL);
INSERT INTO t1 (a, b) VALUES (1, NULL), (2, 10);
INSERT INTO t2 (a, c) VALUES (1, 10.0), (3, NULL);
SELECT * FROM t1 LEFT JOIN t2 ON t1.b = t2.c;
┌───┬────┬───┬──────┐
│ a │ b │ a │ c │
├───┼────┼───┼──────┤
│ 1 │ │ │ │
├───┼────┼───┼──────┤
│ 2 │ 10 │ 1 │ 10.0 │
└───┴────┴───┴──────┘
```
## Motivation and context
This change fixes the `LEFT JOIN` mismatch reported in #4147, where
joins on numerically equal `INTEGER` and `REAL` values failed in Turso
but succeeded in SQLite:
```
turso> CREATE TABLE IF NOT EXISTS t1 (a INTEGER, b INTEGER);
CREATE TABLE IF NOT EXISTS t2 (a INTEGER, c REAL);
INSERT INTO t1 (a, b) VALUES (1, NULL), (2, 10);
INSERT INTO t2 (a, c) VALUES (1, 10.0), (3, NULL);
SELECT * FROM t1 LEFT JOIN t2 ON t1.b = t2.c;
┌───┬────┬───┬───┐
│ a │ b │ a │ c │
├───┼────┼───┼───┤
│ 1 │ │ │ │
├───┼────┼───┼───┤
│ 2 │ 10 │ │ │
└───┴────┴───┴───┘
```
```
sqlite> CREATE TABLE IF NOT EXISTS t1 (a INTEGER, b INTEGER);
sqlite> CREATE TABLE IF NOT EXISTS t2 (a INTEGER, c REAL);
sqlite> INSERT INTO t1 (a, b) VALUES (1, NULL), (2, 10);
sqlite> INSERT INTO t2 (a, c) VALUES (1, 10.0), (3, NULL);
sqlite> SELECT * FROM t1 LEFT JOIN t2 ON t1.b = t2.c;
1|||
2|10|1|10.0
```
Fixes#4147.
## Description of AI Usage
This PR was developed with assistance from GPT-5.1 Codex Max. The AI
helped analyze the hash join–related codebase (including bloom filters
and hash table implementations), identify the root cause of the issue,
and assist in writing the tests.
Reviewed-by: Preston Thorpe <preston@turso.tech>
Closes#4226
## Description
Run TCL tests for MVCC in the CI. Right now almost everything is
commented out as we need to fix them.
## AI Disclosure
No
<!-- CURSOR_SUMMARY -->
---
> [!NOTE]
> Adds CI step to run MVCC TCL tests via a custom sqlite wrapper with
experimental flags, and narrows the MVCC test suite to known passing
tests.
>
> - **CI**:
> - Add `Test mvcc` step in `test-limbo` job to run `make test-compat-
mvcc` in `.github/workflows/rust.yml`.
> - **Makefile**:
> - Update `test-compat-mvcc` to use `SQLITE_EXEC=scripts/turso-mvcc-
sqlite3`.
> - **Test Runner Script** (`scripts/turso-mvcc-sqlite3`):
> - Enable experimental flags: `--experimental-mvcc`, `--experimental-
views`, `--experimental-strict`.
> - **Tests**:
> - Define `testing/all-mvcc.test` with a subset of passing test
files; comment out failing ones.
>
> <sup>Written by [Cursor
Bugbot](https://cursor.com/dashboard?tab=bugbot) for commit
ee48302e30. This will update automatically
on new commits. Configure
[here](https://cursor.com/dashboard?tab=bugbot).</sup>
<!-- /CURSOR_SUMMARY -->
Reviewed-by: Jussi Saurio <jussi.saurio@gmail.com>
Closes#4178
Some of the tests that were added pass only on this branch. I was
surprised to see that there didn't seem to be any TCL tests for
`json_insert()`.
## AI-generated description
When using JSON_INSERT with a path like '$.a.b.d' on an object like
'{"a": {"b": {"c": 5}}}', the function was incorrectly returning the
input unchanged instead of inserting the new key.
The root cause was that InsertNew mode was being applied to all path
segments. The fix uses Upsert mode for intermediate segments and only
applies InsertNew for the final segment.
🤖 Generated with [Claude Code](https://claude.com/claude-code), and
cleaned up by Mikaël
## AI Disclosure
This was written by Claude, and then I cleaned it up manually.
Reviewed-by: Preston Thorpe <preston@turso.tech>
Closes#4166
When using JSON_INSERT with a path like '$.a.b.d' on an object like
'{"a": {"b": {"c": 5}}}', the function was incorrectly returning the
input unchanged instead of inserting the new key.
The root cause was that InsertNew mode was being applied to all path
segments. The fix uses Upsert mode for intermediate segments and only
applies InsertNew for the final segment.
🤖 Generated with [Claude Code](https://claude.com/claude-code), and cleaned up by Mikaël
The idea is to have a custom `all-mvcc.test` so we can add `.test` files
that we expect to work with MVCC. In cases where files are not enough we
have `is_turso_mvcc` to check if we want to run a test.
For example we skip partial index tests like this:
```
if {![is_turso_mvcc]} {
do_execsql_test_on_specific_db {:memory:} autoinc-conflict-on-nothing {
CREATE TABLE t (id INTEGER PRIMARY KEY AUTOINCREMENT, k TEXT);
CREATE UNIQUE INDEX idx_k_partial ON t(k) WHERE id > 1;
INSERT INTO t (k) VALUES ('a');
INSERT INTO t (k) VALUES ('a');
INSERT INTO t (k) VALUES ('a') ON CONFLICT DO NOTHING;
INSERT INTO t (k) VALUES ('b');
SELECT * FROM t ORDER BY id;
} {1|a 2|a 4|b}
}
```
`test-mvcc-compat` is not run under CI for now as we need to fix every
test anyways so no point in making every PR fail for now.
Reviewed-by: Jussi Saurio <jussi.saurio@gmail.com>
Closes#4139
Take e.g.
CREATE TABLE t(x); CREATE INDEX txdesc ON t(x desc);
INSERT INTO t values (1),(2),(3);
SELECT * FROM t WHERE x > NULL;
--
Our plan, like Sqlite, was to start iterating the descending index
from the beginning (Rewind) and stop once we hit a row where x is
<= than NULL using `IdxGe` instruction (GE in descending indexes
means LE).
However, `IdxGe` and other similar instructions use a sort comparison
where NULL is less than numbers/strings etc, so this would incorrectly
not jump.
Fix: we need to emit an explicit NULL check after rewinding.
After this fix, I ran the fuzz test for more than an hour with no
issues.
Closes https://github.com/tursodatabase/turso/issues/4075
## AI disclosure
Claude wrote the implementation and tests from just a copy/paste of the
Github issue.
Reviewed-by: Jussi Saurio <jussi.saurio@gmail.com>
Closes#4119
For an empty database, SQLite has the database header in memory on
startup. We will need to also have the Database Header on startup to
check if the DB is in WAL or MVCC mode. So this is necessary for #3536.
The major changes here is that we don't have a `DbState` variable
anymore, and instead with have an `init_page_1` that holds an in-memory
page1 that is flushed to disk on a `WriteTx`. In my opinion, this change
also reduced the complexity of our initialization process
Closes#3441
Reviewed-by: Mikaël Francoeur (@LeMikaelF)
Reviewed-by: Preston Thorpe <preston@turso.tech>
Closes#4092
SQLite rejects `CREATE INDEX idx ON t(rowid)` with "no such column: rowid"
because rowid is a pseudo-column, not an actual column. Limbo was
incorrectly allowing this.
The fix removes the special exception for ROWID_STRS (rowid, _rowid_, oid)
in validate_index_expression(). Now these identifiers are only allowed
if they match an actual column name in the table (i.e., when shadowed).
🤖 Generated with [Claude Code](https://claude.com/claude-code)
Co-Authored-By: Claude Opus 4.5 <noreply@anthropic.com>
1. Previously compound select would not start a transaction if
the rightmost subselect had no table references, e.g.
SELECT * FROM t UNION VALUES(1)
2. Previously the column names for the query were taken from the
rightmost subselect - instead, they should be taken from the
leftmost subselect.
Fixes#4030
```
turso> CREATE TABLE t (
a INTEGER,
b TEXT
);
CREATE TABLE t1 (
x INTEGER,
y TEXT
);
CREATE TRIGGER trg
AFTER UPDATE ON t1
BEGIN
INSERT INTO t
SELECT * FROM t
WHERE t.a = 42;
END;
insert into t VALUES (42, 'abc');
INSERT INTO t1 VALUES (0, '');
UPDATE t1 SET y = 'z' WHERE TRUE;
turso> select * from t union all select * from t1;
┌────┬─────┐
│ x │ y │
├────┼─────┤
│ 42 │ abc │
├────┼─────┤
│ 42 │ abc │
├────┼─────┤
│ 0 │ z │
└────┴─────┘
```
```
sqlite> CREATE TABLE t (
(x1...> a INTEGER,
(x1...> b TEXT
(x1...> );
sqlite>
sqlite> CREATE TABLE t1 (
(x1...> x INTEGER,
(x1...> y TEXT
(x1...> );
sqlite>
sqlite> CREATE TRIGGER trg
...> AFTER UPDATE ON t1
...> BEGIN
...> INSERT INTO t
...> SELECT * FROM t
...> WHERE t.a = 42;
...> END;
sqlite> insert into t VALUES (42, 'abc');
sqlite> INSERT INTO t1 VALUES (0, '');
sqlite>
sqlite> UPDATE t1 SET y = 'z' WHERE TRUE;
sqlite> select * from t union all select * from t1;
42|abc
42|abc
0|z
```
Reviewed-by: Preston Thorpe <preston@turso.tech>
Closes#4058
Closes#3318
LIKE(X,Y) is now case-sensitive for characters that are beyond ASCII
range.
I did not make any change in LIKE(X,Y,Z), please let me know if I should
do that!
Closes#3902
