MetonaSqlark/tests/v080-output-ordinals.test.ts

/**
 * v0.8.0 回归套件 —— B-5 输出列序号与分隔标识符
 * ============================================================================
 * 两个相关缺陷：
 *
 * ① **输出列序号不被支持**（SQL 标准特性）
 *    `ORDER BY 1` / `GROUP BY 2` 此前直接
 *    `PARSE_ERROR: Expected identifier, got "1"`（实测）。
 *    这在手写 SQL 与 UNION 里很常用 —— 各分支输出列名可能不同，只能按序号引用。
 *    序号的含义依赖 SELECT 列表，因此 parser 只保留数字文本，
 *    由 executor 在拿到 SELECT 列表后解析。
 *
 * ② **分隔标识符（`"1"`）在投影期被当成常量**
 *    `CREATE TABLE q ("1" STRING ...)` 后 `SELECT "1" FROM q` 返回
 *    `{"1": 1}`（**字面量 1**），而 `SELECT *` 返回正确的 `{"1": "z"}`
 *    —— 同一列两种结论（实测）。根因是 parser 把 `"1"` 的引号丢掉，
 *    投影期的裸数字常量子句就把它吃了。
 *
 * 修复涉及的关键设计：**引号只在"解析 → 执行"的边界脱去**，
 * 因为 parser 必须保留引号才能区分"名为 1 的列"（`"1"`）与"常量 1"（`1`）；
 * 而校验/取值必须用真实列名。此前"校验用裸名、投影用带引号的名"的两套规则
 * 正是漂移的来源（实测出现 `1` 与 `"1"` 两个键并存）。
 *
 * 序号优先级（与 SQL 标准的名称解析顺序一致）：
 *   真实列名 > 输出列别名 > 位置序号。
 *   `SELECT "1", other FROM q ORDER BY 1` → 按**名为 1 的列**排序。
 */
import { describe, it, expect, beforeEach } from '@jest/globals';
import { MetonaSqlark } from '../src/core';
import { rows as rowsOf } from './helpers/assertions';
import type { DatabaseConfig } from '../src/constants';

const ENGINES: Array<[string, DatabaseConfig['mode'], Partial<DatabaseConfig>]> = [
  ['memory', 'memory', {}],
  ['disk', 'disk', {}],
  ['hybrid', 'hybrid', {}],
  ['aria', 'aria', { diskEngine: 'memory' }],
];

describe('[v0.8.0] B-5 输出列序号（四引擎）', () => {
  describe.each(ENGINES)('%s 引擎', (label, mode, extra) => {
    let db: MetonaSqlark;
    let seq = 0;

    beforeEach(async () => {
      seq += 1;
      db = await MetonaSqlark.create({
        name: `b5-${label}-${seq}-${Math.random().toString(36).slice(2)}`,
        mode,
        ...extra,
      });
      await db.defineTable('t', {
        id: { type: 'string', primaryKey: true },
        g: { type: 'string' },
        n: { type: 'number' },
      });
      await db.query("INSERT INTO t VALUES ('1','a',30),('2','a',10),('3','b',20)");
    });

    afterEach(async () => {
      await db.close();
    });

    it('ORDER BY 1 / 2 按输出列位置排序', async () => {
      const byFirst = rowsOf<Record<string, unknown>>(
        await db.query('SELECT id, n FROM t ORDER BY 1'),
      );
      expect(byFirst.map((r) => r.id)).toEqual(['1', '2', '3']);
      const bySecond = rowsOf<Record<string, unknown>>(
        await db.query('SELECT id, n FROM t ORDER BY 2'),
      );
      expect(bySecond.map((r) => r.n)).toEqual([10, 20, 30]);
    });

    it('ORDER BY 序号支持 DESC 与多键', async () => {
      const rows = rowsOf<Record<string, unknown>>(
        await db.query('SELECT n, id FROM t ORDER BY 2 DESC, 1'),
      );
      expect(rows.map((r) => r.id)).toEqual(['3', '2', '1']);
    });

    it('ORDER BY 序号可与 LIMIT/OFFSET 组合', async () => {
      const rows = rowsOf<Record<string, unknown>>(
        await db.query('SELECT id FROM t ORDER BY 1 DESC LIMIT 2 OFFSET 1'),
      );
      expect(rows.map((r) => r.id)).toEqual(['2', '1']);
    });

    it('GROUP BY 序号按输出列分组', async () => {
      const rows = rowsOf<Record<string, unknown>>(
        await db.query('SELECT g, COUNT(*) AS c FROM t GROUP BY 1 ORDER BY 1'),
      );
      expect(rows).toEqual([{ g: 'a', c: 2 }, { g: 'b', c: 1 }]);
    });

    it('GROUP BY 序号指向聚合表达式 → QUERY_ERROR', async () => {
      // 按聚合值分组语义上不成立（SQL 标准同样禁止）
      await expect(db.query('SELECT g, COUNT(*) AS c FROM t GROUP BY 2')).rejects.toMatchObject({
        code: 'QUERY_ERROR',
      });
    });

    it('序号越界 → QUERY_ERROR（而不是"未知列"）', async () => {
      await expect(db.query('SELECT id FROM t ORDER BY 3')).rejects.toMatchObject({
        code: 'QUERY_ERROR',
      });
      await expect(db.query('SELECT id FROM t GROUP BY 3')).rejects.toMatchObject({
        code: 'QUERY_ERROR',
      });
    });

    it('ORDER BY 0 / 非整数序号 → PARSE_ERROR', async () => {
      await expect(db.query('SELECT id FROM t ORDER BY 0')).rejects.toMatchObject({
        code: 'PARSE_ERROR',
      });
      await expect(db.query('SELECT id FROM t ORDER BY 1.5')).rejects.toMatchObject({
        code: 'PARSE_ERROR',
      });
    });

    it('列名与别名引用不受影响（回归护栏）', async () => {
      expect(
        rowsOf<Record<string, unknown>>(await db.query('SELECT n AS num FROM t ORDER BY num DESC'))
          .map((r) => r.num),
      ).toEqual([30, 20, 10]);
      expect(
        rowsOf<Record<string, unknown>>(await db.query('SELECT n AS num FROM t ORDER BY n DESC'))
          .map((r) => r.num),
      ).toEqual([30, 20, 10]);
      expect(
        rowsOf<Record<string, unknown>>(
          await db.query('SELECT g AS grp, COUNT(*) AS c FROM t GROUP BY grp ORDER BY grp'),
        ),
      ).toEqual([{ g: 'a', c: 2 }, { g: 'b', c: 1 }]);
    });

    it('UNION 后的 ORDER BY 序号作用于复合结果', async () => {
      const rows = rowsOf<Record<string, unknown>>(
        await db.query('SELECT id FROM t UNION SELECT id FROM t ORDER BY 1 DESC LIMIT 2'),
      );
      expect(rows.map((r) => r.id)).toEqual(['3', '2']);
    });

    it('序号引用带别名的 CASE 输出列', async () => {
      const rows = rowsOf<Record<string, unknown>>(
        await db.query(
          "SELECT id, CASE WHEN n > 15 THEN 'big' ELSE 'small' END AS band FROM t ORDER BY 1",
        ),
      );
      expect(rows.map((r) => r.id)).toEqual(['1', '2', '3']);
    });
  });
});

describe('[v0.8.0] B-5 分隔标识符（四引擎）', () => {
  describe.each(ENGINES)('%s 引擎', (label, mode, extra) => {
    let db: MetonaSqlark;
    let seq = 100;

    beforeEach(async () => {
      seq += 1;
      db = await MetonaSqlark.create({
        name: `b5q-${label}-${seq}-${Math.random().toString(36).slice(2)}`,
        mode,
        ...extra,
      });
      await db.query('CREATE TABLE q ("1" STRING PRIMARY KEY, other NUMBER)');
      await db.query(`INSERT INTO q ("1", other) VALUES ('z', 1), ('y', 2), ('x', 3)`);
    });

    afterEach(async () => {
      await db.close();
    });

    it('SELECT "1" 返回列值而非常量（修复前返回字面量 1）', async () => {
      const rows = rowsOf<Record<string, unknown>>(await db.query('SELECT "1", other FROM q'));
      expect(rows).toHaveLength(3);
      // 值与 `SELECT *` 一致
      const star = rowsOf<Record<string, unknown>>(await db.query('SELECT * FROM q'));
      expect(rows.map((r) => r['1']).sort()).toEqual(star.map((r) => r['1']).sort());
      expect(rows.map((r) => r['1']).sort()).toEqual(['x', 'y', 'z']);
    });

    it('SELECT "1" 的输出键与 SELECT * 一致（不出现重复键）', async () => {
      const rows = rowsOf<Record<string, unknown>>(await db.query('SELECT "1", other FROM q'));
      expect(Object.keys(rows[0]).sort()).toEqual(['1', 'other']);
    });

    it('WHERE 引用分隔标识符', async () => {
      const rows = rowsOf<Record<string, unknown>>(
        await db.query(`SELECT other FROM q WHERE "1" = 'y'`),
      );
      expect(rows).toEqual([{ other: 2 }]);
    });

    it('ORDER BY 优先解析为真实列名（序号简写让位于显式名字）', async () => {
      // 列名就是 "1" → `ORDER BY 1` 按该列排序（升序 x,y,z）
      const rows = rowsOf<Record<string, unknown>>(await db.query('SELECT "1", other FROM q ORDER BY 1'));
      expect(rows.map((r) => r['1'])).toEqual(['x', 'y', 'z']);
    });

    it('GROUP BY 分隔标识符且输出键唯一', async () => {
      const rows = rowsOf<Record<string, unknown>>(
        await db.query('SELECT "1", COUNT(*) AS c FROM q GROUP BY 1 ORDER BY 1'),
      );
      expect(rows).toEqual([
        { '1': 'x', c: 1 },
        { '1': 'y', c: 1 },
        { '1': 'z', c: 1 },
      ]);
    });

    it('表里无同名列时 ORDER BY 1 仍是序号', async () => {
      // 换成没有数字名列表，序号才无歧义：`ORDER BY 1 DESC` 按 other 降序
      const rows = rowsOf<Record<string, unknown>>(await db.query('SELECT other FROM q ORDER BY 1 DESC'));
      expect(rows.map((r) => r.other)).toEqual([3, 2, 1]);
      // 对照：写裸常量 1 时它才是常量列（每行都是 1），排序不改变结果
      const literal = rowsOf<Record<string, unknown>>(await db.query('SELECT 1 FROM q ORDER BY 1'));
      expect(literal.map((r) => r['1'])).toEqual([1, 1, 1]);
    });
  });
});

// ---------------------------------------------------------------------------
//  列引用取值的分支覆盖（共享原语 column-value 的两个关键分支）
// ---------------------------------------------------------------------------

describe('[v0.8.0] B-5 列引用取值原语', () => {
  it('JOIN 行的 `别名.列` 键与裸列引用都能取到', async () => {
    const db = await MetonaSqlark.create({ name: 'b5-joinref', mode: 'memory' });
    await db.defineTable('l', { id: { type: 'string', primaryKey: true }, n: { type: 'number' } });
    await db.defineTable('r', { id: { type: 'string', primaryKey: true }, l_id: { type: 'string' } });
    await db.query("INSERT INTO l VALUES ('l1', 5)");
    await db.query("INSERT INTO r VALUES ('r1', 'l1')");
    // JOIN 行键形如 `l.id`：`ORDER BY l.n` 走"精确命中"，`ORDER BY n` 走"唯一后缀"
    const qualified = rowsOf<Record<string, unknown>>(
      await db.query('SELECT l.id, l.n FROM l JOIN r ON l.id = r.l_id ORDER BY l.n'),
    );
    expect(qualified).toHaveLength(1);
    const bare = rowsOf<Record<string, unknown>>(
      await db.query('SELECT l.id, l.n FROM l JOIN r ON l.id = r.l_id ORDER BY n'),
    );
    expect(bare).toHaveLength(1);
    await db.close();
  });

  it('JOIN 里两表同名列的裸引用 → 歧义报错（不静默取第一个）', async () => {
    const db = await MetonaSqlark.create({ name: 'b5-ambiguous', mode: 'memory' });
    await db.defineTable('l', { id: { type: 'string', primaryKey: true }, tag: { type: 'string' } });
    await db.defineTable('r', { id: { type: 'string', primaryKey: true }, tag: { type: 'string' } });
    await db.query("INSERT INTO l VALUES ('l1', 'a')");
    await db.query("INSERT INTO r VALUES ('r1', 'a')");
    // 两表都有 tag → 裸 `ORDER BY tag` 无法判定归属
    await expect(
      db.query('SELECT l.id FROM l JOIN r ON l.tag = r.tag ORDER BY tag'),
    ).rejects.toMatchObject({ code: 'COLUMN_NOT_FOUND' });
    // 限定后正常
    const rows = rowsOf<Record<string, unknown>>(
      await db.query('SELECT l.id FROM l JOIN r ON l.tag = r.tag ORDER BY l.tag'),
    );
    expect(rows).toHaveLength(1);
    await db.close();
  });
});

// ---------------------------------------------------------------------------
//  CASE 解析缓存上限（防止缓存无界增长的分支）
// ---------------------------------------------------------------------------

describe('[v0.8.0] B-5 CASE 解析缓存上限', () => {
  it('超过上限后清空重建，解析结果仍正确', async () => {
    const db = await MetonaSqlark.create({ name: 'b5-cache', mode: 'memory' });
    await db.defineTable('t', { id: { type: 'string', primaryKey: true }, n: { type: 'number' } });
    await db.query("INSERT INTO t VALUES ('1', 5)");
    // 构造 600 个互不相同的 CASE 表达式（超过 512 的缓存上限），
    // 逐条执行以触发"清空重建"分支；结果必须始终正确。
    for (let i = 0; i < 600; i++) {
      const rows = rowsOf<Record<string, unknown>>(
        await db.query(`SELECT CASE WHEN n > ${i} THEN 'gt' ELSE 'le' END AS r FROM t`),
      );
      expect(rows[0].r).toBe(i < 5 ? 'gt' : 'le');
    }
    await db.close();
  });
});

// ---------------------------------------------------------------------------
//  CASE 条件解析失败（PARSE_ERROR 分支）
// ---------------------------------------------------------------------------

describe('[v0.8.0] B-5 CASE 条件语法错误', () => {
  it('条件无法解析 → PARSE_ERROR 且消息带上条件原文', async () => {
    const db = await MetonaSqlark.create({ name: 'b5-badcond', mode: 'memory' });
    await db.defineTable('t', { id: { type: 'string', primaryKey: true }, n: { type: 'number' } });
    await db.query("INSERT INTO t VALUES ('1', 5)");
    // `n >` 是残缺条件 → parseWhereCondition 抛错 → 包成 PARSE_ERROR
    await expect(
      db.query("SELECT CASE WHEN n > THEN 'a' ELSE 'b' END AS r FROM t"),
    ).rejects.toMatchObject({ code: 'PARSE_ERROR' });
    await db.close();
  });
});