Create a real type for Tuple() and handle appropriately in compiler

Improved the :func:`_sql.tuple_` construct such that it behaves predictably
when used in a columns-clause context.  The SQL tuple is not supported as a
"SELECT" columns clause element on most backends; on those that do
(PostgreSQL, not surprisingly), the Python DBAPI does not have a "nested
type" concept so there are still challenges in fetching rows for such an
object. Use of :func:`_sql.tuple_` in a :func:`_sql.select` or
:class:`_orm.Query` will now raise a :class:`_exc.CompileError` at the
point at which the :func:`_sql.tuple_` object is seen as presenting itself
for fetching rows (i.e., if the tuple is in the columns clause of a
subquery, no error is raised).  For ORM use,the :class:`_orm.Bundle` object
is an explicit directive that a series of columns should be returned as a
sub-tuple per row and is suggested by the error message. Additionally ,the
tuple will now render with parenthesis in all contexts. Previously, the
parenthesization would not render in a columns context leading to
non-defined behavior.

As part of this change, Tuple receives a dedicated datatype
which appears to allow us the very desirable change of removing
the bindparam._expanding_in_types attribute as well as
ClauseList._tuple_values (which might already have not been
needed due to #4645).

Fixes: #5127
Change-Id: Iecafa0e0aac2f1f37ec8d0e1631d562611c90200

5 files changed, 77 insertions, 4 deletions

diff --git a/test/dialect/postgresql/test_dialect.py b/test/dialect/postgresql/test_dialect.py
index 57c243442..d653d04b3 100644
--- a/test/dialect/postgresql/test_dialect.py
+++ b/test/dialect/postgresql/test_dialect.py
@@ -828,7 +828,7 @@ $$ LANGUAGE plpgsql;
 
     def test_extract(self, connection):
         fivedaysago = testing.db.scalar(
-            select(func.now())
+            select(func.now().op("at time zone")("UTC"))
         ) - datetime.timedelta(days=5)
         for field, exp in (
             ("year", fivedaysago.year),
@@ -837,7 +837,11 @@ $$ LANGUAGE plpgsql;
         ):
             r = connection.execute(
                 select(
-                    extract(field, func.now() + datetime.timedelta(days=-5))
+                    extract(
+                        field,
+                        func.now().op("at time zone")("UTC")
+                        + datetime.timedelta(days=-5),
+                    )
                 )
             ).scalar()
             eq_(r, exp)
diff --git a/test/orm/test_bundle.py b/test/orm/test_bundle.py
index 9d1d0b61b..49feb32f0 100644
--- a/test/orm/test_bundle.py
+++ b/test/orm/test_bundle.py
@@ -1,15 +1,18 @@
+from sqlalchemy import exc
 from sqlalchemy import ForeignKey
 from sqlalchemy import func
 from sqlalchemy import Integer
 from sqlalchemy import select
 from sqlalchemy import String
 from sqlalchemy import testing
+from sqlalchemy import tuple_
 from sqlalchemy.orm import aliased
 from sqlalchemy.orm import Bundle
 from sqlalchemy.orm import mapper
 from sqlalchemy.orm import relationship
 from sqlalchemy.orm import Session
 from sqlalchemy.sql.elements import ClauseList
+from sqlalchemy.testing import assert_raises_message
 from sqlalchemy.testing import AssertsCompiledSQL
 from sqlalchemy.testing import eq_
 from sqlalchemy.testing import fixtures
@@ -83,6 +86,34 @@ class BundleTest(fixtures.MappedTest, AssertsCompiledSQL):
         )
         sess.commit()
 
+    def test_tuple_suggests_bundle(self, connection):
+        Data, Other = self.classes("Data", "Other")
+
+        sess = Session(connection)
+        q = sess.query(tuple_(Data.id, Other.id)).join(Data.others)
+
+        assert_raises_message(
+            exc.CompileError,
+            r"Most backends don't support SELECTing from a tuple\(\) object.  "
+            "If this is an ORM query, consider using the Bundle object.",
+            q.all,
+        )
+
+    def test_tuple_suggests_bundle_future(self, connection):
+        Data, Other = self.classes("Data", "Other")
+
+        stmt = select(tuple_(Data.id, Other.id)).join(Data.others)
+
+        sess = Session(connection, future=True)
+
+        assert_raises_message(
+            exc.CompileError,
+            r"Most backends don't support SELECTing from a tuple\(\) object.  "
+            "If this is an ORM query, consider using the Bundle object.",
+            sess.execute,
+            stmt,
+        )
+
     def test_same_named_col_clauselist(self):
         Data, Other = self.classes("Data", "Other")
         bundle = Bundle("pk", Data.id, Other.id)
diff --git a/test/sql/test_operators.py b/test/sql/test_operators.py
index 7a027e28a..e5835a749 100644
--- a/test/sql/test_operators.py
+++ b/test/sql/test_operators.py
@@ -2791,7 +2791,7 @@ class TupleTypingTest(fixtures.TestBase):
         )
         t1 = tuple_(a, b, c)
         expr = t1 == (3, "hi", "there")
-        self._assert_types([bind.type for bind in expr.right.element.clauses])
+        self._assert_types([bind.type for bind in expr.right.clauses])
 
     def test_type_coercion_on_in(self):
         a, b, c = (
@@ -2803,7 +2803,8 @@ class TupleTypingTest(fixtures.TestBase):
         expr = t1.in_([(3, "hi", "there"), (4, "Q", "P")])
 
         eq_(len(expr.right.value), 2)
-        self._assert_types(expr.right._expanding_in_types)
+
+        self._assert_types(expr.right.type.types)
 
 
 class InSelectableTest(fixtures.TestBase, testing.AssertsCompiledSQL):
diff --git a/test/sql/test_query.py b/test/sql/test_query.py
index bca7c262b..3662a6e72 100644
--- a/test/sql/test_query.py
+++ b/test/sql/test_query.py
@@ -179,6 +179,18 @@ class QueryTest(fixtures.TestBase):
         assert row.x == True  # noqa
         assert row.y == False  # noqa
 
+    def test_select_tuple(self, connection):
+        connection.execute(
+            users.insert(), {"user_id": 1, "user_name": "apples"},
+        )
+
+        assert_raises_message(
+            exc.CompileError,
+            r"Most backends don't support SELECTing from a tuple\(\) object.",
+            connection.execute,
+            select(tuple_(users.c.user_id, users.c.user_name)),
+        )
+
     def test_like_ops(self, connection):
         connection.execute(
             users.insert(),
diff --git a/test/sql/test_select.py b/test/sql/test_select.py
index be39fe46b..4c00cb53c 100644
--- a/test/sql/test_select.py
+++ b/test/sql/test_select.py
@@ -6,6 +6,7 @@ from sqlalchemy import MetaData
 from sqlalchemy import select
 from sqlalchemy import String
 from sqlalchemy import Table
+from sqlalchemy import tuple_
 from sqlalchemy.sql import column
 from sqlalchemy.sql import table
 from sqlalchemy.testing import assert_raises_message
@@ -197,3 +198,27 @@ class FutureSelectTest(fixtures.TestBase, AssertsCompiledSQL):
             select(table1).filter_by,
             foo="bar",
         )
+
+    def test_select_tuple_outer(self):
+        stmt = select(tuple_(table1.c.myid, table1.c.name))
+
+        assert_raises_message(
+            exc.CompileError,
+            r"Most backends don't support SELECTing from a tuple\(\) object.  "
+            "If this is an ORM query, consider using the Bundle object.",
+            stmt.compile,
+        )
+
+    def test_select_tuple_subquery(self):
+        subq = select(
+            table1.c.name, tuple_(table1.c.myid, table1.c.name)
+        ).subquery()
+
+        stmt = select(subq.c.name)
+
+        # if we aren't fetching it, then render it
+        self.assert_compile(
+            stmt,
+            "SELECT anon_1.name FROM (SELECT mytable.name AS name, "
+            "(mytable.myid, mytable.name) AS anon_2 FROM mytable) AS anon_1",
+        )