mirror of
https://github.com/tursodatabase/limbo.git
synced 2025-12-23 08:21:09 +00:00
1bcb14f9c5
## 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 |
||
|---|---|---|
| .. | ||
| cli_tests | ||
| javascript | ||
| sqlancer | ||
| sqlite3 | ||
| sqlite_test_ext | ||
| test_files | ||
| unreliable-libc | ||
| wal | ||
| affinity.test | ||
| agg-functions.test | ||
| all-mvcc.test | ||
| all.test | ||
| alter_column.test | ||
| alter_table.test | ||
| analyze.test | ||
| attach.test | ||
| autoincr.test | ||
| boolean.test | ||
| changes.test | ||
| cmdlineshell.test | ||
| coalesce.test | ||
| collate.test | ||
| compare.test | ||
| concat.test | ||
| create_index.test | ||
| create_table.test | ||
| default_value.test | ||
| delete.test | ||
| drop_index.test | ||
| drop_table.test | ||
| foreign_keys.test | ||
| gen-bigass-database.py | ||
| gen-database.py | ||
| glob.test | ||
| groupby.test | ||
| insert.test | ||
| integrity_check.test | ||
| join.test | ||
| json.test | ||
| like.test | ||
| literal.test | ||
| materialized_views.test | ||
| math.test | ||
| null.test | ||
| offset.test | ||
| orderby.test | ||
| partial_idx.test | ||
| pragma.test | ||
| pyproject.toml | ||
| README.md | ||
| returning.test | ||
| rollback.test | ||
| scalar-functions-datetime.test | ||
| scalar-functions-printf.test | ||
| scalar-functions.test | ||
| select.test | ||
| subquery.test | ||
| tester.tcl | ||
| testing.db | ||
| testing_norowidalias.db | ||
| testing_small.db | ||
| testing_user_version_10.db | ||
| time.test | ||
| total-changes.test | ||
| transactions.test | ||
| trigger.test | ||
| update.test | ||
| upsert.test | ||
| values.test | ||
| vector.test | ||
| views.test | ||
| vtab.test | ||
| where.test | ||
| window.test | ||
Turso Testing
Testing Extensions
When adding tests for extensions, please follow these guidelines:
- Tests that verify the internal logic or behavior of a particular extension should go into
cli_tests/extensions.py. - Tests that verify how extensions interact with the database engine, such as virtual table handling, should be written
in TCL (see
vtab.testas an example).
To check which extensions are available in TCL, or to add a new one, refer to the tester.tcl file and look at the extension_map.