From 2d5e206bf99c28e36984eddd413a04cdb358c900 Mon Sep 17 00:00:00 2001
From: Mike Bayer <mike_mp@zzzcomputing.com>
Date: Thu, 28 Jun 2018 14:33:14 -0400
Subject: [PATCH] Reflect ASC/DESC in MySQL index columns

Fixed bug in index reflection where on MySQL 8.0 an index that includes
ASC or DESC in an indexed column specfication would not be correctly
reflected, as MySQL 8.0 introduces support for returning this information
in a table definition string.

Change-Id: I21f64984ade690aac8c87dbe3aad0c1ee8e9727f
Fixes: #4293
(cherry picked from commit 9d2dc7911b7767b97814479d228072b6f566a864)
---
 doc/build/changelog/unreleased_12/4293.rst  |  8 +++
 lib/sqlalchemy/dialects/mysql/reflection.py |  5 +-
 test/dialect/mysql/test_reflection.py       | 65 ++++++++++++++++++++-
 3 files changed, 75 insertions(+), 3 deletions(-)
 create mode 100644 doc/build/changelog/unreleased_12/4293.rst

diff --git a/doc/build/changelog/unreleased_12/4293.rst b/doc/build/changelog/unreleased_12/4293.rst
new file mode 100644
index 0000000000..51fac2033b
--- /dev/null
+++ b/doc/build/changelog/unreleased_12/4293.rst
@@ -0,0 +1,8 @@
+.. change::
+    :tags: bug, mysql
+    :tickets: 4293
+
+    Fixed bug in index reflection where on MySQL 8.0 an index that includes
+    ASC or DESC in an indexed column specfication would not be correctly
+    reflected, as MySQL 8.0 introduces support for returning this information
+    in a table definition string.
diff --git a/lib/sqlalchemy/dialects/mysql/reflection.py b/lib/sqlalchemy/dialects/mysql/reflection.py
index f9b8c8d3b3..3e7affef28 100644
--- a/lib/sqlalchemy/dialects/mysql/reflection.py
+++ b/lib/sqlalchemy/dialects/mysql/reflection.py
@@ -76,6 +76,8 @@ class MySQLTableDefinitionParser(object):
         if m:
             spec = m.groupdict()
             # convert columns into name, length pairs
+            # NOTE: we may want to consider SHOW INDEX as the
+            # format of indexes in MySQL becomes more complex
             spec['columns'] = self._parse_keyexprs(spec['columns'])
             return 'key', spec
 
@@ -310,11 +312,10 @@ class MySQLTableDefinitionParser(object):
 
         # `col`,`col2`(32),`col3`(15) DESC
         #
-        # Note: ASC and DESC aren't reflected, so we'll punt...
         self._re_keyexprs = _re_compile(
             r'(?:'
             r'(?:%(iq)s((?:%(esc_fq)s|[^%(fq)s])+)%(fq)s)'
-            r'(?:\((\d+)\))?(?=\,|$))+' % quotes)
+            r'(?:\((\d+)\))?(?: +(ASC|DESC))?(?=\,|$))+' % quotes)
 
         # 'foo' or 'foo','bar' or 'fo,o','ba''a''r'
         self._re_csv_str = _re_compile(r'\x27(?:\x27\x27|[^\x27])*\x27')
diff --git a/test/dialect/mysql/test_reflection.py b/test/dialect/mysql/test_reflection.py
index 86db65db89..6b126ca0a1 100644
--- a/test/dialect/mysql/test_reflection.py
+++ b/test/dialect/mysql/test_reflection.py
@@ -4,7 +4,7 @@ from sqlalchemy.testing import eq_, is_
 from sqlalchemy import Column, Table, DDL, MetaData, TIMESTAMP, \
     DefaultClause, String, Integer, Text, UnicodeText, SmallInteger,\
     NCHAR, LargeBinary, DateTime, select, UniqueConstraint, Unicode,\
-    BigInteger
+    BigInteger, Index
 from sqlalchemy import event
 from sqlalchemy import sql
 from sqlalchemy import exc
@@ -582,6 +582,20 @@ class ReflectionTest(fixtures.TestBase, AssertsExecutionResults):
         self.assert_(indexes['uc_a'].unique)
         self.assert_('uc_a' not in constraints)
 
+    @testing.provide_metadata
+    def test_non_column_index(self):
+        m1 = self.metadata
+        t1 = Table(
+            'add_ix', m1, Column('x', String(50)), mysql_engine='InnoDB')
+        Index('foo_idx', t1.c.x.desc())
+        m1.create_all()
+
+        insp = inspect(testing.db)
+        eq_(
+            insp.get_indexes("add_ix"),
+            [{'name': 'foo_idx', 'column_names': ['x'], 'unique': False}]
+        )
+
 
 class RawReflectionTest(fixtures.TestBase):
     __backend__ = True
@@ -621,6 +635,55 @@ class RawReflectionTest(fixtures.TestBase):
         assert regex.match(
             "  KEY (`id`) USING BTREE COMMENT 'prefix''text''suffix'")
 
+    def test_key_reflection_columns(self):
+        regex = self.parser._re_key
+        exprs = self.parser._re_keyexprs
+        m = regex.match(
+            "  KEY (`id`) USING BTREE COMMENT '''comment'")
+        eq_(m.group("columns"), '`id`')
+
+        m = regex.match(
+            "  KEY (`x`, `y`) USING BTREE")
+        eq_(m.group("columns"), '`x`, `y`')
+
+        eq_(
+            exprs.findall(m.group("columns")),
+            [("x", "", ""), ("y", "", "")]
+        )
+
+        m = regex.match(
+            "  KEY (`x`(25), `y`(15)) USING BTREE")
+        eq_(m.group("columns"), '`x`(25), `y`(15)')
+        eq_(
+            exprs.findall(m.group("columns")),
+            [("x", "25", ""), ("y", "15", "")]
+        )
+
+        m = regex.match(
+            "  KEY (`x`(25) DESC, `y`(15) ASC) USING BTREE")
+        eq_(m.group("columns"), '`x`(25) DESC, `y`(15) ASC')
+        eq_(
+            exprs.findall(m.group("columns")),
+            [("x", "25", "DESC"), ("y", "15", "ASC")]
+        )
+
+        m = regex.match(
+            "  KEY `foo_idx` (`x` DESC)")
+        eq_(m.group("columns"), '`x` DESC')
+        eq_(
+            exprs.findall(m.group("columns")),
+            [("x", "", "DESC")]
+        )
+
+        eq_(
+            exprs.findall(m.group("columns")),
+            [("x", "", "DESC")]
+        )
+
+        m = regex.match(
+            "  KEY `foo_idx` (`x` DESC, `y` ASC)")
+        eq_(m.group("columns"), '`x` DESC, `y` ASC')
+
     def test_fk_reflection(self):
         regex = self.parser._re_fk_constraint
 
-- 
2.47.2