If whereNot is the first condition in subquery it negates whole subquery

[m-i-c-h-a-l]

Package version

"@adonisjs/lucid": "^18.4.0"

Node.js and npm version

node v18.16.0 npm 9.5.1

Sample Code (to reproduce the issue)

export default class Bank {

    @column()
    public countryCode: string;

   @belongsTo(() => Country, {
        foreignKey: 'countryCode',
        localKey: 'code',
    })
    public country: BelongsTo<typeof Country>;
   ...
}

// not working query
Bank.query()
            .whereHas('country', (q: ModelQueryBuilderContract<typeof Country>) => {
                q.whereNot('name', 'Slovakia');
                q.where('region', 'Europe');  // same result with andWhere
            })

Generated SQL - both name and region are in NOT: SELECT * FROM "banks" WHERE EXISTS (SELECT * FROM "countries" WHERE NOT ("name" = ? AND "region" = ?) AND ("countries"."code" = "banks"."country_code"))

Moving whereNot makes it work:

// working query
Bank.query()
            .whereHas('country', (q: ModelQueryBuilderContract<typeof Country>) => {
                q.where('region', 'Europe');
                q.whereNot('name', 'Slovakia');
                q.where('name', 'Austria');
            })

Generated SQL - only first name is with NOT: SELECT * FROM "banks" WHERE EXISTS (SELECT * FROM "countries" WHERE ("region" = ? AND NOT "name" = ? AND "name" = ?) AND ("countries"."code" = "banks"."country_code"))