diff options
author | Mike Bayer <mike_mp@zzzcomputing.com> | 2020-01-17 14:31:10 -0500 |
---|---|---|
committer | Mike Bayer <mike_mp@zzzcomputing.com> | 2020-01-17 16:29:27 -0500 |
commit | bdbe164d392d41991b64ced0f097930a04a2c420 (patch) | |
tree | fbaf0da3f3e68b211821220843c9b3ab944c38df /lib/sqlalchemy/testing/suite/test_select.py | |
parent | a711522650863dd368acfa90e09216ae37fc3ec2 (diff) | |
download | sqlalchemy-bdbe164d392d41991b64ced0f097930a04a2c420.tar.gz |
apply asbool reduction to the onclause in join()
The :func:`.true` and :func:`.false` operators may now be applied as the
"onclause" of a :func:`.sql.join` on a backend that does not support
"native boolean" expressions, e.g. Oracle or SQL Server, and the expression
will render as "1=1" for true and "1=0" false. This is the behavior that
was introduced many years ago in :ticket:`2804` for and/or expressions.
Change-Id: I85311c31c22d6e226c618f8840f6b95eca611153
Diffstat (limited to 'lib/sqlalchemy/testing/suite/test_select.py')
-rw-r--r-- | lib/sqlalchemy/testing/suite/test_select.py | 102 |
1 files changed, 102 insertions, 0 deletions
diff --git a/lib/sqlalchemy/testing/suite/test_select.py b/lib/sqlalchemy/testing/suite/test_select.py index 9db2daf7a..3b64b0f29 100644 --- a/lib/sqlalchemy/testing/suite/test_select.py +++ b/lib/sqlalchemy/testing/suite/test_select.py @@ -1,3 +1,6 @@ +import itertools + +from sqlalchemy import ForeignKey from .. import AssertsCompiledSQL from .. import AssertsExecutionResults from .. import config @@ -292,6 +295,105 @@ class LimitOffsetTest(fixtures.TablesTest): ) +class JoinTest(fixtures.TablesTest): + __backend__ = True + + def _assert_result(self, select, result, params=()): + eq_(config.db.execute(select, params).fetchall(), result) + + @classmethod + def define_tables(cls, metadata): + Table("a", metadata, Column("id", Integer, primary_key=True)) + Table( + "b", + metadata, + Column("id", Integer, primary_key=True), + Column("a_id", ForeignKey("a.id"), nullable=False), + ) + + @classmethod + def insert_data(cls): + config.db.execute( + cls.tables.a.insert(), + [{"id": 1}, {"id": 2}, {"id": 3}, {"id": 4}, {"id": 5}], + ) + + config.db.execute( + cls.tables.b.insert(), + [ + {"id": 1, "a_id": 1}, + {"id": 2, "a_id": 1}, + {"id": 4, "a_id": 2}, + {"id": 5, "a_id": 3}, + ], + ) + + def test_inner_join_fk(self): + a, b = self.tables("a", "b") + + stmt = select([a, b]).select_from(a.join(b)).order_by(a.c.id, b.c.id) + + self._assert_result(stmt, [(1, 1, 1), (1, 2, 1), (2, 4, 2), (3, 5, 3)]) + + def test_inner_join_true(self): + a, b = self.tables("a", "b") + + stmt = ( + select([a, b]) + .select_from(a.join(b, true())) + .order_by(a.c.id, b.c.id) + ) + + self._assert_result( + stmt, + [ + (a, b, c) + for (a,), (b, c) in itertools.product( + [(1,), (2,), (3,), (4,), (5,)], + [(1, 1), (2, 1), (4, 2), (5, 3)], + ) + ], + ) + + def test_inner_join_false(self): + a, b = self.tables("a", "b") + + stmt = ( + select([a, b]) + .select_from(a.join(b, false())) + .order_by(a.c.id, b.c.id) + ) + + self._assert_result(stmt, []) + + def test_outer_join_false(self): + a, b = self.tables("a", "b") + + stmt = ( + select([a, b]) + .select_from(a.outerjoin(b, false())) + .order_by(a.c.id, b.c.id) + ) + + self._assert_result( + stmt, + [ + (1, None, None), + (2, None, None), + (3, None, None), + (4, None, None), + (5, None, None), + ], + ) + + def test_outer_join_fk(self): + a, b = self.tables("a", "b") + + stmt = select([a, b]).select_from(a.join(b)).order_by(a.c.id, b.c.id) + + self._assert_result(stmt, [(1, 1, 1), (1, 2, 1), (2, 4, 2), (3, 5, 3)]) + + class CompoundSelectTest(fixtures.TablesTest): __backend__ = True |