Specification of Row Pattern Recognition in the SQL Standard and its Implementations

Abstract

Recently, the SQL standardization Committee published a specification for support of the concept called Row Pattern Recognition in SQL. That way, the focus for the members of the Committee turned back to a kernel issue of the language, after standardizing the storage and manipulation of data formats, such as XML and JSON. In this paper we discuss the specified features using several examples and show to what extent different relational database systems as well as Data Analytics tools have integrated them. At the end of the paper, we describe the main inaccuracies of the proposal and the ways how to solve them. From our point of view, the following should be modified in one of the future proposals of the standard in relation to this concept: (a) Naming of several pattern navigation operations is inappropriate and should be changed; (b) The concept of implicit definition for row pattern variables in DEFINE should be changed to explicit; (c) The set of existing functions should be extended.