- move cte tests into their own test/sql/test_cte.py

- rework bindtemplate system of "numbered" params by applying
the numbers last, as we now need to generate these out of order
in some cases
- add positional assertion to assert_compile
- add new cte_positional collection to track bindparams generated
within cte visits; splice this onto the beginning of self.positiontup
at cte render time, [ticket:2521]

1 files changed, 0 insertions, 164 deletions

diff --git a/test/sql/test_compiler.py b/test/sql/test_compiler.py
index 22353f142..f193e6a36 100644
--- a/test/sql/test_compiler.py
+++ b/test/sql/test_compiler.py
@@ -2335,170 +2335,6 @@ class SelectTest(fixtures.TestBase, AssertsCompiledSQL):
             "SELECT x + foo() OVER () AS anon_1"
         )
 
-    def test_cte_nonrecursive(self):
-        orders = table('orders', 
-            column('region'),
-            column('amount'),
-            column('product'),
-            column('quantity')
-        )
-
-        regional_sales = select([
-                            orders.c.region, 
-                            func.sum(orders.c.amount).label('total_sales')
-                        ]).group_by(orders.c.region).cte("regional_sales")
-
-        top_regions = select([regional_sales.c.region]).\
-                where(
-                    regional_sales.c.total_sales > 
-                    select([
-                        func.sum(regional_sales.c.total_sales)/10
-                    ])
-                ).cte("top_regions")
-
-        s = select([
-                    orders.c.region, 
-                    orders.c.product, 
-                    func.sum(orders.c.quantity).label("product_units"), 
-                    func.sum(orders.c.amount).label("product_sales")
-            ]).where(orders.c.region.in_(
-                select([top_regions.c.region])
-            )).group_by(orders.c.region, orders.c.product)
-
-        # needs to render regional_sales first as top_regions
-        # refers to it
-        self.assert_compile(
-            s,
-            "WITH regional_sales AS (SELECT orders.region AS region, "
-            "sum(orders.amount) AS total_sales FROM orders "
-            "GROUP BY orders.region), "
-            "top_regions AS (SELECT "
-            "regional_sales.region AS region FROM regional_sales "
-            "WHERE regional_sales.total_sales > "
-            "(SELECT sum(regional_sales.total_sales) / :sum_1 AS "
-            "anon_1 FROM regional_sales)) "
-            "SELECT orders.region, orders.product, "
-            "sum(orders.quantity) AS product_units, "
-            "sum(orders.amount) AS product_sales "
-            "FROM orders WHERE orders.region "
-            "IN (SELECT top_regions.region FROM top_regions) "
-            "GROUP BY orders.region, orders.product"
-        )
-
-    def test_cte_recursive(self):
-        parts = table('parts', 
-            column('part'),
-            column('sub_part'),
-            column('quantity'),
-        )
-
-        included_parts = select([
-                            parts.c.sub_part, 
-                            parts.c.part, 
-                            parts.c.quantity]).\
-                            where(parts.c.part=='our part').\
-                                cte(recursive=True)
-
-        incl_alias = included_parts.alias()
-        parts_alias = parts.alias()
-        included_parts = included_parts.union(
-            select([
-                parts_alias.c.part, 
-                parts_alias.c.sub_part, 
-                parts_alias.c.quantity]).\
-                where(parts_alias.c.part==incl_alias.c.sub_part)
-            )
-
-        s = select([
-            included_parts.c.sub_part, 
-            func.sum(included_parts.c.quantity).label('total_quantity')]).\
-            select_from(included_parts.join(
-                    parts,included_parts.c.part==parts.c.part)).\
-            group_by(included_parts.c.sub_part)
-        self.assert_compile(s, 
-                "WITH RECURSIVE anon_1(sub_part, part, quantity) "
-                "AS (SELECT parts.sub_part AS sub_part, parts.part "
-                "AS part, parts.quantity AS quantity FROM parts "
-                "WHERE parts.part = :part_1 UNION SELECT parts_1.part "
-                "AS part, parts_1.sub_part AS sub_part, parts_1.quantity "
-                "AS quantity FROM parts AS parts_1, anon_1 AS anon_2 "
-                "WHERE parts_1.part = anon_2.sub_part) "
-                "SELECT anon_1.sub_part, "
-                "sum(anon_1.quantity) AS total_quantity FROM anon_1 "
-                "JOIN parts ON anon_1.part = parts.part "
-                "GROUP BY anon_1.sub_part"
-            )
-
-        # quick check that the "WITH RECURSIVE" varies per
-        # dialect
-        self.assert_compile(s, 
-                "WITH anon_1(sub_part, part, quantity) "
-                "AS (SELECT parts.sub_part AS sub_part, parts.part "
-                "AS part, parts.quantity AS quantity FROM parts "
-                "WHERE parts.part = :part_1 UNION SELECT parts_1.part "
-                "AS part, parts_1.sub_part AS sub_part, parts_1.quantity "
-                "AS quantity FROM parts AS parts_1, anon_1 AS anon_2 "
-                "WHERE parts_1.part = anon_2.sub_part) "
-                "SELECT anon_1.sub_part, "
-                "sum(anon_1.quantity) AS total_quantity FROM anon_1 "
-                "JOIN parts ON anon_1.part = parts.part "
-                "GROUP BY anon_1.sub_part",
-                dialect=mssql.dialect()
-            )
-
-    def test_cte_union(self):
-        orders = table('orders', 
-            column('region'),
-            column('amount'),
-        )
-
-        regional_sales = select([
-                            orders.c.region,
-                            orders.c.amount
-                        ]).cte("regional_sales")
-
-        s = select([regional_sales.c.region]).\
-                where(
-                    regional_sales.c.amount > 500
-                )
-
-        self.assert_compile(s, 
-            "WITH regional_sales AS "
-            "(SELECT orders.region AS region, "
-            "orders.amount AS amount FROM orders) "
-            "SELECT regional_sales.region "
-            "FROM regional_sales WHERE "
-            "regional_sales.amount > :amount_1")
-
-        s = s.union_all(
-            select([regional_sales.c.region]).\
-                where(
-                    regional_sales.c.amount < 300
-                )
-        )
-        self.assert_compile(s, 
-            "WITH regional_sales AS "
-            "(SELECT orders.region AS region, "
-            "orders.amount AS amount FROM orders) "
-            "SELECT regional_sales.region FROM regional_sales "
-            "WHERE regional_sales.amount > :amount_1 "
-            "UNION ALL SELECT regional_sales.region "
-            "FROM regional_sales WHERE "
-            "regional_sales.amount < :amount_2")
-
-    def test_cte_reserved_quote(self):
-        orders = table('orders', 
-            column('order'),
-        )
-        s = select([orders.c.order]).cte("regional_sales", recursive=True)
-        s = select([s.c.order])
-        self.assert_compile(s,
-            'WITH RECURSIVE regional_sales("order") AS '
-            '(SELECT orders."order" AS "order" '
-            "FROM orders)"
-            ' SELECT regional_sales."order" '
-            "FROM regional_sales"
-            )
 
     def test_date_between(self):
         import datetime