Skip to content

Commit 25baadb

Browse files
anandgupta42claude
andcommitted
fix(analyst): predicate_effect accepts table-qualified predicates and keeps caller positions
Independent-review follow-ups: - A bare-table population is wrapped under the table's own name, so a predicate copied from the caller's WHERE clause (`orders.kind = 'a'`) works; SELECT populations keep the `t` alias and the description says so. - The limited-population note fires only for a top-level `LIMIT`, `FETCH FIRST … ROWS ONLY` or `TOP` on the population statement, not for a LIMIT inside a subquery. - Blank predicate entries are reported as skipped at their original position, so predicate numbers match the caller's array. Co-Authored-By: Claude Fable 5.1 <[email protected]>
1 parent 52f0e5e commit 25baadb

3 files changed

Lines changed: 178 additions & 13 deletions

File tree

‎packages/opencode/src/altimate/tools/sql-check.ts‎

Lines changed: 38 additions & 11 deletions
Original file line numberDiff line numberDiff line change
@@ -23,7 +23,9 @@ const parameters = z
2323
.min(1)
2424
.max(8)
2525
.optional()
26-
.describe("1–8 boolean expressions over base_sql columns"),
26+
.describe(
27+
"1–8 boolean expressions; predicates reference the population's columns; for a bare table you may qualify with the table name; for a SELECT population use bare column names or the alias `t`",
28+
),
2729
max_rows_scanned: z
2830
.number()
2931
.int()
@@ -65,7 +67,13 @@ const publicParameters = z
6567
.string()
6668
.optional()
6769
.describe("Population before filtering: read-only SELECT or bare table reference for predicate_effect"),
68-
predicates: z.array(z.string()).max(8).optional().describe("1–8 boolean expressions over base_sql columns"),
70+
predicates: z
71+
.array(z.string())
72+
.max(8)
73+
.optional()
74+
.describe(
75+
"1–8 boolean expressions; predicates reference the population's columns; for a bare table you may qualify with the table name; for a SELECT population use bare column names or the alias `t`",
76+
),
6977
max_rows_scanned: z
7078
.number()
7179
.int()
@@ -186,7 +194,23 @@ function readBase(sql: string, dialect?: string) {
186194
sql.trim(),
187195
)
188196
const table = bare ? sql.trim() : undefined
189-
return { sql: readSql(table ? `SELECT * FROM ${table}` : sql, dialect), table }
197+
// Preserve quoting and case folding, including dots inside a quoted table name.
198+
const alias = table?.match(/"(?:[^"]|"")+"|[A-Za-z_][A-Za-z0-9_$]*/g)?.at(-1) ?? "t"
199+
return { sql: readSql(table ? `SELECT * FROM ${table}` : sql, dialect), table, alias }
200+
}
201+
202+
function populationLimit(sql: string, dialect: Dialect): string | undefined {
203+
// Hide subqueries and CTE bodies, retaining the parentheses for TOP (n).
204+
let depth = 0
205+
let topLevel = ""
206+
for (const char of maskSql(tokenizeSql(sql, dialect).tokens)) {
207+
if (char === ")") depth--
208+
topLevel += depth === 0 ? char : " "
209+
if (char === "(") depth++
210+
}
211+
if (/(?:^|[^\w$])LIMIT(?![\w$])/i.test(topLevel)) return "LIMIT"
212+
if (/(?:^|[^\w$])FETCH\s+FIRST(?![\w$])[\s\S]*?\bROWS?\s+ONLY(?![\w$])/i.test(topLevel)) return "FETCH FIRST"
213+
if (/(?:^|[^\w$])SELECT\s+(?:(?:ALL|DISTINCT)\s+)?TOP\b\s*(?:\d|\()/i.test(topLevel)) return "TOP"
190214
}
191215

192216
function readPredicate(predicate: string, dialect?: string): string {
@@ -311,7 +335,7 @@ export async function checkSql(input: SqlCheckPublicInput) {
311335
const count = (predicate: string) => `COUNT(*) FILTER (WHERE ${predicate})`
312336
const [row] = await run(`SELECT COUNT(*) AS total,
313337
${expressions.map((predicate, i) => `${count(predicate)} AS keep_${i + 1}`).join(", ")},
314-
${count(expressions.join(" AND "))} AS keep_all FROM (\n${population.sql}\n) t`)
338+
${count(expressions.join(" AND "))} AS keep_all FROM (\n${population.sql}\n) ${population.alias}`)
315339
const total = number(row.total)
316340
const effects = args.predicates!.map((predicate, i) => {
317341
const kept = number(row[`keep_${i + 1}`])
@@ -324,16 +348,18 @@ export async function checkSql(input: SqlCheckPublicInput) {
324348
}
325349
})
326350
const combined = number(row.keep_all)
351+
const limit = populationLimit(population.sql, dialect)
352+
let effectIndex = 0
327353
return result(args.check, { total_rows: total, predicates: effects, combined_kept_rows: combined }, [
328354
`predicate_effect: ${total ? `${total} rows in population` : "empty population"}`,
329355
...(estimate === undefined ? ["row_estimate: unknown — probe runs under the query timeout"] : []),
330-
...(/\bLIMIT\b/i.test(maskSql(tokenizeSql(population.sql, dialect).tokens))
331-
? ["counts are over the limited population (base_sql includes LIMIT)"]
332-
: []),
333-
...effects.map(
334-
(effect, i) =>
335-
`predicate ${i + 1} ${display(effect.predicate)}: kept ${effect.kept_rows}; removed ${effect.removed_rows}${effect.warning ? `; ${effect.warning}` : ""}`,
336-
),
356+
...(limit ? [`counts are over the limited population (base_sql includes ${limit})`] : []),
357+
...input.predicates!.map((predicate, i) => {
358+
const label = `predicate ${i + 1} ${display(predicate)}`
359+
if (!predicate.trim()) return `${label}: skipped (blank)`
360+
const effect = effects[effectIndex++]
361+
return `${label}: kept ${effect.kept_rows}; removed ${effect.removed_rows}${effect.warning ? `; ${effect.warning}` : ""}`
362+
}),
337363
`combined (AND): kept ${combined}; removed ${total - combined}${total > 0 && combined === 0 ? "; this combination removes all rows" : ""}`,
338364
"A predicate that returns NULL counts as not kept.",
339365
])
@@ -560,6 +586,7 @@ export const SqlCheckTool = Tool.define("sql_check", {
560586
"Use sql_materialize to put cross-warehouse or other-engine sources into scratch first. " +
561587
"Key normalization is diagnostic and may collapse unrelated values; no automatic fixes. Coverage uses regex except SQLite's explicit LIKE fallback. " +
562588
"predicate_effect takes base_sql before filtering and 1–8 explicit predicates; reports individual and combined removals, with NULL not kept. " +
589+
"predicates reference the population's columns; for a bare table you may qualify with the table name; for a SELECT population use bare column names or the alias `t`. " +
563590
"Bare-table estimates above max_rows_scanned (default 5000000) are refused; unknown estimates use the query timeout and expiry is reported as skipped. " +
564591
"Leave fields a check does not use empty.",
565592
parameters: publicParameters,

‎packages/opencode/test/altimate/sql-check-postgres.test.ts‎

Lines changed: 57 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -23,12 +23,12 @@ afterEach(() => {
2323
Registry.reset()
2424
})
2525

26-
function postgres(relkind = "r", reltuples = 5) {
26+
function postgres(relkind = "r", reltuples = 5, translate = (sql: string) => sql) {
2727
database.run("INSERT INTO pg_class VALUES (1, ?, ?)", [relkind, reltuples])
2828
const execute = mock(async (...[sql]: Parameters<Connector["execute"]>) => {
2929
// Keep the generated WHERE and FILTER clauses executable: removing either
3030
// guard condition must change results, rather than leave a canned mock row.
31-
const query = sql
31+
const query = translate(sql)
3232
.replace("pg_catalog.pg_class", "pg_class")
3333
.replace(/pg_catalog\.to_regclass\(E'(?:[^']|'')*'\)/, "1")
3434
const statement = database.query(query)
@@ -40,6 +40,61 @@ function postgres(relkind = "r", reltuples = 5) {
4040
}
4141

4242
describe("predicate_effect with mocked Postgres", () => {
43+
test("qualifies a bare table using its quoted unqualified name with a dot and escaped quote", async () => {
44+
database.run('CREATE TABLE "Odd.orders""quoted" AS SELECT * FROM orders')
45+
const execute = postgres()
46+
const base_sql = 'main."Odd.orders""quoted"'
47+
const result = await checkSql({
48+
warehouse,
49+
check: "predicate_effect",
50+
base_sql,
51+
predicates: ['"Odd.orders""quoted".kind = \'a\''],
52+
})
53+
expect(result.metadata).toMatchObject({ total_rows: 5, combined_kept_rows: 3 })
54+
expect(execute.mock.calls.at(-1)?.[0]).toContain(`FROM (\nSELECT * FROM ${base_sql}\n) "Odd.orders""quoted"`)
55+
})
56+
57+
test("retains the t qualifier for SELECT populations", async () => {
58+
const execute = postgres()
59+
const base_sql = "SELECT * FROM orders"
60+
const result = await checkSql({
61+
warehouse,
62+
check: "predicate_effect",
63+
base_sql,
64+
predicates: ["t.kind = 'a'"],
65+
})
66+
expect(result.metadata).toMatchObject({ total_rows: 5, combined_kept_rows: 3 })
67+
expect(execute.mock.calls[0][0]).toContain(`FROM (\n${base_sql}\n) t`)
68+
})
69+
70+
test.each([
71+
{ name: "FETCH FIRST", population: "SELECT * FROM orders ORDER BY id FETCH FIRST 2 ROWS ONLY" },
72+
{ name: "TOP", population: "SELECT TOP (2) * FROM orders ORDER BY id" },
73+
])("notes a top-level $name while preserving the population SQL", async ({ population }) => {
74+
const execute = postgres("r", 5, (sql) => sql.replace(population, "SELECT * FROM orders ORDER BY id LIMIT 2"))
75+
const result = await checkSql({
76+
warehouse,
77+
check: "predicate_effect",
78+
base_sql: population,
79+
predicates: ["id > 1"],
80+
})
81+
expect(result.metadata).toMatchObject({ total_rows: 2, combined_kept_rows: 1 })
82+
expect(execute.mock.calls[0][0]).toContain(`FROM (\n${population}\n) t`)
83+
expect(result.output).toContain("counts are over the limited population")
84+
})
85+
86+
test.each([
87+
{ name: "FETCH FIRST", inner: "SELECT * FROM orders ORDER BY id FETCH FIRST 2 ROWS ONLY" },
88+
{ name: "TOP", inner: "SELECT TOP (2) * FROM orders ORDER BY id" },
89+
])("does not note $name confined to a subquery", async ({ inner }) => {
90+
const execute = postgres("r", 5, (sql) => sql.replace(inner, "SELECT * FROM orders ORDER BY id LIMIT 2"))
91+
const base_sql = `SELECT * FROM (${inner}) limited_orders`
92+
const result = await checkSql({ warehouse, check: "predicate_effect", base_sql, predicates: ["id > 1"] })
93+
expect(result.metadata).toMatchObject({ total_rows: 2, combined_kept_rows: 1 })
94+
expect(execute.mock.calls[0][0]).toContain(`FROM (\n${base_sql}\n) t`)
95+
expect(result.output).not.toContain("limited population")
96+
})
97+
4398
test("uses independent FILTER counts and a parenthesized AND probe with newline-safe comments", async () => {
4499
const execute = postgres()
45100
const base_sql = "SELECT * FROM orders -- population comment"

‎packages/opencode/test/altimate/sql-check-predicate-effect.test.ts‎

Lines changed: 83 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -159,6 +159,38 @@ for (const warehouse of warehouses) {
159159
expect(result.metadata).toMatchObject({ total_rows: 5, combined_kept_rows: 3 })
160160
})
161161

162+
test.each(["orders", "main.orders"])("accepts table-qualified predicates for bare population %s", async (base_sql) => {
163+
const result = await checkSql({
164+
warehouse,
165+
check: "predicate_effect",
166+
base_sql,
167+
predicates: ["orders.kind = 'a'"],
168+
})
169+
expect(result.metadata).toMatchObject({ total_rows: 5, combined_kept_rows: 3 })
170+
})
171+
172+
test("preserves quoted unqualified table names containing dots and escaped quotes", async () => {
173+
const connector = await Registry.get(warehouse)
174+
await connector.execute('CREATE TABLE "Order.Items""Archive" AS SELECT * FROM orders')
175+
const result = await checkSql({
176+
warehouse,
177+
check: "predicate_effect",
178+
base_sql: 'main."Order.Items""Archive"',
179+
predicates: ['"Order.Items""Archive".kind = \'a\''],
180+
})
181+
expect(result.metadata).toMatchObject({ total_rows: 5, combined_kept_rows: 3 })
182+
})
183+
184+
test("keeps alias t for SELECT populations", async () => {
185+
const result = await checkSql({
186+
warehouse,
187+
check: "predicate_effect",
188+
base_sql: baseSql,
189+
predicates: ["t.kind = 'a'"],
190+
})
191+
expect(result.metadata).toMatchObject({ total_rows: 5, combined_kept_rows: 3 })
192+
})
193+
162194
test("refuses a bare table whose catalog estimate exceeds the custom cap", async () => {
163195
const connector = await Registry.get(warehouse)
164196
if (warehouse === "sqlite_predicates") await connector.execute("ANALYZE orders")
@@ -251,6 +283,54 @@ for (const warehouse of warehouses) {
251283
expect(result.output).not.toContain("limited population")
252284
})
253285

286+
test("does not mistake a LIMIT suffix in an unquoted identifier for a population limit", async () => {
287+
const result = await checkSql({
288+
warehouse,
289+
check: "predicate_effect",
290+
base_sql: 'SELECT row$LIMIT FROM (SELECT id AS "row$LIMIT" FROM orders) subset',
291+
predicates: ['"row$LIMIT" > 2'],
292+
})
293+
expect(result.metadata).toMatchObject({ total_rows: 5, combined_kept_rows: 3 })
294+
expect(result.output).not.toContain("limited population")
295+
})
296+
297+
test("does not mistake column top2 for a population TOP clause", async () => {
298+
const result = await checkSql({
299+
warehouse,
300+
check: "predicate_effect",
301+
base_sql: "SELECT top2 FROM (SELECT id AS top2 FROM orders) subset",
302+
predicates: ["top2 > 2"],
303+
})
304+
expect(result.metadata).toMatchObject({ total_rows: 5, combined_kept_rows: 3 })
305+
expect(result.output).not.toContain("limited population")
306+
})
307+
308+
test.each([
309+
"SELECT * FROM (SELECT * FROM orders ORDER BY id LIMIT 2) subset",
310+
"WITH subset AS (SELECT * FROM orders ORDER BY id LIMIT 2) SELECT * FROM subset",
311+
])("does not label an inner LIMIT as a population limit: %s", async (base_sql) => {
312+
const result = await checkSql({ warehouse, check: "predicate_effect", base_sql, predicates: ["id > 0"] })
313+
expect(result.metadata).toMatchObject({ total_rows: 2, combined_kept_rows: 2 })
314+
expect(result.output).not.toContain("limited population")
315+
})
316+
317+
test("preserves caller positions and lists leading, middle and trailing blank predicates", async () => {
318+
const result = await checkSql({
319+
warehouse,
320+
check: "predicate_effect",
321+
base_sql: baseSql,
322+
predicates: ["", "id > 2", " \t ", "kind = 'a'", " "],
323+
})
324+
expect(result.output.match(/^predicate \d+.*$/gm)).toEqual([
325+
'predicate 1 "": skipped (blank)',
326+
'predicate 2 "id > 2": kept 3; removed 2',
327+
'predicate 3 " \\t ": skipped (blank)',
328+
`predicate 4 "kind = 'a'": kept 3; removed 2`,
329+
'predicate 5 " ": skipped (blank)',
330+
])
331+
expect(result.metadata).toMatchObject({ total_rows: 5, combined_kept_rows: 2 })
332+
})
333+
254334
test("strict-schema zero cap and blank predicates are treated as absent by the registered tool", async () => {
255335
const precedence = spyOn(Precedence, "check").mockResolvedValue({})
256336
try {
@@ -278,6 +358,9 @@ for (const warehouse of warehouses) {
278358
combined_kept_rows: 3,
279359
})
280360
expect(result.metadata.predicates).toHaveLength(1)
361+
expect(result.output).toContain('predicate 1 "": skipped (blank)')
362+
expect(result.output).toContain('predicate 2 "id > 2": kept 3; removed 2')
363+
expect(result.output).toContain('predicate 3 " ": skipped (blank)')
281364
} finally {
282365
precedence.mockRestore()
283366
}

0 commit comments

Comments
 (0)