{"record":{"id":"bf06c3f25039daf2","repo":"bytebase/bytebase","slug":"cannot-rewrite-percent-fetch-expression","errorCode":null,"errorMessage":"cannot rewrite PERCENT FETCH expression","messagePattern":"cannot rewrite PERCENT FETCH expression","errorType":"exception","errorClass":null,"httpStatus":null,"severity":"error","filePath":"backend/plugin/db/oracle/query.go","lineNumber":289,"sourceCode":"func rightmostOracleSetSelect(selectStmt *oracleast.SelectStmt) *oracleast.SelectStmt {\n\tif selectStmt.Op != oracleast.SETOP_NONE && selectStmt.Rarg != nil {\n\t\treturn rightmostOracleSetSelect(selectStmt.Rarg)\n\t}\n\treturn selectStmt\n}\n\nfunc rewriteOracleFetchClause(sql string, fetch *oracleast.FetchFirstClause, limitCount int) (string, error) {\n\tif fetch.Count == nil {\n\t\tif fetch.Loc.Start < 0 || fetch.Loc.End < fetch.Loc.Start || fetch.Loc.End > len(sql) {\n\t\t\treturn \"\", errors.Errorf(\"invalid FETCH position %d:%d\", fetch.Loc.Start, fetch.Loc.End)\n\t\t}\n\t\tif hasOracleFetchKeyword(sql, fetch.Loc) {\n\t\t\treturn sql, nil\n\t\t}\n\t\treturn sql[:fetch.Loc.End] + fmt.Sprintf(\" FETCH NEXT %d ROWS ONLY\", limitCount) + sql[fetch.Loc.End:], nil\n\t}\n\tif fetch.Percent {\n\t\treturn \"\", errors.Errorf(\"cannot rewrite PERCENT FETCH expression\")\n\t}\n\n\texistingLimit := extractOracleFetchCount(fetch.Count)\n\tif existingLimit > 0 && existingLimit <= limitCount {\n\t\treturn sql, nil\n\t}\n\n\tloc := oracleast.NodeLoc(fetch.Count)\n\tloc = trimOracleLocSpace(sql, loc)\n\tif loc.Start >= 0 && loc.End > loc.Start && loc.End <= len(sql) {\n\t\tif existingLimit <= 0 {\n\t\t\treturn \"\", errors.Errorf(\"cannot rewrite non-constant FETCH expression\")\n\t\t}\n\t\treturn sql[:loc.Start] + fmt.Sprintf(\"%d\", limitCount) + sql[loc.End:], nil\n\t}\n\treturn \"\", errors.Errorf(\"cannot rewrite FETCH expression\")\n}\n","sourceCodeStart":271,"sourceCodeEnd":307,"githubUrl":"https://github.com/bytebase/bytebase/blob/1870550677fe08f0d2a78c07acd27541464eb945/backend/plugin/db/oracle/query.go#L271-L307","documentation":"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.","triggerScenarios":"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.","commonSituations":"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.","solutions":["Rewrite the SQL to use a fixed row count (FETCH FIRST n ROWS ONLY) instead of PERCENT before running the rewriter","Compute the row count yourself in a wrapping subquery (e.g. SELECT COUNT(*) then FETCH NEXT n ROWS ONLY)","Use ROW_NUMBER() OVER (...) with a computed cutoff instead of PERCENT FETCH","Skip PERCENT queries in tooling that cannot rewrite them, surfacing a clear unsupported-feature message"],"exampleFix":"// before\nSELECT * FROM emp FETCH FIRST 50 PERCENT ROWS ONLY;\n// after (pre-compute 50% of rows as N)\nSELECT * FROM emp ORDER BY emp_id FETCH NEXT :N ROWS ONLY;","handlingStrategy":"fallback","validationCode":"if stmt.FetchFirstClause != nil && stmt.FetchFirstClause.Percent {\n    // skip or handle PERCENT queries separately before rewriting\n    return sql, ErrPercentFetchUnsupported\n}","typeGuard":"func isPercentFetch(fetch *oracleast.FetchFirstClause) bool { return fetch != nil && fetch.Percent }","tryCatchPattern":"rewritten, err := rewriteOracleSelectFetch(ctx, sql, stmt, limit)\nif err != nil && strings.Contains(err.Error(), \"PERCENT FETCH\") {\n    // surface an unsupported-feature message instead of failing the whole batch\n    return sql, fmt.Errorf(\"query uses PERCENT FETCH, manual rewrite required: %w\", err)\n}","preventionTips":["Ban PERCENT FETCH in SQL review policies","Use fixed row counts or ROW_NUMBER() window functions for percentage pagination","Document the limitation for teams migrating T-SQL TOP(n) PERCENT queries"],"tags":["oracle","sql-rewrite","percent-fetch","unsupported-feature"],"backgroundTag":"unsupported-operation","analyzedSha":"1870550677fe08f0d2a78c07acd27541464eb945","analyzedAt":"2026-09-06T21:16:13.665Z","contentChangedAt":"2026-09-06T21:16:13.665Z","schemaVersion":2},"datasetVersion":"2026-09-14T05:17:10.506Z"}