[BUG] Oracle operation-log list limits rows before ordering the latest records #7739

Current behavior

The Oracle-specific selectLimit applies WHERE ROWNUM <= limit in the same query block as ORDER BY operation_time DESC. This does not guarantee the latest N operation records: Oracle can choose a limited subset first and sort only that subset. The non-Oracle statement instead orders first and uses LIMIT.

Location / caller

• operation-record-log-sqlmap.xml:58–67
• GET /operation-record/log/list calls OperationRecordLogServiceImpl.list().
• That service calls selectLimit(username, dashboardProperties.getRecordLogLimit()), with a username filter for non-admin users.

Vendor evidence / scenario

Oracle's ROWNUM reference explicitly warns that if ORDER BY follows ROWNUM in the same query, the selected rows are reordered and results depend on the access path. It prescribes an ordered subquery with ROWNUM in the outer query to force ordering before limiting. The official page was retrieved during review.

For a log table with more than N matching rows and an access path that visits older rows first, the current query can return those older N rows sorted, omitting newer rows. Different optimizer/index choices may mask the error; the result is not a deterministic latest-N query.

Validation is current mapper/caller inspection plus official Oracle semantics; no Oracle execution or particular optimizer plan was measured.

Impact / suggested fix

The dashboard/recent-operation list may omit the newest actions on Oracle. Put the operator filter and descending order in an inner query, then apply ROWNUM in an outer query, or use an equivalent version-compatible top-N construct. Preserve the existing configured limit and per-operator filtering.

Related existing work

#6675 concerns a separate selectByCondition search's scan/paging performance; #3489 is original Oracle support. The three-argument-CONCAT search issue is also separate: this report concerns the Oracle-specific limited dashboard list returning the wrong records, even without a search keyword.

Reviewed revision

Apache ShenYu 2.7.2-SNAPSHOT, master@66d7d954ca37d651a5ad916f988a8081157a7ab4. Priority P2 / Medium. Unit-test and CI issue categories excluded.