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 |
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 |