coldbox-modules / quick

A ColdBox ORM Engine
https://quick.ortusbooks.com
MIT License
23 stars 19 forks source link

regression: where and/or grouping different between v4->v5 for the same scope invocation #212

Closed davidAtInleague closed 1 year ago

davidAtInleague commented 1 year ago

We've found some scopes generating bad sql, seems the change happened from the jump to v4->v5.

v4 generates the desired sql.

grammar is mssql.

repro and results we're seeing:

/*
writedump(getInstance("RMME_C").withFoo().toSql());

v4.1.3
SELECT [RMME_C].[id_c] FROM [RMME_C] WHERE (([id] = ? AND [id] != ?) OR ([id] = ? AND [id] != ?))

v5.2.0
SELECT [RMME_C].[id_c] FROM [RMME_C] WHERE [id] = ? AND [id] != ? AND [id] = ? AND [id] != ?

v5.2.7
SELECT [RMME_C].[id_c] FROM [RMME_C] WHERE [id] = ? AND [id] != ? AND [id] = ? AND [id] != ?

*/
component extends="quick.models.BaseEntity" table="RMME_C" accessors=true {
    property name="id_c" type="numeric" sqltype="int";

    variables._key = "id_c";

    function scopeWithFoo(qb) {
        qb.where((X) => {
            X
                .orWhere((X) => X.withBar(1))
                .orWhere((X) => X.withBar(2))
        })
    }

    function scopeWithBar(qb, val) {
        qb
            .where("id", "=", val)
            .andWhere("id", "!=", val - 1);
    }
}