[BUG]: Selecting from sub-query does not qualify the field

[arackaf]

What version of drizzle-orm are you using?

0.28.6

What version of drizzle-kit are you using?

n/a

Describe the Bug

I have this query / subquery

const aggQuery = db
  .select({ book: booksSubjects.book, subjects: sql<string>`JSON_ARRAYAGG(${booksSubjects.subject})`.as("subjects") })
  .from(booksSubjects)
  .innerJoin(subjectsTable, eq(booksSubjects.subject, subjectsTable.id))
  .groupBy(booksSubjects.book)
  .as("agg");

const query = db
  .select({ count: sql`COUNT(*)`.mapWith(val => Number(val)).as("count"), subjects: aggQuery.subjects })
  .from(booksTable)
  .innerJoin(aggQuery, eq(booksTable.id, aggQuery.book))
  .where(eq(booksTable.userId, userId))
  .groupBy(aggQuery.subjects)
  .having(isNotNull(aggQuery.subjects));

which generates this SQL

select COUNT(*) as `count`, `subjects` 
from `books` 
inner join (
  select `books_subjects`.`book`, JSON_ARRAYAGG(`books_subjects`.`subject`) as `subjects` 
  from `books_subjects` 
  inner join `subjects` 
  on `books_subjects`.`subject` = `subjects`.`id` 
  group by `books_subjects`.`book`
) `agg` 
on `books`.`id` = `agg`.`book` 
where `books`.`userId` = ? 
group by `subjects` 
having `subjects` is not null

Note the outermost selection list on line 1 of the sql.

Since the subquery is aliased as agg I believe the subjects selection should be qualified as

`agg`.`subjects`

like

`agg`.`book`

is in the join condition here

on `books`.`id` = `agg`.`book` 

Expected behavior

Field should be aliased

Environment & setup

planetscale-serverless

[relsunkaev]

Some examples of issues this causes with overlapping field names.

Example:

const sq = db
  .select({
    date: sql<string>`${balances.createdAt}::date`.as("date"),
    amount: balances.amount,
  })
  .from(balances)
  .as("sq");

const result = await db
  .select({
    date: transactions.date,
    amount: transactions.amount,
    balance: balances.amount,
  })
  .from(transactions)
  .innerJoin(sq, eq(sq.date, transactions.date));

will result in an error

error: column reference "date" is ambiguous

where the workaround is doing a little dance with sql template literals

const result = await db
  .select({
    date: transactions.date,
    amount: transactions.amount,
    balance: balances.amount,
  })
  .from(transactions)
  .innerJoin(sq, eq(sql`"sq".${sq.date}`, sql`"transactions".${transactions.date}`));

This behavior persists through subsequent subqueries where

const sq = db
  .select({
    date: sql<string>`${balances.createdAt}::date`.as("date"),
    amount: balances.amount,
  })
  .from(balances)
  .as("sq");

const sq2 = db
  .select({
    date: transactions.date,
    balanceDate: sq.date,
    amount: transactions.amount,
    balance: sq.amount,
  })
  .from(transactions)
  .innerJoin(sq, eq(sql`"sq".${sq.date}`, sql`"transactions".${transactions.date}`))
  .as("sq2");

const result = await db.select({
  balanceDate: sq2.balanceDate,
  anotherDate: anotherSq.date, // calculated using sql`...`.as("date")
}).from(sq2).innerJoin(anotherSq, ...);

will result in a similar error because both anotherSq.date and sq2.date resolve to "date".

[nounder]

Same issue here. I tried to use subqueries as lateral join is not yet available (#1079).

It's impossible to do non-trivial aggregation in Drizzle without those two.

[Angelelz]

Can this be accomplished with a CTE as workaround?

const aggQuery = db
  .$with("agg")
  .as(
    db.select({ book: booksSubjects.book, subjects: sql<string>`JSON_ARRAYAGG(${booksSubjects.subject})`.as("subjects") })
    .from(booksSubjects)
    .innerJoin(subjectsTable, eq(booksSubjects.subject, subjectsTable.id))
    .groupBy(booksSubjects.book)
  );

const query = db
  .with(aggQuery)
  .select({ count: sql`COUNT(*)`.mapWith(val => Number(val)).as("count"), subjects: aggQuery.subjects })
  .from(booksTable)
  .innerJoin(aggQuery, eq(booksTable.id, aggQuery.book))
  .where(eq(booksTable.userId, userId))
  .groupBy(aggQuery.subjects)
  .having(isNotNull(aggQuery.subjects));

I haven't tested but I can look into this.

[relsunkaev]

@Angelelz the workaround with CTE's doesn't seem to be working. The subquery fields are still unqualified.

[nicobao]

Same issue here, cannot run subqueries because two of the joined tables have same column names but the columns in the outer select aren't qualified.

[afogel]

This is an issue for me as well.

[Hebilicious]

I have been hit with the

error: column reference "foo" is ambiguous

more times than I can count. This really makes subqueries and CTE annoying to work with.

[GentikSolm]

Is there a reason we cannot default column names to use the full table.name inside of the sql`` operator instead of just the column name? Any time we do sub queries inside of selects we get hit with this when each table has an ID column and we are trying to do some quick subquery query across them. #1703 solves this for CTE's and aliases but not for regular usage of the sql operator inside of select(), unless im missing something.

[Angelelz]

Is there a reason we cannot default column names to use the full table.name inside of the sql`` operator instead of just the column name?

I think that's the current behavior. The issue here is when you use the sql operator in a cte or subquery, the qualifier stays with the original table.

[GentikSolm]

Is there a reason we cannot default column names to use the full table.name inside of the sql`` operator instead of just the column name?

I think that's the current behavior. The issue here is when you use the sql operator in a cte or subquery, the qualifier stays with the original table.

I can confirm this is not the current behavior, I've ran into the above bug multiple times. I can setup a repro later tomorrow afternoon

[relsunkaev]

I can confirm this is not the current behavior, I've ran into the above bug multiple times. I can setup a repro later tomorrow afternoon

I think what he means is that if you have

sql`sum(${table.name})`

It will render as sum("table"."name") which I believe is the current behavior. The problem is that when you use it with .as("calculated_field") and access it via subquery or CTE the field name won't have a qualifier and will resolve to "calculated_field", not "sq"."calculated_field".

[arackaf]

As of 0.30.1 this now generates the following

select COUNT(*) as `count`, `agg.subjects`
from `books`
         inner join (select `books_subjects`.`book`, JSON_ARRAYAGG(`books_subjects`.`subject`) as `agg.subjects`
                     from `books_subjects`
                              inner join `subjects` on `books_subjects`.`subject` = `subjects`.`id`
                     group by `books_subjects`.`book`) `agg` on `books`.`id` = `agg`.`book`
where `books`.`userId` = ?
group by `agg.subjects`
having `agg.subjects` is not null

which as far as I can tell is perfectly valid. Thank you again, brilliant Drizzle team!

[relsunkaev]

I am still experiencing this issue in 0.30.4 where

const sq = db
  .select({
    date: sql<string>`${balances.createdAt}::date`.as("date"),
  })
  .from(balances)
  .as("balances_sq");

const result = await db
  .select({
    balanceDate: sq.date,
  })
  .from(sq)
  .where(isNotNull(sq.date));

renders as

SELECT
  "date"
FROM
  (
    SELECT
      "as_of" :: date AS "date"
    FROM
      "uplinq_bank_account_balances"
  ) "balances_sq"
WHERE
  "date" IS NOT NULL

You can see sq.date and renders it as just "date" instead of "balances_sq"."date". This causes "field X is ambiguous" errors when working with subqueries.

[alexseguin]

I don't think this issue should've been closed.

I'm also getting the same issue with a column not getting aliased when using subqueries (I also tried using a with statement instead, and got the same results)

 const semester = 20241; // TODO: convert this into a function param
  const enrol = db
    //Note: we're only counting from the pscs._activities table, we won't have data before 2022.
    .select({
      students_enrolled: count(activities.emplid).as('students_enrolled'),
      course: substring(courseInventory.catalogNbr, { length: 8 }).as('course'),
      section: substring(classSectionToSection(activities.classSection), {
        length: 3,
      }).as('section'),
      semester: strmToSemester(activities.strm).as('semester'),
    })
    .from(activities)
    .innerJoin(courseInventory, eq(activities.crseId, courseInventory.crseId))
    .where(
      and(
        regexMatch(activities.classSection, '00$'),
        eq(strmToSemester(activities.strm), semester),
      ),
    )
    .groupBy(
      courseInventory.catalogNbr,
      activities.classSection,
      strmToSemester(activities.strm),
    )
    .as('enrol');

  const pos = db
    .select({
      course: coursePositions.course,
      section: coursePositions.section,
      semester: coursePositions.semester,
      positions: jsonAggBuildObject({
        id: coursePositions.id,
        position_type: coursePositions.position,
        num_hours: coursePositions.hours,
        student: jsonBuildObject({
          student_number: coursePositionOffers.candidateStudentId,
          first_name: person.firstName,
          last_name: person.lastName,
          email: person.emailAddr,
        }),
      }).as('positions'),
    })
    .from(coursePositions)
    .leftJoin(
      coursePositionOffers,
      eq(coursePositionOffers.coursePositionId, coursePositions.id),
    )
    .leftJoin(
      person,
      eq(person.emplid, coursePositionOffers.candidateStudentId),
    )
    .groupBy(
      coursePositions.course,
      coursePositions.section,
      coursePositions.semester,
    )
    .as('pos');

  const query = db
    .select({
      course_code: courses.course,
      course_name: courses.description,
      section_code: trim(courses.section),
      semester: courses.semester,
      // Including both comments for now; might not need both for notes field?
      comments: courses.comments,
      ta_comments: courses.taComments,
      language_code: sqlCase<'en' | 'fr' | 'bi'>()
        .when(lt(substring(courses.course, { start: 2, length: 1 }), 5))
        .then(sql`'en'`)
        .when(lt(substring(courses.course, { start: 2, length: 1 }), 9))
        .then(sql`'fr'`)
        .else(sql`'bi'`)
        .end(),
      num_students: enrol.students_enrolled,
      details: jsonBuildObject({
        course_description: courseDesc.description,
        program_description: programs.description,
        program_level: sqlCase()
          .when(eq(courses.ingroup, 'GRADS'))
          .then(sql`'graduate'`)
          .else(sql`'undergraduate'`)
          .end(),
      }),
      professor: jsonBuildObject({
        employee_number: courses.empNo,
        name: courses.profName,
        email: courses.profEmail,
      }),
      positions: pos.positions,
      // This is the legacy method of selecting positions
      correcteurs_requis: courses.correcteursRequis,
      no_ta_requis: courses.noTaRequis,
    })
    .from(courses)
    .innerJoin(programs, eq(programs.program, courses.program))
    .innerJoin(courseDesc, eq(courseDesc.course, courses.course))
    .leftJoin(
      enrol,
      and(
        eq(enrol.course, courses.course),
        eq(enrol.semester, courses.semester),
        eq(enrol.section, courses.section),
      ),
    )
    .leftJoin(
      pos,
      and(
        eq(pos.course, courses.course),
        eq(pos.semester, courses.semester),
        eq(pos.section, courses.section),
      ),
    )
    .where(
      and(
        eq(courses.semester, semester),
        eq(courses.publiclyVisible, 'Y'),
        or(isNull(courses.abolished), gt(courses.abolished, semester)),
        or(isNull(courses.created), lte(courses.created, semester)),
      ),
    );

  return query;

[Tcintra]

Was experiencing this issue as well. We wrote a workaround to get aliased table columns which has solved the ambiguous column names errors for our use case. It's generally type-safe, haven't seen any issue yet. If anyone has any recommendations for improving this, would love to hear it. You basically just replace getTableColumns with getTableColumnAliases and that seems to solve the problem.

type ColumnAlias<T extends AnyColumn> = ChangeColumnTableName<T, string, "pg">;

type ColumnAliases<T extends PgTable> = {
  [K in keyof T["_"]["columns"]]: ColumnAlias<T["_"]["columns"][K]>;
};

export function getTableColumnAliases<T extends PgTable>(
  table: T,
  keysToAlias: (keyof T["_"]["columns"])[],
): ColumnAliases<T> {
  const name = getTableName(table);
  return Object.fromEntries(
    Object.entries(getTableColumns(table)).map(([key, column]) => {
      if (!keysToAlias.includes(key as keyof T["_"]["columns"])) {
        return [key, column];
      }
      return [
        key,
        sql<number>`${table}.${sql.identifier(key)}`
          .mapWith(column.mapFromDriverValue)
          .as(`${name}_${key}`),
      ];
    }),
  ) as unknown as ColumnAliases<T>;
}

[morioust]

There's an another open issue for the same problem: #2772, classified as a feature request

[AsemK]

This is still an issue for me

[DoseOfCode]

are there any workarounds for this?

[arackaf]

To the people posting that this is still an issue - I urge you to create a new issue with a minimal reproduction. That would help the maintainers actually fix this.

My original repro is still fixed on latest, so I can't help there.