From 654b462d668a2ced4e87077b9babb2590acbf983 Mon Sep 17 00:00:00 2001
From: Mike Bayer <mike_mp@zzzcomputing.com>
Date: Fri, 25 Sep 2020 22:31:16 -0400
Subject: tutorial 2.0 WIP

Add SelectBase.exists() method as it seems strange this is
not available already.  The Exists construct itself does
not provide full SELECT-building capabilities so it makes
sense this should be used more like a scalar_subquery.

Make sure stream_results is getting set up when yield_per
is used, for 2.0 style statements as well.  this was
hardcoded inside of Query.yield_per() and is now moved
to take place within QueryContext.

Change-Id: Icafcd4fd9b708772343d56edf40995c9e8f835d6
---
 test/sql/test_compiler.py | 40 ++++++++++++++++++++++++++++++++++++++++
 1 file changed, 40 insertions(+)

(limited to 'test/sql/test_compiler.py')

diff --git a/test/sql/test_compiler.py b/test/sql/test_compiler.py
index d3f8b6a9f..8e5f4a43a 100644
--- a/test/sql/test_compiler.py
+++ b/test/sql/test_compiler.py
@@ -1092,6 +1092,25 @@ class SelectTest(fixtures.TestBase, AssertsCompiledSQL):
             "SELECT NOT (NOT (EXISTS (SELECT 1))) AS anon_1",
         )
 
+    def test_exists_method(self):
+        subq = (
+            select(func.count(table2.c.otherid))
+            .where(table2.c.otherid == table1.c.myid)
+            .correlate(table1)
+            .group_by(table2.c.otherid)
+            .having(func.count(table2.c.otherid) > 1)
+            .exists()
+        )
+
+        self.assert_compile(
+            table1.select().where(subq),
+            "SELECT mytable.myid, mytable.name, mytable.description "
+            "FROM mytable WHERE EXISTS (SELECT count(myothertable.otherid) "
+            "AS count_1 FROM myothertable WHERE myothertable.otherid = "
+            "mytable.myid GROUP BY myothertable.otherid "
+            "HAVING count(myothertable.otherid) > :count_2)",
+        )
+
     def test_where_subquery(self):
         s = (
             select(addresses.c.street)
@@ -1693,6 +1712,15 @@ class SelectTest(fixtures.TestBase, AssertsCompiledSQL):
             "SELECT mytable.myid FROM mytable",
         )
 
+    def test_where_multiple(self):
+        self.assert_compile(
+            select(table1.c.myid).where(
+                table1.c.myid == 12, table1.c.name == "foobar"
+            ),
+            "SELECT mytable.myid FROM mytable WHERE mytable.myid = :myid_1 "
+            "AND mytable.name = :name_1",
+        )
+
     def test_order_by_nulls(self):
         self.assert_compile(
             table2.select().order_by(
@@ -5084,6 +5112,12 @@ class CorrelateTest(fixtures.TestBase, AssertsCompiledSQL):
             select(t2, s1.correlate(t2).scalar_subquery())
         )
 
+    def test_correlate_semiauto_column_correlate_from_subq(self):
+        t1, t2, s1 = self._fixture()
+        self._assert_column_correlated(
+            select(t2, s1.scalar_subquery().correlate(t2))
+        )
+
     def test_correlate_semiauto_from(self):
         t1, t2, s1 = self._fixture()
         self._assert_from_uncorrelated(select(t2, s1.correlate(t2).alias()))
@@ -5094,6 +5128,12 @@ class CorrelateTest(fixtures.TestBase, AssertsCompiledSQL):
             select(t2).having(t2.c.a == s1.correlate(t2).scalar_subquery())
         )
 
+    def test_correlate_semiauto_having_from_subq(self):
+        t1, t2, s1 = self._fixture()
+        self._assert_having_correlated(
+            select(t2).having(t2.c.a == s1.scalar_subquery().correlate(t2))
+        )
+
     def test_correlate_except_inclusion_where(self):
         t1, t2, s1 = self._fixture()
         self._assert_where_correlated(
-- 
cgit v1.2.1