Repository: tursodatabase/limbo (rust)

Issue:
Update, Select and Delete queries can reorder constraints and emit improper parameter indexes for variables
# Repro:

```rust
#[test]
fn test_bind_parameters_update_query() -> anyhow::Result<()> {
    let tmp_db = TempDatabase::new_with_rusqlite("create table test (a integer, b text);");
    let conn = tmp_db.connect_limbo();
    let mut ins = conn.prepare("insert into test (a, b) values (3, 'test1');")?;
    loop {
        match ins.step()? {
            StepResult::IO => tmp_db.io.run_once()?,
            StepResult::Done | StepResult::Interrupt => break,
            StepResult::Busy => panic!("database busy"),
            _ => {}
        }
    }
    let mut ins = conn.prepare("update test set a = ? where b = ?;")?;

    let values = [OwnedValue::Integer(222), OwnedValue::build_text("test1")];
    for (i, value) in values.iter().enumerate() {
        let idx = i + 1;
        ins.bind_at(idx.try_into()?, value.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 from test;")?;
    loop {
        match sel.step()? {
            StepResult::Row => {
                let row = sel.row().unwrap();
                assert_eq!(
                    row.get::<&OwnedValue>(0).unwrap(),
                    &OwnedValue::Integer(222)
                );
                assert_eq!(
                    row.get::<&OwnedValue>(1).unwrap(),
                    &OwnedValue::build_text("test1"),
                );
            }
            StepResult::IO => tmp_db.io.run_once()?,
            StepResult::Done | StepResult::Interrupt => break,
            StepResult::Busy => panic!("database busy"),
        }
    }
    assert_eq!(ins.parameters().count(), 2);
    Ok(())
}

Pull request:
Redesign parameter binding in query translator
closes #1467 

## Example:

Previously as explained in #1449, our parameter binding wasn't working properly because we would essentially 
assign the first index of whatever was translated first
```console
limbo> create table t (id integer primary key, name text, age integer);
limbo> explain select * from t where name = ? and id > ? and age between ? and ?;
addr  opcode             p1    p2    p3    p4             p5  comment
----  -----------------  ----  ----  ----  -------------  --  -------
0     Init               0     20    0                    0   Start at 20
1     OpenRead           0     2     0                    0   table=t, root=2
2     Variable           1     4     0                    0   r[4]=parameter(1) # always 1
3     IsNull             4     19    0                    0   if (r[4]==NULL) goto 19
4     SeekGT             0     19    4                    0   key=[4..4]
5       Column           0     1     5                    0   r[5]=t.name
6       Variable         2     6     0                    0   r[6]=parameter(2) # always 2
7       Ne               5     6     18                   0   if r[5]!=r[6] goto 18
8       Variable         3     7     0                    0   r[7]=parameter(3) # etc...
9       Column           0     2     8                    0   r[8]=t.age
10      Gt               7     8     18                   0   if r[7]>r[8] goto 18
11      Column           0     2     9                    0   r[9]=t.age
12      Variable         4     10    0                    0   r[10]=parameter(4)
13      Gt               9     10    18                   0   if r[9]>r[10] goto 18
14      RowId            0     1     0                    0   r[1]=t.rowid
15      Column           0     1     2                    0   r[2]=t.name
16      Column           0     2     3                    0   r[3]=t.age
17      ResultRow        1     3     0                    0   output=r[1..3]
18    Next               0     5     0                    0
19    Halt               0     0     0                    0
20    Transaction        0     0     0                    0   write=false
21    Goto               0     1     0                    0
```


## Solution:
`rewrite_expr` currently is used to transform `true|false` to `1|0`, so it has been adapted to transform anonymous `Expr::Variable`s to named variables, inserting the appropriate index of the parameter by passing in a counter.

```rust
        ast::Expr::Variable(var) => {
            if var.is_empty() {
                // rewrite anonymous variables only, ensure that the `param_idx` starts at 1 and
                // all the expressions are rewritten in the order they come in the statement
                *expr = ast::Expr::Variable(format!("{}{param_idx}", PARAM_PREFIX));
                *param_idx += 1;
            }
            Ok(())
        }
``` 

# Corrected output: (notice the seek)

```console
limbo> explain select * from t where name = ? and id > ? and age between ? and ?;
addr  opcode             p1    p2    p3    p4             p5  comment
----  -----------------  ----  ----  ----  -------------  --  -------
0     Init               0     20    0                    0   Start at 20
1     OpenRead           0     2     0                    0   table=t, root=2
2     Variable           2     4     0                    0   r[4]=parameter(2)
3     IsNull             4     19    0                    0   if (r[4]==NULL) goto 19
4     SeekGT             0     19    4                    0   key=[4..4]
5       Column           0     1     5                    0   r[5]=t.name
6       Variable         1     6     0                    0   r[6]=parameter(1)
7       Ne               5     6     18                   0   if r[5]!=r[6] goto 18
8       Variable         3     7     0                    0   r[7]=parameter(3)
9       Column           0     2     8                    0   r[8]=t.age
10      Gt               7     8     18                   0   if r[7]>r[8] goto 18
11      Column           0     2     9                    0   r[9]=t.age
12      Variable         4     10    0                    0   r[10]=parameter(4)
13      Gt               9     10    18                   0   if r[9]>r[10] goto 18
14      RowId            0     1     0                    0   r[1]=t.rowid
15      Column           0     1     2                    0   r[2]=t.name
16      Column           0     2     3                    0   r[3]=t.age
17      ResultRow        1     3     0                    0   output=r[1..3]
18    Next               0     5     0                    0
19    Halt               0     0     0                    0
20    Transaction        0     0     0                    0   write=false
21    Goto               0     1     0                    0
```

## And a `Delete`:

```console
limbo> explain delete from t where name = ? and age > ? and id > ?;
addr  opcode             p1    p2    p3    p4             p5  comment
----  -----------------  ----  ----  ----  -------------  --  -------
0     Init               0     15    0                    0   Start at 15
1     OpenWrite          0     2     0                    0
2     Variable           3     1     0                    0   r[1]=parameter(3)
3     IsNull             1     14    0                    0   if (r[1]==NULL) goto 14
4     SeekGT             0     14    1                    0   key=[1..1]
5       Column           0     1     2                    0   r[2]=t.name
6       Variable         1     3     0                    0   r[3]=parameter(1)
7       Ne               2     3     13                   0   if r[2]!=r[3] goto 13
8       Column           0     2     4                    0   r[4]=t.age
9       Variable         2     5     0                    0   r[5]=parameter(2)
10      Le               4     5     13                   0   if r[4]<=r[5] goto 13
11      RowId            0     6     0                    0   r[6]=t.rowid
12      Delete           0     0     0                    0
13    Next               0     5     0                    0
14    Halt               0     0     0                    0
15    Transaction        0     1     0                    0   write=true
16    Goto               0     1     0                    0
```

Patch:
diff --git a/core/parameters.rs b/core/parameters.rs
index a3dab16c..bb29c500 100644
--- a/core/parameters.rs
+++ b/core/parameters.rs
@@ -1,6 +1,7 @@
-use super::ast;
 use std::num::NonZero;
 
+pub const PARAM_PREFIX: &str = "__param_";
+
 #[derive(Clone, Debug)]
 pub enum Parameter {
     Anonymous(NonZero<usize>),
@@ -24,52 +25,10 @@ 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 {
@@ -83,7 +42,6 @@ impl Parameters {
         Self {
             index: 1.try_into().unwrap(),
             list: vec![],
-            insert_ctx: None,
         }
     }
 
@@ -93,18 +51,6 @@ 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()),
@@ -132,15 +78,16 @@ impl Parameters {
 
     pub fn push(&mut self, name: impl AsRef<str>) -> NonZero<usize> {
         match name.as_ref() {
-            "" => {
+            param if param.is_empty() || param.starts_with(PARAM_PREFIX) => {
                 let index = self.next_index();
-                self.list.push(Parameter::Anonymous(index));
-                tracing::trace!("anonymous parameter at {index}");
-                if let Some(idx) = &self.insert_ctx {
-                    idx.get_insert_param_index().unwrap_or(index)
+                let use_idx = if let Some(idx) = param.strip_prefix(PARAM_PREFIX) {
+                    idx.parse().unwrap()
                 } else {
                     index
-                }
+                };
+                self.list.push(Parameter::Anonymous(use_idx));
+                tracing::trace!("anonymous parameter at {use_idx}");
+                use_idx
             }
             name if name.starts_with(['$', ':', '@', '#']) => {
                 match self
@@ -175,14 +122,3 @@ 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/insert.rs b/core/translate/insert.rs
index 233d623c..9250cc1c 100644
--- a/core/translate/insert.rs
+++ b/core/translate/insert.rs
@@ -22,6 +22,7 @@ use crate::{Result, SymbolTable, VirtualTable};
 
 use super::emitter::Resolver;
 use super::expr::{translate_expr_no_constant_opt, NoConstantOptReason};
+use super::optimizer::rewrite_expr;
 
 #[allow(clippy::too_many_arguments)]
 pub fn translate_insert(
@@ -31,7 +32,7 @@ pub fn translate_insert(
     on_conflict: &Option<ResolveType>,
     tbl_name: &QualifiedName,
     columns: &Option<DistinctNames>,
-    body: &InsertBody,
+    body: &mut InsertBody,
     _returning: &Option<Vec<ResultColumn>>,
     syms: &SymbolTable,
 ) -> Result<ProgramBuilder> {
@@ -100,14 +101,16 @@ pub fn translate_insert(
         .collect::<Vec<(&String, usize, usize)>>();
     let root_page = btree_table.root_page;
     let values = match body {
-        InsertBody::Select(select, _) => match &select.body.select.deref() {
-            OneSelect::Values(values) => values,
+        InsertBody::Select(ref mut select, _) => match select.body.select.as_mut() {
+            OneSelect::Values(ref mut values) => values,
             _ => todo!(),
         },
-        InsertBody::DefaultValues => &vec![vec![]],
+        InsertBody::DefaultValues => &mut 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 mut param_idx = 1;
+    for expr in values.iter_mut().flat_map(|v| v.iter_mut()) {
+        rewrite_expr(expr, &mut param_idx)?;
+    }
 
     let column_mappings = resolve_columns_for_insert(&table, columns, values)?;
     let index_col_mappings = resolve_indicies_for_insert(schema, table.as_ref(), &column_mappings)?;
@@ -154,9 +157,8 @@ pub fn translate_insert(
 
         program.resolve_label(start_offset_label, program.offset());
 
-        for (i, value) in values.iter().enumerate() {
+        for value in values.iter() {
             populate_column_registers(
-                i,
                 &mut program,
                 value,
                 &column_mappings,
@@ -194,7 +196,6 @@ pub fn translate_insert(
         });
 
         populate_column_registers(
-            0,
             &mut program,
             &values[0],
             &column_mappings,
@@ -586,7 +587,6 @@ 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],
@@ -610,14 +610,6 @@ 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,
@@ -681,8 +673,6 @@ 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);
@@ -701,7 +691,6 @@ fn translate_virtual_table_insert(
 
     let values_reg = program.alloc_registers(column_mappings.len());
     populate_column_registers(
-        0,
         program,
         &values[0],
         &column_mappings,
diff --git a/core/translate/mod.rs b/core/translate/mod.rs
index 492c33b1..fa0b6d34 100644
--- a/core/translate/mod.rs
+++ b/core/translate/mod.rs
@@ -198,7 +198,7 @@ pub fn translate(
                 or_conflict,
                 tbl_name,
                 columns,
-                body,
+                mut body,
                 returning,
             } = *insert;
             change_cnt_on = true;
@@ -209,7 +209,7 @@ pub fn translate(
                 &or_conflict,
                 &tbl_name,
                 &columns,
-                &body,
+                &mut body,
                 &returning,
                 syms,
             )?
diff --git a/core/translate/optimizer.rs b/core/translate/optimizer.rs
index 1409bd9a..38cdb634 100644
--- a/core/translate/optimizer.rs
+++ b/core/translate/optimizer.rs
@@ -3,6 +3,7 @@ use std::{cmp::Ordering, collections::HashMap, sync::Arc};
 use limbo_sqlite3_parser::ast::{self, Expr, SortOrder};
 
 use crate::{
+    parameters::PARAM_PREFIX,
     schema::{Index, IndexColumn, Schema},
     translate::plan::TerminationKey,
     types::SeekOp,
@@ -416,23 +417,24 @@ fn eliminate_constant_conditions(
 }
 
 fn rewrite_exprs_select(plan: &mut SelectPlan) -> Result<()> {
+    let mut param_count = 1;
     for rc in plan.result_columns.iter_mut() {
-        rewrite_expr(&mut rc.expr)?;
+        rewrite_expr(&mut rc.expr, &mut param_count)?;
     }
     for agg in plan.aggregates.iter_mut() {
-        rewrite_expr(&mut agg.original_expr)?;
+        rewrite_expr(&mut agg.original_expr, &mut param_count)?;
     }
     for cond in plan.where_clause.iter_mut() {
-        rewrite_expr(&mut cond.expr)?;
+        rewrite_expr(&mut cond.expr, &mut param_count)?;
     }
     if let Some(group_by) = &mut plan.group_by {
         for expr in group_by.exprs.iter_mut() {
-            rewrite_expr(expr)?;
+            rewrite_expr(expr, &mut param_count)?;
         }
     }
     if let Some(order_by) = &mut plan.order_by {
         for (expr, _) in order_by.iter_mut() {
-            rewrite_expr(expr)?;
+            rewrite_expr(expr, &mut param_count)?;
         }
     }
 
@@ -440,27 +442,29 @@ fn rewrite_exprs_select(plan: &mut SelectPlan) -> Result<()> {
 }
 
 fn rewrite_exprs_delete(plan: &mut DeletePlan) -> Result<()> {
+    let mut param_idx = 1;
     for cond in plan.where_clause.iter_mut() {
-        rewrite_expr(&mut cond.expr)?;
+        rewrite_expr(&mut cond.expr, &mut param_idx)?;
     }
     Ok(())
 }
 
 fn rewrite_exprs_update(plan: &mut UpdatePlan) -> Result<()> {
-    if let Some(rc) = plan.returning.as_mut() {
-        for rc in rc.iter_mut() {
-            rewrite_expr(&mut rc.expr)?;
-        }
-    }
+    let mut param_idx = 1;
     for (_, expr) in plan.set_clauses.iter_mut() {
-        rewrite_expr(expr)?;
+        rewrite_expr(expr, &mut param_idx)?;
     }
     for cond in plan.where_clause.iter_mut() {
-        rewrite_expr(&mut cond.expr)?;
+        rewrite_expr(&mut cond.expr, &mut param_idx)?;
     }
     if let Some(order_by) = &mut plan.order_by {
         for (expr, _) in order_by.iter_mut() {
-            rewrite_expr(expr)?;
+            rewrite_expr(expr, &mut param_idx)?;
+        }
+    }
+    if let Some(rc) = plan.returning.as_mut() {
+        for rc in rc.iter_mut() {
+            rewrite_expr(&mut rc.expr, &mut param_idx)?;
         }
     }
     Ok(())
@@ -1856,7 +1860,7 @@ pub fn try_extract_rowid_search_expression(
     }
 }
 
-fn rewrite_expr(expr: &mut ast::Expr) -> Result<()> {
+pub fn rewrite_expr(expr: &mut ast::Expr, param_idx: &mut usize) -> Result<()> {
     match expr {
         ast::Expr::Id(id) => {
             // Convert "true" and "false" to 1 and 0
@@ -1870,6 +1874,15 @@ fn rewrite_expr(expr: &mut ast::Expr) -> Result<()> {
             }
             Ok(())
         }
+        ast::Expr::Variable(var) => {
+            if var.is_empty() {
+                // rewrite anonymous variables only, ensure that the `param_idx` starts at 1 and
+                // all the expressions are rewritten in the order they come in the statement
+                *expr = ast::Expr::Variable(format!("{}{param_idx}", PARAM_PREFIX));
+                *param_idx += 1;
+            }
+            Ok(())
+        }
         ast::Expr::Between {
             lhs,
             not,
@@ -1884,9 +1897,9 @@ fn rewrite_expr(expr: &mut ast::Expr) -> Result<()> {
                 (ast::Operator::LessEquals, ast::Operator::LessEquals)
             };
 
-            rewrite_expr(start)?;
-            rewrite_expr(lhs)?;
-            rewrite_expr(end)?;
+            rewrite_expr(start, param_idx)?;
+            rewrite_expr(lhs, param_idx)?;
+            rewrite_expr(end, param_idx)?;
 
             let start = start.take_ownership();
             let lhs = lhs.take_ownership();
@@ -1912,7 +1925,7 @@ fn rewrite_expr(expr: &mut ast::Expr) -> Result<()> {
         }
         ast::Expr::Parenthesized(ref mut exprs) => {
             for subexpr in exprs.iter_mut() {
-                rewrite_expr(subexpr)?;
+                rewrite_expr(subexpr, param_idx)?;
             }
             let exprs = std::mem::take(exprs);
             *expr = ast::Expr::Parenthesized(exprs);
@@ -1920,20 +1933,56 @@ fn rewrite_expr(expr: &mut ast::Expr) -> Result<()> {
         }
         // Process other expressions recursively
         ast::Expr::Binary(lhs, _, rhs) => {
-            rewrite_expr(lhs)?;
-            rewrite_expr(rhs)?;
+            rewrite_expr(lhs, param_idx)?;
+            rewrite_expr(rhs, param_idx)?;
+            Ok(())
+        }
+        ast::Expr::Like {
+            lhs, rhs, escape, ..
+        } => {
+            rewrite_expr(lhs, param_idx)?;
+            rewrite_expr(rhs, param_idx)?;
+            if let Some(escape) = escape {
+                rewrite_expr(escape, param_idx)?;
+            }
+            Ok(())
+        }
+        ast::Expr::Case {
+            base,
+            when_then_pairs,
+            else_expr,
+        } => {
+            if let Some(base) = base {
+                rewrite_expr(base, param_idx)?;
+            }
+            for (lhs, rhs) in when_then_pairs.iter_mut() {
+                rewrite_expr(lhs, param_idx)?;
+                rewrite_expr(rhs, param_idx)?;
+            }
+            if let Some(else_expr) = else_expr {
+                rewrite_expr(else_expr, param_idx)?;
+            }
+            Ok(())
+        }
+        ast::Expr::InList { lhs, rhs, .. } => {
+            rewrite_expr(lhs, param_idx)?;
+            if let Some(rhs) = rhs {
+                for expr in rhs.iter_mut() {
+                    rewrite_expr(expr, param_idx)?;
+                }
+            }
             Ok(())
         }
         ast::Expr::FunctionCall { args, .. } => {
             if let Some(args) = args {
                 for arg in args.iter_mut() {
-                    rewrite_expr(arg)?;
+                    rewrite_expr(arg, param_idx)?;
                 }
             }
             Ok(())
         }
         ast::Expr::Unary(_, arg) => {
-            rewrite_expr(arg)?;
+            rewrite_expr(arg, param_idx)?;
             Ok(())
         }
         _ => Ok(()),
diff --git a/core/translate/plan.rs b/core/translate/plan.rs
index e34efb29..a422b6fe 100644
--- a/core/translate/plan.rs
+++ b/core/translate/plan.rs
@@ -379,7 +379,7 @@ impl SelectPlan {
             name: limbo_sqlite3_parser::ast::Id("count".to_string()),
             filter_over: None,
         };
-        let result_col_expr = &self.result_columns.get(0).unwrap().expr;
+        let result_col_expr = &self.result_columns.first().unwrap().expr;
         if *result_col_expr != count && *result_col_expr != count_star {
             return false;
         }


Test patch:
diff --git a/tests/integration/query_processing/test_read_path.rs b/tests/integration/query_processing/test_read_path.rs
index f7bbe5ca..fd34cb05 100644
--- a/tests/integration/query_processing/test_read_path.rs
+++ b/tests/integration/query_processing/test_read_path.rs
@@ -479,3 +479,283 @@ fn test_insert_parameter_multiple_row() -> anyhow::Result<()> {
     assert_eq!(ins.parameters().count(), 8);
     Ok(())
 }
+
+#[test]
+fn test_bind_parameters_update_query() -> anyhow::Result<()> {
+    let tmp_db = TempDatabase::new_with_rusqlite("create table test (a integer, b text);");
+    let conn = tmp_db.connect_limbo();
+    let mut ins = conn.prepare("insert into test (a, b) values (3, 'test1');")?;
+    loop {
+        match ins.step()? {
+            StepResult::IO => tmp_db.io.run_once()?,
+            StepResult::Done | StepResult::Interrupt => break,
+            StepResult::Busy => panic!("database busy"),
+            _ => {}
+        }
+    }
+    let mut ins = conn.prepare("update test set a = ? where b = ?;")?;
+    ins.bind_at(1.try_into()?, OwnedValue::Integer(222));
+    ins.bind_at(2.try_into()?, OwnedValue::build_text("test1"));
+    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 from test;")?;
+    loop {
+        match sel.step()? {
+            StepResult::Row => {
+                let row = sel.row().unwrap();
+                assert_eq!(
+                    row.get::<&OwnedValue>(0).unwrap(),
+                    &OwnedValue::Integer(222)
+                );
+                assert_eq!(
+                    row.get::<&OwnedValue>(1).unwrap(),
+                    &OwnedValue::build_text("test1"),
+                );
+            }
+            StepResult::IO => tmp_db.io.run_once()?,
+            StepResult::Done | StepResult::Interrupt => break,
+            StepResult::Busy => panic!("database busy"),
+        }
+    }
+    assert_eq!(ins.parameters().count(), 2);
+    Ok(())
+}
+
+#[test]
+fn test_bind_parameters_update_query_multiple_where() -> anyhow::Result<()> {
+    let tmp_db = TempDatabase::new_with_rusqlite(
+        "create table test (a integer, b text, c integer, d integer);",
+    );
+    let conn = tmp_db.connect_limbo();
+    let mut ins = conn.prepare("insert into test (a, b, c, d) values (3, 'test1', 4, 5);")?;
+    loop {
+        match ins.step()? {
+            StepResult::IO => tmp_db.io.run_once()?,
+            StepResult::Done | StepResult::Interrupt => break,
+            StepResult::Busy => panic!("database busy"),
+            _ => {}
+        }
+    }
+    let mut ins = conn.prepare("update test set a = ? where b = ? and c = 4 and d = ?;")?;
+    ins.bind_at(1.try_into()?, OwnedValue::Integer(222));
+    ins.bind_at(2.try_into()?, OwnedValue::build_text("test1"));
+    ins.bind_at(3.try_into()?, OwnedValue::Integer(5));
+    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();
+                assert_eq!(
+                    row.get::<&OwnedValue>(0).unwrap(),
+                    &OwnedValue::Integer(222)
+                );
+                assert_eq!(
+                    row.get::<&OwnedValue>(1).unwrap(),
+                    &OwnedValue::build_text("test1"),
+                );
+                assert_eq!(row.get::<&OwnedValue>(2).unwrap(), &OwnedValue::Integer(4));
+                assert_eq!(row.get::<&OwnedValue>(3).unwrap(), &OwnedValue::Integer(5));
+            }
+            StepResult::IO => tmp_db.io.run_once()?,
+            StepResult::Done | StepResult::Interrupt => break,
+            StepResult::Busy => panic!("database busy"),
+        }
+    }
+    assert_eq!(ins.parameters().count(), 3);
+    Ok(())
+}
+
+#[test]
+fn test_bind_parameters_update_rowid_alias() -> anyhow::Result<()> {
+    let tmp_db =
+        TempDatabase::new_with_rusqlite("CREATE TABLE test (id INTEGER PRIMARY KEY, name TEXT);");
+    let conn = tmp_db.connect_limbo();
+    let mut ins = conn.prepare("insert into test (id, name) values (1, 'test');")?;
+    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 id, name from test;")?;
+    loop {
+        match sel.step()? {
+            StepResult::Row => {
+                let row = sel.row().unwrap();
+                assert_eq!(row.get::<&OwnedValue>(0).unwrap(), &OwnedValue::Integer(1));
+                assert_eq!(
+                    row.get::<&OwnedValue>(1).unwrap(),
+                    &OwnedValue::build_text("test"),
+                );
+            }
+            StepResult::IO => tmp_db.io.run_once()?,
+            StepResult::Done | StepResult::Interrupt => break,
+            StepResult::Busy => panic!("database busy"),
+        }
+    }
+    let mut ins = conn.prepare("update test set name = ? where id = ?;")?;
+    ins.bind_at(1.try_into()?, OwnedValue::build_text("updated"));
+    ins.bind_at(2.try_into()?, OwnedValue::Integer(1));
+    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 id, name from test;")?;
+    loop {
+        match sel.step()? {
+            StepResult::Row => {
+                let row = sel.row().unwrap();
+                assert_eq!(row.get::<&OwnedValue>(0).unwrap(), &OwnedValue::Integer(1));
+                assert_eq!(
+                    row.get::<&OwnedValue>(1).unwrap(),
+                    &OwnedValue::build_text("updated"),
+                );
+            }
+            StepResult::IO => tmp_db.io.run_once()?,
+            StepResult::Done | StepResult::Interrupt => break,
+            StepResult::Busy => panic!("database busy"),
+        }
+    }
+    assert_eq!(ins.parameters().count(), 2);
+    Ok(())
+}
+
+#[test]
+fn test_bind_parameters_update_rowid_alias_seek_rowid() -> anyhow::Result<()> {
+    let tmp_db = TempDatabase::new_with_rusqlite(
+        "CREATE TABLE test (id INTEGER PRIMARY KEY, name TEXT, age integer);",
+    );
+    let conn = tmp_db.connect_limbo();
+    conn.execute("insert into test (id, name, age) values (1, 'test', 4);")?;
+    conn.execute("insert into test (id, name, age) values (2, 'test', 11);")?;
+
+    let mut sel = conn.prepare("select id, name, age 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 { 1 } else { 2 })
+                );
+                assert_eq!(
+                    row.get::<&OwnedValue>(1).unwrap(),
+                    &OwnedValue::build_text("test"),
+                );
+                assert_eq!(
+                    row.get::<&OwnedValue>(2).unwrap(),
+                    &OwnedValue::Integer(if i == 0 { 4 } else { 11 })
+                );
+            }
+            StepResult::IO => tmp_db.io.run_once()?,
+            StepResult::Done | StepResult::Interrupt => break,
+            StepResult::Busy => panic!("database busy"),
+        }
+        i += 1;
+    }
+    let mut ins = conn.prepare("update test set name = ? where id < ? AND age between ? and ?;")?;
+    ins.bind_at(1.try_into()?, OwnedValue::build_text("updated"));
+    ins.bind_at(2.try_into()?, OwnedValue::Integer(2));
+    ins.bind_at(3.try_into()?, OwnedValue::Integer(3));
+    ins.bind_at(4.try_into()?, OwnedValue::Integer(5));
+    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 name 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::build_text(if i == 0 { "updated" } else { "test" }),
+                );
+            }
+            StepResult::IO => tmp_db.io.run_once()?,
+            StepResult::Done | StepResult::Interrupt => break,
+            StepResult::Busy => panic!("database busy"),
+        }
+        i += 1;
+    }
+
+    assert_eq!(ins.parameters().count(), 4);
+    Ok(())
+}
+
+#[test]
+fn test_bind_parameters_delete_rowid_alias_seek_out_of_order() -> anyhow::Result<()> {
+    let tmp_db = TempDatabase::new_with_rusqlite(
+        "CREATE TABLE test (id INTEGER PRIMARY KEY, name TEXT, age integer);",
+    );
+    let conn = tmp_db.connect_limbo();
+    conn.execute("insert into test (id, name, age) values (1, 'correct', 4);")?;
+    conn.execute("insert into test (id, name, age) values (5, 'test', 11);")?;
+
+    let mut ins =
+        conn.prepare("delete from test where age between ? and ? AND id > ? AND name = ?;")?;
+    ins.bind_at(1.try_into()?, OwnedValue::Integer(10));
+    ins.bind_at(2.try_into()?, OwnedValue::Integer(12));
+    ins.bind_at(3.try_into()?, OwnedValue::Integer(4));
+    ins.bind_at(4.try_into()?, OwnedValue::build_text("test"));
+    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 name 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::build_text("correct"),
+                );
+            }
+            StepResult::IO => tmp_db.io.run_once()?,
+            StepResult::Done | StepResult::Interrupt => break,
+            StepResult::Busy => panic!("database busy"),
+        }
+        i += 1;
+    }
+    assert_eq!(i, 1);
+    assert_eq!(ins.parameters().count(), 4);
+    Ok(())
+}