Revert automatic set of sequence start to 1

The :class:`.Sequence` construct restores itself to the DDL behavior it
had prior to the 1.4 series, where creating a :class:`.Sequence` with
no additional arguments will emit a simple ``CREATE SEQUENCE`` instruction
**without** any additional parameters for "start value".   For most backends,
this is how things worked previously in any case; **however**, for
MS SQL Server, the default value on this database is
``-2**63``; to prevent this generally impractical default
from taking effect on SQL Server, the :paramref:`.Sequence.start` parameter
should be provided.   As usage of :class:`.Sequence` is unusual
for SQL Server which for many years has standardized on ``IDENTITY``,
it is hoped that this change has minimal impact.

Fixes: #7211
Change-Id: I1207ea10c8cb1528a1519a0fb3581d9621c27b31

9 files changed, 106 insertions, 32 deletions

diff --git a/lib/sqlalchemy/dialects/mssql/base.py b/lib/sqlalchemy/dialects/mssql/base.py
index 085a2c27b..fdb0704f6 100644
--- a/lib/sqlalchemy/dialects/mssql/base.py
+++ b/lib/sqlalchemy/dialects/mssql/base.py
@@ -335,12 +335,31 @@ This is an auxiliary use case suitable for testing and bulk insert scenarios.
 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 start value of 1, even though the
-T-SQL defaults is -9223372036854775808.
+The :class:`.Sequence` object creates "real" sequences, i.e.,
+``CREATE SEQUENCE``::
 
-.. versionadded:: 1.4.0
+    >>> from sqlalchemy import Sequence
+    >>> from sqlalchemy.schema import CreateSequence
+    >>> from sqlalchemy.dialects import mssql
+    >>> print(CreateSequence(Sequence("my_seq", start=1)).compile(dialect=mssql.dialect()))
+    CREATE SEQUENCE my_seq START WITH 1
+
+For integer primary key generation, SQL Server's ``IDENTITY`` construct should
+generally be preferred vs. sequence.
+
+..tip::
+
+    The default start value for T-SQL is ``-2**63`` instead of 1 as
+    in most other SQL databases. Users should explicitly set the
+    :paramref:`.Sequence.start` to 1 if that's the expected default::
+
+        seq = Sequence("my_sequence", start=1)
+
+.. versionadded:: 1.4 added SQL Server support for :class:`.Sequence`
+
+.. versionchanged:: 2.0 The SQL Server dialect will no longer implicitly
+   render "START WITH 1" for ``CREATE SEQUENCE``, which was the behavior
+   first implemented in version 1.4.
 
 MAX on VARCHAR / NVARCHAR
 -------------------------
@@ -2907,7 +2926,9 @@ class MSDialect(default.DefaultDialect):
 
     supports_sequences = True
     sequences_optional = True
-    # T-SQL's actual default is -9223372036854775808
+    # This is actually used for autoincrement, where itentity is used that
+    # starts with 1.
+    # for sequences T-SQL's actual default is -9223372036854775808
     default_sequence_base = 1
 
     supports_native_boolean = False
diff --git a/lib/sqlalchemy/dialects/mssql/provision.py b/lib/sqlalchemy/dialects/mssql/provision.py
index f307c7140..a7ecf4aa3 100644
--- a/lib/sqlalchemy/dialects/mssql/provision.py
+++ b/lib/sqlalchemy/dialects/mssql/provision.py
@@ -14,6 +14,7 @@ from ...testing.provision import drop_all_schema_objects_pre_tables
 from ...testing.provision import drop_db
 from ...testing.provision import get_temp_table_name
 from ...testing.provision import log
+from ...testing.provision import normalize_sequence
 from ...testing.provision import run_reap_dbs
 from ...testing.provision import temp_table_keyword_args
 
@@ -116,3 +117,10 @@ def drop_all_schema_objects_pre_tables(cfg, eng):
                             )
                         )
                     )
+
+
+@normalize_sequence.for_db("mssql")
+def normalize_sequence(cfg, sequence):
+    if sequence.start is None:
+        sequence.start = 1
+    return sequence
diff --git a/lib/sqlalchemy/dialects/oracle/base.py b/lib/sqlalchemy/dialects/oracle/base.py
index c06da6ffe..6481ae483 100644
--- a/lib/sqlalchemy/dialects/oracle/base.py
+++ b/lib/sqlalchemy/dialects/oracle/base.py
@@ -66,14 +66,14 @@ specify sequences, use the sqlalchemy.schema.Sequence object which is passed
 to a Column construct::
 
   t = Table('mytable', metadata,
-        Column('id', Integer, Sequence('id_seq'), primary_key=True),
+        Column('id', Integer, Sequence('id_seq', start=1), primary_key=True),
         Column(...), ...
   )
 
 This step is also required when using table reflection, i.e. autoload_with=engine::
 
   t = Table('mytable', metadata,
-        Column('id', Integer, Sequence('id_seq'), primary_key=True),
+        Column('id', Integer, Sequence('id_seq', start=1), primary_key=True),
         autoload_with=engine
   )
 
diff --git a/lib/sqlalchemy/dialects/postgresql/base.py b/lib/sqlalchemy/dialects/postgresql/base.py
index f3db1a95e..5eab8f47c 100644
--- a/lib/sqlalchemy/dialects/postgresql/base.py
+++ b/lib/sqlalchemy/dialects/postgresql/base.py
@@ -27,9 +27,13 @@ default corresponding to the column.
 To specify a specific named sequence to be used for primary key generation,
 use the :func:`~sqlalchemy.schema.Sequence` construct::
 
-    Table('sometable', metadata,
-            Column('id', Integer, Sequence('some_id_seq'), primary_key=True)
+    Table(
+        "sometable",
+        metadata,
+        Column(
+            "id", Integer, Sequence("some_id_seq", start=1), primary_key=True
         )
+    )
 
 When SQLAlchemy issues a single INSERT statement, to fulfill the contract of
 having the "last insert identifier" available, a RETURNING clause is added to
diff --git a/lib/sqlalchemy/engine/default.py b/lib/sqlalchemy/engine/default.py
index cb3d0528f..71ceb3301 100644
--- a/lib/sqlalchemy/engine/default.py
+++ b/lib/sqlalchemy/engine/default.py
@@ -129,8 +129,7 @@ class DefaultDialect(Dialect):
     include_set_input_sizes: Optional[Set[Any]] = None
     exclude_set_input_sizes: Optional[Set[Any]] = None
 
-    # the first value we'd get for an autoincrement
-    # column.
+    # the first value we'd get for an autoincrement column.
     default_sequence_base = 1
 
     # most DBAPIs happy with this for execute().
diff --git a/lib/sqlalchemy/sql/compiler.py b/lib/sqlalchemy/sql/compiler.py
index efe0ea2b4..5b7238a07 100644
--- a/lib/sqlalchemy/sql/compiler.py
+++ b/lib/sqlalchemy/sql/compiler.py
@@ -5674,8 +5674,6 @@ class DDLCompiler(Compiled):
 
         if prefix:
             text += prefix
-        if create.element.start is None:
-            create.element.start = self.dialect.default_sequence_base
         options = self.get_identity_options(create.element)
         if options:
             text += " " + options
diff --git a/lib/sqlalchemy/sql/schema.py b/lib/sqlalchemy/sql/schema.py
index 5bfbd37c7..5d5f76c75 100644
--- a/lib/sqlalchemy/sql/schema.py
+++ b/lib/sqlalchemy/sql/schema.py
@@ -3323,7 +3323,7 @@ class Sequence(HasSchemaAttr, IdentityOptions, DefaultGenerator):
 
         some_table = Table(
             'some_table', metadata,
-            Column('id', Integer, Sequence('some_table_seq'),
+            Column('id', Integer, Sequence('some_table_seq', start=1),
             primary_key=True)
         )
 
@@ -3375,9 +3375,16 @@ class Sequence(HasSchemaAttr, IdentityOptions, DefaultGenerator):
 
         :param start: the starting index of the sequence.  This value is
          used when the CREATE SEQUENCE command is emitted to the database
-         as the value of the "START WITH" clause.   If ``None``, the
+         as the value of the "START WITH" clause. If ``None``, the
          clause is omitted, which on most platforms indicates a starting
          value of 1.
+
+         .. versionchanged:: 2.0 The :paramref:`.Sequence.start` parameter
+            is required in order to have DDL emit "START WITH".  This is a
+            reversal of a change made in version 1.4 which would implicitly
+            render "START WITH 1" if the :paramref:`.Sequence.start` were
+            not included.  See :ref:`change_7211` for more detail.
+
         :param increment: the increment value of the sequence.  This
          value is used when the CREATE SEQUENCE command is emitted to
          the database as the value of the "INCREMENT BY" clause.  If ``None``,
diff --git a/lib/sqlalchemy/testing/provision.py b/lib/sqlalchemy/testing/provision.py
index a8650f222..43821854c 100644
--- a/lib/sqlalchemy/testing/provision.py
+++ b/lib/sqlalchemy/testing/provision.py
@@ -475,3 +475,13 @@ def upsert(cfg, table, returning, set_lambda=None):
     raise NotImplementedError(
         f"backend does not include an upsert implementation: {cfg.db.url}"
     )
+
+
+@register.init
+def normalize_sequence(cfg, sequence):
+    """Normalize sequence parameters for dialect that don't start with 1
+    by default.
+
+    The default implementation does nothing
+    """
+    return sequence
diff --git a/lib/sqlalchemy/testing/suite/test_sequence.py b/lib/sqlalchemy/testing/suite/test_sequence.py
index e15fad642..a605e4f42 100644
--- a/lib/sqlalchemy/testing/suite/test_sequence.py
+++ b/lib/sqlalchemy/testing/suite/test_sequence.py
@@ -5,6 +5,7 @@ from .. import fixtures
 from ..assertions import eq_
 from ..assertions import is_true
 from ..config import requirements
+from ..provision import normalize_sequence
 from ..schema import Column
 from ..schema import Table
 from ... import inspect
@@ -29,7 +30,7 @@ class SequenceTest(fixtures.TablesTest):
             Column(
                 "id",
                 Integer,
-                Sequence("tab_id_seq"),
+                normalize_sequence(config, Sequence("tab_id_seq")),
                 primary_key=True,
             ),
             Column("data", String(50)),
@@ -41,7 +42,10 @@ class SequenceTest(fixtures.TablesTest):
             Column(
                 "id",
                 Integer,
-                Sequence("tab_id_seq", data_type=Integer, optional=True),
+                normalize_sequence(
+                    config,
+                    Sequence("tab_id_seq", data_type=Integer, optional=True),
+                ),
                 primary_key=True,
             ),
             Column("data", String(50)),
@@ -53,7 +57,7 @@ class SequenceTest(fixtures.TablesTest):
             Column(
                 "id",
                 Integer,
-                Sequence("noret_id_seq"),
+                normalize_sequence(config, Sequence("noret_id_seq")),
                 primary_key=True,
             ),
             Column("data", String(50)),
@@ -67,7 +71,12 @@ class SequenceTest(fixtures.TablesTest):
                 Column(
                     "id",
                     Integer,
-                    Sequence("noret_sch_id_seq", schema=config.test_schema),
+                    normalize_sequence(
+                        config,
+                        Sequence(
+                            "noret_sch_id_seq", schema=config.test_schema
+                        ),
+                    ),
                     primary_key=True,
                 ),
                 Column("data", String(50)),
@@ -118,7 +127,9 @@ class SequenceTest(fixtures.TablesTest):
             Column(
                 "id",
                 Integer,
-                Sequence("noret_sch_id_seq", schema="alt_schema"),
+                normalize_sequence(
+                    config, Sequence("noret_sch_id_seq", schema="alt_schema")
+                ),
                 primary_key=True,
             ),
             Column("data", String(50)),
@@ -134,7 +145,9 @@ class SequenceTest(fixtures.TablesTest):
 
     @testing.requires.schemas
     def test_nextval_direct_schema_translate(self, connection):
-        seq = Sequence("noret_sch_id_seq", schema="alt_schema")
+        seq = normalize_sequence(
+            config, Sequence("noret_sch_id_seq", schema="alt_schema")
+        )
         connection = connection.execution_options(
             schema_translate_map={"alt_schema": config.test_schema}
         )
@@ -151,7 +164,9 @@ class SequenceCompilerTest(testing.AssertsCompiledSQL, fixtures.TestBase):
         table = Table(
             "x",
             MetaData(),
-            Column("y", Integer, Sequence("y_seq")),
+            Column(
+                "y", Integer, normalize_sequence(config, Sequence("y_seq"))
+            ),
             Column("q", Integer),
         )
 
@@ -159,7 +174,7 @@ class SequenceCompilerTest(testing.AssertsCompiledSQL, fixtures.TestBase):
 
         seq_nextval = connection.dialect.statement_compiler(
             statement=None, dialect=connection.dialect
-        ).visit_sequence(Sequence("y_seq"))
+        ).visit_sequence(normalize_sequence(config, Sequence("y_seq")))
         self.assert_compile(
             stmt,
             "INSERT INTO x (y, q) VALUES (%s, 5)" % (seq_nextval,),
@@ -176,16 +191,28 @@ class HasSequenceTest(fixtures.TablesTest):
 
     @classmethod
     def define_tables(cls, metadata):
-        Sequence("user_id_seq", metadata=metadata)
-        Sequence(
-            "other_seq", metadata=metadata, nomaxvalue=True, nominvalue=True
+        normalize_sequence(config, Sequence("user_id_seq", metadata=metadata))
+        normalize_sequence(
+            config,
+            Sequence(
+                "other_seq",
+                metadata=metadata,
+                nomaxvalue=True,
+                nominvalue=True,
+            ),
         )
         if testing.requires.schemas.enabled:
-            Sequence(
-                "user_id_seq", schema=config.test_schema, metadata=metadata
+            normalize_sequence(
+                config,
+                Sequence(
+                    "user_id_seq", schema=config.test_schema, metadata=metadata
+                ),
             )
-            Sequence(
-                "schema_seq", schema=config.test_schema, metadata=metadata
+            normalize_sequence(
+                config,
+                Sequence(
+                    "schema_seq", schema=config.test_schema, metadata=metadata
+                ),
             )
         Table(
             "user_id_table",
@@ -199,7 +226,7 @@ class HasSequenceTest(fixtures.TablesTest):
     def test_has_sequence_cache(self, connection, metadata):
         insp = inspect(connection)
         eq_(insp.has_sequence("user_id_seq"), True)
-        ss = Sequence("new_seq", metadata=metadata)
+        ss = normalize_sequence(config, Sequence("new_seq", metadata=metadata))
         eq_(insp.has_sequence("new_seq"), False)
         ss.create(connection)
         try: