beads/cmd/bd/sql_proxied_integration_test.go
Julian Knutsen c4b1cb699d
fix(sql): classify bd sql statements with the SQL parser; never drop result sets (#6932)
Proxied bd sql misclassified WITH [RECURSIVE] ... SELECT as a write and silently returned no rows; CALL result sets were dropped on both paths. Classify statements with the dolthub/vitess parser (internal/storage/sqlclass: Read/Write/Mixed); Mixed commits and renders any rows, so no path silently drops a result set.

Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>
2026-09-29 01:08:10 -07:00

258 lines
8.7 KiB
Go

//go:build cgo
package main
import (
"encoding/json"
"strconv"
"strings"
"testing"
)
func bdProxiedSQL(t *testing.T, bd, dir string, args ...string) (string, string) {
t.Helper()
fullArgs := append([]string{"sql"}, args...)
stdout, stderr, err := bdProxiedRunBuffers(t, bd, dir, fullArgs...)
if err != nil {
t.Fatalf("bd sql %s failed: %v\nstdout:\n%s\nstderr:\n%s",
strings.Join(args, " "), err, stdout, stderr)
}
return stdout, stderr
}
func bdProxiedSQLJSON(t *testing.T, bd, dir string, query string) []map[string]interface{} {
t.Helper()
stdout, _ := bdProxiedSQL(t, bd, dir, "--json", query)
start := strings.Index(stdout, "[")
if start < 0 {
t.Fatalf("no JSON array found in sql output:\n%s", stdout)
}
var rows []map[string]interface{}
if err := json.Unmarshal([]byte(stdout[start:]), &rows); err != nil {
t.Fatalf("failed to parse sql JSON output: %v\nraw: %s", err, stdout[start:])
}
return rows
}
func bdProxiedSQLFail(t *testing.T, bd, dir string, args ...string) string {
t.Helper()
fullArgs := append([]string{"sql"}, args...)
stdout, stderr, err := bdProxiedRunBuffers(t, bd, dir, fullArgs...)
if err == nil {
t.Fatalf("expected bd sql %s to fail, but it succeeded:\nstdout:\n%s\nstderr:\n%s",
strings.Join(args, " "), stdout, stderr)
}
return stdout + stderr
}
func TestProxiedServerSQL(t *testing.T) {
requireSharedProxiedServer(t)
t.Parallel()
bd := buildEmbeddedBD(t)
p := newSharedProxiedProject(t, bd, "sql")
one := bdProxiedCreate(t, bd, p.dir, "Test issue one", "--type", "task", "--priority", "1")
two := bdProxiedCreate(t, bd, p.dir, "Test issue two", "--type", "bug", "--priority", "2")
bdProxiedClose(t, bd, p.dir, two.ID)
t.Run("select_count", func(t *testing.T) {
rows := bdProxiedSQLJSON(t, bd, p.dir, "SELECT COUNT(*) as count FROM issues")
if len(rows) != 1 {
t.Fatalf("expected 1 row, got %d", len(rows))
}
count, ok := rows[0]["count"]
if !ok {
t.Fatal("missing 'count' column in result")
}
if !sqlValueEquals(count, 2) {
t.Errorf("expected count=2, got %v", count)
}
})
t.Run("select_with_filter", func(t *testing.T) {
rows := bdProxiedSQLJSON(t, bd, p.dir, `SELECT id, title FROM issues WHERE status = 'open'`)
if len(rows) != 1 {
t.Fatalf("expected 1 open issue, got %d", len(rows))
}
if rows[0]["title"] != "Test issue one" {
t.Errorf("expected 'Test issue one', got %q", rows[0]["title"])
}
if rows[0]["id"] != one.ID {
t.Errorf("expected id %q, got %q", one.ID, rows[0]["id"])
}
})
t.Run("empty_result_json", func(t *testing.T) {
rows := bdProxiedSQLJSON(t, bd, p.dir, `SELECT * FROM issues WHERE title = 'nonexistent'`)
if len(rows) != 0 {
t.Errorf("expected 0 rows, got %d", len(rows))
}
})
t.Run("table_output", func(t *testing.T) {
stdout, _ := bdProxiedSQL(t, bd, p.dir, "SELECT COUNT(*) as count FROM issues")
if !strings.Contains(stdout, "count") {
t.Errorf("expected table header 'count' in output: %s", stdout)
}
if !strings.Contains(stdout, "(1 rows)") {
t.Errorf("expected '(1 rows)' in output: %s", stdout)
}
})
t.Run("empty_table_output", func(t *testing.T) {
stdout, _ := bdProxiedSQL(t, bd, p.dir, `SELECT * FROM issues WHERE title = 'nonexistent'`)
if !strings.Contains(stdout, "(0 rows)") {
t.Errorf("expected '(0 rows)' in output: %s", stdout)
}
})
t.Run("csv_output", func(t *testing.T) {
stdout, _ := bdProxiedSQL(t, bd, p.dir, "--csv", `SELECT id, title FROM issues WHERE status = 'open'`)
lines := strings.Split(strings.TrimSpace(stdout), "\n")
if len(lines) != 2 {
t.Fatalf("expected header + 1 data row, got %d lines:\n%s", len(lines), stdout)
}
if lines[0] != "id,title" {
t.Errorf("expected CSV header 'id,title', got %q", lines[0])
}
if !strings.Contains(lines[1], one.ID) || !strings.Contains(lines[1], "Test issue one") {
t.Errorf("expected CSV row with open issue, got %q", lines[1])
}
})
t.Run("exec_update_reports_rows_affected", func(t *testing.T) {
stdout, _ := bdProxiedSQL(t, bd, p.dir,
"--json", "UPDATE issues SET priority = 3 WHERE id = '"+one.ID+"'")
var res map[string]interface{}
start := strings.Index(stdout, "{")
if start < 0 {
t.Fatalf("no JSON object in exec output:\n%s", stdout)
}
if err := json.Unmarshal([]byte(stdout[start:]), &res); err != nil {
t.Fatalf("parse exec JSON: %v\nraw: %s", err, stdout[start:])
}
if !sqlValueEquals(res["rows_affected"], 1) {
t.Errorf("expected rows_affected=1, got %v", res["rows_affected"])
}
rows := bdProxiedSQLJSON(t, bd, p.dir,
"SELECT priority FROM issues WHERE id = '"+one.ID+"'")
if len(rows) != 1 {
t.Fatalf("expected 1 row after update, got %d", len(rows))
}
if !sqlValueEquals(rows[0]["priority"], 3) {
t.Errorf("expected priority=3 after update, got %v", rows[0]["priority"])
}
})
t.Run("exec_persists_across_connections", func(t *testing.T) {
bdProxiedSQL(t, bd, p.dir,
"UPDATE issues SET priority = 4 WHERE id = '"+two.ID+"'")
db := openProxiedDB(t, p)
var priority int
if err := db.QueryRow(
"SELECT priority FROM issues WHERE id = ?", two.ID).Scan(&priority); err != nil {
t.Fatalf("query priority for %s: %v", two.ID, err)
}
if priority != 4 {
t.Errorf("expected committed priority=4, got %d", priority)
}
})
t.Run("invalid_sql_fails", func(t *testing.T) {
out := bdProxiedSQLFail(t, bd, p.dir, "SELECT * FROM nonexistent_table")
if !strings.Contains(strings.ToLower(out), "error") {
t.Errorf("expected error for invalid SQL, got: %s", out)
}
})
}
func sqlValueEquals(v any, want float64) bool {
switch x := v.(type) {
case float64:
return x == want
case string:
f, err := strconv.ParseFloat(x, 64)
return err == nil && f == want
default:
return false
}
}
// TestProxiedServerSQLStatementRouting pins how proxied `bd sql` routes
// statements that a prefix scan misreads: CTE reads must return their rows,
// CTE writes must commit, and a statement that writes and returns a result set
// must render that result set instead of reporting "0 rows affected".
func TestProxiedServerSQLStatementRouting(t *testing.T) {
requireSharedProxiedServer(t)
t.Parallel()
bd := buildEmbeddedBD(t)
p := newSharedProxiedProject(t, bd, "sqlcte")
one := bdProxiedCreate(t, bd, p.dir, "CTE issue one", "--type", "task", "--priority", "1")
two := bdProxiedCreate(t, bd, p.dir, "CTE issue two", "--type", "task", "--priority", "2")
t.Run("cte_column_list_select_returns_rows", func(t *testing.T) {
rows := bdProxiedSQLJSON(t, bd, p.dir,
"WITH t(issue_id) AS (SELECT id FROM issues WHERE id = '"+one.ID+"') SELECT issue_id FROM t")
if len(rows) != 1 {
t.Fatalf("expected 1 row, got %d: %v", len(rows), rows)
}
if rows[0]["issue_id"] != one.ID {
t.Errorf("expected issue_id=%q, got %v", one.ID, rows[0]["issue_id"])
}
})
t.Run("recursive_cte_select_returns_rows", func(t *testing.T) {
query := "WITH RECURSIVE n(i) AS (SELECT 1 UNION ALL SELECT i + 1 FROM n WHERE i < 3) SELECT i FROM n"
rows := bdProxiedSQLJSON(t, bd, p.dir, query)
if len(rows) != 3 {
t.Fatalf("expected 3 rows, got %d: %v", len(rows), rows)
}
stdout, _ := bdProxiedSQL(t, bd, p.dir, query)
if !strings.Contains(stdout, "(3 rows)") {
t.Errorf("expected '(3 rows)' in table output, got:\n%s", stdout)
}
})
t.Run("cte_write_commits", func(t *testing.T) {
stdout, _ := bdProxiedSQL(t, bd, p.dir, "--json",
"WITH t(issue_id) AS (SELECT id FROM issues WHERE id = '"+two.ID+"') "+
"UPDATE issues SET priority = 0 WHERE id IN (SELECT issue_id FROM t)")
start := strings.Index(stdout, "{")
if start < 0 {
t.Fatalf("no JSON object in exec output:\n%s", stdout)
}
var res map[string]interface{}
if err := json.Unmarshal([]byte(stdout[start:]), &res); err != nil {
t.Fatalf("parse exec JSON: %v\nraw: %s", err, stdout[start:])
}
if !sqlValueEquals(res["rows_affected"], 1) {
t.Errorf("expected rows_affected=1, got %v", res["rows_affected"])
}
db := openProxiedDB(t, p)
var priority int
if err := db.QueryRow("SELECT priority FROM issues WHERE id = ?", two.ID).Scan(&priority); err != nil {
t.Fatalf("query priority for %s: %v", two.ID, err)
}
if priority != 0 {
t.Errorf("expected committed priority=0, got %d", priority)
}
})
t.Run("call_renders_result_set", func(t *testing.T) {
rows := bdProxiedSQLJSON(t, bd, p.dir, "CALL DOLT_BRANCH('sql-routing-probe')")
if len(rows) != 1 {
t.Fatalf("expected the procedure's 1 status row, got %d: %v", len(rows), rows)
}
if _, ok := rows[0]["status"]; !ok {
t.Errorf("expected a status column, got %v", rows[0])
}
branches := bdProxiedSQLJSON(t, bd, p.dir, "SELECT name FROM dolt_branches WHERE name = 'sql-routing-probe'")
if len(branches) != 1 {
t.Errorf("expected the CALL to have created branch sql-routing-probe, got %v", branches)
}
})
}