Skip to content

armgit / Pulse

Public Imported from GitHub
0

Real-time monitoring dashboard for Proxmox VE, PBS, Docker, Kubernetes, TrueNAS and vSphere. Self-hosted, with alerts for failed backups, full disks, unhealthy containers and offline nodes.

34.0 KB
Raw Blame
package metrics

import (
	"database/sql"
	"fmt"
	"net/url"
	"reflect"
	"strings"
	"testing"
	"time"

	pdb "github.com/rcourtman/pulse-go-rewrite/pkg/db"

	_ "modernc.org/sqlite"
)

// TestQueryPlansUseIndexes validates that all critical metrics-store SQL queries
// use indexed lookups (SEARCH) rather than full table scans (SCAN TABLE).
// This catches index regressions — if a schema migration drops an index or a
// query change breaks index eligibility, these tests fail immediately.
//
// Each sub-test runs EXPLAIN QUERY PLAN on SQL the store uses at
// runtime. Queries are classified by how they should access the table:
//   - searchRequired: the plan must use SEARCH (B-tree point/range lookup)
//   - allowCoveringIndexScan: the plan may use a covering-index scan, which
//     reads only the index without touching the table — acceptable when no
//     index has the query's filter columns as a prefix
//
// In all cases, a bare "SCAN TABLE metrics" (full table scan) is rejected.
//
// NOTE: Maintenance SQL is duplicated from store.go. Retained reads exercise
// the shared runtime builder directly in TestRetainedQueryPlansUseIndexes.
// If store.go's schema or queries change, these tests must be updated in
// lockstep — a mismatch means they validate stale SQL.
func TestQueryPlansUseIndexes(t *testing.T) {
	db := newPlanTestDB(t)

	// farFuture is an int64 timestamp safe on 32-bit platforms.
	const farFuture int64 = 9999999999

	tests := []struct {
		name  string
		query string
		args  []any
		// wantIndex, if non-empty, asserts this index name appears on the
		// same plan line as a SEARCH on the metrics table.
		wantIndex string
		// allowCoveringIndexScan permits "SCAN metrics USING COVERING INDEX ..."
		// for queries where the planner correctly chooses a covering-index
		// scan over a table scan. When false, only SEARCH is accepted.
		allowCoveringIndexScan bool
		// allowIndexScan permits "SCAN metrics USING INDEX ..." (index-ordered
		// scan) for queries where the planner uses an index to satisfy GROUP BY
		// or ORDER BY without a separate sort, but still reads table rows.
		allowIndexScan bool
	}{
		{
			name:      "retention delete by tier+time",
			query:     `DELETE FROM metrics WHERE tier = ? AND timestamp < ?`,
			args:      []any{"raw", farFuture},
			wantIndex: "idx_metrics_tier_time",
		},
		{
			name: "rollup candidate discovery (legacy per-candidate path)",
			query: `SELECT DISTINCT resource_type, resource_id, metric_type
				FROM metrics
				WHERE tier = ? AND timestamp >= ? AND timestamp < ?`,
			args: []any{"raw", int64(0), farFuture},
			// This query filters on (tier, timestamp) which aren't the leading
			// columns of most indexes. The planner correctly uses a covering-
			// index scan — reading only the index, never the table.
			allowCoveringIndexScan: true,
		},
		{
			name: "rollup aggregation insert (legacy per-candidate path)",
			query: `INSERT OR IGNORE INTO metrics (resource_type, resource_id, metric_type, value, min_value, max_value, timestamp, tier)
				SELECT
					resource_type, resource_id, metric_type,
					AVG(value) as value,
					MIN(COALESCE(min_value, value)) as min_value,
					MAX(COALESCE(max_value, value)) as max_value,
					(timestamp / ?) * ? as bucket_ts,
					?
				FROM metrics
				WHERE resource_type = ? AND resource_id = ? AND metric_type = ?
				AND tier = ? AND timestamp >= ? AND timestamp < ?
				GROUP BY resource_type, resource_id, metric_type, bucket_ts`,
			args:      []any{int64(60), int64(60), "minute", "vm", "vm-1", "cpu", "raw", int64(0), farFuture},
			wantIndex: metricsIdentityIndex,
		},
		{
			name: "batched rollup aggregation insert",
			query: `INSERT OR IGNORE INTO metrics (resource_type, resource_id, metric_type, value, min_value, max_value, timestamp, tier)
				SELECT
					resource_type,
					resource_id,
					metric_type,
					AVG(value) as value,
					MIN(COALESCE(min_value, value)) as min_value,
					MAX(COALESCE(max_value, value)) as max_value,
					(timestamp / ?) * ? as bucket_ts,
					?
				FROM metrics
				WHERE tier = ? AND timestamp >= ? AND timestamp < ?
				GROUP BY resource_type, resource_id, metric_type, bucket_ts`,
			args: []any{int64(60), int64(60), "minute", "raw", int64(0), farFuture},
			// The SELECT filters on (tier, timestamp) without resource/metric
			// columns. SQLite may use an index-ordered scan (SCAN USING INDEX)
			// on the identity index. A TEMP B-TREE may still be used for
			// GROUP BY. The INSERT side uses the same unique index for ON CONFLICT.
			allowIndexScan: true,
		},
		{
			name:      "max timestamp for tier (rollup scheduling)",
			query:     `SELECT MAX(timestamp) FROM metrics WHERE tier = ?`,
			args:      []any{"raw"},
			wantIndex: "idx_metrics_tier_time",
		},
		{
			name:  "tier count stats (GetStats)",
			query: `SELECT tier, COUNT(*) FROM metrics GROUP BY tier`,
			args:  []any{},
			// GROUP BY tier scans all rows — a covering-index scan on
			// idx_metrics_tier_time reads only the index (tier, timestamp),
			// avoiding the full table row fetch. A SEARCH is also acceptable
			// if the planner chooses a different strategy.
			allowCoveringIndexScan: true,
		},
	}

	for _, tt := range tests {
		t.Run(tt.name, func(t *testing.T) {
			plan := explainQueryPlan(t, db, tt.query, tt.args)

			// Reject a bare full table scan — this is always bad.
			if containsFullTableScan(plan) {
				t.Errorf("query uses full table scan on metrics; expected indexed access\nPlan:\n%s", plan)
			}

			if tt.allowIndexScan {
				// Must use SEARCH, covering-index scan, OR index-ordered scan.
				if !containsMetricsSearch(plan) && !containsCoveringIndexScan(plan) && !containsIndexScan(plan) {
					t.Errorf("query plan has neither SEARCH on metrics nor index scan\nPlan:\n%s", plan)
				}
			} else if tt.allowCoveringIndexScan {
				// Must use EITHER a SEARCH on metrics or a covering-index scan on metrics.
				if !containsMetricsSearch(plan) && !containsCoveringIndexScan(plan) {
					t.Errorf("query plan has neither SEARCH on metrics nor covering-index scan\nPlan:\n%s", plan)
				}
			} else {
				// Must contain at least one SEARCH on the metrics table.
				// We scope to "metrics" to avoid false passes from constraint-
				// handling SEARCH nodes in INSERT ... ON CONFLICT plans.
				if !containsMetricsSearch(plan) {
					t.Errorf("query plan does not contain SEARCH on metrics table; may not be using an index\nPlan:\n%s", plan)
				}
			}

			// If a specific index is expected, verify that the same plan line
			// that does a SEARCH on metrics also references that index.
			// This prevents false passes where one node SEARCHes via
			// conflict handling and a different node uses the expected index.
			if tt.wantIndex != "" && !containsMetricsSearchWithIndex(plan, tt.wantIndex) {
				t.Errorf("expected SEARCH on metrics using index %q not found in plan\nPlan:\n%s", tt.wantIndex, plan)
			}
		})
	}
}

// Query, QueryAll and both batch variants share this exact runtime SQL. The
// display step is applied in SQLite after tier reconciliation.
func TestRetainedQueryPlansUseIndexes(t *testing.T) {
	db := newPlanTestDB(t)
	store := &Store{}
	for _, window := range []time.Duration{time.Hour, 24 * time.Hour, 7 * 24 * time.Hour, 30 * 24 * time.Hour} {
		for _, filtered := range []bool{false, true} {
			for _, step := range []int64{0, 60} {
				t.Run(fmt.Sprintf("%s/filtered_%v/step_%d", window, filtered, step), func(t *testing.T) {
					var metrics []string
					if filtered {
						metrics = []string{"cpu", "memory"}
					}
					end := time.Unix(2000000000, 0)
					sql, args := retainedQuerySQL("vm", []string{"vm-1", "vm-2", "vm-3"}, metrics, end.Add(-window), end, step, store.tierFallbacks(window), false, false)
					plan := explainQueryPlan(t, db, sql, args)
					searches := 0
					for _, line := range strings.Split(plan, "\n") {
						if strings.Contains(line, "SCAN m ") || strings.Contains(line, "SCAN h ") {
							t.Fatalf("unbounded metrics read: %s", plan)
						}
						if strings.Contains(line, "SEARCH m ") || strings.Contains(line, "SEARCH h ") {
							searches++
							for _, constraint := range []string{"resource_type=?", "resource_id=?", "tier=?", "timestamp>?", "timestamp<?"} {
								if !strings.Contains(line, constraint) {
									t.Fatalf("retained search missing indexed %s: %s", constraint, line)
								}
							}
						}
					}
					n := len(store.tierFallbacks(window))
					if searches != n*(n+1)/2 {
						t.Fatalf("expected every tier and overlap probe indexed, got %d: %s", searches, plan)
					}
				})
			}
		}
	}
}

// A single present tier uses direct aggregation. It must retain the same
// bounded identity/tier/time lookup as the reconciled multi-tier query.
func TestRetainedSingleTierQueryPlansUseIndexes(t *testing.T) {
	db := newPlanTestDB(t)
	end := time.Unix(2000000000, 0)
	for _, tier := range []Tier{TierRaw, TierMinute, TierHourly, TierDaily} {
		for _, filtered := range []bool{false, true} {
			t.Run(fmt.Sprintf("%s/filtered_%v", tier, filtered), func(t *testing.T) {
				var metrics []string
				if filtered {
					metrics = []string{"cpu"}
				}
				query, args := retainedQuerySQL("vm", []string{"vm-1"}, metrics, end.Add(-time.Hour), end, 60, []Tier{tier}, false, false)
				plan := explainQueryPlan(t, db, query, args)
				if strings.Contains(plan, "SCAN m ") || !strings.Contains(plan, "SEARCH m ") {
					t.Fatalf("single tier must use a bounded lookup: %s", plan)
				}
				for _, constraint := range []string{"resource_type=?", "resource_id=?", "tier=?", "timestamp>?", "timestamp<?"} {
					if !strings.Contains(plan, constraint) {
						t.Fatalf("missing indexed %s: %s", constraint, plan)
					}
				}
			})
		}
	}
}

func TestAvailabilityHistoryQueryPlansUseIndexes(t *testing.T) {
	db := newPlanTestDB(t)

	tests := []struct {
		name      string
		query     string
		args      []any
		wantIndex string
	}{
		{
			name: "fleet rollup batch",
			query: `SELECT target_id, bucket_start, reachable_millis, unreachable_millis,
				indeterminate_millis, latency_count, latency_sum,
				COALESCE(latency_min, 0), COALESCE(latency_max, 0)
				FROM availability_history_buckets
				WHERE target_id IN (?, ?, ?) AND tier = ? AND bucket_start >= ? AND bucket_start < ?
				ORDER BY target_id, bucket_start`,
			args:      []any{"target-1", "target-2", "target-3", "minute", int64(0), int64(9999999999)},
			wantIndex: "sqlite_autoindex_availability_history_buckets_1",
		},
		{
			name: "latest live tail batch",
			query: `SELECT target_id, timeline_at_ns, valid_until_ns
				FROM availability_observations
				WHERE target_id IN (?, ?, ?)
				ORDER BY target_id, timeline_at_ns DESC, id DESC`,
			args:      []any{"target-1", "target-2", "target-3"},
			wantIndex: "idx_availability_observations_target_timeline",
		},
		{
			name: "revision boundary batch",
			query: `SELECT target_id, revision, started_at_ns
				FROM availability_revision_boundaries
				WHERE target_id IN (?, ?, ?) AND started_at_ns >= ? AND started_at_ns < ?
				ORDER BY target_id, started_at_ns`,
			args:      []any{"target-1", "target-2", "target-3", int64(0), int64(9999999999)},
			wantIndex: "idx_availability_boundaries_target_time",
		},
		{
			name:      "raw retention",
			query:     `DELETE FROM availability_observations WHERE timeline_at_ns < ?`,
			args:      []any{int64(9999999999)},
			wantIndex: "idx_availability_observations_retention",
		},
	}

	for _, tt := range tests {
		t.Run(tt.name, func(t *testing.T) {
			plan := explainQueryPlan(t, db, tt.query, tt.args)
			if !strings.Contains(plan, "SEARCH") || !strings.Contains(plan, tt.wantIndex) {
				t.Fatalf("expected indexed availability-history search using %s\nPlan:\n%s", tt.wantIndex, plan)
			}
		})
	}
}

// lineRefersToMetrics returns true if a plan line references the "metrics"
// table (not metrics_meta). SQLite may emit "SEARCH metrics USING ...",
// "SEARCH TABLE metrics ...", "SCAN metrics ...", or "SCAN TABLE metrics ...".
func lineRefersToMetrics(line string) bool {
	// Match "metrics " (with space after), "metrics(" (function-like),
	// or "metrics" at end of line. Reject "metrics_meta".
	for _, needle := range []string{"metrics ", "metrics("} {
		if strings.Contains(line, needle) && !strings.Contains(line, "metrics_meta") {
			return true
		}
	}
	// Handle "metrics" at end of line (e.g., bare "SCAN metrics").
	if strings.HasSuffix(strings.TrimSpace(line), "metrics") && !strings.Contains(line, "metrics_meta") {
		return true
	}
	return false
}

// containsMetricsSearch returns true if any plan line contains a SEARCH
// specifically on the metrics table (e.g., "SEARCH metrics USING INDEX ..."
// or "SEARCH TABLE metrics USING INDEX ...").
func containsMetricsSearch(plan string) bool {
	for _, line := range strings.Split(plan, "\n") {
		if strings.Contains(line, "SEARCH") && lineRefersToMetrics(line) {
			return true
		}
	}
	return false
}

// containsMetricsSearchWithIndex returns true if a single plan line contains
// a SEARCH on the metrics table AND the given index name. This ensures the
// expected index is used on the metrics-table read path, not on a separate node.
func containsMetricsSearchWithIndex(plan, indexName string) bool {
	for _, line := range strings.Split(plan, "\n") {
		if strings.Contains(line, "SEARCH") && lineRefersToMetrics(line) && strings.Contains(line, indexName) {
			return true
		}
	}
	return false
}

// containsFullTableScan returns true if the plan contains a full table scan
// on the metrics table — "SCAN TABLE metrics" or "SCAN metrics" without a
// "USING ... INDEX" qualifier. A covering-index scan is not a full table scan.
func containsFullTableScan(plan string) bool {
	for _, line := range strings.Split(plan, "\n") {
		if !strings.Contains(line, "SCAN") || !lineRefersToMetrics(line) {
			continue
		}
		// A covering-index scan is acceptable — it reads only the index.
		if strings.Contains(line, "USING") && strings.Contains(line, "INDEX") {
			continue
		}
		// Bare SCAN TABLE metrics or SCAN metrics without index = full scan.
		return true
	}
	return false
}

// containsCoveringIndexScan returns true if any plan line shows a covering-
// index scan on the metrics table (e.g., "SCAN metrics USING COVERING INDEX ...").
func containsCoveringIndexScan(plan string) bool {
	for _, line := range strings.Split(plan, "\n") {
		if strings.Contains(line, "SCAN") && lineRefersToMetrics(line) && strings.Contains(line, "COVERING INDEX") {
			return true
		}
	}
	return false
}

// containsIndexScan returns true if any plan line shows an index-ordered scan
// on the metrics table (e.g., "SCAN metrics USING INDEX idx_metrics_query_all").
// This differs from a covering-index scan in that it reads table rows via the
// index, but still avoids a full table scan.
func containsIndexScan(plan string) bool {
	for _, line := range strings.Split(plan, "\n") {
		if strings.Contains(line, "SCAN") && lineRefersToMetrics(line) && strings.Contains(line, "USING") && strings.Contains(line, "INDEX") {
			return true
		}
	}
	return false
}

// newPlanTestDB creates an in-memory SQLite database with the metrics store
// schema. It uses the same schema and indexes as store.initSchema().
func newPlanTestDB(t *testing.T) *sql.DB {
	t.Helper()

	dsn := ":memory:?" + url.Values{
		"_pragma": []string{
			"busy_timeout(5000)",
			"journal_mode(WAL)",
		},
	}.Encode()

	db, err := sql.Open("sqlite", dsn)
	if err != nil {
		t.Fatalf("open in-memory db: %v", err)
	}
	// Pin to a single connection — a second connection to :memory: would get
	// a different database, causing "no such table" failures.
	db.SetMaxOpenConns(1)
	t.Cleanup(func() { db.Close() })

	schema := `
		CREATE TABLE IF NOT EXISTS metrics (
			id INTEGER PRIMARY KEY AUTOINCREMENT,
			resource_type TEXT NOT NULL,
			resource_id TEXT NOT NULL,
			metric_type TEXT NOT NULL,
			value REAL NOT NULL,
			min_value REAL,
			max_value REAL,
			timestamp INTEGER NOT NULL,
			tier TEXT NOT NULL DEFAULT 'raw'
		);

		CREATE INDEX IF NOT EXISTS idx_metrics_tier_time
		ON metrics(tier, timestamp);

		CREATE UNIQUE INDEX IF NOT EXISTS idx_metrics_query_all
		ON metrics(resource_type, resource_id, tier, timestamp, metric_type);

		CREATE TABLE IF NOT EXISTS metrics_meta (
			key TEXT PRIMARY KEY,
			value TEXT NOT NULL
		);

		CREATE TABLE IF NOT EXISTS availability_observations (
			id INTEGER PRIMARY KEY AUTOINCREMENT,
			observation_id TEXT NOT NULL UNIQUE,
			target_id TEXT NOT NULL,
			config_revision INTEGER NOT NULL,
			outcome TEXT NOT NULL,
			observed_at_ns INTEGER NOT NULL,
			timeline_at_ns INTEGER NOT NULL,
			ingested_at_ns INTEGER NOT NULL,
			valid_until_ns INTEGER NOT NULL,
			execution_source TEXT NOT NULL,
			latency_millis INTEGER
		);
		CREATE INDEX IF NOT EXISTS idx_availability_observations_target_timeline
			ON availability_observations(target_id, timeline_at_ns, id);
		CREATE INDEX IF NOT EXISTS idx_availability_observations_retention
			ON availability_observations(timeline_at_ns);

		CREATE TABLE IF NOT EXISTS availability_history_buckets (
			target_id TEXT NOT NULL,
			tier TEXT NOT NULL,
			bucket_start INTEGER NOT NULL,
			reachable_millis INTEGER NOT NULL DEFAULT 0,
			unreachable_millis INTEGER NOT NULL DEFAULT 0,
			indeterminate_millis INTEGER NOT NULL DEFAULT 0,
			reachable_count INTEGER NOT NULL DEFAULT 0,
			unreachable_count INTEGER NOT NULL DEFAULT 0,
			indeterminate_count INTEGER NOT NULL DEFAULT 0,
			latency_count INTEGER NOT NULL DEFAULT 0,
			latency_sum INTEGER NOT NULL DEFAULT 0,
			latency_min INTEGER,
			latency_max INTEGER,
			PRIMARY KEY(target_id, tier, bucket_start)
		);
		CREATE INDEX IF NOT EXISTS idx_availability_buckets_retention
			ON availability_history_buckets(tier, bucket_start);

		CREATE TABLE IF NOT EXISTS availability_revision_boundaries (
			target_id TEXT NOT NULL,
			revision INTEGER NOT NULL,
			started_at_ns INTEGER NOT NULL,
			PRIMARY KEY(target_id, revision)
		);
		CREATE INDEX IF NOT EXISTS idx_availability_boundaries_target_time
			ON availability_revision_boundaries(target_id, started_at_ns);
	`
	if _, err := db.Exec(schema); err != nil {
		t.Fatalf("create schema: %v", err)
	}

	// Insert a small amount of seed data so the query planner has statistics
	// to reason about. Without data, SQLite may choose different plans.
	for i := 0; i < 100; i++ {
		_, err := db.Exec(
			`INSERT INTO metrics (resource_type, resource_id, metric_type, value, timestamp, tier) VALUES (?, ?, ?, ?, ?, ?)`,
			"vm", fmt.Sprintf("vm-%d", i%10), "cpu", float64(i), int64(1000000+i*5), "raw",
		)
		if err != nil {
			t.Fatalf("seed data: %v", err)
		}

		targetID := fmt.Sprintf("target-%d", i%10)
		_, err = db.Exec(`INSERT INTO availability_observations
			(observation_id, target_id, config_revision, outcome, observed_at_ns, timeline_at_ns,
			 ingested_at_ns, valid_until_ns, execution_source, latency_millis)
			VALUES (?, ?, 1, 'reachable', ?, ?, ?, ?, 'local', 12)`,
			fmt.Sprintf("observation-%d", i), targetID, int64(1000000+i*5), int64(1000000+i*5),
			int64(1000000+i*5), int64(1000060+i*5))
		if err != nil {
			t.Fatalf("seed availability observations: %v", err)
		}
		_, err = db.Exec(`INSERT OR IGNORE INTO availability_history_buckets
			(target_id, tier, bucket_start, reachable_millis, latency_count, latency_sum, latency_min, latency_max)
			VALUES (?, 'minute', ?, 60000, 1, 12, 12, 12)`, targetID, int64(1000000+i*60))
		if err != nil {
			t.Fatalf("seed availability buckets: %v", err)
		}
		_, err = db.Exec(`INSERT OR IGNORE INTO availability_revision_boundaries
			(target_id, revision, started_at_ns) VALUES (?, 1, ?)`, targetID, int64(1000000+i*5))
		if err != nil {
			t.Fatalf("seed availability boundaries: %v", err)
		}
	}

	// Run ANALYZE so the planner has real statistics.
	if _, err := db.Exec("ANALYZE"); err != nil {
		t.Fatalf("analyze: %v", err)
	}

	return db
}

// explainQueryPlan runs EXPLAIN QUERY PLAN on the given query and returns
// the full plan output as a single string.
func explainQueryPlan(t *testing.T, db *sql.DB, query string, args []any) string {
	t.Helper()

	rows, err := db.Query("EXPLAIN QUERY PLAN "+query, args...)
	if err != nil {
		t.Fatalf("EXPLAIN QUERY PLAN: %v\nQuery: %s", err, query)
	}
	defer rows.Close()

	var lines []string
	for rows.Next() {
		var id, parent, notused int
		var detail string
		if err := rows.Scan(&id, &parent, &notused, &detail); err != nil {
			t.Fatalf("scan explain row: %v", err)
		}
		lines = append(lines, detail)
	}
	if err := rows.Err(); err != nil {
		t.Fatalf("explain rows: %v", err)
	}

	return strings.Join(lines, "\n")
}

func TestRetainedUnaggregatedQueryAvoidsMetricSort(t *testing.T) {
	db := newPlanTestDB(t)
	store := &Store{}
	end := time.Unix(2000000040, 0)
	for _, valuesOnly := range []bool{false, true} {
		for _, window := range []time.Duration{time.Hour, 24 * time.Hour, 7 * 24 * time.Hour, 30 * 24 * time.Hour} {
			for _, ids := range [][]string{{"a"}, {"a", "b"}} {
				for _, metrics := range [][]string{nil, {"cpu"}, {"cpu", "memory"}} {
					t.Run(fmt.Sprintf("values_%t/%s/resources_%d/metrics_%d", valuesOnly, window, len(ids), len(metrics)), func(t *testing.T) {
						query, args := retainedQuerySQL("node", ids, metrics, end.Add(-window), end, 0, store.tierFallbacks(window), false, valuesOnly)
						plan := explainQueryPlan(t, db, query, args)
						for _, line := range strings.Split(plan, "\n") {
							if strings.Contains(strings.ToUpper(line), "TEMP B-TREE") {
								t.Fatalf("unaggregated read sorts despite time-ordered index: %s", plan)
							}
							if len(ids) > 1 && strings.Contains(line, "MERGE (UNION ALL)") {
								t.Fatalf("raw fleet read merges repeated identity strings across tiers: %s", plan)
							}
						}
					})
				}
			}
		}
	}
}

func TestRetainedResourceNumbersPreserveScopeAndOrderedSeries(t *testing.T) {
	for _, clustered := range []bool{false, true} {
		t.Run(fmt.Sprintf("clustered_%t", clustered), func(t *testing.T) {
			db := newPlanTestDB(t)
			if clustered {
				if _, err := db.Exec(`DROP TABLE metrics; CREATE TABLE metrics (` + metricsTableColumns + `) WITHOUT ROWID;
					CREATE INDEX idx_metrics_tier_time ON metrics(tier,timestamp)`); err != nil {
					t.Fatal(err)
				}
			}
			store := &Store{db: pdb.Wrap(db, "retained-resource-number-test")}
			store.clusteredMetrics.Store(clustered)
			start := time.Unix(1_800_003_600, 0)
			end := start.Add(3 * time.Hour)
			ids := make([]string, queryAllBatchChunkSize+3)
			tx, err := db.Begin()
			if err != nil {
				t.Fatal(err)
			}
			insert, err := tx.Prepare(`INSERT INTO metrics(resource_type,resource_id,metric_type,value,min_value,max_value,timestamp,tier) VALUES (?,?,?,?,?,?,?,?)`)
			if err != nil {
				t.Fatal(err)
			}
			for i := range ids {
				// Deliberately reverse lexical order, including IDs beyond the
				// chunk boundary. A resource number belongs only to its statement.
				ids[i] = fmt.Sprintf("guest-'-%04d", len(ids)-i)
				for _, metric := range []string{"cpu", "memory", "unrequested"} {
					for _, row := range []struct {
						offset    int64
						tier      Tier
						value     float64
						low, high any
					}{
						{60, TierMinute, 10, float64(0), float64(20)},
						{65, TierRaw, 999, nil, nil}, // owned by the minute bucket
						{180, TierRaw, 30, nil, nil},
						{300, TierMinute, 50, nil, nil},
						{15, TierRaw, 5, nil, nil}, // emitted after minute, must sort
						{0, TierHourly, -100, nil, nil},
						{-1, TierRaw, -999, nil, nil},
						{3*3600 + 1, TierRaw, -999, nil, nil},
					} {
						if _, err := insert.Exec("vm", ids[i], metric, row.value+float64(i), row.low, row.high, start.Unix()+row.offset, string(row.tier)); err != nil {
							t.Fatal(err)
						}
					}
				}
				if _, err := insert.Exec("container", ids[i], "cpu", 9999, nil, nil, start.Unix()+60, string(TierMinute)); err != nil {
					t.Fatal(err)
				}
			}
			if _, err := insert.Exec("vm", "outside-scope", "cpu", 9999, nil, nil, start.Unix()+60, string(TierMinute)); err != nil {
				t.Fatal(err)
			}
			if err := insert.Close(); err != nil {
				t.Fatal(err)
			}
			if err := tx.Commit(); err != nil {
				t.Fatal(err)
			}

			check := func(requested, filters []string, step int64) {
				t.Helper()
				full, err := store.QueryMetricTypesBatch("vm", requested, filters, start, end, step)
				if err != nil {
					t.Fatal(err)
				}
				values, err := store.QueryMetricValuesBatch("vm", requested, filters, start, end, step)
				if err != nil {
					t.Fatal(err)
				}
				assertMetricValueProjection(t, full, values)
				seen := make(map[string]bool)
				for _, id := range requested {
					id = normalizeMetricIdentifier(id)
					if id == "" || seen[id] {
						continue
					}
					seen[id] = true
					one, err := store.QueryMetricTypesBatch("vm", []string{id}, filters, start, end, step)
					if err != nil {
						t.Fatal(err)
					}
					if !reflect.DeepEqual(full[id], one[id]) {
						t.Fatalf("scope/order changed for %q step=%d: batch=%+v single=%+v", id, step, full[id], one[id])
					}
				}
				if _, exists := full["outside-scope"]; exists {
					t.Fatal("unrequested resource returned")
				}
				if len(full) != len(seen)-1 { // one requested ID is deliberately absent
					t.Fatalf("resource count=%d requested distinct=%d", len(full), len(seen))
				}
			}
			for _, filters := range [][]string{nil, {"cpu"}, {"memory", "cpu", "absent"}} {
				for _, step := range []int64{0, 1, 60} {
					check([]string{ids[2], ids[0], ids[1], ids[2], "", "missing"}, filters, step)
					check([]string{ids[1], ids[2], ids[0], "missing"}, filters, step)
				}
			}
			check(append(append([]string{}, ids...), "missing", ids[0]), nil, 0)
			check(append(append([]string{}, ids...), "missing", ids[0]), []string{"memory", "cpu", "absent"}, 0)
		})
	}
}

// Filtered raw fleets bind both dimensions already. The row boundary should
// transport one series ordinal, while a cache hit with reordered metric names
// must still resolve against this statement's current bindings.
func TestRetainedRawFleetSeriesNumbersBindCurrentMetrics(t *testing.T) {
	for _, clustered := range []bool{false, true} {
		for _, valuesOnly := range []bool{false, true} {
			t.Run(fmt.Sprintf("clustered=%t/values=%t", clustered, valuesOnly), func(t *testing.T) {
				db := newPlanTestDB(t)
				if clustered {
					if _, err := db.Exec(`DROP TABLE metrics; CREATE TABLE metrics (` + metricsTableColumns + `) WITHOUT ROWID; CREATE INDEX idx_metrics_tier_time ON metrics(tier,timestamp)`); err != nil {
						t.Fatal(err)
					}
				}
				store := &Store{db: pdb.Wrap(db, "series-number-binding")}
				store.clusteredMetrics.Store(clustered)
				base := time.Unix(1_800_003_600, 0)
				ids := []string{"b':p2", "a:p4"}
				names := []string{"memory", "cpu", ":p4"}
				for i, id := range ids {
					for j, metric := range names {
						if _, err := db.Exec(`INSERT INTO metrics(resource_type,resource_id,metric_type,tier,timestamp,value) VALUES ('node',?,?,'minute',?,?)`, id, metric, base.Unix(), float64(i*100+j)); err != nil {
							t.Fatal(err)
						}
					}
				}
				for _, filters := range [][]string{names, {":p4", "memory", "cpu"}, {"cpu", "memory", ":p4"}} {
					query, args := store.retainedQuerySQL("node", ids, filters, base.Add(-3*time.Hour), base.Add(time.Minute), 0, []Tier{TierMinute}, false, valuesOnly)
					rows, err := db.Query(query, args...)
					if err != nil {
						t.Fatal(err)
					}
					columns, err := rows.Columns()
					if err != nil {
						t.Fatal(err)
					}
					rows.Close()
					want := []string{"series_number", "timestamp", "value"}
					if valuesOnly {
						want = []string{"series_sample", "value"}
					} else {
						want = append(want, "min_value", "max_value")
					}
					if !reflect.DeepEqual(columns, want) {
						t.Fatalf("repeated series identity still crosses row boundary: got %v, want %v", columns, want)
					}
					full, err := store.QueryMetricTypesBatch("node", ids, filters, base.Add(-3*time.Hour), base.Add(time.Minute), 0)
					if err != nil {
						t.Fatal(err)
					}
					values, err := store.QueryMetricValuesBatch("node", ids, filters, base.Add(-3*time.Hour), base.Add(time.Minute), 0)
					if err != nil {
						t.Fatal(err)
					}
					assertMetricValueProjection(t, full, values)
					for i, id := range ids {
						for j, name := range names {
							points := full[id][name]
							if len(points) != 1 || points[0].Value != float64(i*100+j) || !points[0].Timestamp.Equal(base) {
								t.Fatalf("wrong current series for %q/%q: %+v", id, name, points)
							}
						}
					}
				}
			})
		}
	}
}

func TestRetainedPackedSeriesKeepsNegativeTimestampsAndOverflowFallback(t *testing.T) {
	db := newPlanTestDB(t)
	store := &Store{db: pdb.Wrap(db, "packed-series-boundary")}
	ids, names := []string{"second", "first"}, []string{"memory", "cpu", "disk"}
	const width int64 = 6
	const maximum = int64(^uint64(0) >> 1)
	const minimum = -maximum - 1
	for _, tc := range []struct {
		name   string
		start  int64
		packed bool
	}{
		{"negative epoch", -100, true},
		{"ordinary epoch", 1_800_000_000, true},
		{"near positive bound", (maximum-width+1)/width - 2, true},
		{"positive overflow", (maximum - width + 1) / width, false},
		{"near negative bound", minimum / width, true},
		{"negative overflow", minimum/width - 1, false},
	} {
		t.Run(tc.name, func(t *testing.T) {
			start, end := time.Unix(tc.start, 0), time.Unix(tc.start+2, 0)
			if got := retainedQueryUsesPackedSeries(len(ids), len(names), start, end, 0, false, true); got != tc.packed {
				t.Fatalf("packed=%t want %t", got, tc.packed)
			}
			for i, id := range ids {
				for j, metric := range names {
					for offset := int64(0); offset <= 2; offset++ {
						if _, err := db.Exec(`INSERT OR REPLACE INTO metrics(resource_type,resource_id,metric_type,tier,timestamp,value) VALUES ('node',?,?,'raw',?,?)`, id, metric, tc.start+offset, float64(i*100+j*10)+float64(offset)/3); err != nil {
							t.Fatal(err)
						}
					}
				}
			}
			// Reuse one Store and equal parameter counts across packed and
			// overflow windows. Cached SQL must still choose the correct shape.
			full, err := store.QueryMetricTypesBatch("node", ids, names, start, end, 0)
			if err != nil {
				t.Fatal(err)
			}
			values, err := store.QueryMetricValuesBatch("node", ids, names, start, end, 0)
			if err != nil {
				t.Fatal(err)
			}
			assertMetricValueProjection(t, full, values)
			for _, id := range ids {
				for _, name := range names {
					points := values[id][name]
					if len(points) != 3 {
						t.Fatalf("lost observations for %s/%s: %+v", id, name, points)
					}
					for i, point := range points {
						if point.Timestamp.Unix() != tc.start+int64(i) {
							t.Fatalf("moved timestamp: %v", point.Timestamp.Unix())
						}
					}
				}
			}
		})
	}
}

// Direct ordinal accumulation must be indistinguishable from the existing
// single-resource assembler, including interleaved rows and empty series. Wide
// sparse filters exercise the bounded fallback without losing statement scope.
func TestRetainedNumberedAssemblyMatchesSingleResourceReads(t *testing.T) {
	for _, clustered := range []bool{false, true} {
		t.Run(fmt.Sprintf("clustered=%t", clustered), func(t *testing.T) {
			db := newPlanTestDB(t)
			if clustered {
				if _, err := db.Exec(`DROP TABLE metrics; CREATE TABLE metrics (` + metricsTableColumns + `) WITHOUT ROWID; CREATE INDEX idx_metrics_tier_time ON metrics(tier,timestamp)`); err != nil {
					t.Fatal(err)
				}
			}
			store := &Store{db: pdb.Wrap(db, "numbered-series-assembly")}
			store.clusteredMetrics.Store(clustered)
			base := time.Unix(1_800_003_600, 0)
			end := base.Add(6 * time.Hour)
			ids := []string{"b':p2", "a:p4", "missing"}
			names := []string{"memory", "cpu", "netin", "absent"}
			tx, err := db.Begin()
			if err != nil {
				t.Fatal(err)
			}
			insert, err := tx.Prepare(`INSERT INTO metrics(resource_type,resource_id,metric_type,tier,timestamp,value,min_value,max_value) VALUES ('node',?,?,?,?,?,?,?)`)
			if err != nil {
				t.Fatal(err)
			}
			for i, id := range ids[:2] {
				for j, name := range names[:3] {
					// Minute-tier rows come first; the raw first/tail points must
					// still be restored to chronological order, never averaged.
					for p := 0; p < 300; p++ {
						value := float64(i*1000 + j*300 + p)
						if _, err := insert.Exec(id, name, "minute", base.Unix()+int64(p*60+60), value, value-7, value+9); err != nil {
							t.Fatal(err)
						}
					}
					for _, p := range []struct{ offset, value int64 }{{1, 12}, {65, 99999}, {18061, 0}} {
						if _, err := insert.Exec(id, name, "raw", base.Unix()+p.offset, p.value, nil, nil); err != nil {
							t.Fatal(err)
						}
					}
				}
			}
			insert.Close()
			if err := tx.Commit(); err != nil {
				t.Fatal(err)
			}
			for _, shape := range []string{"compact", "rebound", "wide-sparse"} {
				t.Run(shape, func(t *testing.T) {
					requested := append([]string(nil), ids...)
					filters := append([]string(nil), names...)
					if shape == "rebound" {
						requested[0], requested[1] = requested[1], requested[0]
						filters[0], filters[2] = filters[2], filters[0]
					}
					if shape == "wide-sparse" {
						for len(requested) < queryAllBatchChunkSize {
							requested = append(requested, fmt.Sprintf("missing-%d", len(requested)))
						}
						for len(filters) <= maxRetainedNumberedSeries/queryAllBatchChunkSize {
							filters = append(filters, fmt.Sprintf("absent-%d", len(filters)))
						}
					}
					full, err := store.QueryMetricTypesBatch("node", requested, filters, base, end, 0)
					if err != nil {
						t.Fatal(err)
					}
					if len(full) != 2 {
						t.Fatalf("empty resource materialised: %d resources", len(full))
					}
					for _, id := range ids[:2] {
						single, err := store.QueryMetricTypesBatch("node", []string{id}, filters, base, end, 0)
						if err != nil {
							t.Fatal(err)
						}
						if !reflect.DeepEqual(full[id], single[id]) {
							t.Fatalf("fleet differs from single-resource read for %s", id)
						}
						for _, points := range full[id] {
							if len(points) != 302 || points[0].Value != 12 || points[len(points)-1].Value != 0 || points[len(points)-1].Min != 0 || points[1].Min != points[1].Value-7 {
								t.Fatalf("chronology/zero/extrema/overlap changed: %d points", len(points))
							}
						}
					}
					values, err := store.QueryMetricValuesBatch("node", requested, filters, base, end, 0)
					if err != nil {
						t.Fatal(err)
					}
					assertMetricValueProjection(t, full, values)
					full[ids[0]][names[0]][0].Value = -999
					if values[ids[0]][names[0]][0].Value != 12 {
						t.Fatal("reader outputs alias each other")
					}
				})
			}
		})
	}
}