Database governance built for humans and agents — controlling changes and access across every major database.
Go cicddata-governancedata-maskingdata-securitydatabase-accessdatabase-governancedevopsdevsecopsflywaygitopsliquibasemongodbmysqloraclepostgresqlschema-migrationssecuritysql-clientsqlservertidb
package plsql
import (
"io"
"os"
"path/filepath"
"strings"
"testing"
"github.com/stretchr/testify/require"
"gopkg.in/yaml.v3"
"github.com/bytebase/bytebase/backend/common/yamltest"
)
func TestGetStatementWithResultLimit(t *testing.T) {
runLimitTest(t, "test_limit.yaml", false /* record */)
}
func TestAddLimitFor12cAndLaterUsesOmni(t *testing.T) {
stmt := "SELECT emp_id, salary, ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn FROM employees QUALIFY rn <= 10"
got := addLimitFor12cAndLater(stmt, 5)
require.Equal(t, stmt+" FETCH NEXT 5 ROWS ONLY", got)
}
func TestAddResultLimitKeepsNonSelectStatementsUnchanged(t *testing.T) {
tests := []struct {
name string
statement string
engineVersion string
}{
{
name: "12c update",
statement: "UPDATE employees SET salary = salary + 1",
engineVersion: "19.0.0.0.0",
},
{
name: "11g delete",
statement: "DELETE FROM employees WHERE id = 1",
engineVersion: "11.2.0.4.0",
},
{
name: "leading whitespace insert",
statement: "\n\tINSERT INTO employees(id) VALUES (1)",
engineVersion: "19.0.0.0.0",
},
}
for _, tc := range tests {
t.Run(tc.name, func(t *testing.T) {
got := statementWithResultLimit(tc.statement, 10, tc.engineVersion)
require.Equal(t, tc.statement, got)
})
}
}
func TestAddResultLimitSkipsOnlySimpleDualSelect(t *testing.T) {
tests := []struct {
name string
statement string
want string
}{
{
name: "simple dual select",
statement: "SELECT 1 FROM DUAL",
want: "SELECT 1 FROM DUAL",
},
{
name: "dual select with scalar subquery",
statement: "SELECT (SELECT COUNT(*) FROM employees) FROM DUAL",
want: "SELECT (SELECT COUNT(*) FROM employees) FROM DUAL FETCH NEXT 10 ROWS ONLY",
},
{
name: "dual select star keeps historical no-skip behavior",
statement: "SELECT * FROM DUAL",
want: "SELECT * FROM DUAL FETCH NEXT 10 ROWS ONLY",
},
}
for _, tc := range tests {
t.Run(tc.name, func(t *testing.T) {
got := statementWithResultLimit(tc.statement, 10, "19.0.0.0.0")
require.Equal(t, tc.want, got)
})
}
}
func TestAddLimitFor12cAndLaterRegressionClauseOrder(t *testing.T) {
tests := []struct {
name string
statement string
limit int
want string
clausesInOrder []string
validateParse bool
}{
{
name: "outer order by stays before fetch",
statement: "SELECT id FROM employees ORDER BY id",
limit: 10,
want: "SELECT id FROM employees ORDER BY id FETCH NEXT 10 ROWS ONLY",
clausesInOrder: []string{"ORDER BY", "FETCH NEXT 10 ROWS ONLY"},
validateParse: true,
},
{
name: "with query outer order by",
statement: "WITH active_employees AS (SELECT id FROM employees WHERE active = 1) SELECT id FROM active_employees ORDER BY id",
limit: 10,
want: "WITH active_employees AS (SELECT id FROM employees WHERE active = 1) SELECT id FROM active_employees ORDER BY id FETCH NEXT 10 ROWS ONLY",
clausesInOrder: []string{"WITH", "ORDER BY", "FETCH NEXT 10 ROWS ONLY"},
validateParse: true,
},
{
name: "subquery order by is not treated as outer order by",
statement: "SELECT * FROM (SELECT id FROM employees ORDER BY id) ordered_employees",
limit: 10,
want: "SELECT * FROM (SELECT id FROM employees ORDER BY id) ordered_employees FETCH NEXT 10 ROWS ONLY",
clausesInOrder: []string{"ordered_employees", "FETCH NEXT 10 ROWS ONLY"},
validateParse: true,
},
// Oracle expects the row limiting clause before FOR UPDATE. Omni currently
// cannot parse the rewritten statement, so these cases assert clause order
// without validateParse to avoid regressing back to parser-friendly but invalid SQL.
{
name: "for update remains the final clause",
statement: "SELECT * FROM employees FOR UPDATE",
limit: 10,
want: "SELECT * FROM employees FETCH NEXT 10 ROWS ONLY FOR UPDATE",
clausesInOrder: []string{"FETCH NEXT 10 ROWS ONLY", "FOR UPDATE"},
},
{
name: "order by with for update skip locked",
statement: "SELECT * FROM employees ORDER BY id FOR UPDATE SKIP LOCKED",
limit: 10,
want: "SELECT * FROM employees ORDER BY id FETCH NEXT 10 ROWS ONLY FOR UPDATE SKIP LOCKED",
clausesInOrder: []string{"ORDER BY", "FETCH NEXT 10 ROWS ONLY", "FOR UPDATE SKIP LOCKED"},
},
{
name: "union outer order by stays before fetch",
statement: "SELECT id FROM employees UNION ALL SELECT id FROM contractors ORDER BY id",
limit: 10,
want: "SELECT id FROM employees UNION ALL SELECT id FROM contractors ORDER BY id FETCH NEXT 10 ROWS ONLY",
clausesInOrder: []string{"UNION ALL", "ORDER BY", "FETCH NEXT 10 ROWS ONLY"},
validateParse: true,
},
}
for _, tc := range tests {
t.Run(tc.name, func(t *testing.T) {
got := addLimitFor12cAndLater(tc.statement, tc.limit)
require.Equal(t, tc.want, got)
assertSubstringsInOrder(t, got, tc.clausesInOrder)
if tc.validateParse {
_, err := ParsePLSQL(got)
require.NoError(t, err, "rewritten SQL should remain parseable: %s", got)
}
})
}
}
func TestAddLimitFor12cAndLaterParenthesizedQueries(t *testing.T) {
tests := []struct {
name string
statement string
want string
}{
{
name: "parenthesized query",
statement: "(SELECT a FROM t)",
want: "(SELECT a FROM t) FETCH NEXT 10 ROWS ONLY",
},
{
name: "parenthesized query with order by",
statement: "(SELECT a FROM t) ORDER BY a",
want: "(SELECT a FROM t) ORDER BY a FETCH NEXT 10 ROWS ONLY",
},
{
name: "parenthesized query lowers its row limit",
statement: "(SELECT a FROM t) FETCH FIRST 100 ROWS ONLY",
want: "(SELECT a FROM t) FETCH FIRST 10 ROWS ONLY",
},
{
name: "row limit of a parenthesized last operand limits that operand only",
statement: "SELECT a FROM t UNION ALL (SELECT b FROM t FETCH FIRST 3 ROWS ONLY)",
want: "SELECT a FROM t UNION ALL (SELECT b FROM t FETCH FIRST 3 ROWS ONLY) FETCH NEXT 10 ROWS ONLY",
},
{
name: "row limit inside a parenthesized set operation limits that operation only",
statement: "SELECT a FROM t UNION (SELECT b FROM t UNION SELECT a FROM t FETCH FIRST 3 ROWS ONLY)",
want: "SELECT a FROM t UNION (SELECT b FROM t UNION SELECT a FROM t FETCH FIRST 3 ROWS ONLY) FETCH NEXT 10 ROWS ONLY",
},
{
name: "row limit after a parenthesized last operand limits the query",
statement: "SELECT a FROM t UNION (SELECT b FROM t UNION SELECT a FROM t) FETCH FIRST 30 ROWS ONLY",
want: "SELECT a FROM t UNION (SELECT b FROM t UNION SELECT a FROM t) FETCH FIRST 10 ROWS ONLY",
},
{
name: "row limit after a parenthesized middle operand limits the query",
statement: "SELECT a FROM t UNION (SELECT b FROM t) UNION SELECT a FROM t FETCH FIRST 30 ROWS ONLY",
want: "SELECT a FROM t UNION (SELECT b FROM t) UNION SELECT a FROM t FETCH FIRST 10 ROWS ONLY",
},
{
name: "parenthesized operands with order by",
statement: "(SELECT a FROM t) UNION (SELECT b FROM t) ORDER BY 1",
want: "(SELECT a FROM t) UNION (SELECT b FROM t) ORDER BY 1 FETCH NEXT 10 ROWS ONLY",
},
{
name: "parenthesized set operation",
statement: "(SELECT a FROM t UNION SELECT b FROM t)",
want: "(SELECT a FROM t UNION SELECT b FROM t FETCH NEXT 10 ROWS ONLY)",
},
{
name: "parenthesis in a string literal of the last operand",
statement: "SELECT a FROM t UNION (SELECT ')' FROM t)",
want: "SELECT a FROM t UNION (SELECT ')' FROM t) FETCH NEXT 10 ROWS ONLY",
},
{
name: "except",
statement: "SELECT a FROM t EXCEPT SELECT b FROM t",
want: "SELECT a FROM t EXCEPT SELECT b FROM t FETCH NEXT 10 ROWS ONLY",
},
}
for _, tc := range tests {
t.Run(tc.name, func(t *testing.T) {
got := addLimitFor12cAndLater(tc.statement, 10)
require.Equal(t, tc.want, got)
_, err := ParsePLSQL(got)
require.NoError(t, err, "rewritten SQL should remain parseable: %s", got)
})
}
}
func TestAddLimitFor11gWrapsParenthesizedQuery(t *testing.T) {
got := addLimitFor11g("(SELECT a FROM t) ORDER BY a", 10)
require.Equal(t, "SELECT * FROM ((SELECT a FROM t) ORDER BY a) WHERE ROWNUM <= 10", got)
}
func TestAddFetchNextClauseRegressionExistingFetch(t *testing.T) {
tests := []struct {
name string
statement string
limit int
want string
wantErr bool
}{
{
name: "keeps lower existing fetch",
statement: "SELECT * FROM employees FETCH NEXT 5 ROWS ONLY",
limit: 10,
want: "SELECT * FROM employees FETCH NEXT 5 ROWS ONLY",
},
{
name: "lowers higher existing fetch",
statement: "SELECT * FROM employees FETCH NEXT 15 ROWS ONLY",
limit: 10,
want: "SELECT * FROM employees FETCH NEXT 10 ROWS ONLY",
},
{
name: "offset only adds fetch",
statement: "SELECT * FROM employees ORDER BY id OFFSET 20 ROWS",
limit: 10,
want: "SELECT * FROM employees ORDER BY id OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY",
},
{
name: "offset expression containing fetch still adds fetch",
statement: "SELECT * FROM employees ORDER BY id OFFSET LENGTH('FETCH') ROWS",
limit: 10,
want: "SELECT * FROM employees ORDER BY id OFFSET LENGTH('FETCH') ROWS FETCH NEXT 10 ROWS ONLY",
},
{
name: "non-constant fetch expression is not rewritten inline",
statement: "SELECT * FROM employees FETCH NEXT :row_count ROWS ONLY",
limit: 10,
wantErr: true,
},
{
name: "percent fetch expression is not a row count",
statement: "SELECT * FROM employees FETCH NEXT 10 PERCENT ROWS ONLY",
limit: 5,
wantErr: true,
},
}
for _, tc := range tests {
t.Run(tc.name, func(t *testing.T) {
got, err := addFetchNextClause(tc.statement, tc.limit)
if tc.wantErr {
require.Error(t, err)
return
}
require.NoError(t, err)
require.Equal(t, tc.want, got)
})
}
}
func TestAddLimitFor12cAndLaterFallsBackForPercentFetch(t *testing.T) {
stmt := "SELECT * FROM employees FETCH NEXT 10 PERCENT ROWS ONLY"
got := addLimitFor12cAndLater(stmt, 5)
require.Equal(t, "SELECT * FROM (SELECT * FROM employees FETCH NEXT 10 PERCENT ROWS ONLY) WHERE ROWNUM <= 5", got)
}
func assertSubstringsInOrder(t *testing.T, s string, substrings []string) {
t.Helper()
start := 0
for _, substring := range substrings {
index := strings.Index(s[start:], substring)
require.NotEqualf(t, -1, index, "expected %q after offset %d in %q", substring, start, s)
start += index + len(substring)
}
}
type limitTestData struct {
Stmt string `yaml:"stmt"`
Limit int `yaml:"limit"`
Want string `yaml:"want"`
}
func runLimitTest(t *testing.T, file string, record bool) {
var testCases []limitTestData
filepath := filepath.Join("test-data", file)
yamlFile, err := os.Open(filepath)
require.NoError(t, err)
defer yamlFile.Close()
byteValue, err := io.ReadAll(yamlFile)
require.NoError(t, err)
err = yaml.Unmarshal(byteValue, &testCases)
require.NoError(t, err)
for i, tc := range testCases {
want := addLimitFor12cAndLater(tc.Stmt, tc.Limit)
if record {
testCases[i].Want = want
} else {
require.Equal(t, tc.Want, want, tc.Stmt)
}
}
if record {
err := yamlFile.Close()
require.NoError(t, err)
yamltest.Record(t, filepath, testCases)
}
}