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

8 files changed, 132 insertions, 54 deletions

diff --git a/lib/sqlalchemy/dialects/mssql/base.py b/lib/sqlalchemy/dialects/mssql/base.py
index 4295e0ed0..12f495d6e 100644
--- a/lib/sqlalchemy/dialects/mssql/base.py
+++ b/lib/sqlalchemy/dialects/mssql/base.py
@@ -2807,8 +2807,9 @@ class MSDialect(default.DefaultDialect):
     max_identifier_length = 128
     schema_name = "dbo"
 
-    implicit_returning = True
-    full_returning = True
+    insert_returning = True
+    update_returning = True
+    delete_returning = True
 
     colspecs = {
         sqltypes.DateTime: _MSDateTime,
diff --git a/lib/sqlalchemy/dialects/mssql/pyodbc.py b/lib/sqlalchemy/dialects/mssql/pyodbc.py
index 28cca56f7..6d64fdc3e 100644
--- a/lib/sqlalchemy/dialects/mssql/pyodbc.py
+++ b/lib/sqlalchemy/dialects/mssql/pyodbc.py
@@ -522,6 +522,8 @@ class MSDialect_pyodbc(PyODBCConnector, MSDialect):
     # mssql still has problems with this on Linux
     supports_sane_rowcount_returning = False
 
+    favor_returning_over_lastrowid = True
+
     execution_ctx_cls = MSExecutionContext_pyodbc
 
     colspecs = util.update_copy(
diff --git a/lib/sqlalchemy/dialects/mysql/base.py b/lib/sqlalchemy/dialects/mysql/base.py
index b585ea992..68653d976 100644
--- a/lib/sqlalchemy/dialects/mysql/base.py
+++ b/lib/sqlalchemy/dialects/mysql/base.py
@@ -488,6 +488,37 @@ available.
 
         :class:`_mysql.match`
 
+INSERT/DELETE...RETURNING
+-------------------------
+
+The MariaDB dialect supports 10.5+'s ``INSERT..RETURNING`` and
+``DELETE..RETURNING`` (10.0+) syntaxes.   ``INSERT..RETURNING`` may be used
+automatically in some cases in order to fetch newly generated identifiers in
+place of the traditional approach of using ``cursor.lastrowid``, however
+``cursor.lastrowid`` is currently still preferred for simple single-statement
+cases for its better performance.
+
+To specify an explicit ``RETURNING`` clause, use the
+:meth:`._UpdateBase.returning` method on a per-statement basis::
+
+    # INSERT..RETURNING
+    result = connection.execute(
+        table.insert().
+        values(name='foo').
+        returning(table.c.col1, table.c.col2)
+    )
+    print(result.all())
+
+    # DELETE..RETURNING
+    result = connection.execute(
+        table.delete().
+        where(table.c.name=='foo').
+        returning(table.c.col1, table.c.col2)
+    )
+    print(result.all())
+
+.. versionadded:: 2.0  Added support for MariaDB RETURNING
+
 .. _mysql_insert_on_duplicate_key_update:
 
 INSERT...ON DUPLICATE KEY UPDATE (Upsert)
@@ -2500,7 +2531,9 @@ class MySQLDialect(default.DefaultDialect):
 
         server_version_info = tuple(version)
 
-        self._set_mariadb(server_version_info and is_mariadb, val)
+        self._set_mariadb(
+            server_version_info and is_mariadb, server_version_info
+        )
 
         if not is_mariadb:
             self._mariadb_normalized_version_info = server_version_info
@@ -2522,7 +2555,7 @@ class MySQLDialect(default.DefaultDialect):
         if not is_mariadb and self.is_mariadb:
             raise exc.InvalidRequestError(
                 "MySQL version %s is not a MariaDB variant."
-                % (server_version_info,)
+                % (".".join(map(str, server_version_info)),)
             )
         if is_mariadb:
             self.preparer = MariaDBIdentifierPreparer
@@ -2717,6 +2750,14 @@ class MySQLDialect(default.DefaultDialect):
             not self.is_mariadb and self.server_version_info >= (8,)
         )
 
+        self.delete_returning = (
+            self.is_mariadb and self.server_version_info >= (10, 0, 5)
+        )
+
+        self.insert_returning = (
+            self.is_mariadb and self.server_version_info >= (10, 5)
+        )
+
         self._warn_for_known_db_issues()
 
     def _warn_for_known_db_issues(self):
diff --git a/lib/sqlalchemy/dialects/oracle/base.py b/lib/sqlalchemy/dialects/oracle/base.py
index 37b81e1dd..faac0deb7 100644
--- a/lib/sqlalchemy/dialects/oracle/base.py
+++ b/lib/sqlalchemy/dialects/oracle/base.py
@@ -293,40 +293,16 @@ added in a future release.
 RETURNING Support
 -----------------
 
-The Oracle database supports a limited form of RETURNING, in order to retrieve
-result sets of matched rows from INSERT, UPDATE and DELETE statements.
-Oracle's RETURNING..INTO syntax only supports one row being returned, as it
-relies upon OUT parameters in order to function.  In addition, supported
-DBAPIs have further limitations (see :ref:`cx_oracle_returning`).
+The Oracle database supports RETURNING fully for INSERT, UPDATE and DELETE
+statements that are invoked with a single collection of bound parameters
+(that is, a ``cursor.execute()`` style statement; SQLAlchemy does not generally
+support RETURNING with :term:`executemany` statements).  Multiple rows may be
+returned as well.
 
-SQLAlchemy's "implicit returning" feature, which employs RETURNING within an
-INSERT and sometimes an UPDATE statement in order to fetch newly generated
-primary key values and other SQL defaults and expressions, is normally enabled
-on the Oracle backend.  By default, "implicit returning" typically only
-fetches the value of a single ``nextval(some_seq)`` expression embedded into
-an INSERT in order to increment a sequence within an INSERT statement and get
-the value back at the same time. To disable this feature across the board,
-specify ``implicit_returning=False`` to :func:`_sa.create_engine`::
+.. versionchanged:: 2.0  the Oracle backend has full support for RETURNING
+   on parity with other backends.
 
-    engine = create_engine("oracle+cx_oracle://scott:tiger@dsn",
-                           implicit_returning=False)
 
-Implicit returning can also be disabled on a table-by-table basis as a table
-option::
-
-    # Core Table
-    my_table = Table("my_table", metadata, ..., implicit_returning=False)
-
-
-    # declarative
-    class MyClass(Base):
-        __tablename__ = 'my_table'
-        __table_args__ = {"implicit_returning": False}
-
-.. seealso::
-
-    :ref:`cx_oracle_returning` - additional cx_oracle-specific restrictions on
-    implicit returning.
 
 ON UPDATE CASCADE
 -----------------
@@ -1572,8 +1548,9 @@ class OracleDialect(default.DefaultDialect):
     supports_alter = True
     max_identifier_length = 128
 
-    implicit_returning = True
-    full_returning = True
+    insert_returning = True
+    update_returning = True
+    delete_returning = True
 
     div_is_floordiv = False
 
diff --git a/lib/sqlalchemy/dialects/postgresql/base.py b/lib/sqlalchemy/dialects/postgresql/base.py
index 146e59c4d..83e46151f 100644
--- a/lib/sqlalchemy/dialects/postgresql/base.py
+++ b/lib/sqlalchemy/dialects/postgresql/base.py
@@ -44,8 +44,6 @@ subsequent insert. Note that when an
 apply; no RETURNING clause is emitted nor is the sequence pre-executed in this
 case.
 
-To force the usage of RETURNING by default off, specify the flag
-``implicit_returning=False`` to :func:`_sa.create_engine`.
 
 PostgreSQL 10 and above IDENTITY columns
 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
@@ -2351,16 +2349,6 @@ class PGCompiler(compiler.SQLCompiler):
 
         return tmp
 
-    def returning_clause(
-        self, stmt, returning_cols, *, populate_result_map, **kw
-    ):
-        columns = [
-            self._label_returning_column(stmt, c, populate_result_map)
-            for c in expression._select_iterables(returning_cols)
-        ]
-
-        return "RETURNING " + ", ".join(columns)
-
     def visit_substring_func(self, func, **kw):
         s = self.process(func.clauses.clauses[0], **kw)
         start = self.process(func.clauses.clauses[1], **kw)
@@ -3207,8 +3195,9 @@ class PGDialect(default.DefaultDialect):
     execution_ctx_cls = PGExecutionContext
     inspector = PGInspector
 
-    implicit_returning = True
-    full_returning = True
+    update_returning = True
+    delete_returning = True
+    insert_returning = True
 
     connection_characteristics = (
         default.DefaultDialect.connection_characteristics
@@ -3274,7 +3263,9 @@ class PGDialect(default.DefaultDialect):
         super(PGDialect, self).initialize(connection)
 
         if self.server_version_info <= (8, 2):
-            self.full_returning = self.implicit_returning = False
+            self.delete_returning = (
+                self.update_returning
+            ) = self.insert_returning = False
 
         self.supports_native_enum = self.server_version_info >= (8, 3)
         if not self.supports_native_enum:
diff --git a/lib/sqlalchemy/dialects/postgresql/psycopg.py b/lib/sqlalchemy/dialects/postgresql/psycopg.py
index 7ec26cb4e..90bae61e1 100644
--- a/lib/sqlalchemy/dialects/postgresql/psycopg.py
+++ b/lib/sqlalchemy/dialects/postgresql/psycopg.py
@@ -256,7 +256,7 @@ class PGDialect_psycopg(_PGDialect_common_psycopg):
 
         # PGDialect.initialize() checks server version for <= 8.2 and sets
         # this flag to False if so
-        if not self.full_returning:
+        if not self.insert_returning:
             self.insert_executemany_returning = False
 
         # HSTORE can't be registered until we have a connection so that
diff --git a/lib/sqlalchemy/dialects/postgresql/psycopg2.py b/lib/sqlalchemy/dialects/postgresql/psycopg2.py
index f5d84a5a3..3f4ee2a20 100644
--- a/lib/sqlalchemy/dialects/postgresql/psycopg2.py
+++ b/lib/sqlalchemy/dialects/postgresql/psycopg2.py
@@ -613,7 +613,7 @@ class PGDialect_psycopg2(_PGDialect_common_psycopg):
 
         # PGDialect.initialize() checks server version for <= 8.2 and sets
         # this flag to False if so
-        if not self.full_returning:
+        if not self.insert_returning:
             self.insert_executemany_returning = False
             self.executemany_mode = EXECUTEMANY_PLAIN
 
diff --git a/lib/sqlalchemy/dialects/sqlite/base.py b/lib/sqlalchemy/dialects/sqlite/base.py
index 2ce298436..fdcd1340b 100644
--- a/lib/sqlalchemy/dialects/sqlite/base.py
+++ b/lib/sqlalchemy/dialects/sqlite/base.py
@@ -221,6 +221,46 @@ by *not even emitting BEGIN* until the first write operation.
 
     :ref:`dbapi_autocommit`
 
+INSERT/UPDATE/DELETE...RETURNING
+---------------------------------
+
+The SQLite dialect supports SQLite 3.35's  ``INSERT|UPDATE|DELETE..RETURNING``
+syntax.   ``INSERT..RETURNING`` may be used
+automatically in some cases in order to fetch newly generated identifiers in
+place of the traditional approach of using ``cursor.lastrowid``, however
+``cursor.lastrowid`` is currently still preferred for simple single-statement
+cases for its better performance.
+
+To specify an explicit ``RETURNING`` clause, use the
+:meth:`._UpdateBase.returning` method on a per-statement basis::
+
+    # INSERT..RETURNING
+    result = connection.execute(
+        table.insert().
+        values(name='foo').
+        returning(table.c.col1, table.c.col2)
+    )
+    print(result.all())
+
+    # UPDATE..RETURNING
+    result = connection.execute(
+        table.update().
+        where(table.c.name=='foo').
+        values(name='bar').
+        returning(table.c.col1, table.c.col2)
+    )
+    print(result.all())
+
+    # DELETE..RETURNING
+    result = connection.execute(
+        table.delete().
+        where(table.c.name=='foo').
+        returning(table.c.col1, table.c.col2)
+    )
+    print(result.all())
+
+.. versionadded:: 2.0  Added support for SQLite RETURNING
+
 SAVEPOINT Support
 ----------------------------
 
@@ -1280,6 +1320,19 @@ class SQLiteCompiler(compiler.SQLCompiler):
                 "%s is not a valid extract argument." % extract.field
             ) from err
 
+    def returning_clause(
+        self,
+        stmt,
+        returning_cols,
+        *,
+        populate_result_map,
+        **kw,
+    ):
+        kw["include_table"] = False
+        return super().returning_clause(
+            stmt, returning_cols, populate_result_map=populate_result_map, **kw
+        )
+
     def limit_clause(self, select, **kw):
         text = ""
         if select._limit_clause is not None:
@@ -1372,6 +1425,11 @@ class SQLiteCompiler(compiler.SQLCompiler):
 
         return target_text
 
+    def visit_insert(self, insert_stmt, **kw):
+        if insert_stmt._post_values_clause is not None:
+            kw["disable_implicit_returning"] = True
+        return super().visit_insert(insert_stmt, **kw)
+
     def visit_on_conflict_do_nothing(self, on_conflict, **kw):
 
         target_text = self._on_conflict_target(on_conflict, **kw)
@@ -1831,6 +1889,9 @@ class SQLiteDialect(default.DefaultDialect):
     supports_default_values = True
     supports_default_metavalue = False
 
+    # https://github.com/python/cpython/issues/93421
+    supports_sane_rowcount_returning = False
+
     supports_empty_insert = False
     supports_cast = True
     supports_multivalues_insert = True
@@ -1944,6 +2005,11 @@ class SQLiteDialect(default.DefaultDialect):
                 14,
             )
 
+            if self.dbapi.sqlite_version_info >= (3, 35):
+                self.update_returning = (
+                    self.delete_returning
+                ) = self.insert_returning = True
+
     _isolation_lookup = util.immutabledict(
         {"READ UNCOMMITTED": 1, "SERIALIZABLE": 0}
     )