TsDB2/DB/driver/case_insensitive_test.go

package vrtxsql

// 列名大小写不敏感回归测试:
//
// 驱动的 findField 用 strings.EqualFold 做大小写不敏感的存在性校验, 但构造
// 引擎条件/投影/排序时必须改用引擎规范名 string(fi.FieldName) —— 引擎侧
// (CmpCondition.Match / NullCondition / findFieldIndex / 排序键解析) 一律按
// 精确字节匹配列名。用户写 "Title" 而引擎列是 "title" 时, 若把 SQL 原文列名
// 直接传进引擎 API, WHERE 会静默恒假 (UPDATE/DELETE 作用 0 行), ORDER BY /
// 聚合会取不到列。本文件覆盖所有出口: WHERE (=/IN/LIKE/IS NULL)、UPDATE SET、
// DELETE、ORDER BY、SELECT 投影、聚合, 以及 JOIN 的 ON/WHERE/ORDER BY。

import (
	"database/sql"
	"strings"
	"testing"
)

// caseFixture 建小写列名 (name/age/email) 的 people 表并灌 4 行数据。
func caseFixture(t *testing.T, db *sql.DB) {
	t.Helper()
	exec := func(q string, args ...interface{}) {
		t.Helper()
		if _, err := db.Exec(q, args...); err != nil {
			t.Fatalf("%s: %v", q, err)
		}
	}
	exec(`CREATE TABLE people (
		id BIGINT PRIMARY KEY,
		name VARCHAR(32) NOT NULL,
		age INT,
		email VARCHAR(64)
	)`)
	exec(`INSERT INTO people (id, name, age, email) VALUES (?, ?, ?, ?), (?, ?, ?, ?), (?, ?, ?, ?), (?, ?, ?, ?)`,
		1, "alice", 30, "a@x.com",
		2, "bob", 25, nil,
		3, "carl", 40, nil,
		4, "dora", 20, "d@x.com")
}

// TestCaseInsensitiveWhereDML WHERE/UPDATE/DELETE 大小写混写命中。
func TestCaseInsensitiveWhereDML(t *testing.T) {
	db := openTestDB(t)
	caseFixture(t, db)

	// SELECT ... WHERE Name = ? (大写列名, 引擎列小写)
	var name string
	if err := db.QueryRow(`SELECT name FROM people WHERE Name = ?`, "alice").Scan(&name); err != nil {
		t.Fatalf("WHERE Name: %v", err)
	}
	if name != "alice" {
		t.Fatalf("name=%q, 想要 alice", name)
	}

	// 不等 / 范围 / IN / NOT IN 各来一遍
	var cnt int
	for q, want := range map[string]int{
		`SELECT COUNT(*) FROM people WHERE AGE > 25`:   2,
		`SELECT COUNT(*) FROM people WHERE Age <= 25`:  2,
		`SELECT COUNT(*) FROM people WHERE NAME IN ('alice', 'bob')`:    2,
		`SELECT COUNT(*) FROM people WHERE Name NOT IN ('alice', 'bob')`: 2,
	} {
		if err := db.QueryRow(q).Scan(&cnt); err != nil || cnt != want {
			t.Fatalf("%s: cnt=%d err=%v, 想要 %d", q, cnt, err, want)
		}
	}

	// LIKE 大小写混写
	if err := db.QueryRow(`SELECT COUNT(*) FROM people WHERE Name LIKE 'a%'`).Scan(&cnt); err != nil || cnt != 1 {
		t.Fatalf("LIKE: cnt=%d err=%v, 想要 1", cnt, err)
	}

	// UPDATE t SET Name=? WHERE NAME=? —— SET 与 WHERE 都混写大小写
	res, err := db.Exec(`UPDATE people SET Name = ? WHERE NAME = ?`, "alice2", "alice")
	if err != nil {
		t.Fatalf("UPDATE: %v", err)
	}
	if n, _ := res.RowsAffected(); n != 1 {
		t.Fatalf("UPDATE RowsAffected=%d, 想要 1", n)
	}
	if err := db.QueryRow(`SELECT name FROM people WHERE id = 1`).Scan(&name); err != nil {
		t.Fatalf("回读 id=1: %v", err)
	}
	if name != "alice2" {
		t.Fatalf("UPDATE 后 name=%q, 想要 alice2", name)
	}

	// DELETE WHERE AGE >= ? 生效
	res, err = db.Exec(`DELETE FROM people WHERE AGE >= ?`, 40)
	if err != nil {
		t.Fatalf("DELETE: %v", err)
	}
	if n, _ := res.RowsAffected(); n != 1 {
		t.Fatalf("DELETE RowsAffected=%d, 想要 1 (carl, age=40)", n)
	}
	if err := db.QueryRow(`SELECT COUNT(*) FROM people`).Scan(&cnt); err != nil || cnt != 3 {
		t.Fatalf("DELETE 后 cnt=%d err=%v, 想要 3", cnt, err)
	}
}

// TestCaseInsensitiveOrderBy ORDER BY 大小写混用正常排序。
func TestCaseInsensitiveOrderBy(t *testing.T) {
	db := openTestDB(t)
	caseFixture(t, db)

	var names []string
	rows, err := db.Query(`SELECT name FROM people ORDER BY Name`)
	if err != nil {
		t.Fatalf("ORDER BY Name: %v", err)
	}
	for rows.Next() {
		var s string
		if err := rows.Scan(&s); err != nil {
			t.Fatalf("scan: %v", err)
		}
		names = append(names, s)
	}
	rows.Close()
	if err := rows.Err(); err != nil {
		t.Fatal(err)
	}
	// name 列按字典序: alice bob carl dora
	if strings.Join(names, ",") != "alice,bob,carl,dora" {
		t.Fatalf("ORDER BY Name = %v", names)
	}

	// ORDER BY AGE DESC: 40 30 25 20 → carl alice bob dora
	names = names[:0]
	rows, err = db.Query(`SELECT name FROM people ORDER BY AGE DESC`)
	if err != nil {
		t.Fatalf("ORDER BY AGE DESC: %v", err)
	}
	for rows.Next() {
		var s string
		if err := rows.Scan(&s); err != nil {
			t.Fatalf("scan: %v", err)
		}
		names = append(names, s)
	}
	rows.Close()
	if err := rows.Err(); err != nil {
		t.Fatal(err)
	}
	if strings.Join(names, ",") != "carl,alice,bob,dora" {
		t.Fatalf("ORDER BY AGE DESC = %v", names)
	}

	// 多键混用: ORDER BY AGE DESC, Name
	names = names[:0]
	rows, err = db.Query(`SELECT name FROM people ORDER BY Age DESC, NAME`)
	if err != nil {
		t.Fatalf("多键 ORDER BY: %v", err)
	}
	for rows.Next() {
		var s string
		if err := rows.Scan(&s); err != nil {
			t.Fatalf("scan: %v", err)
		}
		names = append(names, s)
	}
	rows.Close()
	if err := rows.Err(); err != nil {
		t.Fatal(err)
	}
	if strings.Join(names, ",") != "carl,alice,bob,dora" {
		t.Fatalf("多键 ORDER BY = %v", names)
	}
}

// TestCaseInsensitiveProjection SELECT 投影混写大小写出数, 表头为引擎规范名。
func TestCaseInsensitiveProjection(t *testing.T) {
	db := openTestDB(t)
	caseFixture(t, db)

	rows, err := db.Query(`SELECT Name, AGE FROM people WHERE Id = ?`, 1)
	if err != nil {
		t.Fatalf("投影混写: %v", err)
	}
	defer rows.Close()
	cols, err := rows.Columns()
	if err != nil {
		t.Fatal(err)
	}
	if strings.Join(cols, ",") != "name,age" {
		t.Fatalf("表头=%v, 想要 [name age] (引擎规范名)", cols)
	}
	if !rows.Next() {
		t.Fatal("应有一行")
	}
	var name string
	var age int
	if err := rows.Scan(&name, &age); err != nil {
		t.Fatalf("scan: %v", err)
	}
	if name != "alice" || age != 30 {
		t.Fatalf("行=(%q,%d), 想要 (alice,30)", name, age)
	}
}

// TestCaseInsensitiveIsNull IS NULL 大小写混写按 NULL 位图命中。
func TestCaseInsensitiveIsNull(t *testing.T) {
	db := openTestDB(t)
	caseFixture(t, db)

	var cnt int
	// email 列实际小写, 查询写 Email; bob/carl 的 email 为 NULL
	if err := db.QueryRow(`SELECT COUNT(*) FROM people WHERE Email IS NULL`).Scan(&cnt); err != nil || cnt != 2 {
		t.Fatalf("Email IS NULL: cnt=%d err=%v, 想要 2", cnt, err)
	}
	if err := db.QueryRow(`SELECT COUNT(*) FROM people WHERE EMAIL IS NOT NULL`).Scan(&cnt); err != nil || cnt != 2 {
		t.Fatalf("EMAIL IS NOT NULL: cnt=%d err=%v, 想要 2", cnt, err)
	}

	// IS NULL 参与 UPDATE/DELETE 谓词 (GORM 软删除同款路径, 必须真正作用到行)
	res, err := db.Exec(`UPDATE people SET email = ? WHERE Email IS NULL`, "filled@x.com")
	if err != nil {
		t.Fatalf("UPDATE IS NULL: %v", err)
	}
	if n, _ := res.RowsAffected(); n != 2 {
		t.Fatalf("UPDATE IS NULL RowsAffected=%d, 想要 2", n)
	}
	res, err = db.Exec(`DELETE FROM people WHERE EMAIL IS NOT NULL`)
	if err != nil {
		t.Fatalf("DELETE IS NOT NULL: %v", err)
	}
	if n, _ := res.RowsAffected(); n != 4 {
		t.Fatalf("DELETE IS NOT NULL RowsAffected=%d, 想要 4", n)
	}
}

// TestCaseInsensitiveAggregate 聚合目标列大小写混写。
func TestCaseInsensitiveAggregate(t *testing.T) {
	db := openTestDB(t)
	caseFixture(t, db)

	var sum float64
	if err := db.QueryRow(`SELECT SUM(AGE) FROM people`).Scan(&sum); err != nil {
		t.Fatalf("SUM(AGE): %v", err)
	}
	if sum != 115 {
		t.Fatalf("SUM(AGE)=%v, 想要 115", sum)
	}
	var mn, mx int
	if err := db.QueryRow(`SELECT MIN(Age) FROM people`).Scan(&mn); err != nil {
		t.Fatalf("MIN(Age): %v", err)
	}
	if err := db.QueryRow(`SELECT MAX(aGe) FROM people`).Scan(&mx); err != nil {
		t.Fatalf("MAX(aGe): %v", err)
	}
	if mn != 20 || mx != 40 {
		t.Fatalf("MIN=%d MAX=%d, 想要 20/40", mn, mx)
	}
	var avg float64
	if err := db.QueryRow(`SELECT AVG(Age) FROM people WHERE Name LIKE 'a%'`).Scan(&avg); err != nil {
		t.Fatalf("AVG: %v", err)
	}
	if avg != 30 {
		t.Fatalf("AVG=%v, 想要 30 (alice)", avg)
	}
}

// TestCaseInsensitiveJoin JOIN 的 ON/WHERE/ORDER BY 大小写混用一轮。
func TestCaseInsensitiveJoin(t *testing.T) {
	db := openTestDB(t)
	joinFixture(t, db)

	// ON 两侧限定列混写大小写
	got := joinRows(t, db, `SELECT u.name, o.title, o.amount FROM users U JOIN orders O ON U.ID = O.USER_ID ORDER BY O.ID ASC`)
	want := []joinHit{{"alice", "book", 99.5}, {"alice", "pen", 20}, {"bob", "cup", 7.5}}
	if len(got) != len(want) {
		t.Fatalf("JOIN 行数=%d, 想要 %d (%+v)", len(got), len(want), got)
	}
	for i := range want {
		if got[i] != want[i] {
			t.Fatalf("行 %d = %+v, 想要 %+v", i, got[i], want[i])
		}
	}

	// WHERE 下推混写大小写 (title 限定名 + 非限定名 age)
	got = joinRows(t, db, `SELECT u.name, o.title, o.amount FROM users u JOIN orders o ON u.id = o.user_id WHERE O.Title = 'book' AND AGE > 25`)
	want = []joinHit{{"alice", "book", 99.5}}
	if len(got) != len(want) || got[0] != want[0] {
		t.Fatalf("WHERE 下推 = %+v, 想要 %+v", got, want)
	}

	// WHERE 残余谓词 (跨表列比较) 混写大小写
	got = joinRows(t, db, `SELECT u.name, o.title, o.amount FROM users u JOIN orders o ON u.id = o.user_id WHERE U.Age > O.Amount ORDER BY o.amount ASC`)
	want = []joinHit{{"bob", "cup", 7.5}, {"alice", "pen", 20}}
	if len(got) != len(want) {
		t.Fatalf("残余谓词 = %+v, 想要 %+v", got, want)
	}
	for i := range want {
		if got[i] != want[i] {
			t.Fatalf("行 %d = %+v, 想要 %+v", i, got[i], want[i])
		}
	}

	// ORDER BY 非限定名混写大小写 (age 只存在于 users)
	got = joinRows(t, db, `SELECT u.name, o.title, o.amount FROM users u JOIN orders o ON u.id = o.user_id ORDER BY Age DESC, o.id ASC`)
	want = []joinHit{{"alice", "book", 99.5}, {"alice", "pen", 20}, {"bob", "cup", 7.5}}
	if len(got) != len(want) {
		t.Fatalf("ORDER BY Age = %+v, 想要 %+v", got, want)
	}
	for i := range want {
		if got[i] != want[i] {
			t.Fatalf("行 %d = %+v, 想要 %+v", i, got[i], want[i])
		}
	}
}

// TestCaseInsensitiveUnknownColumn 错误语义保持: 不存在的列 (任意大小写) 仍报
// 42S22 列不存在, 而不是静默恒假。
func TestCaseInsensitiveUnknownColumn(t *testing.T) {
	db := openTestDB(t)
	caseFixture(t, db)

	checks := []string{
		`SELECT COUNT(*) FROM people WHERE NoSuchCol = 1`,
		`SELECT COUNT(*) FROM people WHERE Email2 IS NULL`,
		`SELECT COUNT(*) FROM people WHERE Name2 LIKE 'a%'`,
		`SELECT COUNT(*) FROM people WHERE Age2 IN (1)`,
		`SELECT NoSuchCol FROM people`,
		`SELECT SUM(NoSuchCol) FROM people`,
		`SELECT name FROM people ORDER BY NoSuchCol`,
	}
	for _, q := range checks {
		if _, err := db.Query(q); err == nil {
			t.Fatalf("%s: 应报列不存在", q)
		} else if st := sqlState(err); st != "42S22" {
			t.Fatalf("%s: 错误状态=%q, 想要 42S22 (%v)", q, st, err)
		}
	}
	// UPDATE/DELETE 的未知 WHERE 列也要报错而不是作用 0 行
	if _, err := db.Exec(`UPDATE people SET name = 'x' WHERE NoSuchCol = 1`); err == nil {
		t.Fatal("UPDATE 未知列应报错")
	} else if st := sqlState(err); st != "42S22" {
		t.Fatalf("UPDATE 未知列: 错误状态=%q, 想要 42S22", st)
	}
	if _, err := db.Exec(`DELETE FROM people WHERE NoSuchCol = 1`); err == nil {
		t.Fatal("DELETE 未知列应报错")
	} else if st := sqlState(err); st != "42S22" {
		t.Fatalf("DELETE 未知列: 错误状态=%q, 想要 42S22", st)
	}
}