toasty_driver_integration_suite/tests/

crud_query.rs

//! Test querying models with various filters and constraints

use crate::prelude::*;
use toasty_core::{
    driver::Operation,
    stmt::{Expr, ExprSet, Statement},
};

#[driver_test(id(ID), scenario(crate::scenarios::two_models))]
pub async fn query_index_eq(t: &mut Test) -> Result<()> {
    let mut db = setup(t).await;

    // Create a few users
    for name in ["one", "two", "three"] {
        User::create().name(name).exec(&mut db).await?;
    }

    let users = User::filter_by_name("one").exec(&mut db).await?;

    assert_eq!(1, users.len());
    assert_eq!("one", users[0].name);

    // Create a second user named "one"

    User::create().name("one").exec(&mut db).await?;

    let users = User::filter_by_name("one").exec(&mut db).await?;

    assert_eq!(2, users.len());
    assert!(users.iter().all(|u| u.name == "one"));
    Ok(())
}

#[driver_test(id(ID))]
pub async fn query_partition_key_string_eq(test: &mut Test) -> Result<()> {
    #[derive(Debug, toasty::Model)]
    #[key(partition = league, local = name)]
    struct Team {
        league: String,

        name: String,

        founded: i64,
    }

    let mut db = test.setup_db(models!(Team)).await;

    // Create some teams
    for (league, name, founded) in [
        ("MLS", "Portland Timbers", 2009),
        ("MLS", "Seattle Sounders FC", 2007),
        ("MLS", "Vancouver Whitecaps FC", 2009),
        ("MLS", "Los Angeles Football Club", 2014),
        ("MLS", "San Jose Earthquakes", 1994),
        ("MLS", "LA Galaxy", 1994),
        ("EPL", "Arsenal", 1886),
        ("EPL", "Chelsea", 1905),
        ("EPL", "Manchester United", 1878),
        ("EPL", "Tottenham", 1882),
        ("La Liga", "FC Barcelona", 1899),
        ("La Liga", "Girona FC", 1930),
        ("La Liga", "Real Madrid", 1902),
        ("La Liga", "Atlético Madrid", 1903),
    ]
    .into_iter()
    {
        Team::create()
            .league(league)
            .name(name)
            .founded(founded)
            .exec(&mut db)
            .await?;
    }

    // Query on the partition key only
    let teams = Team::filter(Team::fields().league().eq("EPL"))
        .exec(&mut db)
        .await?;

    let mut names = teams.iter().map(|team| &team.name).collect::<Vec<_>>();
    names.sort();

    assert_eq!(
        names,
        ["Arsenal", "Chelsea", "Manchester United", "Tottenham"]
    );

    // Query on the partition key and local key
    let teams = Team::filter(
        Team::fields()
            .league()
            .eq("MLS")
            .and(Team::fields().name().eq("Portland Timbers")),
    )
    .exec(&mut db)
    .await?;

    let mut names = teams.iter().map(|team| &team.name).collect::<Vec<_>>();
    names.sort();

    assert_eq!(names, ["Portland Timbers"]);

    // Query on the partition key and a non-index field
    let teams = Team::filter(
        Team::fields()
            .league()
            .eq("MLS")
            .and(Team::fields().founded().eq(2009)),
    )
    .exec(&mut db)
    .await?;

    let mut names = teams.iter().map(|team| &team.name).collect::<Vec<_>>();
    names.sort();

    assert_eq!(names, ["Portland Timbers", "Vancouver Whitecaps FC"]);

    // Query on the partition key, local key, and a non-index field with a match
    let teams = Team::filter(
        Team::fields()
            .league()
            .eq("MLS")
            .and(Team::fields().founded().eq(2009))
            .and(Team::fields().name().eq("Portland Timbers")),
    )
    .exec(&mut db)
    .await?;

    assert_eq!(1, teams.len());
    assert!(teams.iter().all(|team| team.founded == 2009));

    let mut names = teams.iter().map(|team| &team.name).collect::<Vec<_>>();
    names.sort();

    assert_eq!(names, ["Portland Timbers"]);

    // Query on the partition key, local key, and a non-index field without a match
    let teams = Team::filter(
        Team::fields()
            .league()
            .eq("MLS")
            .and(Team::fields().founded().eq(2009))
            .and(Team::fields().name().eq("LA Galaxy")),
    )
    .exec(&mut db)
    .await?;

    assert!(teams.is_empty());
    Ok(())
}

#[driver_test(id(ID))]
pub async fn query_local_key_cmp(test: &mut Test) -> Result<()> {
    #[derive(Debug, toasty::Model)]
    #[key(partition = kind, local = timestamp)]
    struct Event {
        kind: String,

        timestamp: i64,
    }

    let mut db = test.setup_db(models!(Event)).await;

    // Create a bunch of entries
    for (kind, ts) in [
        ("info", 0),
        ("warn", 1),
        ("info", 2),
        ("warn", 3),
        ("info", 4),
        ("warn", 5),
        ("info", 6),
        ("warn", 7),
        ("info", 8),
        ("warn", 9),
        ("info", 10),
        ("warn", 11),
        ("info", 12),
        ("warn", 13),
        ("info", 14),
        ("warn", 15),
        ("info", 16),
        ("warn", 17),
        ("info", 18),
        ("warn", 19),
    ] {
        Event::create()
            .kind(kind)
            .timestamp(ts)
            .exec(&mut db)
            .await?;
    }

    let events: Vec<_> = Event::filter_by_kind("info")
        .filter(Event::fields().timestamp().ne(10))
        .exec(&mut db)
        .await?;

    assert_eq_unordered!(
        events.iter().map(|event| event.timestamp),
        [&0, &2, &4, &6, &8, &12, &14, &16, &18,]
    );

    let events: Vec<_> = Event::filter_by_kind("info")
        .filter(Event::fields().timestamp().gt(10))
        .exec(&mut db)
        .await?;

    assert_eq_unordered!(
        events.iter().map(|event| event.timestamp),
        [&12, &14, &16, &18,]
    );

    let events: Vec<_> = Event::filter_by_kind("info")
        .filter(Event::fields().timestamp().ge(10))
        .exec(&mut db)
        .await?;

    assert_eq_unordered!(
        events.iter().map(|event| event.timestamp),
        [&10, &12, &14, &16, &18,]
    );

    let events: Vec<_> = Event::filter_by_kind("info")
        .filter(Event::fields().timestamp().lt(10))
        .exec(&mut db)
        .await?;

    assert_eq_unordered!(
        events.iter().map(|event| event.timestamp),
        [&0, &2, &4, &6, &8]
    );

    let events: Vec<_> = Event::filter_by_kind("info")
        .filter(Event::fields().timestamp().le(10))
        .exec(&mut db)
        .await?;

    assert_eq_unordered!(
        events.iter().map(|event| event.timestamp),
        [&0, &2, &4, &6, &8, &10]
    );
    Ok(())
}

#[driver_test(id(ID), scenario(crate::scenarios::user_with_age))]
pub async fn set_filter_overwrites(test: &mut Test) -> Result<()> {
    let mut db = setup(test).await;

    toasty::create!(User::[
        { name: "Alice", age: 30 },
        { name: "Bob", age: 20 },
    ])
    .exec(&mut db)
    .await?;

    // `set_filter` replaces the existing filter rather than AND-combining it.
    // If it combined, `name = "Alice" AND name = "Bob"` would match nothing.
    use toasty::stmt::{List, Query};
    let mut q = Query::<List<User>>::all().filter(User::fields().name().eq("Alice"));
    q.set_filter(User::fields().name().eq("Bob"));

    let users = q.exec(&mut db).await?;

    assert_eq!(1, users.len());
    assert_eq!("Bob", users[0].name);
    Ok(())
}

#[driver_test(id(ID), scenario(crate::scenarios::user_with_age))]
pub async fn query_or_basic(test: &mut Test) -> Result<()> {
    let mut db = setup(test).await;
    let _name_column = db.schema().table_for(User::id()).columns[1].id;
    let _age_column = db.schema().table_for(User::id()).columns[2].id;

    // Create some users
    for (name, age) in [("Alice", 25), ("Bob", 30), ("Charlie", 35), ("Diana", 40)] {
        User::create().name(name).age(age).exec(&mut db).await?;
    }

    // Clear the log after setup
    test.log().clear();

    // Query with OR condition: name = "Alice" OR age = 35
    let result = User::filter(
        User::fields()
            .name()
            .eq("Alice")
            .or(User::fields().age().eq(35)),
    )
    .exec(&mut db)
    .await;

    if test.capability().sql {
        let users = result?;
        assert_eq!(2, users.len());
        let mut names: Vec<_> = users.iter().map(|u| u.name.as_str()).collect();
        names.sort();
        assert_eq!(names, ["Alice", "Charlie"]);

        // Verify the driver operation contains the expected OR filter
        let (op, _) = test.log().pop();

        assert_struct!(&op, Operation::QuerySql({
            stmt: Statement::Query({
                body: ExprSet::Select({
                    filter.expr: Some(Expr::Or({
                        // TODO: assert_struct! needs a set matcher
                        /*
                        operands: [
                            Expr::BinaryOp({
                                op: BinaryOp::Eq,
                                *lhs: == Expr::column(age_column),
                                *rhs: Expr::Value(Value::I64(35)),
                            }),
                            Expr::BinaryOp({
                                op: BinaryOp::Eq,
                                *lhs: == Expr::column(name_column),
                                *rhs: Expr::Value(Value::String("Alice")),
                            }),
                        ],
                        */
                    })),
                }),
            }),
        }));
    } else if test.capability().scan {
        let users = result?;
        assert_eq!(2, users.len());
        let mut names: Vec<_> = users.iter().map(|u| u.name.as_str()).collect();
        names.sort();
        assert_eq!(names, ["Alice", "Charlie"]);
    } else {
        assert!(
            result.is_err(),
            "Expected error for OR query without key condition on non-SQL database"
        );
    }
    Ok(())
}

#[driver_test(id(ID), scenario(crate::scenarios::user_with_age))]
pub async fn query_or_multiple(test: &mut Test) -> Result<()> {
    let mut db = setup(test).await;

    // Create some users
    for (name, age) in [("Alice", 25), ("Bob", 30), ("Charlie", 35), ("Diana", 40)] {
        User::create().name(name).age(age).exec(&mut db).await?;
    }

    // Query with multiple OR conditions: name = "Alice" OR age = 35 OR age = 40
    let result = User::filter(
        User::fields()
            .name()
            .eq("Alice")
            .or(User::fields().age().eq(35))
            .or(User::fields().age().eq(40)),
    )
    .exec(&mut db)
    .await;

    if test.capability().sql || test.capability().scan {
        let users = result?;
        assert_eq!(3, users.len());
        let mut names: Vec<_> = users.iter().map(|u| u.name.as_str()).collect();
        names.sort();
        assert_eq!(names, ["Alice", "Charlie", "Diana"]);
    } else {
        assert!(
            result.is_err(),
            "Expected error for OR query without key condition on non-SQL database"
        );
    }
    Ok(())
}

#[driver_test(id(ID), scenario(crate::scenarios::user_with_active))]
pub async fn query_or_and_combined(test: &mut Test) -> Result<()> {
    let mut db = setup(test).await;

    // Create some users
    for (name, age, active) in [
        ("Alice", 25, true),
        ("Bob", 30, false),
        ("Charlie", 35, true),
        ("Diana", 40, false),
        ("Eve", 25, false),
    ] {
        User::create()
            .name(name)
            .age(age)
            .active(active)
            .exec(&mut db)
            .await?;
    }

    // Query with OR and AND: (name = "Alice" OR age = 35) AND active = true
    let result = User::filter(
        User::fields()
            .name()
            .eq("Alice")
            .or(User::fields().age().eq(35))
            .and(User::fields().active().eq(true)),
    )
    .exec(&mut db)
    .await;

    if test.capability().sql || test.capability().scan {
        let users = result?;
        assert_eq!(2, users.len());
        let mut names: Vec<_> = users.iter().map(|u| u.name.as_str()).collect();
        names.sort();
        assert_eq!(names, ["Alice", "Charlie"]);
    } else {
        assert!(
            result.is_err(),
            "Expected error for OR query without key condition on non-SQL database"
        );
    }
    Ok(())
}

#[driver_test(id(ID))]
pub async fn query_or_with_index(test: &mut Test) -> Result<()> {
    #[derive(Debug, toasty::Model)]
    #[key(partition = team, local = name)]
    struct Player {
        team: String,

        name: String,

        #[allow(dead_code)]
        position: String,

        #[allow(dead_code)]
        number: i64,
    }

    let mut db = test.setup_db(models!(Player)).await;

    // Create some players on different teams
    for (team, name, position, number) in [
        ("Timbers", "Diego Valeri", "Midfielder", 8),
        ("Timbers", "Darlington Nagbe", "Midfielder", 6),
        ("Timbers", "Diego Chara", "Midfielder", 21),
        ("Timbers", "Fanendo Adi", "Forward", 9),
        ("Timbers", "Adam Kwarasey", "Goalkeeper", 1),
        ("Sounders", "Clint Dempsey", "Forward", 2),
        ("Sounders", "Obafemi Martins", "Forward", 9),
        ("Sounders", "Osvaldo Alonso", "Midfielder", 6),
    ] {
        Player::create()
            .team(team)
            .name(name)
            .position(position)
            .number(number)
            .exec(&mut db)
            .await?;
    }

    // Query with partition key AND OR conditions on non-indexed fields
    // This should work on both SQL and DynamoDB
    let players = Player::filter(
        Player::fields().team().eq("Timbers").and(
            Player::fields()
                .position()
                .eq("Forward")
                .or(Player::fields().position().eq("Goalkeeper")),
        ),
    )
    .exec(&mut db)
    .await?;

    assert_eq!(2, players.len());
    let mut names: Vec<_> = players.iter().map(|p| p.name.as_str()).collect();
    names.sort();
    assert_eq!(names, ["Adam Kwarasey", "Fanendo Adi"]);

    // Query with partition key AND more complex OR conditions
    let players = Player::filter(
        Player::fields().team().eq("Timbers").and(
            Player::fields()
                .number()
                .eq(8)
                .or(Player::fields().number().eq(21))
                .or(Player::fields().number().eq(9)),
        ),
    )
    .exec(&mut db)
    .await?;

    assert_eq!(3, players.len());
    let mut names: Vec<_> = players.iter().map(|p| p.name.as_str()).collect();
    names.sort();
    assert_eq!(names, ["Diego Chara", "Diego Valeri", "Fanendo Adi"]);
    Ok(())
}

#[driver_test(id(ID))]
pub async fn query_or_on_partition_key(test: &mut Test) -> Result<()> {
    // OR directly on the partition key of a composite primary key.
    //
    // SQL: plain OR in WHERE clause.
    // DynamoDB: fan-out into two QueryPk calls, one per partition key value,
    //           because KeyConditionExpression does not support OR.
    #[derive(Debug, toasty::Model)]
    #[key(partition = team, local = name)]
    struct Player {
        team: String,

        name: String,

        #[allow(dead_code)]
        position: String,
    }

    let mut db = test.setup_db(models!(Player)).await;

    for (team, name, position) in [
        ("Timbers", "Diego Valeri", "Midfielder"),
        ("Timbers", "Fanendo Adi", "Forward"),
        ("Sounders", "Clint Dempsey", "Forward"),
        ("Sounders", "Osvaldo Alonso", "Midfielder"),
        ("Galaxy", "Robbie Keane", "Forward"),
    ] {
        Player::create()
            .team(team)
            .name(name)
            .position(position)
            .exec(&mut db)
            .await?;
    }

    // team = "Timbers" OR team = "Sounders"
    let players = Player::filter(
        Player::fields()
            .team()
            .eq("Timbers")
            .or(Player::fields().team().eq("Sounders")),
    )
    .exec(&mut db)
    .await?;

    assert_eq!(4, players.len());
    let mut names: Vec<_> = players.iter().map(|p| p.name.as_str()).collect();
    names.sort();
    assert_eq!(
        names,
        [
            "Clint Dempsey",
            "Diego Valeri",
            "Fanendo Adi",
            "Osvaldo Alonso"
        ]
    );
    Ok(())
}

#[driver_test(id(ID))]
pub async fn query_or_on_composite_pk(test: &mut Test) -> Result<()> {
    // OR where each branch fully specifies all composite primary key columns.
    //
    // SQL: plain OR in WHERE clause.
    // DynamoDB: routes to GetByKey (BatchGetItem) because all key columns
    //           have exact equality predicates, so key_values is populated.
    #[derive(Debug, toasty::Model)]
    #[key(partition = team, local = name)]
    struct Player {
        team: String,

        name: String,

        #[allow(dead_code)]
        position: String,
    }

    let mut db = test.setup_db(models!(Player)).await;

    for (team, name, position) in [
        ("Timbers", "Diego Valeri", "Midfielder"),
        ("Timbers", "Fanendo Adi", "Forward"),
        ("Sounders", "Clint Dempsey", "Forward"),
        ("Sounders", "Osvaldo Alonso", "Midfielder"),
    ] {
        Player::create()
            .team(team)
            .name(name)
            .position(position)
            .exec(&mut db)
            .await?;
    }

    // (team = "Timbers" AND name = "Diego Valeri") OR (team = "Sounders" AND name = "Clint Dempsey")
    let players = Player::filter(
        Player::fields()
            .team()
            .eq("Timbers")
            .and(Player::fields().name().eq("Diego Valeri"))
            .or(Player::fields()
                .team()
                .eq("Sounders")
                .and(Player::fields().name().eq("Clint Dempsey"))),
    )
    .exec(&mut db)
    .await?;

    assert_eq!(2, players.len());
    let mut names: Vec<_> = players.iter().map(|p| p.name.as_str()).collect();
    names.sort();
    assert_eq!(names, ["Clint Dempsey", "Diego Valeri"]);
    Ok(())
}

#[driver_test(id(ID))]
pub async fn query_or_with_comparisons(test: &mut Test) -> Result<()> {
    #[derive(Debug, toasty::Model)]
    #[key(partition = team, local = name)]
    struct Player {
        team: String,

        name: String,

        #[allow(dead_code)]
        position: String,

        #[allow(dead_code)]
        number: i64,
    }

    let mut db = test.setup_db(models!(Player)).await;

    // Create some players on different teams
    for (team, name, position, number) in [
        ("Timbers", "Diego Valeri", "Midfielder", 8),
        ("Timbers", "Darlington Nagbe", "Midfielder", 6),
        ("Timbers", "Diego Chara", "Midfielder", 21),
        ("Timbers", "Fanendo Adi", "Forward", 9),
        ("Timbers", "Adam Kwarasey", "Goalkeeper", 1),
        ("Sounders", "Clint Dempsey", "Forward", 2),
        ("Sounders", "Obafemi Martins", "Forward", 9),
        ("Sounders", "Osvaldo Alonso", "Midfielder", 6),
    ] {
        Player::create()
            .team(team)
            .name(name)
            .position(position)
            .number(number)
            .exec(&mut db)
            .await?;
    }

    // Query with partition key AND OR conditions using comparisons (not equality)
    // This won't be optimized to IN list, so tests actual OR expression handling
    // Using gt/lt instead of ge/le to avoid boundary condition confusion
    let players = Player::filter(
        Player::fields().team().eq("Timbers").and(
            Player::fields()
                .number()
                .gt(20)
                .or(Player::fields().number().lt(2)),
        ),
    )
    .exec(&mut db)
    .await?;

    assert_eq!(2, players.len());
    let mut names: Vec<_> = players.iter().map(|p| p.name.as_str()).collect();
    names.sort();
    assert_eq!(names, ["Adam Kwarasey", "Diego Chara"]);
    Ok(())
}

#[driver_test(id(ID), requires(scan), scenario(crate::scenarios::user_with_active))]
pub async fn query_arbitrary_constraint(test: &mut Test) -> Result<()> {
    let mut db = setup(test).await;

    // Create a bunch of entries. kind -> name, timestamp -> age; active is unused.
    for (kind, ts) in [
        ("info", 0),
        ("warn", 1),
        ("info", 2),
        ("warn", 3),
        ("info", 4),
        ("warn", 5),
        ("info", 6),
        ("warn", 7),
        ("info", 8),
        ("warn", 9),
        ("info", 10),
        ("warn", 11),
        ("info", 12),
        ("warn", 13),
        ("info", 14),
        ("warn", 15),
        ("info", 16),
        ("warn", 17),
        ("info", 18),
        ("warn", 19),
    ] {
        User::create()
            .name(kind)
            .age(ts)
            .active(true)
            .exec(&mut db)
            .await?;
    }

    let events: Vec<_> = User::filter(User::fields().age().gt(12))
        .exec(&mut db)
        .await?;

    assert_eq_unordered!(
        events.iter().map(|event| event.age),
        [&13, &14, &15, &16, &17, &18, &19,]
    );

    let events: Vec<_> = User::filter(
        User::fields()
            .age()
            .gt(12)
            .and(User::fields().name().ne("info")),
    )
    .exec(&mut db)
    .await?;

    assert!(events.iter().all(|event| event.name != "info"));

    assert_eq_unordered!(events.iter().map(|event| event.age), [&13, &15, &17, &19,]);

    let events: Vec<_> = User::filter(
        User::fields()
            .name()
            .eq("info")
            .and(User::fields().age().ne(10)),
    )
    .exec(&mut db)
    .await?;

    assert_eq_unordered!(
        events.iter().map(|event| event.age),
        [&0, &2, &4, &6, &8, &12, &14, &16, &18,]
    );

    let events: Vec<_> = User::filter(
        User::fields()
            .name()
            .eq("info")
            .and(User::fields().age().gt(10)),
    )
    .exec(&mut db)
    .await?;

    assert_eq_unordered!(events.iter().map(|event| event.age), [&12, &14, &16, &18,]);

    let events: Vec<_> = User::filter(
        User::fields()
            .name()
            .eq("info")
            .and(User::fields().age().ge(10)),
    )
    .exec(&mut db)
    .await?;

    assert_eq_unordered!(
        events.iter().map(|event| event.age),
        [&10, &12, &14, &16, &18,]
    );

    let events: Vec<_> = User::filter(
        User::fields()
            .name()
            .eq("info")
            .and(User::fields().age().lt(10)),
    )
    .exec(&mut db)
    .await?;

    assert_eq_unordered!(events.iter().map(|event| event.age), [&0, &2, &4, &6, &8]);

    let events: Vec<_> = User::filter(
        User::fields()
            .name()
            .eq("info")
            .and(User::fields().age().le(10)),
    )
    .exec(&mut db)
    .await?;

    assert_eq_unordered!(
        events.iter().map(|event| event.age),
        [&0, &2, &4, &6, &8, &10]
    );
    Ok(())
}

#[driver_test(id(ID), scenario(crate::scenarios::user_with_age))]
pub async fn query_not_basic(test: &mut Test) -> Result<()> {
    let mut db = setup(test).await;

    // Create some users
    for (name, age) in [("Alice", 25), ("Bob", 30), ("Charlie", 35), ("Diana", 40)] {
        User::create().name(name).age(age).exec(&mut db).await?;
    }

    // Clear the log after setup
    test.log().clear();

    // Query with NOT condition: NOT (name = "Alice")
    let result = User::filter(User::fields().name().eq("Alice").not())
        .exec(&mut db)
        .await;

    if test.capability().sql || test.capability().scan {
        let users = result?;
        assert_eq!(3, users.len());
        let mut names: Vec<_> = users.iter().map(|u| u.name.as_str()).collect();
        names.sort();
        assert_eq!(names, ["Bob", "Charlie", "Diana"]);
    } else {
        assert!(
            result.is_err(),
            "Expected error for NOT query without key condition on non-SQL database"
        );
    }
    Ok(())
}

#[driver_test(id(ID), scenario(crate::scenarios::user_with_active))]
pub async fn query_not_and_combined(test: &mut Test) -> Result<()> {
    let mut db = setup(test).await;

    // Create some users
    for (name, age, active) in [
        ("Alice", 25, true),
        ("Bob", 30, false),
        ("Charlie", 35, true),
        ("Diana", 40, false),
        ("Eve", 25, false),
    ] {
        User::create()
            .name(name)
            .age(age)
            .active(active)
            .exec(&mut db)
            .await?;
    }

    // Query with NOT combined with AND: active = true AND NOT (age = 25)
    // Should return only Charlie (active=true, age=35)
    let result = User::filter(
        User::fields()
            .active()
            .eq(true)
            .and(User::fields().age().eq(25).not()),
    )
    .exec(&mut db)
    .await;

    if test.capability().sql || test.capability().scan {
        let users = result?;
        assert_eq!(1, users.len());
        assert_eq!("Charlie", users[0].name);
    } else {
        assert!(
            result.is_err(),
            "Expected error for NOT query without key condition on non-SQL database"
        );
    }
    Ok(())
}

#[driver_test(id(ID), scenario(crate::scenarios::user_with_age))]
pub async fn query_not_or_combined(test: &mut Test) -> Result<()> {
    let mut db = setup(test).await;

    // Create some users
    for (name, age) in [("Alice", 25), ("Bob", 30), ("Charlie", 35), ("Diana", 40)] {
        User::create().name(name).age(age).exec(&mut db).await?;
    }

    // Query with NOT combined with OR: NOT (name = "Alice" OR name = "Bob")
    // Should return Charlie and Diana
    let result = User::filter(
        User::fields()
            .name()
            .eq("Alice")
            .or(User::fields().name().eq("Bob"))
            .not(),
    )
    .exec(&mut db)
    .await;

    if test.capability().sql || test.capability().scan {
        let users = result?;
        assert_eq!(2, users.len());
        let mut names: Vec<_> = users.iter().map(|u| u.name.as_str()).collect();
        names.sort();
        assert_eq!(names, ["Charlie", "Diana"]);
    } else {
        assert!(
            result.is_err(),
            "Expected error for NOT query without key condition on non-SQL database"
        );
    }
    Ok(())
}

#[driver_test(id(ID))]
pub async fn query_not_with_index(test: &mut Test) -> Result<()> {
    #[derive(Debug, toasty::Model)]
    #[key(partition = team, local = name)]
    struct Player {
        team: String,

        name: String,

        #[allow(dead_code)]
        position: String,

        #[allow(dead_code)]
        number: i64,
    }

    let mut db = test.setup_db(models!(Player)).await;

    // Create some players
    for (team, name, position, number) in [
        ("Timbers", "Diego Valeri", "Midfielder", 8),
        ("Timbers", "Darlington Nagbe", "Midfielder", 6),
        ("Timbers", "Diego Chara", "Midfielder", 21),
        ("Timbers", "Fanendo Adi", "Forward", 9),
        ("Timbers", "Adam Kwarasey", "Goalkeeper", 1),
        ("Sounders", "Clint Dempsey", "Forward", 2),
        ("Sounders", "Obafemi Martins", "Forward", 9),
        ("Sounders", "Osvaldo Alonso", "Midfielder", 6),
    ] {
        Player::create()
            .team(team)
            .name(name)
            .position(position)
            .number(number)
            .exec(&mut db)
            .await?;
    }

    // Query with partition key AND NOT condition on non-indexed field
    // team = "Timbers" AND NOT (position = "Midfielder")
    // Should return Fanendo Adi (Forward) and Adam Kwarasey (Goalkeeper)
    let players = Player::filter(
        Player::fields()
            .team()
            .eq("Timbers")
            .and(Player::fields().position().eq("Midfielder").not()),
    )
    .exec(&mut db)
    .await?;

    assert_eq!(2, players.len());
    let mut names: Vec<_> = players.iter().map(|p| p.name.as_str()).collect();
    names.sort();
    assert_eq!(names, ["Adam Kwarasey", "Fanendo Adi"]);

    // Query with partition key AND NOT with comparison
    // team = "Timbers" AND NOT (number > 8)
    // Should return players with number <= 8: Diego Valeri (8), Darlington Nagbe (6), Adam Kwarasey (1)
    let players = Player::filter(
        Player::fields()
            .team()
            .eq("Timbers")
            .and(Player::fields().number().gt(8).not()),
    )
    .exec(&mut db)
    .await?;

    assert_eq!(3, players.len());
    let mut names: Vec<_> = players.iter().map(|p| p.name.as_str()).collect();
    names.sort();
    assert_eq!(names, ["Adam Kwarasey", "Darlington Nagbe", "Diego Valeri"]);
    Ok(())
}

#[driver_test(id(ID))]
pub async fn query_not_operator_syntax(test: &mut Test) -> Result<()> {
    #[derive(Debug, toasty::Model)]
    #[key(partition = team, local = name)]
    struct Player {
        team: String,

        name: String,

        #[allow(dead_code)]
        position: String,

        #[allow(dead_code)]
        number: i64,
    }

    let mut db = test.setup_db(models!(Player)).await;

    for (team, name, position, number) in [
        ("Timbers", "Diego Valeri", "Midfielder", 8),
        ("Timbers", "Darlington Nagbe", "Midfielder", 6),
        ("Timbers", "Diego Chara", "Midfielder", 21),
        ("Timbers", "Fanendo Adi", "Forward", 9),
        ("Timbers", "Adam Kwarasey", "Goalkeeper", 1),
    ] {
        Player::create()
            .team(team)
            .name(name)
            .position(position)
            .number(number)
            .exec(&mut db)
            .await?;
    }

    // Use the ! operator instead of .not()
    // team = "Timbers" AND !(position = "Midfielder")
    let players = Player::filter(
        Player::fields()
            .team()
            .eq("Timbers")
            .and(!Player::fields().position().eq("Midfielder")),
    )
    .exec(&mut db)
    .await?;

    assert_eq!(2, players.len());
    let mut names: Vec<_> = players.iter().map(|p| p.name.as_str()).collect();
    names.sort();
    assert_eq!(names, ["Adam Kwarasey", "Fanendo Adi"]);

    // ! on a compound expression: !(number > 8 OR position = "Goalkeeper")
    let players = Player::filter(
        Player::fields().team().eq("Timbers").and(
            !(Player::fields()
                .number()
                .gt(8)
                .or(Player::fields().position().eq("Goalkeeper"))),
        ),
    )
    .exec(&mut db)
    .await?;

    // Excludes Diego Chara (21), Fanendo Adi (9), Adam Kwarasey (Goalkeeper)
    // Keeps Diego Valeri (8, Midfielder), Darlington Nagbe (6, Midfielder)
    assert_eq!(2, players.len());
    let mut names: Vec<_> = players.iter().map(|p| p.name.as_str()).collect();
    names.sort();
    assert_eq!(names, ["Darlington Nagbe", "Diego Valeri"]);
    Ok(())
}