Forum Stats

  • 3,825,244 Users
  • 2,260,486 Discussions
  • 7,896,465 Comments

Discussions

Support options in ORDER BY clause of MATCH_RECOGNIZE

mathguy
mathguy Member Posts: 10,539 Blue Diamond
edited Jul 31, 2020 6:33PM in Database Ideas - Ideas

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.

GregVRanagalSven W.berx
4 votes

Active · Last Updated

Comments