[BUG]: $count() generates the wrong Postgres subquery

[DougSchmidt-Leeward]

Report hasn't been filed before.

• I have verified that the bug I'm about to report hasn't been filed before.

What version of drizzle-orm are you using?

0.36.1

What version of drizzle-kit are you using?

0.28.0

Other packages

No response

Describe the Bug

Likely same root cause as #1815?

But I thought I should report this separately since #1815 mentions sql`` templates where as this was with db.$count()

Drizzle snippet to roll up some counts from JSONB column values:

  findUsage(id: number, accountId: string, includeDeleted: boolean) {
    const fieldMatch = sql.raw(`fields @? '$[*] ? (@.fieldId == ${id})'`);

    return db.query.customFieldsTable.findFirst({
      columns: { id: true },
      extras: {
        feedbackCount: this.drizzleService.db
          .$count(feedbacksTable, and(fieldMatch, includeDeleted ? undefined : isNull(feedbacksTable.deletedAt)))
          .as<number>('feedback_count'),
        ideaCount: this.drizzleService.db
          .$count(ideasTable, and(fieldMatch, includeDeleted ? undefined : isNull(ideasTable.deletedAt)))
          .as<number>('idea_count'),
        customerCount: this.drizzleService.db
          .$count(customersTable, and(fieldMatch, includeDeleted ? undefined : isNull(customersTable.deletedAt)))
          .as<number>('customer_count'),
      },
      where: and(eq(customFieldsTable.accountId, accountId), eq(customFieldsTable.id, id)),
    });
  }

Generated query, with incorrect subqueries for the 3 counts:

select "id"
, (select count(*) from "feedbacks" where (fields @? '$[*] ? (@.fieldId == 354)' and "customFieldsTable"."deleted_at" is null)) as "feedback_count"
, (select count(*) from "ideas"     where (fields @? '$[*] ? (@.fieldId == 354)' and "customFieldsTable"."deleted_at" is null)) as "idea_count"
, (select count(*) from "customers" where (fields @? '$[*] ? (@.fieldId == 354)' and "customFieldsTable"."deleted_at" is null)) as "customer_count" from "custom_fields"
"customFieldsTable" where ("customFieldsTable"."account_id" = $1 and "customFieldsTable"."id" = $2) limit 1;

Each of the 3 subqueries has a "customFieldsTable"."deleted_at" is null clause instead of {relatedTableName}."deleted_at" is null.

This is still semantically valid (nearly all the tables in the schema have a deleted_at column), so it executes without error, but is applying the wrong filter and returning silently-incorrect results.

I have worked around this using sql.raw to reference the deleted_at column in the target table.

  findUsage(id: number, accountId: string, includeDeleted: boolean) {
    const fieldMatch = sql.raw(
      `${includeDeleted ? '' : 'deleted_at IS NULL AND '}fields @? '$[*] ? (@.fieldId == ${id})'`,
    );

    return this.drizzleService.db.query.customFieldsTable.findFirst({
      columns: { id: true },
      extras: {
        feedbackCount: sql`(SELECT COUNT(id) FROM "feedbacks" WHERE ${fieldMatch})`.as<number>('feedback_count'),
        ideaCount: sql`(SELECT COUNT(id) FROM "ideas" WHERE ${fieldMatch})`.as<number>('idea_count'),
        customerCount: sql`(SELECT COUNT(id) FROM "customers" WHERE ${fieldMatch})`.as<number>('customer_count'),
      },
      where: and(eq(customFieldsTable.accountId, accountId), eq(customFieldsTable.id, id)),
    });
  }

[AlexBlokh]

yep, this is unfortunately a bug+feature of relational queries + sql template, which we will fix in relational queries v2, otherwise it will be a breaking change for people who are using relational queries v1 with filters on subrelations