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", )