Repository: sql-formatter-org/sql-formatter (ts)

Issue:
[FORMATTING] when set Indentation style to "Tabular, Right", it doesn't format "case when" well.
Standard style for "case when" is correct, but "Tabular Right" get incorrect format.
I tested it on demo site.

```sql
select
  x,
  case  1 then 'a' else 'none'  end a,
  case  2 then 'b' else 'none'  end b
from andy.t_test
where id < 5;
```
**Actual Output**
```sql
   select x,
          case
                    1 then 'a'
                         else 'none'
          end a,
          case
                    2 then 'b'
                         else 'none'
          end b
     from andy.t_test
    where id < 5;
```


Pull request:
Improve CASE expression formatting
Eliminated `RESERVED_DEPENDENT_CLAUSE` token, which contained `WHEN` and `ELSE`, but also optionally `ELSEIF` and `ELSIF` - these two are actually never part of CASE expressions, so these keywords really don't belong here. There's now separate token types for `WHEN` and `ELSE` instead, while `ELSEIF` is part of ordinary keyword tokens.

Added tests for CASE expression formatting in tabular style, which was pretty messed up. Removed tabular styling from `WHEN` and `ELSE` tokens, which improved the formatting. Not yet ideal, but much better than before.

This whole change also paves road for properly parsing CASE expressions.

Fixes #401 (not ideally, but at least it's not as horrible as before).

Patch:
diff --git a/src/formatter/ExpressionFormatter.ts b/src/formatter/ExpressionFormatter.ts
index 8937c60e..bb62f35b 100644
--- a/src/formatter/ExpressionFormatter.ts
+++ b/src/formatter/ExpressionFormatter.ts
@@ -350,8 +350,6 @@ export default class ExpressionFormatter {
     switch (node.tokenType) {
       case TokenType.RESERVED_JOIN:
         return this.formatJoin(node);
-      case TokenType.RESERVED_DEPENDENT_CLAUSE:
-        return this.formatDependentClause(node);
       case TokenType.AND:
       case TokenType.OR:
       case TokenType.XOR:
@@ -359,11 +357,15 @@ export default class ExpressionFormatter {
       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}`);
     }
@@ -384,7 +386,7 @@ export default class ExpressionFormatter {
     this.layout.add(this.showKw(node), WS.SPACE);
   }
 
-  private formatDependentClause(node: KeywordNode) {
+  private formatCaseWhenOrElse(node: KeywordNode) {
     this.layout.add(WS.NEWLINE, WS.INDENT, this.showKw(node), WS.SPACE);
   }
 
diff --git a/src/formatter/tabularStyle.ts b/src/formatter/tabularStyle.ts
index 75bc8a75..0f0d99ad 100644
--- a/src/formatter/tabularStyle.ts
+++ b/src/formatter/tabularStyle.ts
@@ -31,7 +31,6 @@ export default function toTabularFormat(tokenText: string, indentStyle: IndentSt
 export function isTabularToken(type: TokenType): boolean {
   return (
     isLogicalOperator(type) ||
-    type === TokenType.RESERVED_DEPENDENT_CLAUSE ||
     type === TokenType.RESERVED_COMMAND ||
     type === TokenType.RESERVED_SELECT ||
     type === TokenType.RESERVED_SET_OPERATION ||
diff --git a/src/languages/bigquery/bigquery.formatter.ts b/src/languages/bigquery/bigquery.formatter.ts
index cac282f4..54651b2f 100644
--- a/src/languages/bigquery/bigquery.formatter.ts
+++ b/src/languages/bigquery/bigquery.formatter.ts
@@ -147,7 +147,6 @@ export default class BigQueryFormatter extends Formatter {
       reservedSelect,
       reservedSetOperations,
       reservedJoins,
-      reservedDependentClauses: ['WHEN', 'ELSE'],
       reservedPhrases,
       reservedKeywords: keywords,
       reservedFunctionNames: functions,
diff --git a/src/languages/db2/db2.formatter.ts b/src/languages/db2/db2.formatter.ts
index 50f615ef..b4174cb4 100644
--- a/src/languages/db2/db2.formatter.ts
+++ b/src/languages/db2/db2.formatter.ts
@@ -176,7 +176,6 @@ export default class Db2Formatter extends Formatter {
       reservedSelect,
       reservedSetOperations,
       reservedJoins,
-      reservedDependentClauses: ['WHEN', 'ELSE', 'ELSEIF'],
       reservedPhrases,
       reservedKeywords: keywords,
       reservedFunctionNames: functions,
diff --git a/src/languages/db2/db2.keywords.ts b/src/languages/db2/db2.keywords.ts
index 8df36b6c..d649c53c 100644
--- a/src/languages/db2/db2.keywords.ts
+++ b/src/languages/db2/db2.keywords.ts
@@ -65,6 +65,8 @@ export const keywords = flatKeywordList({
     'DSSIZE',
     'DYNAMIC',
     'EDITPROC',
+    'ELSE',
+    'ELSEIF',
     'ENCODING',
     'ENCRYPTION',
     'ENDING',
diff --git a/src/languages/hive/hive.formatter.ts b/src/languages/hive/hive.formatter.ts
index 4a6f432a..f1a1b2ae 100644
--- a/src/languages/hive/hive.formatter.ts
+++ b/src/languages/hive/hive.formatter.ts
@@ -88,7 +88,6 @@ export default class HiveFormatter extends Formatter {
       reservedSelect,
       reservedSetOperations,
       reservedJoins,
-      reservedDependentClauses: ['WHEN', 'ELSE'],
       reservedPhrases,
       reservedKeywords: keywords,
       reservedFunctionNames: functions,
diff --git a/src/languages/mariadb/mariadb.formatter.ts b/src/languages/mariadb/mariadb.formatter.ts
index 10eecd6e..13355178 100644
--- a/src/languages/mariadb/mariadb.formatter.ts
+++ b/src/languages/mariadb/mariadb.formatter.ts
@@ -268,7 +268,6 @@ export default class MariaDbFormatter extends Formatter {
       reservedSelect,
       reservedSetOperations,
       reservedJoins,
-      reservedDependentClauses: ['WHEN', 'ELSE', 'ELSEIF', 'ELSIF'],
       reservedPhrases,
       supportsXor: true,
       reservedKeywords: keywords,
diff --git a/src/languages/mysql/mysql.formatter.ts b/src/languages/mysql/mysql.formatter.ts
index 29994118..18126613 100644
--- a/src/languages/mysql/mysql.formatter.ts
+++ b/src/languages/mysql/mysql.formatter.ts
@@ -236,7 +236,6 @@ export default class MySqlFormatter extends Formatter {
       reservedSelect,
       reservedSetOperations,
       reservedJoins,
-      reservedDependentClauses: ['WHEN', 'ELSE', 'ELSEIF'],
       reservedPhrases,
       supportsXor: true,
       reservedKeywords: keywords,
diff --git a/src/languages/n1ql/n1ql.formatter.ts b/src/languages/n1ql/n1ql.formatter.ts
index 67fb900e..207b0d6a 100644
--- a/src/languages/n1ql/n1ql.formatter.ts
+++ b/src/languages/n1ql/n1ql.formatter.ts
@@ -85,7 +85,6 @@ export default class N1qlFormatter extends Formatter {
       reservedSelect,
       reservedSetOperations,
       reservedJoins,
-      reservedDependentClauses: ['WHEN', 'ELSE'],
       reservedPhrases,
       supportsXor: true,
       reservedKeywords: keywords,
diff --git a/src/languages/plsql/plsql.formatter.ts b/src/languages/plsql/plsql.formatter.ts
index 35bf443d..af11087f 100644
--- a/src/languages/plsql/plsql.formatter.ts
+++ b/src/languages/plsql/plsql.formatter.ts
@@ -85,7 +85,6 @@ export default class PlSqlFormatter extends Formatter {
       reservedSelect,
       reservedSetOperations,
       reservedJoins,
-      reservedDependentClauses: ['WHEN', 'ELSE'],
       reservedPhrases,
       supportsXor: true,
       reservedKeywords: keywords,
diff --git a/src/languages/postgresql/postgresql.formatter.ts b/src/languages/postgresql/postgresql.formatter.ts
index 207a2b57..b7770041 100644
--- a/src/languages/postgresql/postgresql.formatter.ts
+++ b/src/languages/postgresql/postgresql.formatter.ts
@@ -253,7 +253,6 @@ export default class PostgreSqlFormatter extends Formatter {
       reservedSelect,
       reservedSetOperations,
       reservedJoins,
-      reservedDependentClauses: ['WHEN', 'ELSE'],
       reservedPhrases,
       reservedKeywords: keywords,
       reservedFunctionNames: functions,
diff --git a/src/languages/redshift/redshift.formatter.ts b/src/languages/redshift/redshift.formatter.ts
index 90d5e353..722876d3 100644
--- a/src/languages/redshift/redshift.formatter.ts
+++ b/src/languages/redshift/redshift.formatter.ts
@@ -146,7 +146,6 @@ export default class RedshiftFormatter extends Formatter {
       reservedSelect,
       reservedSetOperations,
       reservedJoins,
-      reservedDependentClauses: ['WHEN', 'ELSE'],
       reservedPhrases,
       reservedKeywords: keywords,
       reservedFunctionNames: functions,
diff --git a/src/languages/singlestoredb/singlestoredb.formatter.ts b/src/languages/singlestoredb/singlestoredb.formatter.ts
index 5259e457..0e51b59b 100644
--- a/src/languages/singlestoredb/singlestoredb.formatter.ts
+++ b/src/languages/singlestoredb/singlestoredb.formatter.ts
@@ -237,7 +237,6 @@ export default class SingleStoreDbFormatter extends Formatter {
       reservedSelect,
       reservedSetOperations,
       reservedJoins,
-      reservedDependentClauses: ['WHEN', 'ELSE', 'ELSEIF'],
       reservedPhrases,
       reservedKeywords: keywords,
       reservedFunctionNames: functions,
diff --git a/src/languages/spark/spark.formatter.ts b/src/languages/spark/spark.formatter.ts
index 85fa7529..a64a34d6 100644
--- a/src/languages/spark/spark.formatter.ts
+++ b/src/languages/spark/spark.formatter.ts
@@ -123,7 +123,6 @@ export default class SparkFormatter extends Formatter {
       reservedSelect,
       reservedSetOperations,
       reservedJoins,
-      reservedDependentClauses: ['WHEN', 'ELSE'],
       reservedPhrases,
       supportsXor: true,
       reservedKeywords: keywords,
diff --git a/src/languages/sql/sql.formatter.ts b/src/languages/sql/sql.formatter.ts
index 016db858..0edcf9c1 100644
--- a/src/languages/sql/sql.formatter.ts
+++ b/src/languages/sql/sql.formatter.ts
@@ -74,7 +74,6 @@ export default class SqlFormatter extends Formatter {
       reservedSelect,
       reservedSetOperations,
       reservedJoins,
-      reservedDependentClauses: ['WHEN', 'ELSE'],
       reservedPhrases,
       reservedKeywords: keywords,
       reservedFunctionNames: functions,
diff --git a/src/languages/sqlite/sqlite.formatter.ts b/src/languages/sqlite/sqlite.formatter.ts
index bd9486fa..1497cf30 100644
--- a/src/languages/sqlite/sqlite.formatter.ts
+++ b/src/languages/sqlite/sqlite.formatter.ts
@@ -67,7 +67,6 @@ export default class SqliteFormatter extends Formatter {
       reservedSelect,
       reservedSetOperations,
       reservedJoins,
-      reservedDependentClauses: ['WHEN', 'ELSE'],
       reservedPhrases,
       reservedKeywords: keywords,
       reservedFunctionNames: functions,
diff --git a/src/languages/transactsql/transactsql.formatter.ts b/src/languages/transactsql/transactsql.formatter.ts
index 1214c5e9..24737ddb 100644
--- a/src/languages/transactsql/transactsql.formatter.ts
+++ b/src/languages/transactsql/transactsql.formatter.ts
@@ -220,7 +220,6 @@ export default class TransactSqlFormatter extends Formatter {
       reservedSelect,
       reservedSetOperations,
       reservedJoins,
-      reservedDependentClauses: ['WHEN', 'ELSE'],
       reservedPhrases,
       reservedKeywords: keywords,
       reservedFunctionNames: functions,
diff --git a/src/languages/trino/trino.formatter.ts b/src/languages/trino/trino.formatter.ts
index 62df2997..9e98a9f1 100644
--- a/src/languages/trino/trino.formatter.ts
+++ b/src/languages/trino/trino.formatter.ts
@@ -126,7 +126,6 @@ export default class TrinoFormatter extends Formatter {
       reservedSelect,
       reservedSetOperations,
       reservedJoins,
-      reservedDependentClauses: ['WHEN', 'ELSE'],
       reservedPhrases,
       reservedKeywords: keywords,
       reservedFunctionNames: functions,
diff --git a/src/lexer/Tokenizer.ts b/src/lexer/Tokenizer.ts
index 691e14fc..0046d182 100644
--- a/src/lexer/Tokenizer.ts
+++ b/src/lexer/Tokenizer.ts
@@ -91,8 +91,18 @@ export default class Tokenizer {
         text: toCanonical,
       },
       {
-        type: TokenType.RESERVED_DEPENDENT_CLAUSE,
-        regex: regex.reservedWord(cfg.reservedDependentClauses, cfg.identChars),
+        type: TokenType.WHEN,
+        regex: /WHEN\b/iuy,
+        text: toCanonical,
+      },
+      {
+        type: TokenType.ELSE,
+        regex: /ELSE\b/iuy,
+        text: toCanonical,
+      },
+      {
+        type: TokenType.THEN,
+        regex: /THEN\b/iuy,
         text: toCanonical,
       },
       {
diff --git a/src/lexer/TokenizerOptions.ts b/src/lexer/TokenizerOptions.ts
index 8d905418..59c381a0 100644
--- a/src/lexer/TokenizerOptions.ts
+++ b/src/lexer/TokenizerOptions.ts
@@ -49,8 +49,6 @@ export interface TokenizerOptions {
   reservedSelect: string[];
   // True to support XOR in addition to AND and OR
   supportsXor?: boolean;
-  // Keywords in CASE expressions that begin new line, like: WHEN, ELSE
-  reservedDependentClauses: string[];
   // Keywords that create newline but no indentaion of their body.
   // These contain set operations like UNION
   reservedSetOperations: string[];
diff --git a/src/lexer/token.ts b/src/lexer/token.ts
index ccc06bb3..e83e70c2 100644
--- a/src/lexer/token.ts
+++ b/src/lexer/token.ts
@@ -7,7 +7,6 @@ export enum TokenType {
   RESERVED_KEYWORD = 'RESERVED_KEYWORD',
   RESERVED_FUNCTION_NAME = 'RESERVED_FUNCTION_NAME',
   RESERVED_PHRASE = 'RESERVED_PHRASE',
-  RESERVED_DEPENDENT_CLAUSE = 'RESERVED_DEPENDENT_CLAUSE',
   RESERVED_SET_OPERATION = 'RESERVED_SET_OPERATION',
   RESERVED_COMMAND = 'RESERVED_COMMAND',
   RESERVED_SELECT = 'RESERVED_SELECT',
@@ -16,6 +15,9 @@ export enum TokenType {
   ARRAY_KEYWORD = 'ARRAY_KEYWORD', // RESERVED_KEYWORD token in front of [
   CASE = 'CASE',
   END = 'END',
+  WHEN = 'WHEN',
+  ELSE = 'ELSE',
+  THEN = 'THEN',
   LIMIT = 'LIMIT',
   BETWEEN = 'BETWEEN',
   AND = 'AND',
@@ -82,7 +84,6 @@ export const isReserved = (type: TokenType): boolean =>
   type === TokenType.RESERVED_KEYWORD ||
   type === TokenType.RESERVED_FUNCTION_NAME ||
   type === TokenType.RESERVED_PHRASE ||
-  type === TokenType.RESERVED_DEPENDENT_CLAUSE ||
   type === TokenType.RESERVED_COMMAND ||
   type === TokenType.RESERVED_SELECT ||
   type === TokenType.RESERVED_SET_OPERATION ||
@@ -90,6 +91,9 @@ export const isReserved = (type: TokenType): boolean =>
   type === TokenType.ARRAY_KEYWORD ||
   type === TokenType.CASE ||
   type === TokenType.END ||
+  type === TokenType.WHEN ||
+  type === TokenType.ELSE ||
+  type === TokenType.THEN ||
   type === TokenType.LIMIT ||
   type === TokenType.BETWEEN ||
   type === TokenType.AND ||
diff --git a/src/parser/grammar.ne b/src/parser/grammar.ne
index 5c13bb60..bcced87e 100644
--- a/src/parser/grammar.ne
+++ b/src/parser/grammar.ne
@@ -234,10 +234,12 @@ literal ->
 keyword ->
   ( %RESERVED_KEYWORD
   | %RESERVED_PHRASE
-  | %RESERVED_DEPENDENT_CLAUSE
   | %RESERVED_JOIN
   | %CASE
   | %END
+  | %WHEN
+  | %ELSE
+  | %THEN
   | %AND
   | %OR
   | %XOR ) {%


Test patch:
diff --git a/test/features/case.ts b/test/features/case.ts
index ee28f8e3..cc2dfcca 100644
--- a/test/features/case.ts
+++ b/test/features/case.ts
@@ -109,4 +109,34 @@ export default function supportsCase(format: FormatFn) {
         quaz
     `);
   });
+
+  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
+                          WHEN 1 THEN bar
+                          ELSE baz
+                END;
+    `);
+  });
+
+  it('formats CASE with identStyle:tabularRight', () => {
+    const result = format('SELECT CASE foo WHEN 1 THEN bar ELSE baz END;', {
+      indentStyle: 'tabularRight',
+    });
+
+    expect(result).toBe(
+      [
+        '   SELECT CASE',
+        '                    foo',
+        '                    WHEN 1 THEN bar',
+        '                    ELSE baz',
+        '          END;',
+      ].join('\n')
+    );
+  });
 }
diff --git a/test/unit/Parser.test.ts b/test/unit/Parser.test.ts
index 17f439ba..10d2eb2a 100644
--- a/test/unit/Parser.test.ts
+++ b/test/unit/Parser.test.ts
@@ -6,7 +6,6 @@ describe('Parser', () => {
     const tokenizer = new Tokenizer({
       reservedCommands: ['FROM', 'WHERE', 'LIMIT', 'CREATE TABLE'],
       reservedSelect: ['SELECT'],
-      reservedDependentClauses: ['WHEN', 'ELSE'],
       reservedSetOperations: ['UNION', 'UNION ALL'],
       reservedJoins: ['JOIN'],
       reservedFunctionNames: ['SQRT', 'CURRENT_TIME'],
diff --git a/test/unit/Tokenizer.test.ts b/test/unit/Tokenizer.test.ts
index 6bab1d6c..afdc4cc8 100644
--- a/test/unit/Tokenizer.test.ts
+++ b/test/unit/Tokenizer.test.ts
@@ -5,7 +5,6 @@ describe('Tokenizer', () => {
     new Tokenizer({
       reservedCommands: ['FROM', 'WHERE', 'LIMIT', 'CREATE TABLE'],
       reservedSelect: ['SELECT'],
-      reservedDependentClauses: ['WHEN', 'ELSE'],
       reservedSetOperations: ['UNION', 'UNION ALL'],
       reservedJoins: ['JOIN'],
       reservedFunctionNames: ['SQRT', 'CURRENT_TIME'],