Drizzle Team•3mo ago

Is this a bug or I am just an idiot

So basically I have 3 tables named as chapter, volume and novel. I was trying to get all the novels (which is 8 right now), and latest 5 chapters of these novels. To achieve this I was trying to use subquery of volume and chapter combined and then giving them a row number. Because volume and chapter both have a column name as number somehow it is causing issue 😕 . For reference: I'm using supabase with @vercel/postgres My error:

error: column reference "number" is ambiguous
    at F:\projects\quaslation\node_modules\@neondatabase\serverless\index.js:1345:74
    at process.processTicksAndRejections (node:internal/process/task_queues:95:5)
    at VercelPgPreparedQuery.execute (F:\projects\quaslation\node_modules\src\vercel-postgres\session.ts:60:20)
    at main (f:\projects\quaslation\temp.ts:35:18) {
  length: 111,
  severity: 'ERROR',
  code: '42702',
  detail: undefined,
  hint: undefined,
  position: '62',
  internalPosition: undefined,
  internalQuery: undefined,
  where: undefined,
  schema: undefined,
  table: undefined,
  column: undefined,
  dataType: undefined,
  constraint: undefined,
  file: 'parse_relation.c',
  line: '816',
  routine: 'scanRTEForColumn'
}

error: column reference "number" is ambiguous
    at F:\projects\quaslation\node_modules\@neondatabase\serverless\index.js:1345:74
    at process.processTicksAndRejections (node:internal/process/task_queues:95:5)
    at VercelPgPreparedQuery.execute (F:\projects\quaslation\node_modules\src\vercel-postgres\session.ts:60:20)
    at main (f:\projects\quaslation\temp.ts:35:18) {
  length: 111,
  severity: 'ERROR',
  code: '42702',
  detail: undefined,
  hint: undefined,
  position: '62',
  internalPosition: undefined,
  internalQuery: undefined,
  where: undefined,
  schema: undefined,
  table: undefined,
  column: undefined,
  dataType: undefined,
  constraint: undefined,
  file: 'parse_relation.c',
  line: '816',
  routine: 'scanRTEForColumn'
}

Solution:

@Shashank Hi, I'm pretty sure the issue is to do with the chapter number and volume number, and the fix should be as simple as this:

chapterNumber: capterTable.number.getSQL().as('chapter_number'),
volumeNumber: volumeTable.number.getSQL().as('volume_number')

chapterNumber: capterTable.number.getSQL().as('chapter_number'),
volumeNumber: volumeTable.number.getSQL().as('volume_number')

...

Jump to solution

5 Replies

Solution

Mario564•3mo ago

@Shashank Hi, I'm pretty sure the issue is to do with the chapter number and volume number, and the fix should be as simple as this:

chapterNumber: capterTable.number.getSQL().as('chapter_number'),
volumeNumber: volumeTable.number.getSQL().as('volume_number')

chapterNumber: capterTable.number.getSQL().as('chapter_number'),
volumeNumber: volumeTable.number.getSQL().as('volume_number')

Mario564•3mo ago

This should resolve the ambiguity the error is describing

ShashankOP•3mo ago

Yupp it does but the problem is it also takes away the power of typescript for chapterNumber and volumeNumber I guess this is the workaround for now

Mario564•3mo ago

Oh yeah, that can be an issue. Adding to the already kinda verbose solution, you can also add .mapWith(Number) to TS knows it's a number (add it before .as()) We'll look into aliasing columns without this clunky behavior sometime soon

ShashankOP•3mo ago

yeah that will be gr8 for now I'm planning to use relational queries. It fits better with my use case.

Gaming

Programming

Is this a bug or I am just an idiot