Repository: tobymao/sqlglot

Issue:
MySQL - Alter Table ... Add Index. Parsing as Command
**Fully reproducible code snippet**
```
ddl = """
ALTER TABLE
    `posts` ADD INDEX `posts_integer_column__index_key_index`(`integer_column__index_key`);
"""
ast = parse_one(ddl, dialect="mysql")
```

**Official Documentation**
https://dev.mysql.com/doc/refman/8.4/en/alter-table.html

Found this one today, not sure if I'm missing something. It works if we remove the index name.
Probably has another related one if it is indeed a bug:
```
ALTER TABLE
    `posts` ADD KEY `posts_integer_column__index_key_index`(`integer_column__index_key`);
```

Thanks

Discussion:

Patch:
diff --git a/sqlglot/dialects/mysql.py b/sqlglot/dialects/mysql.py
index 26c7ad2d..67a32709 100644
--- a/sqlglot/dialects/mysql.py
+++ b/sqlglot/dialects/mysql.py
@@ -202,6 +202,7 @@ class MySQL(Dialect):
             "CHARSET": TokenType.CHARACTER_SET,
             "FORCE": TokenType.FORCE,
             "IGNORE": TokenType.IGNORE,
+            "KEY": TokenType.KEY,
             "LOCK TABLES": TokenType.COMMAND,
             "LONGBLOB": TokenType.LONGBLOB,
             "LONGTEXT": TokenType.LONGTEXT,
diff --git a/sqlglot/dialects/snowflake.py b/sqlglot/dialects/snowflake.py
index 8c3c471c..c8ac0db1 100644
--- a/sqlglot/dialects/snowflake.py
+++ b/sqlglot/dialects/snowflake.py
@@ -510,15 +510,18 @@ class Snowflake(Dialect):
                 self._retreat(self._index - 1)
 
             if self._match_text_seq("MASKING", "POLICY"):
+                policy = self._parse_column()
                 return self.expression(
                     exp.MaskingPolicyColumnConstraint,
-                    this=self._parse_id_var(),
+                    this=policy.to_dot() if isinstance(policy, exp.Column) else policy,
                     expressions=self._match(TokenType.USING)
                     and self._parse_wrapped_csv(self._parse_id_var),
                 )
             if self._match_text_seq("PROJECTION", "POLICY"):
+                policy = self._parse_column()
                 return self.expression(
-                    exp.ProjectionPolicyColumnConstraint, this=self._parse_id_var()
+                    exp.ProjectionPolicyColumnConstraint,
+                    this=policy.to_dot() if isinstance(policy, exp.Column) else policy,
                 )
             if self._match(TokenType.TAG):
                 return self.expression(
diff --git a/sqlglot/parser.py b/sqlglot/parser.py
index c6802605..b99d34c0 100644
--- a/sqlglot/parser.py
+++ b/sqlglot/parser.py
@@ -1138,7 +1138,14 @@ class Parser(metaclass=_Parser):
 
     FETCH_TOKENS = ID_VAR_TOKENS - {TokenType.ROW, TokenType.ROWS, TokenType.PERCENT}
 
-    ADD_CONSTRAINT_TOKENS = {TokenType.CONSTRAINT, TokenType.PRIMARY_KEY, TokenType.FOREIGN_KEY}
+    ADD_CONSTRAINT_TOKENS = {
+        TokenType.CONSTRAINT,
+        TokenType.FOREIGN_KEY,
+        TokenType.INDEX,
+        TokenType.KEY,
+        TokenType.PRIMARY_KEY,
+        TokenType.UNIQUE,
+    }
 
     DISTINCT_TOKENS = {TokenType.DISTINCT}
 
@@ -3242,7 +3249,7 @@ class Parser(metaclass=_Parser):
             while self._match_set(self.TABLE_INDEX_HINT_TOKENS):
                 hint = exp.IndexTableHint(this=self._prev.text.upper())
 
-                self._match_texts(("INDEX", "KEY"))
+                self._match_set((TokenType.INDEX, TokenType.KEY))
                 if self._match(TokenType.FOR):
                     hint.set("target", self._advance_any() and self._prev.text.upper())
 
diff --git a/sqlglot/tokens.py b/sqlglot/tokens.py
index 5b2f2415..cd582f46 100644
--- a/sqlglot/tokens.py
+++ b/sqlglot/tokens.py
@@ -287,6 +287,7 @@ class TokenType(AutoName):
     JOIN = auto()
     JOIN_MARKER = auto()
     KEEP = auto()
+    KEY = auto()
     KILL = auto()
     LANGUAGE = auto()
     LATERAL = auto()


Test patch:
diff --git a/tests/dialects/test_mysql.py b/tests/dialects/test_mysql.py
index fdb7e919..e19a072e 100644
--- a/tests/dialects/test_mysql.py
+++ b/tests/dialects/test_mysql.py
@@ -21,6 +21,9 @@ class TestMySQL(Validator):
         self.validate_identity("CREATE TABLE foo (a BIGINT, FULLTEXT INDEX (b))")
         self.validate_identity("CREATE TABLE foo (a BIGINT, SPATIAL INDEX (b))")
         self.validate_identity("ALTER TABLE t1 ADD COLUMN x INT, ALGORITHM=INPLACE, LOCK=EXCLUSIVE")
+        self.validate_identity("ALTER TABLE t ADD INDEX `i` (`c`)")
+        self.validate_identity("ALTER TABLE t ADD UNIQUE `i` (`c`)")
+        self.validate_identity("ALTER TABLE test_table MODIFY COLUMN test_column LONGTEXT")
         self.validate_identity(
             "CREATE TABLE `oauth_consumer` (`key` VARCHAR(32) NOT NULL, UNIQUE `OAUTH_CONSUMER_KEY` (`key`))"
         )
@@ -60,6 +63,10 @@ class TestMySQL(Validator):
         self.validate_identity(
             "CREATE OR REPLACE VIEW my_view AS SELECT column1 AS `boo`, column2 AS `foo` FROM my_table WHERE column3 = 'some_value' UNION SELECT q.* FROM fruits_table, JSON_TABLE(Fruits, '$[*]' COLUMNS(id VARCHAR(255) PATH '$.$id', value VARCHAR(255) PATH '$.value')) AS q",
         )
+        self.validate_identity(
+            "ALTER TABLE t ADD KEY `i` (`c`)",
+            "ALTER TABLE t ADD INDEX `i` (`c`)",
+        )
         self.validate_identity(
             "CREATE TABLE `foo` (`id` char(36) NOT NULL DEFAULT (uuid()), PRIMARY KEY (`id`), UNIQUE KEY `id` (`id`))",
             "CREATE TABLE `foo` (`id` CHAR(36) NOT NULL DEFAULT (UUID()), PRIMARY KEY (`id`), UNIQUE `id` (`id`))",
@@ -76,9 +83,6 @@ class TestMySQL(Validator):
             "ALTER TABLE test_table ALTER COLUMN test_column SET DATA TYPE LONGTEXT",
             "ALTER TABLE test_table MODIFY COLUMN test_column LONGTEXT",
         )
-        self.validate_identity(
-            "ALTER TABLE test_table MODIFY COLUMN test_column LONGTEXT",
-        )
         self.validate_identity(
             "CREATE TABLE t (c DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP) DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC",
             "CREATE TABLE t (c DATETIME DEFAULT CURRENT_TIMESTAMP() ON UPDATE CURRENT_TIMESTAMP()) DEFAULT CHARACTER SET=utf8 ROW_FORMAT=DYNAMIC",
diff --git a/tests/dialects/test_snowflake.py b/tests/dialects/test_snowflake.py
index 9d9371db..e5bf4f27 100644
--- a/tests/dialects/test_snowflake.py
+++ b/tests/dialects/test_snowflake.py
@@ -1196,16 +1196,16 @@ WHERE
         for constraint_prefix in ("WITH ", ""):
             with self.subTest(f"Constraint prefix: {constraint_prefix}"):
                 self.validate_identity(
-                    f"CREATE TABLE t (id INT {constraint_prefix}MASKING POLICY p)",
-                    "CREATE TABLE t (id INT MASKING POLICY p)",
+                    f"CREATE TABLE t (id INT {constraint_prefix}MASKING POLICY p.q.r)",
+                    "CREATE TABLE t (id INT MASKING POLICY p.q.r)",
                 )
                 self.validate_identity(
                     f"CREATE TABLE t (id INT {constraint_prefix}MASKING POLICY p USING (c1, c2, c3))",
                     "CREATE TABLE t (id INT MASKING POLICY p USING (c1, c2, c3))",
                 )
                 self.validate_identity(
-                    f"CREATE TABLE t (id INT {constraint_prefix}PROJECTION POLICY p)",
-                    "CREATE TABLE t (id INT PROJECTION POLICY p)",
+                    f"CREATE TABLE t (id INT {constraint_prefix}PROJECTION POLICY p.q.r)",
+                    "CREATE TABLE t (id INT PROJECTION POLICY p.q.r)",
                 )
                 self.validate_identity(
                     f"CREATE TABLE t (id INT {constraint_prefix}TAG (key1='value_1', key2='value_2'))",