Support options in ORDER BY clause of MATCH_RECOGNIZE

mathguy

As documented, MATCH_RECOGNIZE only supports the default ordering by the column or columns listed in the ORDER BY (sub)clause.

In practice, MATCH_RECOGNIZE works (and it works as expected) when ORDER BY is used with the options ASC/DESC and NULLS FIRST/NULLS LAST. ASC NULLS LAST is the default, and the only combination documented  to be supported. In addition, there is at least one issue I am aware of, caused by non-default options in the ORDER BY clause of MATCH_RECOGNIZE.

Namely: if a query uses a non-default ORDER BY in MATCH_RECOGNIZE, and then at the end the query has its own (global) ORDER BY clause using different options, the global ORDER BY is simply ignored. This is discussed in this thread on the SQL and PL/SQL forum:   Weird bug: MATCH_RECOGNIZE followed by ORDER BY, wrong output but only in one particular case

The last reply in that thread is from a forum member who logged this as a bug. It is entirely possible that the bug filing is (or was) dismissed offhand, since the behavior is as documented. If so, then what we need is an enhancement request, which I am submitting here.

GregV

The last reply in that thread is from a forum member who logged this as a bug

I am he

Well the Bug 30645590 is still open, and its status is "11 - Code/Hardware Bug (Response/Resolution)" and was last updated on 3rd July (no real update in fact, just an internal update I guess). No "Fixed in version" information has been filled until now.

I've kept the underlying SR open. In the SR I was already suggesting qualifying it as an ER, but the analyst filed it as a code bug.

mathguy

As communicated here: https://community.oracle.com/tech/developers/discussion/4305402/weird-bug-match-recognize-followed-by-order-by-wrong-output-but-only-in-one-particular-case#latest (latest comment, from GregV), this bug/enhancement request seems to have been addressed. Note that the Oracle documentation for the row_pattern_order_by clause (even for Release 21) does not reflect this change: https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/SELECT.html#GUID-CFA006CA-6FF1-4972-821E-6996142A51C6