Repository: AfterShip/clickhouse-sql-parser
Pull request #160 (2025-05-08 21:42:30)

Adding support MV definer
Fixes this issue https://github.com/AfterShip/clickhouse-sql-parser/issues/159

coveralls: ## Pull Request Test Coverage Report for [Build 14916713754](https://coveralls.io/builds/73584335)


### Details

* **28** of **42**   **(66.67%)**  changed or added relevant lines in **2** files are covered.
* **2** unchanged lines in **1** file lost coverage.
* Overall coverage decreased (**-0.005%**) to **62.805%**

---

|  Changes Missing Coverage | Covered Lines | Changed/Added Lines | % |
| :-----|--------------|--------|---: |
| [parser/ast.go](https://coveralls.io/builds/73584335/source?filename=parser%2Fast.go#L1779) | 13 | 17 | 76.47%
| [parser/parser_view.go](https://coveralls.io/builds/73584335/source?filename=parser%2Fparser_view.go#L114) | 15 | 25 | 60.0%
<!-- | **Total:** | **28** | **42** | **66.67%** | -->

|  Files with Coverage Reduction | New Missed Lines | % |
| :-----|--------------|--: |
| [parser/parser_view.go](https://coveralls.io/builds/73584335/source?filename=parser%2Fparser_view.go#L106) | 2 | 65.66% |
<!-- | **Total:** | **2** |  | -->

|  Totals | [![Coverage Status](https://coveralls.io/builds/73584335/badge)](https://coveralls.io/builds/73584335) |
| :-- | --: |
| Change from base [Build 14908245721](https://coveralls.io/builds/73576445): |  -0.005% |
| Covered Lines: | 7053 |
| Relevant Lines: | 11230 |

---
##### 💛  - [Coveralls](https://coveralls.io)


Patch:
diff --git a/parser/ast.go b/parser/ast.go
index ab54f11..c762616 100644
--- a/parser/ast.go
+++ b/parser/ast.go
@@ -1634,6 +1634,8 @@ type CreateMaterializedView struct {
 	SubQuery     *SubQuery
 	Populate     bool
 	Comment      *StringLiteral
+	Definer      *Ident
+	SQLSecurity  string
 }
 
 func (c *CreateMaterializedView) Pos() Pos {
@@ -1697,14 +1699,21 @@ func (c *CreateMaterializedView) String() string {
 	if c.HasEmpty {
 		builder.WriteString(" EMPTY")
 	}
+	if c.Definer != nil {
+		builder.WriteString(" DEFINER = ")
+		builder.WriteString(c.Definer.String())
+	}
+	if c.SQLSecurity != "" {
+		builder.WriteString(" SQL SECURITY ")
+		builder.WriteString(c.SQLSecurity)
+	}
 	if c.Populate {
-		builder.WriteString(" POPULATE ")
+		builder.WriteString(" POPULATE")
 	}
 	if c.SubQuery != nil {
 		builder.WriteString(" AS ")
 		builder.WriteString(c.SubQuery.String())
 	}
-
 	if c.Comment != nil {
 		builder.WriteString(" COMMENT ")
 		builder.WriteString(c.Comment.String())
@@ -1765,6 +1774,16 @@ func (c *CreateMaterializedView) Accept(visitor ASTVisitor) error {
 			return err
 		}
 	}
+	if c.Definer != nil {
+		if err := c.Definer.Accept(visitor); err != nil {
+			return err
+		}
+	}
+	if c.Comment != nil {
+		if err := c.Comment.Accept(visitor); err != nil {
+			return err
+		}
+	}
 	return visitor.VisitCreateMaterializedView(c)
 }
 
diff --git a/parser/keyword.go b/parser/keyword.go
index 14cfd81..b623a78 100644
--- a/parser/keyword.go
+++ b/parser/keyword.go
@@ -234,6 +234,9 @@ const (
 	KeywordWindow       = "WINDOW"
 	KeywordWith         = "WITH"
 	KeywordYear         = "YEAR"
+	KeywordDefiner      = "DEFINER"
+	KeywordSQL          = "SQL"
+	KeywordSecurity     = "SECURITY"
 )
 
 var keywords = NewSet(
@@ -470,4 +473,7 @@ var keywords = NewSet(
 	KeywordWindow,
 	KeywordWith,
 	KeywordYear,
+	KeywordDefiner,
+	KeywordSQL,
+	KeywordSecurity,
 )
diff --git a/parser/parser_view.go b/parser/parser_view.go
index 672d710..e00712f 100644
--- a/parser/parser_view.go
+++ b/parser/parser_view.go
@@ -103,16 +103,44 @@ func (p *Parser) parseCreateMaterializedView(pos Pos) (*CreateMaterializedView,
 		}
 		createMaterializedView.Engine = engineExpr
 		createMaterializedView.StatementEnd = engineExpr.End()
-
-		if p.tryConsumeKeywords(KeywordPopulate) {
-			createMaterializedView.Populate = true
-			createMaterializedView.StatementEnd = p.Pos()
-		}
 	default:
 		return nil, fmt.Errorf("unexpected token: %q, expected TO or ENGINE", p.lastTokenKind())
 	}
 	createMaterializedView.HasEmpty = p.tryConsumeKeywords(KeywordEmpty)
 
+	// Parse DEFINER clause
+	if p.tryConsumeKeywords(KeywordDefiner) {
+		if err := p.expectTokenKind(TokenKindSingleEQ); err != nil {
+			return nil, err
+		}
+		definer, err := p.parseIdent()
+		if err != nil {
+			return nil, err
+		}
+		createMaterializedView.Definer = definer
+	}
+
+	// Parse SQL SECURITY clause
+	if p.tryConsumeKeywords(KeywordSQL, KeywordSecurity) {
+		if !p.matchOneOfKeywords(KeywordDefiner, KeywordNone) {
+			return nil, fmt.Errorf("expected DEFINER or NONE after SQL SECURITY, got %q", p.lastTokenKind())
+		}
+		createMaterializedView.SQLSecurity = p.last().String
+		_ = p.lexer.consumeToken()
+	}
+
+	// Check for POPULATE before AS SELECT - only valid with ENGINE and no Destination
+	if p.tryConsumeKeywords(KeywordPopulate) {
+		if createMaterializedView.Destination != nil {
+			return nil, fmt.Errorf("POPULATE is only allowed when using ENGINE, not with TO clause")
+		}
+		if createMaterializedView.Engine == nil {
+			return nil, fmt.Errorf("POPULATE requires ENGINE to be specified")
+		}
+		createMaterializedView.Populate = true
+		createMaterializedView.StatementEnd = p.Pos()
+	}
+
 	if p.tryConsumeKeywords(KeywordAs) {
 		subQuery, err := p.parseSubQuery(p.Pos())
 		if err != nil {


Test patch:
diff --git a/parser/testdata/ddl/create_materialized_view_with_definer.sql b/parser/testdata/ddl/create_materialized_view_with_definer.sql
new file mode 100644
index 0000000..964c491
--- /dev/null
+++ b/parser/testdata/ddl/create_materialized_view_with_definer.sql
@@ -0,0 +1,15 @@
+CREATE MATERIALIZED VIEW fresh_mv
+REFRESH EVERY 1 HOUR OFFSET 10 MINUTE APPEND TO events_export
+(
+    `timestamp` DateTime64(9),
+    `field_1` String,
+    `field_2` String,
+)
+DEFINER = default SQL SECURITY DEFINER
+AS (SELECT
+    timestamp,
+    field_1,
+    field_2,
+FROM event_table
+WHERE toStartOfHour(timestamp) = toStartOfHour(now() - toIntervalHour(1)))
+COMMENT 'Test comment'
diff --git a/parser/testdata/ddl/format/create_materialized_view_with_definer.sql b/parser/testdata/ddl/format/create_materialized_view_with_definer.sql
new file mode 100644
index 0000000..00ad9d0
--- /dev/null
+++ b/parser/testdata/ddl/format/create_materialized_view_with_definer.sql
@@ -0,0 +1,20 @@
+-- Origin SQL:
+CREATE MATERIALIZED VIEW fresh_mv
+REFRESH EVERY 1 HOUR OFFSET 10 MINUTE APPEND TO events_export
+(
+    `timestamp` DateTime64(9),
+    `field_1` String,
+    `field_2` String,
+)
+DEFINER = default SQL SECURITY DEFINER
+AS (SELECT
+    timestamp,
+    field_1,
+    field_2,
+FROM event_table
+WHERE toStartOfHour(timestamp) = toStartOfHour(now() - toIntervalHour(1)))
+COMMENT 'Test comment'
+
+
+-- Format SQL:
+CREATE MATERIALIZED VIEW fresh_mv REFRESH EVERY 1 HOUR OFFSET 10 MINUTE APPEND TO events_export (`timestamp` DateTime64(9), `field_1` String, `field_2` String) DEFINER = default SQL SECURITY DEFINER AS (SELECT timestamp, field_1, field_2, FROM AS event_table WHERE toStartOfHour(timestamp) = toStartOfHour(now() - toIntervalHour(1))) COMMENT 'Test comment';
diff --git a/parser/testdata/ddl/format/create_materialized_view_with_empty_table_schema.sql b/parser/testdata/ddl/format/create_materialized_view_with_empty_table_schema.sql
index 0cbf223..4b1a0a1 100644
--- a/parser/testdata/ddl/format/create_materialized_view_with_empty_table_schema.sql
+++ b/parser/testdata/ddl/format/create_materialized_view_with_empty_table_schema.sql
@@ -16,4 +16,4 @@ from
 where rn = 1;
 
 -- Format SQL:
-CREATE MATERIALIZED VIEW test.t0 ON CLUSTER default_cluster ENGINE = ReplicatedAggregatingMergeTree('/clickhouse/{layer}-{shard}/test/t0', '{replica}') PARTITION BY toYYYYMM(f0) ORDER BY (f0) POPULATE  AS SELECT f0, f1, f2, coalesce(f0, f1) AS f333 FROM (SELECT f0, f1, f2, ROW_NUMBER() OVER ( PARTITION BY f0 ORDER BY coalesce(f1, f2)) AS rn FROM test.t WHERE f3 IN ('foo', 'bar', 'test') AND env = 'test') AS tmp WHERE rn = 1;
+CREATE MATERIALIZED VIEW test.t0 ON CLUSTER default_cluster ENGINE = ReplicatedAggregatingMergeTree('/clickhouse/{layer}-{shard}/test/t0', '{replica}') PARTITION BY toYYYYMM(f0) ORDER BY (f0) POPULATE AS SELECT f0, f1, f2, coalesce(f0, f1) AS f333 FROM (SELECT f0, f1, f2, ROW_NUMBER() OVER ( PARTITION BY f0 ORDER BY coalesce(f1, f2)) AS rn FROM test.t WHERE f3 IN ('foo', 'bar', 'test') AND env = 'test') AS tmp WHERE rn = 1;
diff --git a/parser/testdata/ddl/output/bug_001.sql.golden.json b/parser/testdata/ddl/output/bug_001.sql.golden.json
index a1b6131..b1390c6 100644
--- a/parser/testdata/ddl/output/bug_001.sql.golden.json
+++ b/parser/testdata/ddl/output/bug_001.sql.golden.json
@@ -571,6 +571,8 @@
       }
     },
     "Populate": false,
-    "Comment": null
+    "Comment": null,
+    "Definer": null,
+    "SQLSecurity": ""
   }
 ]
\ No newline at end of file
diff --git a/parser/testdata/ddl/output/create_materialized_view_basic.sql.golden.json b/parser/testdata/ddl/output/create_materialized_view_basic.sql.golden.json
index 28d211c..a452379 100644
--- a/parser/testdata/ddl/output/create_materialized_view_basic.sql.golden.json
+++ b/parser/testdata/ddl/output/create_materialized_view_basic.sql.golden.json
@@ -542,6 +542,8 @@
       "LiteralPos": 548,
       "LiteralEnd": 565,
       "Literal": "Comment for table"
-    }
+    },
+    "Definer": null,
+    "SQLSecurity": ""
   }
 ]
\ No newline at end of file
diff --git a/parser/testdata/ddl/output/create_materialized_view_with_definer.sql.golden.json b/parser/testdata/ddl/output/create_materialized_view_with_definer.sql.golden.json
new file mode 100644
index 0000000..3ca4ad1
--- /dev/null
+++ b/parser/testdata/ddl/output/create_materialized_view_with_definer.sql.golden.json
@@ -0,0 +1,374 @@
+[
+  {
+    "CreatePos": 0,
+    "StatementEnd": 355,
+    "Name": {
+      "Database": null,
+      "Table": {
+        "Name": "fresh_mv",
+        "QuoteType": 1,
+        "NamePos": 25,
+        "NameEnd": 33
+      }
+    },
+    "IfNotExists": false,
+    "OnCluster": null,
+    "Refresh": {
+      "RefreshPos": 34,
+      "Frequency": "EVERY",
+      "Interval": {
+        "IntervalPos": 0,
+        "Expr": {
+          "NumPos": 48,
+          "NumEnd": 49,
+          "Literal": "1",
+          "Base": 10
+        },
+        "Unit": {
+          "Name": "HOUR",
+          "QuoteType": 1,
+          "NamePos": 50,
+          "NameEnd": 54
+        }
+      },
+      "Offset": {
+        "IntervalPos": 0,
+        "Expr": {
+          "NumPos": 62,
+          "NumEnd": 64,
+          "Literal": "10",
+          "Base": 10
+        },
+        "Unit": {
+          "Name": "MINUTE",
+          "QuoteType": 1,
+          "NamePos": 65,
+          "NameEnd": 71
+        }
+      }
+    },
+    "RandomizeFor": null,
+    "DependsOn": null,
+    "Settings": null,
+    "HasAppend": true,
+    "Engine": null,
+    "HasEmpty": false,
+    "Destination": {
+      "ToPos": 79,
+      "TableIdentifier": {
+        "Database": null,
+        "Table": {
+          "Name": "events_export",
+          "QuoteType": 1,
+          "NamePos": 82,
+          "NameEnd": 95
+        }
+      },
+      "TableSchema": {
+        "SchemaPos": 96,
+        "SchemaEnd": 173,
+        "Columns": [
+          {
+            "NamePos": 103,
+            "ColumnEnd": 126,
+            "Name": {
+              "Ident": {
+                "Name": "timestamp",
+                "QuoteType": 3,
+                "NamePos": 103,
+                "NameEnd": 112
+              },
+              "DotIdent": null
+            },
+            "Type": {
+              "LeftParenPos": 125,
+              "RightParenPos": 126,
+              "Name": {
+                "Name": "DateTime64",
+                "QuoteType": 1,
+                "NamePos": 114,
+                "NameEnd": 124
+              },
+              "Params": [
+                {
+                  "NumPos": 125,
+                  "NumEnd": 126,
+                  "Literal": "9",
+                  "Base": 10
+                }
+              ]
+            },
+            "NotNull": null,
+            "Nullable": null,
+            "DefaultExpr": null,
+            "MaterializedExpr": null,
+            "AliasExpr": null,
+            "Codec": null,
+            "TTL": null,
+            "Comment": null,
+            "CompressionCodec": null
+          },
+          {
+            "NamePos": 134,
+            "ColumnEnd": 149,
+            "Name": {
+              "Ident": {
+                "Name": "field_1",
+                "QuoteType": 3,
+                "NamePos": 134,
+                "NameEnd": 141
+              },
+              "DotIdent": null
+            },
+            "Type": {
+              "Name": {
+                "Name": "String",
+                "QuoteType": 1,
+                "NamePos": 143,
+                "NameEnd": 149
+              }
+            },
+            "NotNull": null,
+            "Nullable": null,
+            "DefaultExpr": null,
+            "MaterializedExpr": null,
+            "AliasExpr": null,
+            "Codec": null,
+            "TTL": null,
+            "Comment": null,
+            "CompressionCodec": null
+          },
+          {
+            "NamePos": 156,
+            "ColumnEnd": 171,
+            "Name": {
+              "Ident": {
+                "Name": "field_2",
+                "QuoteType": 3,
+                "NamePos": 156,
+                "NameEnd": 163
+              },
+              "DotIdent": null
+            },
+            "Type": {
+              "Name": {
+                "Name": "String",
+                "QuoteType": 1,
+                "NamePos": 165,
+                "NameEnd": 171
+              }
+            },
+            "NotNull": null,
+            "Nullable": null,
+            "DefaultExpr": null,
+            "MaterializedExpr": null,
+            "AliasExpr": null,
+            "Codec": null,
+            "TTL": null,
+            "Comment": null,
+            "CompressionCodec": null
+          }
+        ],
+        "AliasTable": null,
+        "TableFunction": null
+      }
+    },
+    "SubQuery": {
+      "HasParen": true,
+      "Select": {
+        "SelectPos": 218,
+        "StatementEnd": 355,
+        "With": null,
+        "Top": null,
+        "SelectItems": [
+          {
+            "Expr": {
+              "Name": "timestamp",
+              "QuoteType": 1,
+              "NamePos": 229,
+              "NameEnd": 238
+            },
+            "Modifiers": [],
+            "Alias": null
+          },
+          {
+            "Expr": {
+              "Name": "field_1",
+              "QuoteType": 1,
+              "NamePos": 244,
+              "NameEnd": 251
+            },
+            "Modifiers": [],
+            "Alias": null
+          },
+          {
+            "Expr": {
+              "Name": "field_2",
+              "QuoteType": 1,
+              "NamePos": 257,
+              "NameEnd": 264
+            },
+            "Modifiers": [],
+            "Alias": null
+          },
+          {
+            "Expr": {
+              "Name": "FROM",
+              "QuoteType": 1,
+              "NamePos": 266,
+              "NameEnd": 270
+            },
+            "Modifiers": [],
+            "Alias": {
+              "Name": "event_table",
+              "QuoteType": 1,
+              "NamePos": 271,
+              "NameEnd": 282
+            }
+          }
+        ],
+        "From": null,
+        "ArrayJoin": null,
+        "Window": null,
+        "Prewhere": null,
+        "Where": {
+          "WherePos": 283,
+          "Expr": {
+            "LeftExpr": {
+              "Name": {
+                "Name": "toStartOfHour",
+                "QuoteType": 1,
+                "NamePos": 289,
+                "NameEnd": 302
+              },
+              "Params": {
+                "LeftParenPos": 302,
+                "RightParenPos": 312,
+                "Items": {
+                  "ListPos": 303,
+                  "ListEnd": 312,
+                  "HasDistinct": false,
+                  "Items": [
+                    {
+                      "Expr": {
+                        "Name": "timestamp",
+                        "QuoteType": 1,
+                        "NamePos": 303,
+                        "NameEnd": 312
+                      },
+                      "Alias": null
+                    }
+                  ]
+                },
+                "ColumnArgList": null
+              }
+            },
+            "Operation": "=",
+            "RightExpr": {
+              "Name": {
+                "Name": "toStartOfHour",
+                "QuoteType": 1,
+                "NamePos": 316,
+                "NameEnd": 329
+              },
+              "Params": {
+                "LeftParenPos": 329,
+                "RightParenPos": 355,
+                "Items": {
+                  "ListPos": 330,
+                  "ListEnd": 354,
+                  "HasDistinct": false,
+                  "Items": [
+                    {
+                      "Expr": {
+                        "LeftExpr": {
+                          "Name": {
+                            "Name": "now",
+                            "QuoteType": 1,
+                            "NamePos": 330,
+                            "NameEnd": 333
+                          },
+                          "Params": {
+                            "LeftParenPos": 333,
+                            "RightParenPos": 334,
+                            "Items": {
+                              "ListPos": 334,
+                              "ListEnd": 334,
+                              "HasDistinct": false,
+                              "Items": []
+                            },
+                            "ColumnArgList": null
+                          }
+                        },
+                        "Operation": "-",
+                        "RightExpr": {
+                          "Name": {
+                            "Name": "toIntervalHour",
+                            "QuoteType": 1,
+                            "NamePos": 338,
+                            "NameEnd": 352
+                          },
+                          "Params": {
+                            "LeftParenPos": 352,
+                            "RightParenPos": 354,
+                            "Items": {
+                              "ListPos": 353,
+                              "ListEnd": 354,
+                              "HasDistinct": false,
+                              "Items": [
+                                {
+                                  "Expr": {
+                                    "NumPos": 353,
+                                    "NumEnd": 354,
+                                    "Literal": "1",
+                                    "Base": 10
+                                  },
+                                  "Alias": null
+                                }
+                              ]
+                            },
+                            "ColumnArgList": null
+                          }
+                        },
+                        "HasGlobal": false,
+                        "HasNot": false
+                      },
+                      "Alias": null
+                    }
+                  ]
+                },
+                "ColumnArgList": null
+              }
+            },
+            "HasGlobal": false,
+            "HasNot": false
+          }
+        },
+        "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": {
+      "LiteralPos": 367,
+      "LiteralEnd": 379,
+      "Literal": "Test comment"
+    },
+    "Definer": {
+      "Name": "default",
+      "QuoteType": 1,
+      "NamePos": 185,
+      "NameEnd": 192
+    },
+    "SQLSecurity": "DEFINER"
+  }
+]
\ No newline at end of file
diff --git a/parser/testdata/ddl/output/create_materialized_view_with_empty_table_schema.sql.golden.json b/parser/testdata/ddl/output/create_materialized_view_with_empty_table_schema.sql.golden.json
index 6a0c4f4..f2439a2 100644
--- a/parser/testdata/ddl/output/create_materialized_view_with_empty_table_schema.sql.golden.json
+++ b/parser/testdata/ddl/output/create_materialized_view_with_empty_table_schema.sql.golden.json
@@ -548,6 +548,8 @@
       }
     },
     "Populate": true,
-    "Comment": null
+    "Comment": null,
+    "Definer": null,
+    "SQLSecurity": ""
   }
 ]
\ No newline at end of file
diff --git a/parser/testdata/ddl/output/create_materialized_view_with_refresh.sql.golden.json b/parser/testdata/ddl/output/create_materialized_view_with_refresh.sql.golden.json
index 4e17a5a..0755290 100644
--- a/parser/testdata/ddl/output/create_materialized_view_with_refresh.sql.golden.json
+++ b/parser/testdata/ddl/output/create_materialized_view_with_refresh.sql.golden.json
@@ -213,6 +213,8 @@
       }
     },
     "Populate": false,
-    "Comment": null
+    "Comment": null,
+    "Definer": null,
+    "SQLSecurity": ""
   }
 ]
\ No newline at end of file
diff --git a/parser/testdata/ddl/output/create_mv_with_not_op.sql.golden.json b/parser/testdata/ddl/output/create_mv_with_not_op.sql.golden.json
index 530c564..e8534bc 100644
--- a/parser/testdata/ddl/output/create_mv_with_not_op.sql.golden.json
+++ b/parser/testdata/ddl/output/create_mv_with_not_op.sql.golden.json
@@ -609,6 +609,8 @@
       }
     },
     "Populate": false,
-    "Comment": null
+    "Comment": null,
+    "Definer": null,
+    "SQLSecurity": ""
   }
 ]
\ No newline at end of file
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
index 0119301..49b6526 100644
--- 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
@@ -156,7 +156,9 @@
       }
     },
     "Populate": false,
-    "Comment": null
+    "Comment": null,
+    "Definer": null,
+    "SQLSecurity": ""
   },
   {
     "CreatePos": 138,
@@ -284,6 +286,8 @@
       }
     },
     "Populate": false,
-    "Comment": null
+    "Comment": null,
+    "Definer": null,
+    "SQLSecurity": ""
   }
 ]
\ No newline at end of file