Repository: sql-formatter-org/sql-formatter
Pull request #531 (2022-11-21 16:17:29)

Extend SingleStoreDB support
This MR extends support for SingleStoreDB by adding new path and casting operators.

SingleStoreDB supports 3 operators for accessing fields in a JSON object ([docs link](https://docs.singlestore.com/managed-service/en/csreate-a-database/physical-database-schema-design/procedures-for-physical-database-schema-design/using-json.html#dml--accessing-data-in-json-columns)):
- `::` to retrieve string in quotes ([doc](https://docs.singlestore.com/managed-service/en/create-a-database/physical-database-schema-design/procedures-for-physical-database-schema-design/using-json.html#accessing-fields-in-a-json-object));
- `::$` to retrieve and convert to an SQL string ([doc](https://docs.singlestore.com/managed-service/en/create-a-database/physical-database-schema-design/procedures-for-physical-database-schema-design/using-json.html#converting-a-json-string-value-into-a-sql-string));
- `::%` to retrieve and convert to an SQL number or double ([doc](https://docs.singlestore.com/managed-service/en/create-a-database/physical-database-schema-design/procedures-for-physical-database-schema-design/using-json.html#converting-a-json-number-or-boolean-value-into-a-sql-double)).

SingleStoreDB also has 2 cast operators, `:>` and `!:>`, that were not contemplated before ([docs link](https://docs.singlestore.com/managed-service/en/reference/sql-reference/conditional-functions/cast-or-convert.html#cast-operators----and----)).

Tests for these cases were also added to the SingleStoreDB test file.



Patch:
diff --git a/package.json b/package.json
index fccdd539..536aa690 100644
--- a/package.json
+++ b/package.json
@@ -1,6 +1,6 @@
 {
   "name": "sql-formatter",
-  "version": "12.0.2",
+  "version": "12.0.0",
   "description": "Format whitespace in a SQL query to make it more readable",
   "license": "MIT",
   "main": "dist/sql-formatter.min.cjs",
diff --git a/src/index.ts b/src/index.ts
index 4ad1fac7..76b57732 100644
--- a/src/index.ts
+++ b/src/index.ts
@@ -1,6 +1,5 @@
 export { supportedDialects, format, formatDialect } from './sqlFormatter.js';
 export { expandPhrases } from './expandPhrases.js';
-export { ConfigError } from './validateConfig.js';
 
 // Intentionally use "export *" syntax here to make sure when adding a new SQL dialect
 // we wouldn't forget to expose it in our public API.
@@ -21,3 +20,4 @@ export type {
   FormatOptions,
 } from './FormatOptions.js';
 export type { DialectOptions } from './dialect.js';
+export type { ConfigError } from './validateConfig.js';
diff --git a/src/languages/singlestoredb/singlestoredb.formatter.ts b/src/languages/singlestoredb/singlestoredb.formatter.ts
index 447251e3..1188f96e 100644
--- a/src/languages/singlestoredb/singlestoredb.formatter.ts
+++ b/src/languages/singlestoredb/singlestoredb.formatter.ts
@@ -254,10 +254,27 @@ export const singlestoredb: DialectOptions = {
       { quote: '``', prefixes: ['@'], requirePrefix: true },
     ],
     lineCommentTypes: ['--', '#'],
-    operators: [':=', '&', '|', '^', '~', '<<', '>>', '<=>', '&&', '||'],
+    operators: [
+      ':=',
+      '&',
+      '|',
+      '^',
+      '~',
+      '<<',
+      '>>',
+      '<=>',
+      '&&',
+      '||',
+      '::',
+      '::$',
+      '::%',
+      ':>',
+      '!:>',
+    ],
     postProcess,
   },
   formatOptions: {
+    alwaysDenseOperators: ['::', '::$', '::%'],
     onelineClauses,
   },
 };
diff --git a/src/parser/createParser.ts b/src/parser/createParser.ts
index 0d3566f2..c0e1924c 100644
--- a/src/parser/createParser.ts
+++ b/src/parser/createParser.ts
@@ -39,7 +39,7 @@ export function createParser(tokenizer: Tokenizer): Parser {
         // but I haven't found a way to get this info from Nearley :(
         throw new Error('Parse error: Invalid SQL');
       } else {
-        throw new Error(`Parse error: Ambiguous grammar\n${JSON.stringify(results, undefined, 2)}`);
+        throw new Error('Parse error: Ambiguous grammar');
       }
     },
   };
diff --git a/src/parser/grammar.ne b/src/parser/grammar.ne
index 91a0d4d6..29339dad 100644
--- a/src/parser/grammar.ne
+++ b/src/parser/grammar.ne
@@ -38,19 +38,6 @@ const addComments = (node: AstNode, { leading, trailing }: CommentAttachments):
   return node;
 };
 
-const addCommentsToArray = (nodes: AstNode[], { leading, trailing }: CommentAttachments): AstNode[] => {
-  if (leading?.length) {
-    const [first, ...rest] = nodes;
-    nodes = [addComments(first, { leading }), ...rest];
-  }
-  if (trailing?.length) {
-    const lead = nodes.slice(0, -1);
-    const last = nodes[nodes.length-1];
-    nodes = [...lead, addComments(last, { trailing })];
-  }
-  return nodes;
-};
-
 %}
 @lexer lexer
 
@@ -150,18 +137,10 @@ set_operation -> %RESERVED_SET_OPERATION free_form_sql:* {%
 
 expression_chain_ -> expression_with_comments_:+ {% id %}
 
-expression_chain -> expression _expression_with_comments:* {%
-  ([expr, chain]) => [expr, ...chain]
-%}
-
 expression_with_comments_ -> expression _ {%
   ([expr, _]) => addComments(expr, { trailing: _ })
 %}
 
-_expression_with_comments -> _ expression {%
-  ([_, expr]) => addComments(expr, { leading: _ })
-%}
-
 free_form_sql -> ( asteriskless_free_form_sql | asterisk ) {% unwrap %}
 
 asteriskless_free_form_sql ->
@@ -251,11 +230,11 @@ property_access -> expression _ %DOT _ (identifier | array_subscript | all_colum
   }
 %}
 
-between_predicate -> %BETWEEN _ expression_chain _ %AND _ expression {%
+between_predicate -> %BETWEEN _ expression _ %AND _ expression {%
   ([betweenToken, _1, expr1, _2, andToken, _3, expr2]) => ({
     type: NodeType.between_predicate,
     betweenKw: toKeywordNode(betweenToken),
-    expr1: addCommentsToArray(expr1, { leading: _1, trailing: _2 }),
+    expr1: [addComments(expr1, { leading: _1, trailing: _2 })],
     andKw: toKeywordNode(andToken),
     expr2: [addComments(expr2, { leading: _3 })],
   })
diff --git a/yarn.lock b/yarn.lock
index 880a3cbe..45263c50 100644
--- a/yarn.lock
+++ b/yarn.lock
@@ -4724,9 +4724,9 @@ loader-runner@^4.2.0:
   integrity sha512-3R/1M+yS3j5ou80Me59j7F9IMs4PXs3VqRrm0TU3AbKPxlmpoY1TNscJV/oGJXo8qCatFGTfDbY6W6ipGOYXfg==
 
 loader-utils@^2.0.0:
-  version "2.0.4"
-  resolved "https://registry.yarnpkg.com/loader-utils/-/loader-utils-2.0.4.tgz#8b5cb38b5c34a9a018ee1fc0e6a066d1dfcc528c"
-  integrity sha512-xXqpXoINfFhgua9xiqD8fPFHgkoq1mmmpE92WlDbm9rNRd/EbRb+Gqf908T2DMfuHjjJlksiK2RbHVOdD/MqSw==
+  version "2.0.3"
+  resolved "https://registry.yarnpkg.com/loader-utils/-/loader-utils-2.0.3.tgz#d4b15b8504c63d1fc3f2ade52d41bc8459d6ede1"
+  integrity sha512-THWqIsn8QRnvLl0shHYVBN9syumU8pYWEHPTmkiVGd+7K5eFNVSY6AJhRvgGF70gg1Dz+l/k8WicvFCxdEs60A==
   dependencies:
     big.js "^5.2.2"
     emojis-list "^3.0.0"


Test patch:
diff --git a/test/features/between.ts b/test/features/between.ts
index 24a59e59..3b4b9e91 100644
--- a/test/features/between.ts
+++ b/test/features/between.ts
@@ -16,17 +16,4 @@ export default function supportsBetween(format: FormatFn) {
         foo BETWEEN /*C1*/ t.bar /*C2*/ AND /*C3*/ t.baz
     `);
   });
-
-  it('supports complex expressions inside BETWEEN', () => {
-    // Not ideal, but better than crashing
-    expect(format('foo BETWEEN 1+2 AND 3+4')).toBe('foo BETWEEN 1 + 2 AND 3  + 4');
-  });
-
-  it('supports CASE inside BETWEEN', () => {
-    expect(format('foo BETWEEN CASE x WHEN 1 THEN 2 END AND 3')).toBe(dedent`
-      foo BETWEEN CASE x
-        WHEN 1 THEN 2
-      END AND 3
-    `);
-  });
 }
diff --git a/test/singlestoredb.test.ts b/test/singlestoredb.test.ts
index 5907f981..4c25d262 100644
--- a/test/singlestoredb.test.ts
+++ b/test/singlestoredb.test.ts
@@ -1,3 +1,4 @@
+import dedent from 'dedent-js';
 import { format as originalFormat, FormatFn } from '../src/sqlFormatter.js';
 import behavesLikeMariaDbFormatter from './behavesLikeMariaDbFormatter.js';
 
@@ -9,6 +10,7 @@ import supportsCreateTable from './features/createTable.js';
 import supportsCreateView from './features/createView.js';
 import supportsAlterTable from './features/alterTable.js';
 import supportsStrings from './features/strings.js';
+import supportsBetween from './features/between.js';
 
 describe('SingleStoreDbFormatter', () => {
   const language = 'singlestoredb';
@@ -39,10 +41,65 @@ describe('SingleStoreDbFormatter', () => {
   supportsLimiting(format, { limit: true, offset: true });
   supportsCreateTable(format, { ifNotExists: true });
   supportsCreateView(format);
+  supportsBetween(format);
   supportsAlterTable(format, {
     addColumn: true,
     dropColumn: true,
     modify: true,
     renameTo: true,
   });
+  describe(`formats traversal of semi structured data`, () => {
+    it(`formats '.' path-operator without spaces`, () => {
+      expect(format(`SELECT TO_JSON(foo.*) AS foo_json FROM foo`)).toBe(dedent`
+        SELECT
+          TO_JSON(foo.*) AS foo_json
+        FROM
+          foo
+      `);
+    });
+    it(`formats '::' path-operator without spaces`, () => {
+      expect(format(`SELECT * FROM foo WHERE json_foo::bar = 'foobar'`)).toBe(dedent`
+        SELECT
+          *
+        FROM
+          foo
+        WHERE
+          json_foo::bar = 'foobar'
+      `);
+    });
+    it(`formats '::$' conversion path-operator without spaces`, () => {
+      expect(format(`SELECT * FROM foo WHERE json_foo::$bar = 'foobar'`)).toBe(dedent`
+        SELECT
+          *
+        FROM
+          foo
+        WHERE
+          json_foo::$bar = 'foobar'
+      `);
+    });
+    it(`formats '::%' conversion path-operator without spaces`, () => {
+      expect(format(`SELECT * FROM foo WHERE json_foo::%bar = 'foobar'`)).toBe(dedent`
+        SELECT
+          *
+        FROM
+          foo
+        WHERE
+          json_foo::%bar = 'foobar'
+      `);
+    });
+  });
+  describe(`formats custom type-cast operators`, () => {
+    it(`formats ':>' type-cast operator`, () => {
+      expect(format(`SELECT 1 :> DOUBLE AS foo`)).toBe(dedent`
+        SELECT
+          1 :> DOUBLE AS foo
+      `);
+    }); 
+    it(`formats '!:>' type-cast operator`, () => {
+      expect(format(`SELECT 1 !:> DOUBLE AS foo`)).toBe(dedent`
+        SELECT
+          1 !:> DOUBLE AS foo
+      `);
+    });
+  });
 });