Repository: David-Wobrock/sqlvalidator

Issue:
AssertionError is raised when using a CASE statement in a CASE statement
Hi David!

Thank you for `sqlvalidator`! :)
The following query seems correct:
```sql
SELECT
  CASE
    WHEN CASE
    WHEN code = 1 THEN 'VALID'
    WHEN code = 2 THEN 'INVALID'
END
  = 'VALID' THEN 'OK'
  ELSE
  'KO'
END
FROM table
```

However, `sqlvalidator` fails to validate/format it: an `AssertionError` is raised.



Discussion:

Patch:
diff --git a/sqlvalidator/grammar/tokeniser.py b/sqlvalidator/grammar/tokeniser.py
index df1e423..62f07fa 100644
--- a/sqlvalidator/grammar/tokeniser.py
+++ b/sqlvalidator/grammar/tokeniser.py
@@ -47,10 +47,12 @@ def get_tokens_until_one_of(tokens, stop_words, first_token=None, keep=None):
     next_token = next(tokens, None)
     count_parenthesis = 0 if first_token != "(" else 1
     count_square_brackets = 0 if first_token != "[" else 1
+    count_case_expr = 0 if first_token != "case" else 1
     while next_token is not None and not (
         lower(next_token) in stop_words
         and count_parenthesis <= 0
         and count_square_brackets <= 0
+        and count_case_expr <= 0
         and (
             not argument_tokens
             or (lower(argument_tokens[-1]), lower(next_token)) not in keep
@@ -65,6 +67,10 @@ def get_tokens_until_one_of(tokens, stop_words, first_token=None, keep=None):
             count_square_brackets += 1
         elif next_token == "]":
             count_square_brackets -= 1
+        elif lower(next_token) == "case":
+            count_case_expr += 1
+        elif lower(next_token) == "end":
+            count_case_expr -= 1
         next_token = next(tokens, None)
 
     return argument_tokens, next_token


Test patch:
diff --git a/tests/integration/test_formatting.py b/tests/integration/test_formatting.py
index 169b28c..80e6199 100644
--- a/tests/integration/test_formatting.py
+++ b/tests/integration/test_formatting.py
@@ -2182,3 +2182,26 @@ SELECT * REPLACE ("x" AS col)
 FROM t
 """
     assert format_sql(sql) == expected.strip()
+
+
+def test_nesting_case_expr():
+    sql = """SELECT CASE
+    WHEN CASE
+    WHEN code = 1 THEN 'VALID'
+    WHEN code = 2 THEN 'INVALID' END
+   = 'VALID' THEN 'OK' ELSE 'KO' END
+FROM table
+"""
+    expected = """
+SELECT CASE
+ WHEN
+  CASE
+   WHEN code = 1 THEN 'VALID'
+   WHEN code = 2 THEN 'INVALID'
+  END = 'VALID'
+ THEN 'OK'
+ ELSE 'KO'
+END
+FROM table
+"""
+    assert format_sql(sql) == expected.strip()