Tighten rules for order_by(Label) resolution

- Fixed bug originally introduced in 0.9 via :ticket:`1068` where
order_by(<some Label()>) would order by the label name based on name
alone, that is, even if the labeled expression were not at all the same
expression otherwise present, implicitly or explicitly, in the
selectable.  The logic that orders by label now ensures that the
labeled expression is related to the one that resolves to that name
before ordering by the label name; additionally, the name has to
resolve to an actual label explicit in the expression elsewhere, not
just a column name.  This logic is carefully kept separate from the
order by(textual name) feature that has a slightly different purpose.

Change-Id: I44fc36dab34380cc238c1e79ecbe23f1628d588a
Fixes: #3882

1 files changed, 50 insertions, 0 deletions

diff --git a/test/sql/test_compiler.py b/test/sql/test_compiler.py
index a85786bed..38ca09c0a 100644
--- a/test/sql/test_compiler.py
+++ b/test/sql/test_compiler.py
@@ -952,6 +952,56 @@ class SelectTest(fixtures.TestBase, AssertsCompiledSQL):
             dialect=dialect
         )
 
+        # expression isn't actually the same thing (even though label is)
+        self.assert_compile(
+            select([lab1, lab2]).order_by(
+                table1.c.myid.label('foo'),
+                desc(table1.c.name.label('bar'))
+            ),
+            "SELECT mytable.myid + :myid_1 AS foo, "
+            "somefunc(mytable.name) AS bar FROM mytable "
+            "ORDER BY mytable.myid, mytable.name DESC",
+            dialect=dialect
+        )
+
+        # it's also an exact match, not aliased etc.
+        self.assert_compile(
+            select([lab1, lab2]).order_by(
+                desc(table1.alias().c.name.label('bar'))
+            ),
+            "SELECT mytable.myid + :myid_1 AS foo, "
+            "somefunc(mytable.name) AS bar FROM mytable "
+            "ORDER BY mytable_1.name DESC",
+            dialect=dialect
+        )
+
+        # but! it's based on lineage
+        lab2_lineage = lab2.element._clone()
+        self.assert_compile(
+            select([lab1, lab2]).order_by(
+                desc(lab2_lineage.label('bar'))
+            ),
+            "SELECT mytable.myid + :myid_1 AS foo, "
+            "somefunc(mytable.name) AS bar FROM mytable "
+            "ORDER BY bar DESC",
+            dialect=dialect
+        )
+
+        # here, 'name' is implicitly available, but w/ #3882 we don't
+        # want to render a name that isn't specifically a Label elsewhere
+        # in the query
+        self.assert_compile(
+            select([table1.c.myid]).order_by(table1.c.name.label('name')),
+            "SELECT mytable.myid FROM mytable ORDER BY mytable.name"
+        )
+
+        # as well as if it doesn't match
+        self.assert_compile(
+            select([table1.c.myid]).order_by(
+                func.lower(table1.c.name).label('name')),
+            "SELECT mytable.myid FROM mytable ORDER BY lower(mytable.name)"
+        )
+
     def test_order_by_labels_disabled(self):
         lab1 = (table1.c.myid + 12).label('foo')
         lab2 = func.somefunc(table1.c.name).label('bar')