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, 58 insertions, 27 deletions

diff --git a/lib/sqlalchemy/engine/create.py b/lib/sqlalchemy/engine/create.py
index 68a6b81e2..36119ab24 100644
--- a/lib/sqlalchemy/engine/create.py
+++ b/lib/sqlalchemy/engine/create.py
@@ -57,7 +57,7 @@ def create_engine(
     execution_options: _ExecuteOptions = ...,
     future: Literal[True],
     hide_parameters: bool = ...,
-    implicit_returning: bool = ...,
+    implicit_returning: Literal[True] = ...,
     isolation_level: _IsolationLevel = ...,
     json_deserializer: Callable[..., Any] = ...,
     json_serializer: Callable[..., Any] = ...,
@@ -266,18 +266,12 @@ def create_engine(url: Union[str, "_url.URL"], **kwargs: Any) -> Engine:
             :ref:`dbengine_logging` - further detail on how to configure
             logging.
 
-    :param implicit_returning=True:  Legacy flag that when set to ``False``
-        will disable the use of ``RETURNING`` on supporting backends where it
-        would normally be used to fetch newly generated primary key values for
-        single-row INSERT statements that do not otherwise specify a RETURNING
-        clause.  This behavior applies primarily to the PostgreSQL, Oracle,
-        SQL Server backends.
-
-        .. warning:: this flag originally allowed the "implicit returning"
-           feature to be *enabled* back when it was very new and there was not
-           well-established database support.  In modern SQLAlchemy, this flag
-           should **always be set to True**.  Some SQLAlchemy features will
-           fail to function properly if this flag is set to ``False``.
+    :param implicit_returning=True:  Legacy parameter that may only be set
+        to True. In SQLAlchemy 2.0, this parameter does nothing. In order to
+        disable "implicit returning" for statements invoked by the ORM,
+        configure this on a per-table basis using the
+        :paramref:`.Table.implicit_returning` parameter.
+
 
     :param isolation_level: optional string name of an isolation level
         which will be set on all new connections unconditionally.
diff --git a/lib/sqlalchemy/engine/cursor.py b/lib/sqlalchemy/engine/cursor.py
index ec1e1abe1..7947456af 100644
--- a/lib/sqlalchemy/engine/cursor.py
+++ b/lib/sqlalchemy/engine/cursor.py
@@ -1817,7 +1817,7 @@ class CursorResult(Result[_T]):
 
     def merge(self, *others: Result[Any]) -> MergedResult[Any]:
         merged_result = super().merge(*others)
-        setup_rowcounts = not self._metadata.returns_rows
+        setup_rowcounts = self.context._has_rowcount
         if setup_rowcounts:
             merged_result.rowcount = sum(
                 cast("CursorResult[Any]", result).rowcount
diff --git a/lib/sqlalchemy/engine/default.py b/lib/sqlalchemy/engine/default.py
index bcbe83f3f..6b76601ff 100644
--- a/lib/sqlalchemy/engine/default.py
+++ b/lib/sqlalchemy/engine/default.py
@@ -57,6 +57,7 @@ from ..sql.compiler import DDLCompiler
 from ..sql.compiler import SQLCompiler
 from ..sql.elements import quoted_name
 from ..sql.schema import default_is_scalar
+from ..util.typing import Literal
 
 if typing.TYPE_CHECKING:
     from types import ModuleType
@@ -135,9 +136,11 @@ class DefaultDialect(Dialect):
     preexecute_autoincrement_sequences = False
     supports_identity_columns = False
     postfetch_lastrowid = True
+    favor_returning_over_lastrowid = False
     insert_null_pk_still_autoincrements = False
-    implicit_returning = False
-    full_returning = False
+    update_returning = False
+    delete_returning = False
+    insert_returning = False
     insert_executemany_returning = False
 
     cte_follows_insert = False
@@ -258,7 +261,7 @@ class DefaultDialect(Dialect):
         paramstyle: Optional[_ParamStyle] = None,
         isolation_level: Optional[_IsolationLevel] = None,
         dbapi: Optional[ModuleType] = None,
-        implicit_returning: Optional[bool] = None,
+        implicit_returning: Literal[True] = True,
         supports_native_boolean: Optional[bool] = None,
         max_identifier_length: Optional[int] = None,
         label_length: Optional[int] = None,
@@ -296,8 +299,6 @@ class DefaultDialect(Dialect):
             self.paramstyle = self.dbapi.paramstyle
         else:
             self.paramstyle = self.default_paramstyle
-        if implicit_returning is not None:
-            self.implicit_returning = implicit_returning
         self.positional = self.paramstyle in ("qmark", "format", "numeric")
         self.identifier_preparer = self.preparer(self)
         self._on_connect_isolation_level = isolation_level
@@ -324,6 +325,18 @@ class DefaultDialect(Dialect):
         self.label_length = label_length
         self.compiler_linting = compiler_linting
 
+    @util.deprecated_property(
+        "2.0",
+        "full_returning is deprecated, please use insert_returning, "
+        "update_returning, delete_returning",
+    )
+    def full_returning(self):
+        return (
+            self.insert_returning
+            and self.update_returning
+            and self.delete_returning
+        )
+
     @util.memoized_property
     def loaded_dbapi(self) -> ModuleType:
         if self.dbapi is None:
@@ -771,7 +784,6 @@ class StrCompileDialect(DefaultDialect):
     supports_sequences = True
     sequences_optional = True
     preexecute_autoincrement_sequences = False
-    implicit_returning = False
 
     supports_native_boolean = True
 
@@ -806,6 +818,8 @@ class DefaultExecutionContext(ExecutionContext):
 
     _soft_closed = False
 
+    _has_rowcount = False
+
     # a hook for SQLite's translation of
     # result column names
     # NOTE: pyhive is using this hook, can't remove it :(
@@ -1450,6 +1464,7 @@ class DefaultExecutionContext(ExecutionContext):
             # is testing this, and psycopg will no longer return
             # rowcount after cursor is closed.
             result.rowcount
+            self._has_rowcount = True
 
             row = result.fetchone()
             if row is not None:
@@ -1465,7 +1480,12 @@ class DefaultExecutionContext(ExecutionContext):
             # no results, get rowcount
             # (which requires open cursor on some drivers)
             result.rowcount
+            self._has_rowcount = True
             result._soft_close()
+        elif self.isupdate or self.isdelete:
+            result.rowcount
+            self._has_rowcount = True
+
         return result
 
     @util.memoized_property
@@ -1479,7 +1499,6 @@ class DefaultExecutionContext(ExecutionContext):
         getter = cast(
             SQLCompiler, self.compiled
         )._inserted_primary_key_from_lastrowid_getter
-
         lastrowid = self.get_lastrowid()
         return [getter(lastrowid, self.compiled_parameters[0])]
 
diff --git a/lib/sqlalchemy/engine/interfaces.py b/lib/sqlalchemy/engine/interfaces.py
index 4020af354..cd6efb904 100644
--- a/lib/sqlalchemy/engine/interfaces.py
+++ b/lib/sqlalchemy/engine/interfaces.py
@@ -737,14 +737,32 @@ class Dialect(EventTarget):
       PostgreSQL.
     """
 
-    implicit_returning: bool
-    """For dialects that support RETURNING, indicate RETURNING may be used
-    to fetch newly generated primary key values and other defaults from
-    an INSERT statement automatically.
+    insert_returning: bool
+    """if the dialect supports RETURNING with INSERT
 
-    .. seealso::
+    .. versionadded:: 2.0
+
+    """
+
+    update_returning: bool
+    """if the dialect supports RETURNING with UPDATE
+
+    .. versionadded:: 2.0
+
+    """
+
+    delete_returning: bool
+    """if the dialect supports RETURNING with DELETE
+
+    .. versionadded:: 2.0
+
+    """
+
+    favor_returning_over_lastrowid: bool
+    """for backends that support both a lastrowid and a RETURNING insert
+    strategy, favor RETURNING for simple single-int pk inserts.
 
-        :paramref:`_schema.Table.implicit_returning`
+    cursor.lastrowid tends to be more performant on most backends.
 
     """