MATCH_RECOGNIZE: aggregates in DEFINE, union variables, CLASSIFIER(X), and a required DEFINE
The Issue & Context
The MATCH_RECOGNIZE clause in DuckDB lacked full compliance with the ISO/IEC 19075-5 standard, missing support for aggregates in DEFINE, union variables, CLASSIFIER functions, and required DEFINE clauses, causing incomplete pattern matching capabilities.
Root Cause Analysis
The implementation previously did not handle running aggregates within DEFINE conditions, union variables that can be left and re-entered, or the CLASSIFIER function with PREV/NEXT navigation, and did not enforce mandatory DEFINE clauses when variables are declared.
Solution Strategy & Architectural Pattern
Added support for aggregates in DEFINE by maintaining running states and rewinds, implemented union variables usage in DEFINE and AFTER MATCH SKIP TO clauses, enabled CLASSIFIER(X) with PREV/NEXT navigation, enforced required DEFINE clauses, optimized row-scoped condition referencing to improve varchar navigation and aggregate evaluation performance, and ensured serialization of aggregate bind data to pass verification tests.
Key Takeaway for Contributors
This PR demonstrates the importance of rigorous standard compliance in pattern matching features and shows how careful state management and serialization can improve both correctness and performance in complex SQL extensions.