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

4 files changed, 147 insertions, 24 deletions

diff --git a/test/sql/test_compiler.py b/test/sql/test_compiler.py
index 15b13caaa..419d14ce7 100644
--- a/test/sql/test_compiler.py
+++ b/test/sql/test_compiler.py
@@ -78,6 +78,7 @@ from sqlalchemy.sql import operators
 from sqlalchemy.sql import table
 from sqlalchemy.sql import util as sql_util
 from sqlalchemy.sql.elements import BooleanClauseList
+from sqlalchemy.sql.elements import ColumnElement
 from sqlalchemy.sql.expression import ClauseElement
 from sqlalchemy.sql.expression import ClauseList
 from sqlalchemy.sql.expression import HasPrefixes
@@ -88,6 +89,7 @@ from sqlalchemy.testing import assert_raises_message
 from sqlalchemy.testing import AssertsCompiledSQL
 from sqlalchemy.testing import eq_
 from sqlalchemy.testing import eq_ignore_whitespace
+from sqlalchemy.testing import expect_raises
 from sqlalchemy.testing import expect_raises_message
 from sqlalchemy.testing import fixtures
 from sqlalchemy.testing import is_
@@ -839,9 +841,9 @@ class SelectTest(fixtures.TestBase, AssertsCompiledSQL):
             "foo_bar.id AS foo_bar_id_1, "  # 2. 1st foo_bar.id, disamb from 1
             "foo.bar_id AS foo_bar_id__1, "  # 3. 2nd foo.bar_id, dedupe from 1
             "foo.id AS foo_id__1, "
-            "foo.bar_id AS foo_bar_id__1, "  # 4. 3rd foo.bar_id, same as 3
-            "foo_bar.id AS foo_bar_id__2, "  # 5. 2nd foo_bar.id
-            "foo_bar.id AS foo_bar_id__2 "  # 6. 3rd foo_bar.id, same as 5
+            "foo.bar_id AS foo_bar_id__2, "  # 4. 3rd foo.bar_id, dedupe again
+            "foo_bar.id AS foo_bar_id__3, "  # 5. 2nd foo_bar.id
+            "foo_bar.id AS foo_bar_id__4 "  # 6. 3rd foo_bar.id, dedupe again
             "FROM foo, foo_bar",
         )
         eq_(
@@ -878,9 +880,9 @@ class SelectTest(fixtures.TestBase, AssertsCompiledSQL):
             "foo_bar.id AS foo_bar_id_1, "  # 2. 1st foo_bar.id, disamb from 1
             "foo.bar_id AS foo_bar_id__1, "  # 3. 2nd foo.bar_id, dedupe from 1
             "foo.id AS foo_id__1, "
-            "foo.bar_id AS foo_bar_id__1, "  # 4. 3rd foo.bar_id, same as 3
-            "foo_bar.id AS foo_bar_id__2, "  # 5. 2nd foo_bar.id
-            "foo_bar.id AS foo_bar_id__2 "  # 6. 3rd foo_bar.id, same as 5
+            "foo.bar_id AS foo_bar_id__2, "  # 4. 3rd foo.bar_id, dedupe again
+            "foo_bar.id AS foo_bar_id__3, "  # 5. 2nd foo_bar.id
+            "foo_bar.id AS foo_bar_id__4 "  # 6. 3rd foo_bar.id, dedupe again
             "FROM foo, foo_bar"
             ") AS anon_1",
         )
@@ -952,9 +954,9 @@ class SelectTest(fixtures.TestBase, AssertsCompiledSQL):
             "foo_bar.id AS foo_bar_id_1, "  # 2. 1st foo_bar.id, disamb from 1
             "foo.bar_id AS foo_bar_id__1, "  # 3. 2nd foo.bar_id, dedupe from 1
             "foo.id AS foo_id__1, "
-            "foo.bar_id AS foo_bar_id__1, "  # 4. 3rd foo.bar_id, same as 3
-            "foo_bar.id AS foo_bar_id__2, "  # 5. 2nd foo_bar.id
-            "foo_bar.id AS foo_bar_id__2 "  # 6. 3rd foo_bar.id, same as 5
+            "foo.bar_id AS foo_bar_id__2, "  # 4. 3rd foo.bar_id, dedupe again
+            "foo_bar.id AS foo_bar_id__3, "  # 5. 2nd foo_bar.id
+            "foo_bar.id AS foo_bar_id__4 "  # 6. 3rd foo_bar.id, dedupe again
             "FROM foo, foo_bar",
         )
 
@@ -965,7 +967,7 @@ class SelectTest(fixtures.TestBase, AssertsCompiledSQL):
                 LABEL_STYLE_TABLENAME_PLUS_COL
             ),
             "SELECT t.a AS t_a, t.a AS t_a__1, t.b AS t_b, "
-            "t.a AS t_a__1 FROM t",
+            "t.a AS t_a__2 FROM t",
         )
 
     def test_dupe_columns_use_labels_derived_selectable(self):
@@ -979,7 +981,7 @@ class SelectTest(fixtures.TestBase, AssertsCompiledSQL):
         self.assert_compile(
             select(stmt).set_label_style(LABEL_STYLE_NONE),
             "SELECT anon_1.t_a, anon_1.t_a, anon_1.t_b, anon_1.t_a FROM "
-            "(SELECT t.a AS t_a, t.a AS t_a__1, t.b AS t_b, t.a AS t_a__1 "
+            "(SELECT t.a AS t_a, t.a AS t_a__1, t.b AS t_b, t.a AS t_a__2 "
             "FROM t) AS anon_1",
         )
 
@@ -992,7 +994,7 @@ class SelectTest(fixtures.TestBase, AssertsCompiledSQL):
                 LABEL_STYLE_TABLENAME_PLUS_COL
             ),
             "SELECT t.a AS t_a, t.a AS t_a__1, t.b AS t_b, "
-            "t.a AS t_a__1 FROM t",
+            "t.a AS t_a__2 FROM t",
         )
 
         self.assert_compile(
@@ -1000,7 +1002,7 @@ class SelectTest(fixtures.TestBase, AssertsCompiledSQL):
                 LABEL_STYLE_TABLENAME_PLUS_COL
             ),
             "SELECT t.a AS t_a, t.a AS t_a__1, t.b AS t_b, "
-            "t.a AS t_a__1 FROM t",
+            "t.a AS t_a__2 FROM t",
         )
 
         self.assert_compile(
@@ -1008,7 +1010,7 @@ class SelectTest(fixtures.TestBase, AssertsCompiledSQL):
                 LABEL_STYLE_TABLENAME_PLUS_COL
             ),
             "SELECT t.a AS t_a, t.a AS t_a__1, t.b AS t_b, "
-            "t.a AS t_a__1 FROM t",
+            "t.a AS t_a__2 FROM t",
         )
 
     def test_dupe_columns_use_labels_derived_selectable_mix_annotations(self):
@@ -1023,7 +1025,7 @@ class SelectTest(fixtures.TestBase, AssertsCompiledSQL):
         self.assert_compile(
             select(stmt).set_label_style(LABEL_STYLE_NONE),
             "SELECT anon_1.t_a, anon_1.t_a, anon_1.t_b, anon_1.t_a FROM "
-            "(SELECT t.a AS t_a, t.a AS t_a__1, t.b AS t_b, t.a AS t_a__1 "
+            "(SELECT t.a AS t_a, t.a AS t_a__1, t.b AS t_b, t.a AS t_a__2 "
             "FROM t) AS anon_1",
         )
 
@@ -1044,8 +1046,8 @@ class SelectTest(fixtures.TestBase, AssertsCompiledSQL):
         self.assert_compile(
             stmt,
             "SELECT foo.bar_id AS foo_bar_id, foo_bar.id AS foo_bar_id_1, "
-            "foo_bar.id AS foo_bar_id__1, foo_bar.id AS foo_bar_id__1, "
-            "foo_bar.id AS foo_bar_id__1 FROM foo, foo_bar",
+            "foo_bar.id AS foo_bar_id__1, foo_bar.id AS foo_bar_id__2, "
+            "foo_bar.id AS foo_bar_id__3 FROM foo, foo_bar",
         )
 
     def test_dupe_columns_use_labels_from_anon(self):
@@ -1060,7 +1062,7 @@ class SelectTest(fixtures.TestBase, AssertsCompiledSQL):
                 LABEL_STYLE_TABLENAME_PLUS_COL
             ),
             "SELECT t_1.a AS t_1_a, t_1.a AS t_1_a__1, t_1.b AS t_1_b, "
-            "t_1.a AS t_1_a__1 "
+            "t_1.a AS t_1_a__2 "
             "FROM t AS t_1",
         )
 
@@ -2536,6 +2538,62 @@ class SelectTest(fixtures.TestBase, AssertsCompiledSQL):
             "WHERE mytable.myid = myothertable.otherid ORDER BY myid",
         )
 
+    def test_deduping_hash_algo(self):
+        """related to #7153.
+
+        testing new feature "add_hash" of _anon_label which adds an additional
+        integer value as part of what the anon label is deduplicated upon.
+
+        """
+
+        class Thing(ColumnElement):
+            def __init__(self, hash_):
+                self._hash = hash_
+
+            def __hash__(self):
+                return self._hash
+
+        t1 = Thing(10)
+        t2 = Thing(11)
+
+        # this is the collision case.  therefore we assert that this
+        # add_hash  has to be below 16 bits.
+        # eq_(
+        #     t1._anon_label('hi', add_hash=65537),
+        #     t2._anon_label('hi', add_hash=1)
+        # )
+        with expect_raises(AssertionError):
+            t1._anon_label("hi", add_hash=65536)
+
+        for i in range(50):
+            ne_(
+                t1._anon_label("hi", add_hash=i),
+                t2._anon_label("hi", add_hash=1),
+            )
+
+    def test_deduping_unique_across_selects(self):
+        """related to #7153
+
+        looking to see that dedupe anon labels use a unique hash not only
+        within each statement but across multiple statements.
+
+        """
+
+        s1 = select(null(), null())
+
+        s2 = select(true(), true())
+
+        s3 = union(s1, s2)
+
+        self.assert_compile(
+            s3,
+            "SELECT NULL AS anon_1, NULL AS anon__1 " "UNION "
+            # without the feature tested in test_deduping_hash_algo we'd get
+            # "SELECT true AS anon_2, true AS anon__1",
+            "SELECT true AS anon_2, true AS anon__2",
+            dialect="default_enhanced",
+        )
+
     def test_compound_grouping(self):
         s = select(column("foo"), column("bar")).select_from(text("bat"))
 
@@ -3602,7 +3660,7 @@ class BindParameterTest(AssertsCompiledSQL, fixtures.TestBase):
         # the label name, but that's a different issue
         self.assert_compile(
             stmt1,
-            "SELECT :foo_1 AS anon_1, :foo_1 AS anon__1, :foo_1 AS anon__1",
+            "SELECT :foo_1 AS anon_1, :foo_1 AS anon__1, :foo_1 AS anon__2",
         )
 
     def _test_binds_no_hash_collision(self):
diff --git a/test/sql/test_cte.py b/test/sql/test_cte.py
index 2d658338f..5d24adff9 100644
--- a/test/sql/test_cte.py
+++ b/test/sql/test_cte.py
@@ -633,8 +633,8 @@ class CTETest(fixtures.TestBase, AssertsCompiledSQL):
             "WITH RECURSIVE anon_1(foo_id, foo_bar_id, foo_bar_id_1) AS "
             "(SELECT foo.id AS foo_id, foo.bar_id AS foo_bar_id, "
             "foo_bar.id AS foo_bar_id_1, foo.bar_id AS foo_bar_id__1, "
-            "foo.id AS foo_id__1, foo.bar_id AS foo_bar_id__1, "
-            "foo_bar.id AS foo_bar_id__2, foo_bar.id AS foo_bar_id__2 "
+            "foo.id AS foo_id__1, foo.bar_id AS foo_bar_id__2, "
+            "foo_bar.id AS foo_bar_id__3, foo_bar.id AS foo_bar_id__4 "
             "FROM foo, foo_bar) "
             "SELECT anon_1.foo_id, anon_1.foo_bar_id, anon_1.foo_bar_id_1, "
             "anon_1.foo_bar_id AS foo_bar_id_2, anon_1.foo_id AS foo_id_1, "
diff --git a/test/sql/test_resultset.py b/test/sql/test_resultset.py
index 936d0d9db..bf912bd25 100644
--- a/test/sql/test_resultset.py
+++ b/test/sql/test_resultset.py
@@ -1957,9 +1957,9 @@ class KeyTargetingTest(fixtures.TablesTest):
                 "keyed2_a",
                 "keyed3_a",
                 "keyed2_a__1",
-                "keyed2_a__1",
-                "keyed3_a__1",
+                "keyed2_a__2",
                 "keyed3_a__1",
+                "keyed3_a__2",
                 "keyed3_d",
                 "keyed3_d__1",
             ],
diff --git a/test/sql/test_selectable.py b/test/sql/test_selectable.py
index 2157b5c71..e68e98a3c 100644
--- a/test/sql/test_selectable.py
+++ b/test/sql/test_selectable.py
@@ -6,6 +6,7 @@ from sqlalchemy import cast
 from sqlalchemy import Column
 from sqlalchemy import exc
 from sqlalchemy import exists
+from sqlalchemy import false
 from sqlalchemy import ForeignKey
 from sqlalchemy import func
 from sqlalchemy import Integer
@@ -22,6 +23,7 @@ from sqlalchemy import String
 from sqlalchemy import Table
 from sqlalchemy import testing
 from sqlalchemy import text
+from sqlalchemy import true
 from sqlalchemy import type_coerce
 from sqlalchemy import TypeDecorator
 from sqlalchemy import union
@@ -513,6 +515,21 @@ class SelectableTest(
         eq_(list(stmt.c.keys()), ["q"])
         eq_(list(stmt2.c.keys()), ["q", "p"])
 
+    @testing.combinations(
+        func.now(), null(), true(), false(), literal_column("10"), column("x")
+    )
+    def test_const_object_correspondence(self, c):
+        """test #7154"""
+
+        stmt = select(c).subquery()
+
+        stmt2 = select(stmt)
+
+        is_(
+            stmt2.selected_columns.corresponding_column(c),
+            stmt2.selected_columns[0],
+        )
+
     def test_append_column_after_visitor_replace(self):
         # test for a supported idiom that matches the deprecated / removed
         # replace_selectable method
@@ -3180,7 +3197,7 @@ class ReprTest(fixtures.TestBase):
             repr(obj)
 
 
-class WithLabelsTest(fixtures.TestBase):
+class WithLabelsTest(AssertsCompiledSQL, fixtures.TestBase):
     def _assert_result_keys(self, s, keys):
         compiled = s.compile()
 
@@ -3266,6 +3283,54 @@ class WithLabelsTest(fixtures.TestBase):
         eq_(list(sel.subquery().c.keys()), ["t1_x", "t1_y", "t1_x_1"])
         self._assert_result_keys(sel, ["t1_x__1", "t1_x", "t1_y"])
 
+    def _columns_repeated_identity(self):
+        m = MetaData()
+        t1 = Table("t1", m, Column("x", Integer), Column("y", Integer))
+        return select(t1.c.x, t1.c.y, t1.c.x, t1.c.x, t1.c.x).set_label_style(
+            LABEL_STYLE_NONE
+        )
+
+    def _anon_columns_repeated_identity_one(self):
+        m = MetaData()
+        t1 = Table("t1", m, Column("x", Integer), Column("y", Integer))
+        return select(t1.c.x, null(), null(), null()).set_label_style(
+            LABEL_STYLE_NONE
+        )
+
+    def _anon_columns_repeated_identity_two(self):
+        fn = func.now()
+        return select(fn, fn, fn, fn).set_label_style(LABEL_STYLE_NONE)
+
+    def test_columns_repeated_identity_disambiguate(self):
+        """test #7153"""
+        sel = self._columns_repeated_identity().set_label_style(
+            LABEL_STYLE_DISAMBIGUATE_ONLY
+        )
+
+        self.assert_compile(
+            sel,
+            "SELECT t1.x, t1.y, t1.x AS x__1, t1.x AS x__2, "
+            "t1.x AS x__3 FROM t1",
+        )
+
+    def test_columns_repeated_identity_subquery_disambiguate(self):
+        """test #7153"""
+        sel = self._columns_repeated_identity()
+
+        stmt = select(sel.subquery()).set_label_style(
+            LABEL_STYLE_DISAMBIGUATE_ONLY
+        )
+
+        # databases like MySQL won't allow the subquery to have repeated labels
+        # even if we don't try to access them
+        self.assert_compile(
+            stmt,
+            "SELECT anon_1.x, anon_1.y, anon_1.x AS x_1, anon_1.x AS x_2, "
+            "anon_1.x AS x_3 FROM "
+            "(SELECT t1.x AS x, t1.y AS y, t1.x AS x__1, t1.x AS x__2, "
+            "t1.x AS x__3 FROM t1) AS anon_1",
+        )
+
     def _labels_overlap(self):
         m = MetaData()
         t1 = Table("t", m, Column("x_id", Integer))