def test_union_label(self):
s1 = select([func.foo("hoho").label("x")])
s2 = select([func.foo("Bar").label("y")])
stmt = union(s1, s2).order_by("x")
self.assert_compile(
stmt,
"SELECT foo(:foo_1) AS x UNION SELECT foo(:foo_2) AS y ORDER BY x",
)
def test_union_column(self):
s1 = select([table1])
s2 = select([table1])
stmt = union(s1, s2).order_by("name")
self.assert_compile(
stmt,
"SELECT mytable.myid, mytable.name, mytable.description FROM "
"mytable UNION SELECT mytable.myid, mytable.name, "
"mytable.description FROM mytable ORDER BY name",
)
def test_b_ab1_union_b_ab2(self):
j1 = a.join(b1)
j2 = a.join(b2)
b_j1 = b.join(j1)
b_j2 = b.join(j2)
s = union(select([b_j1], use_labels=True),
select([b_j2], use_labels=True)).select(use_labels=True)
self._test(s, self._b_ab1_union_c_ab2)
def test_compound_select(self):
table = self._fixture()
s1 = select([table]).where(table.c.y == "hi")
s2 = select([table]).where(table.c.y == "there")
self.assert_compile(
union(s1, s2),
"SELECT test_table.x, lower(test_table.y) AS y "
"FROM test_table WHERE test_table.y = lower(:y_1) "
"UNION SELECT test_table.x, lower(test_table.y) AS y "
"FROM test_table WHERE test_table.y = lower(:y_2)",
)
def test_compound(self):
t1 = table("t1", column("c1"), column("c2"), column("c3"))
t2 = table("t2", column("c1"), column("c2"), column("c3"))
self.assert_compile(
union(t1.select(), t2.select()),
"SELECT t1.c1, t1.c2, t1.c3 FROM t1 UNION "
"SELECT t2.c1, t2.c2, t2.c3 FROM t2",
)
self.assert_compile(
except_(t1.select(), t2.select()),
"SELECT t1.c1, t1.c2, t1.c3 FROM t1 MINUS "
"SELECT t2.c1, t2.c2, t2.c3 FROM t2",
)
def test_select_of_compound_select(self):
table = self._fixture()
s1 = select([table]).where(table.c.y == "hi")
s2 = select([table]).where(table.c.y == "there")
self.assert_compile(
union(s1, s2).alias().select(),
"SELECT anon_1.x, lower(anon_1.y) AS y FROM "
"(SELECT test_table.x AS x, test_table.y AS y "
"FROM test_table WHERE test_table.y = lower(:y_1) "
"UNION SELECT test_table.x AS x, test_table.y AS y "
"FROM test_table WHERE test_table.y = lower(:y_2)) AS anon_1",
)
def test_union(self):
t1 = table(
"t1",
column("col1"),
column("col2"),
column("col3"),
column("col4"),
)
t2 = table(
"t2",
column("col1"),
column("col2"),
column("col3"),
column("col4"),
)
s1, s2 = (
select(
[t1.c.col3.label("col3"),
t1.c.col4.label("col4")],
t1.c.col2.in_(["t1col2r1", "t1col2r2"]),
),
select(
[t2.c.col3.label("col3"),
t2.c.col4.label("col4")],
t2.c.col2.in_(["t2col2r2", "t2col2r3"]),
),
)
u = union(s1, s2, order_by=["col3", "col4"])
self.assert_compile(
u,
"SELECT t1.col3 AS col3, t1.col4 AS col4 "
"FROM t1 WHERE t1.col2 IN (:col2_1, "
":col2_2) UNION SELECT t2.col3 AS col3, "
"t2.col4 AS col4 FROM t2 WHERE t2.col2 IN "
"(:col2_3, :col2_4) ORDER BY col3, col4",
)
self.assert_compile(
u.alias("bar").select(),
"SELECT bar.col3, bar.col4 FROM (SELECT "
"t1.col3 AS col3, t1.col4 AS col4 FROM t1 "
"WHERE t1.col2 IN (:col2_1, :col2_2) UNION "
"SELECT t2.col3 AS col3, t2.col4 AS col4 "
"FROM t2 WHERE t2.col2 IN (:col2_3, "
":col2_4)) AS bar",
)