[BUG]: Subqueries lose alias for `sql` calculated fields

[gutentag2012]

What version of drizzle-orm are you using?

0.34.1

What version of drizzle-kit are you using?

0.25.0

Describe the Bug

When I select join with a subquery and select a field that was calculated using the sql operator, the resulting query loses its alias and only selects with the field alias.

Lets take this example, I have the following subquery:

const subQuery = db
    .select({
      identifier: table.id,
      sqlCol: sql`'some value'`.as('sqlCol'),
    })
    .from(table)
    .as('subQuery')

The subquery alias is subQuery and the calculated field is called sqlCol.

I am using it in the following query:

db.select({
    identifier: subQuery.identifier,
    sqlCol: subQuery.sqlCol,
  })
    .from(table)
    .leftJoin(subQuery, eq(subQuery.identifier, 1))

This results in the following SQL statement being executed:

select "subQuery"."id", "sqlCol" from "measurement" left join (select "id", 'some value' as "sqlCol" from "measurement") "subQuery" on "subQuery"."id" = ?

As you can see, the id field still has the subQuery alias and is selected using "subQuery"."id", the SQL field however is only selected through "sqlCol".

This makes it impossible to have multiple subqueries with the same alias for SQL fields.

Expected behavior

All fields selected from a subquery should be prefixed with the subquery alias, including calculated fields with the sql function

Environment & setup

I am using expo-sqlite (v14.0.6) during this test

[L-Mario564]

@gutentag2012 Fairly sure this isn't a bug. The first query has to be a CTE (with clause), and can be rewritten as such:

const subQuery = db.$with('subquery').as(
  db
    .select({
      identifier: table.id,
      sqlCol: sql`'some value'`.as('sqlCol'),
    })
    .from(table)
);

db
  .with(subQuery)
  .select({
    identifier: subQuery.identifier,
    sqlCol: subQuery.sqlCol,
  })
  .from(table)
  .leftJoin(subQuery, eq(subQuery.identifier, 1))

Let me know if any further issues arise.

[gutentag2012]

Yes I could write that query as a CTE, but that only works for a single with statement in drizzle as far as I know, so with multiple subqueries this would not work. Also the resulting query still has the same issue:

 with "subquery" as (select "id", 'some value' as "sqlCol" from "measurement") select "subquery"."id", "sqlCol" from "measurement" left join "subquery" on "subquery"."id" = ?

Only the "subquery"."id" is prefixed with the table identifier and "sqlCol" is not.

---

Let me show an example of why this is an issue. I have a base query which I need in two variations, with only the where clause changing, I could do this:

const subQueryBase = db
    .select({
      id: table.id,
      value: table.value,
    })
    .from(table)
const subQuery1 = subQueryBase.as('subQuery1')
const subQuery2 = subQueryBase.as('subQuery2')

db
  .select({
    id: subQuery1.id,
    value1: subQuery1.value,
    value2: subQuery2.value,
  })
  .from(table)
  .leftJoin(subQuery1, eq(table.id, subQuery1.id))
  .leftJoin(subQuery2, eq(table.id, subQuery2.id))

This works and results in the following query

select "subQuery1"."id", "subQuery1"."value", "subQuery2"."value" from "measurement" left join (select "id", "value" from "measurement") "subQuery1" on "measurement"."id" = "subQuery1"."id" left join (select "id", "value" from "measurement") "subQuery2" on "measurement"."id" = "subQuery2"."id"

If I want to do the same now only with the value transformed through the SQL function I have the following:

const subQueryBase = db
  .select({
    id: table.id,
    value: sql`${table.value} + 1`.as('value'),
  })
  .from(table)
const subQuery1 = subQueryBase.as('subQuery1')
const subQuery2 = subQueryBase.as('subQuery2')

db
  .select({
    id: subQuery1.id,
    value1: subQuery1.value,
    value2: subQuery2.value,
  })
  .from(table)
  .leftJoin(subQuery1, eq(table.id, subQuery1.id))
  .leftJoin(subQuery2, eq(table.id, subQuery2.id))

This fails because "value" is now used twice in the resulting SQL select statement, to fix this I have to do the following:

const subQuery1 = db
  .select({
    id: table.id,
    value: sql`${table.value} + 1`.as('value1'),
  })
  .from(table)
  .as('subQuery1')
const subQuery2 = db
  .select({
    id: table.id,
    value: sql`${table.value} + 1`.as('value2'),
  })
  .from(table)
  .as('subQuery2')

db
  .select({
    id: subQuery1.id,
    value1: subQuery1.value,
    value2: subQuery2.value,
  })
  .from(table)
  .leftJoin(subQuery1, eq(table.id, subQuery1.id))
  .leftJoin(subQuery2, eq(table.id, subQuery2.id))

This results in the following query and works:

select "subQuery1"."id", "value1", "value2" from "measurement" left join (select "id", "value" + 1 as "value1" from "measurement") "subQuery1" on "measurement"."id" = "subQuery1"."id" left join (select "id", "value" + 1 as "value2" from "measurement") "subQuery2" on "measurement"."id" = "subQuery2"."id"

But again, notice that "value1" and "value2" are not prefixed with anything.

I hope that makes my issue a bit clearer

[cyrilchapon]

Encountering the same trouble with a query that I'd like to keep as a subquery instead of a CTE.

Use case:

Subquery 1

export const _selectValidLicenses = drizzle
  .select({
    company_saas_directoryId: companySaasDirectories.id,
    licenses_count: count(companySaasLicenses.id).as('licenses_count'),
  })
  .from(companySaasDirectories)
  .leftJoin(
    companySaasLicenses,
    eq(companySaasDirectories.id, companySaasLicenses.company_saas_directoryId)
  )
  .where(
    notInArray(
      companySaasLicenses.status,
      deserializeLicenseBillingStatusInclusion(['revoked'])
    )
  )
  .groupBy(companySaasDirectories.id)

Subquery 2

export const _selectActiveLicenses = (defaultInactiveDuration: number) =>
  drizzle
    .select({
      company_saas_directoryId: companySaasDirectories.id,
      licenses_count: count(companySaasLicenses.id).as('licenses_count'),
    })
    .from(companySaasDirectories)
    .leftJoin(
      companySaasLicenses,
      eq(
        companySaasDirectories.id,
        companySaasLicenses.company_saas_directoryId
      )
    )
    .where(
      and(
        inArray(
          companySaasLicenses.status,
          deserializeLicenseBillingStatusInclusion(['running'])
        ),
        between(
          companySaasLicenses.last_activity,
          dateSub(
            now(),
            coalesce(
              companySaasDirectories.inactive_duration,
              defaultInactiveDuration
            ),
            'DAY'
          ),
          now()
        )
      )
    )
    .groupBy(companySaasDirectories.id)

CTE using both

export const _selectLicensesCount = (defaultInactiveDurationDays: number) => {
  const validLicenses = _selectValidLicenses.as('valid_licenses')
  const activeLicenses = _selectActiveLicenses(defaultInactiveDurationDays).as(
    'active_licenses'
  )

  return drizzle
    .select({
      company_saas_directoryId: companySaasDirectories.id,
      valid_licenses: coalesce(validLicenses.licenses_count, 0).as(
        'valid_licenses'
      ),// <== This looses the alias
      active_licenses: coalesce(activeLicenses.licenses_count, 0).as(
        'active_licenses'
      ),// <== This too
      inactive_licenses: minus(
        coalesce(validLicenses.licenses_count, 0),
        coalesce(activeLicenses.licenses_count, 0)
      ).as('inactive_licenses'),
    })
    .from(companySaasDirectories)
    .leftJoin(
      validLicenses,
      eq(companySaasDirectories.id, validLicenses.company_saas_directoryId)
    )
    .leftJoin(
      activeLicenses,
      eq(companySaasDirectories.id, activeLicenses.company_saas_directoryId)
    )
    .groupBy(companySaasDirectories.id)
}

[cyrilchapon]

@gutentag2012 , temporary workaround

from

{
  valid_licenses: validLicenses.licenses_count
}

to

{
  validLicenses: sql<number>`${sql.raw(validLicenses._.alias)}.${sql.raw(validLicenses.licenses_count.fieldAlias)}`
    .as('valid_licenses')
}

---

So I extracted a dirty temporary helper :

export const subqueryColumnName = <T>(
  table: Subquery,
  column: SQL.Aliased<T>
) =>
  sql<T>`${sql.identifier(table._.alias)}.${sql.identifier(column.fieldAlias)}`

which leads to cleaner :

{
  validLicenses: subqueryColumnName(
    validLicenses,
    validLicenses.licenses_count
  ).as('valid_licenses')
}

[gutentag2012]

@gutentag2012 , temporary workaround

from

{
  valid_licenses: validLicenses.licenses_count
}

to

{
  validLicenses: sql<number>`${sql.raw(validLicenses._.alias)}.${sql.raw(validLicenses.licenses_count.fieldAlias)}`
    .as('valid_licenses')
}

So I extracted a dirty temporary helper :

export const subqueryColumnName = <T>(
  table: Subquery,
  column: SQL.Aliased<T>
) =>
  sql<T>`${sql.identifier(table._.alias)}.${sql.identifier(column.fieldAlias)}`

which leads to cleaner :

{
  validLicenses: subqueryColumnName(
    validLicenses,
    validLicenses.licenses_count
  ).as('valid_licenses')
}

Nice helper function! 👍 I guess you could also just rename the label given within the as function, since this does not affect the variables in code, but only the SQL executed.