Skip to content

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

7.9 KB
Raw Blame
package oracle

import (
	"context"
	"database/sql"
	"database/sql/driver"
	"io"
	"regexp"
	"testing"

	"github.com/stretchr/testify/require"
)

func TestCountAffectedRows(t *testing.T) {
	const hint = "/*+ OPT_PARAM('optimizer_dynamic_sampling' 11) */"
	for _, tc := range []struct {
		name      string
		statement string
		// plan holds the ID, PARENT_ID, OPERATION, and CARDINALITY of each PLAN_TABLE row.
		plan      [][]driver.Value
		explained string
		want      int64
		wantErr   bool
	}{
		{
			name:      "large_cardinality",
			statement: "UPDATE huge SET c = 1 WHERE c < 10",
			// go-ora returns NUMBER columns as strings.
			plan: [][]driver.Value{
				{"0", nil, "UPDATE STATEMENT", "5000000"},
				{"1", "0", "UPDATE", nil},
				{"2", "1", "TABLE ACCESS", "5000000"},
			},
			explained: "UPDATE " + hint + " huge SET c = 1 WHERE c < 10",
			want:      5000000,
		},
		{
			name:      "trailing_semicolon",
			statement: "DELETE FROM t WHERE c < 10;\n",
			plan:      [][]driver.Value{{"0", nil, "DELETE STATEMENT", "101"}},
			explained: "DELETE " + hint + " FROM t WHERE c < 10",
			want:      101,
		},
		{
			// Captured from Oracle 23 with dynamic sampling, where the statement row estimates 1 row
			// for a MERGE that inserts 100.
			name:      "merge_reads_the_rows_below_the_merge_operation",
			statement: "MERGE INTO t2 USING (SELECT * FROM t WHERE c < 10) src ON (t2.id = src.id) WHEN NOT MATCHED THEN INSERT VALUES (src.id, src.c, src.d, src.grp, src.v)",
			plan: [][]driver.Value{
				{"0", nil, "MERGE STATEMENT", "1"},
				{"1", "0", "MERGE", nil},
				{"2", "1", "VIEW", nil},
				{"3", "2", "NESTED LOOPS", "100"},
				{"4", "3", "TABLE ACCESS", "100"},
				{"5", "3", "TABLE ACCESS", "1"},
				{"6", "5", "INDEX", "1"},
			},
			explained: "MERGE " + hint + " INTO t2 USING (SELECT * FROM t WHERE c < 10) src ON (t2.id = src.id) WHEN NOT MATCHED THEN INSERT VALUES (src.id, src.c, src.d, src.grp, src.v)",
			want:      100,
		},
		{
			// Captured from Oracle 23, where the statement row estimates the 1000 rows an INSERT ALL reads
			// and writes to three tables.
			name:      "insert_all_writes_each_row_to_every_into",
			statement: "INSERT ALL INTO t1 VALUES (id) INTO t2 VALUES (id) INTO t3 VALUES (id) SELECT id FROM src",
			plan: [][]driver.Value{
				{"0", nil, "INSERT STATEMENT", "1000"},
				{"1", "0", "MULTI-TABLE INSERT", nil},
				{"2", "1", "TABLE ACCESS", "1000"},
				{"3", "1", "INTO", nil},
				{"4", "1", "INTO", nil},
				{"5", "1", "INTO", nil},
			},
			explained: "INSERT " + hint + " ALL INTO t1 VALUES (id) INTO t2 VALUES (id) INTO t3 VALUES (id) SELECT id FROM src",
			want:      3000,
		},
		{
			name:      "insert_first_writes_each_row_once",
			statement: "INSERT /* move */ FIRST WHEN id < 10 THEN INTO t1 VALUES (id) ELSE INTO t2 VALUES (id) SELECT id FROM src",
			plan: [][]driver.Value{
				{"0", nil, "INSERT STATEMENT", "1000"},
				{"1", "0", "MULTI-TABLE INSERT", nil},
				{"2", "1", "TABLE ACCESS", "1000"},
				{"3", "1", "INTO", nil},
				{"4", "1", "INTO", nil},
			},
			explained: "INSERT " + hint + " /* move */ FIRST WHEN id < 10 THEN INTO t1 VALUES (id) ELSE INTO t2 VALUES (id) SELECT id FROM src",
			want:      1000,
		},
		{
			name:      "merge_without_cardinality",
			statement: "MERGE INTO t2 USING t ON (t2.id = t.id) WHEN MATCHED THEN UPDATE SET t2.v = t.v",
			plan: [][]driver.Value{
				{"0", nil, "MERGE STATEMENT", "1000"},
				{"1", "0", "MERGE", nil},
				{"2", "1", "VIEW", nil},
			},
			explained: "MERGE " + hint + " INTO t2 USING t ON (t2.id = t.id) WHEN MATCHED THEN UPDATE SET t2.v = t.v",
			wantErr:   true,
		},
		{
			name:      "null_cardinality",
			statement: "DELETE FROM t;",
			plan:      [][]driver.Value{{"0", nil, "DELETE STATEMENT", nil}},
			explained: "DELETE " + hint + " FROM t",
			wantErr:   true,
		},
		{
			name:      "missing_statement_row",
			statement: "DELETE FROM t;",
			explained: "DELETE " + hint + " FROM t",
			wantErr:   true,
		},
	} {
		t.Run(tc.name, func(t *testing.T) {
			connector := &fakePlanConnector{plan: tc.plan}
			db := sql.OpenDB(connector)
			defer db.Close()
			// Without idle connections, every statement that is not pinned to one connection opens a new one.
			db.SetMaxIdleConns(0)

			got, err := (&Driver{db: db}).CountAffectedRows(context.Background(), tc.statement)
			if tc.wantErr {
				require.Error(t, err)
			} else {
				require.NoError(t, err)
				require.Equal(t, tc.want, got)
			}

			require.NotEmpty(t, connector.queries)
			match := regexp.MustCompile(`^EXPLAIN PLAN SET STATEMENT_ID = '(\d+)' FOR `).FindStringSubmatch(connector.queries[0].text)
			require.NotNil(t, match, connector.queries[0].text)
			id := match[1]
			require.Equal(t, []fakePlanQuery{
				{conn: 1, text: "EXPLAIN PLAN SET STATEMENT_ID = '" + id + "' FOR " + tc.explained},
				{conn: 1, text: "SELECT ID, PARENT_ID, OPERATION, CARDINALITY FROM PLAN_TABLE WHERE STATEMENT_ID = '" + id + "' ORDER BY ID"},
				{conn: 1, text: "DELETE FROM PLAN_TABLE WHERE STATEMENT_ID = '" + id + "'"},
			}, connector.queries)
		})
	}
}

func TestWithDynamicSamplingHint(t *testing.T) {
	for _, tc := range []struct {
		statement string
		want      string
	}{
		{
			statement: "update t set c = 1",
			want:      "update /*+ OPT_PARAM('optimizer_dynamic_sampling' 11) */ t set c = 1",
		},
		{
			statement: "INSERT INTO t2 SELECT * FROM t",
			want:      "INSERT /*+ OPT_PARAM('optimizer_dynamic_sampling' 11) */ INTO t2 SELECT * FROM t",
		},
		{
			statement: "-- clean up\n/* ticket 42 */ DELETE FROM t",
			want:      "-- clean up\n/* ticket 42 */ DELETE /*+ OPT_PARAM('optimizer_dynamic_sampling' 11) */ FROM t",
		},
		{
			statement: "UPDATE /*+ INDEX(t t_c) */ t SET v = 1 WHERE c = 1",
			want:      "UPDATE /*+ OPT_PARAM('optimizer_dynamic_sampling' 11)  INDEX(t t_c) */ t SET v = 1 WHERE c = 1",
		},
		{
			statement: "DELETE --+ FULL(t)\nFROM t",
			want:      "DELETE --+ OPT_PARAM('optimizer_dynamic_sampling' 11)  FULL(t)\nFROM t",
		},
		{
			statement: "SELECT * FROM t",
			want:      "SELECT * FROM t",
		},
	} {
		t.Run(tc.statement, func(t *testing.T) {
			require.Equal(t, tc.want, withDynamicSamplingHint(tc.statement))
		})
	}
}

// fakePlanConnector returns the plan rows for every query and records each statement with the
// number of the connection that ran it.
type fakePlanConnector struct {
	plan [][]driver.Value
	// columns names the plan columns; empty means the four CountAffectedRows reads.
	columns []string
	opened  int
	queries []fakePlanQuery
}

type fakePlanQuery struct {
	conn int
	text string
}

func (c *fakePlanConnector) Connect(context.Context) (driver.Conn, error) {
	c.opened++
	return &fakePlanConn{connector: c, id: c.opened}, nil
}

func (*fakePlanConnector) Driver() driver.Driver {
	return nil
}

type fakePlanConn struct {
	connector *fakePlanConnector
	id        int
}

func (*fakePlanConn) Prepare(string) (driver.Stmt, error) {
	return nil, driver.ErrSkip
}

func (*fakePlanConn) Close() error {
	return nil
}

func (*fakePlanConn) Begin() (driver.Tx, error) {
	return nil, driver.ErrSkip
}

func (c *fakePlanConn) ExecContext(_ context.Context, query string, _ []driver.NamedValue) (driver.Result, error) {
	c.connector.queries = append(c.connector.queries, fakePlanQuery{conn: c.id, text: query})
	return driver.RowsAffected(0), nil
}

func (c *fakePlanConn) QueryContext(_ context.Context, query string, _ []driver.NamedValue) (driver.Rows, error) {
	c.connector.queries = append(c.connector.queries, fakePlanQuery{conn: c.id, text: query})
	return &fakePlanRows{rows: c.connector.plan, columns: c.connector.columns}, nil
}

type fakePlanRows struct {
	rows    [][]driver.Value
	columns []string
}

func (r *fakePlanRows) Columns() []string {
	if len(r.columns) > 0 {
		return r.columns
	}
	return []string{"ID", "PARENT_ID", "OPERATION", "CARDINALITY"}
}

func (*fakePlanRows) Close() error {
	return nil
}

func (r *fakePlanRows) Next(dest []driver.Value) error {
	if len(r.rows) == 0 {
		return io.EOF
	}
	copy(dest, r.rows[0])
	r.rows = r.rows[1:]
	return nil
}