fixes for usage of the null() and similar constants

Adjusted the "column disambiguation" logic that's new in 1.4, where the
same expression repeated gets an "extra anonymous" label, so that the logic
more aggressively deduplicates those labels when the repeated element
is the same Python expression object each time, as occurs in cases like
when using "singleton" values like :func:`_sql.null`.  This is based on
the observation that at least some databases (e.g. MySQL, but not SQLite)
will raise an error if the same label is repeated inside of a subquery.

Related to :ticket:`7153`, fixed an issue where result column lookups
would fail for "adapted" SELECT statements that selected for
"constant" value expressions most typically the NULL expression,
as would occur in such places as joined eager loading in conjunction
with limit/offset.  This was overall a regression due to issue
:ticket:`6259` which removed all "adaption" for constants like NULL,
"true", and "false", but this broke the case where the same adaption
logic were used to match the constant to a labeled expression referring
to the constant in a subquery.

Fixes: #7153
Fixes: #7154
Change-Id: I43823343721b9e70524ea3f5e8f39dd543a3e92b

1 files changed, 28 insertions, 3 deletions

diff --git a/lib/sqlalchemy/sql/util.py b/lib/sqlalchemy/sql/util.py
index da5e31655..7fcb45709 100644
--- a/lib/sqlalchemy/sql/util.py
+++ b/lib/sqlalchemy/sql/util.py
@@ -847,7 +847,7 @@ class ClauseAdapter(visitors.ReplacingExternalTraversal):
         return newcol
 
     @util.preload_module("sqlalchemy.sql.functions")
-    def replace(self, col):
+    def replace(self, col, _include_singleton_constants=False):
         functions = util.preloaded.sql_functions
 
         if isinstance(col, FromClause) and not isinstance(
@@ -881,6 +881,14 @@ class ClauseAdapter(visitors.ReplacingExternalTraversal):
 
         elif not isinstance(col, ColumnElement):
             return None
+        elif not _include_singleton_constants and col._is_singleton_constant:
+            # dont swap out NULL, TRUE, FALSE for a label name
+            # in a SQL statement that's being rewritten,
+            # leave them as the constant.  This is first noted in #6259,
+            # however the logic to check this moved here as of #7154 so that
+            # it is made specific to SQL rewriting and not all column
+            # correspondence
+            return None
 
         if "adapt_column" in col._annotations:
             col = col._annotations["adapt_column"]
@@ -1001,8 +1009,25 @@ class ColumnAdapter(ClauseAdapter):
         return newcol
 
     def _locate_col(self, col):
-
-        c = ClauseAdapter.traverse(self, col)
+        # both replace and traverse() are overly complicated for what
+        # we are doing here and we would do better to have an inlined
+        # version that doesn't build up as much overhead.  the issue is that
+        # sometimes the lookup does in fact have to adapt the insides of
+        # say a labeled scalar subquery.   However, if the object is an
+        # Immutable, i.e. Column objects, we can skip the "clone" /
+        # "copy internals" part since those will be no-ops in any case.
+        # additionally we want to catch singleton objects null/true/false
+        # and make sure they are adapted as well here.
+
+        if col._is_immutable:
+            for vis in self.visitor_iterator:
+                c = vis.replace(col, _include_singleton_constants=True)
+                if c is not None:
+                    break
+            else:
+                c = col
+        else:
+            c = ClauseAdapter.traverse(self, col)
 
         if self._wrap:
             c2 = self._wrap._locate_col(c)