- The :func:`~.expression.column` and :func:`~.expression.table`

constructs are now importable from the "from sqlalchemy" namespace,
just like every other Core construct.
- The implicit conversion of strings to :func:`.text` constructs
when passed to most builder methods of :func:`.select` as
well as :class:`.Query` now emits a warning with just the
plain string sent.   The textual conversion still proceeds normally,
however.  The only method that accepts a string without a warning
are the "label reference" methods like order_by(), group_by();
these functions will now at compile time attempt to resolve a single
string argument to a column or label expression present in the
selectable; if none is located, the expression still renders, but
you get the warning again. The rationale here is that the implicit
conversion from string to text is more unexpected than not these days,
and it is better that the user send more direction to the Core / ORM
when passing a raw string as to what direction should be taken.
Core/ORM tutorials have been updated to go more in depth as to how text
is handled.
fixes #2992

1 files changed, 229 insertions, 25 deletions

diff --git a/test/sql/test_text.py b/test/sql/test_text.py
index 6e9be9eea..182c63624 100644
--- a/test/sql/test_text.py
+++ b/test/sql/test_text.py
@@ -1,10 +1,13 @@
 """Test the TextClause and related constructs."""
 
-from sqlalchemy.testing import fixtures, AssertsCompiledSQL, eq_, assert_raises_message
+from sqlalchemy.testing import fixtures, AssertsCompiledSQL, eq_, \
+    assert_raises_message, expect_warnings
 from sqlalchemy import text, select, Integer, String, Float, \
-    bindparam, and_, func, literal_column, exc, MetaData, Table, Column
+    bindparam, and_, func, literal_column, exc, MetaData, Table, Column,\
+    asc, func, desc, union
 from sqlalchemy.types import NullType
 from sqlalchemy.sql import table, column
+from sqlalchemy import util
 
 table1 = table('mytable',
                column('myid', Integer),
@@ -38,9 +41,14 @@ class SelectCompositionTest(fixtures.TestBase, AssertsCompiledSQL):
 
     def test_select_composition_one(self):
         self.assert_compile(select(
-            ["foobar(a)", "pk_foo_bar(syslaal)"],
-            "a = 12",
-            from_obj=["foobar left outer join lala on foobar.foo = lala.foo"]
+            [
+                literal_column("foobar(a)"),
+                literal_column("pk_foo_bar(syslaal)")
+            ],
+            text("a = 12"),
+            from_obj=[
+                text("foobar left outer join lala on foobar.foo = lala.foo")
+            ]
         ),
             "SELECT foobar(a), pk_foo_bar(syslaal) FROM foobar "
             "left outer join lala on foobar.foo = lala.foo WHERE a = 12"
@@ -48,18 +56,18 @@ class SelectCompositionTest(fixtures.TestBase, AssertsCompiledSQL):
 
     def test_select_composition_two(self):
         s = select()
-        s.append_column("column1")
-        s.append_column("column2")
-        s.append_whereclause("column1=12")
-        s.append_whereclause("column2=19")
+        s.append_column(column("column1"))
+        s.append_column(column("column2"))
+        s.append_whereclause(text("column1=12"))
+        s.append_whereclause(text("column2=19"))
         s = s.order_by("column1")
-        s.append_from("table1")
+        s.append_from(text("table1"))
         self.assert_compile(s, "SELECT column1, column2 FROM table1 WHERE "
                             "column1=12 AND column2=19 ORDER BY column1")
 
     def test_select_composition_three(self):
         self.assert_compile(
-            select(["column1", "column2"],
+            select([column("column1"), column("column2")],
                    from_obj=table1).alias('somealias').select(),
             "SELECT somealias.column1, somealias.column2 FROM "
             "(SELECT column1, column2 FROM mytable) AS somealias"
@@ -68,9 +76,13 @@ class SelectCompositionTest(fixtures.TestBase, AssertsCompiledSQL):
     def test_select_composition_four(self):
         # test that use_labels doesn't interfere with literal columns
         self.assert_compile(
-            select(["column1", "column2", table1.c.myid], from_obj=table1,
-                   use_labels=True),
-            "SELECT column1, column2, mytable.myid AS mytable_myid "
+            select([
+                text("column1"), column("column2"),
+                column("column3").label("bar"), table1.c.myid],
+                from_obj=table1,
+                use_labels=True),
+            "SELECT column1, column2, column3 AS bar, "
+            "mytable.myid AS mytable_myid "
             "FROM mytable"
         )
 
@@ -78,8 +90,10 @@ class SelectCompositionTest(fixtures.TestBase, AssertsCompiledSQL):
         # test that use_labels doesn't interfere
         # with literal columns that have textual labels
         self.assert_compile(
-            select(["column1 AS foobar", "column2 AS hoho", table1.c.myid],
-                   from_obj=table1, use_labels=True),
+            select([
+                text("column1 AS foobar"), text("column2 AS hoho"),
+                table1.c.myid],
+                from_obj=table1, use_labels=True),
             "SELECT column1 AS foobar, column2 AS hoho, "
             "mytable.myid AS mytable_myid FROM mytable"
         )
@@ -89,8 +103,10 @@ class SelectCompositionTest(fixtures.TestBase, AssertsCompiledSQL):
         # doesn't interfere with literal columns,
         # exported columns don't get quoted
         self.assert_compile(
-            select(["column1 AS foobar", "column2 AS hoho", table1.c.myid],
-                   from_obj=[table1]).select(),
+            select([
+                literal_column("column1 AS foobar"),
+                literal_column("column2 AS hoho"), table1.c.myid],
+                from_obj=[table1]).select(),
             "SELECT column1 AS foobar, column2 AS hoho, myid FROM "
             "(SELECT column1 AS foobar, column2 AS hoho, "
             "mytable.myid AS myid FROM mytable)"
@@ -98,25 +114,29 @@ class SelectCompositionTest(fixtures.TestBase, AssertsCompiledSQL):
 
     def test_select_composition_seven(self):
         self.assert_compile(
-            select(['col1', 'col2'], from_obj='tablename').alias('myalias'),
+            select([
+                literal_column('col1'),
+                literal_column('col2')
+            ], from_obj=table('tablename')).alias('myalias'),
             "SELECT col1, col2 FROM tablename"
         )
 
     def test_select_composition_eight(self):
         self.assert_compile(select(
-            [table1.alias('t'), "foo.f"],
-            "foo.f = t.id",
-            from_obj=["(select f from bar where lala=heyhey) foo"]
+            [table1.alias('t'), text("foo.f")],
+            text("foo.f = t.id"),
+            from_obj=[text("(select f from bar where lala=heyhey) foo")]
         ),
             "SELECT t.myid, t.name, t.description, foo.f FROM mytable AS t, "
             "(select f from bar where lala=heyhey) foo WHERE foo.f = t.id")
 
     def test_select_bundle_columns(self):
         self.assert_compile(select(
-            [table1, table2.c.otherid, "sysdate()", "foo, bar, lala"],
+            [table1, table2.c.otherid,
+                text("sysdate()"), text("foo, bar, lala")],
             and_(
-                "foo.id = foofoo(lala)",
-                "datetime(foo) = Today",
+                text("foo.id = foofoo(lala)"),
+                text("datetime(foo) = Today"),
                 table1.c.myid == table2.c.otherid,
             )
         ),
@@ -470,3 +490,187 @@ class AsFromTest(fixtures.TestBase, AssertsCompiledSQL):
             set(t.element._bindparams),
             set(["bat", "foo", "bar"])
         )
+
+
+class TextWarningsTest(fixtures.TestBase, AssertsCompiledSQL):
+    __dialect__ = 'default'
+
+    def _test(self, fn, arg, offending_clause, expected):
+        assert_raises_message(
+            exc.SAWarning,
+            r"Textual (?:SQL|column|SQL FROM) expression %(stmt)r should be "
+            r"explicitly declared (?:with|as) text\(%(stmt)r\)" % {
+                "stmt": util.ellipses_string(offending_clause),
+            },
+            fn, arg
+        )
+
+        with expect_warnings("Textual "):
+            stmt = fn(arg)
+            self.assert_compile(stmt, expected)
+
+    def test_where(self):
+        self._test(
+            select([table1.c.myid]).where, "myid == 5", "myid == 5",
+            "SELECT mytable.myid FROM mytable WHERE myid == 5"
+        )
+
+    def test_column(self):
+        self._test(
+            select, ["myid"], "myid",
+            "SELECT myid"
+        )
+
+    def test_having(self):
+        self._test(
+            select([table1.c.myid]).having, "myid == 5", "myid == 5",
+            "SELECT mytable.myid FROM mytable HAVING myid == 5"
+        )
+
+    def test_from(self):
+        self._test(
+            select([table1.c.myid]).select_from, "mytable", "mytable",
+            "SELECT mytable.myid FROM mytable, mytable"   # two FROMs
+        )
+
+
+class OrderByLabelResolutionTest(fixtures.TestBase, AssertsCompiledSQL):
+    __dialect__ = 'default'
+
+    def _test_warning(self, stmt, offending_clause, expected):
+        with expect_warnings(
+                "Can't resolve label reference %r;" % offending_clause):
+            self.assert_compile(
+                stmt,
+                expected
+            )
+        assert_raises_message(
+            exc.SAWarning,
+            "Can't resolve label reference %r; converting to text" %
+            offending_clause,
+            stmt.compile
+        )
+
+    def test_order_by_label(self):
+        stmt = select([table1.c.myid.label('foo')]).order_by('foo')
+        self.assert_compile(
+            stmt,
+            "SELECT mytable.myid AS foo FROM mytable ORDER BY foo"
+        )
+
+    def test_order_by_colname(self):
+        stmt = select([table1.c.myid]).order_by('name')
+        self.assert_compile(
+            stmt,
+            "SELECT mytable.myid FROM mytable ORDER BY mytable.name"
+        )
+
+    def test_order_by_alias_colname(self):
+        t1 = table1.alias()
+        stmt = select([t1.c.myid]).apply_labels().order_by('name')
+        self.assert_compile(
+            stmt,
+            "SELECT mytable_1.myid AS mytable_1_myid "
+            "FROM mytable AS mytable_1 ORDER BY mytable_1.name"
+        )
+
+    def test_unresolvable_warning_order_by(self):
+        stmt = select([table1.c.myid]).order_by('foobar')
+        self._test_warning(
+            stmt, "foobar",
+            "SELECT mytable.myid FROM mytable ORDER BY foobar"
+        )
+
+    def test_group_by_label(self):
+        stmt = select([table1.c.myid.label('foo')]).group_by('foo')
+        self.assert_compile(
+            stmt,
+            "SELECT mytable.myid AS foo FROM mytable GROUP BY foo"
+        )
+
+    def test_group_by_colname(self):
+        stmt = select([table1.c.myid]).group_by('name')
+        self.assert_compile(
+            stmt,
+            "SELECT mytable.myid FROM mytable GROUP BY mytable.name"
+        )
+
+    def test_unresolvable_warning_group_by(self):
+        stmt = select([table1.c.myid]).group_by('foobar')
+        self._test_warning(
+            stmt, "foobar",
+            "SELECT mytable.myid FROM mytable GROUP BY foobar"
+        )
+
+    def test_asc(self):
+        stmt = select([table1.c.myid]).order_by(asc('name'), 'description')
+        self.assert_compile(
+            stmt,
+            "SELECT mytable.myid FROM mytable "
+            "ORDER BY mytable.name ASC, mytable.description"
+        )
+
+    def test_group_by_subquery(self):
+        stmt = select([table1]).alias()
+        stmt = select([stmt]).apply_labels().group_by("myid")
+        self.assert_compile(
+            stmt,
+            "SELECT anon_1.myid AS anon_1_myid, anon_1.name AS anon_1_name, "
+            "anon_1.description AS anon_1_description FROM "
+            "(SELECT mytable.myid AS myid, mytable.name AS name, "
+            "mytable.description AS description FROM mytable) AS anon_1 "
+            "GROUP BY anon_1.myid"
+        )
+
+    def test_order_by_func_label_desc(self):
+        stmt = select([func.foo('bar').label('fb'), table1]).\
+            order_by(desc('fb'))
+
+        self.assert_compile(
+            stmt,
+            "SELECT foo(:foo_1) AS fb, mytable.myid, mytable.name, "
+            "mytable.description FROM mytable ORDER BY fb DESC"
+        )
+
+    def test_pg_distinct(self):
+        stmt = select([table1]).distinct('name')
+        self.assert_compile(
+            stmt,
+            "SELECT DISTINCT ON (mytable.name) mytable.myid, "
+            "mytable.name, mytable.description FROM mytable",
+            dialect="postgresql"
+        )
+
+    def test_over(self):
+        stmt = select([column("foo"), column("bar")])
+        stmt = select(
+            [func.row_number().
+             over(order_by='foo', partition_by='bar')]
+        ).select_from(stmt)
+
+        self.assert_compile(
+            stmt,
+            "SELECT row_number() OVER (PARTITION BY bar ORDER BY foo) "
+            "AS anon_1 FROM (SELECT foo, bar)"
+        )
+
+    def test_union_column(self):
+        s1 = select([table1])
+        s2 = select([table1])
+        stmt = union(s1, s2).order_by("name")
+        self.assert_compile(
+            stmt,
+            "SELECT mytable.myid, mytable.name, mytable.description FROM "
+            "mytable UNION SELECT mytable.myid, mytable.name, "
+            "mytable.description FROM mytable ORDER BY name"
+        )
+
+    def test_union_label(self):
+        s1 = select([func.foo("hoho").label('x')])
+        s2 = select([func.foo("Bar").label('y')])
+        stmt = union(s1, s2).order_by("x")
+        self.assert_compile(
+            stmt,
+            "SELECT foo(:foo_1) AS x UNION SELECT foo(:foo_2) AS y ORDER BY x"
+        )
+