Skip to content

Database governance built for humans and agents — controlling changes and access across every major database.

19.8 KB
Raw Blame
package gonative_test

import (
	"encoding/json"
	"fmt"
	"os"
	"path/filepath"
	"slices"
	"strings"
	"testing"

	"github.com/Azure/azure-sdk-for-go/sdk/data/azcosmos/queryengine"
	"github.com/stretchr/testify/assert"
	"github.com/stretchr/testify/require"

	"github.com/bytebase/bytebase/backend/plugin/db/cosmosdb/queryengine/gonative"
)

// mustReadFile loads a fixture path and fails the test on error.
func mustReadFile(t *testing.T, path string) []byte {
	t.Helper()
	b, err := os.ReadFile(path)
	require.NoError(t, err, "read %s", path)
	return b
}

// partitionBodies returns a map of pk-range-id -> response body for each file
// found under `dir`. Expects filenames of the form `range_<id>.json`.
func partitionBodies(t *testing.T, dir string) map[string][]byte {
	t.Helper()
	entries, err := os.ReadDir(dir)
	require.NoError(t, err, "read dir %s", dir)
	out := make(map[string][]byte, len(entries))
	for _, e := range entries {
		name := e.Name()
		// file is `range_<id>.json` — strip prefix/suffix
		id := name[len("range_") : len(name)-len(".json")]
		out[id] = mustReadFile(t, filepath.Join(dir, name))
	}
	return out
}

// driveAggPipeline runs a pipeline end-to-end against captured fixtures and
// returns the single emitted row.
func driveAggPipeline(t *testing.T, queryID, originalQuery string) json.RawMessage {
	t.Helper()
	base := filepath.Join("testdata", "query_"+queryID)
	plan := mustReadFile(t, base+"_plan.json")
	pkranges := mustReadFile(t, base+"_pkranges.json")
	bodies := partitionBodies(t, base+"_partitions")

	engine := gonative.Default()
	pipe, err := engine.CreateQueryPipeline(originalQuery, string(plan), string(pkranges))
	require.NoError(t, err, "CreateQueryPipeline")
	defer pipe.Close()

	var emitted json.RawMessage
	for i := 0; i < 10 && !pipe.IsComplete(); i++ { // bounded loop — 10 turns is a safe upper bound for single-partition aggregates
		res, err := pipe.Run()
		require.NoError(t, err, "Run turn %d", i)
		if len(res.Requests) > 0 {
			results := make([]queryengine.QueryResult, 0, len(res.Requests))
			for _, req := range res.Requests {
				body, ok := bodies[req.PartitionKeyRangeID]
				require.True(t, ok, "missing fixture for pk-range %s", req.PartitionKeyRangeID)
				results = append(results, queryengine.NewQueryResult(req.PartitionKeyRangeID, body, ""))
			}
			require.NoError(t, pipe.ProvideData(results))
		}
		if len(res.Items) > 0 {
			require.Len(t, res.Items, 1, "aggregate query emits exactly one row")
			emitted = append(json.RawMessage(nil), res.Items[0]...)
		}
	}
	require.True(t, pipe.IsComplete(), "pipeline did not converge")
	require.NotNil(t, emitted, "no row emitted")
	return emitted
}

func TestAggPipeline_5_1_AliasedCount(t *testing.T) {
	row := driveAggPipeline(t, "5_1", `SELECT COUNT(1) AS totalRecords FROM c`)
	var obj map[string]any
	require.NoError(t, json.Unmarshal(row, &obj))
	assert.EqualValues(t, 18, obj["totalRecords"])
}

func TestAggPipeline_5_1b_ValueCount(t *testing.T) {
	row := driveAggPipeline(t, "5_1b", `SELECT VALUE COUNT(1) FROM c`)
	// VALUE form emits the scalar directly.
	var n float64
	require.NoError(t, json.Unmarshal(row, &n))
	assert.EqualValues(t, 18, n)
}

func TestAggPipeline_5_2_AliasedCountWithFilter(t *testing.T) {
	row := driveAggPipeline(t, "5_2", `SELECT COUNT(1) AS aeCount FROM c WHERE c.country = "AE"`)
	var obj map[string]any
	require.NoError(t, json.Unmarshal(row, &obj))
	assert.EqualValues(t, 5, obj["aeCount"])
}

func TestAggPipeline_5_2b_ValueCountWithFilter(t *testing.T) {
	row := driveAggPipeline(t, "5_2b", `SELECT VALUE COUNT(1) FROM c WHERE c.country = "AE"`)
	var n float64
	require.NoError(t, json.Unmarshal(row, &n))
	assert.EqualValues(t, 5, n)
}

func TestAggPipeline_5_3_AliasedSum(t *testing.T) {
	row := driveAggPipeline(t, "5_3", `SELECT SUM(StringToNumber(c.population)) AS totalPop FROM c`)
	var obj map[string]any
	require.NoError(t, json.Unmarshal(row, &obj))
	assert.Positive(t, obj["totalPop"])
}

func TestAggPipeline_5_3b_ValueSum(t *testing.T) {
	row := driveAggPipeline(t, "5_3b", `SELECT VALUE SUM(StringToNumber(c.population)) FROM c`)
	var n float64
	require.NoError(t, json.Unmarshal(row, &n))
	assert.Positive(t, n)
}

func TestAggPipeline_5_4_AliasedAvg(t *testing.T) {
	row := driveAggPipeline(t, "5_4", `SELECT AVG(StringToNumber(c.population)) AS avgPop FROM c`)
	var obj map[string]any
	require.NoError(t, json.Unmarshal(row, &obj))
	assert.Positive(t, obj["avgPop"])
}

func TestAggPipeline_5_4b_ValueAvg(t *testing.T) {
	row := driveAggPipeline(t, "5_4b", `SELECT VALUE AVG(StringToNumber(c.population)) FROM c`)
	var n float64
	require.NoError(t, json.Unmarshal(row, &n))
	assert.Positive(t, n)
}

func TestAggPipeline_5_5_MultiAggMinMax(t *testing.T) {
	row := driveAggPipeline(t, "5_5", `SELECT MIN(StringToNumber(c.population)) AS minPop, MAX(StringToNumber(c.population)) AS maxPop FROM c WHERE StringToNumber(c.population) > 0`)
	// Aliased multi-agg preserves alias order from the plan.
	assert.Contains(t, string(row), `"minPop"`)
	assert.Contains(t, string(row), `"maxPop"`)
	// Order assertion: minPop appears before maxPop because the plan lists it first.
	idxMin := indexOf(row, []byte(`"minPop"`))
	idxMax := indexOf(row, []byte(`"maxPop"`))
	assert.Less(t, idxMin, idxMax, "aliases emitted in plan order")

	var obj map[string]any
	require.NoError(t, json.Unmarshal(row, &obj))
	minV, ok := obj["minPop"].(float64)
	require.True(t, ok)
	maxV, ok := obj["maxPop"].(float64)
	require.True(t, ok)
	assert.LessOrEqual(t, minV, maxV)
}

// driveAggPagedPipeline is driveAggPipeline for a range that answers over
// several pages: it replays each captured partition body `pages` times, every
// page but the last carrying a continuation token, and returns the single
// emitted row. An aggregate folds one partial per page, so the result is the
// captured partial counted `pages` times over.
func driveAggPagedPipeline(t *testing.T, queryID, originalQuery string, pages int) json.RawMessage {
	t.Helper()
	base := filepath.Join("testdata", "query_"+queryID)
	plan := mustReadFile(t, base+"_plan.json")
	pkranges := mustReadFile(t, base+"_pkranges.json")
	bodies := partitionBodies(t, base+"_partitions")

	engine := gonative.Default()
	pipe, err := engine.CreateQueryPipeline(originalQuery, string(plan), string(pkranges))
	require.NoError(t, err, "CreateQueryPipeline")
	defer pipe.Close()

	served := make(map[string]int, len(bodies))
	var emitted json.RawMessage
	for i := 0; i < 10*pages && !pipe.IsComplete(); i++ {
		res, err := pipe.Run()
		require.NoError(t, err, "Run turn %d", i)
		if len(res.Requests) > 0 {
			results := make([]queryengine.QueryResult, 0, len(res.Requests))
			for _, req := range res.Requests {
				body, ok := bodies[req.PartitionKeyRangeID]
				require.True(t, ok, "missing fixture for pk-range %s", req.PartitionKeyRangeID)
				served[req.PartitionKeyRangeID]++
				continuation := ""
				if served[req.PartitionKeyRangeID] < pages {
					continuation = fmt.Sprintf("%s-page-%d", req.PartitionKeyRangeID, served[req.PartitionKeyRangeID])
				} else {
					require.Equal(t, pages, served[req.PartitionKeyRangeID],
						"pk-range %s was asked for a page past the script", req.PartitionKeyRangeID)
				}
				results = append(results, queryengine.NewQueryResult(req.PartitionKeyRangeID, body, continuation))
			}
			require.NoError(t, pipe.ProvideData(results))
		}
		if len(res.Items) > 0 {
			require.Len(t, res.Items, 1, "aggregate query emits exactly one row")
			emitted = append(json.RawMessage(nil), res.Items[0]...)
		}
	}
	require.True(t, pipe.IsComplete(), "pipeline did not converge")
	require.NotNil(t, emitted, "no row emitted")
	for id, pagesServed := range served {
		assert.Equal(t, pages, pagesServed, "pk-range %s must be read to its last page", id)
	}
	return emitted
}

func TestAggPipeline_ValueCountFoldsPartialsAcrossPages(t *testing.T) {
	row := driveAggPagedPipeline(t, "5_1b", `SELECT VALUE COUNT(1) FROM c`, 3)
	var n float64
	require.NoError(t, json.Unmarshal(row, &n))
	assert.EqualValues(t, 54, n, "three pages of the 18-row partial")
}

func TestAggPipeline_AliasedCountFoldsPartialsAcrossPages(t *testing.T) {
	row := driveAggPagedPipeline(t, "5_1", `SELECT COUNT(1) AS totalRecords FROM c`, 2)
	var obj map[string]any
	require.NoError(t, json.Unmarshal(row, &obj))
	assert.EqualValues(t, 36, obj["totalRecords"], "two pages of the 18-row partial")
}

// drivePipelineAll runs a pipeline end-to-end against captured fixtures and
// returns every emitted row. Used for operators that emit more than one row
// (DISTINCT, ORDER BY, TOP, OFFSET/LIMIT, GROUP BY).
func drivePipelineAll(t *testing.T, queryID, originalQuery string) []json.RawMessage {
	t.Helper()
	base := filepath.Join("testdata", "query_"+queryID)
	plan := mustReadFile(t, base+"_plan.json")
	pkranges := mustReadFile(t, base+"_pkranges.json")
	bodies := partitionBodies(t, base+"_partitions")

	engine := gonative.Default()
	pipe, err := engine.CreateQueryPipeline(originalQuery, string(plan), string(pkranges))
	require.NoError(t, err, "CreateQueryPipeline")
	defer pipe.Close()

	var rows []json.RawMessage
	for i := 0; i < 50 && !pipe.IsComplete(); i++ {
		res, err := pipe.Run()
		require.NoError(t, err, "Run turn %d", i)
		if len(res.Requests) > 0 {
			results := make([]queryengine.QueryResult, 0, len(res.Requests))
			for _, req := range res.Requests {
				body, ok := bodies[req.PartitionKeyRangeID]
				require.True(t, ok, "missing fixture for pk-range %s", req.PartitionKeyRangeID)
				results = append(results, queryengine.NewQueryResult(req.PartitionKeyRangeID, body, ""))
			}
			require.NoError(t, pipe.ProvideData(results))
		}
		for _, it := range res.Items {
			rows = append(rows, append(json.RawMessage(nil), it...))
		}
	}
	require.True(t, pipe.IsComplete(), "pipeline did not converge")
	return rows
}

func TestDistinctPipeline_10_1_ObjectDistinct(t *testing.T) {
	rows := drivePipelineAll(t, "10_1", `SELECT DISTINCT c.country FROM c`)
	// Captured fixture has 6 unique countries in a single partition.
	assert.Len(t, rows, 6)
	seen := map[string]bool{}
	for _, r := range rows {
		var obj map[string]any
		require.NoError(t, json.Unmarshal(r, &obj))
		country, ok := obj["country"].(string)
		require.True(t, ok, "each row must carry a country field")
		assert.False(t, seen[country], "duplicate country emitted: %s", country)
		seen[country] = true
	}
}

func TestGroupPipeline_6_1_CountByCountry(t *testing.T) {
	rows := drivePipelineAll(t, "6_1", `SELECT c.country, COUNT(1) AS cityCount FROM c GROUP BY c.country`)
	// Captured fixture covers 6 countries.
	assert.Len(t, rows, 6)
	byCountry := map[string]float64{}
	for _, r := range rows {
		var obj map[string]any
		require.NoError(t, json.Unmarshal(r, &obj))
		country, ok := obj["country"].(string)
		require.True(t, ok, "row missing country: %s", r)
		cnt, ok := obj["cityCount"].(float64)
		require.True(t, ok, "row missing cityCount: %s", r)
		byCountry[country] = cnt
	}
	// AE has 5 cities in the test container (from Stage 1 captures).
	assert.EqualValues(t, 5, byCountry["AE"])
	// Verify sum of group counts equals the total row count (18).
	var total float64
	for _, n := range byCountry {
		total += n
	}
	assert.Equal(t, float64(18), total)
}

func TestGroupPipeline_6_2_MultipleAggregates(t *testing.T) {
	rows := drivePipelineAll(t, "6_2", `SELECT c.countryRegion, COUNT(1) AS count, SUM(StringToNumber(c.population)) AS totalPop FROM c GROUP BY c.countryRegion`)
	assert.NotEmpty(t, rows)
	// Each row must carry all three aliases and their values must be numeric
	// (count) / numeric (totalPop) / string (countryRegion).
	var totalCount float64
	for _, r := range rows {
		var obj map[string]any
		require.NoError(t, json.Unmarshal(r, &obj))
		_, ok := obj["countryRegion"].(string)
		assert.True(t, ok, "countryRegion should be a string: %s", r)
		cnt, ok := obj["count"].(float64)
		require.True(t, ok, "count should be numeric: %s", r)
		_, ok = obj["totalPop"].(float64)
		assert.True(t, ok, "totalPop should be numeric: %s", r)
		totalCount += cnt
	}
	assert.Equal(t, float64(18), totalCount, "sum of per-region counts must equal total row count")
}

func TestOrderByPipeline_12_1_OffsetZeroLimitTen(t *testing.T) {
	rows := drivePipelineAll(t, "12_1", `SELECT * FROM c ORDER BY c.name OFFSET 0 LIMIT 10`)
	assert.Len(t, rows, 10, "OFFSET 0 LIMIT 10 must emit 10 rows")
	// Rows should be in ascending order by c.name.
	var prevName string
	for i, r := range rows {
		var obj map[string]any
		require.NoError(t, json.Unmarshal(r, &obj), "row %d: %s", i, r)
		name, ok := obj["name"].(string)
		require.True(t, ok, "name must be a string: %s", r)
		if i > 0 {
			assert.LessOrEqual(t, prevName, name, "ascending order broken at row %d", i)
		}
		prevName = name
	}
}

func TestOrderByPipeline_12_2_OffsetTenLimitTen(t *testing.T) {
	rows := drivePipelineAll(t, "12_2", `SELECT * FROM c ORDER BY c.name OFFSET 10 LIMIT 10`)
	// WorldCities has 18 rows total; OFFSET 10 LIMIT 10 returns rows 11–18 (8 rows).
	assert.Len(t, rows, 8)
	var prevName string
	for i, r := range rows {
		var obj map[string]any
		require.NoError(t, json.Unmarshal(r, &obj))
		name, ok := obj["name"].(string)
		require.True(t, ok, "name must be a string: %s", r)
		if i > 0 {
			assert.LessOrEqual(t, prevName, name)
		}
		prevName = name
	}
}

func TestTopPipeline_1_3_TopTen(t *testing.T) {
	rows := drivePipelineAll(t, "1_3", `SELECT TOP 10 * FROM c`)
	assert.Len(t, rows, 10, "TOP 10 must emit exactly 10 rows")
	// Every row should be a valid object (not the payload-wrapped form).
	for i, r := range rows {
		var obj map[string]any
		require.NoError(t, json.Unmarshal(r, &obj), "row %d: %s", i, r)
		assert.NotEmpty(t, obj, "row %d should be a non-empty object", i)
	}
}

func TestOrderByPipeline_4_1_Ascending(t *testing.T) {
	rows := drivePipelineAll(t, "4_1", `SELECT c.name, c.population FROM c ORDER BY c.population ASC`)
	// Captured fixture: 18 cities in the WorldCities test container, mixed
	// numeric + string populations (the gateway delivered them pre-sorted).
	assert.Len(t, rows, 18, "expected one row per city")
	// First row should be the smallest population (number type comes before
	// string under Cosmos ordering, so this is a numeric).
	var first map[string]any
	require.NoError(t, json.Unmarshal(rows[0], &first))
	_, ok := first["population"].(float64)
	assert.True(t, ok, "first row under ASC ordering must be numeric population (numbers sort before strings)")
}

func TestOrderByPipeline_4_2_Descending(t *testing.T) {
	rows := drivePipelineAll(t, "4_2", `SELECT c.name, c.population FROM c ORDER BY c.population DESC`)
	assert.Len(t, rows, 18)
	// DESC: strings come first (strings > numbers under Cosmos ordering).
	var first map[string]any
	require.NoError(t, json.Unmarshal(rows[0], &first))
	_, ok := first["population"].(string)
	assert.True(t, ok, "first row under DESC must be a string-valued population (strings sort after numbers)")
}

func TestDistinctPipeline_10_2_DistinctValue(t *testing.T) {
	rows := drivePipelineAll(t, "10_2", `SELECT DISTINCT VALUE c.countryRegion FROM c`)
	// Captured fixture has 6 unique countryRegion scalars.
	assert.Len(t, rows, 6)
	seen := map[string]bool{}
	for _, r := range rows {
		var v string
		require.NoError(t, json.Unmarshal(r, &v))
		assert.False(t, seen[v], "duplicate scalar emitted: %s", v)
		seen[v] = true
	}
}

func TestAggPipeline_11_2_ValueCount(t *testing.T) {
	row := driveAggPipeline(t, "11_2", `SELECT VALUE COUNT(1) FROM c`)
	var n float64
	require.NoError(t, json.Unmarshal(row, &n))
	assert.EqualValues(t, 18, n)
}

// indexOf is a tiny helper around bytes.Index to keep the test file imports minimal.
func indexOf(haystack, needle []byte) int {
	for i := 0; i+len(needle) <= len(haystack); i++ {
		match := true
		for j := range needle {
			if haystack[i+j] != needle[j] {
				match = false
				break
			}
		}
		if match {
			return i
		}
	}
	return -1
}

// drivePlan runs a recorded plan against the given per-range response bodies
// and returns every emitted row. Unlike drivePipelineAll the bodies are given
// inline, which is how the cases below pin an answer against a partition that
// matched no row.
func drivePlan(t *testing.T, planFile, query string, bodies map[string][]byte) []json.RawMessage {
	t.Helper()
	plan := mustReadFile(t, filepath.Join("testdata", "query_"+planFile+"_plan.json"))
	ids := make([]string, 0, len(bodies))
	for id := range bodies {
		ids = append(ids, fmt.Sprintf("{\"id\":%q}", id))
	}
	// Range order decides which rows a window lands on, so it must not come
	// from map iteration.
	slices.Sort(ids)
	pkranges := fmt.Sprintf(`{"PartitionKeyRanges":[%s]}`, strings.Join(ids, ","))

	pipe, err := gonative.Default().CreateQueryPipeline(query, string(plan), pkranges)
	require.NoError(t, err, "CreateQueryPipeline")
	defer pipe.Close()

	var rows []json.RawMessage
	for i := 0; i < 50 && !pipe.IsComplete(); i++ {
		res, err := pipe.Run()
		require.NoError(t, err, "Run turn %d", i)
		if len(res.Requests) > 0 {
			results := make([]queryengine.QueryResult, 0, len(res.Requests))
			for _, req := range res.Requests {
				body, ok := bodies[req.PartitionKeyRangeID]
				require.True(t, ok, "missing body for pk-range %s", req.PartitionKeyRangeID)
				results = append(results, queryengine.NewQueryResult(req.PartitionKeyRangeID, body, ""))
			}
			require.NoError(t, pipe.ProvideData(results))
		}
		for _, it := range res.Items {
			rows = append(rows, append(json.RawMessage(nil), it...))
		}
	}
	require.True(t, pipe.IsComplete(), "pipeline did not converge")
	return rows
}

// A VALUE-form MIN reads its partial from item2, the only one that reports how
// many rows the partition contributed. Reading item instead fed a bare scalar
// to an aggregator expecting `{min, count}`.
func TestValuePipeline_MinReadsTheItem2Partial(t *testing.T) {
	rows := drivePlan(t, "min_value_empty", `SELECT VALUE MIN(c.seq) FROM c WHERE c.seq > 0`,
		map[string][]byte{
			"0": []byte(`{"Documents":[[{"item":5,"item2":{"min":5,"count":3}}]]}`),
			"1": []byte(`{"Documents":[[{"item":2,"item2":{"min":2,"count":7}}]]}`),
		})
	require.Len(t, rows, 1)
	assert.JSONEq(t, `2`, string(rows[0]))
}

// MIN over a result set no row matched is undefined, and the gateway answers
// it with no row at all. Before this the absent partial failed the query with
// "gonative/agg.min: unexpected end of JSON input".
func TestValuePipeline_MinOverAnEmptySetEmitsNoRow(t *testing.T) {
	rows := drivePlan(t, "min_value_empty", `SELECT VALUE MIN(c.seq) FROM c WHERE c.seq > 99999999`,
		map[string][]byte{
			"0": []byte(`{"Documents":[[{"item2":{"count":0}}]]}`),
			"1": []byte(`{"Documents":[[{"item2":{"count":0}}]]}`),
		})
	assert.Empty(t, rows)
}

// The aliased form of the same query answers with one row, the alias left out.
func TestAliasedPipeline_MinOverAnEmptySetOmitsTheAlias(t *testing.T) {
	rows := drivePlan(t, "min_aliased_empty", `SELECT MIN(c.seq) AS m FROM c WHERE c.seq > 99999999`,
		map[string][]byte{
			"0": []byte(`{"Documents":[{"payload":{"m":{"item2":{"count":0}}}}]}`),
			"1": []byte(`{"Documents":[{"payload":{"m":{"item2":{"count":0}}}}]}`),
		})
	require.Len(t, rows, 1)
	assert.JSONEq(t, `{}`, string(rows[0]))
}

// An aggregate produces exactly one row, so OFFSET 1 skips past it and the
// gateway answers with nothing.
func TestValuePipeline_OffsetPastTheOnlyRowEmitsNothing(t *testing.T) {
	rows := drivePlan(t, "agg_offset_limit", `SELECT VALUE COUNT(1) FROM c OFFSET 1 LIMIT 1`,
		map[string][]byte{
			"0": []byte(`{"Documents":[[{"item":4}]]}`),
			"1": []byte(`{"Documents":[[{"item":6}]]}`),
		})
	assert.Empty(t, rows)
}

// OFFSET/LIMIT with no ORDER BY cuts one window out of the concatenated
// ranges. The engine used to reject the plan outright.
func TestSkipTakePipeline_OffsetLimitWithoutOrderBy(t *testing.T) {
	rows := drivePlan(t, "offset_limit", `SELECT c.seq FROM c OFFSET 2 LIMIT 3`,
		map[string][]byte{
			"0": []byte(`{"Documents":[{"seq":1},{"seq":2},{"seq":3}]}`),
		})
	assert.Equal(t, []string{`{"seq":3}`}, jsonStrings(rows))
}

func jsonStrings(rows []json.RawMessage) []string {
	out := make([]string, 0, len(rows))
	for _, r := range rows {
		out = append(out, string(r))
	}
	return out
}