Label simple column transformations as the column name

Additional logic has been added such that certain SQL expressions which
typically wrap a single database column will use the name of that column as
their "anonymous label" name within a SELECT statement, potentially making
key-based lookups in result tuples more intutive.   The primary example of
this is that of a CAST expression, e.g. ``CAST(table.colname AS INTEGER)``,
which will export its default name as "colname", rather than the usual
"anon_1" label, that is, ``CAST(table.colname AS INTEGER) AS colname``.
If the inner expression doesn't have a name, then the previous "anonymous
label" logic is used.  When using SELECT statements that make use of
:meth:`.Select.apply_labels`, such as those emitted by the ORM, the
labeling logic will produce ``<tablename>_<inner column name>`` in the same
was as if the column were named alone.   The logic applies right now to the
:func:`.cast` and :func:`.type_coerce` constructs as well as some
single-element boolean expressions.

Fixes: #4449
Change-Id: Ie3b73470e3bea53f2386cd86514cdc556491564e

4 files changed, 198 insertions, 8 deletions

diff --git a/test/sql/test_compiler.py b/test/sql/test_compiler.py
index a2153082b..03e18e921 100644
--- a/test/sql/test_compiler.py
+++ b/test/sql/test_compiler.py
@@ -2632,14 +2632,14 @@ class SelectTest(fixtures.TestBase, AssertsCompiledSQL):
                     str(sel),
                     "SELECT casttest.id, casttest.v1, casttest.v2, "
                     "casttest.ts, "
-                    "CAST(casttest.v1 AS DECIMAL) AS anon_1 \nFROM casttest",
+                    "CAST(casttest.v1 AS DECIMAL) AS v1 \nFROM casttest",
                 )
             else:
                 eq_(
                     str(sel),
                     "SELECT casttest.id, casttest.v1, casttest.v2, "
                     "casttest.ts, CAST(casttest.v1 AS NUMERIC) AS "
-                    "anon_1 \nFROM casttest",
+                    "v1 \nFROM casttest",
                 )
 
         # first test with PostgreSQL engine
@@ -3033,7 +3033,7 @@ class SelectTest(fixtures.TestBase, AssertsCompiledSQL):
                 exprs[2],
                 str(exprs[2]),
                 "CAST(mytable.name AS NUMERIC)",
-                "anon_1",
+                "name",  # due to [ticket:4449]
             ),
             (t1.c.col1, "col1", "mytable.col1", None),
             (
@@ -3350,7 +3350,7 @@ class StringifySpecialTest(fixtures.TestBase):
 
         eq_ignore_whitespace(
             str(stmt),
-            "SELECT CAST(mytable.myid AS MyType) AS anon_1 FROM mytable",
+            "SELECT CAST(mytable.myid AS MyType) AS myid FROM mytable",
         )
 
     def test_within_group(self):
@@ -4398,7 +4398,7 @@ class ResultMapTest(fixtures.TestBase):
     def test_label_plus_element(self):
         t = Table("t", MetaData(), Column("a", Integer))
         l1 = t.c.a.label("bar")
-        tc = type_coerce(t.c.a, String)
+        tc = type_coerce(t.c.a + "str", String)
         stmt = select([t.c.a, l1, tc])
         comp = stmt.compile()
         tc_anon_label = comp._create_result_map()["anon_1"][1][0]
@@ -4408,7 +4408,7 @@ class ResultMapTest(fixtures.TestBase):
                 "a": ("a", (t.c.a, "a", "a"), t.c.a.type),
                 "bar": ("bar", (l1, "bar"), l1.type),
                 "anon_1": (
-                    "%%(%d anon)s" % id(tc),
+                    tc.anon_label,
                     (tc_anon_label, "anon_1", tc),
                     tc.type,
                 ),
diff --git a/test/sql/test_labels.py b/test/sql/test_labels.py
index 901f941be..9c14bdbce 100644
--- a/test/sql/test_labels.py
+++ b/test/sql/test_labels.py
@@ -1,13 +1,22 @@
 from sqlalchemy import bindparam
+from sqlalchemy import Boolean
+from sqlalchemy import cast
 from sqlalchemy import exc as exceptions
 from sqlalchemy import Integer
 from sqlalchemy import MetaData
 from sqlalchemy import or_
 from sqlalchemy import select
+from sqlalchemy import String
+from sqlalchemy import type_coerce
 from sqlalchemy.engine import default
+from sqlalchemy.ext.compiler import compiles
+from sqlalchemy.sql import coercions
 from sqlalchemy.sql import column
+from sqlalchemy.sql import roles
 from sqlalchemy.sql import table
 from sqlalchemy.sql.elements import _truncated_label
+from sqlalchemy.sql.elements import ColumnElement
+from sqlalchemy.sql.elements import WrapsColumnExpression
 from sqlalchemy.testing import assert_raises
 from sqlalchemy.testing import AssertsCompiledSQL
 from sqlalchemy.testing import engines
@@ -629,3 +638,184 @@ class LabelLengthTest(fixtures.TestBase, AssertsCompiledSQL):
             set(compiled._create_result_map()),
             set(["tablename_columnn_1", "tablename_columnn_2"]),
         )
+
+
+class ColExprLabelTest(fixtures.TestBase, AssertsCompiledSQL):
+    """Test the :class:`.WrapsColumnExpression` mixin, which provides
+    auto-labels that match a named expression
+
+    """
+
+    __dialect__ = "default"
+
+    table1 = table("some_table", column("name"), column("value"))
+
+    def _fixture(self):
+        class SomeColThing(WrapsColumnExpression, ColumnElement):
+            def __init__(self, expression):
+                self.clause = coercions.expect(
+                    roles.ExpressionElementRole, expression
+                )
+
+            @property
+            def wrapped_column_expression(self):
+                return self.clause
+
+        @compiles(SomeColThing)
+        def process(element, compiler, **kw):
+            return "SOME_COL_THING(%s)" % compiler.process(
+                element.clause, **kw
+            )
+
+        return SomeColThing
+
+    def test_column_auto_label_dupes(self):
+        expr = self._fixture()
+        table1 = self.table1
+
+        self.assert_compile(
+            select(
+                [
+                    table1.c.name,
+                    table1.c.name,
+                    expr(table1.c.name),
+                    expr(table1.c.name),
+                ]
+            ),
+            "SELECT some_table.name, some_table.name, "
+            "SOME_COL_THING(some_table.name) AS name, "
+            "SOME_COL_THING(some_table.name) AS name FROM some_table",
+        )
+
+    def test_anon_expression_fallback(self):
+        expr = self._fixture()
+        table1 = self.table1
+
+        self.assert_compile(
+            select([table1.c.name + "foo", expr(table1.c.name + "foo")]),
+            "SELECT some_table.name || :name_1 AS anon_1, "
+            "SOME_COL_THING(some_table.name || :name_2) AS anon_2 "
+            "FROM some_table",
+        )
+
+    def test_anon_expression_fallback_use_labels(self):
+        expr = self._fixture()
+        table1 = self.table1
+
+        self.assert_compile(
+            select(
+                [table1.c.name + "foo", expr(table1.c.name + "foo")]
+            ).apply_labels(),
+            "SELECT some_table.name || :name_1 AS anon_1, "
+            "SOME_COL_THING(some_table.name || :name_2) AS anon_2 "
+            "FROM some_table",
+        )
+
+    def test_label_auto_label(self):
+        expr = self._fixture()
+        table1 = self.table1
+
+        self.assert_compile(
+            select(
+                [
+                    expr(table1.c.name.label("foo")),
+                    table1.c.name.label("bar"),
+                    table1.c.value,
+                ]
+            ),
+            "SELECT SOME_COL_THING(some_table.name) AS foo, "
+            "some_table.name AS bar, some_table.value FROM some_table",
+        )
+
+    def test_cast_auto_label(self):
+        table1 = self.table1
+
+        self.assert_compile(
+            select(
+                [
+                    cast(table1.c.name, Integer),
+                    cast(table1.c.name, String),
+                    table1.c.name,
+                ]
+            ),
+            "SELECT CAST(some_table.name AS INTEGER) AS name, "
+            "CAST(some_table.name AS VARCHAR) AS name, "
+            "some_table.name FROM some_table",
+        )
+
+    def test_type_coerce_auto_label(self):
+        table1 = self.table1
+
+        self.assert_compile(
+            select(
+                [
+                    type_coerce(table1.c.name, Integer),
+                    type_coerce(table1.c.name, String),
+                    table1.c.name,
+                ]
+            ),
+            # ideally type_coerce wouldn't label at all...
+            "SELECT some_table.name AS name, "
+            "some_table.name AS name, "
+            "some_table.name FROM some_table",
+        )
+
+    def test_boolean_auto_label(self):
+        col = column("value", Boolean)
+
+        self.assert_compile(
+            select([~col, col]),
+            # not sure if this SQL is right but this is what it was
+            # before the new labeling, just different label name
+            "SELECT value = 0 AS value, value",
+        )
+
+    def test_label_auto_label_use_labels(self):
+        expr = self._fixture()
+        table1 = self.table1
+
+        self.assert_compile(
+            select(
+                [
+                    expr(table1.c.name.label("foo")),
+                    table1.c.name.label("bar"),
+                    table1.c.value,
+                ]
+            ).apply_labels(),
+            # the expr around label is treated the same way as plain column
+            # with label
+            "SELECT SOME_COL_THING(some_table.name) AS foo, "
+            "some_table.name AS bar, "
+            "some_table.value AS some_table_value FROM some_table",
+        )
+
+    def test_column_auto_label_dupes_use_labels(self):
+        expr = self._fixture()
+        table1 = self.table1
+
+        self.assert_compile(
+            select(
+                [
+                    table1.c.name,
+                    table1.c.name,
+                    expr(table1.c.name),
+                    expr(table1.c.name),
+                ]
+            ).apply_labels(),
+            "SELECT some_table.name AS some_table_name, "
+            "some_table.name AS some_table_name_1, "
+            "SOME_COL_THING(some_table.name) AS some_table_name_2, "
+            "SOME_COL_THING(some_table.name) AS some_table_name_3 "
+            "FROM some_table",
+        )
+
+    def test_column_auto_label_use_labels(self):
+        expr = self._fixture()
+        table1 = self.table1
+
+        self.assert_compile(
+            select([table1.c.name, expr(table1.c.value)]).apply_labels(),
+            "SELECT some_table.name AS some_table_name, "
+            "SOME_COL_THING(some_table.value) "
+            "AS some_table_value FROM some_table",
+        )
diff --git a/test/sql/test_type_expressions.py b/test/sql/test_type_expressions.py
index 1f4649ffc..2d45231cd 100644
--- a/test/sql/test_type_expressions.py
+++ b/test/sql/test_type_expressions.py
@@ -121,7 +121,7 @@ class SelectTest(_ExprFixture, fixtures.TestBase, AssertsCompiledSQL):
         table = self._fixture()
         self.assert_compile(
             select([cast(table.c.y, String)]),
-            "SELECT CAST(test_table.y AS VARCHAR) AS anon_1 FROM test_table",
+            "SELECT CAST(test_table.y AS VARCHAR) AS y FROM test_table",
         )
 
     def test_select_cols_use_labels(self):
diff --git a/test/sql/test_types.py b/test/sql/test_types.py
index a5c9313f8..9303bb4cd 100644
--- a/test/sql/test_types.py
+++ b/test/sql/test_types.py
@@ -849,7 +849,7 @@ class TypeCoerceCastTest(fixtures.TablesTest):
         t.insert().values(data=coerce_fn("d1", MyType)).execute()
 
         eq_(
-            select([t.c.data, coerce_fn(t.c.data, MyType)])
+            select([t.c.data.label("x"), coerce_fn(t.c.data, MyType)])
             .alias()
             .select()
             .execute()