limit None->null coercion to not occur with crud

Fixed issue where type-specific bound parameter handlers would not be
called upon in the case of using the :meth:`_sql.Insert.values` method with
the Python ``None`` value; in particular, this would be noticed when using
the :class:`_types.JSON` datatype as well as related PostgreSQL specific
types such as :class:`_postgresql.JSONB` which would fail to encode the
Python ``None`` value into JSON null, however the issue was generalized to
any bound parameter handler in conjunction with this specific method of
:class:`_sql.Insert`.

The issue with coercions forcing out ``null()`` may still impact
SQL expression usage as well; the change here is limited to crud
as the behavior there is relevant to some use cases, which may
need to be evaluated separately.

Fixes: #6770
Change-Id: If53edad811b37dada7578a89daf395628db058a6

2 files changed, 16 insertions, 1 deletions

diff --git a/lib/sqlalchemy/sql/coercions.py b/lib/sqlalchemy/sql/coercions.py
index 16a68c8ff..e21f4a9a5 100644
--- a/lib/sqlalchemy/sql/coercions.py
+++ b/lib/sqlalchemy/sql/coercions.py
@@ -464,7 +464,14 @@ class ExpressionElementImpl(_ColumnCoercions, RoleImpl):
     def _literal_coercion(
         self, element, name=None, type_=None, argname=None, is_crud=False, **kw
     ):
-        if element is None:
+        if (
+            element is None
+            and not is_crud
+            and (type_ is None or not type_.should_evaluate_none)
+        ):
+            # TODO: there's no test coverage now for the
+            # "should_evaluate_none" part of this, as outside of "crud" this
+            # codepath is not normally used except in some special cases
             return elements.Null()
         else:
             try:
diff --git a/lib/sqlalchemy/sql/sqltypes.py b/lib/sqlalchemy/sql/sqltypes.py
index cc5f9a6b7..1b05465c9 100644
--- a/lib/sqlalchemy/sql/sqltypes.py
+++ b/lib/sqlalchemy/sql/sqltypes.py
@@ -2371,6 +2371,14 @@ class JSON(Indexable, TypeEngine):
               :paramref:`_schema.Column.server_default`; a value of ``None``
               passed for these parameters means "no default present".
 
+              Additionally, when used in SQL comparison expressions, the
+              Python value ``None`` continues to refer to SQL null, and not
+              JSON NULL.  The :paramref:`_types.JSON.none_as_null` flag refers
+              explicitly to the **persistence** of the value within an
+              INSERT or UPDATE statement.   The :attr:`_types.JSON.NULL`
+              value should be used for SQL expressions that wish to compare to
+              JSON null.
+
          .. seealso::
 
               :attr:`.types.JSON.NULL`