TsDB2/DB/driver/join_test.go

package vrtxsql

// JOIN 支持测试: INNER/LEFT [OUTER] JOIN、链式多表、限定名列、WHERE 下推与
// 残余谓词 (跨表列比较 / 跨表 OR)、* 投影与重名列去重、歧义列/非法 ON 报错。
// 全部走标准 database/sql 全链路, 数据与断言一一对应 join.go 的执行策略。

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

// joinFixture 建 users/orders/refunds 三张表并灌入固定数据:
//
//	users:  1 alice 30 / 2 bob 25 / 3 carl 40
//	orders: 10 ->u1 99.5 book / 11 ->u1 20 pen / 12 ->u2 7.5 cup
//	refunds: 100 ->o10 broken / 101 ->o10 late
func joinFixture(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 users (id BIGINT PRIMARY KEY, name VARCHAR(32) NOT NULL, age INT)`)
	exec(`CREATE TABLE orders (id BIGINT PRIMARY KEY, user_id BIGINT, amount DOUBLE, title VARCHAR(32))`)
	exec(`CREATE TABLE refunds (id BIGINT PRIMARY KEY, order_id BIGINT, reason VARCHAR(32))`)
	exec(`INSERT INTO users (id, name, age) VALUES (?, ?, ?), (?, ?, ?), (?, ?, ?)`,
		1, "alice", 30, 2, "bob", 25, 3, "carl", 40)
	exec(`INSERT INTO orders (id, user_id, amount, title) VALUES (?, ?, ?, ?), (?, ?, ?, ?), (?, ?, ?, ?)`,
		10, 1, 99.5, "book", 11, 1, 20.0, "pen", 12, 2, 7.5, "cup")
	exec(`INSERT INTO refunds (id, order_id, reason) VALUES (?, ?, ?), (?, ?, ?)`,
		100, 10, "broken", 101, 10, "late")
}

type joinHit struct {
	name, title string
	amount      float64
}

// joinRows 执行 JOIN 查询并收集 (name, title, amount) 三元组。
func joinRows(t *testing.T, db *sql.DB, query string, args ...interface{}) []joinHit {
	t.Helper()
	rows, err := db.Query(query, args...)
	if err != nil {
		t.Fatalf("query: %v", err)
	}
	defer rows.Close()
	var got []joinHit
	for rows.Next() {
		var h joinHit
		var name, title []byte
		if err := rows.Scan(&name, &title, &h.amount); err != nil {
			t.Fatalf("scan: %v", err)
		}
		h.name, h.title = string(name), string(title)
		got = append(got, h)
	}
	if err := rows.Err(); err != nil {
		t.Fatal(err)
	}
	return got
}

// TestJoinInner INNER JOIN 命中: users/orders 按 user_id 关联, 验证行数与列值。
func TestJoinInner(t *testing.T) {
	db := openTestDB(t)
	joinFixture(t, db)

	rows, err := db.Query(`SELECT u.name, o.title, o.amount FROM users u JOIN orders o ON u.id = o.user_id ORDER BY o.id ASC`)
	if err != nil {
		t.Fatalf("inner join: %v", err)
	}
	cols, err := rows.Columns()
	if err != nil {
		t.Fatal(err)
	}
	if strings.Join(cols, ",") != "name,title,amount" {
		t.Fatalf("列名=%v, 想要 [name title amount]", cols)
	}
	rows.Close()

	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("行数=%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])
		}
	}

	// INNER JOIN 显式关键字 + 表名限定 (不写别名)
	got = joinRows(t, db, `SELECT users.name, orders.title, orders.amount FROM users INNER JOIN orders ON users.id = orders.user_id ORDER BY orders.id ASC`)
	if len(got) != len(want) {
		t.Fatalf("INNER JOIN 行数=%d, 想要 %d (%+v)", len(got), len(want), got)
	}
}

// TestJoinLeftNullRow LEFT OUTER JOIN 右表无匹配: 右列全 NULL 保留行。
func TestJoinLeftNullRow(t *testing.T) {
	db := openTestDB(t)
	joinFixture(t, db)

	rows, err := db.Query(`SELECT u.name, o.title FROM users u LEFT OUTER JOIN orders o ON u.id = o.user_id ORDER BY u.id ASC, o.id ASC`)
	if err != nil {
		t.Fatalf("left join: %v", err)
	}
	defer rows.Close()
	type row struct {
		name  string
		title sql.NullString
	}
	var got []row
	for rows.Next() {
		var r row
		var name []byte
		if err := rows.Scan(&name, &r.title); err != nil {
			t.Fatalf("scan: %v", err)
		}
		r.name = string(name)
		got = append(got, r)
	}
	if err := rows.Err(); err != nil {
		t.Fatal(err)
	}
	if len(got) != 4 {
		t.Fatalf("行数=%d, 想要 4 (%+v)", len(got), got)
	}
	if got[3].name != "carl" || got[3].title.Valid {
		t.Fatalf("carl 应为右表无匹配 (title NULL), 实际 %+v", got[3])
	}
	if got[0].title.String != "book" || got[1].title.String != "pen" || got[2].title.String != "cup" {
		t.Fatalf("匹配行不对: %+v", got)
	}
}

// TestJoinWhereOrderLimit JOIN + WHERE(限定名) + ORDER BY + LIMIT/OFFSET。
func TestJoinWhereOrderLimit(t *testing.T) {
	db := openTestDB(t)
	joinFixture(t, db)

	// u.age 下推 users 表, o.amount 下推 orders 表
	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 >= ? AND o.amount > ? ORDER BY o.amount DESC LIMIT 1`, 26, 50)
	want := []joinHit{{"alice", "book", 99.5}}
	if len(got) != 1 || got[0] != want[0] {
		t.Fatalf("got %+v, 想要 %+v", got, want)
	}

	// 非限定列名 (唯一归属 age 在 users) + LIMIT offset,count 写法
	// (age>=26 只剩 alice: ASC 序 [pen 20, book 99.5], LIMIT 1,1 = 跳过 1 行取 1 行)
	got = joinRows(t, db,
		`SELECT u.name, o.title, o.amount FROM users u JOIN orders o ON u.id = o.user_id
		 WHERE age >= ? ORDER BY o.amount ASC LIMIT 1, 1`, 26)
	if len(got) != 1 || got[0] != (joinHit{"alice", "book", 99.5}) {
		t.Fatalf("LIMIT offset,count got %+v, 想要 [{alice book 99.5}]", got)
	}

	// OFFSET 关键字写法 (ASC 序 [cup 7.5, pen 20, book 99.5], 跳 1 行取 1 行)
	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.amount ASC LIMIT 1 OFFSET 1`)
	if len(got) != 1 || got[0] != (joinHit{"alice", "pen", 20}) {
		t.Fatalf("OFFSET got %+v, 想要 [{alice pen 20}]", got)
	}
}

// TestJoinThreeTables 3 表链式 JOIN: users → orders → refunds。
func TestJoinThreeTables(t *testing.T) {
	db := openTestDB(t)
	joinFixture(t, db)

	rows, err := db.Query(
		`SELECT u.name, o.title, r.reason FROM users u
		 JOIN orders o ON u.id = o.user_id
		 JOIN refunds r ON o.id = r.order_id
		 ORDER BY r.id ASC`)
	if err != nil {
		t.Fatalf("3 表 join: %v", err)
	}
	defer rows.Close()
	var got [][2]string
	for rows.Next() {
		var name, title, reason []byte
		if err := rows.Scan(&name, &title, &reason); err != nil {
			t.Fatalf("scan: %v", err)
		}
		got = append(got, [2]string{string(name) + "/" + string(title), string(reason)})
	}
	if err := rows.Err(); err != nil {
		t.Fatal(err)
	}
	want := [][2]string{{"alice/book", "broken"}, {"alice/book", "late"}}
	if len(got) != len(want) {
		t.Fatalf("行数=%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])
		}
	}
}

// TestJoinStarAndDedup * 投影跨表展开, 重名列 (id) 用 别名_列名 去重。
func TestJoinStarAndDedup(t *testing.T) {
	db := openTestDB(t)
	joinFixture(t, db)

	rs, err := db.Query(`SELECT * FROM users u JOIN orders o ON u.id = o.user_id WHERE u.id = 1 ORDER BY o.id ASC`)
	if err != nil {
		t.Fatalf("star join: %v", err)
	}
	defer rs.Close()
	cols, err := rs.Columns()
	if err != nil {
		t.Fatal(err)
	}
	wantCols := "id,name,age,o_id,user_id,amount,title"
	if strings.Join(cols, ",") != wantCols {
		t.Fatalf("列=%v, 想要 %v", cols, wantCols)
	}
	n := 0
	for rs.Next() {
		vals := make([]interface{}, len(cols))
		ptrs := make([]interface{}, len(cols))
		for i := range vals {
			ptrs[i] = &vals[i]
		}
		if err := rs.Scan(ptrs...); err != nil {
			t.Fatalf("scan: %v", err)
		}
		if n == 0 {
			// 第一行: alice 的 book 订单
			if vals[0] != int64(1) || string(vals[1].([]byte)) != "alice" || vals[2] != int64(30) ||
				vals[3] != int64(10) || vals[4] != int64(1) || vals[5] != float64(99.5) ||
				string(vals[6].([]byte)) != "book" {
				t.Fatalf("行值不对: %#v", vals)
			}
		}
		n++
	}
	if err := rs.Err(); err != nil {
		t.Fatal(err)
	}
	if n != 2 {
		t.Fatalf("行数=%d, 想要 2", n)
	}

	// t.* 与限定列混用 (u.* = id,name,age 三列 + o.amount)
	rs2, err := db.Query(`SELECT u.*, o.amount FROM users u JOIN orders o ON u.id = o.user_id WHERE o.id = 12`)
	if err != nil {
		t.Fatalf("t.* 混用: %v", err)
	}
	defer rs2.Close()
	if !rs2.Next() {
		t.Fatal("t.* 混用无行")
	}
	var id, age int64
	var uname []byte
	var amount float64
	if err := rs2.Scan(&id, &uname, &age, &amount); err != nil {
		t.Fatalf("scan: %v", err)
	}
	if id != 2 || string(uname) != "bob" || age != 25 || amount != 7.5 {
		t.Fatalf("t.* 混用行 = %d,%s,%d,%v", id, uname, age, amount)
	}
}

// TestJoinResidualPredicate 跨表条件作为残余谓词在组合行上过滤:
// 列与列比较 (o.amount > u.age) 与跨表 OR (u.name = ? OR o.amount > ?)。
func TestJoinResidualPredicate(t *testing.T) {
	db := openTestDB(t)
	joinFixture(t, db)

	// 列比较: alice 99.5>30 命中, 20>30 / 7.5>25 不命中
	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.amount > u.age ORDER BY o.id ASC`)
	want := []joinHit{{"alice", "book", 99.5}}
	if len(got) != len(want) || got[0] != want[0] {
		t.Fatalf("列比较 got %+v, 想要 %+v", got, want)
	}

	// 跨表 OR (结构条件无法下推): alice 两行 + amount>50 的 alice/book
	got = joinRows(t, db,
		`SELECT u.name, o.title, o.amount FROM users u INNER JOIN orders o ON u.id = o.user_id
		 WHERE u.name = ? OR o.amount > ? ORDER BY o.id ASC`, "alice", 50)
	want = []joinHit{{"alice", "book", 99.5}, {"alice", "pen", 20}}
	if len(got) != len(want) {
		t.Fatalf("跨表 OR 行数=%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])
		}
	}
}

// TestJoinErrors 歧义列 / 非法 ON / 未知限定的明确报错。
func TestJoinErrors(t *testing.T) {
	db := openTestDB(t)
	joinFixture(t, db)

	// SELECT 非限定歧义列 (users/orders 都有 id)
	if _, err := db.Query(`SELECT id FROM users u JOIN orders o ON u.id = o.user_id`); err == nil ||
		!strings.Contains(err.Error(), "不明确") {
		t.Fatalf("SELECT 歧义列应报 不明确, 实际 %v", err)
	}
	// WHERE 非限定歧义列 (age 只属 users, id 两表都有)
	if _, err := db.Query(`SELECT u.name FROM users u JOIN orders o ON u.id = o.user_id WHERE id > 10`); err == nil ||
		!strings.Contains(err.Error(), "不明确") {
		t.Fatalf("WHERE 歧义列应报 不明确, 实际 %v", err)
	}
	// ON 非等值
	if _, err := db.Query(`SELECT * FROM users u JOIN orders o ON u.id > o.user_id`); err == nil ||
		!strings.Contains(err.Error(), "JOIN ON 仅支持等值条件") {
		t.Fatalf("ON 非等值应报错, 实际 %v", err)
	}
	// ON 未知限定
	if _, err := db.Query(`SELECT * FROM users u JOIN orders o ON x.id = o.user_id`); err == nil {
		t.Fatalf("ON 未知限定应报错")
	}
	// ON 列不存在
	if _, err := db.Query(`SELECT * FROM users u JOIN orders o ON u.nope = o.user_id`); err == nil {
		t.Fatalf("ON 未知列应报错")
	}
	// JOIN 与聚合同用
	if _, err := db.Query(`SELECT COUNT(*) FROM users u JOIN orders o ON u.id = o.user_id`); err == nil {
		t.Fatalf("JOIN+聚合应报错")
	}
}

// TestJoinQualifiedSingleTable 单表查询的限定名列/t.* (兼容路径, 解限定后走标准翻译)。
func TestJoinQualifiedSingleTable(t *testing.T) {
	db := openTestDB(t)
	joinFixture(t, db)

	var name []byte
	if err := db.QueryRow(`SELECT u.name FROM users u WHERE u.age > ? ORDER BY u.id LIMIT 1`, 26).Scan(&name); err != nil {
		t.Fatalf("限定名 WHERE: %v", err)
	}
	if string(name) != "alice" {
		t.Fatalf("name=%q, 想要 alice", name)
	}
	if err := db.QueryRow(`SELECT name FROM users AS u WHERE u.age >= ? ORDER BY u.id DESC LIMIT 1`, 26).Scan(&name); err != nil {
		t.Fatalf("AS 别名 + 限定名: %v", err)
	}
	if string(name) != "carl" {
		t.Fatalf("name=%q, 想要 carl", name)
	}

	// t.* 单表展开 + 聚合限定列
	var age int64
	if err := db.QueryRow(`SELECT COUNT(u.id) FROM users u WHERE u.age > ?`, 26).Scan(&age); err != nil {
		t.Fatalf("聚合限定列: %v", err)
	}
	if age != 2 {
		t.Fatalf("count=%d, 想要 2", age)
	}

	// 限定词不是本表 → 明确报错
	if _, err := db.Query(`SELECT o.name FROM users u WHERE u.age > 1`); err == nil {
		t.Fatalf("限定词不匹配应报错")
	}
}