Generalize RETURNING and suppor for MariaDB / SQLite

As almost every dialect supports RETURNING now, RETURNING
is also made more of a default assumption.

* the default compiler generates a RETURNING clause now
  when specified; CompileError is no longer raised.
* The dialect-level implicit_returning parameter now has
  no effect.   It's not fully clear if there are real world
  cases relying on the dialect-level parameter, so we will see
  once 2.0 is released.   ORM-level RETURNING can be disabled
  at the table level, and perhaps "implicit returning" should
  become an ORM-level option at some point as that's where
  it applies.
* Altered ORM update() / delete() to respect table-level
  implicit returning for fetch.
* Since MariaDB doesnt support UPDATE returning, "full_returning"
  is now split into insert_returning, update_returning, delete_returning
* Crazy new thing.  Dialects that have *both* cursor.lastrowid
  *and* returning.   so now we can pick between them for SQLite
  and mariadb.  so, we are trying to keep it on .lastrowid for
  simple inserts with an autoincrement column, this helps with
  some edge case test scenarios and i bet .lastrowid is faster
  anyway.  any return_defaults() / multiparams etc then we
  use returning
* SQLite decided they dont want to return rows that match in
  ON CONFLICT.  this is flat out wrong, but for now we need to
  work with it.

Fixes: #6195
Fixes: #7011
Closes: #7047
Pull-request: https://github.com/sqlalchemy/sqlalchemy/pull/7047
Pull-request-sha: d25d5ea3abe094f282c53c7dd87f5f53a9e85248

Co-authored-by: Mike Bayer <mike_mp@zzzcomputing.com>
Change-Id: I9908ce0ff7bdc50bd5b27722081767c31c19a950

4 files changed, 56 insertions, 32 deletions

diff --git a/lib/sqlalchemy/testing/assertsql.py b/lib/sqlalchemy/testing/assertsql.py
index a6e3c8764..4416fe630 100644
--- a/lib/sqlalchemy/testing/assertsql.py
+++ b/lib/sqlalchemy/testing/assertsql.py
@@ -67,10 +67,13 @@ class CursorSQL(SQLMatchRule):
 
 
 class CompiledSQL(SQLMatchRule):
-    def __init__(self, statement, params=None, dialect="default"):
+    def __init__(
+        self, statement, params=None, dialect="default", enable_returning=False
+    ):
         self.statement = statement
         self.params = params
         self.dialect = dialect
+        self.enable_returning = enable_returning
 
     def _compare_sql(self, execute_observed, received_statement):
         stmt = re.sub(r"[\n\t]", "", self.statement)
@@ -82,14 +85,14 @@ class CompiledSQL(SQLMatchRule):
             # this is currently what tests are expecting
             # dialect.supports_default_values = True
             dialect.supports_default_metavalue = True
+
+            if self.enable_returning:
+                dialect.insert_returning = (
+                    dialect.update_returning
+                ) = dialect.delete_returning = True
             return dialect
         else:
-            # ugh
-            if self.dialect == "postgresql":
-                params = {"implicit_returning": True}
-            else:
-                params = {}
-            return url.URL.create(self.dialect).get_dialect()(**params)
+            return url.URL.create(self.dialect).get_dialect()()
 
     def _received_statement(self, execute_observed):
         """reconstruct the statement and params in terms
@@ -221,12 +224,15 @@ class CompiledSQL(SQLMatchRule):
 
 
 class RegexSQL(CompiledSQL):
-    def __init__(self, regex, params=None, dialect="default"):
+    def __init__(
+        self, regex, params=None, dialect="default", enable_returning=False
+    ):
         SQLMatchRule.__init__(self)
         self.regex = re.compile(regex)
         self.orig_regex = regex
         self.params = params
         self.dialect = dialect
+        self.enable_returning = enable_returning
 
     def _failure_message(self, execute_observed, expected_params):
         return (
diff --git a/lib/sqlalchemy/testing/fixtures.py b/lib/sqlalchemy/testing/fixtures.py
index ae7a42488..d0e7d8f3c 100644
--- a/lib/sqlalchemy/testing/fixtures.py
+++ b/lib/sqlalchemy/testing/fixtures.py
@@ -90,6 +90,20 @@ class TestBase:
         conn.close()
 
     @config.fixture()
+    def close_result_when_finished(self):
+        to_close = []
+
+        def go(result):
+            to_close.append(result)
+
+        yield go
+        for r in to_close:
+            try:
+                r.close()
+            except:
+                pass
+
+    @config.fixture()
     def registry(self, metadata):
         reg = registry(
             metadata=metadata,
diff --git a/lib/sqlalchemy/testing/requirements.py b/lib/sqlalchemy/testing/requirements.py
index 4fff6546e..4f9c73cf6 100644
--- a/lib/sqlalchemy/testing/requirements.py
+++ b/lib/sqlalchemy/testing/requirements.py
@@ -365,15 +365,30 @@ class SuiteRequirements(Requirements):
         return exclusions.open()
 
     @property
-    def full_returning(self):
-        """target platform supports RETURNING completely, including
-        multiple rows returned.
+    def delete_returning(self):
+        """target platform supports DELETE ... RETURNING."""
 
-        """
+        return exclusions.only_if(
+            lambda config: config.db.dialect.delete_returning,
+            "%(database)s %(does_support)s 'DELETE ... RETURNING'",
+        )
+
+    @property
+    def insert_returning(self):
+        """target platform supports INSERT ... RETURNING."""
+
+        return exclusions.only_if(
+            lambda config: config.db.dialect.insert_returning,
+            "%(database)s %(does_support)s 'INSERT ... RETURNING'",
+        )
+
+    @property
+    def update_returning(self):
+        """target platform supports UPDATE ... RETURNING."""
 
         return exclusions.only_if(
-            lambda config: config.db.dialect.full_returning,
-            "%(database)s %(does_support)s 'RETURNING of multiple rows'",
+            lambda config: config.db.dialect.update_returning,
+            "%(database)s %(does_support)s 'UPDATE ... RETURNING'",
         )
 
     @property
@@ -391,21 +406,6 @@ class SuiteRequirements(Requirements):
         )
 
     @property
-    def returning(self):
-        """target platform supports RETURNING for at least one row.
-
-        .. seealso::
-
-            :attr:`.Requirements.full_returning`
-
-        """
-
-        return exclusions.only_if(
-            lambda config: config.db.dialect.implicit_returning,
-            "%(database)s %(does_support)s 'RETURNING of a single row'",
-        )
-
-    @property
     def tuple_in(self):
         """Target platform supports the syntax
         "(x, y) IN ((x1, y1), (x2, y2), ...)"
diff --git a/lib/sqlalchemy/testing/suite/test_insert.py b/lib/sqlalchemy/testing/suite/test_insert.py
index f0e4bfcc6..2307d3b3f 100644
--- a/lib/sqlalchemy/testing/suite/test_insert.py
+++ b/lib/sqlalchemy/testing/suite/test_insert.py
@@ -125,10 +125,14 @@ class InsertBehaviorTest(fixtures.TablesTest):
         # case, the row had to have been consumed at least.
         assert not r.returns_rows or r.fetchone() is None
 
-    @requirements.returning
+    @requirements.insert_returning
     def test_autoclose_on_insert_implicit_returning(self, connection):
         r = connection.execute(
-            self.tables.autoinc_pk.insert(), dict(data="some data")
+            # return_defaults() ensures RETURNING will be used,
+            # new in 2.0 as sqlite/mariadb offer both RETURNING and
+            # cursor.lastrowid
+            self.tables.autoinc_pk.insert().return_defaults(),
+            dict(data="some data"),
         )
         assert r._soft_closed
         assert not r.closed
@@ -295,7 +299,7 @@ class InsertBehaviorTest(fixtures.TablesTest):
 
 class ReturningTest(fixtures.TablesTest):
     run_create_tables = "each"
-    __requires__ = "returning", "autoincrement_insert"
+    __requires__ = "insert_returning", "autoincrement_insert"
     __backend__ = True
 
     def _assert_round_trip(self, table, conn):