25 MB lightweight cross-platform database client for 100+ databases, including MySQL, PostgreSQL, SQLite, Redis, MongoDB, DuckDB, SQL Server, and Dameng. Built-in AI, MCP Server, CLI, desktop and Docker. | 轻量级跨平台数据库管理工具,支持 MySQL、PostgreSQL、SQLite、Redis、MongoDB、达梦等 100+ 数据库,提供桌面端、Docker、CLI、内置 AI 助手和 MCP。
TypeScript aicliclickhousedatabasedatabase-clientdatabase-managementdockerguimcpmongodbmysqlpostgresqlredisrustsql-serversqlitetaurivue
import { describe, expect, it } from "vitest";
import { analyzeEditableQueryEditability, analyzeSelectStructureForDisplay, resolveSourceColumnsByOrdinal, type EditableQueryInfo, type EditableQuerySource } from "@/lib/sql/sqlAnalysis";
/**
* Mirrors queryStore.editableQuerySources: single-source analyses store the
* source on the analysis root, so tests normalize it exactly like the store
* before resolving result columns by ordinal.
*/
function displayTableSources(analysis: EditableQueryInfo, columns: readonly string[]): Array<{ source: EditableQuerySource; columns: { name: string }[] }> {
const source: EditableQuerySource = analysis.sources?.length
? analysis.sources[0]!
: {
key: `${analysis.tableAlias ?? analysis.tableName}:0`,
catalog: analysis.catalog,
catalogQuoted: analysis.catalogQuoted,
schema: analysis.schema,
schemaQuoted: analysis.schemaQuoted,
tableName: analysis.tableName,
tableNameQuoted: analysis.tableNameQuoted,
alias: analysis.tableAlias,
};
return [{ source, columns: columns.map((name) => ({ name })) }];
}
/**
* GROUP BY / HAVING queries stay read-only (`aggregation`) but still expose a
* display-only structural analysis so the result grid can resolve comments for
* result columns that map directly back to a base-table column. Aggregate,
* computed and ambiguous projections must stay unresolved (no guessed comment).
*/
describe("grouped query display column mapping", () => {
describe("display structure analysis", () => {
it("parses projections and sources for a GROUP BY query without making it editable", () => {
const editability = analyzeEditableQueryEditability("SELECT department, COUNT(*) AS total FROM users GROUP BY department");
expect(editability).toEqual({ editable: false, reason: "aggregation" });
const analysis = analyzeSelectStructureForDisplay("SELECT department, COUNT(*) AS total FROM users GROUP BY department");
expect(analysis).not.toBeNull();
if (!analysis) return;
expect(analysis.tableName).toBe("users");
expect(analysis.columns.map((column) => column.resultName)).toEqual(["department", "total"]);
expect(analysis.columns[0]).toMatchObject({ sourceName: "department" });
expect(analysis.groupByColumns).toEqual([expect.objectContaining({ sourceName: "department" })]);
expect(analysis.hasHavingClause).toBeUndefined();
expect(analysis.hasWindowClause).toBeUndefined();
// Aggregate expression has no direct source column.
expect(analysis.columns[1]?.sourceName).toBeUndefined();
});
it("parses HAVING queries without an explicit GROUP BY", () => {
const editability = analyzeEditableQueryEditability("SELECT department, COUNT(*) AS total FROM users GROUP BY department HAVING COUNT(*) > 1");
expect(editability).toEqual({ editable: false, reason: "aggregation" });
const analysis = analyzeSelectStructureForDisplay("SELECT department, COUNT(*) AS total FROM users GROUP BY department HAVING COUNT(*) > 1");
expect(analysis).not.toBeNull();
if (!analysis) return;
expect(analysis.columns.map((column) => column.resultName)).toEqual(["department", "total"]);
expect(analysis.hasHavingClause).toBe(true);
});
it("evaluates issue 11672 query editability", () => {
const sqls = [
`SELECT
R.P_ID,
R.SUBJECT,
COUNT(0) AS CNT_NUM
FROM COMMON.RECORD R
WHERE R.CREATE_TIME >= SYSDATE - INTERVAL '1' DAY
AND R.SUBJECT = '邮件提醒'
AND R.P_ID = '123214512'
GROUP BY R.P_ID, R.SUBJECT`,
`SELECT\r\nR.P_ID,\r\nR.SUBJECT,\r\nCOUNT(0) AS CNT_NUM\r\nFROM COMMON.RECORD R\r\nWHERE R.CREATE_TIME >= SYSDATE - INTERVAL '1' DAY\r\nAND R.SUBJECT = '邮件提醒'\r\nAND R.P_ID = '123214512'\r\nGROUP BY R.P_ID, R.SUBJECT`,
`SELECT R.P_ID, R.SUBJECT, COUNT(0) AS CNT_NUM FROM COMMON.RECORD R WHERE R.CREATE_TIME >= SYSDATE - INTERVAL '1' DAY AND R.SUBJECT = '邮件提醒' AND R.P_ID = '123214512' GROUP BY R.P_ID, R.SUBJECT`,
`SELECT R.P_ID, R.SUBJECT, COUNT(0) AS CNT_NUM FROM COMMON.RECORD R GROUP BY R.P_ID, R.SUBJECT`,
];
for (const sql of sqls) {
const editability = analyzeEditableQueryEditability(sql);
expect(editability.editable).toBe(false);
if (!editability.editable) {
expect(editability.reason).toBe("aggregation");
}
}
});
it("records window functions so mutation safety can reject them", () => {
const analysis = analyzeSelectStructureForDisplay("SELECT id, ROW_NUMBER() OVER (ORDER BY id) AS row_number, COUNT(*) FROM users GROUP BY id");
expect(analysis).not.toBeNull();
if (!analysis) return;
expect(analysis.groupByColumns).toEqual([expect.objectContaining({ sourceName: "id" })]);
expect(analysis.hasWindowClause).toBe(true);
});
it("records a top-level RIGHT JOIN so a nullable root source cannot become editable", () => {
const analysis = analyzeSelectStructureForDisplay("SELECT u.id, COUNT(*) FROM users u RIGHT OUTER JOIN orders o ON o.user_id = u.id GROUP BY u.id");
expect(analysis).not.toBeNull();
if (!analysis) return;
expect(analysis.hasRightJoinClause).toBe(true);
});
it("parses multi-source grouped queries with per-source qualifiers", () => {
const analysis = analyzeSelectStructureForDisplay("SELECT u.department, COUNT(o.id) AS order_count FROM users u JOIN orders o ON o.user_id = u.id GROUP BY u.department");
expect(analysis).not.toBeNull();
if (!analysis) return;
expect(analysis.sources!.map((source) => source.tableName)).toEqual(["users", "orders"]);
expect(analysis.columns.map((column) => column.resultName)).toEqual(["department", "order_count"]);
expect(analysis.columns[0]?.sourceKey).toBe("u:0");
});
it("returns null for unparseable / dangerous display shapes", () => {
expect(analyzeSelectStructureForDisplay("WITH x AS (SELECT 1) SELECT * FROM x")).toBeNull();
expect(analyzeSelectStructureForDisplay("SELECT a FROM (SELECT 1 AS a) t")).toBeNull();
expect(analyzeSelectStructureForDisplay("INSERT INTO t VALUES (1)")).toBeNull();
});
});
describe("ordinal resolution", () => {
it("maps direct physical columns and leaves aggregate expressions unresolved (T1)", () => {
const analysis = analyzeSelectStructureForDisplay("SELECT department, COUNT(*) AS total FROM users GROUP BY department");
expect(analysis).not.toBeNull();
if (!analysis) return;
const resolved = resolveSourceColumnsByOrdinal("mysql", analysis, displayTableSources(analysis, ["department", "id"]), 2);
expect(resolved).toEqual([{ sourceKey: "users:0", sourceColumn: "department" }, undefined]);
});
it("resolves aliased physical columns back to the base column (T2)", () => {
const analysis = analyzeSelectStructureForDisplay("SELECT department AS dept, COUNT(*) AS total FROM users GROUP BY department");
expect(analysis).not.toBeNull();
if (!analysis) return;
const resolved = resolveSourceColumnsByOrdinal("mysql", analysis, displayTableSources(analysis, ["department", "id"]), 2);
expect(resolved).toEqual([{ sourceKey: "users:0", sourceColumn: "department" }, undefined]);
});
it("maps multiple grouped physical columns (T5)", () => {
const analysis = analyzeSelectStructureForDisplay("SELECT department, status, COUNT(*) FROM users GROUP BY department, status");
expect(analysis).not.toBeNull();
if (!analysis) return;
const resolved = resolveSourceColumnsByOrdinal("mysql", analysis, displayTableSources(analysis, ["department", "status", "id"]), 3);
expect(resolved).toEqual([{ sourceKey: "users:0", sourceColumn: "department" }, { sourceKey: "users:0", sourceColumn: "status" }, undefined]);
});
it("never lets an aggregate expression inherit its operand's comment (T6/T7)", () => {
const analysis = analyzeSelectStructureForDisplay("SELECT DATE(created_at) AS day, SUM(amount) AS total_amount FROM orders GROUP BY DATE(created_at)");
expect(analysis).not.toBeNull();
if (!analysis) return;
const resolved = resolveSourceColumnsByOrdinal("mysql", analysis, displayTableSources(analysis, ["created_at", "amount", "id"]), 2);
expect(resolved).toEqual([undefined, undefined]);
});
it("resolves qualified grouped columns across joined sources (T4) and keeps aggregates unresolved", () => {
const analysis = analyzeSelectStructureForDisplay("SELECT u.department, COUNT(o.id) AS order_count FROM users u JOIN orders o ON o.user_id = u.id GROUP BY u.department");
expect(analysis).not.toBeNull();
if (!analysis) return;
const usersSource = analysis.sources![0]!;
const ordersSource = analysis.sources![1]!;
const resolved = resolveSourceColumnsByOrdinal(
"mysql",
analysis,
[
{ source: usersSource, columns: [{ name: "id" }, { name: "department" }] },
{ source: ordersSource, columns: [{ name: "id" }, { name: "user_id" }, { name: "amount" }] },
],
2,
);
expect(resolved).toEqual([{ sourceKey: "u:0", sourceColumn: "department" }, undefined]);
});
it("keeps quoted identifier semantics for grouped columns (T8)", () => {
const analysis = analyzeSelectStructureForDisplay('SELECT "ID", COUNT(*) AS total FROM users GROUP BY "ID"');
expect(analysis).not.toBeNull();
if (!analysis) return;
const resolved = resolveSourceColumnsByOrdinal("postgres", analysis, displayTableSources(analysis, ["id", "ID"]), 2);
// Quoted "ID" matches the physical quoted column exactly; unquoted id stays distinct.
expect(resolved).toEqual([{ sourceKey: "users:0", sourceColumn: "ID" }, undefined]);
});
it("leaves ambiguous unqualified grouped columns unresolved (T9)", () => {
const analysis = analyzeSelectStructureForDisplay("SELECT id, COUNT(*) AS total FROM users u JOIN orders o ON o.user_id = u.id GROUP BY id");
expect(analysis).not.toBeNull();
if (!analysis) return;
const resolved = resolveSourceColumnsByOrdinal(
"mysql",
analysis,
[
{ source: analysis.sources![0]!, columns: [{ name: "id" }, { name: "department" }] },
{ source: analysis.sources![1]!, columns: [{ name: "id" }, { name: "user_id" }] },
],
2,
);
expect(resolved).toEqual([undefined, undefined]);
});
});
});