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

Issue:
Parsing nested CTEs
I found that the following valid PostgreSQL query involving nested CTEs is not parsed correctly by this grammar:

```sql
WITH top_cte AS (
  WITH nested_cte AS (
    SELECT 1 as one, 2 as two
  )
  SELECT one, two
  FROM nested_cte
)
SELECT *
FROM top_cte;
```

Received AST:

```
(program
  (ERROR
    (statement
      (keyword_with)
      (cte
        (identifier)
        (keyword_as)
        (ERROR
          (keyword_as))
        (statement
          (select
            (keyword_select)
            (select_expression
              (term
                (literal)
                (keyword_as)
                (identifier))
              (term
                (literal)
                (keyword_as)
                (identifier))))))
      (select
        (keyword_select)
        (select_expression
          (term
            (field
              (identifier)))
          (term
            (field
              (identifier)))))
      (from
        (keyword_from)
        (relation
          (object_reference
            (identifier))))))
  (statement
    (select
      (keyword_select)
      (select_expression
        (term
          (all_fields))))
    (from
      (keyword_from)
      (relation
        (object_reference
          (identifier))))))
```

[This SO answer](https://stackoverflow.com/questions/1413516/can-you-create-nested-with-clauses-for-common-table-expressions) says nested CTEs are not valid, at least for T-SQL. I found that PostgreSQL has no problem executing that query, though:

![image](https://github.com/DerekStride/tree-sitter-sql/assets/889383/aabe3b7e-eb2f-484d-95fa-3bd2411fe8ea)



Discussion / hints:
@dmfay I was thinking that this should be fairly simple. I tested to add `$._cte` to the choices in https://github.com/DerekStride/tree-sitter-sql/blob/ddd1763a4c2dbe97477ff36168ebe28a8da373a7/grammar.js#L609. In my mind this should work, but I still get an error. Any ideas?

Fix patch:
diff --git a/grammar.js b/grammar.js
index 617a6a64..832d6fd8 100644
--- a/grammar.js
+++ b/grammar.js
@@ -598,15 +598,14 @@ module.exports = grammar({
       optional(
         seq(
           optional($.keyword_not),
-          $.keyword_materialized),
+          $.keyword_materialized,
+        ),
       ),
       '(',
       alias(
         choice(
-          $._select_statement,
-          $._delete_statement,
-          $._insert_statement,
-          $._update_statement,
+          $._dml_read,
+          $._dml_write,
         ),
         $.statement,
       ),


Test patch:
diff --git a/test/corpus/cte.txt b/test/corpus/cte.txt
index a0b92805..a0921230 100644
--- a/test/corpus/cte.txt
+++ b/test/corpus/cte.txt
@@ -213,3 +213,149 @@ Parenthesized CTE
       (relation
         (object_reference
           (identifier))))))
+
+================================================================================
+Nested CTE
+================================================================================
+
+WITH top_cte AS (
+  WITH nested_cte AS (
+    SELECT 1 as one, 2 as two
+  )
+  SELECT one, two
+  FROM nested_cte
+)
+SELECT *
+FROM top_cte;
+
+--------------------------------------------------------------------------------
+
+(program
+ (statement
+  (keyword_with)
+  (cte
+   (identifier)
+   (keyword_as)
+   (statement
+    (keyword_with)
+    (cte
+     (identifier)
+     (keyword_as)
+     (statement
+      (select
+       (keyword_select)
+       (select_expression
+        (term
+         (literal)
+         (keyword_as)
+         (identifier))
+        (term
+         (literal)
+         (keyword_as)
+         (identifier))))))
+    (select
+     (keyword_select)
+     (select_expression
+      (term
+       (field
+        (identifier)))
+      (term
+       (field
+        (identifier)))))
+  (from
+   (keyword_from)
+   (relation
+    (object_reference
+     (identifier))))))
+  (select
+   (keyword_select)
+   (select_expression
+    (term
+     (all_fields))))
+  (from
+   (keyword_from)
+   (relation
+    (object_reference
+     (identifier))))))
+
+================================================================================
+Nested deeper
+================================================================================
+
+WITH top_cte AS (
+  WITH nested_cte AS (
+    WITH nested_further AS (
+      SELECT 1 as one, 2 as two
+    )
+    SELECT * FROM nested_further
+  )
+  SELECT one, two
+  FROM nested_cte
+)
+SELECT *
+FROM top_cte;
+
+--------------------------------------------------------------------------------
+
+(program
+ (statement
+  (keyword_with)
+  (cte
+   (identifier)
+   (keyword_as)
+   (statement
+    (keyword_with)
+    (cte
+     (identifier)
+     (keyword_as)
+     (statement
+      (keyword_with)
+      (cte
+       (identifier)
+       (keyword_as)
+       (statement
+        (select
+         (keyword_select)
+         (select_expression
+          (term
+           (literal)
+           (keyword_as)
+           (identifier))
+          (term
+           (literal)
+           (keyword_as)
+           (identifier))))))
+      (select
+       (keyword_select)
+       (select_expression
+        (term
+         (all_fields))))
+  (from
+   (keyword_from)
+   (relation
+    (object_reference
+     (identifier))))))
+  (select
+   (keyword_select)
+   (select_expression
+    (term
+     (field
+      (identifier)))
+    (term
+     (field
+      (identifier)))))
+  (from
+   (keyword_from)
+   (relation
+    (object_reference
+     (identifier))))))
+  (select
+   (keyword_select)
+   (select_expression
+    (term
+     (all_fields))))
+  (from
+   (keyword_from)
+   (relation
+    (object_reference
+     (identifier))))))