Repository: sql-formatter-org/sql-formatter
Pull request #333 (2022-07-19 09:14:56)

Improve Trino row pattern formatting
and also remove `` ` `` quotes, because those were only in the grammar definition so that the parser can tell users in an error message that Trino doesn't support them.

verhovsky: Similarly to me incorrectly including `` ` `` quotes, https://github.com/sql-formatter-org/sql-formatter/wiki/identifiers should actually say "(no differences)" for Trino. Identifiers starting with a number are only [in the grammar](https://github.com/trinodb/trino/blob/0beb0e0ab93315976b4d5ef49fe555a3139cc12a/core/trino-parser/src/main/antlr4/io/trino/sql/parser/SqlBase.g4#L1179-L1181) so that the parser can raise a better error message.
nene: Updated the Wiki page for Trino identifiers: https://github.com/sql-formatter-org/sql-formatter/wiki/Identifiers

Patch:
diff --git a/src/languages/trino/trino.formatter.ts b/src/languages/trino/trino.formatter.ts
index 5f01585b..a7939cf4 100644
--- a/src/languages/trino/trino.formatter.ts
+++ b/src/languages/trino/trino.formatter.ts
@@ -86,6 +86,16 @@ const reservedCommands = [
   'SHOW ROLE GRANTS',
   'SHOW FUNCTIONS',
   'SHOW SESSION',
+
+  // MATCH_RECOGNIZE
+  'MATCH_RECOGNIZE',
+  'MEASURES',
+  'ONE ROW PER MATCH',
+  'ALL ROWS PER MATCH',
+  'AFTER MATCH',
+  'PATTERN',
+  'SUBSET',
+  'DEFINE',
 ];
 
 // https://github.com/trinodb/trino/blob/432d2897bdef99388c1a47188743a061c4ac1f34/core/trino-parser/src/main/antlr4/io/trino/sql/parser/SqlBase.g4#L231-L235
@@ -126,6 +136,7 @@ const reservedJoins = [
 
 export default class TrinoFormatter extends Formatter {
   // https://trino.io/docs/current/functions/list.html#id1
+  // https://trino.io/docs/current/sql/match-recognize.html#row-pattern-syntax
   static operators = ['||', '->'];
 
   tokenizer() {
@@ -136,10 +147,14 @@ export default class TrinoFormatter extends Formatter {
       reservedDependentClauses: ['WHEN', 'ELSE'],
       reservedKeywords: keywords,
       reservedFunctionNames: functions,
-      openParens: ['(', '['],
-      closeParens: [')', ']'],
+      // https://trino.io/docs/current/sql/match-recognize.html#row-pattern-syntax
+      openParens: ['(', '[', '{', '{-'],
+      closeParens: [')', ']', '}', '-}'],
+      // https://trino.io/docs/current/language/types.html#string
+      // https://trino.io/docs/current/language/types.html#varbinary
       stringTypes: [{ quote: "''", prefixes: ['X', 'U&'] }],
-      identTypes: ['""', '``'],
+      // https://trino.io/docs/current/language/reserved.html
+      identTypes: ['""'],
       positionalParams: true,
       operators: TrinoFormatter.operators,
     });
diff --git a/src/languages/trino/trino.functions.ts b/src/languages/trino/trino.functions.ts
index 8ff1b0bc..dece1ade 100644
--- a/src/languages/trino/trino.functions.ts
+++ b/src/languages/trino/trino.functions.ts
@@ -64,6 +64,7 @@ export const functions = flatKeywordList({
     'CHECKSUM',
     'CHR',
     'CLASSIFY',
+    'CLASSIFIER',
     'COALESCE',
     'CODEPOINT',
     'COLOR',
@@ -114,6 +115,7 @@ export const functions = flatKeywordList({
     'EXTRACT',
     'FEATURES',
     'FILTER',
+    'FIRST',
     'FIRST_VALUE',
     'FLATTEN',
     'FLOOR',
@@ -179,6 +181,7 @@ export const functions = flatKeywordList({
     'JSON_VALUE',
     'KURTOSIS',
     'LAG',
+    'LAST',
     'LAST_DAY_OF_MONTH',
     'LAST_VALUE',
     'LEAD',
@@ -214,6 +217,7 @@ export const functions = flatKeywordList({
     'MAP_UNION',
     'MAP_VALUES',
     'MAP_ZIP_WITH',
+    'MATCH_NUMBER',
     'MAX',
     'MAX_BY',
     'MD5',
@@ -229,6 +233,7 @@ export const functions = flatKeywordList({
     'MULTIMAP_FROM_ENTRIES',
     'MURMUR3',
     'NAN',
+    'NEXT',
     'NGRAMS',
     'NONE_MATCH',
     'NORMALIZE',
@@ -244,10 +249,12 @@ export const functions = flatKeywordList({
     'PARSE_DATETIME',
     'PARSE_DURATION',
     'PERCENT_RANK',
+    'PERMUTE',
     'PI',
     'POSITION',
     'POW',
     'POWER',
+    'PREV',
     'QDIGEST_AGG',
     'QUARTER',
     'RADIANS',
diff --git a/src/languages/trino/trino.keywords.ts b/src/languages/trino/trino.keywords.ts
index 4dd08adb..8196aaac 100644
--- a/src/languages/trino/trino.keywords.ts
+++ b/src/languages/trino/trino.keywords.ts
@@ -150,7 +150,6 @@ export const keywords = flatKeywordList({
     'PATH',
     'PATTERN',
     'PER',
-    'PERMUTE',
     'PRECEDING',
     'PRECISION',
     'PREPARE',
diff --git a/src/lexer/Tokenizer.ts b/src/lexer/Tokenizer.ts
index c2760035..21d96290 100644
--- a/src/lexer/Tokenizer.ts
+++ b/src/lexer/Tokenizer.ts
@@ -28,9 +28,9 @@ interface TokenizerOptions {
   // Types of quotes to use for variables
   variableTypes?: regexTypes.VariableType[];
   // Open-parenthesis characters
-  openParens?: ('(' | '[' | '{')[];
+  openParens?: ('(' | '[' | '{' | '{-')[];
   // Close-parenthesis characters
-  closeParens?: (')' | ']' | '}')[];
+  closeParens?: (')' | ']' | '}' | '-}')[];
   // True to allow for positional "?" parameter placeholders
   positionalParams?: boolean;
   // Prefixes for numbered parameter placeholders to support, e.g. :1, :2, :3
diff --git a/src/lexer/regexUtil.ts b/src/lexer/regexUtil.ts
index 57899050..2382edd1 100644
--- a/src/lexer/regexUtil.ts
+++ b/src/lexer/regexUtil.ts
@@ -21,7 +21,7 @@ export const escapeParen = (paren: string): string => {
     return escapeRegExp(paren);
   } else {
     // longer word
-    return '\\b' + paren + '\\b';
+    return '\\b' + escapeRegExp(paren) + '\\b';
   }
 };
 


Test patch:
diff --git a/test/trino.test.ts b/test/trino.test.ts
index ec985865..0bbbadbe 100644
--- a/test/trino.test.ts
+++ b/test/trino.test.ts
@@ -24,8 +24,8 @@ describe('TrinoFormatter', () => {
   supportsComments(format);
   supportsCreateTable(format);
   supportsDeleteFrom(format);
-  supportsStrings(format, ["''", "X''"]);
-  supportsIdentifiers(format, ['""', '``']);
+  supportsStrings(format, ["''", "X''", "U&''"]);
+  supportsIdentifiers(format, ['""']);
   supportsBetween(format);
   supportsOperators(format, TrinoFormatter.operators, ['AND', 'OR']);
   supportsArrayLiterals(format);
@@ -51,6 +51,56 @@ describe('TrinoFormatter', () => {
     `);
   });
 
+  it('formats row PATTERN()s', () => {
+    const result = format(`
+      SELECT * FROM orders MATCH_RECOGNIZE(
+        PARTITION BY custkey
+        ORDER BY orderdate
+        MEASURES
+                  A.totalprice AS starting_price,
+                  LAST(B.totalprice) AS bottom_price,
+                  LAST(U.totalprice) AS top_price
+        ONE ROW PER MATCH
+        AFTER MATCH SKIP PAST LAST ROW
+        PATTERN ((A | B){5} {- C+ D+ -} E+)
+        SUBSET U = (C, D)
+        DEFINE
+                  B AS totalprice < PREV(totalprice),
+                  C AS totalprice > PREV(totalprice) AND totalprice <= A.totalprice,
+                  D AS totalprice > PREV(totalprice)
+        )
+    `);
+    expect(result).toBe(dedent`
+      SELECT
+        *
+      FROM
+        orders
+      MATCH_RECOGNIZE
+        (
+          PARTITION BY
+            custkey
+          ORDER BY
+            orderdate
+          MEASURES
+            A.totalprice AS starting_price,
+            LAST(B.totalprice) AS bottom_price,
+            LAST(U.totalprice) AS top_price
+          ONE ROW PER MATCH
+          AFTER MATCH
+            SKIP PAST LAST ROW
+          PATTERN
+            ((A | B) {5} {- C + D + -} E +)
+          SUBSET
+            U = (C, D)
+          DEFINE
+            B AS totalprice < PREV(totalprice),
+            C AS totalprice > PREV(totalprice)
+            AND totalprice <= A.totalprice,
+            D AS totalprice > PREV(totalprice)
+        )
+    `);
+  });
+
   it('formats basic ALTER TABLE statements', () => {
     const result = format(`
       ALTER TABLE people RENAME TO persons;