Repository: tobymao/sqlglot

Issue:
Support MySQL functional key parts indexes
MySQL supports Functional Key Parts indexes with which an index can be created only for the first part of a varchar column.
Such indexes are now incorrectly parsed by the library.

**Fully reproducible code snippet**
With functional key parts index:

```python
sql = """
CREATE TABLE `x` (
  `username` varchar(200),
  PRIMARY KEY (`username`(16))
);
"""

parsed_sql_exprs = sqlglot.parse(sql, read="mysql")
print(parsed_sql_exprs)

# => 
# [(CREATE this: 
#   (SCHEMA this: 
#     (TABLE this: 
#       (IDENTIFIER this: x, quoted: True)), expressions: 
#     (COLUMNDEF this: 
#       (IDENTIFIER this: username, quoted: True), kind: 
#       (DATATYPE this: Type.VARCHAR, expressions: 
#         (DATATYPEPARAM this: 
#           (LITERAL this: 200, is_string: False)), nested: False, prefix: False)), 
#     (PRIMARYKEY expressions: 
#       (ANONYMOUS this: username, expressions: 
#         (LITERAL this: 16, is_string: False)))), kind: TABLE, exists: False)]
```

Without functional key parts index:
```python
sql = """
CREATE TABLE `x` (
  `username` varchar(200),
  PRIMARY KEY (`username`)
);
"""

parsed_sql_exprs = sqlglot.parse(sql, read="mysql")
print(parsed_sql_exprs)

# => 
# [(CREATE this: 
#   (SCHEMA this: 
#     (TABLE this: 
#       (IDENTIFIER this: x, quoted: True)), expressions: 
#     (COLUMNDEF this: 
#       (IDENTIFIER this: username, quoted: True), kind: 
#       (DATATYPE this: Type.VARCHAR, expressions: 
#         (DATATYPEPARAM this: 
#           (LITERAL this: 200, is_string: False)), nested: False, prefix: False)), 
#     (PRIMARYKEY expressions: 
#       (IDENTIFIER this: username, quoted: True))), kind: TABLE, exists: False)]
```

**Official Documentation**
https://dev.mysql.com/doc/refman/8.0/en/create-index.html


Discussion:

Patch:
diff --git a/sqlglot/dialects/mysql.py b/sqlglot/dialects/mysql.py
index 6a99b0af..190a3769 100644
--- a/sqlglot/dialects/mysql.py
+++ b/sqlglot/dialects/mysql.py
@@ -358,6 +358,15 @@ class MySQL(Dialect):
 
         LOG_DEFAULTS_TO_LN = True
 
+        def _parse_primary_key_part(self) -> t.Optional[exp.Expression]:
+            this = self._parse_id_var()
+            if not self._match(TokenType.L_PAREN):
+                return this
+
+            expression = self._parse_number()
+            self._match_r_paren()
+            return self.expression(exp.ColumnPrefix, this=this, expression=expression)
+
         def _parse_index_constraint(
             self, kind: t.Optional[str] = None
         ) -> exp.IndexColumnConstraint:
diff --git a/sqlglot/expressions.py b/sqlglot/expressions.py
index 6975212d..98afddc8 100644
--- a/sqlglot/expressions.py
+++ b/sqlglot/expressions.py
@@ -20,6 +20,7 @@ import typing as t
 from collections import deque
 from copy import deepcopy
 from enum import auto
+from functools import reduce
 
 from sqlglot._typing import E
 from sqlglot.errors import ParseError
@@ -1170,7 +1171,7 @@ class Column(Condition):
                 parts.append(parent.expression)
             parent = parent.parent
 
-        return Dot.build(parts)
+        return Dot.build(deepcopy(parts))
 
 
 class ColumnPosition(Expression):
@@ -1537,6 +1538,10 @@ class ForeignKey(Expression):
     }
 
 
+class ColumnPrefix(Expression):
+    arg_types = {"this": True, "expression": True}
+
+
 class PrimaryKey(Expression):
     arg_types = {"expressions": True, "options": False}
 
@@ -3795,13 +3800,7 @@ class Dot(Binary):
         if len(expressions) < 2:
             raise ValueError(f"Dot requires >= 2 expressions.")
 
-        a, b, *expressions = expressions
-        dot = Dot(this=a, expression=b)
-
-        for expression in expressions:
-            dot = Dot(this=dot, expression=expression)
-
-        return dot
+        return t.cast(Dot, reduce(lambda x, y: Dot(this=x, expression=y), expressions))
 
 
 class DPipe(Binary):
diff --git a/sqlglot/generator.py b/sqlglot/generator.py
index 9500fc52..399b48bc 100644
--- a/sqlglot/generator.py
+++ b/sqlglot/generator.py
@@ -2771,6 +2771,9 @@ class Generator:
         condition = f" IF {condition}" if condition else ""
         return f"{this} FOR {expr} IN {iterator}{condition}"
 
+    def columnprefix_sql(self, expression: exp.ColumnPrefix) -> str:
+        return f"{self.sql(expression, 'this')}({self.sql(expression, 'expression')})"
+
 
 def cached_generator(
     cache: t.Optional[t.Dict[int, str]] = None
diff --git a/sqlglot/parser.py b/sqlglot/parser.py
index cca425f1..54a56b97 100644
--- a/sqlglot/parser.py
+++ b/sqlglot/parser.py
@@ -3890,6 +3890,9 @@ class Parser(metaclass=_Parser):
             exp.ForeignKey, expressions=expressions, reference=reference, **options  # type: ignore
         )
 
+    def _parse_primary_key_part(self) -> t.Optional[exp.Expression]:
+        return self._parse_field()
+
     def _parse_primary_key(
         self, wrapped_optional: bool = False, in_props: bool = False
     ) -> exp.PrimaryKeyColumnConstraint | exp.PrimaryKey:
@@ -3901,7 +3904,9 @@ class Parser(metaclass=_Parser):
         if not in_props and not self._match(TokenType.L_PAREN, advance=False):
             return self.expression(exp.PrimaryKeyColumnConstraint, desc=desc)
 
-        expressions = self._parse_wrapped_csv(self._parse_field, optional=wrapped_optional)
+        expressions = self._parse_wrapped_csv(
+            self._parse_primary_key_part, optional=wrapped_optional
+        )
         options = self._parse_key_constraint_options()
         return self.expression(exp.PrimaryKey, expressions=expressions, options=options)
 


Test patch:
diff --git a/tests/dialects/test_mysql.py b/tests/dialects/test_mysql.py
index 61bc095c..c4cb4b75 100644
--- a/tests/dialects/test_mysql.py
+++ b/tests/dialects/test_mysql.py
@@ -26,6 +26,9 @@ class TestMySQL(Validator):
         self.validate_identity("CREATE TABLE foo (a BIGINT, INDEX USING BTREE (b))")
         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(
+            "CREATE TABLE `x` (`username` VARCHAR(200), PRIMARY KEY (`username`(16)))"
+        )
         self.validate_identity(
             "UPDATE items SET items.price = 0 WHERE items.id >= 5 ORDER BY items.id LIMIT 10"
         )