Using WM_CONCAT in OceanBase 3.x: Restoring Order with NO_REWRITE Hint

Problem description : The source Oracle database uses the XMLAGG function to concatenate column values. OceanBase 3.x does not support XMLAGG, so the migration replaces it with WM_CONCAT. After the initial conversion the concatenated result is not ordered as expected.

Database versions :

OceanBase 3.2.3

Oracle 11g

Analysis : The original Oracle SQL is:

SELECT xmlagg(xmlparse(content tr.inner_rule_file_name || ','))
       order by tr.inner_rule_file_name
  .getclobval()
FROM tol_report_user tr;

In Oracle this returns a CLOB . Because OceanBase 3.x lacks XMLAGG, the query is rewritten to use WM_CONCAT:

SELECT WM_CONCAT(rt.inner_rule_file_name)
FROM (
    SELECT inner_rule_file_name
    FROM tol_report_user
    ORDER BY inner_rule_file_name) AS rt;

Although the sub‑query sorts the rows, the final concatenated string is still unordered.

Reproduction steps :

-- create test table
CREATE TABLE A (
    id   NUMBER,
    name VARCHAR2(1000),
    age  NUMBER
);
-- insert test data
INSERT INTO A VALUES (1, '001.txt', 29);
INSERT INTO A VALUES (2, '002.pdf', 19);
INSERT INTO A VALUES (3, '003.ppt', 19);
INSERT INTO A VALUES (3, '文件.ppt', 19);
COMMIT;
-- test WM_CONCAT
SELECT WM_CONCAT(a1.name) FROM (
    SELECT name FROM A ORDER BY name) a1;

The output is a comma‑separated list in the original insertion order, confirming that the ORDER BY inside the sub‑query is ignored during concatenation.

Execution plan comparison : Both the plain WM_CONCAT query and the one with the ordered sub‑query produce identical plans without a SORT operator, explaining why the ordering is lost.

Hint intervention : Adding the NO_REWRITE hint forces the optimizer to keep the ordering step:

SELECT /*+ NO_REWRITE */ WM_CONCAT(a1.n) AS a
FROM (
    SELECT name AS n FROM A ORDER BY name) a1;

The result is now correctly ordered and the extended explain plan shows a SORT operator.

Conclusion : In OceanBase 3.x the WM_CONCAT function triggers a rewrite that removes the ORDER BY clause, causing unordered results. Adding a NO_REWRITE hint prevents this rewrite and restores the expected order.

Solution :

SELECT /*+ NO_REWRITE */ WM_CONCAT(rt.inner_rule_file_name)
FROM (
    SELECT inner_rule_file_name
    FROM tol_report_user
    ORDER BY inner_rule_file_name) AS rt;

What about OceanBase 4.x? The newer 4.x version adds native support for XMLAGG and has fixed the WM_CONCAT rewrite issue, so the hint is no longer required.