Skip to content

join with a callback refers to a selected alias of the joined sub-query as a table column #916

Open

Description

@IlyaSemenov

When a joined sub-query selects a column under an alias, join with a callback refers to the alias inside the sub-query as if it were a table column, and PostgreSQL fails with column ... does not exist. The same join with column arguments works.

Reproduction

Set PG_URL to a PostgreSQL database and run this TypeScript example. It creates only temporary tables, dropped when the transaction ends.

import { createDb } from 'pqb/postgres-js';

const db = createDb({ databaseURL: process.env.PG_URL! });
const author = db('join_alias_author', (t) => ({
  id: t.integer().primaryKey(),
  name: t.text(),
}));
const message = db('join_alias_message', (t) => ({
  id: t.integer().primaryKey(),
  authorId: t.name('author_id').integer(),
  text: t.text(),
}));

async function main() {
  try {
    await db.transaction(async () => {
      await db.query`
        CREATE TEMP TABLE join_alias_author (id integer PRIMARY KEY, name text NOT NULL) ON COMMIT DROP
      `;
      await db.query`
        CREATE TEMP TABLE join_alias_message (id integer PRIMARY KEY, author_id integer NOT NULL, text text NOT NULL) ON COMMIT DROP
      `;
      await author.insert({ id: 1, name: 'Ann' });
      await message.insert({ id: 1, authorId: 1, text: 'Hi' });

      const messages = message.select({ writer: 'authorId', text: 'text' }).as('m');

      // Works: the selected alias is matched outside of the sub-query.
      const withArgs = author.join(messages, 'm.writer', 'id').select('name', 'm.text');
      console.log('args:', await withArgs);

      // Fails: the condition refers to the selected alias inside the sub-query.
      const withCallback = author
        .join(messages, (q) => q.on('m.writer', 'join_alias_author.id'))
        .select('name', 'm.text');
      console.log(withCallback.toSQL());
      console.log('callback:', await withCallback);
    });
  } finally {
    await db.close();
  }
}

main().catch((error) => {
  console.error(String(error));
  process.exitCode = 1;
});

Expected: the callback form returns [ { name: 'Ann', text: 'Hi' } ], the same as the form with arguments.

Actual:

args: Result(1) [ { name: 'Ann', text: 'Hi' } ]
{
  text: 'SELECT "join_alias_author"."name", "m"."text" FROM "join_alias_author" JOIN LATERAL (SELECT "m"."author_id" "writer", "m"."text" FROM "join_alias_message" "m" WHERE "m"."writer" = "join_alias_author"."id") "m" ON true',
  values: [],
  ...
}
Error: column m.writer does not exist

Found while working on #912.

Verified environment

Reproduced against real PostgreSQL 18.4 using Node 24.22.3, the postgres-js adapter (postgres 4.1.1), and pqb 0.73.48 from npm. The example type-checks.

AI analysis

The join callback gets a query builder whose columns are the output columns of the joined sub-query (getShapeFromSelect of the query with its select), so m.writer resolves to "m"."writer". That is right for conditions rendered outside of the sub-query, as ON "m"."writer" = ... in the form with arguments. But with a callback, the conditions are merged into the sub-query and rendered in its WHERE as a LATERAL join, where "m" is the joined table itself and only "m"."author_id" exists.

Activity

  1. added
    bugSomething isn't working
    found by aiDiscovered by AI while working on something else; ping if the issue is important for real use
    on Nov 17, 2026
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Labels

    bugSomething isn't workingfound by aiDiscovered by AI while working on something else; ping if the issue is important for real use

    Projects

    No projects

      Milestone

      No milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions