Render NULL for bindparam w/ None value/literal_binds, warn

Adjusted the "literal_binds" feature of :class:`_sql.Compiler` to render
NULL for a bound parameter that has ``None`` as the value, either
explicitly passed or omitted. The previous error message "bind parameter
without a renderable value" is removed, and a missing or ``None`` value
will now render NULL in all cases. Previously, rendering of NULL was
starting to happen for DML statements due to internal refactorings, but was
not explicitly part of test coverage, which it now is.

While no error is raised, when the context is within that of a column
comparison, and the operator is not "IS"/"IS NOT", a warning is emitted
that this is not generally useful from a SQL perspective.

Fixes: #5888
Change-Id: Id5939d8dbfb1156a9f8a7f7e76cf18327155331a

1 files changed, 68 insertions, 0 deletions

diff --git a/test/sql/test_compiler.py b/test/sql/test_compiler.py
index fea75d679..2a543aa61 100644
--- a/test/sql/test_compiler.py
+++ b/test/sql/test_compiler.py
@@ -4171,6 +4171,74 @@ class BindParameterTest(AssertsCompiledSQL, fixtures.TestBase):
             checkparams={"foo_1": 1, "foo_2": 2, "foo_3": 3},
         )
 
+    @testing.combinations(
+        (
+            select(table1.c.myid).where(
+                table1.c.myid == bindparam("x", value=None)
+            ),
+            "SELECT mytable.myid FROM mytable WHERE mytable.myid = NULL",
+            True,
+            None,
+        ),
+        (
+            select(table1.c.myid).where(table1.c.myid == None),
+            "SELECT mytable.myid FROM mytable WHERE mytable.myid IS NULL",
+            False,
+            None,
+        ),
+        (
+            select(table1.c.myid, None),
+            "SELECT mytable.myid, NULL AS anon_1 FROM mytable",
+            False,
+            None,
+        ),
+        (
+            select(table1.c.myid).where(
+                table1.c.myid.is_(bindparam("x", value=None))
+            ),
+            "SELECT mytable.myid FROM mytable WHERE mytable.myid IS NULL",
+            False,
+            None,
+        ),
+        (
+            # as of SQLAlchemy 1.4, values like these are considered to be
+            # SQL expressions up front, so it is coerced to null()
+            # immediately and no bindparam() is created
+            table1.insert().values({"myid": None}),
+            "INSERT INTO mytable (myid) VALUES (NULL)",
+            False,
+            None,
+        ),
+        (table1.insert(), "INSERT INTO mytable DEFAULT VALUES", False, {}),
+        (
+            table1.update().values({"myid": None}),
+            "UPDATE mytable SET myid=NULL",
+            False,
+            None,
+        ),
+        (
+            table1.update()
+            .where(table1.c.myid == bindparam("x"))
+            .values({"myid": None}),
+            "UPDATE mytable SET myid=NULL WHERE mytable.myid = NULL",
+            True,
+            None,
+        ),
+    )
+    def test_render_nulls_literal_binds(self, stmt, expected, warns, params):
+        if warns:
+            with testing.expect_warnings(
+                r"Bound parameter '.*?' rendering literal "
+                "NULL in a SQL expression"
+            ):
+                self.assert_compile(
+                    stmt, expected, literal_binds=True, params=params
+                )
+        else:
+            self.assert_compile(
+                stmt, expected, literal_binds=True, params=params
+            )
+
 
 class UnsupportedTest(fixtures.TestBase):
     def test_unsupported_element_str_visit_name(self):