bytebase/bytebase · error

cannot rewrite PERCENT FETCH expression

Error message

cannot rewrite PERCENT FETCH expression

What it means

The Oracle FETCH FIRST n PERCENT / WITH TIES variant fetches a percentage of rows, which cannot be expressed by rewriting into a fixed 'FETCH NEXT <n> ROWS ONLY' clause. When the AST reports fetch.Percent is true, the rewriter refuses to produce a semantically different query and returns this error.

Source

Thrown at backend/plugin/db/oracle/query.go:289

func rightmostOracleSetSelect(selectStmt *oracleast.SelectStmt) *oracleast.SelectStmt {
	if selectStmt.Op != oracleast.SETOP_NONE && selectStmt.Rarg != nil {
		return rightmostOracleSetSelect(selectStmt.Rarg)
	}
	return selectStmt
}

func rewriteOracleFetchClause(sql string, fetch *oracleast.FetchFirstClause, limitCount int) (string, error) {
	if fetch.Count == nil {
		if fetch.Loc.Start < 0 || fetch.Loc.End < fetch.Loc.Start || fetch.Loc.End > len(sql) {
			return "", errors.Errorf("invalid FETCH position %d:%d", fetch.Loc.Start, fetch.Loc.End)
		}
		if hasOracleFetchKeyword(sql, fetch.Loc) {
			return sql, nil
		}
		return sql[:fetch.Loc.End] + fmt.Sprintf(" FETCH NEXT %d ROWS ONLY", limitCount) + sql[fetch.Loc.End:], nil
	}
	if fetch.Percent {
		return "", errors.Errorf("cannot rewrite PERCENT FETCH expression")
	}

	existingLimit := extractOracleFetchCount(fetch.Count)
	if existingLimit > 0 && existingLimit <= limitCount {
		return sql, nil
	}

	loc := oracleast.NodeLoc(fetch.Count)
	loc = trimOracleLocSpace(sql, loc)
	if loc.Start >= 0 && loc.End > loc.Start && loc.End <= len(sql) {
		if existingLimit <= 0 {
			return "", errors.Errorf("cannot rewrite non-constant FETCH expression")
		}
		return sql[:loc.Start] + fmt.Sprintf("%d", limitCount) + sql[loc.End:], nil
	}
	return "", errors.Errorf("cannot rewrite FETCH expression")
}

View on GitHub (pinned to 1870550677)

Solutions

1. Rewrite the SQL to use a fixed row count (FETCH FIRST n ROWS ONLY) instead of PERCENT before running the rewriter
2. Compute the row count yourself in a wrapping subquery (e.g. SELECT COUNT(*) then FETCH NEXT n ROWS ONLY)
3. Use ROW_NUMBER() OVER (...) with a computed cutoff instead of PERCENT FETCH
4. Skip PERCENT queries in tooling that cannot rewrite them, surfacing a clear unsupported-feature message

Example fix

// before
SELECT * FROM emp FETCH FIRST 50 PERCENT ROWS ONLY;
// after (pre-compute 50% of rows as N)
SELECT * FROM emp ORDER BY emp_id FETCH NEXT :N ROWS ONLY;

if stmt.FetchFirstClause != nil && stmt.FetchFirstClause.Percent {
    // skip or handle PERCENT queries separately before rewriting
    return sql, ErrPercentFetchUnsupported
}

func isPercentFetch(fetch *oracleast.FetchFirstClause) bool { return fetch != nil && fetch.Percent }

rewritten, err := rewriteOracleSelectFetch(ctx, sql, stmt, limit)
if err != nil && strings.Contains(err.Error(), "PERCENT FETCH") {
    // surface an unsupported-feature message instead of failing the whole batch
    return sql, fmt.Errorf("query uses PERCENT FETCH, manual rewrite required: %w", err)
}

When it happens

Trigger: Calling rewriteOracleSelectFetch on a statement whose SQL contains 'FETCH FIRST 50 PERCENT ROWS ONLY' (or the parser sets fetch.Percent), with fetch.Count == nil so the clause is inspected and Percent is detected.

Common situations: Migrating or validating T-SQL-style TOP(n) PERCENT queries converted to Oracle; legacy reports using PERCENT pagination; SQL review tooling scanning queries with PERCENT FETCH.

Understand the failure class

Background: UnsupportedOperationException and "is not supported" errors: when a library deliberately refuses a call — this error's family across 30 libraries.

Related errors

• invalid FETCH position %d:%d
• cannot rewrite non-constant FETCH expression
• cannot rewrite FETCH expression
• number_payload is required for this rule
• number_payload is required for this rule

AI-assisted analysis of bytebase/bytebase@1870550677 (2026-09-06). Data as JSON: /api/errors/bf06c3f25039daf2. Report an issue: GitHub.