match_recognize: Logical navigation function FIRST yields unexpected output #26981
Description
Hi everyone,
the query
WITH t (rid, price) AS (
SELECT * FROM (VALUES
(1, 1009),
(2, 1019),
(3, 1029),
(4, 1039),
(5, 1049),
(6, 1059),
(7, 1069))
)
SELECT rid, first_price, first_price1, final_first_price1
FROM t MATCH_RECOGNIZE(
ORDER BY rid
MEASURES
FIRST(price) AS first_price,
FIRST(price, 1) AS first_price1,
FINAL FIRST(price, 1) AS final_first_price1
ALL ROWS PER MATCH
PATTERN (A B+)
DEFINE B AS B.price < 1050
);yields the following output in Trino:
rid | first_price | first_price1 | final_first_price1
-----+-------------+--------------+--------------------
1 | 1009 | 1019 | 1019
2 | 1009 | 1019 | 1019
3 | 1009 | 1019 | 1019
4 | 1009 | 1019 | 1019
5 | 1009 | 1019 | 1019
(5 rows)
The same query in Oracle
WITH t (rid, price) AS (
SELECT 1, 1009 FROM dual UNION ALL
SELECT 2, 1019 FROM dual UNION ALL
SELECT 3, 1029 FROM dual UNION ALL
SELECT 4, 1039 FROM dual UNION ALL
SELECT 5, 1049 FROM dual UNION ALL
SELECT 6, 1059 FROM dual UNION ALL
SELECT 7, 1069 FROM dual
)
SELECT rid, first_price, first_price1, final_first_price1
FROM t MATCH_RECOGNIZE(
ORDER BY rid
MEASURES
FIRST(price) AS first_price,
FIRST(price, 1) AS first_price1,
FINAL FIRST(price, 1) AS final_first_price1
ALL ROWS PER MATCH
PATTERN (A B+)
DEFINE B AS B.price < 1050
);yields a different output:
RID FIRST_PRICE FIRST_PRICE1 FINAL_FIRST_PRICE1
--- ----------- ------------ ------------------
1 1009 1019
2 1009 1019 1019
3 1009 1019 1019
4 1009 1019 1019
5 1009 1019 1019
The output Trino produces is the output Oracle produces when using FINAL FIRST(price, 1).
Is this an intended difference to Oracle? I did understand that unless the FINAL keyword is used only the part of the match up to the current row is known and therefore will yield NULL values if one refers to future rows of a match as with the offset of 1. Is my notion wrong here?
I work with Trino 476 in Docker.