11e278de4SMike Bayerfrom sqlalchemy import and_
21e278de4SMike Bayerfrom sqlalchemy import asc
31e278de4SMike Bayerfrom sqlalchemy import bindparam
41e278de4SMike Bayerfrom sqlalchemy import cast
51e278de4SMike Bayerfrom sqlalchemy import desc
61e278de4SMike Bayerfrom sqlalchemy import exc
71e278de4SMike Bayerfrom sqlalchemy import except_
81e278de4SMike Bayerfrom sqlalchemy import ForeignKey
91e278de4SMike Bayerfrom sqlalchemy import func
101e278de4SMike Bayerfrom sqlalchemy import INT
111e278de4SMike Bayerfrom sqlalchemy import Integer
121e278de4SMike Bayerfrom sqlalchemy import intersect
131e278de4SMike Bayerfrom sqlalchemy import literal
141e278de4SMike Bayerfrom sqlalchemy import literal_column
151e278de4SMike Bayerfrom sqlalchemy import MetaData
161e278de4SMike Bayerfrom sqlalchemy import not_
171e278de4SMike Bayerfrom sqlalchemy import or_
181e278de4SMike Bayerfrom sqlalchemy import select
191e278de4SMike Bayerfrom sqlalchemy import sql
201e278de4SMike Bayerfrom sqlalchemy import String
2120cdc645SMike Bayerfrom sqlalchemy import testing
221e278de4SMike Bayerfrom sqlalchemy import text
231e278de4SMike Bayerfrom sqlalchemy import tuple_
241e278de4SMike Bayerfrom sqlalchemy import TypeDecorator
251e278de4SMike Bayerfrom sqlalchemy import union
261e278de4SMike Bayerfrom sqlalchemy import union_all
271e278de4SMike Bayerfrom sqlalchemy import VARCHAR
28a8e1d33aSMike Bayerfrom sqlalchemy.engine import default
2922f65156SGord Thompsonfrom sqlalchemy.sql import LABEL_STYLE_TABLENAME_PLUS_COL
3022f65156SGord Thompsonfrom sqlalchemy.sql.selectable import LABEL_STYLE_NONE
311e278de4SMike Bayerfrom sqlalchemy.testing import assert_raises_message
321e278de4SMike Bayerfrom sqlalchemy.testing import eq_
331e278de4SMike Bayerfrom sqlalchemy.testing import fixtures
341e278de4SMike Bayerfrom sqlalchemy.testing import is_
351e278de4SMike Bayerfrom sqlalchemy.testing.schema import Column
361e278de4SMike Bayerfrom sqlalchemy.testing.schema import Table
3779bde753SMike Bayerfrom sqlalchemy.testing.util import resolve_lambda
381e278de4SMike Bayer
3960996bddSMike Bayer
40fd3c063dSMike Bayerclass QueryTest(fixtures.TablesTest):
412f150beeSMike Bayer    __backend__ = True
42bcb586a9SJason Kirtland
4345cec095SMike Bayer    @classmethod
44fd3c063dSMike Bayer    def define_tables(cls, metadata):
45fd3c063dSMike Bayer        Table(
46fd3c063dSMike Bayer            "users",
471e1a38e7SMike Bayer            metadata,
48fa80b73eSTony Locke            Column(
491e1a38e7SMike Bayer                "user_id", INT, primary_key=True, test_needs_autoincrement=True
501e1a38e7SMike Bayer            ),
511e1a38e7SMike Bayer            Column("user_name", VARCHAR(20)),
521e1a38e7SMike Bayer            test_needs_acid=True,
5360996bddSMike Bayer        )
54fd3c063dSMike Bayer        Table(
55fd3c063dSMike Bayer            "addresses",
561e1a38e7SMike Bayer            metadata,
57fa80b73eSTony Locke            Column(
581e1a38e7SMike Bayer                "address_id",
591e1a38e7SMike Bayer                Integer,
601e1a38e7SMike Bayer                primary_key=True,
611e1a38e7SMike Bayer                test_needs_autoincrement=True,
621e1a38e7SMike Bayer            ),
63fd3c063dSMike Bayer            Column("user_id", Integer, ForeignKey("users.user_id")),
641e1a38e7SMike Bayer            Column("address", String(30)),
651e1a38e7SMike Bayer            test_needs_acid=True,
66bb5f4392SMike Bayer        )
67350aed3fSMike Bayer
68fd3c063dSMike Bayer        Table(
691e1a38e7SMike Bayer            "u2",
701e1a38e7SMike Bayer            metadata,
711e1a38e7SMike Bayer            Column("user_id", INT, primary_key=True),
721e1a38e7SMike Bayer            Column("user_name", VARCHAR(20)),
731e1a38e7SMike Bayer            test_needs_acid=True,
747009653aSMike Bayer        )
752dbeeff5SMike Bayer
76b4a83504SGord Thompson    def test_order_by_label(self, connection):
77edf6b16fSMike Bayer        """test that a label within an ORDER BY works on each backend.
78350aed3fSMike Bayer
79318f47dcSMike Bayer        This test should be modified to support [ticket:1068] when that ticket
80318f47dcSMike Bayer        is implemented.  For now, you need to put the actual string in the
81318f47dcSMike Bayer        ORDER BY.
82350aed3fSMike Bayer
83edf6b16fSMike Bayer        """
847b24e458SMike Bayer
85fd3c063dSMike Bayer        users = self.tables.users
86fd3c063dSMike Bayer
87b4a83504SGord Thompson        connection.execute(
88b4a83504SGord Thompson            users.insert(),
89ebbbac0aSMike Bayer            [
90ebbbac0aSMike Bayer                {"user_id": 7, "user_name": "jack"},
91ebbbac0aSMike Bayer                {"user_id": 8, "user_name": "ed"},
92ebbbac0aSMike Bayer                {"user_id": 9, "user_name": "fred"},
93ebbbac0aSMike Bayer            ],
94edf6b16fSMike Bayer        )
95350aed3fSMike Bayer
961e1a38e7SMike Bayer        concat = ("test: " + users.c.user_name).label("thedata")
9745cec095SMike Bayer        eq_(
98e8600608SFederico Caselli            connection.execute(select(concat).order_by("thedata")).fetchall(),
991e1a38e7SMike Bayer            [("test: ed",), ("test: fred",), ("test: jack",)],
100edf6b16fSMike Bayer        )
101350aed3fSMike Bayer
1028fc5005dSMike Bayer        eq_(
103e8600608SFederico Caselli            connection.execute(select(concat).order_by("thedata")).fetchall(),
1041e1a38e7SMike Bayer            [("test: ed",), ("test: fred",), ("test: jack",)],
1058fc5005dSMike Bayer        )
106edf6b16fSMike Bayer
1071e1a38e7SMike Bayer        concat = ("test: " + users.c.user_name).label("thedata")
10845cec095SMike Bayer        eq_(
109b4a83504SGord Thompson            connection.execute(
110e8600608SFederico Caselli                select(concat).order_by(desc("thedata"))
111b4a83504SGord Thompson            ).fetchall(),
1121e1a38e7SMike Bayer            [("test: jack",), ("test: fred",), ("test: ed",)],
113edf6b16fSMike Bayer        )
114edf6b16fSMike Bayer
1158433a48fSMike Bayer    @testing.requires.order_by_label_with_expression
116b4a83504SGord Thompson    def test_order_by_label_compound(self, connection):
117fd3c063dSMike Bayer        users = self.tables.users
118b4a83504SGord Thompson        connection.execute(
119b4a83504SGord Thompson            users.insert(),
120ebbbac0aSMike Bayer            [
121ebbbac0aSMike Bayer                {"user_id": 7, "user_name": "jack"},
122ebbbac0aSMike Bayer                {"user_id": 8, "user_name": "ed"},
123ebbbac0aSMike Bayer                {"user_id": 9, "user_name": "fred"},
124ebbbac0aSMike Bayer            ],
1258433a48fSMike Bayer        )
1268433a48fSMike Bayer
1271e1a38e7SMike Bayer        concat = ("test: " + users.c.user_name).label("thedata")
1288433a48fSMike Bayer        eq_(
129b4a83504SGord Thompson            connection.execute(
130e8600608SFederico Caselli                select(concat).order_by(literal_column("thedata") + "x")
131b4a83504SGord Thompson            ).fetchall(),
1321e1a38e7SMike Bayer            [("test: ed",), ("test: fred",), ("test: jack",)],
1338433a48fSMike Bayer        )
134350aed3fSMike Bayer
1358fc5005dSMike Bayer    @testing.requires.boolean_col_expressions
136b4a83504SGord Thompson    def test_or_and_as_columns(self, connection):
1373b724ae1SMike Bayer        true, false = literal(True), literal(False)
138350aed3fSMike Bayer
139e8600608SFederico Caselli        eq_(connection.execute(select(and_(true, false))).scalar(), False)
140e8600608SFederico Caselli        eq_(connection.execute(select(and_(true, true))).scalar(), True)
141e8600608SFederico Caselli        eq_(connection.execute(select(or_(true, false))).scalar(), True)
142e8600608SFederico Caselli        eq_(connection.execute(select(or_(false, false))).scalar(), False)
143fa80b73eSTony Locke        eq_(
144c3f102c9SMike Bayer            connection.execute(select(not_(or_(false, false)))).scalar(),
145c3f102c9SMike Bayer            True,
1461e1a38e7SMike Bayer        )
147e1582344SMike Bayer
148b4a83504SGord Thompson        row = connection.execute(
149e8600608SFederico Caselli            select(or_(false, false).label("x"), and_(true, false).label("y"))
1501e1a38e7SMike Bayer        ).first()
151fa80b73eSTony Locke        assert row.x == False  # noqa
152fa80b73eSTony Locke        assert row.y == False  # noqa
153e1582344SMike Bayer
154b4a83504SGord Thompson        row = connection.execute(
155e8600608SFederico Caselli            select(or_(true, false).label("x"), and_(true, false).label("y"))
1561e1a38e7SMike Bayer        ).first()
157fa80b73eSTony Locke        assert row.x == True  # noqa
158fa80b73eSTony Locke        assert row.y == False  # noqa
159350aed3fSMike Bayer
1603b4bbbb2SMike Bayer    def test_select_tuple(self, connection):
161fd3c063dSMike Bayer        users = self.tables.users
1623b4bbbb2SMike Bayer        connection.execute(
163c3f102c9SMike Bayer            users.insert(),
164c3f102c9SMike Bayer            {"user_id": 1, "user_name": "apples"},
1653b4bbbb2SMike Bayer        )
1663b4bbbb2SMike Bayer
1673b4bbbb2SMike Bayer        assert_raises_message(
1683b4bbbb2SMike Bayer            exc.CompileError,
1693b4bbbb2SMike Bayer            r"Most backends don't support SELECTing from a tuple\(\) object.",
1703b4bbbb2SMike Bayer            connection.execute,
1713b4bbbb2SMike Bayer            select(tuple_(users.c.user_id, users.c.user_name)),
1723b4bbbb2SMike Bayer        )
1733b4bbbb2SMike Bayer
17479bde753SMike Bayer    @testing.combinations(
17579bde753SMike Bayer        (
17679bde753SMike Bayer            lambda users: select(users.c.user_id).where(
17779bde753SMike Bayer                users.c.user_name.startswith("apple")
17879bde753SMike Bayer            ),
17979bde753SMike Bayer            [(1,)],
18079bde753SMike Bayer        ),
18179bde753SMike Bayer        (
18279bde753SMike Bayer            lambda users: select(users.c.user_id).where(
18379bde753SMike Bayer                users.c.user_name.contains("i % t")
18479bde753SMike Bayer            ),
18579bde753SMike Bayer            [(5,)],
18679bde753SMike Bayer        ),
18779bde753SMike Bayer        (
18879bde753SMike Bayer            lambda users: select(users.c.user_id).where(
18979bde753SMike Bayer                users.c.user_name.endswith("anas")
19079bde753SMike Bayer            ),
19179bde753SMike Bayer            [(3,)],
19279bde753SMike Bayer        ),
19379bde753SMike Bayer        (
19479bde753SMike Bayer            lambda users: select(users.c.user_id).where(
19579bde753SMike Bayer                users.c.user_name.contains("i % t", escape="&")
19679bde753SMike Bayer            ),
19779bde753SMike Bayer            [(5,)],
19879bde753SMike Bayer        ),
19979bde753SMike Bayer        argnames="expr,result",
20079bde753SMike Bayer    )
20179bde753SMike Bayer    def test_like_ops(self, connection, expr, result):
202fd3c063dSMike Bayer        users = self.tables.users
203b4a83504SGord Thompson        connection.execute(
204b4a83504SGord Thompson            users.insert(),
205ebbbac0aSMike Bayer            [
206ebbbac0aSMike Bayer                {"user_id": 1, "user_name": "apples"},
207ebbbac0aSMike Bayer                {"user_id": 2, "user_name": "oranges"},
208ebbbac0aSMike Bayer                {"user_id": 3, "user_name": "bananas"},
209ebbbac0aSMike Bayer                {"user_id": 4, "user_name": "legumes"},
210ebbbac0aSMike Bayer                {"user_id": 5, "user_name": "hi % there"},
211ebbbac0aSMike Bayer            ],
2125bc1f17cSMike Bayer        )
2135bc1f17cSMike Bayer
21479bde753SMike Bayer        expr = resolve_lambda(expr, users=users)
21579bde753SMike Bayer        eq_(connection.execute(expr).fetchall(), result)
216350aed3fSMike Bayer
21747858b85SMike Bayer    @testing.requires.mod_operator_as_percent_sign
2181e1a38e7SMike Bayer    @testing.emits_warning(".*now automatically escapes.*")
219b4a83504SGord Thompson    def test_percents_in_text(self, connection):
2205bc1f17cSMike Bayer        for expr, result in (
2215bc1f17cSMike Bayer            (text("select 6 % 10"), 6),
2225bc1f17cSMike Bayer            (text("select 17 % 10"), 7),
2231e1a38e7SMike Bayer            (text("select '%'"), "%"),
2241e1a38e7SMike Bayer            (text("select '%%'"), "%%"),
2251e1a38e7SMike Bayer            (text("select '%%%'"), "%%%"),
2261e1a38e7SMike Bayer            (text("select 'hello % world'"), "hello % world"),
2275bc1f17cSMike Bayer        ):
228b4a83504SGord Thompson            eq_(connection.scalar(expr), result)
229350aed3fSMike Bayer
230b4a83504SGord Thompson    def test_ilike(self, connection):
231fd3c063dSMike Bayer        users = self.tables.users
232b4a83504SGord Thompson        connection.execute(
233b4a83504SGord Thompson            users.insert(),
234ebbbac0aSMike Bayer            [
235ebbbac0aSMike Bayer                {"user_id": 1, "user_name": "one"},
236ebbbac0aSMike Bayer                {"user_id": 2, "user_name": "TwO"},
237ebbbac0aSMike Bayer                {"user_id": 3, "user_name": "ONE"},
238ebbbac0aSMike Bayer                {"user_id": 4, "user_name": "OnE"},
239ebbbac0aSMike Bayer            ],
2406d2d5e92SMike Bayer        )
2416d2d5e92SMike Bayer
242fa80b73eSTony Locke        eq_(
243b4a83504SGord Thompson            connection.execute(
244e8600608SFederico Caselli                select(users.c.user_id).where(users.c.user_name.ilike("one"))
245b4a83504SGord Thompson            ).fetchall(),
2461e1a38e7SMike Bayer            [(1,), (3,), (4,)],
2471e1a38e7SMike Bayer        )
2486d2d5e92SMike Bayer
249fa80b73eSTony Locke        eq_(
250b4a83504SGord Thompson            connection.execute(
251e8600608SFederico Caselli                select(users.c.user_id).where(users.c.user_name.ilike("TWO"))
252b4a83504SGord Thompson            ).fetchall(),
2531e1a38e7SMike Bayer            [(2,)],
2541e1a38e7SMike Bayer        )
2556d2d5e92SMike Bayer
2561e1a38e7SMike Bayer        if testing.against("postgresql"):
257fa80b73eSTony Locke            eq_(
258b4a83504SGord Thompson                connection.execute(
259e8600608SFederico Caselli                    select(users.c.user_id).where(
260b4a83504SGord Thompson                        users.c.user_name.like("one")
261b4a83504SGord Thompson                    )
262b4a83504SGord Thompson                ).fetchall(),
2631e1a38e7SMike Bayer                [(1,)],
2641e1a38e7SMike Bayer            )
265fa80b73eSTony Locke            eq_(
266b4a83504SGord Thompson                connection.execute(
267e8600608SFederico Caselli                    select(users.c.user_id).where(
268b4a83504SGord Thompson                        users.c.user_name.like("TWO")
269b4a83504SGord Thompson                    )
270b4a83504SGord Thompson                ).fetchall(),
2711e1a38e7SMike Bayer                [],
2721e1a38e7SMike Bayer            )
2736d2d5e92SMike Bayer
274b4a83504SGord Thompson    def test_compiled_execute(self, connection):
275fd3c063dSMike Bayer        users = self.tables.users
276ebbbac0aSMike Bayer        connection.execute(users.insert(), dict(user_id=7, user_name="jack"))
2776fccdf4aSMike Bayer        s = (
2786fccdf4aSMike Bayer            select(users)
2796fccdf4aSMike Bayer            .where(users.c.user_id == bindparam("id"))
2806fccdf4aSMike Bayer            .compile(connection)
2816fccdf4aSMike Bayer        )
282ebbbac0aSMike Bayer        eq_(connection.execute(s, dict(id=7)).first()._mapping["user_id"], 7)
283bcb586a9SJason Kirtland
284b4a83504SGord Thompson    def test_compiled_insert_execute(self, connection):
285fd3c063dSMike Bayer        users = self.tables.users
286b4a83504SGord Thompson        connection.execute(
287ebbbac0aSMike Bayer            users.insert().compile(connection),
288ebbbac0aSMike Bayer            dict(user_id=7, user_name="jack"),
2896fccdf4aSMike Bayer        )
2906fccdf4aSMike Bayer        s = (
2916fccdf4aSMike Bayer            select(users)
2926fccdf4aSMike Bayer            .where(users.c.user_id == bindparam("id"))
2936fccdf4aSMike Bayer            .compile(connection)
294b4a83504SGord Thompson        )
295ebbbac0aSMike Bayer        eq_(connection.execute(s, dict(id=7)).first()._mapping["user_id"], 7)
296e8d8fa14SMike Bayer
297b4a83504SGord Thompson    def test_repeated_bindparams(self, connection):
298afa713d9SJason Kirtland        """Tests that a BindParam can be used more than once.
299bcb586a9SJason Kirtland
300afa713d9SJason Kirtland        This should be run for DB-APIs with both positional and named
301afa713d9SJason Kirtland        paramstyles.
302afa713d9SJason Kirtland        """
303fd3c063dSMike Bayer        users = self.tables.users
3047b24e458SMike Bayer
305ebbbac0aSMike Bayer        connection.execute(users.insert(), dict(user_id=7, user_name="jack"))
306ebbbac0aSMike Bayer        connection.execute(users.insert(), dict(user_id=8, user_name="fred"))
307fc17f7e6SMike Bayer
3081e1a38e7SMike Bayer        u = bindparam("userid")
3094ff4760fSGord Thompson        s = users.select().where(
3104ff4760fSGord Thompson            and_(users.c.user_name == u, users.c.user_name == u)
3114ff4760fSGord Thompson        )
312ebbbac0aSMike Bayer        r = connection.execute(s, dict(userid="fred")).fetchall()
313541b6772SMike Bayer        assert len(r) == 1
31417d3c876SJason Kirtland
315ce0d72e6SJason Kirtland    def test_bindparam_detection(self):
3161e1a38e7SMike Bayer        dialect = default.DefaultDialect(paramstyle="qmark")
31777237473SKhairi Hafsham
3181e1a38e7SMike Bayer        def prep(q):
3191e1a38e7SMike Bayer            return str(sql.text(q).compile(dialect=dialect))
320ce0d72e6SJason Kirtland
321ce0d72e6SJason Kirtland        def a_eq(got, wanted):
322ce0d72e6SJason Kirtland            if got != wanted:
3234b614b9bSMike Bayer                print("Wanted %s" % wanted)
3244b614b9bSMike Bayer                print("Received %s" % got)
3257c6c1b99SMike Bayer            self.assert_(got == wanted, got)
326ce0d72e6SJason Kirtland
3271e1a38e7SMike Bayer        a_eq(prep("select foo"), "select foo")
328ce0d72e6SJason Kirtland        a_eq(prep("time='12:30:00'"), "time='12:30:00'")
3294b614b9bSMike Bayer        a_eq(prep("time='12:30:00'"), "time='12:30:00'")
330ce0d72e6SJason Kirtland        a_eq(prep(":this:that"), ":this:that")
331ce0d72e6SJason Kirtland        a_eq(prep(":this :that"), "? ?")
332ce0d72e6SJason Kirtland        a_eq(prep("(:this),(:that :other)"), "(?),(? ?)")
333ce0d72e6SJason Kirtland        a_eq(prep("(:this),(:that:other)"), "(?),(:that:other)")
334ce0d72e6SJason Kirtland        a_eq(prep("(:this),(:that,:other)"), "(?),(?,?)")
335ce0d72e6SJason Kirtland        a_eq(prep("(:that_:other)"), "(:that_:other)")
336ce0d72e6SJason Kirtland        a_eq(prep("(:that_ :other)"), "(? ?)")
337ce0d72e6SJason Kirtland        a_eq(prep("(:that_other)"), "(?)")
338ce0d72e6SJason Kirtland        a_eq(prep("(:that$other)"), "(?)")
339ce0d72e6SJason Kirtland        a_eq(prep("(:that$:other)"), "(:that$:other)")
340ce0d72e6SJason Kirtland        a_eq(prep(".:that$ :other."), ".? ?.")
341ce0d72e6SJason Kirtland
3421e1a38e7SMike Bayer        a_eq(prep(r"select \foo"), r"select \foo")
343ce0d72e6SJason Kirtland        a_eq(prep(r"time='12\:30:00'"), r"time='12\:30:00'")
344fa6dd376SMike Bayer        a_eq(prep(r":this \:that"), "? :that")
345ce0d72e6SJason Kirtland        a_eq(prep(r"(\:that$other)"), "(:that$other)")
346ce0d72e6SJason Kirtland        a_eq(prep(r".\:that$ :other."), ".:that$ ?.")
347bcb586a9SJason Kirtland
34847858b85SMike Bayer    @testing.requires.standalone_binds
349b4a83504SGord Thompson    def test_select_from_bindparam(self, connection):
350fa80b73eSTony Locke        """Test result row processing when selecting from a plain bind
351fa80b73eSTony Locke        param."""
35267e0f356SMike Bayer
35367e0f356SMike Bayer        class MyInteger(TypeDecorator):
35467e0f356SMike Bayer            impl = Integer
3556967b450SMike Bayer            cache_ok = True
356fa80b73eSTony Locke
35767e0f356SMike Bayer            def process_bind_param(self, value, dialect):
35867e0f356SMike Bayer                return int(value[4:])
35967e0f356SMike Bayer
36067e0f356SMike Bayer            def process_result_value(self, value, dialect):
36167e0f356SMike Bayer                return "INT_%d" % value
36267e0f356SMike Bayer
36367e0f356SMike Bayer        eq_(
364c3f102c9SMike Bayer            connection.scalar(select(cast("INT_5", type_=MyInteger))),
365c3f102c9SMike Bayer            "INT_5",
36667e0f356SMike Bayer        )
36767e0f356SMike Bayer        eq_(
368b4a83504SGord Thompson            connection.scalar(
369e8600608SFederico Caselli                select(cast("INT_5", type_=MyInteger).label("foo"))
3701e1a38e7SMike Bayer            ),
3711e1a38e7SMike Bayer            "INT_5",
37267e0f356SMike Bayer        )
37367e0f356SMike Bayer
374b4a83504SGord Thompson    def test_order_by(self, connection):
375df929d36SJason Kirtland        """Exercises ORDER BY clause generation.
376df929d36SJason Kirtland
377df929d36SJason Kirtland        Tests simple, compound, aliased and DESC clauses.
378df929d36SJason Kirtland        """
379bcb586a9SJason Kirtland
380fd3c063dSMike Bayer        users = self.tables.users
381fd3c063dSMike Bayer
382ebbbac0aSMike Bayer        connection.execute(users.insert(), dict(user_id=1, user_name="c"))
383ebbbac0aSMike Bayer        connection.execute(users.insert(), dict(user_id=2, user_name="b"))
384ebbbac0aSMike Bayer        connection.execute(users.insert(), dict(user_id=3, user_name="a"))
385d174b3bdSJason Kirtland
386d174b3bdSJason Kirtland        def a_eq(executable, wanted):
387b4a83504SGord Thompson            got = list(connection.execute(executable))
38845cec095SMike Bayer            eq_(got, wanted)
389d174b3bdSJason Kirtland
390833503edSJason Kirtland        for labels in False, True:
39122f65156SGord Thompson            label_style = (
39222f65156SGord Thompson                LABEL_STYLE_NONE
39322f65156SGord Thompson                if labels is False
39422f65156SGord Thompson                else LABEL_STYLE_TABLENAME_PLUS_COL
39522f65156SGord Thompson            )
396e8600608SFederico Caselli
397e8600608SFederico Caselli            def go(stmt):
398e8600608SFederico Caselli                if labels:
39922f65156SGord Thompson                    stmt = stmt.set_label_style(label_style)
400e8600608SFederico Caselli                return stmt
401e8600608SFederico Caselli
4021e1a38e7SMike Bayer            a_eq(
403c2ea2b73SGord Thompson                users.select()
404c2ea2b73SGord Thompson                .order_by(users.c.user_id)
405c2ea2b73SGord Thompson                .set_label_style(label_style),
4061e1a38e7SMike Bayer                [(1, "c"), (2, "b"), (3, "a")],
4071e1a38e7SMike Bayer            )
4081e1a38e7SMike Bayer
4091e1a38e7SMike Bayer            a_eq(
410c2ea2b73SGord Thompson                users.select()
411c2ea2b73SGord Thompson                .order_by(users.c.user_name, users.c.user_id)
412c2ea2b73SGord Thompson                .set_label_style(label_style),
4131e1a38e7SMike Bayer                [(3, "a"), (2, "b"), (1, "c")],
4141e1a38e7SMike Bayer            )
4151e1a38e7SMike Bayer
4161e1a38e7SMike Bayer            a_eq(
417e8600608SFederico Caselli                go(
418e8600608SFederico Caselli                    select(users.c.user_id.label("foo")).order_by(
419e8600608SFederico Caselli                        users.c.user_id
420e8600608SFederico Caselli                    )
4211e1a38e7SMike Bayer                ),
4221e1a38e7SMike Bayer                [(1,), (2,), (3,)],
4231e1a38e7SMike Bayer            )
4241e1a38e7SMike Bayer
4251e1a38e7SMike Bayer            a_eq(
426e8600608SFederico Caselli                go(
427e8600608SFederico Caselli                    select(
428e8600608SFederico Caselli                        users.c.user_id.label("foo"), users.c.user_name
429e8600608SFederico Caselli                    ).order_by(users.c.user_name, users.c.user_id),
4301e1a38e7SMike Bayer                ),
4311e1a38e7SMike Bayer                [(3, "a"), (2, "b"), (1, "c")],
4321e1a38e7SMike Bayer            )
4331e1a38e7SMike Bayer
4341e1a38e7SMike Bayer            a_eq(
435c2ea2b73SGord Thompson                users.select()
436c2ea2b73SGord Thompson                .distinct()
437c2ea2b73SGord Thompson                .order_by(users.c.user_id)
438c2ea2b73SGord Thompson                .set_label_style(label_style),
4391e1a38e7SMike Bayer                [(1, "c"), (2, "b"), (3, "a")],
4401e1a38e7SMike Bayer            )
4411e1a38e7SMike Bayer
4421e1a38e7SMike Bayer            a_eq(
443e8600608SFederico Caselli                go(
444e8600608SFederico Caselli                    select(users.c.user_id.label("foo"))
445e8600608SFederico Caselli                    .distinct()
446e8600608SFederico Caselli                    .order_by(users.c.user_id),
4471e1a38e7SMike Bayer                ),
4481e1a38e7SMike Bayer                [(1,), (2,), (3,)],
4491e1a38e7SMike Bayer            )
4501e1a38e7SMike Bayer
4511e1a38e7SMike Bayer            a_eq(
452e8600608SFederico Caselli                go(
453e8600608SFederico Caselli                    select(
4541e1a38e7SMike Bayer                        users.c.user_id.label("a"),
4551e1a38e7SMike Bayer                        users.c.user_id.label("b"),
4561e1a38e7SMike Bayer                        users.c.user_name,
457e8600608SFederico Caselli                    ).order_by(users.c.user_id),
4581e1a38e7SMike Bayer                ),
4591e1a38e7SMike Bayer                [(1, 1, "c"), (2, 2, "b"), (3, 3, "a")],
4601e1a38e7SMike Bayer            )
4611e1a38e7SMike Bayer
4621e1a38e7SMike Bayer            a_eq(
463c2ea2b73SGord Thompson                users.select()
464c2ea2b73SGord Thompson                .distinct()
465c2ea2b73SGord Thompson                .order_by(desc(users.c.user_id))
466c2ea2b73SGord Thompson                .set_label_style(label_style),
4671e1a38e7SMike Bayer                [(3, "a"), (2, "b"), (1, "c")],
4681e1a38e7SMike Bayer            )
4691e1a38e7SMike Bayer
4701e1a38e7SMike Bayer            a_eq(
471e8600608SFederico Caselli                go(
472e8600608SFederico Caselli                    select(users.c.user_id.label("foo"))
473e8600608SFederico Caselli                    .distinct()
474e8600608SFederico Caselli                    .order_by(users.c.user_id.desc()),
4751e1a38e7SMike Bayer                ),
4761e1a38e7SMike Bayer                [(3,), (2,), (1,)],
4771e1a38e7SMike Bayer            )
47815ea17d7SMichael Trier
47915ea17d7SMichael Trier    @testing.requires.nullsordering
480b4a83504SGord Thompson    def test_order_by_nulls(self, connection):
48115ea17d7SMichael Trier        """Exercises ORDER BY clause generation.
48215ea17d7SMichael Trier
48315ea17d7SMichael Trier        Tests simple, compound, aliased and DESC clauses.
48415ea17d7SMichael Trier        """
48515ea17d7SMichael Trier
486fd3c063dSMike Bayer        users = self.tables.users
487fd3c063dSMike Bayer
488ebbbac0aSMike Bayer        connection.execute(users.insert(), dict(user_id=1))
489ebbbac0aSMike Bayer        connection.execute(users.insert(), dict(user_id=2, user_name="b"))
490ebbbac0aSMike Bayer        connection.execute(users.insert(), dict(user_id=3, user_name="a"))
49115ea17d7SMichael Trier
49215ea17d7SMichael Trier        def a_eq(executable, wanted):
493b4a83504SGord Thompson            got = list(connection.execute(executable))
49415ea17d7SMichael Trier            eq_(got, wanted)
49515ea17d7SMichael Trier
49615ea17d7SMichael Trier        for labels in False, True:
49722f65156SGord Thompson            label_style = (
49822f65156SGord Thompson                LABEL_STYLE_NONE
49922f65156SGord Thompson                if labels is False
50022f65156SGord Thompson                else LABEL_STYLE_TABLENAME_PLUS_COL
50122f65156SGord Thompson            )
5021e1a38e7SMike Bayer            a_eq(
503c2ea2b73SGord Thompson                users.select()
504c2ea2b73SGord Thompson                .order_by(users.c.user_name.nulls_first())
505c2ea2b73SGord Thompson                .set_label_style(label_style),
5061e1a38e7SMike Bayer                [(1, None), (3, "a"), (2, "b")],
5071e1a38e7SMike Bayer            )
50815ea17d7SMichael Trier
5091e1a38e7SMike Bayer            a_eq(
510c2ea2b73SGord Thompson                users.select()
511c2ea2b73SGord Thompson                .order_by(users.c.user_name.nulls_last())
512c2ea2b73SGord Thompson                .set_label_style(label_style),
5131e1a38e7SMike Bayer                [(3, "a"), (2, "b"), (1, None)],
5141e1a38e7SMike Bayer            )
51515ea17d7SMichael Trier
5161e1a38e7SMike Bayer            a_eq(
517c2ea2b73SGord Thompson                users.select()
518c2ea2b73SGord Thompson                .order_by(asc(users.c.user_name).nulls_first())
519c2ea2b73SGord Thompson                .set_label_style(label_style),
5201e1a38e7SMike Bayer                [(1, None), (3, "a"), (2, "b")],
5211e1a38e7SMike Bayer            )
52215ea17d7SMichael Trier
5231e1a38e7SMike Bayer            a_eq(
524c2ea2b73SGord Thompson                users.select()
525c2ea2b73SGord Thompson                .order_by(asc(users.c.user_name).nulls_last())
526c2ea2b73SGord Thompson                .set_label_style(label_style),
5271e1a38e7SMike Bayer                [(3, "a"), (2, "b"), (1, None)],
5281e1a38e7SMike Bayer            )
52915ea17d7SMichael Trier
5301e1a38e7SMike Bayer            a_eq(
531c2ea2b73SGord Thompson                users.select()
532c2ea2b73SGord Thompson                .order_by(users.c.user_name.desc().nulls_first())
533c2ea2b73SGord Thompson                .set_label_style(label_style),
5341e1a38e7SMike Bayer                [(1, None), (2, "b"), (3, "a")],
5351e1a38e7SMike Bayer            )
53615ea17d7SMichael Trier
537fa80b73eSTony Locke            a_eq(
538c2ea2b73SGord Thompson                users.select()
539c2ea2b73SGord Thompson                .order_by(users.c.user_name.desc().nulls_last())
540c2ea2b73SGord Thompson                .set_label_style(label_style),
5411e1a38e7SMike Bayer                [(2, "b"), (3, "a"), (1, None)],
5421e1a38e7SMike Bayer            )
54315ea17d7SMichael Trier
544fa80b73eSTony Locke            a_eq(
545c2ea2b73SGord Thompson                users.select()
546c2ea2b73SGord Thompson                .order_by(desc(users.c.user_name).nulls_first())
547c2ea2b73SGord Thompson                .set_label_style(label_style),
5481e1a38e7SMike Bayer                [(1, None), (2, "b"), (3, "a")],
5491e1a38e7SMike Bayer            )
55015ea17d7SMichael Trier
5511e1a38e7SMike Bayer            a_eq(
552c2ea2b73SGord Thompson                users.select()
553c2ea2b73SGord Thompson                .order_by(desc(users.c.user_name).nulls_last())
554c2ea2b73SGord Thompson                .set_label_style(label_style),
5551e1a38e7SMike Bayer                [(2, "b"), (3, "a"), (1, None)],
5561e1a38e7SMike Bayer            )
55715ea17d7SMichael Trier
558fa80b73eSTony Locke            a_eq(
559c2ea2b73SGord Thompson                users.select()
560c2ea2b73SGord Thompson                .order_by(
561c2ea2b73SGord Thompson                    users.c.user_name.nulls_first(),
562c2ea2b73SGord Thompson                    users.c.user_id,
563c2ea2b73SGord Thompson                )
564c2ea2b73SGord Thompson                .set_label_style(label_style),
5651e1a38e7SMike Bayer                [(1, None), (3, "a"), (2, "b")],
5661e1a38e7SMike Bayer            )
56715ea17d7SMichael Trier
568fa80b73eSTony Locke            a_eq(
569c2ea2b73SGord Thompson                users.select()
570c2ea2b73SGord Thompson                .order_by(users.c.user_name.nulls_last(), users.c.user_id)
571c2ea2b73SGord Thompson                .set_label_style(label_style),
5721e1a38e7SMike Bayer                [(3, "a"), (2, "b"), (1, None)],
5731e1a38e7SMike Bayer            )
57415ea17d7SMichael Trier
575b4a83504SGord Thompson    def test_in_filtering(self, connection):
576ed4fc64bSMike Bayer        """test the behavior of the in_() function."""
577fd3c063dSMike Bayer        users = self.tables.users
578bcb586a9SJason Kirtland
579ebbbac0aSMike Bayer        connection.execute(users.insert(), dict(user_id=7, user_name="jack"))
580ebbbac0aSMike Bayer        connection.execute(users.insert(), dict(user_id=8, user_name="fred"))
581ebbbac0aSMike Bayer        connection.execute(users.insert(), dict(user_id=9, user_name=None))
582bcb586a9SJason Kirtland
5834ff4760fSGord Thompson        s = users.select().where(users.c.user_name.in_([]))
584b4a83504SGord Thompson        r = connection.execute(s).fetchall()
585bc58df9cSMike Bayer        # No username is in empty set
586bc58df9cSMike Bayer        assert len(r) == 0
587bcb586a9SJason Kirtland
5884ff4760fSGord Thompson        s = users.select().where(not_(users.c.user_name.in_([])))
589b4a83504SGord Thompson        r = connection.execute(s).fetchall()
590f3b6f4f8SMike Bayer        assert len(r) == 3
591bcb586a9SJason Kirtland
5924ff4760fSGord Thompson        s = users.select().where(users.c.user_name.in_(["jack", "fred"]))
593b4a83504SGord Thompson        r = connection.execute(s).fetchall()
594bc58df9cSMike Bayer        assert len(r) == 2
595bcb586a9SJason Kirtland
5964ff4760fSGord Thompson        s = users.select().where(not_(users.c.user_name.in_(["jack", "fred"])))
597b4a83504SGord Thompson        r = connection.execute(s).fetchall()
598bc58df9cSMike Bayer        # Null values are not outside any set
599bc58df9cSMike Bayer        assert len(r) == 0
600bcb586a9SJason Kirtland
601b4a83504SGord Thompson    def test_expanding_in(self, connection):
602fd3c063dSMike Bayer        users = self.tables.users
603b4a83504SGord Thompson        connection.execute(
6047d9f241dSMike Bayer            users.insert(),
6057d9f241dSMike Bayer            [
6061e1a38e7SMike Bayer                dict(user_id=7, user_name="jack"),
6071e1a38e7SMike Bayer                dict(user_id=8, user_name="fred"),
6081e1a38e7SMike Bayer                dict(user_id=9, user_name=None),
6091e1a38e7SMike Bayer            ],
6107d9f241dSMike Bayer        )
6117d9f241dSMike Bayer
612b4a83504SGord Thompson        stmt = (
613e8600608SFederico Caselli            select(users)
614b4a83504SGord Thompson            .where(users.c.user_name.in_(bindparam("uname", expanding=True)))
615b4a83504SGord Thompson            .order_by(users.c.user_id)
616b4a83504SGord Thompson        )
6177d9f241dSMike Bayer
618b4a83504SGord Thompson        eq_(
619b4a83504SGord Thompson            connection.execute(stmt, {"uname": ["jack"]}).fetchall(),
620b4a83504SGord Thompson            [(7, "jack")],
621b4a83504SGord Thompson        )
6227d9f241dSMike Bayer
623b4a83504SGord Thompson        eq_(
624b4a83504SGord Thompson            connection.execute(stmt, {"uname": ["jack", "fred"]}).fetchall(),
625b4a83504SGord Thompson            [(7, "jack"), (8, "fred")],
626b4a83504SGord Thompson        )
6277d9f241dSMike Bayer
628b4a83504SGord Thompson        eq_(connection.execute(stmt, {"uname": []}).fetchall(), [])
6291e1a38e7SMike Bayer
630b4a83504SGord Thompson        assert_raises_message(
631b4a83504SGord Thompson            exc.StatementError,
632b4a83504SGord Thompson            "'expanding' parameters can't be used with executemany()",
633b4a83504SGord Thompson            connection.execute,
634b4a83504SGord Thompson            users.update().where(
635b4a83504SGord Thompson                users.c.user_name.in_(bindparam("uname", expanding=True))
636b4a83504SGord Thompson            ),
637b4a83504SGord Thompson            [{"uname": ["fred"]}, {"uname": ["ed"]}],
638b4a83504SGord Thompson        )
6397d9f241dSMike Bayer
640e447582bSMike Bayer    @testing.requires.no_quoting_special_bind_names
641b4a83504SGord Thompson    def test_expanding_in_special_chars(self, connection):
642fd3c063dSMike Bayer        users = self.tables.users
643b4a83504SGord Thompson        connection.execute(
644e447582bSMike Bayer            users.insert(),
645e447582bSMike Bayer            [
6461e1a38e7SMike Bayer                dict(user_id=7, user_name="jack"),
6471e1a38e7SMike Bayer                dict(user_id=8, user_name="fred"),
6481e1a38e7SMike Bayer            ],
649e447582bSMike Bayer        )
650e447582bSMike Bayer
651b4a83504SGord Thompson        stmt = (
652e8600608SFederico Caselli            select(users)
653b4a83504SGord Thompson            .where(users.c.user_name.in_(bindparam("u35", expanding=True)))
654b4a83504SGord Thompson            .where(users.c.user_id == bindparam("u46"))
655b4a83504SGord Thompson            .order_by(users.c.user_id)
656b4a83504SGord Thompson        )
657e447582bSMike Bayer
658b4a83504SGord Thompson        eq_(
659b4a83504SGord Thompson            connection.execute(
660b4a83504SGord Thompson                stmt, {"u35": ["jack", "fred"], "u46": 7}
661b4a83504SGord Thompson            ).fetchall(),
662b4a83504SGord Thompson            [(7, "jack")],
663b4a83504SGord Thompson        )
664e447582bSMike Bayer
665b4a83504SGord Thompson        stmt = (
666e8600608SFederico Caselli            select(users)
667b4a83504SGord Thompson            .where(users.c.user_name.in_(bindparam("u.35", expanding=True)))
668b4a83504SGord Thompson            .where(users.c.user_id == bindparam("u.46"))
669b4a83504SGord Thompson            .order_by(users.c.user_id)
670b4a83504SGord Thompson        )
671e447582bSMike Bayer
672b4a83504SGord Thompson        eq_(
673b4a83504SGord Thompson            connection.execute(
674b4a83504SGord Thompson                stmt, {"u.35": ["jack", "fred"], "u.46": 7}
675b4a83504SGord Thompson            ).fetchall(),
676b4a83504SGord Thompson            [(7, "jack")],
677b4a83504SGord Thompson        )
678e447582bSMike Bayer
679b4a83504SGord Thompson    def test_expanding_in_multiple(self, connection):
680fd3c063dSMike Bayer        users = self.tables.users
681fd3c063dSMike Bayer
682b4a83504SGord Thompson        connection.execute(
683e447582bSMike Bayer            users.insert(),
684e447582bSMike Bayer            [
6851e1a38e7SMike Bayer                dict(user_id=7, user_name="jack"),
6861e1a38e7SMike Bayer                dict(user_id=8, user_name="fred"),
6871e1a38e7SMike Bayer                dict(user_id=9, user_name="ed"),
6881e1a38e7SMike Bayer            ],
689e447582bSMike Bayer        )
690e447582bSMike Bayer
691b4a83504SGord Thompson        stmt = (
692e8600608SFederico Caselli            select(users)
693b4a83504SGord Thompson            .where(users.c.user_name.in_(bindparam("uname", expanding=True)))
694b4a83504SGord Thompson            .where(users.c.user_id.in_(bindparam("userid", expanding=True)))
695b4a83504SGord Thompson            .order_by(users.c.user_id)
696b4a83504SGord Thompson        )
697e447582bSMike Bayer
698b4a83504SGord Thompson        eq_(
699b4a83504SGord Thompson            connection.execute(
700b4a83504SGord Thompson                stmt, {"uname": ["jack", "fred", "ed"], "userid": [8, 9]}
701b4a83504SGord Thompson            ).fetchall(),
702b4a83504SGord Thompson            [(8, "fred"), (9, "ed")],
703b4a83504SGord Thompson        )
704e447582bSMike Bayer
705b4a83504SGord Thompson    def test_expanding_in_repeated(self, connection):
706fd3c063dSMike Bayer        users = self.tables.users
707fd3c063dSMike Bayer
708b4a83504SGord Thompson        connection.execute(
709c4957697SMike Bayer            users.insert(),
710c4957697SMike Bayer            [
7111e1a38e7SMike Bayer                dict(user_id=7, user_name="jack"),
7121e1a38e7SMike Bayer                dict(user_id=8, user_name="fred"),
7131e1a38e7SMike Bayer                dict(user_id=9, user_name="ed"),
7141e1a38e7SMike Bayer            ],
715c4957697SMike Bayer        )
716c4957697SMike Bayer
717b4a83504SGord Thompson        stmt = (
718e8600608SFederico Caselli            select(users)
719b4a83504SGord Thompson            .where(
720b4a83504SGord Thompson                users.c.user_name.in_(bindparam("uname", expanding=True))
721b4a83504SGord Thompson                | users.c.user_name.in_(bindparam("uname2", expanding=True))
722b4a83504SGord Thompson            )
723b4a83504SGord Thompson            .where(users.c.user_id == 8)
724b4a83504SGord Thompson        )
725b4a83504SGord Thompson        stmt = stmt.union(
726e8600608SFederico Caselli            select(users)
727b4a83504SGord Thompson            .where(
728b4a83504SGord Thompson                users.c.user_name.in_(bindparam("uname", expanding=True))
729b4a83504SGord Thompson                | users.c.user_name.in_(bindparam("uname2", expanding=True))
730c4957697SMike Bayer            )
731b4a83504SGord Thompson            .where(users.c.user_id == 9)
732b4a83504SGord Thompson        ).order_by("user_id")
733b4a83504SGord Thompson
734b4a83504SGord Thompson        eq_(
735b4a83504SGord Thompson            connection.execute(
736b4a83504SGord Thompson                stmt,
737b4a83504SGord Thompson                {
738b4a83504SGord Thompson                    "uname": ["jack", "fred"],
739b4a83504SGord Thompson                    "uname2": ["ed"],
740b4a83504SGord Thompson                    "userid": [8, 9],
741b4a83504SGord Thompson                },
742b4a83504SGord Thompson            ).fetchall(),
743b4a83504SGord Thompson            [(8, "fred"), (9, "ed")],
744b4a83504SGord Thompson        )
745c4957697SMike Bayer
7467d9f241dSMike Bayer    @testing.requires.tuple_in
747b4a83504SGord Thompson    def test_expanding_in_composite(self, connection):
748fd3c063dSMike Bayer        users = self.tables.users
749fd3c063dSMike Bayer
750b4a83504SGord Thompson        connection.execute(
7517d9f241dSMike Bayer            users.insert(),
7527d9f241dSMike Bayer            [
7531e1a38e7SMike Bayer                dict(user_id=7, user_name="jack"),
7541e1a38e7SMike Bayer                dict(user_id=8, user_name="fred"),
7551e1a38e7SMike Bayer                dict(user_id=9, user_name=None),
7561e1a38e7SMike Bayer            ],
7577d9f241dSMike Bayer        )
7587d9f241dSMike Bayer
759b4a83504SGord Thompson        stmt = (
760e8600608SFederico Caselli            select(users)
761b4a83504SGord Thompson            .where(
762b4a83504SGord Thompson                tuple_(users.c.user_id, users.c.user_name).in_(
763b4a83504SGord Thompson                    bindparam("uname", expanding=True)
7641e1a38e7SMike Bayer                )
7651e1a38e7SMike Bayer            )
766b4a83504SGord Thompson            .order_by(users.c.user_id)
767b4a83504SGord Thompson        )
7687d9f241dSMike Bayer
769b4a83504SGord Thompson        eq_(
770b4a83504SGord Thompson            connection.execute(stmt, {"uname": [(7, "jack")]}).fetchall(),
771b4a83504SGord Thompson            [(7, "jack")],
772b4a83504SGord Thompson        )
7737d9f241dSMike Bayer
774b4a83504SGord Thompson        eq_(
775b4a83504SGord Thompson            connection.execute(
776b4a83504SGord Thompson                stmt, {"uname": [(7, "jack"), (8, "fred")]}
777b4a83504SGord Thompson            ).fetchall(),
778b4a83504SGord Thompson            [(7, "jack"), (8, "fred")],
779b4a83504SGord Thompson        )
7807d9f241dSMike Bayer
781b4a83504SGord Thompson    def test_expanding_in_dont_alter_compiled(self, connection):
782debeea97SFederico Caselli        """test for issue #5048"""
783c6554ac5SMike Bayer
784c6554ac5SMike Bayer        class NameWithProcess(TypeDecorator):
785c6554ac5SMike Bayer            impl = String
7866967b450SMike Bayer            cache_ok = True
787c6554ac5SMike Bayer
788c6554ac5SMike Bayer            def process_bind_param(self, value, dialect):
789c6554ac5SMike Bayer                return value[3:]
790c6554ac5SMike Bayer
791c6554ac5SMike Bayer        users = Table(
792fd3c063dSMike Bayer            "users",
793c6554ac5SMike Bayer            MetaData(),
794c6554ac5SMike Bayer            Column("user_id", Integer, primary_key=True),
795c6554ac5SMike Bayer            Column("user_name", NameWithProcess()),
796c6554ac5SMike Bayer        )
797c6554ac5SMike Bayer
798b4a83504SGord Thompson        connection.execute(
799b4a83504SGord Thompson            users.insert(),
800b4a83504SGord Thompson            [
801b4a83504SGord Thompson                dict(user_id=7, user_name="AB jack"),
802b4a83504SGord Thompson                dict(user_id=8, user_name="BE fred"),
803b4a83504SGord Thompson                dict(user_id=9, user_name="GP ed"),
804b4a83504SGord Thompson            ],
805b4a83504SGord Thompson        )
806c6554ac5SMike Bayer
807b4a83504SGord Thompson        stmt = (
808e8600608SFederico Caselli            select(users)
809b4a83504SGord Thompson            .where(users.c.user_name.in_(bindparam("uname", expanding=True)))
810b4a83504SGord Thompson            .order_by(users.c.user_id)
811b4a83504SGord Thompson        )
812c6554ac5SMike Bayer
813b4a83504SGord Thompson        compiled = stmt.compile(testing.db)
814b4a83504SGord Thompson        eq_(len(compiled._bind_processors), 1)
815c6554ac5SMike Bayer
816b4a83504SGord Thompson        eq_(
817b4a83504SGord Thompson            connection.execute(
818b4a83504SGord Thompson                compiled, {"uname": ["HJ jack", "RR fred"]}
819b4a83504SGord Thompson            ).fetchall(),
820b4a83504SGord Thompson            [(7, "jack"), (8, "fred")],
821b4a83504SGord Thompson        )
822c6554ac5SMike Bayer
823b4a83504SGord Thompson        eq_(len(compiled._bind_processors), 1)
824c6554ac5SMike Bayer
8251e1a38e7SMike Bayer    @testing.skip_if(["mssql"])
826b4a83504SGord Thompson    def test_bind_in(self, connection):
827318f47dcSMike Bayer        """test calling IN against a bind parameter.
828350aed3fSMike Bayer
829318f47dcSMike Bayer        this isn't allowed on several platforms since we
830318f47dcSMike Bayer        generate ? = ?.
831350aed3fSMike Bayer
832318f47dcSMike Bayer        """
8337b24e458SMike Bayer
834fd3c063dSMike Bayer        users = self.tables.users
835fd3c063dSMike Bayer
836ebbbac0aSMike Bayer        connection.execute(users.insert(), dict(user_id=7, user_name="jack"))
837ebbbac0aSMike Bayer        connection.execute(users.insert(), dict(user_id=8, user_name="fred"))
838ebbbac0aSMike Bayer        connection.execute(users.insert(), dict(user_id=9, user_name=None))
839bcb586a9SJason Kirtland
84060e7034aSMike Bayer        u = bindparam("search_key", type_=String)
841bcb586a9SJason Kirtland
8424ff4760fSGord Thompson        s = users.select().where(not_(u.in_([])))
843ebbbac0aSMike Bayer        r = connection.execute(s, dict(search_key="john")).fetchall()
844bc58df9cSMike Bayer        assert len(r) == 3
845ebbbac0aSMike Bayer        r = connection.execute(s, dict(search_key=None)).fetchall()
846f3b6f4f8SMike Bayer        assert len(r) == 3
847350aed3fSMike Bayer
848b4a83504SGord Thompson    def test_literal_in(self, connection):
849318f47dcSMike Bayer        """similar to test_bind_in but use a bind with a value."""
850bcb586a9SJason Kirtland
851fd3c063dSMike Bayer        users = self.tables.users
852fd3c063dSMike Bayer
853ebbbac0aSMike Bayer        connection.execute(users.insert(), dict(user_id=7, user_name="jack"))
854ebbbac0aSMike Bayer        connection.execute(users.insert(), dict(user_id=8, user_name="fred"))
855ebbbac0aSMike Bayer        connection.execute(users.insert(), dict(user_id=9, user_name=None))
856bcb586a9SJason Kirtland
8574ff4760fSGord Thompson        s = users.select().where(not_(literal("john").in_([])))
858b4a83504SGord Thompson        r = connection.execute(s).fetchall()
859318f47dcSMike Bayer        assert len(r) == 3
860350aed3fSMike Bayer
86195c02143SMike Bayer    @testing.requires.boolean_col_expressions
862b4a83504SGord Thompson    def test_empty_in_filtering_static(self, connection):
86322ba1c43SMike Bayer        """test the behavior of the in_() function when
86495c02143SMike Bayer        comparing against an empty collection, specifically
86595c02143SMike Bayer        that a proper boolean value is generated.
866350aed3fSMike Bayer
86795c02143SMike Bayer        """
868fd3c063dSMike Bayer        users = self.tables.users
86911619ad8SAnts Aasma
870b4a83504SGord Thompson        connection.execute(
871b4a83504SGord Thompson            users.insert(),
872b4a83504SGord Thompson            [
873b4a83504SGord Thompson                {"user_id": 7, "user_name": "jack"},
874b4a83504SGord Thompson                {"user_id": 8, "user_name": "ed"},
875b4a83504SGord Thompson                {"user_id": 9, "user_name": None},
876b4a83504SGord Thompson            ],
877b4a83504SGord Thompson        )
878f3b6f4f8SMike Bayer
8794ff4760fSGord Thompson        s = users.select().where(users.c.user_name.in_([]) == True)  # noqa
880b4a83504SGord Thompson        r = connection.execute(s).fetchall()
881b4a83504SGord Thompson        assert len(r) == 0
8824ff4760fSGord Thompson        s = users.select().where(users.c.user_name.in_([]) == False)  # noqa
883b4a83504SGord Thompson        r = connection.execute(s).fetchall()
884b4a83504SGord Thompson        assert len(r) == 3
8854ff4760fSGord Thompson        s = users.select().where(users.c.user_name.in_([]) == None)  # noqa
886b4a83504SGord Thompson        r = connection.execute(s).fetchall()
887b4a83504SGord Thompson        assert len(r) == 0
88811619ad8SAnts Aasma
889fa80b73eSTony Locke
890326f2e4fSMike Bayerclass RequiredBindTest(fixtures.TablesTest):
891326f2e4fSMike Bayer    run_create_tables = None
892326f2e4fSMike Bayer    run_deletes = None
893326f2e4fSMike Bayer
894326f2e4fSMike Bayer    @classmethod
895326f2e4fSMike Bayer    def define_tables(cls, metadata):
896fa80b73eSTony Locke        Table(
8971e1a38e7SMike Bayer            "foo",
8981e1a38e7SMike Bayer            metadata,
8991e1a38e7SMike Bayer            Column("id", Integer, primary_key=True),
9001e1a38e7SMike Bayer            Column("data", String(50)),
9011e1a38e7SMike Bayer            Column("x", Integer),
902fa80b73eSTony Locke        )
903326f2e4fSMike Bayer
904326f2e4fSMike Bayer    def _assert_raises(self, stmt, params):
905ba5cbf93SMike Bayer        with testing.db.connect() as conn:
906ba5cbf93SMike Bayer            assert_raises_message(
907ba5cbf93SMike Bayer                exc.StatementError,
908ba5cbf93SMike Bayer                "A value is required for bind parameter 'x'",
909ba5cbf93SMike Bayer                conn.execute,
910ba5cbf93SMike Bayer                stmt,
911ba5cbf93SMike Bayer                params,
912ba5cbf93SMike Bayer            )
913326f2e4fSMike Bayer
914326f2e4fSMike Bayer    def test_insert(self):
915fa80b73eSTony Locke        stmt = self.tables.foo.insert().values(
9161e1a38e7SMike Bayer            x=bindparam("x"), data=bindparam("data")
9171e1a38e7SMike Bayer        )
9181e1a38e7SMike Bayer        self._assert_raises(stmt, {"data": "data"})
919326f2e4fSMike Bayer
920326f2e4fSMike Bayer    def test_select_where(self):
9211e1a38e7SMike Bayer        stmt = (
922e8600608SFederico Caselli            select(self.tables.foo)
9231e1a38e7SMike Bayer            .where(self.tables.foo.c.data == bindparam("data"))
9241e1a38e7SMike Bayer            .where(self.tables.foo.c.x == bindparam("x"))
9251e1a38e7SMike Bayer        )
9261e1a38e7SMike Bayer        self._assert_raises(stmt, {"data": "data"})
927326f2e4fSMike Bayer
928626ae5b7SMike Bayer    @testing.requires.standalone_binds
929326f2e4fSMike Bayer    def test_select_columns(self):
930e8600608SFederico Caselli        stmt = select(bindparam("data"), bindparam("x"))
9311e1a38e7SMike Bayer        self._assert_raises(stmt, {"data": "data"})
932326f2e4fSMike Bayer
933326f2e4fSMike Bayer    def test_text(self):
934326f2e4fSMike Bayer        stmt = text("select * from foo where x=:x and data=:data1")
9351e1a38e7SMike Bayer        self._assert_raises(stmt, {"data1": "data"})
936326f2e4fSMike Bayer
937326f2e4fSMike Bayer    def test_required_flag(self):
9381e1a38e7SMike Bayer        is_(bindparam("foo").required, True)
9391e1a38e7SMike Bayer        is_(bindparam("foo", required=False).required, False)
9401e1a38e7SMike Bayer        is_(bindparam("foo", "bar").required, False)
9411e1a38e7SMike Bayer        is_(bindparam("foo", "bar", required=True).required, True)
942326f2e4fSMike Bayer
9431e1a38e7SMike Bayer        def c():
9441e1a38e7SMike Bayer            return None
9451e1a38e7SMike Bayer
9461e1a38e7SMike Bayer        is_(bindparam("foo", callable_=c, required=True).required, True)
9471e1a38e7SMike Bayer        is_(bindparam("foo", callable_=c).required, False)
9481e1a38e7SMike Bayer        is_(bindparam("foo", callable_=c, required=False).required, False)
949326f2e4fSMike Bayer
950fa80b73eSTony Locke
951fd3c063dSMike Bayerclass LimitTest(fixtures.TablesTest):
9522f150beeSMike Bayer    __backend__ = True
9539eafb43cSMike Bayer
95445cec095SMike Bayer    @classmethod
955fd3c063dSMike Bayer    def define_tables(cls, metadata):
956fd3c063dSMike Bayer        Table(
957fd3c063dSMike Bayer            "users",
9581e1a38e7SMike Bayer            metadata,
9591e1a38e7SMike Bayer            Column("user_id", INT, primary_key=True),
9601e1a38e7SMike Bayer            Column("user_name", VARCHAR(20)),
9619eafb43cSMike Bayer        )
962fd3c063dSMike Bayer        Table(
963fd3c063dSMike Bayer            "addresses",
9641e1a38e7SMike Bayer            metadata,
9651e1a38e7SMike Bayer            Column("address_id", Integer, primary_key=True),
966fd3c063dSMike Bayer            Column("user_id", Integer, ForeignKey("users.user_id")),
9671e1a38e7SMike Bayer            Column("address", String(30)),
9681e1a38e7SMike Bayer        )
969350aed3fSMike Bayer
97045cec095SMike Bayer    @classmethod
971fd3c063dSMike Bayer    def insert_data(cls, connection):
972fd3c063dSMike Bayer        users, addresses = cls.tables("users", "addresses")
973fd3c063dSMike Bayer        conn = connection
974ebbbac0aSMike Bayer        conn.execute(users.insert(), dict(user_id=1, user_name="john"))
975fd3c063dSMike Bayer        conn.execute(
976ebbbac0aSMike Bayer            addresses.insert(), dict(address_id=1, user_id=1, address="addr1")
977fd3c063dSMike Bayer        )
978ebbbac0aSMike Bayer        conn.execute(users.insert(), dict(user_id=2, user_name="jack"))
979fd3c063dSMike Bayer        conn.execute(
980ebbbac0aSMike Bayer            addresses.insert(), dict(address_id=2, user_id=2, address="addr1")
981fd3c063dSMike Bayer        )
982ebbbac0aSMike Bayer        conn.execute(users.insert(), dict(user_id=3, user_name="ed"))
983fd3c063dSMike Bayer        conn.execute(
984ebbbac0aSMike Bayer            addresses.insert(), dict(address_id=3, user_id=3, address="addr2")
985fd3c063dSMike Bayer        )
986ebbbac0aSMike Bayer        conn.execute(users.insert(), dict(user_id=4, user_name="wendy"))
987fd3c063dSMike Bayer        conn.execute(
988ebbbac0aSMike Bayer            addresses.insert(), dict(address_id=4, user_id=4, address="addr3")
989fd3c063dSMike Bayer        )
990ebbbac0aSMike Bayer        conn.execute(users.insert(), dict(user_id=5, user_name="laura"))
991fd3c063dSMike Bayer        conn.execute(
992ebbbac0aSMike Bayer            addresses.insert(), dict(address_id=5, user_id=5, address="addr4")
993fd3c063dSMike Bayer        )
994ebbbac0aSMike Bayer        conn.execute(users.insert(), dict(user_id=6, user_name="ralph"))
995fd3c063dSMike Bayer        conn.execute(
996ebbbac0aSMike Bayer            addresses.insert(), dict(address_id=6, user_id=6, address="addr5")
997fd3c063dSMike Bayer        )
998ebbbac0aSMike Bayer        conn.execute(users.insert(), dict(user_id=7, user_name="fido"))
999fd3c063dSMike Bayer        conn.execute(
1000ebbbac0aSMike Bayer            addresses.insert(), dict(address_id=7, user_id=7, address="addr5")
1001fd3c063dSMike Bayer        )
10029eafb43cSMike Bayer
1003b4a83504SGord Thompson    def test_select_limit(self, connection):
1004fd3c063dSMike Bayer        users, addresses = self.tables("users", "addresses")
1005b4a83504SGord Thompson        r = connection.execute(
1006c2ea2b73SGord Thompson            users.select().limit(3).order_by(users.c.user_id)
1007b4a83504SGord Thompson        ).fetchall()
10081e1a38e7SMike Bayer        self.assert_(r == [(1, "john"), (2, "jack"), (3, "ed")], repr(r))
10099eafb43cSMike Bayer
101095c02143SMike Bayer    @testing.requires.offset
1011b4a83504SGord Thompson    def test_select_limit_offset(self, connection):
10128924a0e4SMichael Trier        """Test the interaction between limit and offset"""
10138924a0e4SMichael Trier
1014fd3c063dSMike Bayer        users, addresses = self.tables("users", "addresses")
1015fd3c063dSMike Bayer
1016b4a83504SGord Thompson        r = connection.execute(
1017c2ea2b73SGord Thompson            users.select().limit(3).offset(2).order_by(users.c.user_id)
1018b4a83504SGord Thompson        ).fetchall()
10191e1a38e7SMike Bayer        self.assert_(r == [(3, "ed"), (4, "wendy"), (5, "laura")])
1020b4a83504SGord Thompson        r = connection.execute(
1021c2ea2b73SGord Thompson            users.select().offset(5).order_by(users.c.user_id)
1022b4a83504SGord Thompson        ).fetchall()
10231e1a38e7SMike Bayer        self.assert_(r == [(6, "ralph"), (7, "fido")])
10249eafb43cSMike Bayer
1025b4a83504SGord Thompson    def test_select_distinct_limit(self, connection):
10269eafb43cSMike Bayer        """Test the interaction between limit and distinct"""
10279eafb43cSMike Bayer
1028fd3c063dSMike Bayer        users, addresses = self.tables("users", "addresses")
1029fd3c063dSMike Bayer
1030fa80b73eSTony Locke        r = sorted(
10311e1a38e7SMike Bayer            [
10321e1a38e7SMike Bayer                x[0]
1033b4a83504SGord Thompson                for x in connection.execute(
1034e8600608SFederico Caselli                    select(addresses.c.address).distinct().limit(3)
1035b4a83504SGord Thompson                )
10361e1a38e7SMike Bayer            ]
10371e1a38e7SMike Bayer        )
10389eafb43cSMike Bayer        self.assert_(len(r) == 3, repr(r))
10399eafb43cSMike Bayer        self.assert_(r[0] != r[1] and r[1] != r[2], repr(r))
10409eafb43cSMike Bayer
104195c02143SMike Bayer    @testing.requires.offset
1042b4a83504SGord Thompson    def test_select_distinct_offset(self, connection):
10438924a0e4SMichael Trier        """Test the interaction between distinct and offset"""
10449eafb43cSMike Bayer
1045fd3c063dSMike Bayer        users, addresses = self.tables("users", "addresses")
1046fd3c063dSMike Bayer
1047fa80b73eSTony Locke        r = sorted(
10481e1a38e7SMike Bayer            [
10491e1a38e7SMike Bayer                x[0]
1050b4a83504SGord Thompson                for x in connection.execute(
1051e8600608SFederico Caselli                    select(addresses.c.address)
1052b4a83504SGord Thompson                    .distinct()
1053b4a83504SGord Thompson                    .offset(1)
1054b4a83504SGord Thompson                    .order_by(addresses.c.address)
1055b4a83504SGord Thompson                ).fetchall()
10561e1a38e7SMike Bayer            ]
10571e1a38e7SMike Bayer        )
10588433a48fSMike Bayer        eq_(len(r), 4)
10599eafb43cSMike Bayer        self.assert_(r[0] != r[1] and r[1] != r[2] and r[2] != [3], repr(r))
10609eafb43cSMike Bayer
106195c02143SMike Bayer    @testing.requires.offset
1062b4a83504SGord Thompson    def test_select_distinct_limit_offset(self, connection):
10639eafb43cSMike Bayer        """Test the interaction between limit and limit/offset"""
10649eafb43cSMike Bayer
1065fd3c063dSMike Bayer        users, addresses = self.tables("users", "addresses")
1066fd3c063dSMike Bayer
1067b4a83504SGord Thompson        r = connection.execute(
1068e8600608SFederico Caselli            select(addresses.c.address)
10691e1a38e7SMike Bayer            .order_by(addresses.c.address)
10701e1a38e7SMike Bayer            .distinct()
10711e1a38e7SMike Bayer            .offset(2)
10721e1a38e7SMike Bayer            .limit(3)
1073b4a83504SGord Thompson        ).fetchall()
10749eafb43cSMike Bayer        self.assert_(len(r) == 3, repr(r))
10759eafb43cSMike Bayer        self.assert_(r[0] != r[1] and r[1] != r[2], repr(r))
10769eafb43cSMike Bayer
1077fa80b73eSTony Locke
1078fd3c063dSMike Bayerclass CompoundTest(fixtures.TablesTest):
1079bb5f4392SMike Bayer
1080fa80b73eSTony Locke    """test compound statements like UNION, INTERSECT, particularly their
1081fa80b73eSTony Locke    ability to nest on different databases."""
10822f150beeSMike Bayer
10832f150beeSMike Bayer    __backend__ = True
10842f150beeSMike Bayer
1085fd3c063dSMike Bayer    run_inserts = "each"
1086fd3c063dSMike Bayer
108745cec095SMike Bayer    @classmethod
1088fd3c063dSMike Bayer    def define_tables(cls, metadata):
1089fd3c063dSMike Bayer        Table(
10901e1a38e7SMike Bayer            "t1",
10911e1a38e7SMike Bayer            metadata,
1092fa80b73eSTony Locke            Column(
10931e1a38e7SMike Bayer                "col1",
10941e1a38e7SMike Bayer                Integer,
10951e1a38e7SMike Bayer                test_needs_autoincrement=True,
10961e1a38e7SMike Bayer                primary_key=True,
10971e1a38e7SMike Bayer            ),
10981e1a38e7SMike Bayer            Column("col2", String(30)),
10991e1a38e7SMike Bayer            Column("col3", String(40)),
11001e1a38e7SMike Bayer            Column("col4", String(30)),
11011e1a38e7SMike Bayer        )
1102fd3c063dSMike Bayer        Table(
11031e1a38e7SMike Bayer            "t2",
11041e1a38e7SMike Bayer            metadata,
1105fa80b73eSTony Locke            Column(
11061e1a38e7SMike Bayer                "col1",
11071e1a38e7SMike Bayer                Integer,
11081e1a38e7SMike Bayer                test_needs_autoincrement=True,
11091e1a38e7SMike Bayer                primary_key=True,
11101e1a38e7SMike Bayer            ),
11111e1a38e7SMike Bayer            Column("col2", String(30)),
11121e1a38e7SMike Bayer            Column("col3", String(40)),
11131e1a38e7SMike Bayer            Column("col4", String(30)),
11141e1a38e7SMike Bayer        )
1115fd3c063dSMike Bayer        Table(
11161e1a38e7SMike Bayer            "t3",
11171e1a38e7SMike Bayer            metadata,
1118fa80b73eSTony Locke            Column(
11191e1a38e7SMike Bayer                "col1",
11201e1a38e7SMike Bayer                Integer,
11211e1a38e7SMike Bayer                test_needs_autoincrement=True,
11221e1a38e7SMike Bayer                primary_key=True,
11231e1a38e7SMike Bayer            ),
11241e1a38e7SMike Bayer            Column("col2", String(30)),
11251e1a38e7SMike Bayer            Column("col3", String(40)),
11261e1a38e7SMike Bayer            Column("col4", String(30)),
11271e1a38e7SMike Bayer        )
1128350aed3fSMike Bayer
112945cec095SMike Bayer    @classmethod
1130fd3c063dSMike Bayer    def insert_data(cls, connection):
1131fd3c063dSMike Bayer        t1, t2, t3 = cls.tables("t1", "t2", "t3")
1132fd3c063dSMike Bayer        conn = connection
1133fd3c063dSMike Bayer        conn.execute(
1134fd3c063dSMike Bayer            t1.insert(),
1135fd3c063dSMike Bayer            [
1136fd3c063dSMike Bayer                dict(col2="t1col2r1", col3="aaa", col4="aaa"),
1137fd3c063dSMike Bayer                dict(col2="t1col2r2", col3="bbb", col4="bbb"),
1138fd3c063dSMike Bayer                dict(col2="t1col2r3", col3="ccc", col4="ccc"),
1139fd3c063dSMike Bayer            ],
1140fd3c063dSMike Bayer        )
1141fd3c063dSMike Bayer        conn.execute(
1142fd3c063dSMike Bayer            t2.insert(),
1143fd3c063dSMike Bayer            [
1144fd3c063dSMike Bayer                dict(col2="t2col2r1", col3="aaa", col4="bbb"),
1145fd3c063dSMike Bayer                dict(col2="t2col2r2", col3="bbb", col4="ccc"),
1146fd3c063dSMike Bayer                dict(col2="t2col2r3", col3="ccc", col4="aaa"),
1147fd3c063dSMike Bayer            ],
1148fd3c063dSMike Bayer        )
1149fd3c063dSMike Bayer        conn.execute(
1150fd3c063dSMike Bayer            t3.insert(),
1151fd3c063dSMike Bayer            [
1152fd3c063dSMike Bayer                dict(col2="t3col2r1", col3="aaa", col4="ccc"),
1153fd3c063dSMike Bayer                dict(col2="t3col2r2", col3="bbb", col4="aaa"),
1154fd3c063dSMike Bayer                dict(col2="t3col2r3", col3="ccc", col4="bbb"),
1155fd3c063dSMike Bayer            ],
1156fd3c063dSMike Bayer        )
1157a014d289SJason Kirtland
1158a014d289SJason Kirtland    def _fetchall_sorted(self, executed):
1159a014d289SJason Kirtland        return sorted([tuple(row) for row in executed.fetchall()])
1160a014d289SJason Kirtland
1161e41c0f41SJason Kirtland    @testing.requires.subqueries
1162b4a83504SGord Thompson    def test_union(self, connection):
1163fd3c063dSMike Bayer        t1, t2, t3 = self.tables("t1", "t2", "t3")
116419fcb943SMike Bayer        (s1, s2) = (
1165e8600608SFederico Caselli            select(t1.c.col3.label("col3"), t1.c.col4.label("col4")).where(
11661e1a38e7SMike Bayer                t1.c.col2.in_(["t1col2r1", "t1col2r2"]),
11671e1a38e7SMike Bayer            ),
1168e8600608SFederico Caselli            select(t2.c.col3.label("col3"), t2.c.col4.label("col4")).where(
11691e1a38e7SMike Bayer                t2.c.col2.in_(["t2col2r2", "t2col2r3"]),
11701e1a38e7SMike Bayer            ),
1171bcb586a9SJason Kirtland        )
1172a014d289SJason Kirtland        u = union(s1, s2)
1173a014d289SJason Kirtland
11741e1a38e7SMike Bayer        wanted = [
11751e1a38e7SMike Bayer            ("aaa", "aaa"),
11761e1a38e7SMike Bayer            ("bbb", "bbb"),
11771e1a38e7SMike Bayer            ("bbb", "ccc"),
11781e1a38e7SMike Bayer            ("ccc", "aaa"),
11791e1a38e7SMike Bayer        ]
1180b4a83504SGord Thompson        found1 = self._fetchall_sorted(connection.execute(u))
118145cec095SMike Bayer        eq_(found1, wanted)
1182a014d289SJason Kirtland
1183b4a83504SGord Thompson        found2 = self._fetchall_sorted(
1184b4a83504SGord Thompson            connection.execute(u.alias("bar").select())
1185b4a83504SGord Thompson        )
118645cec095SMike Bayer        eq_(found2, wanted)
1187bcb586a9SJason Kirtland
1188b4a83504SGord Thompson    def test_union_ordered(self, connection):
1189fd3c063dSMike Bayer        t1, t2, t3 = self.tables("t1", "t2", "t3")
1190fd3c063dSMike Bayer
1191a014d289SJason Kirtland        (s1, s2) = (
1192e8600608SFederico Caselli            select(t1.c.col3.label("col3"), t1.c.col4.label("col4")).where(
11931e1a38e7SMike Bayer                t1.c.col2.in_(["t1col2r1", "t1col2r2"]),
11941e1a38e7SMike Bayer            ),
1195e8600608SFederico Caselli            select(t2.c.col3.label("col3"), t2.c.col4.label("col4")).where(
11961e1a38e7SMike Bayer                t2.c.col2.in_(["t2col2r2", "t2col2r3"]),
11971e1a38e7SMike Bayer            ),
1198bcb586a9SJason Kirtland        )
1199f11b221aSGord Thompson        u = union(s1, s2).order_by("col3", "col4")
12001e1a38e7SMike Bayer
12011e1a38e7SMike Bayer        wanted = [
12021e1a38e7SMike Bayer            ("aaa", "aaa"),
12031e1a38e7SMike Bayer            ("bbb", "bbb"),
12041e1a38e7SMike Bayer            ("bbb", "ccc"),
12051e1a38e7SMike Bayer            ("ccc", "aaa"),
12061e1a38e7SMike Bayer        ]
1207b4a83504SGord Thompson        eq_(connection.execute(u).fetchall(), wanted)
1208a014d289SJason Kirtland
1209e41c0f41SJason Kirtland    @testing.requires.subqueries
1210b4a83504SGord Thompson    def test_union_ordered_alias(self, connection):
1211fd3c063dSMike Bayer        t1, t2, t3 = self.tables("t1", "t2", "t3")
1212fd3c063dSMike Bayer
1213a014d289SJason Kirtland        (s1, s2) = (
1214e8600608SFederico Caselli            select(t1.c.col3.label("col3"), t1.c.col4.label("col4")).where(
12151e1a38e7SMike Bayer                t1.c.col2.in_(["t1col2r1", "t1col2r2"]),
12161e1a38e7SMike Bayer            ),
1217e8600608SFederico Caselli            select(t2.c.col3.label("col3"), t2.c.col4.label("col4")).where(
12181e1a38e7SMike Bayer                t2.c.col2.in_(["t2col2r2", "t2col2r3"]),
12191e1a38e7SMike Bayer            ),
1220bcb586a9SJason Kirtland        )
1221f11b221aSGord Thompson        u = union(s1, s2).order_by("col3", "col4")
1222a014d289SJason Kirtland
12231e1a38e7SMike Bayer        wanted = [
12241e1a38e7SMike Bayer            ("aaa", "aaa"),
12251e1a38e7SMike Bayer            ("bbb", "bbb"),
12261e1a38e7SMike Bayer            ("bbb", "ccc"),
12271e1a38e7SMike Bayer            ("ccc", "aaa"),
12281e1a38e7SMike Bayer        ]
1229b4a83504SGord Thompson        eq_(connection.execute(u.alias("bar").select()).fetchall(), wanted)
1230a014d289SJason Kirtland
12311e1a38e7SMike Bayer    @testing.crashes("oracle", "FIXME: unknown, verify not fails_on")
12322f27dd35SMike Bayer    @testing.fails_on(
12332f27dd35SMike Bayer        testing.requires._mysql_not_mariadb_104, "FIXME: unknown"
12342f27dd35SMike Bayer    )
12351e1a38e7SMike Bayer    @testing.fails_on("sqlite", "FIXME: unknown")
1236b4a83504SGord Thompson    def test_union_all(self, connection):
1237fd3c063dSMike Bayer        t1, t2, t3 = self.tables("t1", "t2", "t3")
1238fd3c063dSMike Bayer
1239a014d289SJason Kirtland        e = union_all(
1240c3f102c9SMike Bayer            select(t1.c.col3),
1241c3f102c9SMike Bayer            union(select(t1.c.col3), select(t1.c.col3)),
1242a014d289SJason Kirtland        )
1243a014d289SJason Kirtland
12441e1a38e7SMike Bayer        wanted = [("aaa",), ("aaa",), ("bbb",), ("bbb",), ("ccc",), ("ccc",)]
1245b4a83504SGord Thompson        found1 = self._fetchall_sorted(connection.execute(e))
124645cec095SMike Bayer        eq_(found1, wanted)
1247a014d289SJason Kirtland
1248b4a83504SGord Thompson        found2 = self._fetchall_sorted(
1249b4a83504SGord Thompson            connection.execute(e.alias("foo").select())
1250b4a83504SGord Thompson        )
125145cec095SMike Bayer        eq_(found2, wanted)
1252a014d289SJason Kirtland
1253b4a83504SGord Thompson    def test_union_all_lightweight(self, connection):
125422ba1c43SMike Bayer        """like test_union_all, but breaks the sub-union into
12558fc5005dSMike Bayer        a subquery with an explicit column reference on the outside,
12568fc5005dSMike Bayer        more palatable to a wider variety of engines.
1257350aed3fSMike Bayer
12588fc5005dSMike Bayer        """
12597b24e458SMike Bayer
1260fd3c063dSMike Bayer        t1, t2, t3 = self.tables("t1", "t2", "t3")
1261fd3c063dSMike Bayer
1262e8600608SFederico Caselli        u = union(select(t1.c.col3), select(t1.c.col3)).alias()
1263350aed3fSMike Bayer
1264e8600608SFederico Caselli        e = union_all(select(t1.c.col3), select(u.c.col3))
12658fc5005dSMike Bayer
12661e1a38e7SMike Bayer        wanted = [("aaa",), ("aaa",), ("bbb",), ("bbb",), ("ccc",), ("ccc",)]
1267b4a83504SGord Thompson        found1 = self._fetchall_sorted(connection.execute(e))
12688fc5005dSMike Bayer        eq_(found1, wanted)
12698fc5005dSMike Bayer
1270b4a83504SGord Thompson        found2 = self._fetchall_sorted(
1271b4a83504SGord Thompson            connection.execute(e.alias("foo").select())
1272b4a83504SGord Thompson        )
12738fc5005dSMike Bayer        eq_(found2, wanted)
12748fc5005dSMike Bayer
127595c02143SMike Bayer    @testing.requires.intersect
1276b4a83504SGord Thompson    def test_intersect(self, connection):
1277fd3c063dSMike Bayer        t1, t2, t3 = self.tables("t1", "t2", "t3")
1278fd3c063dSMike Bayer
127919fcb943SMike Bayer        i = intersect(
1280e8600608SFederico Caselli            select(t2.c.col3, t2.c.col4),
1281e8600608SFederico Caselli            select(t2.c.col3, t2.c.col4).where(t2.c.col4 == t3.c.col3),
128219fcb943SMike Bayer        )
1283a014d289SJason Kirtland
12841e1a38e7SMike Bayer        wanted = [("aaa", "bbb"), ("bbb", "ccc"), ("ccc", "aaa")]
1285a014d289SJason Kirtland
1286b4a83504SGord Thompson        found1 = self._fetchall_sorted(connection.execute(i))
128745cec095SMike Bayer        eq_(found1, wanted)
1288a014d289SJason Kirtland
1289b4a83504SGord Thompson        found2 = self._fetchall_sorted(
1290b4a83504SGord Thompson            connection.execute(i.alias("bar").select())
1291b4a83504SGord Thompson        )
129245cec095SMike Bayer        eq_(found2, wanted)
129319fcb943SMike Bayer
129495c02143SMike Bayer    @testing.requires.except_
12951e1a38e7SMike Bayer    @testing.fails_on("sqlite", "Can't handle this style of nesting")
1296b4a83504SGord Thompson    def test_except_style1(self, connection):
1297fd3c063dSMike Bayer        t1, t2, t3 = self.tables("t1", "t2", "t3")
1298fd3c063dSMike Bayer
12991e1a38e7SMike Bayer        e = except_(
13001e1a38e7SMike Bayer            union(
1301e8600608SFederico Caselli                select(t1.c.col3, t1.c.col4),
1302e8600608SFederico Caselli                select(t2.c.col3, t2.c.col4),
1303e8600608SFederico Caselli                select(t3.c.col3, t3.c.col4),
13041e1a38e7SMike Bayer            ),
1305e8600608SFederico Caselli            select(t2.c.col3, t2.c.col4),
13061e1a38e7SMike Bayer        )
1307a014d289SJason Kirtland
13081e1a38e7SMike Bayer        wanted = [
13091e1a38e7SMike Bayer            ("aaa", "aaa"),
13101e1a38e7SMike Bayer            ("aaa", "ccc"),
13111e1a38e7SMike Bayer            ("bbb", "aaa"),
13121e1a38e7SMike Bayer            ("bbb", "bbb"),
13131e1a38e7SMike Bayer            ("ccc", "bbb"),
13141e1a38e7SMike Bayer            ("ccc", "ccc"),
13151e1a38e7SMike Bayer        ]
1316a014d289SJason Kirtland
1317b4a83504SGord Thompson        found = self._fetchall_sorted(connection.execute(e.alias().select()))
131845cec095SMike Bayer        eq_(found, wanted)
131919fcb943SMike Bayer
132095c02143SMike Bayer    @testing.requires.except_
1321b4a83504SGord Thompson    def test_except_style2(self, connection):
132267e7f45cSMike Bayer        # same as style1, but add alias().select() to the except_().
132367e7f45cSMike Bayer        # sqlite can handle it now.
1324350aed3fSMike Bayer
1325fd3c063dSMike Bayer        t1, t2, t3 = self.tables("t1", "t2", "t3")
1326fd3c063dSMike Bayer
13271e1a38e7SMike Bayer        e = except_(
13281e1a38e7SMike Bayer            union(
1329e8600608SFederico Caselli                select(t1.c.col3, t1.c.col4),
1330e8600608SFederico Caselli                select(t2.c.col3, t2.c.col4),
1331e8600608SFederico Caselli                select(t3.c.col3, t3.c.col4),
13321e1a38e7SMike Bayer            )
13331e1a38e7SMike Bayer            .alias()
13341e1a38e7SMike Bayer            .select(),
1335e8600608SFederico Caselli            select(t2.c.col3, t2.c.col4),
13361e1a38e7SMike Bayer        )
1337a014d289SJason Kirtland
13381e1a38e7SMike Bayer        wanted = [
13391e1a38e7SMike Bayer            ("aaa", "aaa"),
13401e1a38e7SMike Bayer            ("aaa", "ccc"),
13411e1a38e7SMike Bayer            ("bbb", "aaa"),
13421e1a38e7SMike Bayer            ("bbb", "bbb"),
13431e1a38e7SMike Bayer            ("ccc", "bbb"),
13441e1a38e7SMike Bayer            ("ccc", "ccc"),
13451e1a38e7SMike Bayer        ]
1346a014d289SJason Kirtland
1347b4a83504SGord Thompson        found1 = self._fetchall_sorted(connection.execute(e))
134845cec095SMike Bayer        eq_(found1, wanted)
1349a014d289SJason Kirtland
1350b4a83504SGord Thompson        found2 = self._fetchall_sorted(connection.execute(e.alias().select()))
135145cec095SMike Bayer        eq_(found2, wanted)
135219fcb943SMike Bayer
1353081d4275SMike Bayer    @testing.fails_on(
13542f27dd35SMike Bayer        ["sqlite", testing.requires._mysql_not_mariadb_104],
13552f27dd35SMike Bayer        "Can't handle this style of nesting",
13561e1a38e7SMike Bayer    )
135795c02143SMike Bayer    @testing.requires.except_
1358b4a83504SGord Thompson    def test_except_style3(self, connection):
135951c0d90cSAnts Aasma        # aaa, bbb, ccc - (aaa, bbb, ccc - (ccc)) = ccc
1360fd3c063dSMike Bayer        t1, t2, t3 = self.tables("t1", "t2", "t3")
1361fd3c063dSMike Bayer
136251c0d90cSAnts Aasma        e = except_(
1363e8600608SFederico Caselli            select(t1.c.col3),  # aaa, bbb, ccc
136451c0d90cSAnts Aasma            except_(
1365e8600608SFederico Caselli                select(t2.c.col3),  # aaa, bbb, ccc
1366e8600608SFederico Caselli                select(t3.c.col3).where(t3.c.col3 == "ccc"),  # ccc
13671e1a38e7SMike Bayer            ),
136851c0d90cSAnts Aasma        )
1369b4a83504SGord Thompson        eq_(connection.execute(e).fetchall(), [("ccc",)])
1370b4a83504SGord Thompson        eq_(connection.execute(e.alias("foo").select()).fetchall(), [("ccc",)])
137151c0d90cSAnts Aasma
137295c02143SMike Bayer    @testing.requires.except_
1373b4a83504SGord Thompson    def test_except_style4(self, connection):
137467e7f45cSMike Bayer        # aaa, bbb, ccc - (aaa, bbb, ccc - (ccc)) = ccc
1375fd3c063dSMike Bayer        t1, t2, t3 = self.tables("t1", "t2", "t3")
1376fd3c063dSMike Bayer
137767e7f45cSMike Bayer        e = except_(
1378e8600608SFederico Caselli            select(t1.c.col3),  # aaa, bbb, ccc
137967e7f45cSMike Bayer            except_(
1380e8600608SFederico Caselli                select(t2.c.col3),  # aaa, bbb, ccc
1381e8600608SFederico Caselli                select(t3.c.col3).where(t3.c.col3 == "ccc"),  # ccc
13821e1a38e7SMike Bayer            )
13831e1a38e7SMike Bayer            .alias()
13841e1a38e7SMike Bayer            .select(),
138567e7f45cSMike Bayer        )
1386350aed3fSMike Bayer
1387b4a83504SGord Thompson        eq_(connection.execute(e).fetchall(), [("ccc",)])
1388b4a83504SGord Thompson        eq_(connection.execute(e.alias().select()).fetchall(), [("ccc",)])
138967e7f45cSMike Bayer
139095c02143SMike Bayer    @testing.requires.intersect
13911e1a38e7SMike Bayer    @testing.fails_on(
13922f27dd35SMike Bayer        ["sqlite", testing.requires._mysql_not_mariadb_104],
13932f27dd35SMike Bayer        "sqlite can't handle leading parenthesis",
13941e1a38e7SMike Bayer    )
1395b4a83504SGord Thompson    def test_intersect_unions(self, connection):
1396fd3c063dSMike Bayer        t1, t2, t3 = self.tables("t1", "t2", "t3")
1397fd3c063dSMike Bayer
139867e7f45cSMike Bayer        u = intersect(
1399e8600608SFederico Caselli            union(select(t1.c.col3, t1.c.col4), select(t3.c.col3, t3.c.col4)),
1400e8600608SFederico Caselli            union(select(t2.c.col3, t2.c.col4), select(t3.c.col3, t3.c.col4))
14011e1a38e7SMike Bayer            .alias()
14021e1a38e7SMike Bayer            .select(),
140367e7f45cSMike Bayer        )
14041e1a38e7SMike Bayer        wanted = [("aaa", "ccc"), ("bbb", "aaa"), ("ccc", "bbb")]
1405b4a83504SGord Thompson        found = self._fetchall_sorted(connection.execute(u))
140667e7f45cSMike Bayer
140767e7f45cSMike Bayer        eq_(found, wanted)
140867e7f45cSMike Bayer
140995c02143SMike Bayer    @testing.requires.intersect
1410b4a83504SGord Thompson    def test_intersect_unions_2(self, connection):
1411fd3c063dSMike Bayer        t1, t2, t3 = self.tables("t1", "t2", "t3")
1412fd3c063dSMike Bayer
141367e7f45cSMike Bayer        u = intersect(
1414e8600608SFederico Caselli            union(select(t1.c.col3, t1.c.col4), select(t3.c.col3, t3.c.col4))
14151e1a38e7SMike Bayer            .alias()
14161e1a38e7SMike Bayer            .select(),
1417e8600608SFederico Caselli            union(select(t2.c.col3, t2.c.col4), select(t3.c.col3, t3.c.col4))
14181e1a38e7SMike Bayer            .alias()
14191e1a38e7SMike Bayer            .select(),
142067e7f45cSMike Bayer        )
14211e1a38e7SMike Bayer        wanted = [("aaa", "ccc"), ("bbb", "aaa"), ("ccc", "bbb")]
1422b4a83504SGord Thompson        found = self._fetchall_sorted(connection.execute(u))
142367e7f45cSMike Bayer
142467e7f45cSMike Bayer        eq_(found, wanted)
1425350aed3fSMike Bayer
142695c02143SMike Bayer    @testing.requires.intersect
1427b4a83504SGord Thompson    def test_intersect_unions_3(self, connection):
1428fd3c063dSMike Bayer        t1, t2, t3 = self.tables("t1", "t2", "t3")
1429fd3c063dSMike Bayer
143019fcb943SMike Bayer        u = intersect(
1431e8600608SFederico Caselli            select(t2.c.col3, t2.c.col4),
143219fcb943SMike Bayer            union(
1433e8600608SFederico Caselli                select(t1.c.col3, t1.c.col4),
1434e8600608SFederico Caselli                select(t2.c.col3, t2.c.col4),
1435e8600608SFederico Caselli                select(t3.c.col3, t3.c.col4),
14361e1a38e7SMike Bayer            )
14371e1a38e7SMike Bayer            .alias()
14381e1a38e7SMike Bayer            .select(),
143919fcb943SMike Bayer        )
14401e1a38e7SMike Bayer        wanted = [("aaa", "bbb"), ("bbb", "ccc"), ("ccc", "aaa")]
1441b4a83504SGord Thompson        found = self._fetchall_sorted(connection.execute(u))
1442bcb586a9SJason Kirtland
144345cec095SMike Bayer        eq_(found, wanted)
1444a014d289SJason Kirtland
144595c02143SMike Bayer    @testing.requires.intersect
1446b4a83504SGord Thompson    def test_composite_alias(self, connection):
1447fd3c063dSMike Bayer        t1, t2, t3 = self.tables("t1", "t2", "t3")
1448fd3c063dSMike Bayer
1449a014d289SJason Kirtland        ua = intersect(
1450e8600608SFederico Caselli            select(t2.c.col3, t2.c.col4),
1451a014d289SJason Kirtland            union(
1452e8600608SFederico Caselli                select(t1.c.col3, t1.c.col4),
1453e8600608SFederico Caselli                select(t2.c.col3, t2.c.col4),
1454e8600608SFederico Caselli                select(t3.c.col3, t3.c.col4),
14551e1a38e7SMike Bayer            )
14561e1a38e7SMike Bayer            .alias()
14571e1a38e7SMike Bayer            .select(),
145867e7f45cSMike Bayer        ).alias()
1459a014d289SJason Kirtland
14601e1a38e7SMike Bayer        wanted = [("aaa", "bbb"), ("bbb", "ccc"), ("ccc", "aaa")]
1461b4a83504SGord Thompson        found = self._fetchall_sorted(connection.execute(ua.select()))
146245cec095SMike Bayer        eq_(found, wanted)
1463849743acSJason Kirtland
146477237473SKhairi Hafsham
1465fd3c063dSMike Bayerclass JoinTest(fixtures.TablesTest):
1466bb5f4392SMike Bayer
146714563004SJason Kirtland    """Tests join execution.
146814563004SJason Kirtland
146914563004SJason Kirtland    The compiled SQL emitted by the dialect might be ANSI joins or
147014563004SJason Kirtland    theta joins ('old oracle style', with (+) for OUTER).  This test
147114563004SJason Kirtland    tries to exercise join syntax and uncover any inconsistencies in
147214563004SJason Kirtland    `JOIN rhs ON lhs.col=rhs.col` vs `rhs.col=lhs.col`.  At least one
147314563004SJason Kirtland    database seems to be sensitive to this.
147414563004SJason Kirtland    """
14751e1a38e7SMike Bayer
14762f150beeSMike Bayer    __backend__ = True
147714563004SJason Kirtland
147845cec095SMike Bayer    @classmethod
1479fd3c063dSMike Bayer    def define_tables(cls, metadata):
1480fd3c063dSMike Bayer        Table(
14811e1a38e7SMike Bayer            "t1",
14821e1a38e7SMike Bayer            metadata,
14831e1a38e7SMike Bayer            Column("t1_id", Integer, primary_key=True),
14841e1a38e7SMike Bayer            Column("name", String(32)),
14851e1a38e7SMike Bayer        )
1486fd3c063dSMike Bayer        Table(
14871e1a38e7SMike Bayer            "t2",
14881e1a38e7SMike Bayer            metadata,
14891e1a38e7SMike Bayer            Column("t2_id", Integer, primary_key=True),
14901e1a38e7SMike Bayer            Column("t1_id", Integer, ForeignKey("t1.t1_id")),
14911e1a38e7SMike Bayer            Column("name", String(32)),
14921e1a38e7SMike Bayer        )
1493fd3c063dSMike Bayer        Table(
14941e1a38e7SMike Bayer            "t3",
14951e1a38e7SMike Bayer            metadata,
14961e1a38e7SMike Bayer            Column("t3_id", Integer, primary_key=True),
14971e1a38e7SMike Bayer            Column("t2_id", Integer, ForeignKey("t2.t2_id")),
14981e1a38e7SMike Bayer            Column("name", String(32)),
14991e1a38e7SMike Bayer        )
1500849743acSJason Kirtland
150145cec095SMike Bayer    @classmethod
1502fd3c063dSMike Bayer    def insert_data(cls, connection):
1503fd3c063dSMike Bayer        conn = connection
1504fd3c063dSMike Bayer        # t1.10 -> t2.20 -> t3.30
1505fd3c063dSMike Bayer        # t1.11 -> t2.21
1506fd3c063dSMike Bayer        # t1.12
1507fd3c063dSMike Bayer        t1, t2, t3 = cls.tables("t1", "t2", "t3")
1508fd3c063dSMike Bayer
1509fd3c063dSMike Bayer        conn.execute(
1510fd3c063dSMike Bayer            t1.insert(),
1511ebbbac0aSMike Bayer            [
1512ebbbac0aSMike Bayer                {"t1_id": 10, "name": "t1 #10"},
1513ebbbac0aSMike Bayer                {"t1_id": 11, "name": "t1 #11"},
1514ebbbac0aSMike Bayer                {"t1_id": 12, "name": "t1 #12"},
1515ebbbac0aSMike Bayer            ],
1516fd3c063dSMike Bayer        )
1517fd3c063dSMike Bayer        conn.execute(
1518fd3c063dSMike Bayer            t2.insert(),
1519ebbbac0aSMike Bayer            [
1520ebbbac0aS