summaryrefslogtreecommitdiff
path: root/lib/sqlalchemy/testing/suite/test_select.py
diff options
context:
space:
mode:
authorMike Bayer <mike_mp@zzzcomputing.com>2020-01-17 14:31:10 -0500
committerMike Bayer <mike_mp@zzzcomputing.com>2020-01-17 16:29:27 -0500
commitbdbe164d392d41991b64ced0f097930a04a2c420 (patch)
treefbaf0da3f3e68b211821220843c9b3ab944c38df /lib/sqlalchemy/testing/suite/test_select.py
parenta711522650863dd368acfa90e09216ae37fc3ec2 (diff)
downloadsqlalchemy-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.py102
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