def test_noorderby_parameters_insubquery(self):
"""test that the ms-sql dialect does not include ORDER BY
positional parameters in subqueries"""
table1 = table(
"mytable",
column("myid", Integer),
column("name", String),
column("description", String),
)
q = select(
[table1.c.myid, sql.literal("bar").label("c1")],
order_by=[table1.c.name + "-"],
).alias("foo")
crit = q.c.myid == table1.c.myid
dialect = mssql.dialect()
dialect.paramstyle = "qmark"
dialect.positional = True
self.assert_compile(
select(["*"], crit),
"SELECT * FROM (SELECT mytable.myid AS "
"myid, ? AS c1 FROM mytable) AS foo, mytable WHERE "
"foo.myid = mytable.myid",
dialect=dialect,
checkparams={"param_1": "bar"},
# if name_1 is included, too many parameters are passed to dbapi
checkpositional=("bar", ),
)
def test_named_alias_quote(self):
cte = select([literal(1).label("id")]).cte(name="CTE")
s1 = select([cte.c.id]).alias(name="Quotes Required")
s = select([s1])
self.assert_compile(
s,
'WITH "CTE" AS (SELECT :param_1 AS id) '
'SELECT "Quotes Required".id FROM '
'(SELECT "CTE".id AS id FROM "CTE") AS "Quotes Required"',
)
def test_named_alias_no_quote(self):
cte = select([literal(1).label("id")]).cte(name="CTE")
s1 = select([cte.c.id]).alias(name="no_quotes")
s = select([s1])
self.assert_compile(
s,
'WITH "CTE" AS (SELECT :param_1 AS id) '
"SELECT no_quotes.id FROM "
'(SELECT "CTE".id AS id FROM "CTE") AS no_quotes',
)
def test_positional_binds_2_asliteral(self):
orders = table("orders", column("order"))
s = select([orders.c.order, literal("x")]).cte("regional_sales")
s = select([s.c.order, literal("y")])
dialect = default.DefaultDialect()
dialect.positional = True
dialect.paramstyle = "numeric"
s1 = (select([orders.c.order
]).where(orders.c.order == "x").cte("regional_sales_1"))
s1a = s1.alias()
s2 = (select([
orders.c.order == "y",
s1a.c.order,
orders.c.order,
s1.c.order,
]).where(orders.c.order == "z").cte("regional_sales_2"))
s3 = select([s2])
self.assert_compile(
s3,
"WITH regional_sales_1 AS "
'(SELECT orders."order" AS "order" '
"FROM orders "
"WHERE orders.\"order\" = 'x'), "
"regional_sales_2 AS "
"(SELECT orders.\"order\" = 'y' AS anon_1, "
'anon_2."order" AS "order", orders."order" AS "order", '
'regional_sales_1."order" AS "order" '
"FROM orders, regional_sales_1 AS anon_2, regional_sales_1 "
"WHERE orders.\"order\" = 'z') "
'SELECT regional_sales_2.anon_1, regional_sales_2."order" '
"FROM regional_sales_2",
checkpositional=(),
dialect=dialect,
literal_binds=True,
)
def test_positional_binds(self):
orders = table("orders", column("order"))
s = select([orders.c.order, literal("x")]).cte("regional_sales")
s = select([s.c.order, literal("y")])
dialect = default.DefaultDialect()
dialect.positional = True
dialect.paramstyle = "numeric"
self.assert_compile(
s,
'WITH regional_sales AS (SELECT orders."order" '
'AS "order", :1 AS anon_2 FROM orders) SELECT '
'regional_sales."order", :2 AS anon_1 FROM regional_sales',
checkpositional=("x", "y"),
dialect=dialect,
)
self.assert_compile(
s.union(s),
'WITH regional_sales AS (SELECT orders."order" '
'AS "order", :1 AS anon_2 FROM orders) SELECT '
'regional_sales."order", :2 AS anon_1 FROM regional_sales '
'UNION SELECT regional_sales."order", :3 AS anon_1 '
"FROM regional_sales",
checkpositional=("x", "y", "y"),
dialect=dialect,
)
s = (select([orders.c.order
]).where(orders.c.order == "x").cte("regional_sales"))
s = select([s.c.order]).where(s.c.order == "y")
self.assert_compile(
s,
'WITH regional_sales AS (SELECT orders."order" AS '
'"order" FROM orders WHERE orders."order" = :1) '
'SELECT regional_sales."order" FROM regional_sales '
'WHERE regional_sales."order" = :2',
checkpositional=("x", "y"),
dialect=dialect,
)
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_upsert_from_select(self):
orders = table(
"orders",
column("region"),
column("amount"),
column("product"),
column("quantity"),
)
upsert = (orders.update().where(orders.c.region == "Region1").values(
amount=1.0, product="Product1",
quantity=1).returning(*(orders.c._all_columns)).cte("upsert"))
insert = orders.insert().from_select(
orders.c.keys(),
select([
literal("Region1"),
literal(1.0),
literal("Product1"),
literal(1),
]).where(~exists(upsert.select())),
)
self.assert_compile(
insert,
"WITH upsert AS (UPDATE orders SET amount=:amount, "
"product=:product, quantity=:quantity "
"WHERE orders.region = :region_1 "
"RETURNING orders.region, orders.amount, "
"orders.product, orders.quantity) "
"INSERT INTO orders (region, amount, product, quantity) "
"SELECT :param_1 AS anon_1, :param_2 AS anon_2, "
":param_3 AS anon_3, :param_4 AS anon_4 WHERE NOT (EXISTS "
"(SELECT upsert.region, upsert.amount, upsert.product, "
"upsert.quantity FROM upsert))",
)
eq_(insert.compile().isinsert, True)
def test_named_alias_disable_quote(self):
cte = select([literal(1).label("id")
]).cte(name=quoted_name("CTE", quote=False))
s1 = select([cte.c.id
]).alias(name=quoted_name("DontQuote", quote=False))
s = select([s1])
self.assert_compile(
s,
"WITH CTE AS (SELECT :param_1 AS id) "
"SELECT DontQuote.id FROM "
"(SELECT CTE.id AS id FROM CTE) AS DontQuote",
)
def test_multi_subq_alias(self):
cte = select([literal(1).label("id")]).cte(name="cte1").alias("aa")
s1 = select([cte.c.id]).alias()
s2 = select([cte.c.id]).alias()
s = select([s1, s2])
self.assert_compile(
s,
"WITH cte1 AS (SELECT :param_1 AS id) "
"SELECT anon_1.id, anon_2.id FROM "
"(SELECT aa.id AS id FROM cte1 AS aa) AS anon_1, "
"(SELECT aa.id AS id FROM cte1 AS aa) AS anon_2",
)
def test_multi_subq_quote(self):
cte = select([literal(1).label("id")]).cte(name="CTE")
s1 = select([cte.c.id]).alias()
s2 = select([cte.c.id]).alias()
s = select([s1, s2])
self.assert_compile(
s,
'WITH "CTE" AS (SELECT :param_1 AS id) '
"SELECT anon_1.id, anon_2.id FROM "
'(SELECT "CTE".id AS id FROM "CTE") AS anon_1, '
'(SELECT "CTE".id AS id FROM "CTE") AS anon_2',
)
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_positional_binds_2(self):
orders = table("orders", column("order"))
s = select([orders.c.order, literal("x")]).cte("regional_sales")
s = select([s.c.order, literal("y")])
dialect = default.DefaultDialect()
dialect.positional = True
dialect.paramstyle = "numeric"
s1 = (select([orders.c.order
]).where(orders.c.order == "x").cte("regional_sales_1"))
s1a = s1.alias()
s2 = (select([
orders.c.order == "y",
s1a.c.order,
orders.c.order,
s1.c.order,
]).where(orders.c.order == "z").cte("regional_sales_2"))
s3 = select([s2])
self.assert_compile(
s3,
'WITH regional_sales_1 AS (SELECT orders."order" AS "order" '
'FROM orders WHERE orders."order" = :1), regional_sales_2 AS '
'(SELECT orders."order" = :2 AS anon_1, '
'anon_2."order" AS "order", '
'orders."order" AS "order", '
'regional_sales_1."order" AS "order" FROM orders, '
"regional_sales_1 "
"AS anon_2, regional_sales_1 "
'WHERE orders."order" = :3) SELECT regional_sales_2.anon_1, '
'regional_sales_2."order" FROM regional_sales_2',
checkpositional=("x", "y", "z"),
dialect=dialect,
)
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",
)
