Materialized Views Panic When Created with JOIN Statements

[OrthelT]

Package: Turso CLI 0.2.1

Materialized Views result in a panic if created with a JOIN statement. However, the underlying SELECT statement will work correctly. I have tested it with several different JOIN statements and it appears that that they all result in a panic.

The following SQL works correctly as a SELECT statement but results in a panic that causes the CLI to crash if used to create a Materialized View:

CREATE MATERIALIZED VIEW ggroups AS
SELECT fittings_fitting.name, fittings_type.group_id
FROM fittings_fitting
JOIN fittings_type
ON fittings_fitting.ship_type_id = fittings_type.type_id;

Fails with the following backtrace:

thread 'main' panicked at core/storage/btree.rs:6440:9:
assertion failed: self.current_page >= 0
stack backtrace:
   0: __rustc::rust_begin_unwind
             at /rustc/6b00bc3880198600130e1cf62b8f8a93494488cc/library/std/src/panicking.rs:697:5
   1: core::panicking::panic_fmt
             at /rustc/6b00bc3880198600130e1cf62b8f8a93494488cc/library/core/src/panicking.rs:75:14
   2: core::panicking::panic
             at /rustc/6b00bc3880198600130e1cf62b8f8a93494488cc/library/core/src/panicking.rs:145:5
   3: insert_into_page
   4: insert
             at /home/runner/work/turso/turso/core/storage/btree.rs:4736:31
   5: write_row
             at /home/runner/work/turso/turso/core/incremental/persistence.rs:255:35
   6: commit
             at /home/runner/work/turso/turso/core/incremental/join_operator.rs:664:25
   7: process_node
             at /home/runner/work/turso/turso/core/incremental/compiler.rs:360:40
   8: execute_node
             at /home/runner/work/turso/turso/core/incremental/compiler.rs:767:39
   9: execute_node
             at /home/runner/work/turso/turso/core/incremental/compiler.rs:748:51
  10: run_circuit
             at /home/runner/work/turso/turso/core/incremental/compiler.rs:456:13
  11: commit
             at /home/runner/work/turso/turso/core/incremental/compiler.rs:573:25
  12: merge_delta
             at /home/runner/work/turso/turso/core/incremental/view.rs:1363:36
  13: process_one_row
             at /home/runner/work/turso/turso/core/incremental/view.rs:1319:9
  14: populate_from_table
             at /home/runner/work/turso/turso/core/incremental/view.rs:1179:31
  15: op_populate_materialized_views
             at /home/runner/work/turso/turso/core/vdbe/execute.rs:7121:27
  16: normal_step
             at /home/runner/work/turso/turso/core/vdbe/mod.rs:667:19
  17: step
             at /home/runner/work/turso/turso/core/vdbe/mod.rs:519:34
  18: step
             at /home/runner/work/turso/turso/core/lib.rs:2512:27
  19: print_query_result
             at /home/runner/work/turso/turso/cli/app.rs:110:15
  20: run_query
             at /home/runner/work/turso/turso/cli/app.rs:474:16
  21: consume
  22: main
             at /home/runner/work/turso/turso/cli/main.rs:68:22
  23: call_once<fn() -> core::result::Result<(), anyhow::Error>, ()>
             at /rustc/6b00bc3880198600130e1cf62b8f8a93494488cc/library/core/src/ops/function.rs:250:5
note: Some details are omitted, run with `RUST_BACKTRACE=full` for a verbose backtrace.
Aborted (core dumped)

[Pavan-Nambi]

@OrthelT could u perhaps give full sqlite statements u used?
i did not verified this locally, but looking at this issue #3401 (comment) (jussi is on v0.2.0-pre.10.... and it seems to be working.)

also could u please check if this is same behaviour on 0.3 or main?

[OrthelT]

I'd be happy to. That's the full SQL statement. It works fine if you just run everything after line 2. I tried several different statements, all of which worked as standalone SELECT/JOINS, but failed when using them to create materialized views. I just posted this one because it was the simplest variation that replicated the error.

[OrthelT]

Here are three SQL statements that fail when used to create materialized views but work correctly otherwise. I tested on Arch Linux and WSL2/Ubuntu with the same result:

CREATE MATERIALIZED VIEW fgroups AS
SELECT f.name, f.ship_type_id, t.group_id
FROM fittings_fitting AS f
JOIN fittings_type as t
ON f.ship_type_id = t.type_id;

CREATE MATERIALIZED VIEW ggroups AS
SELECT fittings_fitting.name, fittings_type.group_id
FROM fittings_fitting
JOIN fittings_type
ON fittings_fitting.ship_type_id = fittings_type.type_id;

CREATE MATERIALIZED VIEW xtypesm AS
SELECT
    fit.id,
    fit.name,
    fit.ship_type_id,
    i.type_id,
    t.type_name,
    t.group_id
FROM fittings_fitting AS fit
JOIN fittings_fittingitem AS i
    ON i.fit_id = fit.id
JOIN fittings_type AS t
    ON t.type_id = i.type_id
WHERE t.type_name LIKE '%X-Type EM Armor%';

turso>
CREATE MATERIALIZED VIEW xtypesm AS
SELECT
    fit.id,
    fit.name,
    fit.ship_type_id,
    i.type_id,
    t.type_name,
    t.group_id
FROM fittings_fitting AS fit
JOIN fittings_fittingitem AS i
    ON i.fit_id = fit.id
JOIN fittings_type AS t
    ON t.type_id = i.type_id
WHERE t.type_name LIKE '%X-Type EM Armor%';


thread 'main' panicked at core/storage/btree.rs:6440:9:
assertion failed: self.current_page >= 0
stack backtrace:
   0: __rustc::rust_begin_unwind
             at /rustc/6b00bc3880198600130e1cf62b8f8a93494488cc/library/std/src/panicking.rs:697:5
   1: core::panicking::panic_fmt
             at /rustc/6b00bc3880198600130e1cf62b8f8a93494488cc/library/core/src/panicking.rs:75:14
   2: core::panicking::panic
             at /rustc/6b00bc3880198600130e1cf62b8f8a93494488cc/library/core/src/panicking.rs:145:5
   3: insert_into_page
   4: insert
             at /home/runner/work/turso/turso/core/storage/btree.rs:4736:31
   5: write_row
             at /home/runner/work/turso/turso/core/incremental/persistence.rs:255:35
   6: commit
             at /home/runner/work/turso/turso/core/incremental/join_operator.rs:712:25
   7: process_node
             at /home/runner/work/turso/turso/core/incremental/compiler.rs:360:40
   8: execute_node
             at /home/runner/work/turso/turso/core/incremental/compiler.rs:767:39
   9: execute_node
             at /home/runner/work/turso/turso/core/incremental/compiler.rs:748:51
  10: execute_node
             at /home/runner/work/turso/turso/core/incremental/compiler.rs:748:51
  11: execute_node
             at /home/runner/work/turso/turso/core/incremental/compiler.rs:748:51
  12: execute_node
             at /home/runner/work/turso/turso/core/incremental/compiler.rs:748:51
  13: execute_node
             at /home/runner/work/turso/turso/core/incremental/compiler.rs:748:51
  14: run_circuit
             at /home/runner/work/turso/turso/core/incremental/compiler.rs:456:13
  15: commit
             at /home/runner/work/turso/turso/core/incremental/compiler.rs:573:25
  16: merge_delta
             at /home/runner/work/turso/turso/core/incremental/view.rs:1363:36
  17: process_one_row
             at /home/runner/work/turso/turso/core/incremental/view.rs:1319:9
  18: populate_from_table
             at /home/runner/work/turso/turso/core/incremental/view.rs:1179:31
  19: op_populate_materialized_views
             at /home/runner/work/turso/turso/core/vdbe/execute.rs:7068:27
  20: normal_step
             at /home/runner/work/turso/turso/core/vdbe/mod.rs:667:19
  21: step
             at /home/runner/work/turso/turso/core/vdbe/mod.rs:519:34
  22: step
             at /home/runner/work/turso/turso/core/lib.rs:2487:27
  23: print_query_result
             at /home/runner/work/turso/turso/cli/app.rs:110:15
  24: run_query
             at /home/runner/work/turso/turso/cli/app.rs:474:16
  25: consume
  26: main
             at /home/runner/work/turso/turso/cli/main.rs:68:22
  27: call_once<fn() -> core::result::Result<(), anyhow::Error>, ()>
             at /rustc/6b00bc3880198600130e1cf62b8f8a93494488cc/library/core/src/ops/function.rs:250:5
note: Some details are omitted, run with `RUST_BACKTRACE=full` for a verbose backtrace.
Aborted                    (core dumped) tursodb wcfitting.db --experimental-views

[OrthelT]

I tried it with and without aliased tables to determine if that may be a factor and saw no difference. The only common element was that it always failed with JOIN statements.

[Pavan-Nambi]

Hmm, i'm not able to reproduce this , if someone else can, maybe they could help u