Repository: tobymao/sqlglot

Issue:
ASC/DESC index not supported "ParseError: Expecting )."
**Fully reproducible code snippet**
```python
from sqlglot import parse

parse("CREATE TABLE `foo` (a VARCHAR(10), KEY idx_a (a DESC))", dialect="mysql")
```

**Official Documentation**
- https://dev.mysql.com/doc/refman/8.0/en/create-table.html
   MySQL supports `ASC` or `DESC` qualifiers for an index
- https://www.postgresql.org/docs/current/indexes-ordering.html
   Postgres supports `ASC` or `DESC` qualifiers for an index, and additionally `NULLS FIRST` and `NULLS LAST`

I had a look at how to add this just for MySQL initially, but ...it's complicated.

The MySQL dialect has this:
```python
        def _parse_index_constraint(
            self, kind: t.Optional[str] = None
        ) -> exp.IndexColumnConstraint:
            if kind:
                self._match_texts(("INDEX", "KEY"))

            this = self._parse_id_var(any_token=False)
            index_type = self._match(TokenType.USING) and self._advance_any() and self._prev.text
            schema = self._parse_schema()
```

and `_parse_schema` has:
```python

    def _parse_schema(self, this: t.Optional[exp.Expression] = None) -> t.Optional[exp.Expression]:
        index = self._index

        if not self._match(TokenType.L_PAREN):
            return this

        # Disambiguate between schema and subquery/CTE, e.g. in INSERT INTO table (<expr>),
        # expr can be of both types
        if self._match_set(self.SELECT_START_TOKENS):
            self._retreat(index)
            return this

        args = self._parse_csv(lambda: self._parse_constraint() or self._parse_field_def())

        self._match_r_paren()
        return self.expression(exp.Schema, this=this, expressions=args)
```

The problem seems to be in the `_parse_csv` part

I believe the idea is to parse a list of column names for composite index, i.e. `(a, b, c)` but it errors if it's something like `(a, b DESC, c)`... it expects either a `,` or `)` after the `b` token.

When parsing an example without the direction qualifier:
```python
parse("CREATE TABLE `foo` (a VARCHAR(10), b VARCHAR(10), KEY idx_ab (a, b))", dialect="mysql")
```
we get:
```
[Create(
   this=Schema(
     this=Table(
       this=Identifier(this=foo, quoted=True)),
     expressions=[
       ColumnDef(
         this=Identifier(this=a, quoted=False),
         kind=DataType(
           this=Type.VARCHAR,
           expressions=[
             DataTypeParam(
               this=Literal(this=10, is_string=False))],
           nested=False)),
       ColumnDef(
         this=Identifier(this=b, quoted=False),
         kind=DataType(
           this=Type.VARCHAR,
           expressions=[
             DataTypeParam(
               this=Literal(this=10, is_string=False))],
           nested=False)),
       IndexColumnConstraint(
         this=Identifier(this=idx_ab, quoted=False),
         schema=Schema(
           expressions=[
             Identifier(this=a, quoted=False),
             Identifier(this=b, quoted=False)]))]),
   kind=TABLE)]
```

`Schema` to me in sql context would be referring to the hierarchy level above a table... is it used with same meaning here? (I'm not sure how it relates)

If I replace the call to `_parse_schema` with `self._parse_csv(self._parse_ordered)` then I get a successful parse (apparently with bonus support for `NULLS FIRST` also)

```
[Create(
   this=Schema(
     this=Table(
       this=Identifier(this=foo, quoted=True)),
     expressions=[
       ColumnDef(
         this=Identifier(this=a, quoted=False),
         kind=DataType(
           this=Type.VARCHAR,
           expressions=[
             DataTypeParam(
               this=Literal(this=10, is_string=False))],
           nested=False)),
       IndexColumnConstraint(
         this=Identifier(this=idx_a, quoted=False),
         schema=[
           Ordered(
             this=Paren(
               this=Alias(
                 this=Column(
                   this=Identifier(this=a, quoted=False)),
                 alias=Identifier(this=DESC, quoted=False))),
             nulls_first=True)])]),
   kind=TABLE)]
```

but there are new problems downstream when going the other direction from AST -> SQL (`ValueError: Expected an Expression. Received <class 'list'>`)

probably something can be done in `Generator.indexcolumnconstraint_sql`...

I tried `self.expressions(expression, key="schema", sep=" ")` which gives `'(a AS DESC)'``

I guess the problem is here... we don't want any aliases here:
```
Alias(
                 this=Column(
                   this=Identifier(this=a, quoted=False)),
                 alias=Identifier(this=DESC, quoted=False))),
             nulls_first=True)])]),
```
...not sure yet where that comes from right now 🤔 

Discussion:

Patch:
diff --git a/sqlglot/dialects/mysql.py b/sqlglot/dialects/mysql.py
index d9dc2dab..1d53346e 100644
--- a/sqlglot/dialects/mysql.py
+++ b/sqlglot/dialects/mysql.py
@@ -461,7 +461,7 @@ class MySQL(Dialect):
 
             this = self._parse_id_var(any_token=False)
             index_type = self._match(TokenType.USING) and self._advance_any() and self._prev.text
-            schema = self._parse_schema()
+            expressions = self._parse_wrapped_csv(self._parse_ordered)
 
             options = []
             while True:
@@ -495,7 +495,7 @@ class MySQL(Dialect):
             return self.expression(
                 exp.IndexColumnConstraint,
                 this=this,
-                schema=schema,
+                expressions=expressions,
                 kind=kind,
                 index_type=index_type,
                 options=options,
diff --git a/sqlglot/expressions.py b/sqlglot/expressions.py
index fc7de00f..e79c04bd 100644
--- a/sqlglot/expressions.py
+++ b/sqlglot/expressions.py
@@ -1669,7 +1669,7 @@ class GeneratedAsRowColumnConstraint(ColumnConstraintKind):
 class IndexColumnConstraint(ColumnConstraintKind):
     arg_types = {
         "this": False,
-        "schema": False,
+        "expressions": False,
         "kind": False,
         "index_type": False,
         "options": False,
diff --git a/sqlglot/generator.py b/sqlglot/generator.py
index 0408afab..76d9b5d6 100644
--- a/sqlglot/generator.py
+++ b/sqlglot/generator.py
@@ -3421,11 +3421,11 @@ class Generator(metaclass=_Generator):
         this = f" {this}" if this else ""
         index_type = self.sql(expression, "index_type")
         index_type = f" USING {index_type}" if index_type else ""
-        schema = self.sql(expression, "schema")
-        schema = f" {schema}" if schema else ""
+        expressions = self.expressions(expression, flat=True)
+        expressions = f" ({expressions})" if expressions else ""
         options = self.expressions(expression, key="options", sep=" ")
         options = f" {options}" if options else ""
-        return f"{kind}{this}{index_type}{schema}{options}"
+        return f"{kind}{this}{index_type}{expressions}{options}"
 
     def nvl2_sql(self, expression: exp.Nvl2) -> str:
         if self.NVL2_SUPPORTED:


Test patch:
diff --git a/tests/dialects/test_mysql.py b/tests/dialects/test_mysql.py
index 49552bf5..7a9d6bf5 100644
--- a/tests/dialects/test_mysql.py
+++ b/tests/dialects/test_mysql.py
@@ -92,6 +92,10 @@ class TestMySQL(Validator):
             "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",
         )
+        self.validate_identity(
+            "CREATE TABLE `foo` (a VARCHAR(10), KEY idx_a (a DESC))",
+            "CREATE TABLE `foo` (a VARCHAR(10), INDEX idx_a (a DESC))",
+        )
 
         self.validate_all(
             "CREATE TABLE z (a INT) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARACTER SET=utf8 COLLATE=utf8_bin COMMENT='x'",