- Fixed issue where a primary key column that has a Sequence on it,

yet the column is not the "auto increment" column, either because
it has a foreign key constraint or ``autoincrement=False`` set,
would attempt to fire the Sequence on INSERT for backends that don't
support sequences, when presented with an INSERT missing the primary
key value.  This would take place on non-sequence backends like
SQLite, MySQL. [ticket:2896]

3 files changed, 48 insertions, 1 deletions

diff --git a/doc/build/changelog/changelog_08.rst b/doc/build/changelog/changelog_08.rst
index 751cefdae..1d48d96bc 100644
--- a/doc/build/changelog/changelog_08.rst
+++ b/doc/build/changelog/changelog_08.rst
@@ -14,6 +14,19 @@
     .. change::
         :tags: bug, sql
         :versions: 0.9.0b2
+        :tickets: 2896
+
+        Fixed issue where a primary key column that has a Sequence on it,
+        yet the column is not the "auto increment" column, either because
+        it has a foreign key constraint or ``autoincrement=False`` set,
+        would attempt to fire the Sequence on INSERT for backends that don't
+        support sequences, when presented with an INSERT missing the primary
+        key value.  This would take place on non-sequence backends like
+        SQLite, MySQL.
+
+    .. change::
+        :tags: bug, sql
+        :versions: 0.9.0b2
         :tickets: 2895
 
         Fixed bug with :meth:`.Insert.from_select` method where the order
diff --git a/lib/sqlalchemy/sql/compiler.py b/lib/sqlalchemy/sql/compiler.py
index bd886bd40..b09900570 100644
--- a/lib/sqlalchemy/sql/compiler.py
+++ b/lib/sqlalchemy/sql/compiler.py
@@ -2035,7 +2035,13 @@ class SQLCompiler(Compiled):
                         else:
                             self.returning.append(c)
                     else:
-                        if c.default is not None or \
+                        if (
+                                c.default is not None and
+                                (
+                                    not c.default.is_sequence or
+                                    self.dialect.supports_sequences
+                                )
+                            ) or \
                             c is stmt.table._autoincrement_column and (
                                 self.dialect.supports_sequences or
                                 self.dialect.preexecute_autoincrement_sequences
diff --git a/test/sql/test_defaults.py b/test/sql/test_defaults.py
index 4a17c1cda..1622c4ed8 100644
--- a/test/sql/test_defaults.py
+++ b/test/sql/test_defaults.py
@@ -615,6 +615,33 @@ class AutoIncrementTest(fixtures.TablesTest):
 
         nonai.insert().execute(id=1, data='row 1')
 
+
+    def test_col_w_sequence_non_autoinc_no_firing(self):
+        metadata = self.metadata
+        # plain autoincrement/PK table in the actual schema
+        Table("x", metadata,
+            Column("set_id", Integer, primary_key=True)
+        )
+        metadata.create_all()
+
+        # for the INSERT use a table with a Sequence
+        # and autoincrement=False.  Using a ForeignKey
+        # would have the same effect
+        dataset_no_autoinc = Table("x", MetaData(),
+            Column("set_id", Integer, Sequence("some_seq"),
+                        primary_key=True, autoincrement=False)
+            )
+
+        testing.db.execute(
+                dataset_no_autoinc.insert()
+        )
+        eq_(
+            testing.db.scalar(dataset_no_autoinc.count()), 1
+        )
+
+
+
+
 class SequenceDDLTest(fixtures.TestBase, testing.AssertsCompiledSQL):
     __dialect__ = 'default'
 
@@ -887,6 +914,7 @@ class SequenceTest(fixtures.TestBase, testing.AssertsCompiledSQL):
         assert not self._has_sequence('s1')
         assert not self._has_sequence('s2')
 
+
 cartitems = sometable = metadata = None
 class TableBoundSequenceTest(fixtures.TestBase):
     __requires__ = ('sequences',)