Note: contains 1000 lines of TCL tests generated by cursor :] runtime
changes are smaller and this actually deletes code in aggregate.
---
Main change is to support arbitrary expressions in RETURNING instead of
a specialcased subset, but also e.g. disallow returning TABLE.* which is
illegal syntax in SQLite.
Main idea is we add the columns of the target table (the table affected
by INSERT/UPDATE/DELETE) into `expr_to_reg_cache`) and then just
translate the RETURNING expressions as normal
Closes#3942
# Fix: Clean up DBSP state table when dropping materialized views
## Problem
When dropping a materialized view, the internal DBSP state table (e.g.,
`__turso_internal_dbsp_state_v1_view_name`) and its automatic primary
key index were not being properly cleaned up. This caused two issues:
1. **Persistent schema entries**: The DBSP table and index entries
remained in `sqlite_schema` after dropping the view
2. **In-memory schema inconsistency**: The DBSP table remained in the
in-memory schema's `tables` HashMap, causing "table already exists"
errors when trying to recreate a materialized view with the same name
## Root Cause
The issue had two parts:
1. **Missing sqlite_schema cleanup**: The `translate_drop_view` function
deleted the view entry from `sqlite_schema` but didn't delete the
associated DBSP state table and index entries
2. **Missing in-memory schema cleanup**: The `remove_view` function
removed the materialized view from the in-memory schema but didn't
remove the DBSP state table and its indexes
## Solution
### Changes in `core/translate/view.rs`
- Added a second pass loop in `translate_drop_view` to scan
`sqlite_schema` and delete DBSP table and index entries
- The loop checks for entries matching the DBSP table name pattern
(`__turso_internal_dbsp_state_v{version}_{view_name}`) and the automatic
index name pattern (`sqlite_autoindex___turso_internal_dbsp_state_v{vers
ion}_{view_name}_1`)
- Registers for comparison values are allocated outside the loop for
efficiency
- Column registers are reused across loop iterations
### Changes in `core/schema.rs`
- Updated `remove_view` to also remove the DBSP state table and its
indexes from the in-memory schema's `tables` HashMap and `indexes`
collection
- This ensures consistency between the persistent schema
(`sqlite_schema`) and the in-memory schema
### Tests Added
Added two new test cases in `testing/materialized_views.test`:
1. **`matview-drop-cleans-up-dbsp-table`**: Explicitly verifies that
after dropping a materialized view:
- The view entry is removed from `sqlite_schema`
- The DBSP state table entry is removed from `sqlite_schema`
- The DBSP state index entry is removed from `sqlite_schema`
2. **`matview-recreate-after-drop`**: Verifies that a materialized view
can be successfully recreated after being dropped, which implicitly
tests that all underlying resources (including DBSP tables) are properly
cleaned up
## Testing
- All existing materialized view tests pass
- New tests specifically verify the cleanup behavior
- Manual testing confirms that materialized views can be dropped and
recreated without errors
## Related
This fix ensures that materialized views can be safely dropped and
recreated, resolving issues where the DBSP state table would persist and
cause conflicts.
Reviewed-by: Preston Thorpe <preston@turso.tech>
Closes#3928
## Related issue
- closes#3885
## Description
Add a check to reject dropping a table when PRAGMA foreign_keys=ON and
the table is referenced by foreign keys
Reviewed-by: Preston Thorpe <preston@turso.tech>
Closes#3913
Closes#3892Closes#3888
Stuff like:
```sql
turso> create table t1(x);
create table t2(y, z);
insert into t1 values (1);
insert into t2 values (1, 2);
select case (select y, z from t2) when 1 then 'one' else 'other' end from t1;
× Parse error: base expression in CASE must return 1 value
turso> create table t(x, y);
insert into t values (1, 2);
select (select x, y from t) as result;
× Parse error: result column must return 1 value, got 2
turso> create table t1(x,y);
create table t2(y);
insert into t1 values (1,1);
insert into t2 values (1);
select * from t2 where y = (select x,y from t1);
× Parse error: all arguments to binary operator = must return the same number of
│ values. Got: (1) = (2)
turso> create table orders(customer_id, amount);
create table thresholds(min_amount, max_amount);
insert into orders values (100, 50), (100, 150);
insert into thresholds values (100, 200);
select customer_id, sum(amount) as total
from orders
group by customer_id
having total > (select min_amount, max_amount from thresholds);
× Parse error: all arguments to binary operator > must return the same number of
│ values. Got: (1) > (2)
turso> create table items(id);
create table config(max_results, other_col);
insert into items values (1), (2), (3);
insert into config values (2, 3);
select * from items limit (select max_results, other_col from config);
× Parse error: limit expression must return 1 value, got 2
turso> create table items(id);
create table config(skip_count, other_col);
insert into items values (1), (2), (3);
insert into config values (1, 2);
select * from items limit 1 offset (select skip_count, other_col from config);
× Parse error: offset expression must return 1 value, got 2
turso> create table items(id, name);
create table sort_order(priority, other_col);
insert into items values (1, 'a'), (2, 'b');
insert into sort_order values (1, 2);
select * from items order by (select priority, other_col from sort_order);
× Parse error: order by expression must return 1 value, got 2
turso> create table sales(product_id, amount);
create table grouping(category, other_col);
insert into sales values (1, 100), (2, 200);
insert into grouping values (1, 2);
select sum(amount) from sales group by (select category, other_col from grouping);
× Parse error: group by expression must return 1 value, got 2
turso> create table t1(x);
create table t2(y, z);
insert into t1 values (1);
insert into t2 values (1, 2);
select case when (select y, z from t2) then 'yes' else 'no' end from t1;
× Parse error: when expression in CASE must return 1 value. Got: (2)
turso> create table t1(x);
create table t2(y, z);
insert into t1 values (1);
insert into t2 values (1, 2);
select case when x = 1 then (select y, z from t2) else 0 end from t1;
× Parse error: then expression in CASE must return 1 value. Got: (2)
turso> create table t1(x);
create table t2(y, z);
insert into t1 values (1);
insert into t2 values (1, 2);
select case when x = 2 then 0 else (select y, z from t2) end from t1;
× Parse error: else expression in CASE must return 1 value. Got: (2)
turso> create table t1(x);
create table t2(y, z);
insert into t1 values (1);
insert into t2 values (1, 2);
select max((select y, z from t2)) from t1;
× Parse error: argument 0 to function call max must return 1 value. Got: (2)
turso> create table t1(x);
create table t2(y, z);
insert into t1 values (1);
insert into t2 values (1, 2);
select x + (select y, z from t2) from t1;
× Parse error: all arguments to binary operator + must return the same number of
│ values. Got: (1) + (2)
turso> create table t1(x);
create table t2(y, z);
insert into t1 values (5);
insert into t2 values (1, 2);
select * from t1 where x between (select y, z from t2) and 10;
× Parse error: all arguments to binary operator <= must return the same number of
│ values. Got: (2) <= (1)
turso> create table t1(x);
create table t2(y, z);
insert into t1 values (1);
insert into t2 values (1, 2);
select cast((select y, z from t2) as integer) from t1;
× Parse error: argument to CAST must return 1 value. Got: (2)
turso> create table t1(x);
create table t2(y, z);
insert into t1 values (1);
insert into t2 values ('a', 'b');
select (select y, z from t2) collate nocase from t1;
× Parse error: argument to COLLATE must return 1 value. Got: (2)
turso> create table t1(x);
create table t2(y, z);
insert into t1 values (1);
insert into t2 values (1, 2);
select * from t1 where (select y, z from t2) is null;
× Parse error: all arguments to binary operator IS must return the same number of
│ values. Got: (2) IS (1)
turso> create table t1(x);
create table t2(y, z);
insert into t1 values (1);
insert into t2 values (1, 2);
select * from t1 where (select y, z from t2) not null;
× Parse error: argument to NOT NULL must return 1 value. Got: (2)
turso> create table t1(x);
create table t2(y, z);
insert into t1 values (1);
insert into t2 values ('a', 'b');
select * from t1 where (select y, z from t2) like 'a%';
× Parse error: left operand of LIKE must return 1 value. Got: (2)
turso> create table t1(x);
create table t2(y, z);
insert into t1 values (1);
insert into t2 values (1, 2);
select -(select y, z from t2) from t1;
× Parse error: argument to unary operator - must return 1 value. Got: (2)
turso> create table t1(x);
create table t2(y, z);
insert into t1 values (1);
insert into t2 values (1, 2);
select abs((select y, z from t2)) from t1;
× Parse error: argument 0 to function call abs must return 1 value. Got: (2)
```
Closes#3906
I discovered a flaw in our current translation that makes queries of type
HAVING foo IN (SELECT ...) not work properly - in these cases we need to
defer translation of the subquery until later.
I will fix this in a future PR because I suspect it's not trivial.
Currently LIMIT 0 jumps to "after the main loop", and it is done
before ORDER BY and GROUP BY cursor have had a chance to be initialized,
which causes a panic.
Simplest fix for now is to delay the LIMIT initialization.
Trying to return integer sometimes to match SQLite led to more problems
that I anticipated. The reason being, we can't *really* match SQLite's
behavior unless we know the type of *every* element in the sum. This is
not impossible, but it is very hard, for very little gain.
Fixes#3831
Implements COUNT/SUM/AVG(DISTINCT) and SELECT DISTINCT for materialized
views. To do this we have to keep a list of the actual distinct values
(similarly to how we do for min/max). We then update the operator (and
issue deltas) only when there is a state transition (for example, if we
already count the value x = 1, and we see an insert for x = 1, we do
nothing).
SELECT DISTINCT (with no aggregator) is similar. We already have to keep
a list of the values anyway to power the aggregates. So we just issue
new deltas based on the transition, without updating the aggregator.
Closes#3808
This PR implements simple heap-sort approach for query plans like
`SELECT ... FROM t WHERE ... ORDER BY ... LIMIT N` in order to maintain
small set of top N elements in the ephemeral B-tree and avoid sort and
materialization of whole dataset.
I removed all optimizations not related to this particular change in
order to make branch lightweight.
Reviewed-by: Jussi Saurio <jussi.saurio@gmail.com>
Closes#3726
