TsDB2/DB/driver/concat_like_test.go

package vrtxsql

// SQLite 方言 '||' 字符串拼接测试: 拼接链作 LIKE/NOT LIKE 的左操作数
// (生产形态: WHERE ','||service_name||',' LIKE '%,check,%' AND deleted_at IS NULL,
// 语义 = 逗号分隔列表的元素精确包含 x —— 'check'/'a,check,b' 命中, 'check2'/'precheck' 不命中)。

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

func openConcatTestDB(t *testing.T) *sql.DB {
	t.Helper()
	db := openTestDB(t)
	if _, err := db.Exec(`CREATE TABLE services (
		id BIGINT PRIMARY KEY,
		service_name VARCHAR(64) NOT NULL,
		deleted_at VARCHAR(32) NULL
	)`); err != nil {
		t.Fatalf("create table: %v", err)
	}
	rows := []struct {
		id      int64
		name    string
		deleted sql.NullString
	}{
		{1, "check", sql.NullString{}},                         // 单元素精确命中
		{2, "a,check,b", sql.NullString{}},                     // 列表中间元素命中
		{3, "check2", sql.NullString{}},                        // 前缀相似, 不命中
		{4, "precheck", sql.NullString{}},                      // 后缀相似, 不命中
		{5, "other", sql.NullString{String: "2024-01-01", Valid: true}}, // 已删除
	}
	for _, r := range rows {
		if _, err := db.Exec(`INSERT INTO services (id, service_name, deleted_at) VALUES (?, ?, ?)`,
			r.id, r.name, r.deleted); err != nil {
			t.Fatalf("insert %d: %v", r.id, err)
		}
	}
	return db
}

func queryServiceIDs(t *testing.T, db *sql.DB, query string, args ...interface{}) []int64 {
	t.Helper()
	rows, err := db.Query(query, args...)
	if err != nil {
		t.Fatalf("query %q: %v", query, err)
	}
	defer rows.Close()
	var ids []int64
	for rows.Next() {
		var id int64
		var name string
		var deletedAt sql.NullString
		if err := rows.Scan(&id, &name, &deletedAt); err != nil {
			t.Fatalf("scan: %v", err)
		}
		ids = append(ids, id)
	}
	if err := rows.Err(); err != nil {
		t.Fatalf("rows: %v", err)
	}
	return ids
}

func TestConcatLikeMatchesExactListElement(t *testing.T) {
	db := openConcatTestDB(t)

	// ','||service_name||',' LIKE '%,check,%': 恰好命中 1 ('check') 和 2 ('a,check,b')。
	got := queryServiceIDs(t, db,
		`SELECT id, service_name, deleted_at FROM services WHERE ','||service_name||',' LIKE '%,check,%' ORDER BY id`)
	if len(got) != 2 || got[0] != 1 || got[1] != 2 {
		t.Fatalf("got %v, 想要 [1 2]", got)
	}
}

func TestConcatNotLikeReverses(t *testing.T) {
	db := openConcatTestDB(t)

	got := queryServiceIDs(t, db,
		`SELECT id, service_name, deleted_at FROM services WHERE ','||service_name||',' NOT LIKE '%,check,%' ORDER BY id`)
	// 3 (check2) 与 4 (precheck) 反向命中; 5 (other) 是已删除行但本条件不含 deleted_at。
	if len(got) != 3 || got[0] != 3 || got[1] != 4 || got[2] != 5 {
		t.Fatalf("got %v, 想要 [3 4 5]", got)
	}
}

func TestConcatLikeWithIsNullCombination(t *testing.T) {
	db := openConcatTestDB(t)

	// 生产触发 SQL 同款形态: 拼接 LIKE + AND deleted_at IS NULL + LIMIT。
	var id int64
	var name string
	err := db.QueryRow(
		`SELECT id, service_name FROM services WHERE ','||service_name||',' like '%,check,%' AND deleted_at IS NULL ORDER BY id LIMIT 1`).
		Scan(&id, &name)
	if err != nil {
		t.Fatalf("query: %v", err)
	}
	if id != 1 || name != "check" {
		t.Fatalf("got id=%d name=%q, 想要 id=1 name=check", id, name)
	}

	// NOT LIKE + IS NULL: 未删除且不含 check 元素 → 3 / 4。
	got := queryServiceIDs(t, db,
		`SELECT id, service_name, deleted_at FROM services WHERE ','||service_name||',' NOT LIKE '%,check,%' AND deleted_at IS NULL ORDER BY id`)
	if len(got) != 2 || got[0] != 3 || got[1] != 4 {
		t.Fatalf("got %v, 想要 [3 4]", got)
	}
}

func TestConcatLikePatternPlaceholder(t *testing.T) {
	db := openConcatTestDB(t)

	// 模式走占位符 (GORM Where("... LIKE ?", pat) 形态)。
	got := queryServiceIDs(t, db,
		`SELECT id, service_name, deleted_at FROM services WHERE ','||service_name||',' LIKE ? ORDER BY id`, "%,check,%")
	if len(got) != 2 || got[0] != 1 || got[1] != 2 {
		t.Fatalf("got %v, 想要 [1 2]", got)
	}
}

func TestConcatRejectedForNonLikeOperators(t *testing.T) {
	db := openConcatTestDB(t)

	queries := []string{
		`SELECT id FROM services WHERE ','||service_name||',' = ',check,'`,
		`SELECT id FROM services WHERE ','||service_name||',' IN (',check,')`,
		`SELECT id FROM services WHERE ','||service_name||',' IS NULL`,
		`SELECT id FROM services WHERE service_name||',' LIKE '%,check,%' AND ','||service_name = ',check'`,
	}
	for _, q := range queries {
		if _, err := db.Query(q); err == nil {
			t.Fatalf("%q 应报错 (|| 拼接仅支持 LIKE 匹配)", q)
		} else if !strings.Contains(err.Error(), "|| 拼接仅支持 LIKE 匹配") {
			t.Fatalf("%q 报错 %q, 期望包含 '|| 拼接仅支持 LIKE 匹配'", q, err)
		}
	}

	// 单个 '|' 仍是 tokenizer 报错。
	if _, err := db.Query(`SELECT id FROM services WHERE service_name | 'x' LIKE 'y'`); err == nil {
		t.Fatalf("单个 '|' 应报无法识别的字符")
	} else if !strings.Contains(err.Error(), "无法识别的字符") {
		t.Fatalf("单个 '|' 报错 %q, 期望 '无法识别的字符'", err)
	}
}