Repository: AfterShip/clickhouse-sql-parser (go)

Issue:
Create materialized view fails when specifying an ORDER BY clause
See this MV setup:

```
CREATE MATERIALIZED VIEW IF NOT EXISTS test_mv
ENGINE = ReplacingMergeTree()
PRIMARY KEY (id)
ORDER BY (id)
AS
SELECT * FROM test_table;
```

Which fails to create:

```
Received unexpected error:
line 5:7 <EOF> or ';' was expected, but got: "*"
SELECT * FROM test_table;
       ^
```

The issue seems to be that the parser interprets `AS SELECT` in `ORDER BY (id) AS SELECT * FROM test_table` as alias for the id column instead of the query for the materialized view in [here](https://github.com/AfterShip/clickhouse-sql-parser/blob/d03ad5b77f8c0621486596d2bc7e2bb8af2ffe7a/parser/parser_table.go#L672).

Pull request:
Fix SubQuery was being recognized as an ident
This closes #131 

Patch:
diff --git a/parser/parser_table.go b/parser/parser_table.go
index a0ebe62..7bbab20 100644
--- a/parser/parser_table.go
+++ b/parser/parser_table.go
@@ -669,7 +669,16 @@ func (p *Parser) parseOrderExpr(pos Pos) (*OrderExpr, error) {
 	}
 
 	var alias *Ident
-	if p.tryConsumeKeyword(KeywordAs) != nil {
+	if p.matchKeyword(KeywordAs) {
+		// It should be a subquery instead of an order by alias if the `AS` is followed by `SELECT` keyword.
+		if nextToken, err := p.lexer.peekToken(); err == nil && nextToken.ToString() == KeywordSelect {
+			return &OrderExpr{
+				OrderPos: pos,
+				Expr:     columnExpr,
+			}, nil
+		}
+		// consume the `AS` keyword
+		_ = p.lexer.consumeToken()
 		alias, err = p.parseIdent()
 		if err != nil {
 			return nil, err


Test patch:
diff --git a/parser/testdata/ddl/create_mv_with_order_by.sql b/parser/testdata/ddl/create_mv_with_order_by.sql
new file mode 100644
index 0000000..2c0ae0f
--- /dev/null
+++ b/parser/testdata/ddl/create_mv_with_order_by.sql
@@ -0,0 +1,12 @@
+CREATE MATERIALIZED VIEW IF NOT EXISTS test_mv
+ENGINE = ReplacingMergeTree()
+PRIMARY KEY (id)
+ORDER BY (id)
+AS
+SELECT * FROM test_table;
+
+CREATE MATERIALIZED VIEW IF NOT EXISTS test_mv
+ENGINE = ReplacingMergeTree()
+PRIMARY KEY (id)
+AS
+SELECT * FROM test_table;
\ No newline at end of file
diff --git a/parser/testdata/ddl/format/create_mv_with_order_by.sql b/parser/testdata/ddl/format/create_mv_with_order_by.sql
new file mode 100644
index 0000000..8c29062
--- /dev/null
+++ b/parser/testdata/ddl/format/create_mv_with_order_by.sql
@@ -0,0 +1,17 @@
+-- Origin SQL:
+CREATE MATERIALIZED VIEW IF NOT EXISTS test_mv
+ENGINE = ReplacingMergeTree()
+PRIMARY KEY (id)
+ORDER BY (id)
+AS
+SELECT * FROM test_table;
+
+CREATE MATERIALIZED VIEW IF NOT EXISTS test_mv
+ENGINE = ReplacingMergeTree()
+PRIMARY KEY (id)
+AS
+SELECT * FROM test_table;
+
+-- Format SQL:
+CREATE MATERIALIZED VIEW IF NOT EXISTS test_mv ENGINE = ReplacingMergeTree() PRIMARY KEY (id) ORDER BY (id)  AS SELECT * FROM test_table;
+CREATE MATERIALIZED VIEW IF NOT EXISTS test_mv ENGINE = ReplacingMergeTree() PRIMARY KEY (id) AS SELECT * FROM test_table;
diff --git a/parser/testdata/ddl/output/create_mv_with_order_by.sql.golden.json b/parser/testdata/ddl/output/create_mv_with_order_by.sql.golden.json
new file mode 100644
index 0000000..2934ef4
--- /dev/null
+++ b/parser/testdata/ddl/output/create_mv_with_order_by.sql.golden.json
@@ -0,0 +1,277 @@
+[
+  {
+    "CreatePos": 0,
+    "StatementEnd": 135,
+    "Name": {
+      "Database": null,
+      "Table": {
+        "Name": "test_mv",
+        "QuoteType": 1,
+        "NamePos": 39,
+        "NameEnd": 46
+      }
+    },
+    "IfNotExists": true,
+    "OnCluster": null,
+    "Engine": {
+      "EnginePos": 47,
+      "EngineEnd": 106,
+      "Name": "ReplacingMergeTree",
+      "Params": {
+        "LeftParenPos": 74,
+        "RightParenPos": 75,
+        "Items": {
+          "ListPos": 75,
+          "ListEnd": 75,
+          "HasDistinct": false,
+          "Items": []
+        },
+        "ColumnArgList": null
+      },
+      "PrimaryKey": {
+        "PrimaryPos": 77,
+        "Expr": {
+          "LeftParenPos": 89,
+          "RightParenPos": 92,
+          "Items": {
+            "ListPos": 90,
+            "ListEnd": 92,
+            "HasDistinct": false,
+            "Items": [
+              {
+                "Expr": {
+                  "Name": "id",
+                  "QuoteType": 1,
+                  "NamePos": 90,
+                  "NameEnd": 92
+                },
+                "Alias": null
+              }
+            ]
+          },
+          "ColumnArgList": null
+        }
+      },
+      "PartitionBy": null,
+      "SampleBy": null,
+      "TTL": null,
+      "Settings": null,
+      "OrderBy": {
+        "OrderPos": 94,
+        "ListEnd": 106,
+        "Items": [
+          {
+            "OrderPos": 94,
+            "Expr": {
+              "LeftParenPos": 103,
+              "RightParenPos": 106,
+              "Items": {
+                "ListPos": 104,
+                "ListEnd": 106,
+                "HasDistinct": false,
+                "Items": [
+                  {
+                    "Expr": {
+                      "Name": "id",
+                      "QuoteType": 1,
+                      "NamePos": 104,
+                      "NameEnd": 106
+                    },
+                    "Alias": null
+                  }
+                ]
+              },
+              "ColumnArgList": null
+            },
+            "Alias": null,
+            "Direction": ""
+          }
+        ]
+      }
+    },
+    "Destination": null,
+    "SubQuery": {
+      "HasParen": false,
+      "Select": {
+        "SelectPos": 111,
+        "StatementEnd": 135,
+        "With": null,
+        "Top": null,
+        "SelectItems": [
+          {
+            "Expr": {
+              "Name": "*",
+              "QuoteType": 0,
+              "NamePos": 118,
+              "NameEnd": 118
+            },
+            "Modifiers": [],
+            "Alias": null
+          }
+        ],
+        "From": {
+          "FromPos": 120,
+          "Expr": {
+            "Table": {
+              "TablePos": 125,
+              "TableEnd": 135,
+              "Alias": null,
+              "Expr": {
+                "Database": null,
+                "Table": {
+                  "Name": "test_table",
+                  "QuoteType": 1,
+                  "NamePos": 125,
+                  "NameEnd": 135
+                }
+              },
+              "HasFinal": false
+            },
+            "StatementEnd": 135,
+            "SampleRatio": null,
+            "HasFinal": false
+          }
+        },
+        "ArrayJoin": null,
+        "Window": null,
+        "Prewhere": null,
+        "Where": null,
+        "GroupBy": null,
+        "WithTotal": false,
+        "Having": null,
+        "OrderBy": null,
+        "LimitBy": null,
+        "Limit": null,
+        "Settings": null,
+        "Format": null,
+        "UnionAll": null,
+        "UnionDistinct": null,
+        "Except": null
+      }
+    },
+    "Populate": false,
+    "Comment": null
+  },
+  {
+    "CreatePos": 138,
+    "StatementEnd": 259,
+    "Name": {
+      "Database": null,
+      "Table": {
+        "Name": "test_mv",
+        "QuoteType": 1,
+        "NamePos": 177,
+        "NameEnd": 184
+      }
+    },
+    "IfNotExists": true,
+    "OnCluster": null,
+    "Engine": {
+      "EnginePos": 185,
+      "EngineEnd": 230,
+      "Name": "ReplacingMergeTree",
+      "Params": {
+        "LeftParenPos": 212,
+        "RightParenPos": 213,
+        "Items": {
+          "ListPos": 213,
+          "ListEnd": 213,
+          "HasDistinct": false,
+          "Items": []
+        },
+        "ColumnArgList": null
+      },
+      "PrimaryKey": {
+        "PrimaryPos": 215,
+        "Expr": {
+          "LeftParenPos": 227,
+          "RightParenPos": 230,
+          "Items": {
+            "ListPos": 228,
+            "ListEnd": 230,
+            "HasDistinct": false,
+            "Items": [
+              {
+                "Expr": {
+                  "Name": "id",
+                  "QuoteType": 1,
+                  "NamePos": 228,
+                  "NameEnd": 230
+                },
+                "Alias": null
+              }
+            ]
+          },
+          "ColumnArgList": null
+        }
+      },
+      "PartitionBy": null,
+      "SampleBy": null,
+      "TTL": null,
+      "Settings": null,
+      "OrderBy": null
+    },
+    "Destination": null,
+    "SubQuery": {
+      "HasParen": false,
+      "Select": {
+        "SelectPos": 235,
+        "StatementEnd": 259,
+        "With": null,
+        "Top": null,
+        "SelectItems": [
+          {
+            "Expr": {
+              "Name": "*",
+              "QuoteType": 0,
+              "NamePos": 242,
+              "NameEnd": 242
+            },
+            "Modifiers": [],
+            "Alias": null
+          }
+        ],
+        "From": {
+          "FromPos": 244,
+          "Expr": {
+            "Table": {
+              "TablePos": 249,
+              "TableEnd": 259,
+              "Alias": null,
+              "Expr": {
+                "Database": null,
+                "Table": {
+                  "Name": "test_table",
+                  "QuoteType": 1,
+                  "NamePos": 249,
+                  "NameEnd": 259
+                }
+              },
+              "HasFinal": false
+            },
+            "StatementEnd": 259,
+            "SampleRatio": null,
+            "HasFinal": false
+          }
+        },
+        "ArrayJoin": null,
+        "Window": null,
+        "Prewhere": null,
+        "Where": null,
+        "GroupBy": null,
+        "WithTotal": false,
+        "Having": null,
+        "OrderBy": null,
+        "LimitBy": null,
+        "Limit": null,
+        "Settings": null,
+        "Format": null,
+        "UnionAll": null,
+        "UnionDistinct": null,
+        "Except": null
+      }
+    },
+    "Populate": false,
+    "Comment": null
+  }
+]
\ No newline at end of file