Repository navigation
Description
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