Repository navigation
Description
fromSql called on a table names its source "t", while the types of the query keep the table name as its alias. A qualified column accepted by the types, such as 'author.id', is rendered with the table name, and PostgreSQL fails with missing FROM-clause entry for table. Unqualified columns work.
Reproduction
Set PG_URL to a PostgreSQL database and run this TypeScript example. It creates only a temporary table, dropped when the transaction ends.
import { createDb } from 'pqb/postgres-js';
const db = createDb({ databaseURL: process.env.PG_URL! });
const author = db('fromsql_alias_author', (t) => ({
id: t.integer().primaryKey(),
name: t.text(),
}));
async function main() {
try {
await db.transaction(async () => {
await db.query`
CREATE TEMP TABLE fromsql_alias_author (id integer PRIMARY KEY, name text NOT NULL) ON COMMIT DROP
`;
const source = author.fromSql`(SELECT 1 AS id, 'Ann' AS name)`;
// Works: an unqualified column refers to the source alias.
console.log('unqualified:', await source.where({ id: 1 }));
// Fails: the types accept the table name as the alias, the SQL names the source "t".
const qualified = source.where({ 'fromsql_alias_author.id': 1 });
console.log(qualified.toSQL());
console.log('qualified:', await qualified);
});
} finally {
await db.close();
}
}
main().catch((error) => {
console.error(String(error));
process.exitCode = 1;
});Expected: the qualified condition refers to the source, as the types promise, and returns [ { id: 1, name: 'Ann' } ].
Actual:
unqualified: Result(1) [ { id: 1, name: 'Ann' } ]
{
text: `SELECT * FROM (SELECT 1 AS id, 'Ann' AS name) "t" WHERE "fromsql_alias_author"."id" = $1`,
values: [ 1 ],
...
}
Error: missing FROM-clause entry for table "fromsql_alias_author"
Qualified columns in join conditions and other methods are affected the same way. Calling .as(...) after fromSql avoids it.
Found while working on #912, where fromSql with declared columns defaults its alias to the table name instead.
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
queryFromSql sets data.as ||= 't'. A table query has no as of its own, its alias is the table name, and the type of the query keeps __as as the table name, so the types and the SQL disagree. Defaulting the alias to the table name fixes it, but changes the SQL of existing queries that refer to "t" in raw SQL, such as in whereSql.
Activity