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 redshift
import (
"strings"
"testing"
"github.com/stretchr/testify/assert"
"github.com/stretchr/testify/require"
)
func TestGetStatementWithResultLimit(t *testing.T) {
tests := []struct {
name string
statement string
limit int
want string
}{
{
name: "Simple SELECT without LIMIT",
statement: "SELECT * FROM users",
limit: 10,
want: "SELECT * FROM users LIMIT 10",
},
{
name: "SELECT with existing LIMIT higher than requested",
statement: "SELECT * FROM users LIMIT 100",
limit: 10,
want: "SELECT * FROM users LIMIT 10",
},
{
name: "SELECT with existing LIMIT lower than requested",
statement: "SELECT * FROM users LIMIT 5",
limit: 10,
want: "SELECT * FROM users LIMIT 5",
},
{
name: "SELECT with existing LIMIT 0 is kept, not replaced",
statement: "SELECT * FROM users LIMIT 0",
limit: 10,
want: "SELECT * FROM users LIMIT 0",
},
{
name: "WITH query (CTE)",
statement: "WITH active_users AS (SELECT * FROM users WHERE active = true) SELECT * FROM active_users",
limit: 10,
want: "WITH active_users AS (SELECT * FROM users WHERE active = true) SELECT * FROM active_users LIMIT 10",
},
{
name: "SELECT with ORDER BY",
statement: "SELECT * FROM users ORDER BY created_at DESC",
limit: 10,
want: "SELECT * FROM users ORDER BY created_at DESC LIMIT 10",
},
{
name: "UNION query",
statement: "SELECT id FROM users UNION SELECT id FROM admins",
limit: 10,
want: "SELECT id FROM users UNION SELECT id FROM admins LIMIT 10",
},
{
name: "SELECT with FOR UPDATE",
statement: "SELECT * FROM users WHERE id = 1 FOR UPDATE",
limit: 10,
want: "SELECT * FROM users WHERE id = 1 LIMIT 10 FOR UPDATE",
},
{
name: "Non-SELECT statement (INSERT)",
statement: "INSERT INTO users (name) VALUES ('John')",
limit: 10,
want: "INSERT INTO users (name) VALUES ('John')",
},
{
name: "Non-SELECT statement (DELETE)",
statement: "DELETE FROM users WHERE active = false",
limit: 10,
want: "DELETE FROM users WHERE active = false",
},
}
for _, tt := range tests {
t.Run(tt.name, func(t *testing.T) {
got := statementWithResultLimit(tt.statement, tt.limit, "")
if tt.statement == tt.want {
assert.Equal(t, tt.want, got)
} else {
assert.Contains(t, got, "LIMIT")
}
})
}
}
func TestGetStatementWithResultLimitInline(t *testing.T) {
tests := []struct {
name string
statement string
limit int
wantErr bool
want string
}{
{
name: "Valid SELECT statement",
statement: "SELECT * FROM users",
limit: 10,
want: "SELECT * FROM users LIMIT 10",
},
{
name: "Invalid SQL syntax",
statement: "SELECT * FROM WHERE",
limit: 10,
wantErr: true,
},
{
name: "Empty statement",
statement: "",
limit: 10,
wantErr: true,
},
{
name: "FOR UPDATE gets proper whitespace",
statement: "SELECT * FROM users WHERE id = 1 FOR UPDATE",
limit: 10,
want: "SELECT * FROM users WHERE id = 1 LIMIT 10 FOR UPDATE",
},
{
name: "Non-constant LIMIT expression falls back to error",
statement: "SELECT * FROM t LIMIT (1+2)",
limit: 5,
wantErr: true,
},
{
name: "LIMIT 0 is kept, not replaced with the requested cap",
statement: "SELECT * FROM t LIMIT 0",
limit: 5,
want: "SELECT * FROM t LIMIT 0",
},
}
for _, tt := range tests {
t.Run(tt.name, func(t *testing.T) {
got, err := statementWithResultLimitInline(tt.statement, tt.limit)
if tt.wantErr {
require.Error(t, err)
return
}
require.NoError(t, err)
assert.NotEmpty(t, got)
if tt.want != "" {
assert.Equal(t, tt.want, got)
}
})
}
}
// TestGetStatementWithResultLimitFallback locks Redshift's own fallback text,
// distinct from PostgreSQL's: a blind CTE-wrap for a statement the AST rewrite
// cannot handle, still never applied to a statement that already parsed clean.
func TestGetStatementWithResultLimitFallback(t *testing.T) {
got := statementWithResultLimit("SELECT * FROM t LIMIT (1+2)", 5, "")
require.Equal(t, "WITH result AS (\nSELECT * FROM t LIMIT (1+2)\n) SELECT * FROM result LIMIT 5;", got)
}
func TestGetStatementWithResultLimitInlineClauseOrder(t *testing.T) {
tests := []struct {
name string
statement string
limit int
want string
clausesInOrder []string
}{
{
name: "ORDER BY",
statement: "SELECT * FROM users ORDER BY created_at DESC",
limit: 10,
want: "SELECT * FROM users ORDER BY created_at DESC LIMIT 10",
clausesInOrder: []string{"ORDER BY", "LIMIT 10"},
},
{
name: "ORDER BY with FOR UPDATE",
statement: "SELECT * FROM users ORDER BY id FOR UPDATE",
limit: 10,
want: "SELECT * FROM users ORDER BY id LIMIT 10 FOR UPDATE",
clausesInOrder: []string{"ORDER BY", "LIMIT 10", "FOR UPDATE"},
},
{
name: "WITH query with outer ORDER BY",
statement: "WITH active_users AS (SELECT * FROM users WHERE active = true) SELECT * FROM active_users ORDER BY created_at",
limit: 10,
want: "WITH active_users AS (SELECT * FROM users WHERE active = true) SELECT * FROM active_users ORDER BY created_at LIMIT 10",
clausesInOrder: []string{"WITH", "ORDER BY", "LIMIT 10"},
},
}
for _, tt := range tests {
t.Run(tt.name, func(t *testing.T) {
got, err := statementWithResultLimitInline(tt.statement, tt.limit)
require.NoError(t, err)
if tt.want != "" {
assert.Equal(t, tt.want, got)
}
assertSubstringsInOrder(t, got, tt.clausesInOrder)
_, err = ParseRedshift(got)
require.NoError(t, err, "rewritten SQL should remain parseable: %s", 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)
}
}
func TestUnlimitedResultLimit(t *testing.T) {
for _, clause := range []string{"ALL", "NULL"} {
t.Run(clause, func(t *testing.T) {
statement := "SELECT * FROM t LIMIT " + clause + " OFFSET 2;"
got := statementWithResultLimit(statement, 10, "")
require.Equal(t, "WITH result AS (\nSELECT * FROM t LIMIT "+clause+" OFFSET 2\n) SELECT * FROM result LIMIT 10;", got)
})
}
}