Repository: sql-formatter-org/sql-formatter
Pull request #459 (2022-09-26 08:38:08)

Parse CASE expressions
Implemented proper parsing of case expressions.

Also changed the formatting so that instead of:

```sql
CASE
  foo
  WHEN 1 THEN 'A'
  ELSE 'B'
END
```

we now format the expression following `CASE` on the same line:

```sql
CASE foo
  WHEN 1 THEN 'A'
  ELSE 'B'
END
```



Patch:
diff --git a/package.json b/package.json
index 20685292..65cd32ab 100644
--- a/package.json
+++ b/package.json
@@ -1,6 +1,6 @@
 {
   "name": "sql-formatter",
-  "version": "10.7.2",
+  "version": "10.7.1",
   "description": "Format whitespace in a SQL query to make it more readable",
   "license": "MIT",
   "main": "lib/index.js",
diff --git a/src/formatter/ExpressionFormatter.ts b/src/formatter/ExpressionFormatter.ts
index bb62f35b..587b9c86 100644
--- a/src/formatter/ExpressionFormatter.ts
+++ b/src/formatter/ExpressionFormatter.ts
@@ -25,6 +25,9 @@ import {
   KeywordNode,
   PropertyAccessNode,
   CommentNode,
+  CaseExpressionNode,
+  CaseWhenNode,
+  CaseElseNode,
 } from 'src/parser/ast';
 
 import Layout, { WS } from './Layout';
@@ -82,6 +85,12 @@ export default class ExpressionFormatter {
         return this.formatParenthesis(node);
       case NodeType.between_predicate:
         return this.formatBetweenPredicate(node);
+      case NodeType.case_expression:
+        return this.formatCaseExpression(node);
+      case NodeType.case_when:
+        return this.formatCaseWhen(node);
+      case NodeType.case_else:
+        return this.formatCaseElse(node);
       case NodeType.clause:
         return this.formatClause(node);
       case NodeType.set_operation:
@@ -163,6 +172,32 @@ export default class ExpressionFormatter {
     this.layout.add(WS.SPACE);
   }
 
+  private formatCaseExpression(node: CaseExpressionNode) {
+    this.formatNode(node.case);
+    this.layout = this.formatSubExpression(node.expr);
+
+    this.layout.indentation.increaseBlockLevel();
+    this.layout = this.formatSubExpression(node.clauses);
+    this.layout.indentation.decreaseBlockLevel();
+
+    this.layout.add(WS.NEWLINE, WS.INDENT);
+    this.formatNode(node.end);
+  }
+
+  private formatCaseWhen(node: CaseWhenNode) {
+    this.layout.add(WS.NEWLINE, WS.INDENT);
+    this.formatNode(node.when);
+    this.layout = this.formatSubExpression(node.condition);
+    this.formatNode(node.then);
+    this.layout = this.formatSubExpression(node.result);
+  }
+
+  private formatCaseElse(node: CaseElseNode) {
+    this.layout.add(WS.NEWLINE, WS.INDENT);
+    this.formatNode(node.else);
+    this.layout = this.formatSubExpression(node.result);
+  }
+
   private formatClause(node: ClauseNode) {
     if (isTabularStyle(this.cfg)) {
       this.layout.add(WS.NEWLINE, WS.INDENT, this.showKw(node.name), WS.SPACE);
@@ -354,20 +389,8 @@ export default class ExpressionFormatter {
       case TokenType.OR:
       case TokenType.XOR:
         return this.formatLogicalOperator(node);
-      case TokenType.RESERVED_KEYWORD:
-      case TokenType.RESERVED_FUNCTION_NAME:
-      case TokenType.RESERVED_PHRASE:
-      case TokenType.THEN:
-        return this.formatKeyword(node);
-      case TokenType.CASE:
-        return this.formatCaseStart(node);
-      case TokenType.END:
-        return this.formatCaseEnd(node);
-      case TokenType.WHEN:
-      case TokenType.ELSE:
-        return this.formatCaseWhenOrElse(node);
       default:
-        throw new Error(`Unexpected token type: ${node.tokenType}`);
+        return this.formatKeyword(node);
     }
   }
 
@@ -386,10 +409,6 @@ export default class ExpressionFormatter {
     this.layout.add(this.showKw(node), WS.SPACE);
   }
 
-  private formatCaseWhenOrElse(node: KeywordNode) {
-    this.layout.add(WS.NEWLINE, WS.INDENT, this.showKw(node), WS.SPACE);
-  }
-
   private formatLogicalOperator(node: KeywordNode) {
     if (this.cfg.logicalOperatorNewline === 'before') {
       if (isTabularStyle(this.cfg)) {
@@ -405,21 +424,6 @@ export default class ExpressionFormatter {
     }
   }
 
-  private formatCaseStart(node: KeywordNode) {
-    this.layout.indentation.increaseBlockLevel();
-    this.layout.add(this.showKw(node), WS.NEWLINE, WS.INDENT);
-  }
-
-  private formatCaseEnd(node: KeywordNode) {
-    this.formatMultilineBlockEnd(node);
-  }
-
-  private formatMultilineBlockEnd(node: KeywordNode) {
-    this.layout.indentation.decreaseBlockLevel();
-
-    this.layout.add(WS.NEWLINE, WS.INDENT, this.showKw(node), WS.SPACE);
-  }
-
   private showKw(node: KeywordNode): string {
     if (isTabularToken(node.tokenType)) {
       return toTabularFormat(this.showNonTabularKw(node), this.cfg.indentStyle);
diff --git a/src/parser/ast.ts b/src/parser/ast.ts
index c94580b0..5240a8c9 100644
--- a/src/parser/ast.ts
+++ b/src/parser/ast.ts
@@ -9,6 +9,9 @@ export enum NodeType {
   property_access = 'property_access',
   parenthesis = 'parenthesis',
   between_predicate = 'between_predicate',
+  case_expression = 'case_expression',
+  case_when = 'case_when',
+  case_else = 'case_else',
   limit_clause = 'limit_clause',
   all_columns_asterisk = 'all_columns_asterisk',
   literal = 'literal',
@@ -73,6 +76,28 @@ export interface BetweenPredicateNode extends BaseNode {
   expr2: AstNode[];
 }
 
+export interface CaseExpressionNode extends BaseNode {
+  type: NodeType.case_expression;
+  case: KeywordNode;
+  end: KeywordNode;
+  expr: AstNode[];
+  clauses: (CaseWhenNode | CaseElseNode)[];
+}
+
+export interface CaseWhenNode extends BaseNode {
+  type: NodeType.case_when;
+  when: KeywordNode;
+  then: KeywordNode;
+  condition: AstNode[];
+  result: AstNode[];
+}
+
+export interface CaseElseNode extends BaseNode {
+  type: NodeType.case_else;
+  else: KeywordNode;
+  result: AstNode[];
+}
+
 // LIMIT <count>
 // LIMIT <offset>, <count>
 export interface LimitClauseNode extends BaseNode {
@@ -146,6 +171,9 @@ export type AstNode =
   | PropertyAccessNode
   | ParenthesisNode
   | BetweenPredicateNode
+  | CaseExpressionNode
+  | CaseWhenNode
+  | CaseElseNode
   | LimitClauseNode
   | AllColumnsAsteriskNode
   | LiteralNode
diff --git a/src/parser/grammar.ne b/src/parser/grammar.ne
index 842c7e5f..2f0a3d17 100644
--- a/src/parser/grammar.ne
+++ b/src/parser/grammar.ne
@@ -92,13 +92,6 @@ select_clause -> %RESERVED_SELECT (all_columns_asterisk expression:* | asteriskl
     children: [exp, ...expressions],
   })
 %}
-select_clause -> %RESERVED_SELECT {%
-  ([nameToken]) => ({
-    type: NodeType.clause,
-    name: toKeywordNode(nameToken),
-    children: [],
-  })
-%}
 
 all_columns_asterisk -> %ASTERISK {%
   () => ({ type: NodeType.all_columns_asterisk })
@@ -124,9 +117,15 @@ expression_with_comments -> simple_expression _ {%
   ([expr, _]) => addTrailingComments(expr, _)
 %}
 
-expression -> ( simple_expression | between_predicate | comma | comment ) {% unwrap %}
+expression -> ( asteriskless_expression | asterisk ) {% unwrap %}
 
-asteriskless_expression -> ( simple_expression_without_asterisk | between_predicate | comma | comment ) {% unwrap %}
+asteriskless_expression ->
+  ( simple_expression_without_asterisk
+  | between_predicate
+  | case_expression
+  | comma
+  | comment
+  | other_keyword ) {% unwrap %}
 
 simple_expression -> ( simple_expression_without_asterisk | asterisk ) {% unwrap %}
 
@@ -217,6 +216,33 @@ between_predicate -> %BETWEEN _ simple_expression _ %AND _ simple_expression {%
   })
 %}
 
+case_expression -> %CASE _ simple_expression:* case_clause:* _ %END {%
+  ([caseToken, _1, expr, clauses, _2, endToken]) => ({
+    type: NodeType.case_expression,
+    case: addTrailingComments(toKeywordNode(caseToken), _1),
+    end: addLeadingComments(toKeywordNode(endToken), _2),
+    expr,
+    clauses,
+  })
+%}
+
+case_clause -> _ %WHEN _ simple_expression:+ _ %THEN _ simple_expression:+ {%
+  ([_1, whenToken, _2, cond, _3, thenToken, _4, expr]) => ({
+    type: NodeType.case_when,
+    when: addTrailingComments(addLeadingComments(toKeywordNode(whenToken), _1), _2),
+    then: addTrailingComments(addLeadingComments(toKeywordNode(thenToken), _3), _4),
+    condition: cond,
+    result: expr,
+  })
+%}
+case_clause -> _ %ELSE _ simple_expression:+ {%
+  ([_1, elseToken, _2, expr]) => ({
+    type: NodeType.case_else,
+    else: addTrailingComments(addLeadingComments(toKeywordNode(elseToken), _1), _2),
+    result: expr,
+  })
+%}
+
 comma -> ( %COMMA ) {% ([[token]]) => ({ type: NodeType.comma }) %}
 
 asterisk -> ( %ASTERISK ) {% ([[token]]) => ({ type: NodeType.operator, text: token.text }) %}
@@ -242,17 +268,20 @@ keyword ->
   ( %RESERVED_KEYWORD
   | %RESERVED_PHRASE
   | %RESERVED_JOIN
-  | %CASE
-  | %END
-  | %WHEN
-  | %ELSE
-  | %THEN
   | %AND
   | %OR
   | %XOR ) {%
   ([[token]]) => toKeywordNode(token)
 %}
 
+other_keyword ->
+  ( %WHEN
+  | %THEN
+  | %ELSE
+  | %END ) {%
+  ([[token]]) => toKeywordNode(token)
+%}
+
 _ -> comment:* {% ([comments]) => comments %}
 
 comment -> %LINE_COMMENT {%


Test patch:
diff --git a/test/features/case.ts b/test/features/case.ts
index cc2dfcca..c356aed5 100644
--- a/test/features/case.ts
+++ b/test/features/case.ts
@@ -24,8 +24,7 @@ export default function supportsCase(format: FormatFn) {
     );
 
     expect(result).toBe(dedent`
-      CASE
-        trim(sqrt(2))
+      CASE trim(sqrt(2))
         WHEN 'one' THEN 1
         WHEN 'two' THEN 2
         WHEN 'three' THEN 3
@@ -43,8 +42,7 @@ export default function supportsCase(format: FormatFn) {
       SELECT
         foo,
         bar,
-        CASE
-          baz
+        CASE baz
           WHEN 'one' THEN 1
           WHEN 'two' THEN 2
           ELSE 3
@@ -84,8 +82,7 @@ export default function supportsCase(format: FormatFn) {
       { keywordCase: 'upper' }
     );
     expect(result).toBe(dedent`
-      CASE
-        TRIM(SQRT(my_field))
+      CASE TRIM(SQRT(my_field))
         WHEN 'one' THEN 1
         WHEN 'two' THEN 2
         WHEN 'three' THEN 3
@@ -100,8 +97,7 @@ export default function supportsCase(format: FormatFn) {
     expect(result).toBe(dedent`
       select
         sum(
-          case
-            a
+          case a
             when foo then bar
           end
         )
@@ -110,14 +106,43 @@ export default function supportsCase(format: FormatFn) {
     `);
   });
 
+  it('formats CASE with comments', () => {
+    const result = format(`
+      SELECT CASE /*c1*/ foo /*c2*/
+      WHEN /*c3*/ 1 /*c4*/ THEN /*c5*/ 2 /*c6*/
+      ELSE /*c7*/ 3 /*c8*/
+      END;
+    `);
+
+    expect(result).toBe(dedent`
+      SELECT
+        CASE
+        /*c1*/
+        foo
+          /*c2*/
+          WHEN
+          /*c3*/
+          1
+          /*c4*/
+          THEN
+          /*c5*/
+          2
+          /*c6*/
+          ELSE
+          /*c7*/
+          3
+        /*c8*/
+        END;
+    `);
+  });
+
   it('formats CASE with identStyle:tabularLeft', () => {
     const result = format('SELECT CASE foo WHEN 1 THEN bar ELSE baz END;', {
       indentStyle: 'tabularLeft',
     });
 
     expect(result).toBe(dedent`
-      SELECT    CASE
-                          foo
+      SELECT    CASE foo
                           WHEN 1 THEN bar
                           ELSE baz
                 END;
@@ -131,8 +156,7 @@ export default function supportsCase(format: FormatFn) {
 
     expect(result).toBe(
       [
-        '   SELECT CASE',
-        '                    foo',
+        '   SELECT CASE foo',
         '                    WHEN 1 THEN bar',
         '                    ELSE baz',
         '          END;',
diff --git a/test/n1ql.test.ts b/test/n1ql.test.ts
index f2e15104..ceb54441 100644
--- a/test/n1ql.test.ts
+++ b/test/n1ql.test.ts
@@ -118,8 +118,7 @@ describe('N1qlFormatter', () => {
       USE KEYS
         'Elinor_33313792'
       NEST
-        orders_with_users orders ON KEYS ARRAY s.order_id FOR s IN usr.shipped_order_history
-      END;
+        orders_with_users orders ON KEYS ARRAY s.order_id FOR s IN usr.shipped_order_history END;
     `);
   });
 
diff --git a/test/postgresql.test.ts b/test/postgresql.test.ts
index 075e1fd6..7839ee62 100644
--- a/test/postgresql.test.ts
+++ b/test/postgresql.test.ts
@@ -204,13 +204,6 @@ describe('PostgreSqlFormatter', () => {
     `);
   });
 
-  // Regression test for issue #447
-  it('formats empty SELECT', () => {
-    expect(format('SELECT;')).toBe(dedent`
-      SELECT;
-    `);
-  });
-
   // Regression test for issue #391
   it('formats TIMESTAMP WITH TIME ZONE syntax', () => {
     expect(
diff --git a/test/unit/Parser.test.ts b/test/unit/Parser.test.ts
index 10d2eb2a..5899f254 100644
--- a/test/unit/Parser.test.ts
+++ b/test/unit/Parser.test.ts
@@ -90,4 +90,8 @@ describe('Parser', () => {
   it('parses qualified.identifier.sequence', () => {
     expect(parse('SELECT foo.bar.baz;')).toMatchSnapshot();
   });
+
+  it('parses CASE expression', () => {
+    expect(parse('SELECT CASE foo WHEN 1+1 THEN 10 ELSE 20 END;')).toMatchSnapshot();
+  });
 });
diff --git a/test/unit/__snapshots__/Parser.test.ts.snap b/test/unit/__snapshots__/Parser.test.ts.snap
index a2f141fa..0a147ae5 100644
--- a/test/unit/__snapshots__/Parser.test.ts.snap
+++ b/test/unit/__snapshots__/Parser.test.ts.snap
@@ -53,6 +53,101 @@ Array [
 ]
 `;
 
+exports[`Parser parses CASE expression 1`] = `
+Array [
+  Object {
+    "children": Array [
+      Object {
+        "children": Array [
+          Object {
+            "case": Object {
+              "raw": "CASE",
+              "text": "CASE",
+              "tokenType": "CASE",
+              "type": "keyword",
+            },
+            "clauses": Array [
+              Object {
+                "condition": Array [
+                  Object {
+                    "text": "1",
+                    "type": "literal",
+                  },
+                  Object {
+                    "text": "+",
+                    "type": "operator",
+                  },
+                  Object {
+                    "text": "1",
+                    "type": "literal",
+                  },
+                ],
+                "result": Array [
+                  Object {
+                    "text": "10",
+                    "type": "literal",
+                  },
+                ],
+                "then": Object {
+                  "raw": "THEN",
+                  "text": "THEN",
+                  "tokenType": "THEN",
+                  "type": "keyword",
+                },
+                "type": "case_when",
+                "when": Object {
+                  "raw": "WHEN",
+                  "text": "WHEN",
+                  "tokenType": "WHEN",
+                  "type": "keyword",
+                },
+              },
+              Object {
+                "else": Object {
+                  "raw": "ELSE",
+                  "text": "ELSE",
+                  "tokenType": "ELSE",
+                  "type": "keyword",
+                },
+                "result": Array [
+                  Object {
+                    "text": "20",
+                    "type": "literal",
+                  },
+                ],
+                "type": "case_else",
+              },
+            ],
+            "end": Object {
+              "raw": "END",
+              "text": "END",
+              "tokenType": "END",
+              "type": "keyword",
+            },
+            "expr": Array [
+              Object {
+                "text": "foo",
+                "type": "identifier",
+              },
+            ],
+            "type": "case_expression",
+          },
+        ],
+        "name": Object {
+          "raw": "SELECT",
+          "text": "SELECT",
+          "tokenType": "RESERVED_SELECT",
+          "type": "keyword",
+        },
+        "type": "clause",
+      },
+    ],
+    "hasSemicolon": true,
+    "type": "statement",
+  },
+]
+`;
+
 exports[`Parser parses LIMIT clause with count 1`] = `
 Array [
   Object {