From 65bd6ec96602ab8b755b9bc25638957a09477de6 Mon Sep 17 00:00:00 2001 From: Mike Bayer Date: Fri, 20 Dec 2013 10:26:09 -0500 Subject: [PATCH] - 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] --- doc/build/changelog/changelog_08.rst | 13 +++++++++++++ lib/sqlalchemy/sql/compiler.py | 8 +++++++- test/sql/test_defaults.py | 28 ++++++++++++++++++++++++++++ 3 files changed, 48 insertions(+), 1 deletion(-) diff --git a/doc/build/changelog/changelog_08.rst b/doc/build/changelog/changelog_08.rst index 751cefdae8..1d48d96bc6 100644 --- a/doc/build/changelog/changelog_08.rst +++ b/doc/build/changelog/changelog_08.rst @@ -11,6 +11,19 @@ .. changelog:: :version: 0.8.5 + .. 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 diff --git a/lib/sqlalchemy/sql/compiler.py b/lib/sqlalchemy/sql/compiler.py index bd886bd40f..b099005702 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 4a17c1cda7..1622c4ed83 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',) -- 2.47.3