toasty_driver_integration_suite/tests/
query_in_list.rs

1//! Tests for `IN`/`NOT IN` list filters.
2//!
3//! On PostgreSQL the engine rewrites `expr IN (...)` to `expr = ANY($1)` and
4//! `expr NOT IN (...)` to `expr <> ALL($1)`, binding the list as a single
5//! array parameter. Other SQL drivers continue to expand the list into one
6//! parameter per item. These tests cover both code paths via a single
7//! `Item` scenario, with each test exercising a different element type or
8//! list shape.
9//!
10//! Assertions on the issued driver op are gated on `bind_list_param +
11//! predicate_match_any`: when both are on (PostgreSQL) a single
12//! `Value::List` param typed `List(elem)` is expected; otherwise N scalar
13//! params each typed `elem`.
14
15use crate::prelude::*;
16
17use toasty_core::{
18    driver::{Capability, Operation, operation::QuerySql},
19    schema::db,
20    stmt,
21};
22
23#[driver_test(id(ID), scenario(crate::scenarios::in_list_item))]
24pub async fn in_list_string(t: &mut Test) -> Result<()> {
25    let mut db = setup(t).await;
26
27    toasty::create!(Item::[
28        { name: "a", n: 1 },
29        { name: "b", n: 2 },
30        { name: "c", n: 3 },
31        { name: "d", n: 4 },
32    ])
33    .exec(&mut db)
34    .await?;
35
36    t.log().clear();
37
38    let items = Item::filter(Item::fields().name().in_list(["a", "c"]))
39        .exec(&mut db)
40        .await?;
41
42    assert_eq!(items.len(), 2);
43    let mut names: Vec<_> = items.iter().map(|i| i.name.clone()).collect();
44    names.sort();
45    assert_eq!(names, vec!["a".to_string(), "c".to_string()]);
46
47    if t.capability().sql {
48        let elem = column_storage_ty(&db, "items", "name");
49        assert_in_list_bind(&pop_select(t), t.capability(), &elem, Some(2));
50    }
51
52    Ok(())
53}
54
55#[driver_test(id(ID), scenario(crate::scenarios::in_list_item))]
56pub async fn not_in_list_string(t: &mut Test) -> Result<()> {
57    let mut db = setup(t).await;
58
59    toasty::create!(Item::[
60        { name: "a", n: 1 },
61        { name: "b", n: 2 },
62        { name: "c", n: 3 },
63        { name: "d", n: 4 },
64    ])
65    .exec(&mut db)
66    .await?;
67
68    t.log().clear();
69
70    // `NOT IN [a, c]` → on PG this lowers to `name <> ALL($1)`.
71    let items = Item::filter(Item::fields().name().in_list(["a", "c"]).not())
72        .exec(&mut db)
73        .await?;
74
75    assert_eq!(items.len(), 2);
76    let mut names: Vec<_> = items.iter().map(|i| i.name.clone()).collect();
77    names.sort();
78    assert_eq!(names, vec!["b".to_string(), "d".to_string()]);
79
80    if t.capability().sql {
81        let elem = column_storage_ty(&db, "items", "name");
82        assert_in_list_bind(&pop_select(t), t.capability(), &elem, Some(2));
83    }
84
85    Ok(())
86}
87
88#[driver_test(id(ID), scenario(crate::scenarios::in_list_item))]
89pub async fn in_list_empty(t: &mut Test) -> Result<()> {
90    let mut db = setup(t).await;
91
92    toasty::create!(Item::[
93        { name: "a", n: 1 },
94        { name: "b", n: 2 },
95    ])
96    .exec(&mut db)
97    .await?;
98
99    t.log().clear();
100
101    // Empty list — `IN ()` is unsatisfiable; the simplifier folds the
102    // predicate to `false` and the engine short-circuits before issuing
103    // any driver op. If this regresses and an empty list reached
104    // extract_params, `finalize_ty` would panic on `List(Unknown)` (no
105    // column refinement runs on a folded branch).
106    let empty: Vec<String> = vec![];
107    let items = Item::filter(Item::fields().name().in_list(empty))
108        .exec(&mut db)
109        .await?;
110
111    assert!(items.is_empty(), "IN () must match nothing, got {items:?}");
112    assert!(
113        t.log().is_empty(),
114        "empty IN () should short-circuit before issuing any driver op",
115    );
116
117    Ok(())
118}
119
120#[driver_test(id(ID), scenario(crate::scenarios::in_list_item))]
121pub async fn in_list_i64_large(t: &mut Test) -> Result<()> {
122    // Regression guard: with PG's gate on, the engine must bind the whole
123    // list as a single `Value::List` param. With the gate off (SQLite,
124    // MySQL), the engine must still emit one bind per item.
125
126    let mut db = setup(t).await;
127
128    for n in 0..200_i64 {
129        Item::create()
130            .name(format!("item-{n}"))
131            .n(n)
132            .exec(&mut db)
133            .await?;
134    }
135
136    let needles: Vec<i64> = (0..200_i64).step_by(2).collect();
137    let expected = needles.len();
138
139    t.log().clear();
140
141    let items = Item::filter(Item::fields().n().in_list(needles))
142        .exec(&mut db)
143        .await?;
144
145    assert_eq!(items.len(), expected);
146
147    if t.capability().sql {
148        let elem = column_storage_ty(&db, "items", "n");
149        assert_in_list_bind(&pop_select(t), t.capability(), &elem, Some(expected));
150    }
151
152    Ok(())
153}
154
155#[driver_test(id(ID), scenario(crate::scenarios::in_list_item))]
156pub async fn in_list_id(t: &mut Test) -> Result<()> {
157    // Filter by the auto-generated id. Runs once per ID variant, so this
158    // exercises the PG driver's per-element-type dispatch for both `u64`
159    // (INT8) and `uuid::Uuid` (UUID) bind paths.
160
161    let mut db = setup(t).await;
162
163    let items = toasty::create!(Item::[
164        { name: "a", n: 1 },
165        { name: "b", n: 2 },
166        { name: "c", n: 3 },
167        { name: "d", n: 4 },
168    ])
169    .exec(&mut db)
170    .await?;
171
172    let needles = vec![items[0].id, items[2].id];
173
174    t.log().clear();
175
176    let found = Item::filter(Item::fields().id().in_list(needles))
177        .exec(&mut db)
178        .await?;
179
180    assert_eq!(found.len(), 2);
181    let mut names: Vec<_> = found.iter().map(|i| i.name.clone()).collect();
182    names.sort();
183    assert_eq!(names, vec!["a".to_string(), "c".to_string()]);
184
185    if t.capability().sql {
186        let elem = column_storage_ty(&db, "items", "id");
187        assert_in_list_bind(&pop_select(t), t.capability(), &elem, Some(2));
188    }
189
190    Ok(())
191}
192
193#[driver_test(id(ID), scenario(crate::scenarios::in_list_item))]
194pub async fn in_list_with_null(t: &mut Test) -> Result<()> {
195    // Exercises the PG driver's `Vec<Option<T>>` bind path: a `None` in the
196    // list maps to a SQL NULL inside the bound array.
197    //
198    // SQL semantics: `bio IN ('rusty', NULL)` is true when `bio = 'rusty'`
199    // and unknown (treated as false in WHERE) when `bio` is NULL or differs
200    // — the NULL in the list never matches anything.
201
202    let mut db = setup(t).await;
203
204    toasty::create!(Item::[
205        { name: "a", n: 1, bio: "rusty" },
206        { name: "b", n: 2 },
207        { name: "c", n: 3, bio: "databases" },
208    ])
209    .exec(&mut db)
210    .await?;
211
212    t.log().clear();
213
214    let needles: Vec<Option<String>> = vec![Some("rusty".to_string()), None];
215    let items = Item::filter(Item::fields().bio().in_list(needles))
216        .exec(&mut db)
217        .await?;
218
219    assert_eq!(items.len(), 1);
220    assert_eq!(items[0].name, "a");
221
222    if t.capability().sql {
223        // The per-item path's scalar count is implementation-defined here
224        // (the engine may drop the null operand at extract time), so pass
225        // `None` to skip the count check; element-type assertions still run.
226        let elem = column_storage_ty(&db, "items", "bio");
227        assert_in_list_bind(&pop_select(t), t.capability(), &elem, None);
228    }
229
230    Ok(())
231}
232
233// ============================================================================
234// Helpers
235// ============================================================================
236
237/// Pop ops from the log until the SELECT `QuerySql` is found.
238#[track_caller]
239fn pop_select(t: &mut Test) -> QuerySql {
240    while !t.log().is_empty() {
241        if let Operation::QuerySql(q) = t.log().pop_op()
242            && matches!(q.stmt, stmt::Statement::Query(_))
243        {
244            return q;
245        }
246    }
247    panic!("expected a SELECT QuerySql op in the log");
248}
249
250/// Look up the storage type of a column from the schema. Drives element-
251/// type assertions per driver, since the same model maps to different
252/// storage types (e.g. `String` → `Text` on PG/SQLite vs. `VarChar(191)`
253/// on MySQL).
254fn column_storage_ty(db: &toasty::Db, table_name: &str, column_name: &str) -> db::Type {
255    let schema = db.schema();
256    let table = schema
257        .db
258        .tables
259        .iter()
260        .find(|t| t.name == table_name || t.name.ends_with(table_name))
261        .unwrap_or_else(|| panic!("table '{table_name}' not in schema"));
262    table
263        .columns
264        .iter()
265        .find(|c| c.name == column_name)
266        .unwrap_or_else(|| panic!("column '{column_name}' not in table '{table_name}'"))
267        .storage_ty
268        .clone()
269}
270
271/// Assert that an `IN`-list query was bound according to the driver's
272/// capabilities. With both `bind_list_param` and `predicate_match_any` on
273/// (PostgreSQL), expects a single `Value::List` param typed
274/// `List(elem)`. Otherwise expects `expected_scalars` scalar params, each
275/// typed `elem`.
276#[track_caller]
277fn assert_in_list_bind(
278    query: &QuerySql,
279    cap: &Capability,
280    elem: &db::Type,
281    expected_scalars: Option<usize>,
282) {
283    if cap.bind_list_param && cap.predicate_match_any {
284        let expected_ty = db::Type::List(Box::new(elem.clone()));
285        assert_struct!(query, {
286            params: [{
287                value: stmt::Value::List(_),
288                ty: == expected_ty,
289            }],
290        });
291    } else {
292        if let Some(n) = expected_scalars {
293            assert_eq!(query.params.len(), n);
294        }
295        for (i, p) in query.params.iter().enumerate() {
296            assert_eq!(
297                &p.ty, elem,
298                "params[{i}].ty should be the column storage type"
299            );
300        }
301    }
302}
toasty_driver_integration_suite/tests/query_in_list.rs

toasty_driver_integration_suite/tests/
query_in_list.rs
