- calling str() on a core sql construct has been made more "friendly",

when the construct contains non-standard sql elements such as
returning, array index operations, or dialect-specific or custom
datatypes.  a string is now returned in these cases rendering an
approximation of the construct (typically the postgresql-style
version of it) rather than raising an error. fixes #3631
- add within_group to top-level imports
- add eq_ignore_whitespace to sqlalchemy.testing imports

3 files changed, 79 insertions, 9 deletions

diff --git a/test/sql/test_compiler.py b/test/sql/test_compiler.py
index 5d082175a..85a9f77bc 100644
--- a/test/sql/test_compiler.py
+++ b/test/sql/test_compiler.py
@@ -10,7 +10,8 @@ styling and coherent test organization.
 
 """
 
-from sqlalchemy.testing import eq_, is_, assert_raises, assert_raises_message
+from sqlalchemy.testing import eq_, is_, assert_raises, \
+    assert_raises_message, eq_ignore_whitespace
 from sqlalchemy import testing
 from sqlalchemy.testing import fixtures, AssertsCompiledSQL
 from sqlalchemy import Integer, String, MetaData, Table, Column, select, \
@@ -2562,7 +2563,7 @@ class UnsupportedTest(fixtures.TestBase):
 
         assert_raises_message(
             exc.UnsupportedCompilationError,
-            r"Compiler <sqlalchemy.sql.compiler.SQLCompiler .*"
+            r"Compiler <sqlalchemy.sql.compiler.StrSQLCompiler .*"
             r"can't render element of type <class '.*SomeElement'>",
             SomeElement().compile
         )
@@ -2578,7 +2579,7 @@ class UnsupportedTest(fixtures.TestBase):
 
         assert_raises_message(
             exc.UnsupportedCompilationError,
-            r"Compiler <sqlalchemy.sql.compiler.SQLCompiler .*"
+            r"Compiler <sqlalchemy.sql.compiler.StrSQLCompiler .*"
             r"can't render element of type <class '.*SomeElement'>",
             SomeElement().compile
         )
@@ -2591,12 +2592,76 @@ class UnsupportedTest(fixtures.TestBase):
         binary = BinaryExpression(column("foo"), column("bar"), myop)
         assert_raises_message(
             exc.UnsupportedCompilationError,
-            r"Compiler <sqlalchemy.sql.compiler.SQLCompiler .*"
+            r"Compiler <sqlalchemy.sql.compiler.StrSQLCompiler .*"
             r"can't render element of type <function.*",
             binary.compile
         )
 
 
+class StringifySpecialTest(fixtures.TestBase):
+    def test_basic(self):
+        stmt = select([table1]).where(table1.c.myid == 10)
+        eq_ignore_whitespace(
+            str(stmt),
+            "SELECT mytable.myid, mytable.name, mytable.description "
+            "FROM mytable WHERE mytable.myid = :myid_1"
+        )
+
+    def test_cte(self):
+        # stringify of these was supported anyway by defaultdialect.
+        stmt = select([table1.c.myid]).cte()
+        stmt = select([stmt])
+        eq_ignore_whitespace(
+            str(stmt),
+            "WITH anon_1 AS (SELECT mytable.myid AS myid FROM mytable) "
+            "SELECT anon_1.myid FROM anon_1"
+        )
+
+    def test_returning(self):
+        stmt = table1.insert().returning(table1.c.myid)
+
+        eq_ignore_whitespace(
+            str(stmt),
+            "INSERT INTO mytable (myid, name, description) "
+            "VALUES (:myid, :name, :description) RETURNING mytable.myid"
+        )
+
+    def test_array_index(self):
+        stmt = select([column('foo', types.ARRAY(Integer))[5]])
+
+        eq_ignore_whitespace(
+            str(stmt),
+            "SELECT foo[:foo_1] AS anon_1"
+        )
+
+    def test_unknown_type(self):
+        class MyType(types.TypeEngine):
+            __visit_name__ = 'mytype'
+
+        stmt = select([cast(table1.c.myid, MyType)])
+
+        eq_ignore_whitespace(
+            str(stmt),
+            "SELECT CAST(mytable.myid AS MyType) AS anon_1 FROM mytable"
+        )
+
+    def test_within_group(self):
+        # stringify of these was supported anyway by defaultdialect.
+        from sqlalchemy import within_group
+        stmt = select([
+            table1.c.myid,
+            within_group(
+                func.percentile_cont(0.5),
+                table1.c.name.desc()
+            )
+        ])
+        eq_ignore_whitespace(
+            str(stmt),
+            "SELECT mytable.myid, percentile_cont(:percentile_cont_1) "
+            "WITHIN GROUP (ORDER BY mytable.name DESC) AS anon_1 FROM mytable"
+        )
+
+
 class KwargPropagationTest(fixtures.TestBase):
 
     @classmethod
diff --git a/test/sql/test_metadata.py b/test/sql/test_metadata.py
index bbc318421..47ecf5a9b 100644
--- a/test/sql/test_metadata.py
+++ b/test/sql/test_metadata.py
@@ -9,6 +9,7 @@ from sqlalchemy import Integer, String, UniqueConstraint, \
     events, Unicode, types as sqltypes, bindparam, \
     Table, Column, Boolean, Enum, func, text, TypeDecorator
 from sqlalchemy import schema, exc
+from sqlalchemy.engine import default
 from sqlalchemy.sql import elements, naming
 import sqlalchemy as tsa
 from sqlalchemy.testing import fixtures
@@ -3682,7 +3683,7 @@ class NamingConventionTest(fixtures.TestBase, AssertsCompiledSQL):
             exc.InvalidRequestError,
             "Naming convention including \%\(constraint_name\)s token "
             "requires that constraint is explicitly named.",
-            schema.CreateTable(u1).compile
+            schema.CreateTable(u1).compile, dialect=default.DefaultDialect()
         )
 
     def test_schematype_no_ck_name_boolean_no_name(self):
diff --git a/test/sql/test_selectable.py b/test/sql/test_selectable.py
index b9cbbf480..7203cc5a3 100644
--- a/test/sql/test_selectable.py
+++ b/test/sql/test_selectable.py
@@ -155,15 +155,19 @@ class SelectableTest(
         assert c in s.c.bar.proxy_set
 
     def test_no_error_on_unsupported_expr_key(self):
-        from sqlalchemy.dialects.postgresql import ARRAY
+        from sqlalchemy.sql.expression import BinaryExpression
 
-        t = table('t', column('x', ARRAY(Integer)))
+        def myop(x, y):
+            pass
+
+        t = table('t', column('x'), column('y'))
+
+        expr = BinaryExpression(t.c.x, t.c.y, myop)
 
-        expr = t.c.x[5]
         s = select([t, expr])
         eq_(
             s.c.keys(),
-            ['x', expr.anon_label]
+            ['x', 'y', expr.anon_label]
         )
 
     def test_cloned_intersection(self):