Skip to content

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

8.3 KB
Raw Blame
package doris

import (
	"testing"

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

func TestGetStatementWithResultLimit(t *testing.T) {
	testCases := []struct {
		stmt  string
		count int
		want  string
	}{
		{
			stmt:  "SELECT * FROM t;",
			count: 10,
			want:  "SELECT * FROM t LIMIT 10;",
		},
		{
			stmt:  "SELECT * FROM t LIMIT 10;",
			count: 5,
			want:  "SELECT * FROM t LIMIT 5;",
		},
		{
			stmt:  "SELECT * FROM t LIMIT 5;",
			count: 10,
			want:  "SELECT * FROM t LIMIT 5;",
		},
		{
			stmt:  "SELECT * FROM t LIMIT 123;",
			count: 10,
			want:  "SELECT * FROM t LIMIT 10;",
		},
		{
			stmt:  "SELECT * FROM t LIMIT 10 OFFSET 123;",
			count: 5,
			want:  "SELECT * FROM t LIMIT 5 OFFSET 123;",
		},
		{
			stmt:  "SELECT * FROM t2 JOIN t1 ON t2.c2 = t1.c2 where t2.c2 > 10;",
			count: 10,
			want:  "SELECT * FROM t2 JOIN t1 ON t2.c2 = t1.c2 where t2.c2 > 10 LIMIT 10;",
		},
		{
			stmt:  "SELECT * FROM t1 ORDER BY c2;",
			count: 10,
			want:  "SELECT * FROM t1 ORDER BY c2 LIMIT 10;",
		},
		{
			stmt:  "SELECT COUNT(col1) AS col2 FROM t GROUP BY col2 HAVING col2 = 2;",
			count: 10,
			want:  "SELECT COUNT(col1) AS col2 FROM t GROUP BY col2 HAVING col2 = 2 LIMIT 10;",
		},
		{
			stmt:  "SELECT firstName, lastName FROM employees UNION SELECT contactFirstName, contactLastName FROM customers;",
			count: 10,
			// SetOpStmt.Limit is unreliable in the Doris grammar (see limit.go),
			// so a set operation always takes the CTE-wrap fallback.
			want: "SELECT * FROM (SELECT firstName, lastName FROM employees UNION SELECT contactFirstName, contactLastName FROM customers) result LIMIT 10;",
		},
		{
			stmt:  "SELECT customerNumber, checkNumber, amount FROM payments WHERE amount = (SELECT MAX(amount) FROM payments);",
			count: 10,
			want:  "SELECT customerNumber, checkNumber, amount FROM payments WHERE amount = (SELECT MAX(amount) FROM payments) LIMIT 10;",
		},
		{
			stmt:  "SELECT firstName, lastName FROM employees UNION SELECT contactFirstName, contactLastName FROM customers LIMIT 10;",
			count: 10,
			want:  "SELECT * FROM (SELECT firstName, lastName FROM employees UNION SELECT contactFirstName, contactLastName FROM customers LIMIT 10) result LIMIT 10;",
		},
		{
			// The outer statement is a SelectStmt (its WITH clause), so it still
			// gets the precise in-place edit despite the UNION ALL inside the CTE.
			stmt:  "WITH RECURSIVE cte_count (n) AS ( SELECT 1 UNION ALL SELECT n + 1 FROM cte_count WHERE n < 3 ) SELECT n FROM cte_count;",
			count: 10,
			want:  "WITH RECURSIVE cte_count (n) AS ( SELECT 1 UNION ALL SELECT n + 1 FROM cte_count WHERE n < 3 ) SELECT n FROM cte_count LIMIT 10;",
		},
		{
			stmt:  "SELECT firstName FROM employees EXCEPT SELECT contactFirstName FROM customers;",
			count: 10,
			want:  "SELECT * FROM (SELECT firstName FROM employees EXCEPT SELECT contactFirstName FROM customers) result LIMIT 10;",
		},
		{
			stmt:  "SELECT col1, col2 FROM table1 ORDER BY col1;",
			count: 10,
			want:  "SELECT col1, col2 FROM table1 ORDER BY col1 LIMIT 10;",
		},
		{
			stmt:  "SELECT * FROM t LIMIT 0,20;",
			count: 10,
			want:  "SELECT * FROM t LIMIT 0,10;",
		},
		{
			stmt:  "SELECT * FROM t LIMIT 123,20;",
			count: 10,
			want:  "SELECT * FROM t LIMIT 123,10;",
		},
		{
			// A literal 0 count is kept, not replaced with the requested cap.
			stmt:  "SELECT * FROM t LIMIT 5,0;",
			count: 10,
			want:  "SELECT * FROM t LIMIT 5,0;",
		},
		{
			stmt:  "SELECT * FROM t LIMIT 0,\n1000000;",
			count: 10,
			want:  "SELECT * FROM t LIMIT 0,\n10;",
		},
		{
			stmt:  "SELECT * FROM t LIMIT 0 /* offset comment */, 1000000;",
			count: 10,
			want:  "SELECT * FROM t LIMIT 0 /* offset comment */, 10;",
		},
		// SHOW statements should not be wrapped
		{
			stmt:  "SHOW DATA",
			count: 10,
			want:  "SHOW DATA",
		},
		{
			stmt:  "SHOW TABLES",
			count: 10,
			want:  "SHOW TABLES",
		},
		{
			stmt:  "show data",
			count: 10,
			want:  "show data",
		},
		{
			stmt:  "  SHOW DATA  ",
			count: 10,
			want:  "  SHOW DATA  ",
		},
		{
			stmt:  "SELECT * FROM t -- note",
			count: 10,
			want:  "SELECT * FROM t LIMIT 10 -- note",
		},
		{
			stmt:  "SELECT * FROM t /* block comment */;",
			count: 10,
			want:  "SELECT * FROM t LIMIT 10 /* block comment */;",
		},
		{
			stmt:  "SELECT * FROM t # hash comment",
			count: 10,
			want:  "SELECT * FROM t LIMIT 10 # hash comment",
		},
		{
			stmt:  "SELECT * FROM t;\n",
			count: 10,
			want:  "SELECT * FROM t LIMIT 10;\n",
		},
		// A set operation always takes the CTE-wrap fallback (see limit.go), so an
		// existing LIMIT on it — larger, smaller, or comma-form — is never capped
		// in place; the fallback's own LIMIT is still the one that binds.
		{
			stmt:  "SELECT a FROM t1 UNION SELECT b FROM t2 LIMIT 100;",
			count: 10,
			want:  "SELECT * FROM (SELECT a FROM t1 UNION SELECT b FROM t2 LIMIT 100) result LIMIT 10;",
		},
		{
			stmt:  "SELECT a FROM t1 INTERSECT SELECT b FROM t2;",
			count: 10,
			want:  "SELECT * FROM (SELECT a FROM t1 INTERSECT SELECT b FROM t2) result LIMIT 10;",
		},
		// Doris has no dedicated node for a parenthesized top-level query: the
		// grammar strips the parens transparently and produces a plain
		// SelectStmt whose Loc always lands after the closing paren(s), so a
		// bare append at Loc.End still lands outside them and is valid SQL.
		{
			stmt:  "(SELECT * FROM t)",
			count: 10,
			want:  "(SELECT * FROM t) LIMIT 10",
		},
		{
			stmt:  "((SELECT * FROM t))",
			count: 10,
			want:  "((SELECT * FROM t)) LIMIT 10",
		},
		{
			stmt:  "(SELECT * FROM t) LIMIT 1000000",
			count: 10,
			want:  "(SELECT * FROM t) LIMIT 10",
		},
		{
			stmt:  "(SELECT * FROM t) ORDER BY 1",
			count: 10,
			want:  "(SELECT * FROM t) ORDER BY 1 LIMIT 10",
		},
	}

	for _, tc := range testCases {
		got := statementWithResultLimit(tc.stmt, tc.count, "")
		require.Equal(t, tc.want, got, tc.stmt)
	}
}

func TestResultLimitOutfile(t *testing.T) {
	tests := []struct {
		name      string
		statement string
		want      string
	}{
		{
			name:      "select with export options",
			statement: `SELECT * FROM orders INTO OUTFILE 's3://bucket/export/' FORMAT AS CSV PROPERTIES("column_separator" = ",");`,
			want:      "SELECT * FROM orders LIMIT 10\n" + `INTO OUTFILE 's3://bucket/export/' FORMAT AS CSV PROPERTIES("column_separator" = ",");`,
		},
		{
			name:      "existing limit",
			statement: "SELECT * FROM orders LIMIT 100 INTO OUTFILE 's3://bucket/export/';",
			want:      "SELECT * FROM orders LIMIT 10\nINTO OUTFILE 's3://bucket/export/';",
		},
		{
			name:      "zero limit",
			statement: "SELECT * FROM orders LIMIT 0 INTO OUTFILE 's3://bucket/export/';",
			want:      "SELECT * FROM orders LIMIT 0\nINTO OUTFILE 's3://bucket/export/';",
		},
		{
			name:      "zero comma limit",
			statement: "SELECT * FROM orders LIMIT 5,0 INTO OUTFILE 's3://bucket/export/';",
			want:      "SELECT * FROM orders LIMIT 5,0\nINTO OUTFILE 's3://bucket/export/';",
		},
		{
			name:      "union fallback",
			statement: "SELECT a FROM t1 UNION SELECT a FROM t2 INTO OUTFILE 's3://bucket/export/';",
			want:      "SELECT * FROM (\nSELECT a FROM t1 UNION SELECT a FROM t2\n) result LIMIT 10\nINTO OUTFILE 's3://bucket/export/';",
		},
		{
			name:      "comments and case",
			statement: "SELECT * FROM orders -- export\ninto /* target */ outfile 's3://bucket/export/';",
			want:      "SELECT * FROM orders LIMIT 10 -- export\ninto /* target */ outfile 's3://bucket/export/';",
		},
		{
			name:      "union with trailing comment",
			statement: "SELECT a FROM t1 UNION SELECT a FROM t2 -- export\nINTO OUTFILE 's3://bucket/export/';",
			want:      "SELECT * FROM (\nSELECT a FROM t1 UNION SELECT a FROM t2 -- export\n) result LIMIT 10\nINTO OUTFILE 's3://bucket/export/';",
		},
		{
			name:      "parenthesized query",
			statement: "(SELECT * FROM orders) INTO OUTFILE 's3://bucket/export/';",
			want:      "(SELECT * FROM orders) LIMIT 10\nINTO OUTFILE 's3://bucket/export/';",
		},
		{
			name:      "quoted words",
			statement: "SELECT 'INTO OUTFILE', `INTO OUTFILE` FROM t",
			want:      "SELECT 'INTO OUTFILE', `INTO OUTFILE` FROM t LIMIT 10",
		},
		{
			name:      "comment words",
			statement: "SELECT * FROM t /* INTO OUTFILE */;",
			want:      "SELECT * FROM t LIMIT 10 /* INTO OUTFILE */;",
		},
	}
	for _, tt := range tests {
		t.Run(tt.name, func(t *testing.T) {
			require.Equal(t, tt.want, statementWithResultLimit(tt.statement, 10, ""))
		})
	}
}