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

Issue:
fail to parse query with GROUPING SETS modifier
```sql
SELECT
    datacenter,
    distro, 
    SUM (quantity) AS qty
FROM
    servers
GROUP BY
    GROUPING SETS(
        (datacenter,distro),
        (datacenter),
        (distro),
        ()
    )
```

throws **_failed to parse query: line 7:13 <EOF> or ';' was expected, but got: "SETS"_**

Pull request:
Add support of `GROUPING SETS` syntax for the group by clause
This closes #149.

Patch:
diff --git a/parser/ast.go b/parser/ast.go
index 1ebb841..65bfbf7 100644
--- a/parser/ast.go
+++ b/parser/ast.go
@@ -5048,6 +5048,7 @@ func (w *PrewhereClause) Accept(visitor ASTVisitor) error {
 
 type GroupByClause struct {
 	GroupByPos    Pos
+	GroupByEnd    Pos
 	AggregateType string
 	Expr          Expr
 	WithCube      bool
@@ -5060,7 +5061,7 @@ func (g *GroupByClause) Pos() Pos {
 }
 
 func (g *GroupByClause) End() Pos {
-	return g.Expr.End()
+	return g.GroupByEnd
 }
 
 func (g *GroupByClause) String() string {
@@ -5069,7 +5070,9 @@ func (g *GroupByClause) String() string {
 	if g.AggregateType != "" {
 		builder.WriteString(g.AggregateType)
 	}
-	builder.WriteString(g.Expr.String())
+	if g.Expr != nil {
+		builder.WriteString(g.Expr.String())
+	}
 	if g.WithCube {
 		builder.WriteString(" WITH CUBE")
 	}
@@ -5085,8 +5088,10 @@ func (g *GroupByClause) String() string {
 func (g *GroupByClause) Accept(visitor ASTVisitor) error {
 	visitor.enter(g)
 	defer visitor.leave(g)
-	if err := g.Expr.Accept(visitor); err != nil {
-		return err
+	if g.Expr != nil {
+		if err := g.Expr.Accept(visitor); err != nil {
+			return err
+		}
 	}
 	return visitor.VisitGroupByExpr(g)
 }
diff --git a/parser/keyword.go b/parser/keyword.go
index 655f9f7..64eb4b0 100644
--- a/parser/keyword.go
+++ b/parser/keyword.go
@@ -89,6 +89,7 @@ const (
 	KeywordGrant        = "GRANT"
 	KeywordGranularity  = "GRANULARITY"
 	KeywordGroup        = "GROUP"
+	KeywordGrouping     = "GROUPING"
 	KeywordHaving       = "HAVING"
 	KeywordHierarchical = "HIERARCHICAL"
 	KeywordHour         = "HOUR"
@@ -181,6 +182,7 @@ const (
 	KeywordSemi         = "SEMI"
 	KeywordSends        = "SENDS"
 	KeywordSet          = "SET"
+	KeywordSets         = "SETS"
 	KeywordSettings     = "SETTINGS"
 	KeywordShow         = "SHOW"
 	KeywordShutdown     = "SHUTDOWN"
@@ -317,6 +319,7 @@ var keywords = NewSet(
 	KeywordGrant,
 	KeywordGranularity,
 	KeywordGroup,
+	KeywordGrouping,
 	KeywordHaving,
 	KeywordHierarchical,
 	KeywordHour,
@@ -409,6 +412,7 @@ var keywords = NewSet(
 	KeywordSemi,
 	KeywordSends,
 	KeywordSet,
+	KeywordSets,
 	KeywordSettings,
 	KeywordShow,
 	KeywordShutdown,
diff --git a/parser/parser_query.go b/parser/parser_query.go
index b335169..53b78d7 100644
--- a/parser/parser_query.go
+++ b/parser/parser_query.go
@@ -419,17 +419,22 @@ func (p *Parser) parseGroupByClause(pos Pos) (*GroupByClause, error) {
 	var expr Expr
 	var err error
 	aggregateType := ""
-	if p.matchKeyword(KeywordCube) || p.matchKeyword(KeywordRollup) {
+	switch {
+	case p.matchKeyword(KeywordCube) || p.matchKeyword(KeywordRollup):
 		aggregateType = p.last().String
 		_ = p.lexer.consumeToken()
 		expr, err = p.parseFunctionParams(p.Pos())
-	} else {
+	case p.tryConsumeKeywords(KeywordGrouping, KeywordSets):
+		aggregateType = "GROUPING SETS"
+		expr, err = p.parseFunctionParams(p.Pos())
+	case p.tryConsumeKeywords(KeywordAll):
+		aggregateType = "ALL"
+	default:
 		expr, err = p.parseColumnExprListWithLParen(p.Pos())
 	}
 	if err != nil {
 		return nil, err
 	}
-
 	groupBy := &GroupByClause{
 		GroupByPos:    pos,
 		AggregateType: aggregateType,
@@ -449,6 +454,7 @@ func (p *Parser) parseGroupByClause(pos Pos) (*GroupByClause, error) {
 			return nil, fmt.Errorf("expected CUBE, ROLLUP or TOTALS, got %s", p.lastTokenKind())
 		}
 	}
+	groupBy.GroupByEnd = p.Pos()
 
 	return groupBy, nil
 }


Test patch:
diff --git a/parser/testdata/ddl/output/alter_table_add_projection.sql.golden.json b/parser/testdata/ddl/output/alter_table_add_projection.sql.golden.json
index 168d3b6..ef0201e 100644
--- a/parser/testdata/ddl/output/alter_table_add_projection.sql.golden.json
+++ b/parser/testdata/ddl/output/alter_table_add_projection.sql.golden.json
@@ -50,6 +50,7 @@
             },
             "GroupBy": {
               "GroupByPos": 86,
+              "GroupByEnd": 105,
               "AggregateType": "",
               "Expr": {
                 "ListPos": 95,
diff --git a/parser/testdata/query/format/select_with_group_by.sql b/parser/testdata/query/format/select_with_group_by.sql
new file mode 100644
index 0000000..83c657a
--- /dev/null
+++ b/parser/testdata/query/format/select_with_group_by.sql
@@ -0,0 +1,26 @@
+-- Origin SQL:
+SELECT
+    datacenter,
+    distro,
+    SUM (quantity) AS qty
+FROM
+    servers
+GROUP BY
+    GROUPING SETS(
+    (datacenter,distro),
+    (datacenter),
+    (distro),
+    ()
+);
+
+SELECT
+    datacenter,
+    distro,
+    SUM (quantity) AS qty
+FROM
+    servers
+GROUP BY ALL;
+
+-- Format SQL:
+SELECT datacenter, distro, SUM(quantity) AS qty FROM servers GROUP BY GROUPING SETS((datacenter, distro), (datacenter), (distro), ());
+SELECT datacenter, distro, SUM(quantity) AS qty FROM servers GROUP BY ALL;
diff --git a/parser/testdata/query/output/select_simple.sql.golden.json b/parser/testdata/query/output/select_simple.sql.golden.json
index a3b88ac..7cbc54b 100644
--- a/parser/testdata/query/output/select_simple.sql.golden.json
+++ b/parser/testdata/query/output/select_simple.sql.golden.json
@@ -357,6 +357,7 @@
     },
     "GroupBy": {
       "GroupByPos": 239,
+      "GroupByEnd": 258,
       "AggregateType": "",
       "Expr": {
         "ListPos": 248,
diff --git a/parser/testdata/query/output/select_simple_with_group_by_with_cube_totals.sql.golden.json b/parser/testdata/query/output/select_simple_with_group_by_with_cube_totals.sql.golden.json
index 5e85a82..8ea3165 100644
--- a/parser/testdata/query/output/select_simple_with_group_by_with_cube_totals.sql.golden.json
+++ b/parser/testdata/query/output/select_simple_with_group_by_with_cube_totals.sql.golden.json
@@ -78,6 +78,7 @@
     "Where": null,
     "GroupBy": {
       "GroupByPos": 37,
+      "GroupByEnd": 76,
       "AggregateType": "CUBE",
       "Expr": {
         "LeftParenPos": 50,
diff --git a/parser/testdata/query/output/select_with_group_by.sql.golden.json b/parser/testdata/query/output/select_with_group_by.sql.golden.json
new file mode 100644
index 0000000..f870eb4
--- /dev/null
+++ b/parser/testdata/query/output/select_with_group_by.sql.golden.json
@@ -0,0 +1,332 @@
+[
+  {
+    "SelectPos": 0,
+    "StatementEnd": 171,
+    "With": null,
+    "Top": null,
+    "SelectItems": [
+      {
+        "Expr": {
+          "Name": "datacenter",
+          "QuoteType": 1,
+          "NamePos": 11,
+          "NameEnd": 21
+        },
+        "Modifiers": [],
+        "Alias": null
+      },
+      {
+        "Expr": {
+          "Name": "distro",
+          "QuoteType": 1,
+          "NamePos": 27,
+          "NameEnd": 33
+        },
+        "Modifiers": [],
+        "Alias": null
+      },
+      {
+        "Expr": {
+          "Name": {
+            "Name": "SUM",
+            "QuoteType": 1,
+            "NamePos": 39,
+            "NameEnd": 42
+          },
+          "Params": {
+            "LeftParenPos": 43,
+            "RightParenPos": 52,
+            "Items": {
+              "ListPos": 44,
+              "ListEnd": 52,
+              "HasDistinct": false,
+              "Items": [
+                {
+                  "Expr": {
+                    "Name": "quantity",
+                    "QuoteType": 1,
+                    "NamePos": 44,
+                    "NameEnd": 52
+                  },
+                  "Alias": null
+                }
+              ]
+            },
+            "ColumnArgList": null
+          }
+        },
+        "Modifiers": [],
+        "Alias": {
+          "Name": "qty",
+          "QuoteType": 1,
+          "NamePos": 57,
+          "NameEnd": 60
+        }
+      }
+    ],
+    "From": {
+      "FromPos": 61,
+      "Expr": {
+        "Table": {
+          "TablePos": 70,
+          "TableEnd": 77,
+          "Alias": null,
+          "Expr": {
+            "Database": null,
+            "Table": {
+              "Name": "servers",
+              "QuoteType": 1,
+              "NamePos": 70,
+              "NameEnd": 77
+            }
+          },
+          "HasFinal": false
+        },
+        "StatementEnd": 77,
+        "SampleRatio": null,
+        "HasFinal": false
+      }
+    },
+    "ArrayJoin": null,
+    "Window": null,
+    "Prewhere": null,
+    "Where": null,
+    "GroupBy": {
+      "GroupByPos": 78,
+      "GroupByEnd": 171,
+      "AggregateType": "GROUPING SETS",
+      "Expr": {
+        "LeftParenPos": 104,
+        "RightParenPos": 170,
+        "Items": {
+          "ListPos": 110,
+          "ListEnd": 168,
+          "HasDistinct": false,
+          "Items": [
+            {
+              "Expr": {
+                "LeftParenPos": 110,
+                "RightParenPos": 128,
+                "Items": {
+                  "ListPos": 111,
+                  "ListEnd": 128,
+                  "HasDistinct": false,
+                  "Items": [
+                    {
+                      "Expr": {
+                        "Name": "datacenter",
+                        "QuoteType": 1,
+                        "NamePos": 111,
+                        "NameEnd": 121
+                      },
+                      "Alias": null
+                    },
+                    {
+                      "Expr": {
+                        "Name": "distro",
+                        "QuoteType": 1,
+                        "NamePos": 122,
+                        "NameEnd": 128
+                      },
+                      "Alias": null
+                    }
+                  ]
+                },
+                "ColumnArgList": null
+              },
+              "Alias": null
+            },
+            {
+              "Expr": {
+                "LeftParenPos": 135,
+                "RightParenPos": 146,
+                "Items": {
+                  "ListPos": 136,
+                  "ListEnd": 146,
+                  "HasDistinct": false,
+                  "Items": [
+                    {
+                      "Expr": {
+                        "Name": "datacenter",
+                        "QuoteType": 1,
+                        "NamePos": 136,
+                        "NameEnd": 146
+                      },
+                      "Alias": null
+                    }
+                  ]
+                },
+                "ColumnArgList": null
+              },
+              "Alias": null
+            },
+            {
+              "Expr": {
+                "LeftParenPos": 153,
+                "RightParenPos": 160,
+                "Items": {
+                  "ListPos": 154,
+                  "ListEnd": 160,
+                  "HasDistinct": false,
+                  "Items": [
+                    {
+                      "Expr": {
+                        "Name": "distro",
+                        "QuoteType": 1,
+                        "NamePos": 154,
+                        "NameEnd": 160
+                      },
+                      "Alias": null
+                    }
+                  ]
+                },
+                "ColumnArgList": null
+              },
+              "Alias": null
+            },
+            {
+              "Expr": {
+                "LeftParenPos": 167,
+                "RightParenPos": 168,
+                "Items": {
+                  "ListPos": 168,
+                  "ListEnd": 168,
+                  "HasDistinct": false,
+                  "Items": []
+                },
+                "ColumnArgList": null
+              },
+              "Alias": null
+            }
+          ]
+        },
+        "ColumnArgList": null
+      },
+      "WithCube": false,
+      "WithRollup": false,
+      "WithTotals": false
+    },
+    "WithTotal": false,
+    "Having": null,
+    "OrderBy": null,
+    "LimitBy": null,
+    "Limit": null,
+    "Settings": null,
+    "Format": null,
+    "UnionAll": null,
+    "UnionDistinct": null,
+    "Except": null
+  },
+  {
+    "SelectPos": 174,
+    "StatementEnd": 264,
+    "With": null,
+    "Top": null,
+    "SelectItems": [
+      {
+        "Expr": {
+          "Name": "datacenter",
+          "QuoteType": 1,
+          "NamePos": 185,
+          "NameEnd": 195
+        },
+        "Modifiers": [],
+        "Alias": null
+      },
+      {
+        "Expr": {
+          "Name": "distro",
+          "QuoteType": 1,
+          "NamePos": 201,
+          "NameEnd": 207
+        },
+        "Modifiers": [],
+        "Alias": null
+      },
+      {
+        "Expr": {
+          "Name": {
+            "Name": "SUM",
+            "QuoteType": 1,
+            "NamePos": 213,
+            "NameEnd": 216
+          },
+          "Params": {
+            "LeftParenPos": 217,
+            "RightParenPos": 226,
+            "Items": {
+              "ListPos": 218,
+              "ListEnd": 226,
+              "HasDistinct": false,
+              "Items": [
+                {
+                  "Expr": {
+                    "Name": "quantity",
+                    "QuoteType": 1,
+                    "NamePos": 218,
+                    "NameEnd": 226
+                  },
+                  "Alias": null
+                }
+              ]
+            },
+            "ColumnArgList": null
+          }
+        },
+        "Modifiers": [],
+        "Alias": {
+          "Name": "qty",
+          "QuoteType": 1,
+          "NamePos": 231,
+          "NameEnd": 234
+        }
+      }
+    ],
+    "From": {
+      "FromPos": 235,
+      "Expr": {
+        "Table": {
+          "TablePos": 244,
+          "TableEnd": 251,
+          "Alias": null,
+          "Expr": {
+            "Database": null,
+            "Table": {
+              "Name": "servers",
+              "QuoteType": 1,
+              "NamePos": 244,
+              "NameEnd": 251
+            }
+          },
+          "HasFinal": false
+        },
+        "StatementEnd": 251,
+        "SampleRatio": null,
+        "HasFinal": false
+      }
+    },
+    "ArrayJoin": null,
+    "Window": null,
+    "Prewhere": null,
+    "Where": null,
+    "GroupBy": {
+      "GroupByPos": 252,
+      "GroupByEnd": 264,
+      "AggregateType": "ALL",
+      "Expr": null,
+      "WithCube": false,
+      "WithRollup": false,
+      "WithTotals": false
+    },
+    "WithTotal": false,
+    "Having": null,
+    "OrderBy": null,
+    "LimitBy": null,
+    "Limit": null,
+    "Settings": null,
+    "Format": null,
+    "UnionAll": null,
+    "UnionDistinct": null,
+    "Except": null
+  }
+]
\ No newline at end of file
diff --git a/parser/testdata/query/output/select_with_keyword_in_group_by.sql.golden.json b/parser/testdata/query/output/select_with_keyword_in_group_by.sql.golden.json
index e7ea4f9..1baf028 100644
--- a/parser/testdata/query/output/select_with_keyword_in_group_by.sql.golden.json
+++ b/parser/testdata/query/output/select_with_keyword_in_group_by.sql.golden.json
@@ -123,6 +123,7 @@
     },
     "GroupBy": {
       "GroupByPos": 112,
+      "GroupByEnd": 145,
       "AggregateType": "",
       "Expr": {
         "ListPos": 121,
diff --git a/parser/testdata/query/select_with_group_by.sql b/parser/testdata/query/select_with_group_by.sql
new file mode 100644
index 0000000..9b0a62c
--- /dev/null
+++ b/parser/testdata/query/select_with_group_by.sql
@@ -0,0 +1,21 @@
+SELECT
+    datacenter,
+    distro,
+    SUM (quantity) AS qty
+FROM
+    servers
+GROUP BY
+    GROUPING SETS(
+    (datacenter,distro),
+    (datacenter),
+    (distro),
+    ()
+);
+
+SELECT
+    datacenter,
+    distro,
+    SUM (quantity) AS qty
+FROM
+    servers
+GROUP BY ALL;
\ No newline at end of file