explicitly fetch inserted pk for values(pkcol=None)

Altered the compilation mechanics of the :class:`.Insert` construct such
that the "autoincrement primary key" column value will be fetched via
``cursor.lastrowid`` or RETURNING even if present in the parameter set or
within the :meth:`.Insert.values` method as a plain bound value, for
single-row INSERT statements on specific backends that are known to
generate autoincrementing values even when explicit NULL is passed. This
restores a behavior that was in the 1.3 series for both the use case of
separate parameter set as well as :meth:`.Insert.values`. In 1.4, the
parameter set behavior unintentionally changed to no longer do this, but
the :meth:`.Insert.values` method would still fetch autoincrement values up
until 1.4.21 where :ticket:`6770` changed the behavior yet again again
unintentionally as this use case was never covered.

The behavior is now defined as "working" to suit the case where databases
such as SQLite, MySQL and MariaDB will ignore an explicit NULL primary key
value and nonetheless invoke an autoincrement generator.

Fixes: #7998
Change-Id: I5d4105a14217945f87fbe9a6f2a3c87f6ef20529

1 files changed, 41 insertions, 2 deletions

diff --git a/test/sql/test_insert_exec.py b/test/sql/test_insert_exec.py
index ef1a3be09..45f4098b2 100644
--- a/test/sql/test_insert_exec.py
+++ b/test/sql/test_insert_exec.py
@@ -421,7 +421,7 @@ class InsertExecTest(fixtures.TablesTest):
 class TableInsertTest(fixtures.TablesTest):
 
     """test for consistent insert behavior across dialects
-    regarding the inline() method, lower-case 't' tables.
+    regarding the inline() method, values() method, lower-case 't' tables.
 
     """
 
@@ -479,8 +479,12 @@ class TableInsertTest(fixtures.TablesTest):
         returning=None,
         inserted_primary_key=False,
         table=None,
+        parameters=None,
     ):
-        r = connection.execute(stmt)
+        if parameters is not None:
+            r = connection.execute(stmt, parameters)
+        else:
+            r = connection.execute(stmt)
 
         if returning:
             returned = r.first()
@@ -645,3 +649,38 @@ class TableInsertTest(fixtures.TablesTest):
             (testing.db.dialect.default_sequence_base, "data", 5),
             inserted_primary_key=(),
         )
+
+    @testing.requires.database_discards_null_for_autoincrement
+    def test_explicit_null_pk_values_db_ignores_it(self, connection):
+        """test new use case in #7998"""
+
+        # NOTE: this use case uses cursor.lastrowid on SQLite, MySQL, MariaDB,
+        # however when SQLAlchemy 2.0 adds support for RETURNING to SQLite
+        # and MariaDB, it should work there as well.
+
+        t = self.tables.foo_no_seq
+        self._test(
+            connection,
+            t.insert().values(id=None, data="data", x=5),
+            (testing.db.dialect.default_sequence_base, "data", 5),
+            inserted_primary_key=(testing.db.dialect.default_sequence_base,),
+            table=t,
+        )
+
+    @testing.requires.database_discards_null_for_autoincrement
+    def test_explicit_null_pk_params_db_ignores_it(self, connection):
+        """test new use case in #7998"""
+
+        # NOTE: this use case uses cursor.lastrowid on SQLite, MySQL, MariaDB,
+        # however when SQLAlchemy 2.0 adds support for RETURNING to SQLite
+        # and MariaDB, it should work there as well.
+
+        t = self.tables.foo_no_seq
+        self._test(
+            connection,
+            t.insert(),
+            (testing.db.dialect.default_sequence_base, "data", 5),
+            inserted_primary_key=(testing.db.dialect.default_sequence_base,),
+            table=t,
+            parameters=dict(id=None, data="data", x=5),
+        )