Include DISTINCT in error message for label reference

Needed to add tests to ensure this label reference is handled
correctly, so also modified the exception message to
be more clear if someone has this error within distinct().

Change-Id: I6e685e46ae336596272d14366445ac224c18d92c

5 files changed, 73 insertions, 5 deletions

diff --git a/doc/build/changelog/unreleased_13/dist_warn.rst b/doc/build/changelog/unreleased_13/dist_warn.rst
new file mode 100644
index 000000000..b70a9f08e
--- /dev/null
+++ b/doc/build/changelog/unreleased_13/dist_warn.rst
@@ -0,0 +1,7 @@
+.. change::
+    :tags: bug, sql
+
+    Changed the text of the exception for "Can't resolve label reference" to
+    include other kinds of label coercions, namely that "DISTINCT" is also in
+    this category under the PostgreSQL dialect.
+
diff --git a/lib/sqlalchemy/sql/compiler.py b/lib/sqlalchemy/sql/compiler.py
index 85c1750b7..a28ce465a 100644
--- a/lib/sqlalchemy/sql/compiler.py
+++ b/lib/sqlalchemy/sql/compiler.py
@@ -811,7 +811,10 @@ class SQLCompiler(Compiled):
         except KeyError:
             coercions._no_text_coercion(
                 element.element,
-                extra="Can't resolve label reference for ORDER BY / GROUP BY.",
+                extra=(
+                    "Can't resolve label reference for ORDER BY / "
+                    "GROUP BY / DISTINCT etc."
+                ),
                 exc_cls=exc.CompileError,
             )
         else:
diff --git a/test/orm/test_query.py b/test/orm/test_query.py
index 2bc0af3c2..068a7c893 100644
--- a/test/orm/test_query.py
+++ b/test/orm/test_query.py
@@ -3990,6 +3990,45 @@ class DistinctTest(QueryTest, AssertsCompiledSQL):
             dialect="postgresql",
         )
 
+    def test_columns_augmented_sql_three_using_label_reference(self):
+        User, Address = self.classes.User, self.classes.Address
+
+        sess = create_session()
+
+        q = (
+            sess.query(User.id, User.name.label("foo"), Address.id)
+            .distinct("name")
+            .order_by(User.id, User.name, Address.email_address)
+        )
+
+        # no columns are added when DISTINCT ON is used
+        self.assert_compile(
+            q,
+            "SELECT DISTINCT ON (users.name) users.id AS users_id, "
+            "users.name AS foo, addresses.id AS addresses_id FROM users, "
+            "addresses ORDER BY users.id, users.name, addresses.email_address",
+            dialect="postgresql",
+        )
+
+    def test_columns_augmented_sql_illegal_label_reference(self):
+        User, Address = self.classes.User, self.classes.Address
+
+        sess = create_session()
+
+        q = sess.query(User.id, User.name.label("foo"), Address.id).distinct(
+            "not a label"
+        )
+
+        from sqlalchemy.dialects import postgresql
+
+        assert_raises_message(
+            sa_exc.CompileError,
+            "Can't resolve label reference for ORDER BY / "
+            "GROUP BY / DISTINCT etc.",
+            q.with_labels().statement.compile,
+            dialect=postgresql.dialect(),
+        )
+
     def test_columns_augmented_sql_four(self):
         User, Address = self.classes.User, self.classes.Address
 
diff --git a/test/sql/test_compiler.py b/test/sql/test_compiler.py
index ca73f6c18..486b54682 100644
--- a/test/sql/test_compiler.py
+++ b/test/sql/test_compiler.py
@@ -2073,7 +2073,8 @@ class SelectTest(fixtures.TestBase, AssertsCompiledSQL):
         # to check the special thing CompoundSelect does with labels
         assert_raises_message(
             exc.CompileError,
-            "Can't resolve label reference for ORDER BY / GROUP BY. Textual "
+            "Can't resolve label reference for ORDER BY / GROUP BY / "
+            "DISTINCT etc. Textual "
             "SQL expression 'noname'",
             union(
                 select([table1.c.myid, table1.c.name]),
diff --git a/test/sql/test_text.py b/test/sql/test_text.py
index 3f12d06a9..3386d0aae 100644
--- a/test/sql/test_text.py
+++ b/test/sql/test_text.py
@@ -678,14 +678,16 @@ class TextErrorsTest(fixtures.TestBase, AssertsCompiledSQL):
 class OrderByLabelResolutionTest(fixtures.TestBase, AssertsCompiledSQL):
     __dialect__ = "default"
 
-    def _test_exception(self, stmt, offending_clause):
+    def _test_exception(self, stmt, offending_clause, dialect=None):
         assert_raises_message(
             exc.CompileError,
-            r"Can't resolve label reference for ORDER BY / GROUP BY. "
+            r"Can't resolve label reference for ORDER BY / GROUP BY / "
+            "DISTINCT etc. "
             "Textual SQL "
             "expression %r should be explicitly "
             r"declared as text\(%r\)" % (offending_clause, offending_clause),
             stmt.compile,
+            dialect=dialect,
         )
 
     def test_order_by_label(self):
@@ -736,6 +738,21 @@ class OrderByLabelResolutionTest(fixtures.TestBase, AssertsCompiledSQL):
         stmt = select([table1.c.myid]).order_by("foobar")
         self._test_exception(stmt, "foobar")
 
+    def test_distinct_label(self):
+
+        stmt = select([table1.c.myid.label("foo")]).distinct("foo")
+        self.assert_compile(
+            stmt,
+            "SELECT DISTINCT ON (foo) mytable.myid AS foo FROM mytable",
+            dialect="postgresql",
+        )
+
+    def test_unresolvable_distinct_label(self):
+        from sqlalchemy.dialects import postgresql
+
+        stmt = select([table1.c.myid.label("foo")]).distinct("not a label")
+        self._test_exception(stmt, "not a label", dialect=postgresql.dialect())
+
     def test_group_by_label(self):
         stmt = select([table1.c.myid.label("foo")]).group_by("foo")
         self.assert_compile(
@@ -890,7 +907,8 @@ class OrderByLabelResolutionTest(fixtures.TestBase, AssertsCompiledSQL):
 
         assert_raises_message(
             exc.CompileError,
-            r"Can't resolve label reference for ORDER BY / GROUP BY. "
+            r"Can't resolve label reference for ORDER BY / GROUP BY / "
+            "DISTINCT etc. "
             "Textual SQL "
             "expression 't1name' should be explicitly "
             r"declared as text\('t1name'\)",