Skip to content

armgit / dbx

Public Imported from GitHub
0

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。

126 KB
Raw Blame
import { describe, expect, it } from "vitest";
import {
  buildPostgresSequenceLiteralCompletionItems,
  buildSelectStarExpansion,
  buildSqlCompletionItems,
  buildSqlCompletionItemsFromContext,
  getPostgresSequenceLiteralCompletionContext,
  getSqlCompletionContext,
  prepareSqlCompletionReplacement,
  quotedCompletionFilterPrefix,
  selectStarResultColumnsMatch,
  shouldAutoOpenSqlCompletion,
} from "@/lib/sql/sqlCompletion";
import { sqlCompletionContextFromSemantic } from "@/lib/sql/semantic/completion";
import { buildSqlSemanticModel } from "@/lib/sql/semantic/model";
import { originForSqlCompletionProvider, originForTypedSqlCompletionStart, shouldAllowSqlCompletionTrigger, type SqlCompletionTriggerFacts } from "@/lib/sql/sqlCompletionTriggerPolicy";

describe("OceanBase quoted routine lookup context", () => {
  it("does not trust a cached package owner when the current schema is unknown", () => {
    const sql = "CALL PKG.R";
    const items = buildSqlCompletionItems(sql, sql.length, {
      databaseType: "oceanbase-oracle",
      dialect: "oracle",
      tables: [],
      columnsByTable: new Map(),
      objects: [{ name: "RUN", schema: "OTHER", parentSchema: "OTHER", parentName: "PKG", type: "procedure", signature: "P NUMBER", routineId: "other-run" }],
    });
    expect(items.some((item) => item.label === "RUN")).toBe(false);
  });

  it.each([
    ["CALL PKG.R", "APP", "APP.PKG.RUN"],
    ["CALL OTHER.PKG.R", "APP", "OTHER.PKG.RUN"],
    ["CALL OTHER.R", "APP", "OTHER.RUN"],
    ["CALL PKG.R", "OTHER", "OTHER.PKG.RUN"],
  ])("keeps cached package owners scoped for %s in %s", (sql, currentSchema, expected) => {
    const objects = [
      { name: "RUN", schema: "APP", parentSchema: "APP", parentName: "PKG", type: "procedure" as const, signature: "P VARCHAR2", routineId: "app-run" },
      { name: "RUN", schema: "OTHER", parentSchema: "OTHER", parentName: "PKG", type: "procedure" as const, signature: "P NUMBER", routineId: "other-run" },
      { name: "RUN", schema: "OTHER", type: "procedure" as const, signature: "P DATE", routineId: "standalone-run" },
    ];
    const items = buildSqlCompletionItems(sql, sql.length, { databaseType: "oceanbase-oracle", dialect: "oracle", currentSchema, tables: [], columnsByTable: new Map(), objects });
    expect(items.filter((item) => item.label === "RUN").map((item) => item.info?.split("\n")[0])).toEqual([expected]);
  });

  it.each(['CALL "Mixed.Owner"."Do', 'CALL "Mixed.Owner"."Do"'])("preserves quoted prefixes in %s", (sql) => {
    const context = getSqlCompletionContext(sql, sql.length, { databaseType: "oceanbase-oracle" });
    expect(context).toMatchObject({ prefix: "Do", prefixQuoted: true, qualifierParts: ["Mixed.Owner"], qualifierQuoted: [true] });
  });
});

describe("SQL completion replacement", () => {
  const columnItem = { label: "price", type: "column" as const, apply: "price", boost: 0 };

  it("marks a standalone SELECT wildcard for replacement", () => {
    const sql = "SELECT * FROM users";
    const cursor = sql.indexOf("*");
    const context = getSqlCompletionContext(sql, cursor);

    expect(prepareSqlCompletionReplacement(sql, cursor, context, [columnItem]).items[0]).toMatchObject({ replaceSelectWildcard: true });
  });

  it("does not mark the multiplication operator before an untyped right operand", () => {
    const sql = "SELECT *qty FROM users";
    const cursor = sql.indexOf("*");
    const context = getSqlCompletionContext(sql, cursor);

    expect(prepareSqlCompletionReplacement(sql, cursor, context, [columnItem]).items[0]).not.toHaveProperty("replaceSelectWildcard");
  });

  it("does not mark an expression operator after another projection operand", () => {
    const sql = "SELECT amount + * FROM users";
    const cursor = sql.indexOf("*");
    const context = getSqlCompletionContext(sql, cursor);

    expect(prepareSqlCompletionReplacement(sql, cursor, context, [columnItem]).items[0]).not.toHaveProperty("replaceSelectWildcard");
  });

  it("marks a standalone wildcard after a SELECT modifier", () => {
    const sql = "SELECT DISTINCT * FROM users";
    const cursor = sql.indexOf("*");
    const context = getSqlCompletionContext(sql, cursor);

    expect(prepareSqlCompletionReplacement(sql, cursor, context, [columnItem]).items[0]).toMatchObject({ replaceSelectWildcard: true });
  });
});

describe("sqlCompletion keyword snippets", () => {
  it.each(["mysql", "postgres", "oracle"] as const)("offers the REPLACE keyword in %s CREATE OR statements", (databaseType) => {
    for (const text of ["CREATE OR ", "CREATE OR r", "CREATE OR repla", "-- heading\nCREATE /* comment */ OR repla"]) {
      const items = buildSqlCompletionItems(text, text.length, { tables: [], columnsByTable: new Map(), databaseType });
      expect(items[0], text).toMatchObject({ label: "REPLACE", type: "keyword" });
      expect(items[0].apply ?? items[0].label).toBe("REPLACE");
    }
  });

  it("preserves REPLACE function completion in SELECT expressions", () => {
    const text = "SELECT repla";
    const items = buildSqlCompletionItems(text, text.length, { tables: [], columnsByTable: new Map(), databaseType: "mysql" });
    expect(items[0]).toMatchObject({ label: "REPLACE", type: "function" });
    expect(items[0]?.apply).toMatch(/^REPLACE\(/);
  });

  it.each(["sqlserver", "sqlite"] as const)("does not suggest unsupported CREATE OR REPLACE syntax on %s", (databaseType) => {
    const text = "CREATE OR repla";
    const items = buildSqlCompletionItems(text, text.length, { tables: [], columnsByTable: new Map(), databaseType });
    expect(items.some((item) => item.type === "keyword" && item.label === "REPLACE")).toBe(false);
  });

  it("honors lower-case keyword preferences for CREATE OR REPLACE", () => {
    const text = "create or repla";
    const items = buildSqlCompletionItems(text, text.length, { tables: [], columnsByTable: new Map(), databaseType: "postgres", keywordCase: "lower" });
    expect(items[0]).toMatchObject({ label: "replace", type: "keyword" });
    expect(items[0].apply ?? items[0].label).toBe("replace");
  });

  it("auto-opens and suggests SELECT when typing sel", () => {
    const sql = "sel";
    const items = buildSqlCompletionItems(sql, sql.length, {
      tables: [],
      columnsByTable: new Map(),
    });

    expect(shouldAutoOpenSqlCompletion(sql, sql.length)).toBe(true);
    expect(items).toEqual(expect.arrayContaining([expect.objectContaining({ label: "select *", type: "snippet" }), expect.objectContaining({ label: "SELECT", type: "keyword" })]));
  });

  it("offers uppercase keyword completion when typing full lowercase keywords for case conversion (#8325)", () => {
    const testCases = [
      { sql: "select", word: "SELECT" },
      { sql: "SELECT id from", word: "FROM" },
      { sql: "SELECT * FROM users where", word: "WHERE" },
      { sql: "SELECT * FROM users WHERE id = 1 and", word: "AND" },
      { sql: "SELECT * FROM users WHERE id = 1 or", word: "OR" },
      { sql: "SELECT * FROM users WHERE id is", word: "IS" },
      { sql: "SELECT * FROM users WHERE id is null", word: "NULL" },
      { sql: "SELECT * FROM users WHERE id not", word: "NOT" },
      { sql: "SELECT * FROM users WHERE id between", word: "BETWEEN" },
      { sql: "SELECT * FROM users WHERE name like", word: "LIKE" },
      { sql: "SELECT * FROM users WHERE id in", word: "IN" },
      { sql: "SELECT * FROM users order", word: "ORDER" },
      { sql: "SELECT * FROM users ORDER by", word: "BY" },
      { sql: "SELECT * FROM users group", word: "GROUP" },
      { sql: "SELECT * FROM users GROUP by", word: "BY" },
      { sql: "SELECT * FROM users GROUP BY id having", word: "HAVING" },
      { sql: "SELECT * FROM users limit", word: "LIMIT" },
      { sql: "SELECT * FROM users LIMIT 10 offset", word: "OFFSET" },
      { sql: "SELECT * FROM users join", word: "JOIN" },
      { sql: "SELECT * FROM users inner", word: "INNER" },
      { sql: "SELECT * FROM users left", word: "LEFT" },
      { sql: "SELECT * FROM users right", word: "RIGHT" },
      { sql: "SELECT * FROM users full", word: "FULL" },
      { sql: "SELECT * FROM users cross", word: "CROSS" },
      { sql: "SELECT * FROM users JOIN orders on", word: "ON" },
      { sql: "UPDATE users set", word: "SET" },
      { sql: "INSERT into", word: "INTO" },
      { sql: "INSERT INTO users values", word: "VALUES" },
      { sql: "SELECT distinct", word: "DISTINCT" },
      { sql: "SELECT id as", word: "AS" },
      { sql: "SELECT CASE when", word: "WHEN" },
      { sql: "SELECT CASE WHEN 1 then", word: "THEN" },
      { sql: "SELECT CASE WHEN 1 THEN 2 else", word: "ELSE" },
      { sql: "SELECT CASE WHEN 1 THEN 2 ELSE 3 end", word: "END" },
      { sql: "CREATE table", word: "TABLE" },
      { sql: "DROP table", word: "TABLE" },
      { sql: "ALTER table", word: "TABLE" },
    ];

    for (const { sql, word } of testCases) {
      const legacy = getSqlCompletionContext(sql, sql.length);
      const semanticContext = sqlCompletionContextFromSemantic(buildSqlSemanticModel(sql, sql.length), legacy);
      const items = buildSqlCompletionItemsFromContext(semanticContext, {
        tables: [{ name: "users" }, { name: "orders" }],
        columnsByTable: new Map([
          [
            "users",
            [
              { name: "id", table: "users" },
              { name: "name", table: "users" },
            ],
          ],
          [
            "orders",
            [
              { name: "id", table: "orders" },
              { name: "user_id", table: "orders" },
            ],
          ],
        ]),
        keywordCase: "upper",
      });

      const keywordItem = items.find((item) => item.label === word && item.type === "keyword");
      expect(keywordItem, `Expected keyword ${word} in completion for "${sql}"`).toBeDefined();
      expect(keywordItem?.exactMatch, `Expected exactMatch flag for ${word}`).toBe(true);
      expect(
        items.slice(0, 2).map((item) => item.label),
        `Expected ${word} in top suggestions for "${sql}"`,
      ).toContain(word);
    }
  });

  it("offers lowercase keyword completion when typing full uppercase keywords with keywordCase 'lower' (#8325)", () => {
    const sql = "SELECT * FROM users WHERE";
    const legacy = getSqlCompletionContext(sql, sql.length);
    const semanticContext = sqlCompletionContextFromSemantic(buildSqlSemanticModel(sql, sql.length), legacy);
    const items = buildSqlCompletionItemsFromContext(semanticContext, {
      tables: [{ name: "users" }],
      columnsByTable: new Map([
        [
          "users",
          [
            { name: "id", table: "users" },
            { name: "name", table: "users" },
          ],
        ],
      ]),
      keywordCase: "lower",
    });

    const whereItem = items.find((item) => item.label === "where" && item.type === "keyword");
    expect(whereItem).toBeDefined();
    expect(whereItem?.exactMatch).toBe(true);
    expect(items[0]?.label).toBe("where");
  });

  it("keeps multi-word keywords available alongside individual keywords (#8325)", () => {
    const orderSql = "SELECT * FROM users order";
    const orderLegacy = getSqlCompletionContext(orderSql, orderSql.length);
    const orderContext = sqlCompletionContextFromSemantic(buildSqlSemanticModel(orderSql, orderSql.length), orderLegacy);
    const orderItems = buildSqlCompletionItemsFromContext(orderContext, {
      tables: [{ name: "users" }],
      columnsByTable: new Map([
        [
          "users",
          [
            { name: "id", table: "users" },
            { name: "name", table: "users" },
          ],
        ],
      ]),
      keywordCase: "upper",
    });

    expect(orderItems.some((item) => item.label === "ORDER" && item.type === "keyword")).toBe(true);
    expect(orderItems.some((item) => item.label === "ORDER BY" && item.type === "keyword")).toBe(true);

    const groupSql = "SELECT * FROM users group";
    const groupLegacy = getSqlCompletionContext(groupSql, groupSql.length);
    const groupContext = sqlCompletionContextFromSemantic(buildSqlSemanticModel(groupSql, groupSql.length), groupLegacy);
    const groupItems = buildSqlCompletionItemsFromContext(groupContext, {
      tables: [{ name: "users" }],
      columnsByTable: new Map([
        [
          "users",
          [
            { name: "id", table: "users" },
            { name: "name", table: "users" },
          ],
        ],
      ]),
      keywordCase: "upper",
    });

    expect(groupItems.some((item) => item.label === "GROUP" && item.type === "keyword")).toBe(true);
    expect(groupItems.some((item) => item.label === "GROUP BY" && item.type === "keyword")).toBe(true);
  });

  it("offers DuckDB-specific query and statement keywords", () => {
    const expectedKeywords = ["QUALIFY", "SUMMARIZE", "PIVOT", "UNPIVOT", "ASOF", "POSITIONAL", "FROM"];

    for (const keyword of expectedKeywords) {
      const prefix = keyword.slice(0, 4);
      const sql = keyword === "FROM" || keyword === "SUMMARIZE" ? prefix : `SELECT * FROM items ${prefix}`;
      const items = buildSqlCompletionItems(sql, sql.length, {
        tables: [],
        columnsByTable: new Map(),
        databaseType: "duckdb",
      });

      expect(items, keyword).toEqual(expect.arrayContaining([expect.objectContaining({ label: keyword, type: "keyword" })]));
    }
  });

  it("does not expose DuckDB-only keywords to other databases", () => {
    const sql = "SELECT * FROM items qua";
    const items = buildSqlCompletionItems(sql, sql.length, {
      tables: [],
      columnsByTable: new Map(),
      databaseType: "postgres",
    });

    expect(items.some((item) => item.label === "QUALIFY")).toBe(false);
  });

  describe("clause keywords after a FROM table reference (#13120)", () => {
    const routineNames = ["wms_handle_order", "WriteHistoryEntry", "WhereUsed", "web_hook", "w_h_e_r", "wh_sync", "group_hist", "order_sync"];
    const objects = routineNames.flatMap((name) => [
      { name, schema: "app", type: "procedure" as const, signature: "" },
      { name: `${name}_fn`, schema: "app", type: "function" as const, signature: "" },
    ]);

    function rankedItems(sql: string, databaseType: "mysql" | "postgres" | "sqlserver") {
      const legacy = getSqlCompletionContext(sql, sql.length, { databaseType });
      const context = sqlCompletionContextFromSemantic(buildSqlSemanticModel(sql, sql.length, { databaseType }), legacy);
      return buildSqlCompletionItemsFromContext(context, {
        databaseType,
        tables: [{ name: "users" }],
        columnsByTable: new Map([["users", [{ name: "id", table: "users" }]]]),
        objects,
        keywordCase: "upper",
      });
    }

    it.each([
      ["SELECT * FROM users w", "WHERE"],
      ["SELECT * FROM users wh", "WHERE"],
      ["SELECT * FROM users whe", "WHERE"],
      ["SELECT * FROM users wher", "WHERE"],
      ["SELECT * FROM users u wh", "WHERE"],
      ["SELECT * FROM users AS u gr", "GROUP"],
      ["SELECT * FROM users ord", "ORDER"],
    ])("ranks the clause keyword above routines for %s", (sql, keyword) => {
      for (const databaseType of ["mysql", "postgres", "sqlserver"] as const) {
        const items = rankedItems(sql, databaseType);
        const keywordIndex = items.findIndex((item) => item.type === "keyword" && item.label === keyword);
        const firstRoutineIndex = items.findIndex((item) => item.type === "function");
        expect(keywordIndex, `${databaseType} ${sql}`).toBeGreaterThanOrEqual(0);
        expect(items[0]?.type, `${databaseType} ${sql}`).toBe("keyword");
        if (firstRoutineIndex >= 0) expect(keywordIndex, `${databaseType} ${sql}`).toBeLessThan(firstRoutineIndex);
      }
    });

    it("keeps routines ahead of keywords where an expression is expected", () => {
      const items = rankedItems("SELECT * FROM users WHERE id = wh", "mysql");

      expect(items[0]?.type).toBe("function");
    });
  });

  describe("clause keywords after a completed JOIN ... ON predicate (#13283)", () => {
    const objects = ["wms_handle_order", "WhereUsed", "web_hook", "wh_sync"].flatMap((name) => [
      { name, schema: "app", type: "procedure" as const, signature: "" },
      { name: `${name}_fn`, schema: "app", type: "function" as const, signature: "" },
    ]);

    function rankedItems(sql: string, databaseType: "mysql" | "postgres" | "sqlserver") {
      const legacy = getSqlCompletionContext(sql, sql.length, { databaseType });
      const context = sqlCompletionContextFromSemantic(buildSqlSemanticModel(sql, sql.length, { databaseType }), legacy);
      return buildSqlCompletionItemsFromContext(context, {
        databaseType,
        tables: [{ name: "orders" }, { name: "users" }],
        columnsByTable: new Map([
          [
            "orders",
            [
              { name: "user_id", table: "orders" },
              { name: "warehouse_id", table: "orders" },
            ],
          ],
          ["users", [{ name: "id", table: "users" }]],
        ]),
        objects,
        keywordCase: "upper",
      });
    }

    it.each([
      ["SELECT * FROM orders LEFT JOIN users ON users.id = orders.user_id w", "WHERE"],
      ["SELECT * FROM orders LEFT JOIN users ON users.id = orders.user_id wh", "WHERE"],
      ["SELECT * FROM orders JOIN users ON users.id = orders.user_id AND users.id > 0 w", "WHERE"],
      ["SELECT * FROM orders LEFT JOIN users ON users.id = orders.user_id gr", "GROUP"],
    ])("ranks the clause keyword first for %s", (sql, keyword) => {
      for (const databaseType of ["mysql", "postgres", "sqlserver"] as const) {
        const items = rankedItems(sql, databaseType);
        expect(items[0]?.type, `${databaseType} ${sql}`).toBe("keyword");
        expect(items[0]?.label, `${databaseType} ${sql}`).toBe(keyword);
      }
    });

    it("keeps columns ahead of keywords while the ON predicate is still open", () => {
      const items = rankedItems("SELECT * FROM orders LEFT JOIN users ON users.id = orders.user_", "mysql");

      expect(items[0]?.label).toBe("user_id");
    });

    it.each([
      ["SELECT * FROM orders LEFT JOIN users ON users.id = orders.user_id GROUP BY w", "orders.warehouse_id"],
      ["SELECT * FROM orders LEFT JOIN users ON users.id = orders.user_id ORDER BY w", "orders.warehouse_id"],
    ])("keeps the typed column first after a later clause boundary for %s", (sql, expected) => {
      for (const databaseType of ["mysql", "postgres", "sqlserver"] as const) {
        const items = rankedItems(sql, databaseType);
        expect(items[0]?.label, `${databaseType} ${sql}`).toBe(expected);
      }
    });

    it.each([
      ["... LIMIT 10 w", "SELECT * FROM orders LEFT JOIN users ON users.id = orders.user_id LIMIT 10 w", "SELECT * FROM orders LIMIT 10 w"],
      ["... UNION w", "SELECT * FROM orders LEFT JOIN users ON users.id = orders.user_id UNION w", "SELECT * FROM orders UNION w"],
    ])("ranks the same items with and without a preceding join predicate for %s", (_label, withJoin, withoutJoin) => {
      for (const databaseType of ["mysql", "postgres", "sqlserver"] as const) {
        const joined = rankedItems(withJoin, databaseType);
        const plain = rankedItems(withoutJoin, databaseType);
        expect(joined[0]?.label, `${databaseType} ${withJoin}`).toBe(plain[0]?.label);
        expect(joined[0]?.type, `${databaseType} ${withJoin}`).toBe(plain[0]?.type);
      }
    });
  });
});

describe("SQL Server datepart completion", () => {
  it.each(["DATEADD", "DATEDIFF", "DATEPART", "DATENAME"])("suggests datepart values for the first %s argument", (functionName) => {
    const sql = `SELECT ${functionName}(d`;
    const items = buildSqlCompletionItems(sql, sql.length, {
      tables: [],
      columnsByTable: new Map(),
      databaseType: "sqlserver",
      dialect: "sqlserver",
      keywordCase: "lower",
    });

    expect(items).toEqual(expect.arrayContaining([expect.objectContaining({ label: "day", type: "keyword" }), expect.objectContaining({ label: "dayofyear", type: "keyword" })]));
    expect(items.some((item) => item.label === "deadlock_priority")).toBe(false);
  });

  it("auto-opens the datepart list immediately after the opening parenthesis", () => {
    const sql = "SELECT DATEADD(";

    expect(shouldAutoOpenSqlCompletion(sql, sql.length, { databaseType: "sqlserver", dialect: "sqlserver" })).toBe(true);
  });

  it("stops offering datepart values after the first argument", () => {
    const sql = "SELECT DATEADD(day, d";

    expect(getSqlCompletionContext(sql, sql.length, { databaseType: "sqlserver", dialect: "sqlserver" }).preferredValueKeywords).toBeUndefined();
  });

  it("does not apply SQL Server datepart values to other dialects", () => {
    const sql = "SELECT DATEADD(d";

    expect(getSqlCompletionContext(sql, sql.length, { databaseType: "mysql", dialect: "mysql" }).preferredValueKeywords).toBeUndefined();
  });
});

describe("SQL Server local variable completion", () => {
  const completionItems = (sql: string, cursor = sql.length) =>
    buildSqlCompletionItems(sql, cursor, {
      tables: [],
      columnsByTable: new Map(),
      databaseType: "sqlserver",
      dialect: "sqlserver",
    });

  it("suggests variables declared earlier in the current batch", () => {
    const sql = `CREATE OR ALTER PROCEDURE dbo.find_customer AS
BEGIN
  DECLARE @customer_id INT = 1,
          @customer_name NVARCHAR(100);
  SELECT @customer_`;

    expect(completionItems(sql)).toEqual(expect.arrayContaining([expect.objectContaining({ label: "@customer_id", type: "variable" }), expect.objectContaining({ label: "@customer_name", type: "variable" })]));
  });

  it("suggests procedure parameters in the current routine body", () => {
    const sql = `CREATE OR ALTER PROCEDURE dbo.find_customer
  @customer_id AS INT,
  @customer_name NVARCHAR(100) = NULL OUTPUT
AS
BEGIN
  SELECT @customer_`;

    expect(completionItems(sql)).toEqual(expect.arrayContaining([expect.objectContaining({ label: "@customer_id", type: "variable" }), expect.objectContaining({ label: "@customer_name", type: "variable" })]));
  });

  it("suggests parenthesized function parameters without collecting body references", () => {
    const sql = `ALTER FUNCTION dbo.customer_label(
  @customer_id INT,
  @separator NVARCHAR(10) = N'-'
)
RETURNS NVARCHAR(200)
AS
BEGIN
  DECLARE @label NVARCHAR(200);
  SET @phantom = 1;
  SELECT @`;
    const variables = completionItems(sql)
      .filter((item) => item.type === "variable")
      .map((item) => item.label);

    expect(variables).toEqual(expect.arrayContaining(["@customer_id", "@separator", "@label"]));
    expect(variables).not.toContain("@phantom");
  });

  it("ignores later declarations and declarations inside comments or strings", () => {
    const sql = `DECLARE @visible INT;
-- DECLARE @commented INT;
SELECT 'DECLARE @string_value INT';
SELECT @|
DECLARE @later INT;`;
    const cursor = sql.indexOf("|");
    const variables = completionItems(sql.replace("|", ""), cursor)
      .filter((item) => item.type === "variable")
      .map((item) => item.label);

    expect(variables).toContain("@visible");
    expect(variables).not.toContain("@commented");
    expect(variables).not.toContain("@string_value");
    expect(variables).not.toContain("@later");
  });

  it("does not leak variables across GO batch boundaries", () => {
    const sql = `DECLARE @old_batch INT;
GO
DECLARE @current_batch INT;
SELECT @`;
    const variables = completionItems(sql)
      .filter((item) => item.type === "variable")
      .map((item) => item.label);

    expect(variables).toContain("@current_batch");
    expect(variables).not.toContain("@old_batch");
  });

  it("preserves SQL Server system variable completion", () => {
    const sql = "DECLARE @row_id INT; SELECT @@row";

    expect(completionItems(sql)).toEqual(expect.arrayContaining([expect.objectContaining({ label: "@@ROWCOUNT", type: "keyword" })]));
  });
});

describe("MySQL DESCRIBE table completion", () => {
  it.each(["DESC", "DESCRIBE"])("treats %s as a table-name context", (keyword) => {
    const sql = `${keyword} ord`;
    const options = { databaseType: "mysql", dialect: "mysql" } as const;
    const context = sqlCompletionContextFromSemantic(buildSqlSemanticModel(sql, sql.length, options), getSqlCompletionContext(sql, sql.length, options));
    expect(context.suggestTables).toBe(true);
    expect(shouldAutoOpenSqlCompletion(sql, sql.length, options)).toBe(true);
    expect(
      buildSqlCompletionItemsFromContext(context, {
        tables: [{ name: "orders" }],
        columnsByTable: new Map(),
        ...options,
      }),
    ).toEqual(expect.arrayContaining([expect.objectContaining({ label: "orders", type: "table" })]));
  });

  it("does not treat PostgreSQL ORDER BY DESC as a table-name context", () => {
    const sql = "SELECT * FROM users ORDER BY name DESC ord";
    const options = { databaseType: "postgres", dialect: "postgres" } as const;
    const context = sqlCompletionContextFromSemantic(buildSqlSemanticModel(sql, sql.length, options), getSqlCompletionContext(sql, sql.length, options));

    expect(context.suggestTables).toBe(false);
    expect(
      buildSqlCompletionItemsFromContext(context, {
        tables: [{ name: "orders" }],
        columnsByTable: new Map(),
        ...options,
      }).some((item) => item.type === "table"),
    ).toBe(false);
  });
});

describe("PostgreSQL sequence literal completion", () => {
  it.each(["nextval", "currval", "setval"])("recognizes the first %s regclass literal", (functionName) => {
    const sql = `SELECT ${functionName}('public.order_`;
    const context = getPostgresSequenceLiteralCompletionContext(sql, sql.length, "postgres");

    expect(context).toEqual(
      expect.objectContaining({
        prefix: "order_",
        schema: "public",
        schemaQuoted: false,
        nameQuoted: false,
      }),
    );
    expect(context?.from).toBe(sql.lastIndexOf("order_"));
    expect(shouldAutoOpenSqlCompletion(sql, sql.length, { databaseType: "postgres" })).toBe(true);
  });

  it("preserves quoted mixed-case schema and sequence identifiers", () => {
    const sql = `SELECT pg_catalog.nextval('"App"."Order`;
    const context = getPostgresSequenceLiteralCompletionContext(sql, sql.length, "postgres");

    expect(context).toEqual(
      expect.objectContaining({
        prefix: "Order",
        schema: "App",
        schemaQuoted: true,
        nameQuoted: true,
        nameQuoteClosed: false,
      }),
    );
    expect(
      buildPostgresSequenceLiteralCompletionItems(context!, [
        { name: "OrderSequence", schema: "App", type: "sequence" },
        { name: "order_sequence", schema: "App", type: "sequence" },
      ]),
    ).toEqual([
      expect.objectContaining({
        label: "OrderSequence",
        apply: 'OrderSequence"',
        replaceClosingQuote: '"',
        detail: "sequence in App",
      }),
    ]);
  });

  it("keeps doubled apostrophes inside the sequence identifier and escapes insertion", () => {
    const sql = `SELECT nextval('"customer''s_`;
    const context = getPostgresSequenceLiteralCompletionContext(sql, sql.length, "postgres");

    expect(context).toEqual(expect.objectContaining({ prefix: "customer's_", nameQuoted: true }));
    expect(buildPostgresSequenceLiteralCompletionItems(context!, [{ name: "customer's_seq", schema: "public", type: "sequence" }])).toEqual([expect.objectContaining({ label: "customer's_seq", apply: `customer''s_seq"` })]);
  });

  it("quotes an accepted mixed-case identifier in an unquoted literal", () => {
    const sql = "SELECT nextval('mix";
    const context = getPostgresSequenceLiteralCompletionContext(sql, sql.length, "postgres");

    expect(buildPostgresSequenceLiteralCompletionItems(context!, [{ name: "MixedSequence", schema: "public", type: "sequence" }])).toEqual([expect.objectContaining({ label: "MixedSequence", filterText: "MixedSequence", apply: '"MixedSequence"' })]);
  });

  it("does not expose sequence metadata through ordinary SQL object completion", () => {
    const sql = "SELECT order_";
    const items = buildSqlCompletionItems(sql, sql.length, {
      tables: [],
      objects: [{ name: "order_seq", schema: "public", type: "sequence" }],
      columnsByTable: new Map(),
      databaseType: "postgres",
      dialect: "postgres",
    });

    expect(items.some((item) => item.label === "order_seq")).toBe(false);
  });

  it("suggests Oracle sequences in SQL expressions and select lists", () => {
    const sql = "SELECT ord";
    const unqualifiedItems = buildSqlCompletionItems(sql, sql.length, {
      tables: [],
      objects: [{ name: "ORDER_SEQ", schema: "APP", type: "sequence" }],
      columnsByTable: new Map(),
      databaseType: "oracle",
      dialect: "oracle",
      currentSchema: "APP",
    });

    expect(unqualifiedItems).toEqual(
      expect.arrayContaining([
        expect.objectContaining({
          label: "ORDER_SEQ",
          type: "sequence",
          detail: "sequence in APP",
          apply: "ORDER_SEQ",
        }),
      ]),
    );

    const qualifiedItems = buildSqlCompletionItems(sql, sql.length, {
      tables: [],
      objects: [{ name: "ORDER_SEQ", schema: "APP", type: "sequence" }],
      columnsByTable: new Map(),
      databaseType: "oracle",
      dialect: "oracle",
    });

    expect(qualifiedItems).toEqual(
      expect.arrayContaining([
        expect.objectContaining({
          label: "ORDER_SEQ",
          type: "sequence",
          detail: "sequence in APP",
          apply: "APP.ORDER_SEQ",
        }),
      ]),
    );
  });

  it("suggests Oracle sequences under schema qualifier", () => {
    const sql = "SELECT app.ord";
    const items = buildSqlCompletionItems(sql, sql.length, {
      tables: [],
      objects: [{ name: "ORDER_SEQ", schema: "APP", type: "sequence" }],
      columnsByTable: new Map(),
      databaseType: "oracle",
      dialect: "oracle",
    });

    expect(items).toEqual(
      expect.arrayContaining([
        expect.objectContaining({
          label: "ORDER_SEQ",
          type: "sequence",
          apply: "ORDER_SEQ",
        }),
      ]),
    );
  });

  it("suggests NEXTVAL and CURRVAL pseudo-columns for Oracle sequences", () => {
    const sql = "SELECT ORDER_SEQ.";
    const items = buildSqlCompletionItems(sql, sql.length, {
      tables: [],
      objects: [{ name: "ORDER_SEQ", schema: "APP", type: "sequence" }],
      columnsByTable: new Map(),
      databaseType: "oracle",
      dialect: "oracle",
    });

    expect(items).toEqual(
      expect.arrayContaining([
        expect.objectContaining({
          label: "NEXTVAL",
          type: "column",
          detail: "sequence pseudo-column",
        }),
        expect.objectContaining({
          label: "CURRVAL",
          type: "column",
          detail: "sequence pseudo-column",
        }),
      ]),
    );
  });

  it("suggests NEXTVAL and CURRVAL pseudo-columns for schema-qualified Oracle sequences", () => {
    const sql = "SELECT APP.ORDER_SEQ.";
    const items = buildSqlCompletionItems(sql, sql.length, {
      tables: [],
      objects: [{ name: "ORDER_SEQ", schema: "APP", type: "sequence" }],
      columnsByTable: new Map(),
      databaseType: "oracle",
      dialect: "oracle",
    });

    expect(items).toEqual(
      expect.arrayContaining([
        expect.objectContaining({
          label: "NEXTVAL",
          type: "column",
          detail: "sequence pseudo-column",
        }),
        expect.objectContaining({
          label: "CURRVAL",
          type: "column",
          detail: "sequence pseudo-column",
        }),
      ]),
    );
  });

  it("ranks tables and columns ahead of sequences for matching prefixes (#12266)", () => {
    const columnsByTable = new Map([["ORDERS", [{ name: "order_id", table: "ORDERS" }]]]);
    const items = buildSqlCompletionItems("SELECT ord FROM ORDERS", "SELECT ord".length, {
      tables: [{ name: "ORDERS" }],
      objects: [{ name: "ORDER_SEQ", schema: "APP", type: "sequence" }],
      columnsByTable,
      databaseType: "oracle",
      dialect: "oracle",
    });

    const columnIndex = items.findIndex((item) => item.label === "order_id" && item.type === "column");
    const sequenceIndex = items.findIndex((item) => item.label === "ORDER_SEQ" && item.type === "sequence");
    expect(columnIndex).toBeGreaterThanOrEqual(0);
    expect(sequenceIndex).toBeGreaterThanOrEqual(0);
    expect(columnIndex).toBeLessThan(sequenceIndex);
  });

  it("suppresses sequence suggestions and pseudo-columns when sequenceCompletionEnabled is false (#12266)", () => {
    const items = buildSqlCompletionItems("SELECT ord", "SELECT ord".length, {
      tables: [],
      objects: [{ name: "ORDER_SEQ", schema: "APP", type: "sequence" }],
      columnsByTable: new Map(),
      databaseType: "oracle",
      dialect: "oracle",
      sequenceCompletionEnabled: false,
    });
    expect(items.some((item) => item.label === "ORDER_SEQ")).toBe(false);

    const pseudoItems = buildSqlCompletionItems("SELECT ORDER_SEQ.", "SELECT ORDER_SEQ.".length, {
      tables: [],
      objects: [{ name: "ORDER_SEQ", schema: "APP", type: "sequence" }],
      columnsByTable: new Map(),
      databaseType: "oracle",
      dialect: "oracle",
      sequenceCompletionEnabled: false,
    });
    expect(pseudoItems.some((item) => item.label === "NEXTVAL" || item.label === "CURRVAL")).toBe(false);
  });

  it("suggests Dameng sequences like Oracle sequences", () => {
    const unqualifiedItems = buildSqlCompletionItems("SELECT ord", "SELECT ord".length, {
      tables: [],
      objects: [{ name: "ORDER_SEQ", schema: "APP", type: "sequence" }],
      columnsByTable: new Map(),
      databaseType: "dameng",
      dialect: "oracle",
      currentSchema: "APP",
    });

    expect(unqualifiedItems).toEqual(
      expect.arrayContaining([
        expect.objectContaining({
          label: "ORDER_SEQ",
          type: "sequence",
          detail: "sequence in APP",
          apply: "ORDER_SEQ",
        }),
      ]),
    );

    const pseudoColumnSql = "SELECT ORDER_SEQ.";
    const pseudoColumnItems = buildSqlCompletionItems(pseudoColumnSql, pseudoColumnSql.length, {
      tables: [],
      objects: [{ name: "ORDER_SEQ", schema: "APP", type: "sequence" }],
      columnsByTable: new Map(),
      databaseType: "dameng",
      dialect: "oracle",
    });

    expect(pseudoColumnItems).toEqual(
      expect.arrayContaining([
        expect.objectContaining({
          label: "NEXTVAL",
          type: "column",
          detail: "sequence pseudo-column",
        }),
        expect.objectContaining({
          label: "CURRVAL",
          type: "column",
          detail: "sequence pseudo-column",
        }),
      ]),
    );
  });

  it("does not suggest sequences for databases without Oracle-style sequences", () => {
    const items = buildSqlCompletionItems("SELECT ord", "SELECT ord".length, {
      tables: [],
      objects: [{ name: "ORDER_SEQ", schema: "APP", type: "sequence" }],
      columnsByTable: new Map(),
      databaseType: "mysql",
      dialect: "mysql",
    });

    expect(items).not.toEqual(expect.arrayContaining([expect.objectContaining({ label: "ORDER_SEQ", type: "sequence" })]));
  });

  it("respects keyword case for Oracle sequence pseudo-columns", () => {
    const sql = "SELECT ORDER_SEQ.n";
    const items = buildSqlCompletionItems(sql, sql.length, {
      tables: [],
      objects: [{ name: "ORDER_SEQ", schema: "APP", type: "sequence" }],
      columnsByTable: new Map(),
      databaseType: "oracle",
      dialect: "oracle",
      keywordCase: "lower",
    });

    expect(items).toEqual(
      expect.arrayContaining([
        expect.objectContaining({
          label: "nextval",
          apply: "nextval",
        }),
      ]),
    );
  });

  it("suggests Oracle sequence pseudo-columns in INSERT VALUES clause", () => {
    const sql = "INSERT INTO orders (id, name) VALUES (ORDER_SEQ.";
    const items = buildSqlCompletionItems(sql, sql.length, {
      tables: [{ name: "orders", schema: "APP" }],
      objects: [{ name: "ORDER_SEQ", schema: "APP", type: "sequence" }],
      columnsByTable: new Map([["APP.orders", [{ name: "id" }, { name: "name" }]]]),
      databaseType: "oracle",
      dialect: "oracle",
    });

    expect(items).toEqual(
      expect.arrayContaining([
        expect.objectContaining({
          label: "NEXTVAL",
          type: "column",
          detail: "sequence pseudo-column",
        }),
      ]),
    );
  });

  it("suggests OceanBase-Oracle sequences in SQL expressions and select lists", () => {
    const sql = "SELECT ord";
    const unqualifiedItems = buildSqlCompletionItems(sql, sql.length, {
      tables: [],
      objects: [{ name: "ORDER_SEQ", schema: "APP", type: "sequence" }],
      columnsByTable: new Map(),
      databaseType: "oceanbase-oracle",
      dialect: "oracle",
      currentSchema: "APP",
    });

    expect(unqualifiedItems).toEqual(
      expect.arrayContaining([
        expect.objectContaining({
          label: "ORDER_SEQ",
          type: "sequence",
          detail: "sequence in APP",
          apply: "ORDER_SEQ",
        }),
      ]),
    );

    const qualifiedItems = buildSqlCompletionItems(sql, sql.length, {
      tables: [],
      objects: [{ name: "ORDER_SEQ", schema: "APP", type: "sequence" }],
      columnsByTable: new Map(),
      databaseType: "oceanbase-oracle",
      dialect: "oracle",
    });

    expect(qualifiedItems).toEqual(
      expect.arrayContaining([
        expect.objectContaining({
          label: "ORDER_SEQ",
          type: "sequence",
          detail: "sequence in APP",
          apply: "APP.ORDER_SEQ",
        }),
      ]),
    );
  });

  it("suggests OceanBase-Oracle sequences under schema qualifier", () => {
    const sql = "SELECT app.ord";
    const items = buildSqlCompletionItems(sql, sql.length, {
      tables: [],
      objects: [{ name: "ORDER_SEQ", schema: "APP", type: "sequence" }],
      columnsByTable: new Map(),
      databaseType: "oceanbase-oracle",
      dialect: "oracle",
    });

    expect(items).toEqual(
      expect.arrayContaining([
        expect.objectContaining({
          label: "ORDER_SEQ",
          type: "sequence",
          apply: "ORDER_SEQ",
        }),
      ]),
    );
  });

  it("suggests NEXTVAL and CURRVAL pseudo-columns for OceanBase-Oracle sequences", () => {
    const sql = "SELECT ORDER_SEQ.";
    const items = buildSqlCompletionItems(sql, sql.length, {
      tables: [],
      objects: [{ name: "ORDER_SEQ", schema: "APP", type: "sequence" }],
      columnsByTable: new Map(),
      databaseType: "oceanbase-oracle",
      dialect: "oracle",
    });

    expect(items).toEqual(
      expect.arrayContaining([
        expect.objectContaining({
          label: "NEXTVAL",
          type: "column",
          detail: "sequence pseudo-column",
        }),
        expect.objectContaining({
          label: "CURRVAL",
          type: "column",
          detail: "sequence pseudo-column",
        }),
      ]),
    );
  });

  it("suggests NEXTVAL and CURRVAL pseudo-columns for schema-qualified OceanBase-Oracle sequences", () => {
    const sql = "SELECT APP.ORDER_SEQ.";
    const items = buildSqlCompletionItems(sql, sql.length, {
      tables: [],
      objects: [{ name: "ORDER_SEQ", schema: "APP", type: "sequence" }],
      columnsByTable: new Map(),
      databaseType: "oceanbase-oracle",
      dialect: "oracle",
    });

    expect(items).toEqual(
      expect.arrayContaining([
        expect.objectContaining({
          label: "NEXTVAL",
          type: "column",
          detail: "sequence pseudo-column",
        }),
        expect.objectContaining({
          label: "CURRVAL",
          type: "column",
          detail: "sequence pseudo-column",
        }),
      ]),
    );
  });

  it("respects keyword case for OceanBase-Oracle sequence pseudo-columns", () => {
    const sql = "SELECT ORDER_SEQ.n";
    const items = buildSqlCompletionItems(sql, sql.length, {
      tables: [],
      objects: [{ name: "ORDER_SEQ", schema: "APP", type: "sequence" }],
      columnsByTable: new Map(),
      databaseType: "oceanbase-oracle",
      dialect: "oracle",
      keywordCase: "lower",
    });

    expect(items).toEqual(
      expect.arrayContaining([
        expect.objectContaining({
          label: "nextval",
          apply: "nextval",
        }),
      ]),
    );
  });

  it("suggests OceanBase-Oracle sequence pseudo-columns in INSERT VALUES clause", () => {
    const sql = "INSERT INTO orders (id, name) VALUES (ORDER_SEQ.";
    const items = buildSqlCompletionItems(sql, sql.length, {
      tables: [{ name: "orders", schema: "APP" }],
      objects: [{ name: "ORDER_SEQ", schema: "APP", type: "sequence" }],
      columnsByTable: new Map([["APP.orders", [{ name: "id" }, { name: "name" }]]]),
      databaseType: "oceanbase-oracle",
      dialect: "oracle",
    });

    expect(items).toEqual(
      expect.arrayContaining([
        expect.objectContaining({
          label: "NEXTVAL",
          type: "column",
          detail: "sequence pseudo-column",
        }),
      ]),
    );
  });

  it.each([
    ["SELECT 'order_", "postgres"],
    ["SELECT nextval('order_", "mysql"],
    ["SELECT nextval('order_') || 'suffix", "postgres"],
    ["SELECT setval(42, 'order_", "postgres"],
    ["SELECT app.nextval('order_", "postgres"],
    ["SELECT app. nextval('order_", "postgres"],
    ["SELECT app.pg_catalog.nextval('order_", "postgres"],
    [`SELECT "PG_CATALOG".nextval('order_`, "postgres"],
    ["SELECT $$ nextval('order_", "postgres"],
    ["SELECT $body$ nextval('order_", "postgres"],
  ] as const)("does not enable sequence completion for unrelated literals: %s (%s)", (sql, databaseType) => {
    expect(getPostgresSequenceLiteralCompletionContext(sql, sql.length, databaseType)).toBeNull();
    expect(shouldAutoOpenSqlCompletion(sql, sql.length, { databaseType })).toBe(false);
  });
});

describe("SELECT star expansion", () => {
  it("reuses completion column ordering for an unqualified star", () => {
    const sql = "SELECT * FROM apis";
    const context = getSqlCompletionContext(sql, "SELECT *".length);

    expect(
      buildSelectStarExpansion(
        context,
        new Map([
          [
            "apis",
            [
              { name: "id", table: "apis" },
              { name: "created_at", table: "apis" },
              { name: "method", table: "apis" },
            ],
          ],
        ]),
      ),
    ).toBe("id, created_at, method");
  });

  it("expands a multi-table star with aliases and preserves duplicate column names", () => {
    const sql = "SELECT * FROM tVillage tV INNER JOIN tland tl ON tV.villageId = tl.villageId";
    const cursor = "SELECT *".length;
    const context = sqlCompletionContextFromSemantic(buildSqlSemanticModel(sql, cursor), getSqlCompletionContext(sql, cursor));

    expect(
      buildSelectStarExpansion(
        context,
        new Map([
          [
            "tVillage",
            [
              { name: "villageId", table: "tVillage" },
              { name: "villageName", table: "tVillage" },
            ],
          ],
          [
            "tland",
            [
              { name: "villageId", table: "tland" },
              { name: "landName", table: "tland" },
            ],
          ],
        ]),
      ),
    ).toBe("tV.villageId, tV.villageName, tl.villageId, tl.landName");
  });

  it("uses FROM/JOIN order even when the metadata map arrives in another order", () => {
    const sql = "SELECT * FROM tVillage tv INNER JOIN tland tl ON tv.villageId = tl.villageId";
    const cursor = "SELECT *".length;
    const context = sqlCompletionContextFromSemantic(buildSqlSemanticModel(sql, cursor), getSqlCompletionContext(sql, cursor));

    expect(
      buildSelectStarExpansion(
        context,
        new Map([
          [
            "tland",
            [
              { name: "landName", table: "tland" },
              { name: "villageId", table: "tland" },
            ],
          ],
          [
            "tVillage",
            [
              { name: "villageName", table: "tVillage" },
              { name: "villageId", table: "tVillage" },
            ],
          ],
        ]),
      ),
    ).toBe("tv.villageName, tv.villageId, tl.landName, tl.villageId");
  });

  it("preserves an alias while replacing only the star", () => {
    const sql = "SELECT ap.* FROM apis AS ap";
    const cursor = "SELECT ap.*".length;
    const context = sqlCompletionContextFromSemantic(buildSqlSemanticModel(sql, cursor), getSqlCompletionContext(sql, cursor));

    expect(
      buildSelectStarExpansion(
        context,
        new Map([
          [
            "apis",
            [
              { name: "id", table: "apis" },
              { name: "created_at", table: "apis" },
            ],
          ],
        ]),
      ),
    ).toBe("id, ap.created_at");
  });

  it.each([
    ["postgres", "postgres", '"Order Alias"', '"created at"'],
    ["mysql", "mysql", "`Order Alias`", "`created at`"],
    ["sqlserver", "sqlserver", "[Order Alias]", "[created at]"],
    ["oracle", "mysql", '"Order Alias"', '"created at"'],
  ] as const)("preserves a quoted %s alias for every expanded column", (databaseType, dialect, qualifierSql, quotedColumn) => {
    const sql = `SELECT ${qualifierSql}.* FROM orders AS ${qualifierSql}`;
    const cursor = sql.indexOf("*") + 1;
    const context = sqlCompletionContextFromSemantic(buildSqlSemanticModel(sql, cursor, { databaseType, dialect }), getSqlCompletionContext(sql, cursor, { databaseType, dialect }));

    expect(
      buildSelectStarExpansion(
        context,
        new Map([
          [
            "orders",
            [
              { name: "id", table: "orders" },
              { name: "created at", table: "orders" },
            ],
          ],
        ]),
        dialect,
        qualifierSql,
        databaseType,
      ),
    ).toBe(`${databaseType === "oracle" ? '"id"' : "id"}, ${qualifierSql}.${quotedColumn}`);
  });

  it("does not quote all-uppercase Oracle columns when expanding alias.* (regression #9163)", () => {
    const sql = "SELECT t.* FROM device_table AS t";
    const cursor = sql.indexOf("*") + 1;
    const context = sqlCompletionContextFromSemantic(buildSqlSemanticModel(sql, cursor, { databaseType: "oracle", dialect: "mysql" }), getSqlCompletionContext(sql, cursor, { databaseType: "oracle", dialect: "mysql" }));

    expect(
      buildSelectStarExpansion(
        context,
        new Map([
          [
            "device_table",
            [
              { name: "AGENT_NAME", table: "device_table" },
              { name: "PROTOCOL", table: "device_table" },
              { name: "created at", table: "device_table" },
            ],
          ],
        ]),
        "mysql",
        "t",
        "oracle",
      ),
    ).toBe('AGENT_NAME, t.PROTOCOL, t."created at"');
  });

  it("does not quote all-uppercase Oracle columns for an unqualified star when only databaseType is set", () => {
    const sql = "SELECT * FROM device_table";
    const cursor = "SELECT *".length;
    const context = sqlCompletionContextFromSemantic(buildSqlSemanticModel(sql, cursor, { databaseType: "oracle" }), getSqlCompletionContext(sql, cursor, { databaseType: "oracle" }));

    expect(
      buildSelectStarExpansion(
        context,
        new Map([
          [
            "device_table",
            [
              { name: "AGENT_NAME", table: "device_table" },
              { name: "SPARE1", table: "device_table" },
            ],
          ],
        ]),
        undefined,
        undefined,
        "oracle",
      ),
    ).toBe("AGENT_NAME, SPARE1");
  });

  it.each([
    ["oracle", "mysql"],
    ["oracle", undefined],
    ["oceanbase-oracle", "mysql"],
    ["oceanbase-oracle", undefined],
    [undefined, "oracle"],
  ] as const)("preserves required Oracle column quotes with databaseType %s and dialect %s", (databaseType, dialect) => {
    const columnsByTable = new Map([["orders", ["OrderId", "order_id", "AGENT_NAME", "SELECT", "created at", "ACCOUNT$SYS"].map((name) => ({ name, table: "orders" }))]]);
    const contextOptions = { databaseType, dialect: "mysql" as const };

    for (const [sql, expected] of [
      ["SELECT o.* FROM orders o", '"OrderId", o."order_id", o.AGENT_NAME, o."SELECT", o."created at", o.ACCOUNT$SYS'],
      ["SELECT * FROM orders", '"OrderId", "order_id", AGENT_NAME, "SELECT", "created at", ACCOUNT$SYS'],
    ]) {
      const cursor = sql.indexOf("*") + 1;
      const context = sqlCompletionContextFromSemantic(buildSqlSemanticModel(sql, cursor, contextOptions), getSqlCompletionContext(sql, cursor, contextOptions));

      expect(buildSelectStarExpansion(context, columnsByTable, dialect, context.qualifier, databaseType)).toBe(expected);
      expect(buildSelectStarExpansion(context, new Map(), dialect, context.qualifier, databaseType)).toBeNull();
    }
  });

  it("expands an unqualified star from result columns when the table has an alias", () => {
    const sql = "select *\nfrom apis as ap\nlimit 100;";
    const cursor = "select *".length;
    const context = sqlCompletionContextFromSemantic(buildSqlSemanticModel(sql, cursor), getSqlCompletionContext(sql, cursor));

    expect(
      buildSelectStarExpansion(
        context,
        new Map([
          [
            "apis",
            [
              { name: "id", table: "apis" },
              { name: "created_at", table: "apis" },
              { name: "updated_at", table: "apis" },
              { name: "deleted_at", table: "apis" },
              { name: "method", table: "apis" },
            ],
          ],
        ]),
      ),
    ).toBe("id, created_at, updated_at, deleted_at, method");
  });

  it("accepts result columns only when their source still contains the target star", () => {
    const currentSql = "select * from apis;\nselect * from users;";
    const sourceStatement = "select * from users";
    const sourceFrom = currentSql.lastIndexOf("select");
    const targetFrom = currentSql.lastIndexOf("*");

    expect(selectStarResultColumnsMatch({ currentSql, targetFrom, targetTo: targetFrom + 1, statementSql: sourceStatement, sourceStatement, sourceFrom, sourceTo: sourceFrom + sourceStatement.length })).toBe(true);
    expect(selectStarResultColumnsMatch({ currentSql, targetFrom: currentSql.indexOf("*"), targetTo: currentSql.indexOf("*") + 1, statementSql: "select * from apis", sourceStatement, sourceFrom, sourceTo: sourceFrom + sourceStatement.length })).toBe(false);
  });

  it("rejects stale and incomplete result source metadata", () => {
    expect(selectStarResultColumnsMatch({ currentSql: "select * from users", targetFrom: 7, targetTo: 8, statementSql: "select * from users", sourceStatement: "select * from apis" })).toBe(false);
    expect(selectStarResultColumnsMatch({ currentSql: "select * from users", targetFrom: 7, targetTo: 8, statementSql: "select * from users", sourceStatement: "select * from users", sourceFrom: 0 })).toBe(false);
  });
});

describe("sqlCompletion database functions", () => {
  it("suggests ClickHouse functions with canonical casing and preferred placeholders", () => {
    const sql = "SELECT tostart";
    const items = buildSqlCompletionItems(sql, sql.length, {
      databaseType: "clickhouse",
      tables: [],
      columnsByTable: new Map(),
      functionCase: "lower",
    });

    expect(items.find((item) => item.label === "toStartOfDay")).toMatchObject({
      type: "function",
      apply: "toStartOfDay(${value})",
    });
  });

  it("uses exact ClickHouse window function placeholders", () => {
    const denseRankSql = "SELECT dense_";
    const denseRankItems = buildSqlCompletionItems(denseRankSql, denseRankSql.length, {
      databaseType: "clickhouse",
      tables: [],
      columnsByTable: new Map(),
    });
    expect(denseRankItems.find((item) => item.label === "dense_rank")?.apply).toBe("dense_rank()");

    const ntileSql = "SELECT nti";
    const ntileItems = buildSqlCompletionItems(ntileSql, ntileSql.length, {
      databaseType: "clickhouse",
      tables: [],
      columnsByTable: new Map(),
    });
    expect(ntileItems.find((item) => item.label === "ntile")?.apply).toBe("ntile(${buckets})");
  });

  it("suggests ClickHouse FINAL and query modifiers as keywords", () => {
    const sql = "SELECT * FROM events FI";
    const items = buildSqlCompletionItems(sql, sql.length, {
      databaseType: "clickhouse",
      tables: [{ name: "events", type: "table" }],
      columnsByTable: new Map(),
    });

    expect(items.find((item) => item.label === "FINAL")).toMatchObject({ type: "keyword" });
  });

  it("suggests ClickHouse PREWHERE and SETTINGS as keywords", () => {
    const sql = "SELECT * FROM events SETT";
    const items = buildSqlCompletionItems(sql, sql.length, {
      databaseType: "clickhouse",
      tables: [{ name: "events", type: "table" }],
      columnsByTable: new Map(),
    });

    expect(items.find((item) => item.label === "SETTINGS")).toMatchObject({ type: "keyword" });
  });

  it("does not leak ClickHouse FINAL to MySQL", () => {
    const sql = "SELECT * FROM events FI";
    const items = buildSqlCompletionItems(sql, sql.length, {
      databaseType: "mysql",
      tables: [{ name: "events", type: "table" }],
      columnsByTable: new Map(),
    });

    expect(items.some((item) => item.label === "FINAL")).toBe(false);
  });

  it("does not leak ClickHouse-only functions to MySQL", () => {
    const sql = "SELECT tostart";
    const items = buildSqlCompletionItems(sql, sql.length, {
      databaseType: "mysql",
      tables: [],
      columnsByTable: new Map(),
    });

    expect(items.some((item) => item.label === "toStartOfDay")).toBe(false);
  });

  it("suggests only ClickHouse table functions alongside tables after FROM", () => {
    const sql = "SELECT * FROM num";
    const items = buildSqlCompletionItems(sql, sql.length, {
      databaseType: "clickhouse",
      tables: [{ name: "number_events", type: "table" }],
      columnsByTable: new Map(),
    });

    expect(items).toEqual(expect.arrayContaining([expect.objectContaining({ label: "numbers", type: "function" }), expect.objectContaining({ label: "number_events", type: "table" })]));
    expect(items.some((item) => item.label === "toStartOfDay")).toBe(false);
  });

  it("does not insert a duplicate opening parenthesis before an existing call", () => {
    const sql = "SELECT toStart()";
    const cursor = "SELECT toStart".length;
    const items = buildSqlCompletionItems(sql, cursor, {
      databaseType: "clickhouse",
      tables: [],
      columnsByTable: new Map(),
    });

    expect(items.find((item) => item.label === "toStartOfDay")?.apply).toBe("toStartOfDay");
  });

  it("suggests MySQL Unix timestamp functions with function snippets", () => {
    const fromUnixSql = "SELECT from_unix";
    const fromUnixItems = buildSqlCompletionItems(fromUnixSql, fromUnixSql.length, {
      databaseType: "mysql",
      tables: [],
      columnsByTable: new Map(),
    });
    const fromUnixTime = fromUnixItems.find((item) => item.label === "FROM_UNIXTIME");

    expect(fromUnixItems[0]).toBe(fromUnixTime);
    expect(fromUnixTime).toEqual(
      expect.objectContaining({
        type: "function",
        apply: "FROM_UNIXTIME(${unix_timestamp})",
      }),
    );

    const unixTimestampSql = "SELECT unix_time";
    const unixTimestampItems = buildSqlCompletionItems(unixTimestampSql, unixTimestampSql.length, {
      databaseType: "mysql",
      tables: [],
      columnsByTable: new Map(),
    });

    expect(unixTimestampItems[0]).toEqual(
      expect.objectContaining({
        label: "UNIX_TIMESTAMP",
        type: "function",
        apply: "UNIX_TIMESTAMP()",
      }),
    );
  });

  it("ranks MySQL function prefixes ahead of ordinary keyword prefixes", () => {
    const sql = "SELECT uni";
    const items = buildSqlCompletionItems(sql, sql.length, {
      databaseType: "mysql",
      tables: [],
      columnsByTable: new Map(),
    });

    expect(items.some((item) => item.type === "keyword")).toBe(true);
    expect(items[0]).toEqual(expect.objectContaining({ label: "UNIX_TIMESTAMP", type: "function" }));
  });

  it("does not expose MySQL-only functions to other databases", () => {
    const sql = "SELECT from_unix";
    const items = buildSqlCompletionItems(sql, sql.length, {
      databaseType: "postgres",
      tables: [],
      columnsByTable: new Map(),
    });

    expect(items.some((item) => item.label === "FROM_UNIXTIME")).toBe(false);
  });
});

describe("sqlCompletion quoted schema qualifiers", () => {
  it("parses quoted PostgreSQL schema names before a dot", () => {
    const sql = 'SELECT *\nFROM "order-management".';
    const context = getSqlCompletionContext(sql, sql.length);

    expect(context.qualifier).toBe("order-management");
    expect(context.prefix).toBe("");
    expect(context.suggestTables).toBe(true);
    expect(context.exclusiveColumnSuggestions).toBe(false);
  });

  it("suggests tables after a quoted schema qualifier", () => {
    const sql = 'SELECT *\nFROM "order-management".';
    const items = buildSqlCompletionItems(sql, sql.length, {
      dialect: "postgres",
      tables: [
        { name: "orders", schema: "order-management", type: "table" },
        { name: "shipments", schema: "order-management", type: "table" },
      ],
      columnsByTable: new Map(),
    });

    expect(items.some((item) => item.label === "orders" && item.type === "table")).toBe(true);
    expect(items.some((item) => item.label === "shipments" && item.type === "table")).toBe(true);
  });
});

describe("sqlCompletion table targets", () => {
  it("suggests tables after a database qualifier in an EXISTS table list", () => {
    const sql = "SELECT * FROM aa.tb t WHERE EXISTS (SELECT 1 FROM aa.tb1 t1, aa.";
    const context = getSqlCompletionContext(sql, sql.length);
    const items = buildSqlCompletionItems(sql, sql.length, {
      databaseType: "mysql",
      tables: [{ name: "tb2", schema: "aa", type: "table" }],
      columnsByTable: new Map(),
    });

    expect(context.qualifier).toBe("aa");
    expect(context.suggestTables).toBe(true);
    expect(items).toEqual(expect.arrayContaining([expect.objectContaining({ label: "tb2", type: "table" })]));
  });

  it("does not suggest aliases while completing an empty FROM target before LIMIT", () => {
    const sql = "SELECT *\nFROM \nLIMIT 100;";
    const cursor = "SELECT *\nFROM ".length;
    const items = buildSqlCompletionItems(sql, cursor, {
      tables: [{ name: "users", type: "table" }],
      columnsByTable: new Map(),
    });

    expect(items.some((item) => item.type === "snippet" && item.detail === "alias for LIMIT")).toBe(false);
    expect(items.some((item) => item.type === "table" && item.label === "users")).toBe(true);
  });
});

describe("sqlCompletion table aliases", () => {
  it("uses initials from all words for generated aliases", () => {
    const sql = "SELECT * FROM mat";
    const items = buildSqlCompletionItems(sql, sql.length, {
      tables: [{ name: "materials_order_item", type: "table" }],
      columnsByTable: new Map(),
      autoAliasTables: true,
    });

    const table = items.find((item) => item.label === "materials_order_item" && item.type === "table");
    expect(table?.apply).toBe("materials_order_item moi");
  });

  it("uses every word initial for longer multi-word names", () => {
    const sql = "SELECT * FROM sup";
    const items = buildSqlCompletionItems(sql, sql.length, {
      tables: [{ name: "super_long_customer_order_history_archive_snapshot_daily_replica", type: "table" }],
      columnsByTable: new Map(),
      autoAliasTables: true,
    });

    const table = items.find((item) => item.label === "super_long_customer_order_history_archive_snapshot_daily_replica" && item.type === "table");
    expect(table?.apply).toBe("super_long_customer_order_history_archive_snapshot_daily_replica slcohasdr");
  });

  it("applies generated aliases to table completions when enabled", () => {
    const sql = "SELECT * FROM ord";
    const items = buildSqlCompletionItems(sql, sql.length, {
      tables: [{ name: "order_items", type: "table" }],
      columnsByTable: new Map(),
      autoAliasTables: true,
    });

    const table = items.find((item) => item.label === "order_items" && item.type === "table");
    expect(table?.apply).toBe("order_items oi");
  });

  it("omits AS from Oracle table alias completions", () => {
    const sql = "SELECT * FROM ord";
    const items = buildSqlCompletionItems(sql, sql.length, {
      tables: [{ name: "order_items", type: "table" }],
      columnsByTable: new Map(),
      databaseType: "oracle",
      autoAliasTables: true,
    });

    const table = items.find((item) => item.label === "order_items" && item.type === "table");
    // The lowercase table name must stay quoted (#9526); the generated alias is a new identifier and stays bare.
    expect(table?.apply).toBe('"order_items" oi');
  });

  it("never adds generated aliases to Cassandra table completions", () => {
    const sql = "SELECT * FROM ord";
    const items = buildSqlCompletionItems(sql, sql.length, {
      tables: [{ name: "order_items", type: "table" }],
      columnsByTable: new Map(),
      databaseType: "cassandra",
      autoAliasTables: true,
    });

    const table = items.find((item) => item.label === "order_items" && item.type === "table");
    expect(table?.apply).toBe("order_items");
  });

  it("does not suggest table aliases for Cassandra", () => {
    const sql = "SELECT * FROM order_items ";
    const items = buildSqlCompletionItems(sql, sql.length, {
      tables: [{ name: "order_items", type: "table" }],
      columnsByTable: new Map(),
      databaseType: "cassandra",
    });

    expect(items.some((item) => item.type === "snippet" && item.detail === "alias for order_items")).toBe(false);
  });

  it("keeps plain table completions when generated aliases are disabled", () => {
    const sql = "SELECT * FROM ord";
    const items = buildSqlCompletionItems(sql, sql.length, {
      tables: [{ name: "order_items", type: "table" }],
      columnsByTable: new Map(),
      autoAliasTables: false,
    });

    const table = items.find((item) => item.label === "order_items" && item.type === "table");
    expect(table?.apply).toBe("order_items");
  });

  it("omits AS from Oracle alias suggestions", () => {
    const sql = "SELECT * FROM order_items ";
    const items = buildSqlCompletionItems(sql, sql.length, {
      tables: [{ name: "order_items", type: "table" }],
      columnsByTable: new Map(),
      databaseType: "oracle",
    });

    const alias = items.find((item) => item.type === "snippet" && item.detail === "alias for order_items");
    expect(alias?.apply).toBe("oi ");
  });

  it("uses a numbered alias when the generated table alias already exists", () => {
    const sql = "SELECT * FROM order_items oi JOIN ord";
    const items = buildSqlCompletionItems(sql, sql.length, {
      tables: [{ name: "order_items", type: "table" }],
      columnsByTable: new Map(),
      autoAliasTables: true,
    });

    const table = items.find((item) => item.label === "order_items" && item.type === "table");
    expect(table?.apply).toBe("order_items oi2");
  });

  it("applies generated aliases in comma-separated FROM table lists", () => {
    const sql = "SELECT * FROM users u, ord";
    const items = buildSqlCompletionItems(sql, sql.length, {
      tables: [{ name: "order_items", type: "table" }],
      columnsByTable: new Map(),
      autoAliasTables: true,
    });

    const table = items.find((item) => item.label === "order_items" && item.type === "table");
    expect(table?.apply).toBe("order_items oi");
  });

  it("does not apply generated aliases to non-query table completions", () => {
    const sql = "INSERT INTO ord";
    const items = buildSqlCompletionItems(sql, sql.length, {
      tables: [{ name: "order_items", type: "table" }],
      columnsByTable: new Map(),
      autoAliasTables: true,
    });

    const table = items.find((item) => item.label === "order_items" && item.type === "table");
    expect(table?.apply).toBe("order_items");
  });

  it("emits table aliases without AS on every dialect (issue #9525)", () => {
    // `FROM orders AS o` is rejected by Oracle and the Oracle-compatible profiles, while the
    // implicit form is accepted everywhere, so generated SQL must use it for all dialects.
    for (const databaseType of ["postgres", "mysql", "sqlserver", "oracle", "sqlite", "clickhouse"] as const) {
      const sql = "SELECT * FROM ord";
      const items = buildSqlCompletionItems(sql, sql.length, {
        databaseType,
        tables: [{ name: "order_items", type: "table" }],
        columnsByTable: new Map(),
        autoAliasTables: true,
      });

      const table = items.find((item) => item.label === "order_items" && item.type === "table");
      // Oracle additionally double-quotes the identifier to preserve its stored case.
      expect(table?.apply, databaseType).not.toContain(" AS ");
      expect(table?.apply, databaseType).toMatch(/order_items"? oi$/);
    }
  });
});

describe("sqlCompletion scoped context classification", () => {
  it("classifies JOIN table contexts", () => {
    const sql = "SELECT * FROM users u JOIN ";
    const context = getSqlCompletionContext(sql, sql.length);

    expect(context.contextKind).toBe("join");
    expect(context.suggestTables).toBe(true);
    expect(context.exclusiveTableSuggestions).toBe(true);
  });

  it("classifies alias-qualified column contexts", () => {
    const sql = "SELECT * FROM users u WHERE u.";
    const context = getSqlCompletionContext(sql, sql.length);

    expect(context.contextKind).toBe("alias_column");
    expect(context.qualifier).toBe("u");
    expect(context.suggestColumns).toBe(true);
  });

  it("keeps alias-qualified column context after select-list subqueries", () => {
    const sql = `
      SELECT
        p.id,
        p.create_user_name 'creator',
        (SELECT t.\`code\` FROM sys_user t WHERE t.user_id = p.apply_user_id) 'creator_code',
        p.
      FROM sys_process p
      LIMIT 10
    `;
    const cursor = sql.indexOf("p.\n      FROM");
    const context = getSqlCompletionContext(sql, cursor + 2);

    expect(context.contextKind).toBe("alias_column");
    expect(context.qualifier).toBe("p");
    expect(context.suggestTables).toBe(false);
    expect(context.exclusiveTableSuggestions).toBe(false);
    expect(context.suggestColumns).toBe(true);
  });

  it("suggests alias columns after select-list subqueries instead of tables", () => {
    const sql = `
      SELECT
        p.id,
        p.create_user_name 'creator',
        (SELECT t.\`code\` FROM sys_user t WHERE t.user_id = p.apply_user_id) 'creator_code',
        p.
      FROM sys_process p
      LIMIT 10
    `;
    const cursor = sql.indexOf("p.\n      FROM") + 2;
    const items = buildSqlCompletionItems(sql, cursor, {
      dialect: "mysql",
      tables: [
        { name: "act_evt_log", type: "table" },
        { name: "sys_process", type: "table" },
        { name: "sys_user", type: "table" },
      ],
      columnsByTable: new Map([
        [
          "sys_process",
          [
            { name: "id", table: "sys_process" },
            { name: "create_user_name", table: "sys_process" },
            { name: "apply_user_id", table: "sys_process" },
          ],
        ],
        ["sys_user", [{ name: "code", table: "sys_user" }]],
      ]),
    });

    const columnLabels = items.filter((item) => item.type === "column").map((item) => item.label);
    expect(columnLabels).toEqual(expect.arrayContaining(["id", "create_user_name", "apply_user_id"]));
    expect(items[0]?.type).toBe("column");
    expect(items.some((item) => item.type === "table")).toBe(false);
    expect(items.some((item) => item.type === "keyword")).toBe(false);
  });

  it("classifies unqualified WHERE field input as column context", () => {
    const sql = "SELECT * FROM A1User WHERE userc";
    const context = getSqlCompletionContext(sql, sql.length);

    expect(context.contextKind).toBe("column");
    expect(context.prefix).toBe("userc");
    expect(context.referencedTables).toEqual(expect.arrayContaining([expect.objectContaining({ name: "A1User" })]));
    expect(context.suggestColumns).toBe(true);
    expect(context.suggestRoutines).toBe(true);
  });

  it("classifies unqualified WHERE field input as column context for unquoted non-ASCII table/schema names", () => {
    const options = { databaseType: "mysql" } as const;

    const tableOnly = getSqlCompletionContext("SELECT * FROM 用户表 WHERE ", "SELECT * FROM 用户表 WHERE ".length, options);
    expect(tableOnly.contextKind).toBe("column");
    expect(tableOnly.referencedTables).toEqual(expect.arrayContaining([expect.objectContaining({ name: "用户表" })]));
    expect(tableOnly.suggestColumns).toBe(true);

    const withSchema = getSqlCompletionContext("SELECT * FROM 中文库.用户表 WHERE ", "SELECT * FROM 中文库.用户表 WHERE ".length, options);
    expect(withSchema.referencedTables).toEqual(expect.arrayContaining([expect.objectContaining({ schema: "中文库", name: "用户表" })]));
    expect(withSchema.suggestColumns).toBe(true);
  });

  it("does not treat a non-ASCII 'from' phrase inside a string literal as a referenced table", () => {
    const sql = "SELECT * FROM real_table WHERE note = 'from 测试表' AND ";
    const context = getSqlCompletionContext(sql, sql.length, { databaseType: "mysql" });
    expect(context.referencedTables).toEqual(expect.arrayContaining([expect.objectContaining({ name: "real_table" })]));
    expect(context.referencedTables.some((t) => t.name === "测试表")).toBe(false);
  });

  it("does not treat a non-ASCII 'from' phrase inside a line comment as a referenced table", () => {
    const sql = "SELECT *\nFROM real_table\n-- from 测试表\nWHERE ";
    const context = getSqlCompletionContext(sql, sql.length, { databaseType: "mysql" });
    expect(context.referencedTables).toEqual(expect.arrayContaining([expect.objectContaining({ name: "real_table" })]));
    expect(context.referencedTables.some((t) => t.name === "测试表")).toBe(false);
  });

  it("does not treat a 'from' phrase inside a block comment as a referenced table", () => {
    const sql = "SELECT *\nFROM real_table\n/* from ghost_table */\nWHERE ";
    const context = getSqlCompletionContext(sql, sql.length, { databaseType: "mysql" });
    expect(context.referencedTables).toEqual(expect.arrayContaining([expect.objectContaining({ name: "real_table" })]));
    expect(context.referencedTables.some((t) => t.name === "ghost_table")).toBe(false);
  });

  it("does not let a quote inside a line comment desync literal/comment masking", () => {
    const sql = "SELECT * FROM real_table -- it's a comment\nWHERE ";
    const context = getSqlCompletionContext(sql, sql.length, { databaseType: "mysql" });
    expect(context.referencedTables).toEqual(expect.arrayContaining([expect.objectContaining({ name: "real_table" })]));
  });

  it("does not let a comment-like sequence inside a string literal swallow real SQL", () => {
    const sql = "SELECT * FROM t1 WHERE note = '-- not a comment' AND ";
    const context = getSqlCompletionContext(sql, sql.length, { databaseType: "mysql" });
    expect(context.referencedTables).toEqual(expect.arrayContaining([expect.objectContaining({ name: "t1" })]));
  });

  it("does not match unquoted non-ASCII table names for ANSI-strict dialects", () => {
    const sql = "SELECT * FROM 用户表 WHERE ";
    const context = getSqlCompletionContext(sql, sql.length, { databaseType: "clickhouse" });
    expect(context.referencedTables.some((t) => t.name === "用户表")).toBe(false);
  });

  it("masks a double-quoted value right after an operator from being read as a table reference (mysql)", () => {
    const sql = 'SELECT * FROM real_table WHERE note = "from 测试表" AND ';
    const context = getSqlCompletionContext(sql, sql.length, { databaseType: "mysql" });
    expect(context.referencedTables).toEqual(expect.arrayContaining([expect.objectContaining({ name: "real_table" })]));
    expect(context.referencedTables.some((t) => t.name === "测试表")).toBe(false);
  });

  it("treats a double-quoted FROM target as a table reference regardless of MySQL's sql_mode (position-aware masking)", () => {
    // sql_mode (and therefore whether "..." is ANSI_QUOTES identifier quoting or a string literal)
    // isn't observable at parse time, so "..." right after FROM/JOIN/UPDATE/etc. is always treated
    // as a potential table identifier -- matching ANSI_QUOTES-enabled MySQL -- while "..." elsewhere
    // (function args, CASE branches, operator-adjacent values) is still masked as a value, matching
    // MySQL's actual default sql_mode. See maskSqlLiteralsAndComments's doc comment.
    const sql = 'SELECT * FROM "orders" WHERE ';
    const context = getSqlCompletionContext(sql, sql.length, { databaseType: "mysql" });
    expect(context.referencedTables).toEqual(expect.arrayContaining([expect.objectContaining({ name: "orders" })]));
  });

  it("keeps resolving a double-quoted FROM target as a table when a comment sits between FROM and it (mysql)", () => {
    const sql = 'SELECT * FROM /* c */ "orders" WHERE ';
    const context = getSqlCompletionContext(sql, sql.length, { databaseType: "mysql" });
    expect(context.referencedTables).toEqual(expect.arrayContaining([expect.objectContaining({ name: "orders" })]));
  });

  it("resolves a double-quoted, dotted schema-qualified table reference (mysql)", () => {
    const sql = 'SELECT * FROM "db"."orders" WHERE ';
    const context = getSqlCompletionContext(sql, sql.length, { databaseType: "mysql" });
    expect(context.referencedTables).toEqual(expect.arrayContaining([expect.objectContaining({ schema: "db", name: "orders" })]));
  });

  it("resolves both an unquoted and a double-quoted table across a JOIN, alongside a plain alias (mysql)", () => {
    const sql = 'SELECT * FROM a JOIN "orders" o ON a.id = o.id WHERE ';
    const context = getSqlCompletionContext(sql, sql.length, { databaseType: "mysql" });
    expect(context.referencedTables).toEqual(expect.arrayContaining([expect.objectContaining({ name: "a" }), expect.objectContaining({ name: "orders", alias: "o" })]));
  });

  it("resolves a double-quoted UPDATE target as a referenced table while still masking a double-quoted value in SET (mysql)", () => {
    const sql = 'UPDATE "orders" SET status = "x" WHERE ';
    const context = getSqlCompletionContext(sql, sql.length, { databaseType: "mysql" });
    expect(context.referencedTables).toEqual(expect.arrayContaining([expect.objectContaining({ name: "orders" })]));
    expect(context.referencedTables.some((t) => t.name === "x")).toBe(false);
  });

  it("does not desync the token stream on a backslash-escaped quote inside a double-quoted value (mysql)", () => {
    const sql = 'SELECT * FROM real_table WHERE note = "she said \\"hi\\"" ';
    const context = getSqlCompletionContext(sql, sql.length, { databaseType: "mysql" });
    expect(context.referencedTables).toEqual(expect.arrayContaining([expect.objectContaining({ name: "real_table" })]));
    expect(context.preferredKeywords).toEqual(expect.arrayContaining(["AND", "OR"]));
  });

  it("does not desync the token stream on a backslash-escaped quote inside a double-quoted value (hive)", () => {
    // Hive isn't in MYSQL_DASH_COMMENT_DIALECTS, so "..." here is (and was, before this fix) always
    // a quoted_identifier, masked only via the operator-adjacency fallback -- this test isn't about
    // that masking decision, it's about whether reading the "..." span itself (now always routed
    // through readQuotedString, see tokens.ts) still finds the true closing quote for a
    // mysqlBackslashEscape dialect outside MYSQL_DASH_COMMENT_DIALECTS. Before this fix, hive read
    // "..." with the doubled-quote-only reader regardless of mysqlBackslashEscape, so this span
    // would desync at the first backslash-escaped quote and corrupt everything parsed after it.
    const sql = 'SELECT * FROM real_table WHERE note = "she said \\"hi\\"" ';
    const context = getSqlCompletionContext(sql, sql.length, { databaseType: "hive" });
    expect(context.referencedTables).toEqual(expect.arrayContaining([expect.objectContaining({ name: "real_table" })]));
    expect(context.preferredKeywords).toEqual(expect.arrayContaining(["AND", "OR"]));
  });

  it("masks a double-quoted string used as a function argument from being read as a table reference (mysql)", () => {
    const sql = 'SELECT CONCAT("from ghost_table", name) FROM real_table WHERE ';
    const context = getSqlCompletionContext(sql, sql.length, { databaseType: "mysql" });
    expect(context.referencedTables).toEqual(expect.arrayContaining([expect.objectContaining({ name: "real_table" })]));
    expect(context.referencedTables.some((t) => t.name === "ghost_table")).toBe(false);
  });

  it("masks a double-quoted CASE branch value from being read as a table reference (mysql)", () => {
    const sql = 'SELECT CASE WHEN enabled THEN "from ghost_case" ELSE "ok" END FROM real_table WHERE ';
    const context = getSqlCompletionContext(sql, sql.length, { databaseType: "mysql" });
    expect(context.referencedTables).toEqual(expect.arrayContaining([expect.objectContaining({ name: "real_table" })]));
    expect(context.referencedTables.some((t) => t.name === "ghost_case")).toBe(false);
  });

  it("masks a double-quoted string nested inside a parenthesized expression from being read as a table reference (mysql)", () => {
    const sql = 'SELECT * FROM real_table WHERE (status = "open" AND note = CONCAT("from ghost_nested", 1)) AND ';
    const context = getSqlCompletionContext(sql, sql.length, { databaseType: "mysql" });
    expect(context.referencedTables).toEqual(expect.arrayContaining([expect.objectContaining({ name: "real_table" })]));
    expect(context.referencedTables.some((t) => t.name === "ghost_nested")).toBe(false);
  });

  it("masks a double-quoted value right after an operator from being read as a table reference (postgres)", () => {
    const sql = 'SELECT * FROM real_table WHERE note = "from 测试表" AND ';
    const context = getSqlCompletionContext(sql, sql.length, { databaseType: "postgres" });
    // Postgres treats "..." as a quoted identifier, not a string literal -- but a "..." span
    // right after "=" is unambiguously a value position regardless of dialect, so this is masked
    // the same way as mysql above, not left as a (harmless but avoidable) false-positive parse.
    expect(context.referencedTables).toEqual(expect.arrayContaining([expect.objectContaining({ name: "real_table" })]));
    expect(context.referencedTables.some((t) => t.name === "测试表")).toBe(false);
  });

  it("keeps a double-quoted table name intact for dialects where they aren't string literals (postgres)", () => {
    const sql = 'SELECT * FROM "orders" WHERE ';
    const context = getSqlCompletionContext(sql, sql.length, { databaseType: "postgres" });
    expect(context.referencedTables).toEqual(expect.arrayContaining([expect.objectContaining({ name: "orders", nameQuoted: true })]));
  });

  it("does not let a MySQL backslash-escaped quote desync the literal scan", () => {
    const sql = "SELECT * FROM t1 WHERE note = 'it\\'s a test' UNION SELECT * FROM real_table2 WHERE ";
    const context = getSqlCompletionContext(sql, sql.length, { databaseType: "mysql" });
    expect(context.referencedTables).toEqual(expect.arrayContaining([expect.objectContaining({ name: "t1" }), expect.objectContaining({ name: "real_table2" })]));
  });

  it("does not treat MySQL's unspaced '--' double-negation as a comment", () => {
    const sql = "SELECT * FROM t1 WHERE x = 1--1 FROM real_table3 WHERE ";
    const context = getSqlCompletionContext(sql, sql.length, { databaseType: "mysql" });
    expect(context.referencedTables).toEqual(expect.arrayContaining([expect.objectContaining({ name: "t1" }), expect.objectContaining({ name: "real_table3" })]));
  });

  it("still treats a bare '--' as a comment for non-MySQL dialects", () => {
    const sql = "SELECT * FROM t1 WHERE x = 1--1 FROM real_table3 WHERE ";
    const context = getSqlCompletionContext(sql, sql.length, { databaseType: "postgres" });
    expect(context.referencedTables.some((t) => t.name === "real_table3")).toBe(false);
  });

  it("does not let a doubled single-quote escape desync the literal scan", () => {
    const sql = "SELECT * FROM t1 WHERE note = 'it''s a test' AND ";
    const context = getSqlCompletionContext(sql, sql.length, { databaseType: "mysql" });
    expect(context.referencedTables).toEqual(expect.arrayContaining([expect.objectContaining({ name: "t1" })]));
  });

  it("does not let a backslash-escaped quote desync the literal scan for MySQL-family dialects outside the dash-comment allowlist (hive)", () => {
    const sql = "SELECT * FROM t1 WHERE note = 'it\\'s a test' UNION SELECT * FROM real_table2 WHERE ";
    const context = getSqlCompletionContext(sql, sql.length, { databaseType: "hive" });
    expect(context.referencedTables).toEqual(expect.arrayContaining([expect.objectContaining({ name: "t1" }), expect.objectContaining({ name: "real_table2" })]));
  });

  it("defaults to ASCII-only unquoted identifiers when databaseType is not yet known", () => {
    const sql = "SELECT * FROM 用户表 WHERE ";
    const context = getSqlCompletionContext(sql, sql.length, {});
    expect(context.referencedTables.some((t) => t.name === "用户表")).toBe(false);
  });

  it("does not let a special character inside a double-quoted qualified table name desync the scan (postgres)", () => {
    const sql = 'SELECT * FROM "mydb"."orders" WHERE ';
    const context = getSqlCompletionContext(sql, sql.length, { databaseType: "postgres" });
    expect(context.referencedTables).toEqual(expect.arrayContaining([expect.objectContaining({ schema: "mydb", name: "orders" })]));
  });

  it("does not let a special character inside a backtick-quoted qualified table name desync the scan (mysql)", () => {
    const sql = "SELECT * FROM `mydb`.`orders` WHERE ";
    const context = getSqlCompletionContext(sql, sql.length, { databaseType: "mysql" });
    expect(context.referencedTables).toEqual(expect.arrayContaining([expect.objectContaining({ schema: "mydb", name: "orders" })]));
  });

  it("auto-opens column completion after WHERE whitespace before LIMIT", () => {
    const sql = "SELECT *\nFROM t_0001 AS t0 WHERE \nLIMIT 100;";
    const cursor = "SELECT *\nFROM t_0001 AS t0 WHERE ".length;
    const context = getSqlCompletionContext(sql, cursor);

    expect(context.contextKind).toBe("column");
    expect(context.prefix).toBe("");
    expect(context.referencedTables).toEqual(expect.arrayContaining([expect.objectContaining({ name: "t_0001", alias: "t0" })]));
    expect(context.suggestColumns).toBe(true);
    expect(shouldAutoOpenSqlCompletion(sql, cursor)).toBe(true);
  });

  it("does not let a trailing comment's stray 'and'/'or' text suppress the AND/OR keyword suggestion", () => {
    const sql = "SELECT * FROM t WHERE id = 1 # mentions where and or\n";
    const context = getSqlCompletionContext(sql, sql.length, { databaseType: "mysql" });
    expect(context.preferredKeywords).toEqual(expect.arrayContaining(["AND", "OR"]));
  });

  it("does not let a special character inside a backtick-quoted table name break WHERE-clause keyword detection", () => {
    const sql = "SELECT * FROM `my's table` t WHERE t.id = 1 ";
    const context = getSqlCompletionContext(sql, sql.length, { databaseType: "mysql" });
    expect(context.preferredKeywords).toEqual(expect.arrayContaining(["AND", "OR"]));
  });

  it("does not let a trailing comment's stray 'where' text auto-open column completion with no active clause", () => {
    const sql = "SELECT * FROM t_0001 t0 -- mentions where\n";
    const cursor = sql.length;
    expect(shouldAutoOpenSqlCompletion(sql, cursor, { databaseType: "mysql" })).toBe(false);
  });

  it("classifies CALL routine contexts", () => {
    const sql = "CALL usp_";
    const context = getSqlCompletionContext(sql, sql.length);

    expect(context.contextKind).toBe("exec");
    expect(context.suggestRoutines).toBe(true);
    expect(context.exclusiveRoutineSuggestions).toBe(true);
  });

  it("classifies INSERT column-list contexts", () => {
    const sql = "INSERT INTO dbo.Users (";
    const context = getSqlCompletionContext(sql, sql.length);

    expect(context.contextKind).toBe("column");
    expect(context.insertSchema).toBe("dbo");
    expect(context.insertTable).toBe("Users");
    expect(context.exclusiveColumnSuggestions).toBe(true);
  });

  it("classifies UPDATE SET column contexts", () => {
    const sql = "UPDATE dbo.Users SET ";
    const context = getSqlCompletionContext(sql, sql.length);

    expect(context.contextKind).toBe("column");
    expect(context.updateTarget).toEqual({ schema: "dbo", table: "Users" });
    expect(context.suggestColumns).toBe(true);
  });

  it("extracts statement-local table aliases", () => {
    const sql = "SELECT * FROM dbo.Users u JOIN Orders AS o ON o.user_id = u.id WHERE u.";
    const context = getSqlCompletionContext(sql, sql.length);

    expect(context.referencedTables).toEqual(expect.arrayContaining([expect.objectContaining({ schema: "dbo", name: "Users", alias: "u" }), expect.objectContaining({ name: "Orders", alias: "o" })]));
  });

  it("preserves SQL Server database and omitted schema in legacy table references", () => {
    const sql = "SELECT * FROM BarDB..orders AS o WHERE o.";
    const context = getSqlCompletionContext(sql, sql.length, { databaseType: "sqlserver" });

    expect(context.referencedTables).toEqual([expect.objectContaining({ database: "BarDB", schema: "dbo", name: "orders", alias: "o" })]);
  });

  it("treats schema-qualified table prefixes in FROM as table completion input", () => {
    const sql = "SELECT * FROM dws_game_sdk_base.di";
    const context = getSqlCompletionContext(sql, sql.length);

    expect(context.qualifier).toBe("dws_game_sdk_base");
    expect(context.prefix).toBe("di");
    expect(context.suggestTables).toBe(true);
    expect(context.exclusiveTableSuggestions).toBe(true);
    expect(context.suggestColumns).toBe(true);
  });

  it("exposes CTEs as table-like referenced tables", () => {
    const sql = "WITH recent_orders(id, total) AS (SELECT id, total FROM orders) SELECT * FROM recent_orders ro WHERE ro.";
    const context = getSqlCompletionContext(sql, sql.length);

    expect(context.referencedTables).toEqual(expect.arrayContaining([expect.objectContaining({ name: "recent_orders", columns: ["id", "total"] }), expect.objectContaining({ name: "recent_orders", alias: "ro" })]));
  });

  it("scopes CTE bodies out of the outer query's referenced tables", () => {
    const sql = "WITH cte AS (SELECT id, name FROM orders) SELECT id, na FROM cte";
    const cursor = sql.indexOf(" FROM cte");
    const context = getSqlCompletionContext(sql, cursor);

    expect(context.referencedTables.map((table) => table.name)).toEqual(["cte"]);
  });

  it("suggests bare CTE column names instead of forcing a cte. qualifier (issue #7396)", () => {
    const sql = "WITH cte AS (SELECT id, name FROM orders) SELECT id, na FROM cte";
    const cursor = sql.indexOf(" FROM cte");
    const items = buildSqlCompletionItems(sql, cursor, {
      tables: [],
      columnsByTable: new Map([
        [
          "orders",
          [
            { name: "id", table: "orders", dataType: "int" },
            { name: "name", table: "orders", dataType: "text" },
          ],
        ],
        [
          "cte",
          [
            { name: "id", table: "cte" },
            { name: "name", table: "cte" },
          ],
        ],
      ]),
    });

    expect(items).toEqual(expect.arrayContaining([expect.objectContaining({ label: "name", type: "column" })]));
    expect(items.filter((item) => item.type === "column").map((item) => item.label)).not.toContain("orders.name");
  });

  it("offers the CTE name as a table candidate above fuzzy catalog matches (#8381)", () => {
    // The reported shape: no space between `as` and `(`, cursor still inside `tm`.
    const sql = "with tmp as(select id, c1, c2 from test.t)\nselect * from tm";
    // Same composition the editor provider uses: the semantic layer carries the
    // resolved CTE reference that the legacy context alone does not keep.
    const legacy = getSqlCompletionContext(sql, sql.length);
    const semanticContext = sqlCompletionContextFromSemantic(buildSqlSemanticModel(sql, sql.length), legacy);
    const items = buildSqlCompletionItemsFromContext(semanticContext, {
      tables: [{ name: "tb_market_price" }],
      columnsByTable: new Map(),
    });

    const cteIndex = items.findIndex((item) => item.label === "tmp");
    expect(cteIndex).toBeGreaterThanOrEqual(0);
    expect(items[cteIndex]).toMatchObject({ type: "table" });
    // The CTE is what the statement selects from, so it outranks a catalog table
    // that only matched by initials.
    expect(cteIndex).toBeLessThan(items.findIndex((item) => item.label === "tb_market_price"));
    // The alias snippet for the relation being typed must not be offered,
    // avoiding replacing the table name being completed (#13750).
    expect(items.some((item) => item.type === "snippet" && item.apply === "tm ")).toBe(false);
  });

  it("offers a star-projection CTE as a table candidate without a duplicate alias snippet", () => {
    // `SELECT *` inside the CTE body leaves its extracted columns empty; the
    // CTE itself must still complete as a table (#8381's most common shape).
    const sql = "with tmp as(select * from test.t)\nselect * from tm";
    const legacy = getSqlCompletionContext(sql, sql.length);
    const semanticContext = sqlCompletionContextFromSemantic(buildSqlSemanticModel(sql, sql.length), legacy);
    const items = buildSqlCompletionItemsFromContext(semanticContext, { tables: [], columnsByTable: new Map() });

    expect(items.some((item) => item.type === "table" && item.label === "tmp")).toBe(true);
    expect(items.some((item) => item.type === "snippet" && item.apply === "tmp ")).toBe(false);
  });

  it("does not offer the half-typed relation as a table candidate", () => {
    const sql = "with tmp as(select id from test.t)\nselect * from tm";
    const legacy = getSqlCompletionContext(sql, sql.length);
    const semanticContext = sqlCompletionContextFromSemantic(buildSqlSemanticModel(sql, sql.length), legacy);
    const items = buildSqlCompletionItemsFromContext(semanticContext, { tables: [], columnsByTable: new Map() });

    expect(items.filter((item) => item.type === "table").map((item) => item.label)).not.toContain("tm");
  });

  it("keeps the CTE body's own tables while completing inside that body", () => {
    const sql = "WITH cte AS (SELECT id, na FROM orders) SELECT * FROM cte";
    const cursor = sql.indexOf(" FROM orders");
    const context = getSqlCompletionContext(sql, cursor);

    expect(context.referencedTables.map((table) => table.name)).toEqual(expect.arrayContaining(["orders", "cte"]));
  });

  it("keeps the underlying table when a CTE body projects an unresolved star", () => {
    const sql = "WITH cte AS (SELECT * FROM orders) SELECT na FROM cte";
    const cursor = sql.indexOf(" FROM cte");
    const context = getSqlCompletionContext(sql, cursor);

    expect(context.referencedTables.map((table) => table.name)).toEqual(expect.arrayContaining(["orders", "cte"]));
  });

  it("expands nested CTE columns when selecting alias.* from preceding CTE (#11479)", () => {
    const sql = `with test_a as (
    select '1' as 字段
)
, test_b as (
    select ta.*
        , '2' as 追加字段
    from test_a ta
)
select tb.
from test_b tb`;
    const cursor = sql.indexOf("select tb.") + "select tb.".length;
    const context = getSqlCompletionContext(sql, cursor);
    const tbRef = context.referencedTables.find((t) => t.alias === "tb" || t.name === "test_b");
    expect(tbRef).toBeDefined();
    expect(tbRef?.columns).toEqual(["字段", "追加字段"]);

    // ta from inside test_b must not leak as a referenced table in outer query
    expect(context.referencedTables.some((t) => t.alias === "ta")).toBe(false);

    const items = buildSqlCompletionItems(sql, cursor, {
      tables: [],
      columnsByTable: new Map(),
    });
    const labels = items.map((item) => item.label);
    expect(labels).toContain("字段");
    expect(labels).toContain("追加字段");
  });

  it("expands multi-level chained CTEs with bare and qualified wildcards (#11479)", () => {
    const sql = `with c1 as (
    select 1 as id, 'alice' as name
)
, c2 as (
    select *
        , true as is_active
    from c1
)
, c3 as (
    select c2.*
        , 99 as score
    from c2
)
select c3.
from c3`;
    const cursor = sql.indexOf("select c3.") + "select c3.".length;
    const context = getSqlCompletionContext(sql, cursor);
    const c3Ref = context.referencedTables.find((t) => t.name === "c3");
    expect(c3Ref?.columns).toEqual(["id", "name", "is_active", "score"]);

    const items = buildSqlCompletionItems(sql, cursor, {
      tables: [],
      columnsByTable: new Map(),
    });
    const labels = items.map((item) => item.label);
    expect(labels).toContain("id");
    expect(labels).toContain("name");
    expect(labels).toContain("is_active");
    expect(labels).toContain("score");
  });

  it("extracts subquery aliases and projected columns", () => {
    const sql = "SELECT * FROM (SELECT id, name AS user_name FROM users) sq WHERE sq.";
    const context = getSqlCompletionContext(sql, sql.length);

    expect(context.referencedTables).toEqual(expect.arrayContaining([expect.objectContaining({ name: "sq", alias: "sq", columns: ["id", "user_name"] })]));
  });

  it("suggests columns for cross-database qualified table references", () => {
    const sql = "SELECT * FROM current_orders WHERE reporting.orders.";
    const context = getSqlCompletionContext(sql, sql.length);
    const items = buildSqlCompletionItems(sql, sql.length, {
      tables: [],
      columnsByTable: new Map([
        [
          "reporting.orders",
          [
            { name: "id", table: "orders", schema: "reporting", dataType: "int" },
            { name: "status", table: "orders", schema: "reporting", dataType: "varchar" },
          ],
        ],
        ["archive.orders", [{ name: "archived_at", table: "orders", schema: "archive", dataType: "datetime" }]],
      ]),
    });

    expect(context.qualifier).toBe("reporting.orders");
    expect(context.qualifierParts).toEqual(["reporting", "orders"]);
    expect(context.suggestColumns).toBe(true);
    expect(items).toEqual(expect.arrayContaining([expect.objectContaining({ label: "id", type: "column" }), expect.objectContaining({ label: "status", type: "column" })]));
    expect(items.some((item) => item.label === "archived_at")).toBe(false);
  });
});

describe("sqlCompletion scoped metadata ranking", () => {
  it("shows a table metadata detail in the completion item", () => {
    const sql = "SELECT * FROM orders";
    const items = buildSqlCompletionItems(sql, sql.length, {
      tables: [{ name: "orders", type: "table", detail: "→ Customer orders" }],
      columnsByTable: new Map(),
    }).filter((item) => item.type === "table");

    expect(items).toEqual([expect.objectContaining({ label: "orders", detail: "→ Customer orders" })]);
  });

  it("hides the redundant table type while preserving view and schema details", () => {
    const simpleItems = buildSqlCompletionItems("SELECT * FROM orders", "SELECT * FROM orders".length, {
      tables: [{ name: "orders", schema: "public", type: "table" }],
      columnsByTable: new Map(),
    }).filter((item) => item.type === "table");

    expect(simpleItems.find((item) => item.label === "orders")?.detail).toBeUndefined();

    const viewItems = buildSqlCompletionItems("SELECT * FROM order_view", "SELECT * FROM order_view".length, {
      tables: [{ name: "order_view", schema: "public", type: "view" }],
      columnsByTable: new Map(),
    }).filter((item) => item.type === "table");

    expect(viewItems.find((item) => item.label === "order_view")?.detail).toBe("view");

    const duplicateItems = buildSqlCompletionItems("SELECT * FROM orders", "SELECT * FROM orders".length, {
      tables: [
        { name: "orders", schema: "archive", type: "table" },
        { name: "orders", schema: "sales", type: "table" },
      ],
      columnsByTable: new Map(),
    }).filter((item) => item.type === "table");

    expect(duplicateItems.map((item) => item.detail).sort()).toEqual(["archive.orders", "sales.orders"]);

    const annotatedDuplicateItems = buildSqlCompletionItems("SELECT * FROM orders", "SELECT * FROM orders".length, {
      tables: [
        { name: "orders", schema: "archive", type: "table", detail: "→ Archived orders" },
        { name: "orders", schema: "sales", type: "table", detail: "→ Current orders" },
      ],
      columnsByTable: new Map(),
    }).filter((item) => item.type === "table");

    expect(annotatedDuplicateItems.map((item) => item.detail).sort()).toEqual(["archive.orders  → Archived orders", "sales.orders  → Current orders"]);
  });

  it("ranks exact and prefix table matches ahead of contains/fuzzy matches", () => {
    const sql = "SELECT * FROM Temp";
    const items = buildSqlCompletionItems(sql, sql.length, {
      dialect: "sqlserver",
      tables: [
        { name: "ArchiveTempTable", schema: "dbo", type: "table" },
        { name: "TempAudit", schema: "dbo", type: "table" },
        { name: "Temp", schema: "dbo", type: "table" },
        { name: "Template", schema: "dbo", type: "table" },
      ],
      columnsByTable: new Map(),
    }).filter((item) => item.type === "table");

    expect(items.map((item) => item.label).slice(0, 3)).toEqual(["Temp", "Template", "TempAudit"]);
    expect(items.some((item) => item.label === "ArchiveTempTable")).toBe(true);
  });

  it("keeps large table catalogs bounded", () => {
    const tables = Array.from({ length: 500 }, (_, index) => ({ name: `TempTable_${String(index).padStart(3, "0")}`, schema: "dbo", type: "table" as const }));
    const sql = "SELECT * FROM Temp";
    const items = buildSqlCompletionItems(sql, sql.length, { dialect: "sqlserver", tables, columnsByTable: new Map() }).filter((item) => item.type === "table");

    expect(items.length).toBeLessThanOrEqual(200);
    expect(items[0]?.label).toBe("TempTable_000");
  });

  it("ranks real Oracle tables before built-in table functions in FROM contexts", () => {
    const sql = "SELECT * FROM ";
    const items = buildSqlCompletionItems(sql, sql.length, {
      databaseType: "oracle",
      tables: [{ name: "ORDERS_10K", schema: "DBX_TEST", type: "table" }],
      columnsByTable: new Map(),
    });

    expect(items.findIndex((item) => item.label === "ORDERS_10K")).toBeLessThan(items.findIndex((item) => item.label === "TABLE"));
  });

  it("uses on-collision qualification by default for same-name PostgreSQL tables", () => {
    const sql = "SELECT * FROM shared";
    const items = buildSqlCompletionItems(sql, sql.length, {
      databaseType: "postgres",
      dialect: "postgres",
      tables: [
        { name: "shared", schema: "public", type: "table" },
        { name: "shared", schema: "reporting", type: "table" },
      ],
      columnsByTable: new Map(),
    }).filter((item) => item.type === "table");

    expect(items).toHaveLength(2);
    expect(items.map((item) => item.apply).sort()).toEqual(["public.shared", "reporting.shared"]);
  });

  it("never qualifies table names while keeping colliding candidates distinct", () => {
    const sql = "SELECT * FROM shared";
    const items = buildSqlCompletionItems(sql, sql.length, {
      databaseType: "postgres",
      dialect: "postgres",
      tables: [
        { name: "shared", schema: "public", type: "table", applyName: "public.shared" },
        { name: "shared", schema: "reporting", type: "view", applyName: "reporting.shared" },
      ],
      columnsByTable: new Map(),
      tableCompletionSchemaQualification: "never",
    }).filter((item) => item.type === "table");

    expect(items).toHaveLength(2);
    expect(items.map((item) => item.apply)).toEqual(["shared", "shared"]);
    expect(items.map((item) => item.detail).sort()).toEqual(["public.shared", "reporting.shared"]);
  });

  it("always qualifies tables and views when schema metadata is available", () => {
    const sql = "SELECT * FROM ";
    const items = buildSqlCompletionItems(sql, sql.length, {
      databaseType: "postgres",
      dialect: "postgres",
      tables: [
        { name: "Order Details", schema: "Sales Data", type: "view" },
        { name: "scratch", type: "table" },
      ],
      columnsByTable: new Map(),
      tableCompletionSchemaQualification: "always",
    }).filter((item) => item.type === "table");

    expect(items.find((item) => item.label === "Order Details")?.apply).toBe('"Sales Data"."Order Details"');
    expect(items.find((item) => item.label === "scratch")?.apply).toBe("scratch");
  });

  it.each([
    ["never", "customers"],
    ["always", "sales.customers"],
  ] as const)("applies the %s policy to foreign-key table suggestions", (tableCompletionSchemaQualification, expectedApply) => {
    const sql = "SELECT * FROM sales.orders o JOIN cus";
    const items = buildSqlCompletionItems(sql, sql.length, {
      databaseType: "postgres",
      dialect: "postgres",
      tables: [
        { name: "orders", schema: "sales", type: "table" },
        { name: "customers", schema: "sales", type: "table" },
      ],
      columnsByTable: new Map(),
      foreignKeysByTable: new Map([["sales.orders", [{ name: "orders_customer_id_fkey", column: "customer_id", ref_table: "customers", ref_schema: "sales", ref_column: "id" }]]]),
      tableCompletionSchemaQualification,
    });
    const customers = items.filter((item) => item.type === "table" && item.label === "customers");

    expect(customers).toHaveLength(1);
    expect(customers[0]?.apply).toBe(expectedApply);
    expect(customers[0]?.detail).toContain("related by");
  });

  it("does not repeat an already typed quoted schema or SQL Server database/schema qualifier", () => {
    const postgresSql = 'SELECT * FROM "Sales Data".Ord';
    const postgresItems = buildSqlCompletionItems(postgresSql, postgresSql.length, {
      databaseType: "postgres",
      dialect: "postgres",
      tables: [{ name: "Order Details", schema: "Sales Data", type: "table" }],
      columnsByTable: new Map(),
      tableCompletionSchemaQualification: "always",
    });
    const sqlServerSql = "SELECT * FROM Reporting.dbo.Ord";
    const sqlServerItems = buildSqlCompletionItems(sqlServerSql, sqlServerSql.length, {
      databaseType: "sqlserver",
      dialect: "sqlserver",
      tables: [{ name: "Order Details", database: "Reporting", schema: "dbo", type: "table" }],
      columnsByTable: new Map(),
      tableCompletionSchemaQualification: "always",
    });

    expect(postgresItems.find((item) => item.label === "Order Details")?.apply).toBe('"Order Details"');
    expect(sqlServerItems.find((item) => item.label === "Order Details")?.apply).toBe("[Order Details]");
  });

  it("qualifies same-name tables for generic metadata providers", () => {
    const sql = "SELECT * FROM orders";
    const items = buildSqlCompletionItems(sql, sql.length, {
      tables: [
        { name: "orders", schema: "archive", type: "table" },
        { name: "orders", schema: "sales", type: "table" },
      ],
      columnsByTable: new Map(),
    }).filter((item) => item.type === "table");

    expect(items.map((item) => item.apply).sort()).toEqual(["archive.orders", "sales.orders"]);
  });

  it("preserves Oracle current-schema and SQL Server unique-table insertion", () => {
    const oracleItems = buildSqlCompletionItems("SELECT * FROM ORDERS", "SELECT * FROM ORDERS".length, {
      databaseType: "oracle",
      tables: [
        { name: "ORDERS", schema: "APP", type: "table" },
        { name: "ORDERS", schema: "REPORTING", type: "table" },
      ],
      columnsByTable: new Map(),
      currentSchema: "APP",
    }).filter((item) => item.type === "table");
    const sqlServerItems = buildSqlCompletionItems("SELECT * FROM Orders", "SELECT * FROM Orders".length, {
      databaseType: "sqlserver",
      dialect: "sqlserver",
      tables: [{ name: "Orders", schema: "dbo", type: "table" }],
      columnsByTable: new Map(),
    }).filter((item) => item.type === "table");

    expect(oracleItems.map((item) => item.apply).sort()).toEqual(["ORDERS", "REPORTING.ORDERS"]);
    expect(sqlServerItems).toEqual([expect.objectContaining({ label: "Orders", apply: "Orders" })]);
  });
});

describe("shouldAllowSqlCompletionTrigger", () => {
  const typingFacts = (overrides: Partial<SqlCompletionTriggerFacts> = {}): SqlCompletionTriggerFacts => ({
    origin: "typing",
    hasIdentifierPrefix: false,
    qualifierTriggered: false,
    useDatabasePrefix: null,
    ...overrides,
  });

  const explicitFacts = (overrides: Partial<SqlCompletionTriggerFacts> = {}): SqlCompletionTriggerFacts => ({
    origin: "explicit",
    hasIdentifierPrefix: false,
    qualifierTriggered: false,
    useDatabasePrefix: null,
    ...overrides,
  });

  describe("explicit", () => {
    it("allows explicit completion in any mode", () => {
      expect(shouldAllowSqlCompletionTrigger("manual", explicitFacts())).toBe(true);
      expect(shouldAllowSqlCompletionTrigger("require-prefix", explicitFacts())).toBe(true);
      expect(shouldAllowSqlCompletionTrigger("positional", explicitFacts())).toBe(true);
    });
  });

  describe("manual", () => {
    it("rejects all typing completions", () => {
      expect(shouldAllowSqlCompletionTrigger("manual", typingFacts())).toBe(false);
      expect(shouldAllowSqlCompletionTrigger("manual", typingFacts({ hasIdentifierPrefix: true }))).toBe(false);
      expect(shouldAllowSqlCompletionTrigger("manual", typingFacts({ qualifierTriggered: true }))).toBe(false);
      expect(shouldAllowSqlCompletionTrigger("manual", typingFacts({ useDatabasePrefix: "m" }))).toBe(false);
      expect(shouldAllowSqlCompletionTrigger("manual", typingFacts({ positionalEligible: true }))).toBe(false);
    });
  });

  describe("require-prefix", () => {
    it("allows when identifier prefix is non-empty", () => {
      expect(shouldAllowSqlCompletionTrigger("require-prefix", typingFacts({ hasIdentifierPrefix: true }))).toBe(true);
    });

    it("allows when qualifier is triggered (dot with qualifier)", () => {
      expect(shouldAllowSqlCompletionTrigger("require-prefix", typingFacts({ qualifierTriggered: true }))).toBe(true);
    });

    it("allows when useDatabasePrefix is non-empty", () => {
      expect(shouldAllowSqlCompletionTrigger("require-prefix", typingFacts({ useDatabasePrefix: "m" }))).toBe(true);
      expect(shouldAllowSqlCompletionTrigger("require-prefix", typingFacts({ useDatabasePrefix: "Bar" }))).toBe(true);
    });

    it("rejects empty prefix, no qualifier, no useDatabasePrefix", () => {
      expect(shouldAllowSqlCompletionTrigger("require-prefix", typingFacts())).toBe(false);
    });

    it("rejects empty useDatabasePrefix (USE<space> without prefix)", () => {
      expect(shouldAllowSqlCompletionTrigger("require-prefix", typingFacts({ useDatabasePrefix: "" }))).toBe(false);
    });

    it("does not use positionalEligible", () => {
      // Even if positionalEligible is true, require-prefix ignores it.
      expect(shouldAllowSqlCompletionTrigger("require-prefix", typingFacts({ positionalEligible: true }))).toBe(false);
    });
  });

  describe("positional", () => {
    it("allows when positionalEligible is true", () => {
      expect(shouldAllowSqlCompletionTrigger("positional", typingFacts({ positionalEligible: true }))).toBe(true);
    });

    it("allows when useDatabasePrefix is set (even empty)", () => {
      expect(shouldAllowSqlCompletionTrigger("positional", typingFacts({ useDatabasePrefix: "" }))).toBe(true);
      expect(shouldAllowSqlCompletionTrigger("positional", typingFacts({ useDatabasePrefix: "m" }))).toBe(true);
    });

    it("rejects when positionalEligible is false and no useDatabasePrefix", () => {
      expect(shouldAllowSqlCompletionTrigger("positional", typingFacts({ positionalEligible: false }))).toBe(false);
    });

    it("rejects when positionalEligible is undefined and no useDatabasePrefix", () => {
      expect(shouldAllowSqlCompletionTrigger("positional", typingFacts())).toBe(false);
    });
  });
});

describe("originForTypedSqlCompletionStart", () => {
  it("starts a new automatic session as typing", () => {
    expect(originForTypedSqlCompletionStart(null)).toBe("typing");
  });

  it("preserves the origin of an active completion session", () => {
    expect(originForTypedSqlCompletionStart("typing")).toBe("typing");
    expect(originForTypedSqlCompletionStart("explicit")).toBe("explicit");
  });
});

describe("originForSqlCompletionProvider", () => {
  it("classifies an unmarked provider call from CodeMirror", () => {
    expect(originForSqlCompletionProvider(null, false)).toBe("typing");
    expect(originForSqlCompletionProvider(null, true)).toBe("explicit");
  });

  it("preserves the active session independently of the current provider flag", () => {
    expect(originForSqlCompletionProvider("typing", true)).toBe("typing");
    expect(originForSqlCompletionProvider("explicit", false)).toBe("explicit");
  });
});

describe("select alias visibility", () => {
  it("prioritizes SELECT aliases inside HAVING for MySQL", () => {
    const sql = "SELECT COUNT(*) AS cnt, g FROM orders GROUP BY g HAVING cnt > ";
    const context = getSqlCompletionContext(sql, sql.length, { databaseType: "mysql", dialect: "mysql" });

    expect(context.prioritizeSelectAliases).toBe(true);
    expect(context.selectAliases).toContain("cnt");
  });

  it("prioritizes SELECT aliases inside ORDER BY even with later clauses", () => {
    const sql = "SELECT SUM(price) AS total FROM orders ORDER BY total LIMIT 10";
    const cursor = sql.indexOf("total", sql.indexOf("ORDER BY")) + "total".length;
    const context = getSqlCompletionContext(sql, cursor, { databaseType: "mysql", dialect: "mysql" });

    expect(context.prioritizeSelectAliases).toBe(true);
    expect(context.selectAliases).toContain("total");
  });

  it("does not prioritize SELECT aliases inside WHERE", () => {
    const sql = "SELECT id AS uid FROM orders WHERE uid > ";
    const context = getSqlCompletionContext(sql, sql.length, { databaseType: "mysql", dialect: "mysql" });

    expect(context.prioritizeSelectAliases).toBe(false);
    expect(context.selectAliases).toEqual([]);
  });

  it("offers HAVING alias completion items", () => {
    const sql = "SELECT COUNT(*) AS cnt, g FROM orders GROUP BY g HAVING cn";
    const items = buildSqlCompletionItems(sql, sql.length, {
      dialect: "mysql",
      tables: [{ name: "orders", type: "table" }],
      columnsByTable: new Map([["orders", [{ name: "g" } as never]]]),
    });

    expect(items.some((item) => item.label === "cnt")).toBe(true);
  });

  it("does not prioritize HAVING aliases for dialects that reject them", () => {
    const sql = "SELECT COUNT(*) AS cnt, g FROM orders GROUP BY g HAVING cnt > ";
    for (const databaseType of ["postgres", "sqlserver", "db2", "oracle"] as const) {
      const context = getSqlCompletionContext(sql, sql.length, { databaseType });

      expect(context.prioritizeSelectAliases, databaseType).toBe(false);
      expect(context.selectAliases, databaseType).toEqual([]);
    }
  });

  it("keeps HAVING alias priority for SQLite-family and DuckDB dialects", () => {
    const sql = "SELECT COUNT(*) AS cnt, g FROM orders GROUP BY g HAVING cnt > ";
    for (const databaseType of ["duckdb", "sqlite", "doris", "bigquery"] as const) {
      const context = getSqlCompletionContext(sql, sql.length, { databaseType });

      expect(context.prioritizeSelectAliases, databaseType).toBe(true);
      expect(context.selectAliases, databaseType).toContain("cnt");
    }
  });

  it("does not anchor alias visibility on identifiers containing HAVING", () => {
    const sql = "SELECT id AS uid FROM orders WHERE having_count > 1";
    const context = getSqlCompletionContext(sql, sql.length, { databaseType: "mysql" });

    expect(context.prioritizeSelectAliases).toBe(false);
    expect(context.selectAliases).toEqual([]);
  });

  it("keeps alias visibility in ORDER BY after an identifier containing HAVING", () => {
    const sql = "SELECT id AS uid FROM orders WHERE having_count > 1 ORDER BY uid";
    const cursor = sql.length;
    const context = getSqlCompletionContext(sql, cursor, { databaseType: "mysql" });

    expect(context.prioritizeSelectAliases).toBe(true);
    expect(context.selectAliases).toContain("uid");
  });

  it("keeps HAVING alias priority for unlisted dialects (deny-list polarity)", () => {
    const sql = "SELECT COUNT(*) AS cnt, g FROM orders GROUP BY g HAVING cnt > ";
    for (const databaseType of ["spark", "databricks", "snowflake", "hive"] as const) {
      const context = getSqlCompletionContext(sql, sql.length, { databaseType });

      expect(context.prioritizeSelectAliases, databaseType).toBe(true);
      expect(context.selectAliases, databaseType).toContain("cnt");
    }
    // No database type at all keeps the permissive legacy behavior.
    const untyped = getSqlCompletionContext(sql, sql.length, {});
    expect(untyped.prioritizeSelectAliases).toBe(true);
    expect(untyped.selectAliases).toContain("cnt");
  });
});

describe("line block statement boundary", () => {
  it.each(["-- explanatory comment", "/* explanatory comment */", "/*\n explanatory comment\n*/"])("keeps column sources after %s", (comment) => {
    const sql = `select na\n${comment}\nfrom users`;
    const cursor = "select na".length;
    const context = getSqlCompletionContext(sql, cursor, { databaseType: "iris" });

    expect(context.referencedTables.map((table) => table.name)).toEqual(["users"]);
    const items = buildSqlCompletionItems(sql, cursor, {
      databaseType: "iris",
      tables: [{ name: "users", type: "table" }],
      columnsByTable: new Map([["users", [{ name: "name", table: "users", key: "name" } as never]]]),
    });
    expect(items.some((item) => item.label === "name")).toBe(true);
  });

  it("still ends the block at a blank line before a new statement", () => {
    // #10196 refined the blank-line rule: a blank line only ends the block when
    // the next non-empty line opens a new top-level statement. A blank line
    // before FROM/WHERE continues the same statement.
    const sql = "select na from t1\n\nselect nb from t2";
    const context = getSqlCompletionContext(sql, "select na from t1".length, { databaseType: "iris" });
    expect(context.referencedTables.map((table) => table.name)).toEqual(["t1"]);
  });

  it("keeps FROM tables when the select list is separated from FROM by a blank line (#10196)", () => {
    const sql = "select na\n\nfrom users";
    const context = getSqlCompletionContext(sql, "select na".length, { databaseType: "iris" });
    expect(context.referencedTables.map((table) => table.name)).toEqual(["users"]);
    expect(context.suggestColumns).toBe(true);
  });

  it("stops the active block at a top-level statement line without a semicolon", () => {
    // t8y2/dbx#9370: two semicolon-free SELECT lines — completion at the first
    // statement's WHERE must not pull the second statement's table in.
    const sql = "select * from CT_Nation where\nselect * from CT_CityArea";
    const cursor = sql.indexOf("where") + "where".length;
    const context = getSqlCompletionContext(sql, cursor, { databaseType: "iris" });

    expect(context.referencedTables.map((table) => table.name)).toEqual(["CT_Nation"]);
  });

  it("keeps column completion unqualified once the second statement is excluded", () => {
    const sql = "select * from CT_Nation where c\nselect * from CT_CityArea";
    const cursor = sql.indexOf("where") + "where c".length;
    const items = buildSqlCompletionItems(sql, cursor, {
      databaseType: "iris",
      tables: [
        { name: "CT_Nation", type: "table" },
        { name: "CT_CityArea", type: "table" },
      ],
      columnsByTable: new Map([
        ["CT_Nation", [{ name: "CTNAT_Code", table: "CT_Nation", key: "k1" } as never]],
        ["CT_CityArea", [{ name: "CITAREA_Code", table: "CT_CityArea", key: "k2" } as never]],
      ]),
    });

    expect(items.some((item) => item.label === "CT_CityArea.CITAREA_Code")).toBe(false);
    expect(items.some((item) => item.label === "CTNAT_Code")).toBe(true);
  });

  it("does not end the block at a SELECT line inside parentheses", () => {
    const sql = "select *\nfrom (\n  select id from users\n) x\nwhere |";
    const cursor = sql.length;
    const context = getSqlCompletionContext(sql, cursor, { databaseType: "mysql" });

    expect(context.referencedTables.map((table) => table.name)).toEqual(expect.arrayContaining(["users"]));
  });

  it("keeps WITH clause in scope when cursor is in the following top-level main query", () => {
    const sql = "with cte as (\n  select id, name from users\n)\nselect c.\nfrom cte c\n\nselect * from other_table";
    const cursor = sql.indexOf("select c.") + "select c.".length;
    const context = getSqlCompletionContext(sql, cursor, { databaseType: "mysql" });

    expect(context.referencedTables.some((t) => t.name === "cte" || t.alias === "c")).toBe(true);
    expect(context.referencedTables.some((t) => t.name === "other_table")).toBe(false);
  });

  it("stops line block before following statement when completing in a separate query after WITH", () => {
    const sql = "with cte as (\n  select id, name from users\n)\nselect * from cte c\n\nselect * from other_table ot where ot.";
    const cursor = sql.length;
    const context = getSqlCompletionContext(sql, cursor, { databaseType: "mysql" });

    expect(context.referencedTables.some((t) => t.name === "other_table" || t.alias === "ot")).toBe(true);
    expect(context.referencedTables.some((t) => t.name === "cte")).toBe(false);
  });
});

describe("select-list function argument completion", () => {
  const options = {
    databaseType: "mysql" as const,
    tables: [{ name: "daily_statistic", type: "table" as const }],
    columnsByTable: new Map([["daily_statistic", ["CNKI_CITATIONS", "CNKI_DOWNLOADS", "stat_date"].map((name) => ({ name, table: "daily_statistic" }))]]),
  };

  const aggregateTemplates = ["SELECT\n  SUM(CNKI_CITATIONS) AS cnkiCitations,\n  SUM(CNKI_DOW|) AS cnkiDownloads\nFROM\n  daily_statistic", "SELECT stat_date, CNKI_CITATIONS, COUNT(CNKI_DOW|) FROM daily_statistic", "SELECT stat_date, CNKI_CITATIONS, ROUND(AVG(CNKI_DOW|), 2) FROM daily_statistic"];

  it.each(aggregateTemplates)("suggests columns inside an aggregate after earlier projections: %s", (template) => {
    const cursor = template.indexOf("|");
    const sql = template.replace("|", "");
    const items = buildSqlCompletionItems(sql, cursor, options);

    expect(items.filter((item) => item.type === "column").map((item) => item.label)).toContain("CNKI_DOWNLOADS");
  });

  it("does not offer select-list-only items inside an aggregate", () => {
    const sql = "SELECT stat_date, CNKI_CITATIONS, SUM() FROM daily_statistic";
    const cursor = sql.indexOf("SUM(") + "SUM(".length;
    const items = buildSqlCompletionItems(sql, cursor, options);

    expect(items.filter((item) => item.type === "column").map((item) => item.label)).toContain("CNKI_DOWNLOADS");
    expect(items.some((item) => item.type === "snippet" && item.label === "daily_statistic.*")).toBe(false);
    expect(items.filter((item) => item.type === "column").every((item) => item.batchSelectionMode === undefined)).toBe(true);
    expect(getSqlCompletionContext(sql, cursor, options).selectListColumnContext).toBe(false);
  });
});

describe("smart unpaired SQL keyword completion before existing keywords (#11527)", () => {
  it("detects following keywords across whitespace and comments", () => {
    expect(getSqlCompletionContext("SELECT * FROM users CR JOIN orders", "SELECT * FROM users CR".length).followingKeyword).toBe("JOIN");
    expect(getSqlCompletionContext("SELECT * FROM users CR /* comment */ JOIN orders", "SELECT * FROM users CR".length).followingKeyword).toBe("JOIN");
    expect(getSqlCompletionContext("SELECT * FROM users CR\n  JOIN orders", "SELECT * FROM users CR".length).followingKeyword).toBe("JOIN");
    expect(getSqlCompletionContext("SELECT * FROM users ORD BY id", "SELECT * FROM users ORD".length).followingKeyword).toBe("BY");
    expect(getSqlCompletionContext("SELECT * FROM users CR; SELECT * FROM orders", "SELECT * FROM users CR".length).followingKeyword).toBeUndefined();
    expect(getSqlCompletionContext("SELECT * FROM users CR", "SELECT * FROM users CR".length).followingKeyword).toBeUndefined();
  });

  it("suggests standalone CROSS instead of compound CROSS JOIN when JOIN already follows", () => {
    const sql = "SELECT * FROM users CR JOIN orders";
    const cursor = "SELECT * FROM users CR".length;
    const items = buildSqlCompletionItems(sql, cursor, {
      databaseType: "mysql",
      tables: [
        { name: "users", type: "table" },
        { name: "orders", type: "table" },
      ],
      columnsByTable: new Map(),
    });

    const keywords = items.filter((item) => item.type === "keyword");
    const labels = keywords.map((item) => item.label);

    // Standalone CROSS should be available and ranked at the top
    expect(labels[0]).toBe("CROSS");
    // Compound CROSS JOIN should NOT be suggested because JOIN already follows
    expect(labels).not.toContain("CROSS JOIN");

    // The CROSS item should not enforce a trailing space that would duplicate the space before JOIN
    const crossItem = keywords.find((item) => item.label === "CROSS");
    expect(crossItem?.apply).toBeUndefined();
  });

  it("suggests both CROSS JOIN and standalone CROSS when JOIN does not follow", () => {
    const sql = "SELECT * FROM users CR";
    const cursor = sql.length;
    const items = buildSqlCompletionItems(sql, cursor, {
      databaseType: "mysql",
      tables: [{ name: "users", type: "table" }],
      columnsByTable: new Map(),
    });

    const keywords = items.filter((item) => item.type === "keyword");
    const labels = keywords.map((item) => item.label);

    expect(labels).toContain("CROSS JOIN");
    expect(labels).toContain("CROSS");

    const crossJoinItem = keywords.find((item) => item.label === "CROSS JOIN");
    expect(crossJoinItem?.apply).toBe("CROSS JOIN ");
  });

  it("suggests ORDER without duplicating BY when BY already follows", () => {
    const sql = "SELECT * FROM users ORD BY id";
    const cursor = "SELECT * FROM users ORD".length;
    const items = buildSqlCompletionItems(sql, cursor, {
      databaseType: "mysql",
      tables: [{ name: "users", type: "table" }],
      columnsByTable: new Map(),
    });

    const keywords = items.filter((item) => item.type === "keyword");
    const labels = keywords.map((item) => item.label);

    expect(labels[0]).toBe("ORDER");
    expect(labels).not.toContain("ORDER BY");
  });

  it("suggests GROUP without duplicating BY when BY already follows", () => {
    const sql = "SELECT * FROM users GR BY id";
    const cursor = "SELECT * FROM users GR".length;
    const items = buildSqlCompletionItems(sql, cursor, {
      databaseType: "mysql",
      tables: [{ name: "users", type: "table" }],
      columnsByTable: new Map(),
    });

    const keywords = items.filter((item) => item.type === "keyword");
    const labels = keywords.map((item) => item.label);

    expect(labels[0]).toBe("GROUP");
    expect(labels).not.toContain("GROUP BY");
  });

  it("suggests both ORDER and ORDER BY when BY does not follow", () => {
    const sql = "SELECT * FROM users ORD";
    const cursor = sql.length;
    const items = buildSqlCompletionItems(sql, cursor, {
      databaseType: "mysql",
      tables: [{ name: "users", type: "table" }],
      columnsByTable: new Map(),
    });

    const labels = items.filter((item) => item.type === "keyword").map((item) => item.label);
    expect(labels).toContain("ORDER");
    expect(labels).toContain("ORDER BY");
  });

  it("supports other paired clauses like APPLY and KEY without duplication", () => {
    const applySql = "SELECT * FROM users a OUT APPLY sys.tables";
    const applyCursor = "SELECT * FROM users a OUT".length;
    const applyItems = buildSqlCompletionItems(applySql, applyCursor, {
      databaseType: "sqlserver",
      tables: [{ name: "users", type: "table" }],
      columnsByTable: new Map(),
    });
    const applyLabels = applyItems.filter((item) => item.type === "keyword").map((item) => item.label);
    expect(applyLabels[0]).toBe("OUTER");
    expect(applyLabels).not.toContain("OUTER APPLY");

    const keySql = "CREATE TABLE t (id INT UNIQ KEY)";
    const keyCursor = "CREATE TABLE t (id INT UNIQ".length;
    const keyItems = buildSqlCompletionItems(keySql, keyCursor, {
      databaseType: "mysql",
      tables: [],
      columnsByTable: new Map(),
    });
    const keyLabels = keyItems.filter((item) => item.type === "keyword").map((item) => item.label);
    expect(keyLabels[0]).toBe("UNIQUE");
    expect(keyLabels).not.toContain("UNIQUE KEY");
  });
});

describe("quoted identifier completion filtering (issue #12008)", () => {
  // CodeMirror filters candidate labels against the raw document span from the
  // completion result's `from` to the cursor. A quoted-identifier completion
  // replaces the opening quote too, so that span starts with `` ` ``/`"`/`[`
  // and no identifier label can ever match it, leaving the menu empty.
  it.each([
    ["`test_", "test_", "test_"],
    ['"test_', "test_", "test_"],
    ["[test_", "test_", "test_"],
    ["`test_`", "test_", "test_"],
    ['"test_"', "test_", "test_"],
    ["[test_]", "test_", "test_"],
  ])("maps the quoted span %s with prefix %s back to %s", (query, prefix, expected) => {
    expect(quotedCompletionFilterPrefix(query, prefix)).toBe(expected);
  });

  it.each([
    ["test_", "test_"],
    ["`other_", "test_"],
    ["`test", "test_"],
    ["tes", "test_"],
    ["", "test_"],
  ])("leaves %s (prefix %s) to CodeMirror's own matcher", (query, prefix) => {
    expect(quotedCompletionFilterPrefix(query, prefix)).toBeUndefined();
  });
});

describe("Spanner keyword completion (issue #13129)", () => {
  it.each([
    "CREATE CHANGE STREAM",
    "CREATE SEARCH INDEX",
    "CREATE VECTOR INDEX",
    "CREATE PROPERTY GRAPH",
    "INTERLEAVE IN PARENT",
    "STORING",
    "PENDING_COMMIT_TIMESTAMP",
    "GENERATE_UUID",
    // Ported from the overlapping #13138 (row-deletion policies, DML exits,
    // scalar types, sequence options) so one list carries the full dialect.
    "ROW DELETION POLICY",
    "THEN RETURN",
    "INSERT OR UPDATE",
    "SQL SECURITY INVOKER",
    "BIT_REVERSED_POSITIVE",
    "INT64",
  ])("offers %s", (label) => {
    const sql = "CREATE ";
    const items = buildSqlCompletionItems(sql, sql.length, {
      databaseType: "spanner",
      tables: [],
      columnsByTable: new Map(),
    });

    expect(items.some((item) => item.label === label)).toBe(true);
  });

  it("keeps BigQuery-only syntax out of the Spanner keyword list", () => {
    const sql = "SELECT * ";
    const items = buildSqlCompletionItems(sql, sql.length, {
      databaseType: "spanner",
      tables: [],
      columnsByTable: new Map(),
    });

    // TABLESAMPLE and FOR SYSTEM_TIME AS OF are BigQuery syntax; Spanner reads
    // staleness through the API, not SQL.
    expect(items.some((item) => item.label === "TABLESAMPLE")).toBe(false);
    expect(items.some((item) => item.label === "FOR SYSTEM_TIME AS OF")).toBe(false);
  });

  it("keeps Spanner-only DDL out of the PostgreSQL keyword list", () => {
    const sql = "CREATE ";
    const items = buildSqlCompletionItems(sql, sql.length, {
      databaseType: "postgres",
      tables: [],
      columnsByTable: new Map(),
    });

    expect(items.some((item) => item.label === "CREATE CHANGE STREAM")).toBe(false);
  });
});