SeaQL / sea-query

🔱 A dynamic SQL query builder for MySQL, Postgres and SQLite
https://www.sea-ql.org
Other
1.12k stars 182 forks source link

Parentheses for complex LIKE query is different than the same Postgres ILIKE query #776

Open jcrossley3 opened 4 months ago

jcrossley3 commented 4 months ago

Description

When I changed from LIKE to ILIKE I noticed I was getting a lot more parentheses in my queries. I would expect the precedence rules for both to be the same, therefore I'd expect the resulting parentheses to be the same.

Steps to Reproduce

  1. Modify the select_22 test to use ilike instead of like, like so:
#[test]
fn select_22() {
    use sea_query::extension::postgres::PgExpr;
    assert_eq!(
        Query::select()
            .column(Char::Character)
            .from(Char::Table)
            .cond_where(
                Cond::all()
                    .add(
                        Cond::any().add(Expr::col(Char::Character).ilike("C")).add(
                            Expr::col(Char::Character)
                                .ilike("D")
                                .and(Expr::col(Char::Character).ilike("E"))
                        )
                    )
                    .add(
                        Expr::col(Char::Character)
                            .ilike("F")
                            .or(Expr::col(Char::Character).ilike("G"))
                    )
            )
            .to_string(PostgresQueryBuilder),
        r#"SELECT "character" FROM "character" WHERE ("character" ILIKE 'C' OR ("character" ILIKE 'D' AND "character" ILIKE 'E')) AND ("character" ILIKE 'F' OR "character" ILIKE 'G')"#
    );
}

Expected Behavior

SELECT "character" FROM "character" WHERE ("character" ILIKE 'C' OR ("character" ILIKE 'D' AND "character" ILIKE 'E')) AND ("character" ILIKE 'F' OR "character" ILIKE 'G')

Actual Behavior

SELECT "character" FROM "character" WHERE (("character" ILIKE 'C') OR (("character" ILIKE 'D') AND ("character" ILIKE 'E'))) AND (("character" ILIKE 'F') OR ("character" ILIKE 'G'))

Reproduces How Often

Yep

Versions

The postgres extension of v0.31.0-rc.5

tyt2y3 commented 4 months ago

may be it will be a good idea to make ILIKE a built-in operator. now it is not, as it is Postgres specific