def test_recursive_union_no_alias_one(self):
s1 = select(literal(0).label("x"))
cte = s1.cte(name="cte", recursive=True)
cte = cte.union_all(select(cte.c.x + 1).where(cte.c.x < 10))
s2 = select(cte)
self.assert_compile(
s2,
"WITH RECURSIVE cte(x) AS "
"(SELECT :param_1 AS x UNION ALL "
"SELECT cte.x + :x_1 AS anon_1 "
"FROM cte WHERE cte.x < :x_2) "
"SELECT cte.x FROM cte",
)
def test_recursive_union_no_alias_one(self):
s1 = select([literal(0).label("x")])
cte = s1.cte(name="cte", recursive=True)
cte = cte.union_all(select([cte.c.x + 1]).where(cte.c.x < 10))
s2 = select([cte])
self.assert_compile(
s2,
"WITH RECURSIVE cte(x) AS "
"(SELECT :param_1 AS x UNION ALL "
"SELECT cte.x + :x_1 AS anon_1 "
"FROM cte WHERE cte.x < :x_2) "
"SELECT cte.x FROM cte",
)
def test_recursive_union_alias_four(self):
# like test one and three, but let's refer
# previous version of "cte". here we test
# how the compiler resolves multiple instances
# of "cte".
s1 = select([literal(0).label("x")])
cte = s1.cte(name="cte", recursive=True)
bar = select([cte]).cte("bar").alias("cs1")
cte = cte.union_all(select([cte.c.x + 1]).where(cte.c.x < 10)).alias(
"cs2"
)
# outer cte rendered first, then bar, which
# includes "inner" cte
s2 = select([cte, bar])
self.assert_compile(
s2,
"WITH RECURSIVE cte(x) AS "
"(SELECT :param_1 AS x UNION ALL "
"SELECT cte.x + :x_1 AS anon_1 "
"FROM cte WHERE cte.x < :x_2), "
"bar AS (SELECT cte.x AS x FROM cte) "
"SELECT cs2.x, cs1.x FROM cte AS cs2, bar AS cs1",
)
# bar rendered, only includes "inner" cte,
# "outer" cte isn't present
s2 = select([bar])
self.assert_compile(
s2,
"WITH RECURSIVE cte(x) AS "
"(SELECT :param_1 AS x), "
"bar AS (SELECT cte.x AS x FROM cte) "
"SELECT cs1.x FROM bar AS cs1",
)
# bar rendered, but then the "outer"
# cte is rendered.
s2 = select([bar, cte])
self.assert_compile(
s2,
"WITH RECURSIVE bar AS (SELECT cte.x AS x FROM cte), "
"cte(x) AS "
"(SELECT :param_1 AS x UNION ALL "
"SELECT cte.x + :x_1 AS anon_1 "
"FROM cte WHERE cte.x < :x_2) "
"SELECT cs1.x, cs2.x FROM bar AS cs1, cte AS cs2",
)
def test_recursive_union_alias_four(self):
# like test one and three, but let's refer
# previous version of "cte". here we test
# how the compiler resolves multiple instances
# of "cte".
s1 = select([literal(0).label("x")])
cte = s1.cte(name="cte", recursive=True)
bar = select([cte]).cte("bar").alias("cs1")
cte = cte.union_all(select([cte.c.x + 1]).where(cte.c.x < 10)).alias(
"cs2"
)
# outer cte rendered first, then bar, which
# includes "inner" cte
s2 = select([cte, bar])
self.assert_compile(
s2,
"WITH RECURSIVE cte(x) AS "
"(SELECT :param_1 AS x UNION ALL "
"SELECT cte.x + :x_1 AS anon_1 "
"FROM cte WHERE cte.x < :x_2), "
"bar AS (SELECT cte.x AS x FROM cte) "
"SELECT cs2.x, cs1.x FROM cte AS cs2, bar AS cs1",
)
# bar rendered, only includes "inner" cte,
# "outer" cte isn't present
s2 = select([bar])
self.assert_compile(
s2,
"WITH RECURSIVE cte(x) AS "
"(SELECT :param_1 AS x), "
"bar AS (SELECT cte.x AS x FROM cte) "
"SELECT cs1.x FROM bar AS cs1",
)
# bar rendered, but then the "outer"
# cte is rendered.
s2 = select([bar, cte])
self.assert_compile(
s2,
"WITH RECURSIVE bar AS (SELECT cte.x AS x FROM cte), "
"cte(x) AS "
"(SELECT :param_1 AS x UNION ALL "
"SELECT cte.x + :x_1 AS anon_1 "
"FROM cte WHERE cte.x < :x_2) "
"SELECT cs1.x, cs2.x FROM bar AS cs1, cte AS cs2",
)
def test_recursive_union_no_alias_three(self):
# like test one, but let's refer to the CTE
# in a sibling CTE.
s1 = select(literal(0).label("x"))
cte = s1.cte(name="cte", recursive=True)
# can't do it here...
# bar = select(cte).cte('bar')
cte = cte.union_all(select(cte.c.x + 1).where(cte.c.x < 10))
bar = select(cte).cte("bar")
s2 = select(cte, bar)
self.assert_compile(
s2,
"WITH RECURSIVE cte(x) AS "
"(SELECT :param_1 AS x UNION ALL "
"SELECT cte.x + :x_1 AS anon_1 "
"FROM cte WHERE cte.x < :x_2), "
"bar AS (SELECT cte.x AS x FROM cte) "
"SELECT cte.x, bar.x FROM cte, bar",
)
def test_recursive_union_no_alias_three(self):
# like test one, but let's refer to the CTE
# in a sibling CTE.
s1 = select([literal(0).label("x")])
cte = s1.cte(name="cte", recursive=True)
# can't do it here...
# bar = select([cte]).cte('bar')
cte = cte.union_all(select([cte.c.x + 1]).where(cte.c.x < 10))
bar = select([cte]).cte("bar")
s2 = select([cte, bar])
self.assert_compile(
s2,
"WITH RECURSIVE cte(x) AS "
"(SELECT :param_1 AS x UNION ALL "
"SELECT cte.x + :x_1 AS anon_1 "
"FROM cte WHERE cte.x < :x_2), "
"bar AS (SELECT cte.x AS x FROM cte) "
"SELECT cte.x, bar.x FROM cte, bar",
)
