oracle-databaseoracle12cnullifmatch-recognize

NULLIF of FINAL SUM Doubled when using MATCH_RECOGNIZE


When I run the following code, I would expect b1 and b2 to be equal, however, b2 is doubled. Am I doing something wrong? Is this a bug in the database? We're running Oracle 12c (12.2.0.1.0).

WITH TBL AS
  (
  SELECT 1 a, 1 b FROM DUAL UNION ALL
  SELECT 1 a, 2 b FROM DUAL UNION ALL
  SELECT 1 a, 3 b FROM DUAL UNION ALL
  SELECT 1 a, 4 b FROM DUAL
  )
SELECT
  *
FROM
  TBL
MATCH_RECOGNIZE
  (
  PARTITION BY
    a
  ORDER BY
    b
  MEASURES
    FINAL SUM(b) b1,
    NULLIF(FINAL SUM(b), 0) b2
  ALL ROWS PER MATCH WITH UNMATCHED ROWS
  AFTER MATCH SKIP PAST LAST ROW
  PATTERN
    (C*)
  DEFINE
    C AS B > 0
  ) mr

Result:

| A | B | B1 | B2 |
|---|---|----|----|
| 1 | 1 | 10 | 20 |
| 1 | 2 | 10 | 20 |
| 1 | 3 | 10 | 20 |
| 1 | 4 | 10 | 20 |

Solution

  • The problem seems to be with NULLIF when I converted the same into it's logical equivalent and it is working fine CASE WHEN expr1 = expr 2 THEN NULL ELSE expr1 END

    WITH TBL AS
          (
          SELECT 1 a, 1 b FROM DUAL UNION ALL
          SELECT 1 a, 2 b FROM DUAL UNION ALL
          SELECT 1 a, 3 b FROM DUAL UNION ALL
          SELECT 1 a, 4 b FROM DUAL
          )
        SELECT
          *
        FROM
          TBL
        MATCH_RECOGNIZE
          (
          PARTITION BY
            a
          ORDER BY
            b
          MEASURES
            FINAL SUM(b) b1,
           CASE WHEN FINAL SUM(b)=0 THEN NULL ELSE FINAL SUM(b) END b2
          ALL ROWS PER MATCH WITH UNMATCHED ROWS
          AFTER MATCH SKIP PAST LAST ROW
          PATTERN
            (C*)
          DEFINE
            C AS B > 0
          ) mr 
    

    Result

        | A | B | B1 | B2 |
        |---|---|----|----|
        | 1 | 1 | 10 | 10 |
        | 1 | 2 | 10 | 10 |
        | 1 | 3 | 10 | 10 |
        | 1 | 4 | 10 | 10 |