Unexpected Behavior: Different Results Based on Argument Order in 'where' Clause

[shauryavclarityvoice]

Title

Different Results Based on Argument Order in 'where' Clause

Description

I've encountered an issue where the order of arguments in the where clause significantly affects the query results, which is unexpected and potentially problematic. Specifically, when using a combination of date filters (before and after) along with agentId, changing the order of these arguments leads to different query outcomes.

Steps to Reproduce

Set up a query with the following where clause:

const whereArgs1 = {
      ...((args?.before || args?.after) && {
        date: {
          ...(args?.before && {
            "<": args.before,
          }),
          ...(args?.after && {
            ">": args.after,
          }),
        },
      }),
      agentId: auth.userId,
    };
const result = await this.connection.select<AggregateCallRecordData>({
      from: DB.TBL_COMM_HISTORY,
      groupBy: "customerId",
      aggregate: {
        max: ["date"],
      },
      order: {
        by: "max(date)",
        type: "desc",
      },
      where: whereArgs1,
    });

2nd Case: Where i move agentId to top

const whereArgs2 = {
      agentId: auth.userId,
      ...((args?.before || args?.after) && {
        date: {
          ...(args?.before && {
            "<": args.before,
          }),
          ...(args?.after && {
            ">": args.after,
          }),
        },
      }), 
    };
    const result = await this.connection.select<AggregateCallRecordData>({
      from: DB.TBL_COMM_HISTORY,
      groupBy: "customerId",
      aggregate: {
        max: ["date"],
      },
      order: {
        by: "max(date)",
        type: "desc",
      },
      where: whereArgs2,
    });

Expected Behavior: The results of the query should be consistent regardless of the order of properties in the whereArgs object.

Actual Behavior: The order of properties in the whereArgs object affects the query results, leading to inconsistent and unpredictable behavior.

[ujjwalguptaofficial]

Yes whereQuery order affect the results - as it tries to filter data by firstValue in whereArgs and then consecutively.

If you want ordered results, then you need to focus on order query : and i see you are specifying aggregated column in the order which shoulkd throw error like this, may be you are on older version -

You need to specify the right column in the order query like this -

Here is link - https://ujjwalguptaofficial.github.io/idbstudio/?db=Demo&query=select(%7B%0A%20%20%20%20%20%20from%3A'Customers'%2C%0A%20%20%20%20%20%20%0A%7D)