Repository: tursodatabase/limbo (rust)

Issue:
INSERT statements seem to ignore column order specification when inserted via Go/Rust/Python adapter
When using named columns in INSERT statements, Limbo seems to be ignoring the specified column order and inserting values according to the table's definition order instead.

```go
package main

import (
	"database/sql"
	"fmt"
	"log"

	_ "github.com/tursodatabase/limbo"
)

func main() {
	fmt.Println("Testing column ordering in INSERT statements")
	fmt.Println("==========================================")

	// Connect to in-memory database
	db, err := sql.Open("sqlite3", ":memory:")
	if err != nil {
		log.Fatalf("Failed to open database: %v", err)
	}
	defer db.Close()

	// Create a table with specific column order
	createSQL := `CREATE TABLE IF NOT EXISTS test (
		id INTEGER PRIMARY KEY AUTOINCREMENT,
		col_a TEXT,
		col_b TEXT,
		col_c TEXT,
		col_d TEXT);`
	
	_, err = db.Exec(createSQL)
	if err != nil {
		log.Fatalf("Failed to create table: %v", err)
	}

	// Insert data with different column order than table definition
	// Table order: col_a, col_b, col_c, col_d
	// Insert order: col_b, col_d, col_a, col_c
	insertSQL := `INSERT INTO test (col_b, col_d, col_a, col_c) VALUES (?, ?, ?, ?)`
	
	values := []string{"VALUE_B", "VALUE_D", "VALUE_A", "VALUE_C"}
	_, err = db.Exec(insertSQL, values[0], values[1], values[2], values[3])
	if err != nil {
		log.Fatalf("Failed to insert: %v", err)
	}

	// Query data back
	rows, err := db.Query("SELECT id, col_a, col_b, col_c, col_d FROM test")
	if err != nil {
		log.Fatalf("Failed to query: %v", err)
	}
	defer rows.Close()

	fmt.Println("\nColumn Order Expected:")
	fmt.Println("col_a: VALUE_A")
	fmt.Println("col_b: VALUE_B")
	fmt.Println("col_c: VALUE_C")
	fmt.Println("col_d: VALUE_D")

	fmt.Println("\nActual Results:")
	for rows.Next() {
		var id int
		var colA, colB, colC, colD string
		err = rows.Scan(&id, &colA, &colB, &colC, &colD)
		if err != nil {
			log.Fatalf("Failed to scan: %v", err)
		}
		fmt.Printf("id: %d\n", id)
		fmt.Printf("col_a: %s\n", colA)
		fmt.Printf("col_b: %s\n", colB)
		fmt.Printf("col_c: %s\n", colC)
		fmt.Printf("col_d: %s\n", colD)
	}

	// Also test with a different scenario
	fmt.Println("\n\nTest 2: More complex ordering")
	fmt.Println("-----------------------------")

	// Insert with another different order
	_, err = db.Exec(`INSERT INTO test (col_d, col_a, col_c, col_b) VALUES (?, ?, ?, ?)`, 
		"VALUE_D2", "VALUE_A2", "VALUE_C2", "VALUE_B2")
	if err != nil {
		log.Fatalf("Failed to insert test 2: %v", err)
	}

	// Query all data
	rows2, err := db.Query("SELECT id, col_a, col_b, col_c, col_d FROM test ORDER BY id")
	if err != nil {
		log.Fatalf("Failed to query test 2: %v", err)
	}
	defer rows2.Close()

	fmt.Println("\nAll rows:")
	for rows2.Next() {
		var id int
		var colA, colB, colC, colD string
		err = rows2.Scan(&id, &colA, &colB, &colC, &colD)
		if err != nil {
			log.Fatalf("Failed to scan test 2: %v", err)
		}
		fmt.Printf("Row %d: A=%s, B=%s, C=%s, D=%s\n", id, colA, colB, colC, colD)
	}
}
```
```
Testing column ordering in INSERT statements
==========================================

Column Order Expected:
col_a: VALUE_A
col_b: VALUE_B
col_c: VALUE_C
col_d: VALUE_D

Actual Results:
id: 1
col_a: VALUE_B
col_b: VALUE_D
col_c: VALUE_A
col_d: VALUE_C


Test 2: More complex ordering
-----------------------------

All rows:
Row 1: A=VALUE_B, B=VALUE_D, C=VALUE_A, D=VALUE_C
Row 2: A=VALUE_D2, B=VALUE_A2, C=VALUE_C2, D=VALUE_B2
```



Pull request description:
Fix bound parameters on insert statements with out of order column indexes
closes #1449 

A quick `explain` will show the root of the problem:
![image](https://github.com/user-attachments/assets/c5e9f2cf-494f-410a-9e6f-399d4256cbc8)
vs `sqlite3`
![image](https://github.com/user-attachments/assets/79b18254-4a5d-40bb-a5f2-1322326b745f)


We process the columns in the order they need to be to create the record, and although we store the index of the value meant to be inserted into that column based on the order they are bound, we still lose the original ordering.

## EDIT: Fixed

![image](https://github.com/user-attachments/assets/4b40451a-6eda-4ff1-8f21-a36ba8598d00)

### Multi-row insert:
![image](https://github.com/user-attachments/assets/5b68b44c-1a96-442b-80cd-3a4df0e598ae)


## Solution:

We just needed to traverse the insert values beforehand and create an array of N integers each representing the index of each `ast::Expr::Variable`: then later on we can lookup the `value_index` in that array, and grab the index of matching integer to get  the parameters index to be bound to the relevant variable.

(yes I know that sounds like a leetcode problem)


Thanks to @jnesss for the write-up for this bug

Patch:
diff --git a/bindings/go/rs_src/types.rs b/bindings/go/rs_src/types.rs
index f58a4a0b..fd39ac41 100644
--- a/bindings/go/rs_src/types.rs
+++ b/bindings/go/rs_src/types.rs
@@ -1,4 +1,7 @@
-use std::ffi::{c_char, c_void};
+use std::{
+    ffi::{c_char, c_void},
+    fmt::Debug,
+};
 
 #[allow(dead_code)]
 #[repr(C)]
@@ -20,6 +23,7 @@ pub enum ResultCode {
     NoSuchEntity = 13,
 }
 
+#[derive(Debug)]
 #[repr(C)]
 pub enum ValueType {
     Integer = 0,
@@ -34,6 +38,34 @@ pub struct LimboValue {
     value_type: ValueType,
     value: ValueUnion,
 }
+impl Debug for LimboValue {
+    fn fmt(&self, f: &mut std::fmt::Formatter<'_>) -> std::fmt::Result {
+        match self.value_type {
+            ValueType::Integer => {
+                let i = self.value.to_int();
+                f.debug_struct("LimboValue").field("value", &i).finish()
+            }
+            ValueType::Real => {
+                let r = self.value.to_real();
+                f.debug_struct("LimboValue").field("value", &r).finish()
+            }
+            ValueType::Text => {
+                let t = self.value.to_str();
+                f.debug_struct("LimboValue").field("value", &t).finish()
+            }
+            ValueType::Blob => {
+                let blob = self.value.to_bytes();
+                f.debug_struct("LimboValue")
+                    .field("value", &blob.to_vec())
+                    .finish()
+            }
+            ValueType::Null => f
+                .debug_struct("LimboValue")
+                .field("value", &"NULL")
+                .finish(),
+        }
+    }
+}
 
 #[repr(C)]
 union ValueUnion {
diff --git a/core/parameters.rs b/core/parameters.rs
index b533a1b5..a3dab16c 100644
--- a/core/parameters.rs
+++ b/core/parameters.rs
@@ -1,3 +1,4 @@
+use super::ast;
 use std::num::NonZero;
 
 #[derive(Clone, Debug)]
@@ -23,10 +24,52 @@ impl Parameter {
     }
 }
 
+#[derive(Debug)]
+struct InsertContext {
+    param_positions: Vec<usize>,
+    current_col_value_idx: usize,
+}
+
+impl InsertContext {
+    fn new(param_positions: Vec<usize>) -> Self {
+        Self {
+            param_positions,
+            current_col_value_idx: 0,
+        }
+    }
+
+    /// Find the relevant parameter index needed for the current value index of insert stmt
+    /// Example for table t (a,b,c):
+    ///         `insert into t (c,a,b) values (?,?,?)`
+    ///
+    /// col a -> value_index 1
+    /// col b -> value_index 2
+    /// col c -> value_index 0
+    ///
+    /// however translation will always result in parameters 1, 2, 3
+    /// because columns are translated in the table order so `col a` gets
+    /// translated first, translate_expr calls parameters.push and always gets index 1.
+    ///
+    /// Instead, we created an array representing all the value_index's that are type
+    /// Expr::Variable, in the case above would be [1, 2, 0], and stored it in insert_ctx.
+    /// That array can be used to look up the necessary parameter index by searching for the value
+    /// index in the array and returning the index of that value + 1.
+    ///  value_index->   [1, 2, 0]
+    ///  param index->   |0, 1, 2|
+    fn get_insert_param_index(&self) -> Option<NonZero<usize>> {
+        self.param_positions
+            .iter()
+            .position(|param| param.eq(&self.current_col_value_idx))
+            .map(|p| NonZero::new(p + 1).unwrap())
+    }
+}
+
 #[derive(Debug)]
 pub struct Parameters {
     index: NonZero<usize>,
     pub list: Vec<Parameter>,
+    // Context for reordering parameters during insert statements
+    insert_ctx: Option<InsertContext>,
 }
 
 impl Default for Parameters {
@@ -40,6 +83,7 @@ impl Parameters {
         Self {
             index: 1.try_into().unwrap(),
             list: vec![],
+            insert_ctx: None,
         }
     }
 
@@ -49,6 +93,18 @@ impl Parameters {
         params.len()
     }
 
+    /// Begin preparing for an Insert statement by providing the array of values from the Insert body.
+    pub fn init_insert_parameters(&mut self, values: &[Vec<ast::Expr>]) {
+        self.insert_ctx = Some(InsertContext::new(expected_param_indicies(values)));
+    }
+
+    /// Set the value index for the column currently being translated for an Insert stmt.
+    pub fn set_insert_value_index(&mut self, idx: usize) {
+        if let Some(ctx) = &mut self.insert_ctx {
+            ctx.current_col_value_idx = idx;
+        }
+    }
+
     pub fn name(&self, index: NonZero<usize>) -> Option<String> {
         self.list.iter().find_map(|p| match p {
             Parameter::Anonymous(i) if *i == index => Some("?".to_string()),
@@ -80,7 +136,11 @@ impl Parameters {
                 let index = self.next_index();
                 self.list.push(Parameter::Anonymous(index));
                 tracing::trace!("anonymous parameter at {index}");
-                index
+                if let Some(idx) = &self.insert_ctx {
+                    idx.get_insert_param_index().unwrap_or(index)
+                } else {
+                    index
+                }
             }
             name if name.starts_with(['$', ':', '@', '#']) => {
                 match self
@@ -115,3 +175,14 @@ impl Parameters {
         }
     }
 }
+
+/// Gather all the expected indicies of all Expr::Variable
+/// in the provided array of insert values.
+pub fn expected_param_indicies(cols: &[Vec<ast::Expr>]) -> Vec<usize> {
+    cols.iter()
+        .flat_map(|col| col.iter())
+        .enumerate()
+        .filter(|(_, col)| matches!(col, ast::Expr::Variable(_)))
+        .map(|(i, _)| i)
+        .collect::<Vec<_>>()
+}
diff --git a/core/translate/expr.rs b/core/translate/expr.rs
index 62f51d26..629d2e40 100644
--- a/core/translate/expr.rs
+++ b/core/translate/expr.rs
@@ -1851,11 +1851,8 @@ pub fn translate_expr(
                             } else {
                                 Some(program.resolve_cursor_id(&table_reference.identifier))
                             };
-                            let index_cursor_id = if let Some(index) = index {
-                                Some(program.resolve_cursor_id(&index.name))
-                            } else {
-                                None
-                            };
+                            let index_cursor_id =
+                                index.map(|index| program.resolve_cursor_id(&index.name));
                             if *is_rowid_alias {
                                 if let Some(index_cursor_id) = index_cursor_id {
                                     program.emit_insn(Insn::IdxRowId {
diff --git a/core/translate/insert.rs b/core/translate/insert.rs
index 815d934d..233d623c 100644
--- a/core/translate/insert.rs
+++ b/core/translate/insert.rs
@@ -106,6 +106,8 @@ pub fn translate_insert(
         },
         InsertBody::DefaultValues => &vec![vec![]],
     };
+    // prepare parameters by tracking the number of variables we will be binding to values later on
+    program.parameters.init_insert_parameters(values);
 
     let column_mappings = resolve_columns_for_insert(&table, columns, values)?;
     let index_col_mappings = resolve_indicies_for_insert(schema, table.as_ref(), &column_mappings)?;
@@ -152,8 +154,9 @@ pub fn translate_insert(
 
         program.resolve_label(start_offset_label, program.offset());
 
-        for value in values {
+        for (i, value) in values.iter().enumerate() {
             populate_column_registers(
+                i,
                 &mut program,
                 value,
                 &column_mappings,
@@ -191,6 +194,7 @@ pub fn translate_insert(
         });
 
         populate_column_registers(
+            0,
             &mut program,
             &values[0],
             &column_mappings,
@@ -452,7 +456,6 @@ fn resolve_columns_for_insert<'a>(
     }
 
     let table_columns = &table.columns();
-
     // Case 1: No columns specified - map values to columns in order
     if columns.is_none() {
         let num_values = values[0].len();
@@ -503,15 +506,15 @@ fn resolve_columns_for_insert<'a>(
                 .map_or(false, |name| name.eq_ignore_ascii_case(&column_name))
         });
 
-        if table_index.is_none() {
+        let Some(table_index) = table_index else {
             crate::bail_parse_error!(
                 "table {} has no column named {}",
                 &table.get_name(),
                 column_name
             );
-        }
+        };
 
-        mappings[table_index.unwrap()].value_index = Some(value_index);
+        mappings[table_index].value_index = Some(value_index);
     }
 
     Ok(mappings)
@@ -581,7 +584,9 @@ fn resolve_indicies_for_insert(
 }
 
 /// Populates the column registers with values for a single row
+#[allow(clippy::too_many_arguments)]
 fn populate_column_registers(
+    row_idx: usize,
     program: &mut ProgramBuilder,
     value: &[Expr],
     column_mappings: &[ColumnMapping],
@@ -605,6 +610,14 @@ fn populate_column_registers(
             } else {
                 target_reg
             };
+            // We need the 'parameters' to be aware of the value_index of the current row
+            // so it can map it to the correct parameter index in the Variable opcode
+            // but we need to make sure the value_index is not overwritten if this is a multi-row
+            // insert. For 'insert into t values: (?,?), (?,?);'
+            // value_index should be (1,2),(3,4) instead of (1,2),(1,2), so multiply by col length
+            program
+                .parameters
+                .set_insert_value_index(value_index + (column_mappings.len() * row_idx));
             translate_expr_no_constant_opt(
                 program,
                 None,
@@ -668,21 +681,12 @@ fn translate_virtual_table_insert(
         InsertBody::DefaultValues => &vec![],
         _ => crate::bail_parse_error!("Unsupported INSERT body for virtual tables"),
     };
-
+    // initiate parameters by tracking the number of variables we will be binding to values
+    program.parameters.init_insert_parameters(values);
     let table = Table::Virtual(virtual_table.clone());
     let column_mappings = resolve_columns_for_insert(&table, columns, values)?;
+    let registers_start = program.alloc_registers(2);
 
-    let value_registers_start = program.alloc_registers(values[0].len());
-    for (i, expr) in values[0].iter().enumerate() {
-        translate_expr_no_constant_opt(
-            program,
-            None,
-            expr,
-            value_registers_start + i,
-            resolver,
-            NoConstantOptReason::RegisterReuse,
-        )?;
-    }
     /* *
      * Inserts for virtual tables are done in a single step.
      * argv[0] = (NULL for insert)
@@ -690,35 +694,22 @@ fn translate_virtual_table_insert(
      * argv[2..] = column values
      * */
 
-    let rowid_reg = program.alloc_registers(column_mappings.len() + 3);
-    let insert_rowid_reg = rowid_reg + 1; // argv[1] = insert_rowid
-    let data_start_reg = rowid_reg + 2; // argv[2..] = column values
-
-    program.emit_insn(Insn::Null {
-        dest: rowid_reg,
-        dest_end: None,
-    });
     program.emit_insn(Insn::Null {
-        dest: insert_rowid_reg,
-        dest_end: None,
+        dest: registers_start,
+        dest_end: Some(registers_start + 1),
     });
 
-    for (i, mapping) in column_mappings.iter().enumerate() {
-        let target_reg = data_start_reg + i;
-        if let Some(value_index) = mapping.value_index {
-            program.emit_insn(Insn::Copy {
-                src_reg: value_registers_start + value_index,
-                dst_reg: target_reg,
-                amount: 1,
-            });
-        } else {
-            program.emit_insn(Insn::Null {
-                dest: target_reg,
-                dest_end: None,
-            });
-        }
-    }
-
+    let values_reg = program.alloc_registers(column_mappings.len());
+    populate_column_registers(
+        0,
+        program,
+        &values[0],
+        &column_mappings,
+        values_reg,
+        false,
+        registers_start,
+        resolver,
+    )?;
     let conflict_action = on_conflict.as_ref().map(|c| c.bit_value()).unwrap_or(0) as u16;
 
     let cursor_id = program.alloc_cursor_id(
@@ -729,7 +720,7 @@ fn translate_virtual_table_insert(
     program.emit_insn(Insn::VUpdate {
         cursor_id,
         arg_count: column_mappings.len() + 2,
-        start_reg: rowid_reg,
+        start_reg: registers_start,
         vtab_ptr: virtual_table.implementation.as_ref().ctx as usize,
         conflict_action,
     });


Test patch:
diff --git a/bindings/go/limbo_test.go b/bindings/go/limbo_test.go
index 598a28a6..d5e61faa 100644
--- a/bindings/go/limbo_test.go
+++ b/bindings/go/limbo_test.go
@@ -608,6 +608,81 @@ func TestJSONFunctions(t *testing.T) {
 	}
 }
 
+func TestParameterOrdering(t *testing.T) {
+	newConn, err := sql.Open("sqlite3", ":memory:")
+	if err != nil {
+		t.Fatalf("Error opening new connection: %v", err)
+	}
+	sql := "CREATE TABLE test (a,b,c);"
+	newConn.Exec(sql)
+
+	// Test inserting with parameters in a different order than
+	// the table definition.
+	sql = "INSERT INTO test (b, c ,a) VALUES (?, ?, ?);"
+	expectedValues := []int{1, 2, 3}
+	stmt, err := newConn.Prepare(sql)
+	_, err = stmt.Exec(expectedValues[1], expectedValues[2], expectedValues[0])
+	if err != nil {
+		t.Fatalf("Error preparing statement: %v", err)
+	}
+	// check that the values are in the correct order
+	query := "SELECT a,b,c FROM test;"
+	rows, err := newConn.Query(query)
+	if err != nil {
+		t.Fatalf("Error executing query: %v", err)
+	}
+	for rows.Next() {
+		var a, b, c int
+		err := rows.Scan(&a, &b, &c)
+		if err != nil {
+			t.Fatal("Error scanning row: ", err)
+		}
+		result := []int{a, b, c}
+		for i := range 3 {
+			if result[i] != expectedValues[i] {
+				fmt.Printf("RESULTS: %d, %d, %d\n", a, b, c)
+				fmt.Printf("EXPECTED: %d, %d, %d\n", expectedValues[0], expectedValues[1], expectedValues[2])
+			}
+		}
+	}
+
+	// -- part 2 --
+	// mixed parameters and regular values
+	sql2 := "CREATE TABLE test2 (a,b,c);"
+	newConn.Exec(sql2)
+	expectedValues2 := []int{1, 2, 3}
+
+	// Test inserting with parameters in a different order than
+	// the table definition, with a mixed regular parameter included
+	sql2 = "INSERT INTO test2 (a, b ,c) VALUES (1, ?, ?);"
+	_, err = newConn.Exec(sql2, expectedValues2[1], expectedValues2[2])
+	if err != nil {
+		t.Fatalf("Error preparing statement: %v", err)
+	}
+	// check that the values are in the correct order
+	query2 := "SELECT a,b,c FROM test2;"
+	rows2, err := newConn.Query(query2)
+	if err != nil {
+		t.Fatalf("Error executing query: %v", err)
+	}
+	for rows2.Next() {
+		var a, b, c int
+		err := rows2.Scan(&a, &b, &c)
+		if err != nil {
+			t.Fatal("Error scanning row: ", err)
+		}
+		result := []int{a, b, c}
+
+		fmt.Printf("RESULTS: %d, %d, %d\n", a, b, c)
+		fmt.Printf("EXPECTED: %d, %d, %d\n", expectedValues[0], expectedValues[1], expectedValues[2])
+		for i := range 3 {
+			if result[i] != expectedValues[i] {
+				t.Fatalf("Expected %d, got %d", expectedValues[i], result[i])
+			}
+		}
+	}
+}
+
 func slicesAreEq(a, b []byte) bool {
 	if len(a) != len(b) {
 		fmt.Printf("LENGTHS NOT EQUAL: %d != %d\n", len(a), len(b))
diff --git a/tests/integration/common.rs b/tests/integration/common.rs
index 1ef890db..a18b7596 100644
--- a/tests/integration/common.rs
+++ b/tests/integration/common.rs
@@ -38,6 +38,9 @@ impl TempDatabase {
     }
 
     pub fn new_with_rusqlite(table_sql: &str) -> Self {
+        let _ = tracing_subscriber::fmt()
+            .with_max_level(tracing::Level::TRACE)
+            .finish();
         let mut path = TempDir::new().unwrap().into_path();
         path.push("test.db");
         {
diff --git a/tests/integration/query_processing/test_read_path.rs b/tests/integration/query_processing/test_read_path.rs
index 7f525f6a..f7bbe5ca 100644
--- a/tests/integration/query_processing/test_read_path.rs
+++ b/tests/integration/query_processing/test_read_path.rs
@@ -3,7 +3,6 @@ use limbo_core::{OwnedValue, StepResult};
 
 #[test]
 fn test_statement_reset_bind() -> anyhow::Result<()> {
-    let _ = env_logger::try_init();
     let tmp_db = TempDatabase::new_with_rusqlite("create table test (i integer);");
     let conn = tmp_db.connect_limbo();
 
@@ -48,7 +47,6 @@ fn test_statement_reset_bind() -> anyhow::Result<()> {
 
 #[test]
 fn test_statement_bind() -> anyhow::Result<()> {
-    let _ = env_logger::try_init();
     let tmp_db = TempDatabase::new_with_rusqlite("create table test (i integer);");
     let conn = tmp_db.connect_limbo();
 
@@ -99,3 +97,385 @@ fn test_statement_bind() -> anyhow::Result<()> {
     }
     Ok(())
 }
+
+#[test]
+fn test_insert_parameter_remap() -> anyhow::Result<()> {
+    // ───────────────────────  schema  ──────────────────────────────
+    // Table             a     b     c     d
+    // INSERT lists:     d ,   c ,   a ,   b
+    // VALUES list:    22 ,   ?1 ,   7 ,   ?2
+    //
+    // Expected row on disk:  a = 7 , b = ?2 , c = ?1 , d = 22
+    //
+    // We bind ?1 = 111 and ?2 = 222 and expect (7,222,111,22).
+    // ───────────────────────────────────────────────────────────────
+
+    let tmp_db = TempDatabase::new_with_rusqlite(
+        "create table test (a integer, b integer, c integer, d integer);",
+    );
+    let conn = tmp_db.connect_limbo();
+
+    // prepare INSERT with re-ordered columns and constants
+    let mut ins = conn.prepare("insert into test (d, c, a, b) values (22, ?, 7, ?);")?;
+    let args = [OwnedValue::Integer(111), OwnedValue::Integer(222)];
+    for (i, arg) in args.iter().enumerate() {
+        let idx = i + 1;
+        ins.bind_at(idx.try_into()?, arg.clone());
+    }
+    loop {
+        match ins.step()? {
+            StepResult::IO => tmp_db.io.run_once()?,
+            StepResult::Done | StepResult::Interrupt => break,
+            StepResult::Busy => panic!("database busy"),
+            _ => {}
+        }
+    }
+
+    let mut sel = conn.prepare("select a, b, c, d from test;")?;
+    loop {
+        match sel.step()? {
+            StepResult::Row => {
+                let row = sel.row().unwrap();
+                // insert_index = 3
+                // A = 7
+                assert_eq!(row.get::<&OwnedValue>(0).unwrap(), &OwnedValue::Integer(7));
+                // insert_index = 4
+                // B = 222
+                assert_eq!(
+                    row.get::<&OwnedValue>(1).unwrap(),
+                    &OwnedValue::Integer(222)
+                );
+                // insert_index = 2
+                // C = 111
+                assert_eq!(
+                    row.get::<&OwnedValue>(2).unwrap(),
+                    &OwnedValue::Integer(111)
+                );
+                // insert_index = 1
+                // D = 22
+                assert_eq!(row.get::<&OwnedValue>(3).unwrap(), &OwnedValue::Integer(22));
+            }
+            StepResult::IO => tmp_db.io.run_once()?,
+            StepResult::Done | StepResult::Interrupt => break,
+            StepResult::Busy => panic!("database busy"),
+        }
+    }
+
+    // exactly two distinct parameters were used
+    assert_eq!(ins.parameters().count(), 2);
+
+    Ok(())
+}
+
+#[test]
+fn test_insert_parameter_remap_all_params() -> anyhow::Result<()> {
+    // ───────────────────────  schema  ──────────────────────────────
+    // Table             a     b     c     d
+    // INSERT lists:     d ,   a ,   c ,   b
+    // VALUES list:     ?1 ,  ?2 ,  ?3 ,  ?4
+    //
+    // Expected row on disk:  a = ?2 , b = ?4 , c = ?3 , d = ?1
+    //
+    // We bind ?1 = 999, ?2 = 111, ?3 = 333, ?4 = 444.
+    // The row should be (111, 444, 333, 999).
+    // ───────────────────────────────────────────────────────────────
+
+    let tmp_db = TempDatabase::new_with_rusqlite(
+        "create table test (a integer, b integer, c integer, d integer);",
+    );
+    let conn = tmp_db.connect_limbo();
+    let mut ins = conn.prepare("insert into test (d, a, c, b) values (?, ?, ?, ?);")?;
+
+    let values = [
+        OwnedValue::Integer(999), // ?1 → d
+        OwnedValue::Integer(111), // ?2 → a
+        OwnedValue::Integer(333), // ?3 → c
+        OwnedValue::Integer(444), // ?4 → b
+    ];
+    for (i, value) in values.iter().enumerate() {
+        let idx = i + 1;
+        ins.bind_at(idx.try_into()?, value.clone());
+    }
+
+    // execute the insert (no rows returned)
+    loop {
+        match ins.step()? {
+            StepResult::IO => tmp_db.io.run_once()?,
+            StepResult::Done | StepResult::Interrupt => break,
+            StepResult::Busy => panic!("database busy"),
+            _ => {}
+        }
+    }
+
+    let mut sel = conn.prepare("select a, b, c, d from test;")?;
+    loop {
+        match sel.step()? {
+            StepResult::Row => {
+                let row = sel.row().unwrap();
+
+                // insert_index = 2
+                // A = 111
+                assert_eq!(
+                    row.get::<&OwnedValue>(0).unwrap(),
+                    &OwnedValue::Integer(111)
+                );
+                // insert_index = 4
+                // B = 444
+                assert_eq!(
+                    row.get::<&OwnedValue>(1).unwrap(),
+                    &OwnedValue::Integer(444)
+                );
+                // insert_index = 3
+                // C = 333
+                assert_eq!(
+                    row.get::<&OwnedValue>(2).unwrap(),
+                    &OwnedValue::Integer(333)
+                );
+                // insert_index = 1
+                // D = 999
+                assert_eq!(
+                    row.get::<&OwnedValue>(3).unwrap(),
+                    &OwnedValue::Integer(999)
+                );
+            }
+            StepResult::IO => tmp_db.io.run_once()?,
+            StepResult::Done | StepResult::Interrupt => break,
+            StepResult::Busy => panic!("database busy"),
+        }
+    }
+    assert_eq!(ins.parameters().count(), 4);
+    Ok(())
+}
+
+#[test]
+fn test_insert_parameter_multiple_remap_backwards() -> anyhow::Result<()> {
+    // ───────────────────────  schema  ──────────────────────────────
+    // Table             a     b     c     d
+    // INSERT lists:     d ,   c ,   b ,   a
+    // VALUES list:     ?1 ,  ?2 ,  ?3 ,  ?4
+    //
+    // Expected row on disk:  a = ?1 , b = ?2 , c = ?3 , d = ?4
+    //
+    // The row should be (111, 222, 333, 444)
+    // ───────────────────────────────────────────────────────────────
+
+    let tmp_db = TempDatabase::new_with_rusqlite(
+        "create table test (a integer, b integer, c integer, d integer);",
+    );
+    let conn = tmp_db.connect_limbo();
+    let mut ins = conn.prepare("insert into test (d,c,b,a) values (?, ?, ?, ?);")?;
+
+    let values = [
+        OwnedValue::Integer(444), // ?1 → d
+        OwnedValue::Integer(333), // ?2 → c
+        OwnedValue::Integer(222), // ?3 → b
+        OwnedValue::Integer(111), // ?4 → a
+    ];
+    for (i, value) in values.iter().enumerate() {
+        let idx = i + 1;
+        ins.bind_at(idx.try_into()?, value.clone());
+    }
+
+    // execute the insert (no rows returned)
+    loop {
+        match ins.step()? {
+            StepResult::IO => tmp_db.io.run_once()?,
+            StepResult::Done | StepResult::Interrupt => break,
+            StepResult::Busy => panic!("database busy"),
+            _ => {}
+        }
+    }
+
+    let mut sel = conn.prepare("select a, b, c, d from test;")?;
+    loop {
+        match sel.step()? {
+            StepResult::Row => {
+                let row = sel.row().unwrap();
+
+                // insert_index = 2
+                // A = 111
+                assert_eq!(
+                    row.get::<&OwnedValue>(0).unwrap(),
+                    &OwnedValue::Integer(111)
+                );
+                // insert_index = 4
+                // B = 444
+                assert_eq!(
+                    row.get::<&OwnedValue>(1).unwrap(),
+                    &OwnedValue::Integer(222)
+                );
+                // insert_index = 3
+                // C = 333
+                assert_eq!(
+                    row.get::<&OwnedValue>(2).unwrap(),
+                    &OwnedValue::Integer(333)
+                );
+                // insert_index = 1
+                // D = 999
+                assert_eq!(
+                    row.get::<&OwnedValue>(3).unwrap(),
+                    &OwnedValue::Integer(444)
+                );
+            }
+            StepResult::IO => tmp_db.io.run_once()?,
+            StepResult::Done | StepResult::Interrupt => break,
+            StepResult::Busy => panic!("database busy"),
+        }
+    }
+    assert_eq!(ins.parameters().count(), 4);
+    Ok(())
+}
+#[test]
+fn test_insert_parameter_multiple_no_remap() -> anyhow::Result<()> {
+    // ───────────────────────  schema  ──────────────────────────────
+    // Table             a     b     c     d
+    // INSERT lists:     a ,   b ,   c ,   d
+    // VALUES list:     ?1 ,  ?2 ,  ?3 ,  ?4
+    //
+    // Expected row on disk:  a = ?1 , b = ?2 , c = ?3 , d = ?4
+    //
+    // The row should be (111, 222, 333, 444)
+    // ───────────────────────────────────────────────────────────────
+
+    let tmp_db = TempDatabase::new_with_rusqlite(
+        "create table test (a integer, b integer, c integer, d integer);",
+    );
+    let conn = tmp_db.connect_limbo();
+    let mut ins = conn.prepare("insert into test (a,b,c,d) values (?, ?, ?, ?);")?;
+
+    let values = [
+        OwnedValue::Integer(111), // ?1 → a
+        OwnedValue::Integer(222), // ?2 → b
+        OwnedValue::Integer(333), // ?3 → c
+        OwnedValue::Integer(444), // ?4 → d
+    ];
+    for (i, value) in values.iter().enumerate() {
+        let idx = i + 1;
+        ins.bind_at(idx.try_into()?, value.clone());
+    }
+
+    // execute the insert (no rows returned)
+    loop {
+        match ins.step()? {
+            StepResult::IO => tmp_db.io.run_once()?,
+            StepResult::Done | StepResult::Interrupt => break,
+            StepResult::Busy => panic!("database busy"),
+            _ => {}
+        }
+    }
+
+    let mut sel = conn.prepare("select a, b, c, d from test;")?;
+    loop {
+        match sel.step()? {
+            StepResult::Row => {
+                let row = sel.row().unwrap();
+
+                // insert_index = 2
+                // A = 111
+                assert_eq!(
+                    row.get::<&OwnedValue>(0).unwrap(),
+                    &OwnedValue::Integer(111)
+                );
+                // insert_index = 4
+                // B = 444
+                assert_eq!(
+                    row.get::<&OwnedValue>(1).unwrap(),
+                    &OwnedValue::Integer(222)
+                );
+                // insert_index = 3
+                // C = 333
+                assert_eq!(
+                    row.get::<&OwnedValue>(2).unwrap(),
+                    &OwnedValue::Integer(333)
+                );
+                // insert_index = 1
+                // D = 999
+                assert_eq!(
+                    row.get::<&OwnedValue>(3).unwrap(),
+                    &OwnedValue::Integer(444)
+                );
+            }
+            StepResult::IO => tmp_db.io.run_once()?,
+            StepResult::Done | StepResult::Interrupt => break,
+            StepResult::Busy => panic!("database busy"),
+        }
+    }
+    assert_eq!(ins.parameters().count(), 4);
+    Ok(())
+}
+
+#[test]
+fn test_insert_parameter_multiple_row() -> anyhow::Result<()> {
+    // ───────────────────────  schema  ──────────────────────────────
+    // Table             a     b     c     d
+    // INSERT lists:     b ,   a ,   d ,   c
+    // VALUES list:     (?1 ,  ?2 ,  ?3 ,  ?4),
+    //                  (?5,   ?6,   ?7,   ?8);
+    //
+    // The row should be (111, 222, 333, 444), (555, 666, 777, 888)
+    // ───────────────────────────────────────────────────────────────
+
+    let tmp_db = TempDatabase::new_with_rusqlite(
+        "create table test (a integer, b integer, c integer, d integer);",
+    );
+    let conn = tmp_db.connect_limbo();
+    let mut ins = conn.prepare("insert into test (b,a,d,c) values (?, ?, ?, ?), (?, ?, ?, ?);")?;
+
+    let values = [
+        OwnedValue::Integer(222), // ?1 → b
+        OwnedValue::Integer(111), // ?2 → a
+        OwnedValue::Integer(444), // ?3 → d
+        OwnedValue::Integer(333), // ?4 → c
+        OwnedValue::Integer(666), // ?1 → b
+        OwnedValue::Integer(555), // ?2 → a
+        OwnedValue::Integer(888), // ?3 → d
+        OwnedValue::Integer(777), // ?4 → c
+    ];
+    for (i, value) in values.iter().enumerate() {
+        let idx = i + 1;
+        ins.bind_at(idx.try_into()?, value.clone());
+    }
+
+    // execute the insert (no rows returned)
+    loop {
+        match ins.step()? {
+            StepResult::IO => tmp_db.io.run_once()?,
+            StepResult::Done | StepResult::Interrupt => break,
+            StepResult::Busy => panic!("database busy"),
+            _ => {}
+        }
+    }
+
+    let mut sel = conn.prepare("select a, b, c, d from test;")?;
+    let mut i = 0;
+    loop {
+        match sel.step()? {
+            StepResult::Row => {
+                let row = sel.row().unwrap();
+
+                assert_eq!(
+                    row.get::<&OwnedValue>(0).unwrap(),
+                    &OwnedValue::Integer(if i == 0 { 111 } else { 555 })
+                );
+                assert_eq!(
+                    row.get::<&OwnedValue>(1).unwrap(),
+                    &OwnedValue::Integer(if i == 0 { 222 } else { 666 })
+                );
+                assert_eq!(
+                    row.get::<&OwnedValue>(2).unwrap(),
+                    &OwnedValue::Integer(if i == 0 { 333 } else { 777 })
+                );
+                assert_eq!(
+                    row.get::<&OwnedValue>(3).unwrap(),
+                    &OwnedValue::Integer(if i == 0 { 444 } else { 888 })
+                );
+                i += 1;
+            }
+            StepResult::IO => tmp_db.io.run_once()?,
+            StepResult::Done | StepResult::Interrupt => break,
+            StepResult::Busy => panic!("database busy"),
+        }
+    }
+    assert_eq!(ins.parameters().count(), 8);
+    Ok(())
+}