Repository: DerekStride/tree-sitter-sql (language: python)

Issue:
Parse issues with SQLite generated columns
Hello! This is a wonderful parser that I benefit from daily. Thanks for your hard work!

I noticed an issue when I was experimenting with generated columns in my SQLite schema. Here's a quick cut-down example:

```sql
CREATE TABLE IF NOT EXISTS Items (
  item_type INTEGER NOT NULL,
  item_type_name TEXT GENERATED ALWAYS AS (
    CASE item_type
      WHEN 1 THEN 'foo'
      WHEN 2 THEN 'bar'
      ELSE 'UNKNOWN'
    END
  ) VIRTUAL
);
```

https://sqlime.org/#deta:pak48dg2wu8z

The AST appears to break after the `AS` keyword:

```
(statement) ; [1:1 - 5:22]
 (create_table) ; [1:1 - 5:22]
  (keyword_create) ; [1:1 - 6]
  (keyword_table) ; [1:8 - 12]
  (keyword_if) ; [1:14 - 15]
  (keyword_not) ; [1:17 - 19]
  (keyword_exists) ; [1:21 - 26]
  (object_reference) ; [1:28 - 32]
   name: (identifier) ; [1:28 - 32]
  (column_definitions) ; [1:34 - 5:22]
   (column_definition) ; [2:3 - 28]
    name: (identifier) ; [2:3 - 11]
    type: (int) ; [2:13 - 19]
     (keyword_int) ; [2:13 - 19]
    (keyword_not) ; [2:21 - 23]
    (keyword_null) ; [2:25 - 28]
   (column_definition) ; [3:3 - 5:22]
    name: (identifier) ; [3:3 - 16]
    type: (keyword_text) ; [3:18 - 21]
    (keyword_generated) ; [3:23 - 31]
    (keyword_always) ; [3:33 - 38]
    (keyword_as) ; [3:40 - 41]
    (ERROR) ; [3:43 - 5:19]
     (keyword_case) ; [4:5 - 8]
     (identifier) ; [4:10 - 18]
     (keyword_then) ; [5:14 - 17]
    (identifier) ; [5:20 - 22]
(ERROR) ; [5:23 - 11:0]
```

Since this is valid SQLite, it would be cool if it didn't break. I haven't checked other DB engines exhaustively, but it appears MySQL and Postgres offer similar constructs.

[MySQL](https://dev.mysql.com/doc/refman/8.0/en/create-table-generated-columns.html):

```sql
col_name data_type [GENERATED ALWAYS] AS (expr)
  [VIRTUAL | STORED] [NOT NULL | NULL]
  [UNIQUE [KEY]] [[PRIMARY] KEY]
  [COMMENT 'string']
```

[Postgres](https://www.postgresql.org/docs/current/ddl-generated-columns.html):

```sql
CREATE TABLE people (
    ...,
    height_cm numeric,
    height_in numeric GENERATED ALWAYS AS (height_cm / 2.54) STORED
);
```



Discussion / hints:
Thanks for reporting, yes column specification might miss some corner cases. I am a bit short on time for the next weeks, but I can have a look.
As of now, the parser can only use `indentifiers` there after this `as`: https://github.com/DerekStride/tree-sitter-sql/blob/9ed90cc742a396423610d0523db0ea5751fa795e/grammar.js#L2206

Should be at least an `_expression`

Fix patch:
diff --git a/grammar.js b/grammar.js
index 49642592..acb15ad7 100644
--- a/grammar.js
+++ b/grammar.js
@@ -264,6 +264,7 @@ module.exports = grammar({
     // Hive Keywords
     keyword_external: _ => make_keyword("external"),
     keyword_stored: _ => make_keyword("stored"),
+    keyword_virtual: _ => make_keyword("virtual"),
     keyword_cached: _ => make_keyword("cached"),
     keyword_uncached: _ => make_keyword("uncached"),
     keyword_replication: _ => make_keyword("replication"),
@@ -2190,22 +2191,26 @@ module.exports = grammar({
       alias($._literal_string, $.literal)
     ),
 
-    _column_constraint: $ => choice(
-        choice(
-            $.keyword_null,
-            $._not_null,
-        ),
-        $._default_expression,
-        $._primary_key,
-        $.keyword_auto_increment,
-        $.direction,
-        $._column_comment,
-        seq(
-          optional(seq($.keyword_generated, $.keyword_always)),
-          $.keyword_as,
-          $.identifier,
-        ),
-    ),
+    _column_constraint: $ => prec.left(choice(
+      choice(
+        $.keyword_null,
+        $._not_null,
+      ),
+      $._default_expression,
+      $._primary_key,
+      $.keyword_auto_increment,
+      $.direction,
+      $._column_comment,
+      seq(
+        optional(seq($.keyword_generated, $.keyword_always)),
+        $.keyword_as,
+        $._expression,
+      ),
+      choice(
+        $.keyword_stored,
+        $.keyword_virtual,
+      )
+    )),
 
     _default_expression: $ => seq(
       $.keyword_default,
diff --git a/queries/highlights.scm b/queries/highlights.scm
index fdd42238..a3fa5ff5 100644
--- a/queries/highlights.scm
+++ b/queries/highlights.scm
@@ -206,6 +206,7 @@
   (keyword_uncached)
   (keyword_lines)
   (keyword_stored)
+  (keyword_virtual)
   (keyword_partitioned)
   (keyword_analyze)
   (keyword_explain)


Test patch:
diff --git a/test/corpus/create.txt b/test/corpus/create.txt
index 69006142..8fcfc11f 100644
--- a/test/corpus/create.txt
+++ b/test/corpus/create.txt
@@ -837,7 +837,7 @@ CREATE TABLE type_test (
   a_datetime DATETIME,
   a_datetime2 DATETIME2,
   a_datetimeoffset DATETIMEOFFSET,
-  a_smalldatetime SMALLDATE,
+  a_smalldatetime SMALLDATETIME,
   a_time TIME,
   a_timestamp TIMESTAMP,
   a_verbose_timestamp TIMESTAMP WITHOUT TIME ZONE,
@@ -1034,7 +1034,8 @@ CREATE TABLE type_test (
           type: (datetimeoffset
             (keyword_datetimeoffset)))
         (column_definition
-          name: (identifier))
+          name: (identifier)
+          type: (keyword_smalldatetime))
         (column_definition
           name: (identifier)
           type: (time
@@ -1584,7 +1585,15 @@ Create table with generated always
 
 CREATE TABLE "Role" (
   "roleId" bigint generated always as identity,
-  "name" varchar NOT NULL
+  "name" varchar NOT NULL,
+  height_in numeric GENERATED ALWAYS AS (height_cm / 2.54) STORED,
+  item_type_name TEXT GENERATED ALWAYS AS (
+     CASE item_type
+       WHEN 1 THEN 'foo'
+       WHEN 2 THEN 'bar'
+       ELSE 'UNKNOWN'
+     END
+  ) VIRTUAL
 );
 
 --------------------------------------------------------------------------------
@@ -1604,13 +1613,48 @@ CREATE TABLE "Role" (
           (keyword_generated)
           (keyword_always)
           (keyword_as)
-          (identifier))
+          (field
+            name: (identifier)))
         (column_definition
           name: (literal)
           type: (varchar
             (keyword_varchar))
           (keyword_not)
-          (keyword_null))))))
+          (keyword_null))
+        (column_definition
+          name: (identifier)
+          type: (numeric
+            (keyword_numeric))
+          (keyword_generated)
+          (keyword_always)
+          (keyword_as)
+          (binary_expression
+            left: (field
+              name: (identifier))
+            right: (literal))
+          (keyword_stored))
+        (column_definition
+          name: (identifier)
+          type: (keyword_text)
+          (keyword_generated)
+          (keyword_always)
+          (keyword_as)
+          (case
+            (keyword_case)
+            (field
+              name: (identifier))
+            (keyword_when)
+            (literal)
+            (keyword_then)
+            (literal)
+            (keyword_when)
+            (literal)
+            (keyword_then)
+            (literal)
+            (keyword_else)
+            (literal)
+            (keyword_end))
+          (keyword_virtual))))))
 
 ================================================================================
 Inet type