Consider mysql partition options separately from other table options

Move down all the PARTITION, SUBPARTITION options
into a separate segment so that they come out at the
end of CREATE TABLE after the table options.

Change-Id: Iaa1c823848c93680ca22d72bda1b7c49742b9060
Fixes: #3961

3 files changed, 76 insertions, 5 deletions

diff --git a/doc/build/changelog/changelog_11.rst b/doc/build/changelog/changelog_11.rst
index 20223ca1a..423a7342a 100644
--- a/doc/build/changelog/changelog_11.rst
+++ b/doc/build/changelog/changelog_11.rst
@@ -21,6 +21,16 @@
 .. changelog::
     :version: 1.1.10
 
+    .. change:: 3961
+        :tags: bug, mysql
+        :tickets: 3961
+
+        Fixed bug in MySQL dialect regarding rendering of table options in
+        conjunction with PARTITION options when rendering CREATE TABLE.
+        The PARTITION related options need to follow the table options,
+        whereas previously this ordering was not enforced.
+
+
 .. changelog::
     :version: 1.1.9
     :released: April 4, 2017
diff --git a/lib/sqlalchemy/dialects/mysql/base.py b/lib/sqlalchemy/dialects/mysql/base.py
index da59ba6fa..c6c1eb8bb 100644
--- a/lib/sqlalchemy/dialects/mysql/base.py
+++ b/lib/sqlalchemy/dialects/mysql/base.py
@@ -1032,11 +1032,18 @@ class MySQLDDLCompiler(compiler.DDLCompiler):
         if table.comment is not None:
             opts['COMMENT'] = table.comment
 
+        partition_options = [
+            'PARTITION_BY', 'PARTITIONS', 'SUBPARTITIONS',
+            'SUBPARTITION_BY'
+        ]
+
+        nonpart_options = set(opts).difference(partition_options)
+        part_options = set(opts).intersection(partition_options)
+
         for opt in topological.sort([
             ('DEFAULT_CHARSET', 'COLLATE'),
             ('DEFAULT_CHARACTER_SET', 'COLLATE'),
-            ('PARTITION_BY', 'PARTITIONS'),  # only for test consistency
-        ], opts):
+        ], nonpart_options):
             arg = opts[opt]
             if opt in _reflection._options_of_type_string:
                 arg = "'%s'" % arg.replace("\\", "\\\\").replace("'", "''")
@@ -1044,16 +1051,33 @@ class MySQLDDLCompiler(compiler.DDLCompiler):
             if opt in ('DATA_DIRECTORY', 'INDEX_DIRECTORY',
                        'DEFAULT_CHARACTER_SET', 'CHARACTER_SET',
                        'DEFAULT_CHARSET',
-                       'DEFAULT_COLLATE', 'PARTITION_BY'):
+                       'DEFAULT_COLLATE'):
                 opt = opt.replace('_', ' ')
 
             joiner = '='
             if opt in ('TABLESPACE', 'DEFAULT CHARACTER SET',
-                       'CHARACTER SET', 'COLLATE',
-                       'PARTITION BY', 'PARTITIONS'):
+                       'CHARACTER SET', 'COLLATE'):
                 joiner = ' '
 
             table_opts.append(joiner.join((opt, arg)))
+
+        for opt in topological.sort([
+            ('PARTITION_BY', 'PARTITIONS'),
+            ('PARTITION_BY', 'SUBPARTITION_BY'),
+            ('PARTITION_BY', 'SUBPARTITIONS'),
+            ('PARTITIONS', 'SUBPARTITIONS'),
+            ('PARTITIONS', 'SUBPARTITION_BY'),
+            ('SUBPARTITION_BY', 'SUBPARTITIONS')
+        ], part_options):
+            arg = opts[opt]
+            if opt in _reflection._options_of_type_string:
+                arg = "'%s'" % arg.replace("\\", "\\\\").replace("'", "''")
+
+            opt = opt.replace('_', ' ')
+            joiner = ' '
+
+            table_opts.append(joiner.join((opt, arg)))
+
         return ' '.join(table_opts)
 
     def visit_create_index(self, create):
diff --git a/test/dialect/mysql/test_compiler.py b/test/dialect/mysql/test_compiler.py
index 35ff603a8..be5f002e3 100644
--- a/test/dialect/mysql/test_compiler.py
+++ b/test/dialect/mysql/test_compiler.py
@@ -607,6 +607,26 @@ class SQLTest(fixtures.TestBase, AssertsCompiledSQL):
             ')PARTITION BY KEY(other_id) PARTITIONS 2'
         )
 
+    def test_create_table_with_subpartition(self):
+        t1 = Table(
+            'testtable', MetaData(),
+            Column('id', Integer(), primary_key=True, autoincrement=True),
+            Column('other_id', Integer(), primary_key=True,
+                   autoincrement=False),
+            mysql_partitions='2',
+            mysql_partition_by='KEY(other_id)',
+            mysql_subpartition_by="HASH(some_expr)",
+            mysql_subpartitions='2')
+        self.assert_compile(
+            schema.CreateTable(t1),
+            'CREATE TABLE testtable ('
+            'id INTEGER NOT NULL AUTO_INCREMENT, '
+            'other_id INTEGER NOT NULL, '
+            'PRIMARY KEY (id, other_id)'
+            ')PARTITION BY KEY(other_id) PARTITIONS 2 '
+            'SUBPARTITION BY HASH(some_expr) SUBPARTITIONS 2'
+        )
+
     def test_create_table_with_partition_hash(self):
         t1 = Table(
             'testtable', MetaData(),
@@ -623,6 +643,23 @@ class SQLTest(fixtures.TestBase, AssertsCompiledSQL):
             ')PARTITION BY HASH(other_id) PARTITIONS 2'
         )
 
+    def test_create_table_with_partition_and_other_opts(self):
+        t1 = Table(
+            'testtable', MetaData(),
+            Column('id', Integer(), primary_key=True, autoincrement=True),
+            Column('other_id', Integer(), primary_key=True,
+                   autoincrement=False),
+            mysql_stats_sample_pages='2',
+            mysql_partitions='2', mysql_partition_by='HASH(other_id)')
+        self.assert_compile(
+            schema.CreateTable(t1),
+            'CREATE TABLE testtable ('
+            'id INTEGER NOT NULL AUTO_INCREMENT, '
+            'other_id INTEGER NOT NULL, '
+            'PRIMARY KEY (id, other_id)'
+            ')STATS_SAMPLE_PAGES=2 PARTITION BY HASH(other_id) PARTITIONS 2'
+        )
+
     def test_inner_join(self):
         t1 = table('t1', column('x'))
         t2 = table('t2', column('y'))