Merge "Do not specify type on mssql by default"

10 files changed, 31 insertions, 46 deletions

diff --git a/lib/sqlalchemy/dialects/mssql/base.py b/lib/sqlalchemy/dialects/mssql/base.py
index 505a6c772..b398610b2 100644
--- a/lib/sqlalchemy/dialects/mssql/base.py
+++ b/lib/sqlalchemy/dialects/mssql/base.py
@@ -242,9 +242,8 @@ SEQUENCE support
 
 The :class:`.Sequence` object now creates "real" sequences, i.e.,
 ``CREATE SEQUENCE``. To provide compatibility with other dialects,
-:class:`.Sequence` defaults to a data type of Integer and a start value of 1,
-even though the T-SQL defaults are BIGINT and -9223372036854775808,
-respectively.
+:class:`.Sequence` defaults to a start value of 1, even though the
+T-SQL defaults is -9223372036854775808.
 
 .. versionadded:: 1.4.0
 
@@ -757,7 +756,6 @@ from ...sql import func
 from ...sql import quoted_name
 from ...sql import roles
 from ...sql import util as sql_util
-from ...sql.type_api import to_instance
 from ...types import BIGINT
 from ...types import BINARY
 from ...types import CHAR
@@ -1639,6 +1637,16 @@ class MSExecutionContext(default.DefaultExecutionContext):
             type_,
         )
 
+    def get_insert_default(self, column):
+        if (
+            isinstance(column, sa_schema.Column)
+            and column is column.table._autoincrement_column
+            and isinstance(column.default, sa_schema.Sequence)
+            and column.default.optional
+        ):
+            return None
+        return super(MSExecutionContext, self).get_insert_default(column)
+
 
 class MSSQLCompiler(compiler.SQLCompiler):
     returning_precedes_values = True
@@ -2204,11 +2212,10 @@ class MSDDLCompiler(compiler.DDLCompiler):
         elif (
             column is column.table._autoincrement_column
             or column.autoincrement is True
+        ) and (
+            not isinstance(column.default, Sequence) or column.default.optional
         ):
-            if not isinstance(column.default, Sequence):
-                colspec += self.process(
-                    Identity(start=start, increment=increment)
-                )
+            colspec += self.process(Identity(start=start, increment=increment))
         else:
             default = self.get_column_default_string(column)
             if default is not None:
@@ -2332,13 +2339,10 @@ class MSDDLCompiler(compiler.DDLCompiler):
         return text
 
     def visit_create_sequence(self, create, **kw):
-
+        prefix = None
         if create.element.data_type is not None:
             data_type = create.element.data_type
-        else:
-            data_type = to_instance(self.dialect.sequence_default_column_type)
-
-        prefix = " AS %s" % self.type_compiler.process(data_type)
+            prefix = " AS %s" % self.type_compiler.process(data_type)
         return super(MSDDLCompiler, self).visit_create_sequence(
             create, prefix=prefix, **kw
         )
@@ -2547,8 +2551,7 @@ class MSDialect(default.DefaultDialect):
     ischema_names = ischema_names
 
     supports_sequences = True
-    # T-SQL's actual default is BIGINT
-    sequence_default_column_type = INTEGER
+    sequences_optional = True
     # T-SQL's actual default is -9223372036854775808
     default_sequence_base = 1
 
@@ -3148,7 +3151,6 @@ class MSDialect(default.DefaultDialect):
         RR = ischema.ref_constraints
         C = ischema.key_constraints.alias("C")
         R = ischema.key_constraints.alias("R")
-
         # Foreign key constraints
         s = (
             sql.select(
diff --git a/lib/sqlalchemy/dialects/mysql/base.py b/lib/sqlalchemy/dialects/mysql/base.py
index 49ff0c14a..130aeeb0f 100644
--- a/lib/sqlalchemy/dialects/mysql/base.py
+++ b/lib/sqlalchemy/dialects/mysql/base.py
@@ -2432,7 +2432,7 @@ class MySQLDialect(default.DefaultDialect):
     supports_sequences = False  # default for MySQL ...
     # ... may be updated to True for MariaDB 10.3+ in initialize()
 
-    sequences_optional = True
+    sequences_optional = False
 
     supports_for_update_of = False  # default for MySQL ...
     # ... may be updated to True for MySQL 8+ in initialize()
diff --git a/lib/sqlalchemy/dialects/postgresql/base.py b/lib/sqlalchemy/dialects/postgresql/base.py
index 819f1238a..93ee7ca29 100644
--- a/lib/sqlalchemy/dialects/postgresql/base.py
+++ b/lib/sqlalchemy/dialects/postgresql/base.py
@@ -2695,7 +2695,6 @@ class PGDialect(default.DefaultDialect):
 
     supports_sequences = True
     sequences_optional = True
-    sequence_default_column_type = INTEGER
     preexecute_autoincrement_sequences = True
     postfetch_lastrowid = False
 
diff --git a/lib/sqlalchemy/engine/default.py b/lib/sqlalchemy/engine/default.py
index 198ec4159..9d2eaf606 100644
--- a/lib/sqlalchemy/engine/default.py
+++ b/lib/sqlalchemy/engine/default.py
@@ -24,7 +24,6 @@ from . import cursor as _cursor
 from . import interfaces
 from .. import event
 from .. import exc
-from .. import Integer
 from .. import pool
 from .. import processors
 from .. import types as sqltypes
@@ -66,8 +65,6 @@ class DefaultDialect(interfaces.Dialect):
     # not cx_oracle.
     execute_sequence_format = tuple
 
-    sequence_default_column_type = Integer
-
     supports_views = True
     supports_sequences = False
     sequences_optional = False
diff --git a/test/dialect/mssql/test_sequence.py b/test/dialect/mssql/test_sequence.py
index 36e2edd6d..2b1db0b41 100644
--- a/test/dialect/mssql/test_sequence.py
+++ b/test/dialect/mssql/test_sequence.py
@@ -21,7 +21,9 @@ class SequenceTest(fixtures.TablesTest):
         Table(
             "int_seq_t",
             metadata,
-            Column("id", Integer, default=Sequence("int_seq")),
+            Column(
+                "id", Integer, default=Sequence("int_seq", data_type=Integer())
+            ),
             Column("txt", String(50)),
         )
 
@@ -29,11 +31,7 @@ class SequenceTest(fixtures.TablesTest):
             "bigint_seq_t",
             metadata,
             Column(
-                "id",
-                BIGINT,
-                default=Sequence(
-                    "bigint_seq", data_type=BIGINT, start=3000000000
-                ),
+                "id", BIGINT, default=Sequence("bigint_seq", start=3000000000),
             ),
             Column("txt", String(50)),
         )
diff --git a/test/dialect/mssql/test_types.py b/test/dialect/mssql/test_types.py
index df54321b4..34b026cf3 100644
--- a/test/dialect/mssql/test_types.py
+++ b/test/dialect/mssql/test_types.py
@@ -561,10 +561,9 @@ class TypeRoundTripTest(
             metadata,
             Column(
                 "id",
-                testing.db.dialect.sequence_default_column_type,
+                Integer,
                 Sequence("numeric_id_seq", optional=True),
                 primary_key=True,
-                autoincrement=False,
             ),
             Column(
                 "numericcol", Numeric(precision=38, scale=20, asdecimal=True)
@@ -652,7 +651,7 @@ class TypeRoundTripTest(
             metadata,
             Column(
                 "id",
-                testing.db.dialect.sequence_default_column_type,
+                Integer,
                 Sequence("numeric_id_seq", optional=True),
                 primary_key=True,
             ),
diff --git a/test/engine/test_execute.py b/test/engine/test_execute.py
index fdab1e8da..61e25488e 100644
--- a/test/engine/test_execute.py
+++ b/test/engine/test_execute.py
@@ -1932,12 +1932,7 @@ class EngineEventsTest(fixtures.TestBase):
         t = Table(
             "t",
             self.metadata,
-            Column(
-                "x",
-                testing.db.dialect.sequence_default_column_type,
-                Sequence("t_id_seq"),
-                primary_key=True,
-            ),
+            Column("x", Integer, Sequence("t_id_seq"), primary_key=True,),
             implicit_returning=False,
         )
         self.metadata.create_all(engine)
diff --git a/test/sql/test_defaults.py b/test/sql/test_defaults.py
index 3733d70c7..f9bec7914 100644
--- a/test/sql/test_defaults.py
+++ b/test/sql/test_defaults.py
@@ -996,7 +996,7 @@ class PKIncrementTest(fixtures.TablesTest):
             metadata,
             Column(
                 "id",
-                testing.db.dialect.sequence_default_column_type,
+                Integer,
                 Sequence("ai_id_seq", optional=True),
                 primary_key=True,
             ),
@@ -1227,7 +1227,7 @@ class SpecialTypePKTest(fixtures.TestBase):
     @classmethod
     def setup_class(cls):
         class MyInteger(TypeDecorator):
-            impl = testing.db.dialect.sequence_default_column_type
+            impl = Integer
 
             def process_bind_param(self, value, dialect):
                 if value is None:
diff --git a/test/sql/test_functions.py b/test/sql/test_functions.py
index 717fc47af..73954a8af 100644
--- a/test/sql/test_functions.py
+++ b/test/sql/test_functions.py
@@ -971,7 +971,7 @@ class ExecuteTest(fixtures.TestBase):
             meta,
             Column(
                 "id",
-                testing.db.dialect.sequence_default_column_type,
+                Integer,
                 Sequence("t1idseq", optional=True),
                 primary_key=True,
             ),
@@ -982,7 +982,7 @@ class ExecuteTest(fixtures.TestBase):
             meta,
             Column(
                 "id",
-                testing.db.dialect.sequence_default_column_type,
+                Integer,
                 Sequence("t2idseq", optional=True),
                 primary_key=True,
             ),
diff --git a/test/sql/test_insert_exec.py b/test/sql/test_insert_exec.py
index 16b27aeaa..c4266603e 100644
--- a/test/sql/test_insert_exec.py
+++ b/test/sql/test_insert_exec.py
@@ -369,12 +369,7 @@ class TableInsertTest(fixtures.TablesTest):
         Table(
             "foo",
             metadata,
-            Column(
-                "id",
-                testing.db.dialect.sequence_default_column_type,
-                Sequence("t_id_seq"),
-                primary_key=True,
-            ),
+            Column("id", Integer, Sequence("t_id_seq"), primary_key=True,),
             Column("data", String(50)),
             Column("x", Integer),
         )