Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback, please email oracle-forums_moderators_us@oracle.com

Weird bug in DEFINE clause of MATCH_RECOGNIZE using BETWEEN comparison

mathguyOct 27 2023 — edited Oct 27 2023

This came up in a (production?) question posted on this forum today. That question is irrelevant for my post, except for the fact that it proves the issue isn't made up.

It appears that in some cases, the BETWEEN operator in the DEFINE clause of MATCH_RECOGNIZE doesn't work correctly. Here is a very small example for testing. I ran this in Oracle 12.2, but then, to see if it has been fixed since, I ran it again in Oracle 23, with the same (incorrect) result.

Test data: a single numerical column, only two rows - with values 1 and 10 respectively.

create table t (n number);
insert into t(n) values (1);
insert into t(n) values (10);
commit;

Query (pay attention to the condition in DEFINE):

select cn, pn, ratio
from   t
match_recognize(
  order    by n
  measures c.n as cn, p.n as pn, c.n / p.n as ratio
  pattern  ( p c )
  define   c as n / p.n between 1 and 3
);

Output:

        CN         PN      RATIO
---------- ---------- ----------
        10          1         10

The query should return no rows; the ratio is 10, which is not between 1 and 3.

If I change the condition to

  define   c as n / p.n between 2 and 3

then the query returns no rows (the correct output).

If I change it to

  define c as n / p.n between 1 and 1

the query returns the wrong result again (it does return the same row as earlier). Clearly (??) the runtime thinks ā€œnā€ in the numerator of the fraction is the same as p.n; perhaps at this stage it thinks P refers to the current row, and not to the one already marked as P? But if that was the reason, then the following modification shouldn't work either; the modified query produces the correct output (no rows), which means that the explanation for the bug must be more complex.

select cn, pn, ratio
from   t
match_recognize(
  order    by n
  measures c.n as cn, p.n as pn, c.n / p.n as ratio
  pattern  ( p c )
  define   c as n between p.n and p.n
);

Of course, the following even simpler condition is equivalent to n/p.n between 1 and 1; yet it also produces the correct output (no rows); again, the simple explanation that MATCH_RECOGNIZE is confused and it thinks that P refers to the current row doesn't hold water.

select cn, pn, ratio
from   t
match_recognize(
  order    by n
  measures c.n as cn, p.n as pn, c.n / p.n as ratio
  pattern  ( p c )
  define   c as n = p.n
);

Has anyone run into this before? If not, it is probably worth logging as a bug; I can't do that myself, as a non-(paying customer) of Oracle.

Comments
Post Details
Added on Oct 27 2023
13 comments
217 views