CAN/CSA-ISO/IEC TR 19075-5-18 — Row Pattern Recognition in SQL: A Comprehensive Technical Guide

Scope and Purpose of CAN/CSA-ISO/IEC TR 19075-5-18

CAN/CSA-ISO/IEC TR 19075-5-18 is the Canadian adoption of the international technical report ISO/IEC TR 19075-5:2016, titled “Information technology — Database languages — SQL technical reports — Part 5: Row pattern recognition in SQL”. Published by the Canadian Standards Association (CSA Group) in 2018, this document provides an in-depth technical overview of the row pattern recognition capabilities added to the SQL language. It is intended for database implementers, application developers, and data analysts who need to understand, design, or evaluate SQL-based pattern matching features, particularly the MATCH_RECOGNIZE clause.

As a Technical Report (TR) rather than a full International Standard, it serves as an informative guide that explains the syntax, semantics, and design rationales behind row pattern recognition. It does not itself define normative requirements but illuminates the technical foundations that later became part of the SQL:2016 standard (ISO/IEC 9075:2016) and its subsequent editions. The Canadian adoption ensures that stakeholders in Canada can reference a locally authored document aligned with international practice.

Technical Requirements and Core Concepts

Row Pattern Recognition in SQL

The central concept covered in CAN/CSA-ISO/IEC TR 19075-5-18 is order-sensitive row pattern matching — the ability to detect sequences of rows that match a specified pattern within a result set. This is accomplished primarily through the MATCH_RECOGNIZE clause, which can be used in a FROM clause to transform a base table or view into a pattern-annotated relational expression.

Key Elements of the Pattern Matching Model

• Partitioning and Ordering: Data is divided into partitions by the PARTITION BY clause and ordered within each partition using the ORDER BY clause.
• Pattern Definition: The PATTERN clause specifies a regular expression-like sequence of row pattern variables (e.g., PATTERN ( A B+ C )), with quantifiers, alternation, grouping, and optional start/end anchors.
• Row Pattern Variables: Each pattern variable corresponds to a class of rows defined by a condition in the DEFINE clause. These conditions are boolean predicates evaluated against the current row and, optionally, preceding or following rows.
• Measures and Output: The MEASURES clause computes expressions (e.g., aggregate, positional, or conditional) over matched rows, with the ability to reference row pattern variables and their attributes.
• Result Row Modes: The ONE ROW PER MATCH, ALL ROWS PER MATCH, and other modes control how the matched subsequence is reflected in the output.

Sample Pattern Matching Scenario

Consider a table of stock trades with columns timestamp, symbol, and price. The following query identifies occurrences of a “head-and-shoulders” pattern:

SELECT * FROM trades MATCH_RECOGNIZE ( PARTITION BY symbol ORDER BY timestamp MEASURES MATCH_NUMBER() AS match_num, RUNNING COUNT(up.total) AS up_count ONE ROW PER MATCH PATTERN (up peak down peak2 down2) DEFINE up AS price > PREV(price), peak AS price > PREV(price) AND price > LAG(price, 2), down AS price < PREV(price), peak2 AS price > PREV(price) AND price > LAG(price, 2), down2 AS price < PREV(price) );

Implementation Highlights for Database Systems

CAN/CSA-ISO/IEC TR 19075-5-18 discusses several implementation considerations that database vendors and advanced users should be aware of:

• Efficient Pattern Matching Engine: The TR describes a non‑deterministic finite automaton (NFA) approach to matching, which allows for greedy and reluctant quantifiers. Implementations must carefully manage state space to avoid exponential blow‑up in complex patterns.
• Support for Empty Matches and Zero‑Length Patterns: The report formally defines behavior for empty matches, which must be handled to prevent infinite loops and ensure predictable results.
• Integration with SQL’s Streaming Extensions: Although MATCH_RECOGNIZE is defined for static tables, the report outlines how the same semantics can be applied to streaming data, forming the basis for the MATCH_RECOGNIZE clause in SQL streaming standards (e.g., ANSI SQL 2016 for streams).
• Row Pattern Navigation: Functions like PREV(), NEXT(), FIRST(), and LAST() are defined to navigate within the matched sequence, with specific rules for their behavior when pattern variables span multiple rows.

Compliance, Testing, and Conformance Notes

Because CAN/CSA-ISO/IEC TR 19075-5-18 is a Technical Report, it does not impose formal conformance requirements. However, it serves as an essential reference for evaluating whether a database system correctly implements the MATCH_RECOGNIZE feature as specified in ISO/IEC 9075 (SQL standard). Adopting organizations in Canada may use this report to:

• Assess Feature Coverage: Compare a product’s implementation against the descriptions in the TR to identify missing or incorrectly implemented capabilities.
• Develop Test Suites: Use the examples and edge‑case descriptions in the report to design conformance tests for pattern matching.
• Guide Procurement: Reference the TR when specifying required SQL pattern-matching support in request‑for‑proposal documents.

Frequently Asked Questions

Document prepared for informational purposes. Refer to the official publication of CAN/CSA-ISO/IEC TR 19075-5-18 for the complete authoritative text. Year of adoption: 2026.