Propertly ignore ``Identity`` in MySQL and MariaDb.

Ensure that the MySQL and MariaDB dialect ignore the
:class:`_sql.Identity` construct while rendering the
``AUTO_INCREMENT`` keyword in a create table.

The Oracle and PostgreSQL compiler was updated to not render
:class:`_sql.Identity` if the database version does not support it
(Oracle < 12 and PostgreSQL < 10). Previously it was rendered regardless
of the database version.

Fixes: #6338
Change-Id: I2ca0902fdd7b4be4fc1a563cf5585504cbea9360

1 files changed, 35 insertions, 3 deletions

diff --git a/test/dialect/postgresql/test_compiler.py b/test/dialect/postgresql/test_compiler.py
index 4b2004a5f..a517ad1ac 100644
--- a/test/dialect/postgresql/test_compiler.py
+++ b/test/dialect/postgresql/test_compiler.py
@@ -1857,18 +1857,50 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL):
             dialect=postgresql.dialect(),
         )
 
-    def test_column_identity(self):
+    @testing.combinations(True, False)
+    def test_column_identity(self, pk):
         # all other tests are in test_identity_column.py
         m = MetaData()
         t = Table(
             "t",
             m,
-            Column("y", Integer, Identity(always=True, start=4, increment=7)),
+            Column(
+                "y",
+                Integer,
+                Identity(always=True, start=4, increment=7),
+                primary_key=pk,
+            ),
         )
         self.assert_compile(
             schema.CreateTable(t),
             "CREATE TABLE t (y INTEGER GENERATED ALWAYS AS IDENTITY "
-            "(INCREMENT BY 7 START WITH 4))",
+            "(INCREMENT BY 7 START WITH 4)%s)"
+            % (", PRIMARY KEY (y)" if pk else ""),
+        )
+
+    @testing.combinations(True, False)
+    def test_column_identity_no_support(self, pk):
+        m = MetaData()
+        t = Table(
+            "t",
+            m,
+            Column(
+                "y",
+                Integer,
+                Identity(always=True, start=4, increment=7),
+                primary_key=pk,
+            ),
+        )
+        dd = PGDialect()
+        dd.supports_identity_columns = False
+        self.assert_compile(
+            schema.CreateTable(t),
+            "CREATE TABLE t (y %s%s)"
+            % (
+                "SERIAL NOT NULL" if pk else "INTEGER NOT NULL",
+                ", PRIMARY KEY (y)" if pk else "",
+            ),
+            dialect=dd,
         )
 
     def test_column_identity_null(self):