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_limit_offset_w_ambiguous_cols(self):
        t = table("t", column("x", Integer), column("y", Integer))

        cols = [t.c.x, t.c.x.label("q"), t.c.x.label("p"), t.c.y]
        s = select(cols).where(t.c.x == 5).order_by(t.c.y).limit(10).offset(20)

        self.assert_compile(
            s,
            "SELECT anon_1.x, anon_1.q, anon_1.p, anon_1.y "
            "FROM (SELECT t.x AS x, t.x AS q, t.x AS p, t.y AS y, "
            "ROW_NUMBER() OVER (ORDER BY t.y) AS mssql_rn "
            "FROM t "
            "WHERE t.x = :x_1) AS anon_1 "
            "WHERE mssql_rn > :param_1 AND mssql_rn <= :param_2 + :param_1",
            checkparams={
                "param_1": 20,
                "param_2": 10,
                "x_1": 5
            },
        )
        c = s.compile(dialect=mssql.dialect())
        eq_(len(c._result_columns), 4)

        result_map = c._create_result_map()

        for col in cols:
            is_(result_map[col.key][1][0], col)
示例#3
0
    def test_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.sub_part,
                parts_alias.c.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.sub_part AS sub_part, "
            "parts_1.part AS 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.sub_part AS sub_part, "
            "parts_1.part AS 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 setup(self):
        t = Table(
            "sometable",
            MetaData(),
            Column("pk_column", Integer),
            Column("test_column", String),
        )
        self.column = t.c.test_column

        dialect = mssql.dialect()
        self.ddl_compiler = dialect.ddl_compiler(dialect,
                                                 schema.CreateTable(t))
    def test_limit_zero_using_top(self):
        t = table("t", column("x", Integer), column("y", Integer))

        s = select([t]).where(t.c.x == 5).order_by(t.c.y).limit(0)

        self.assert_compile(
            s,
            "SELECT TOP 0 t.x, t.y FROM t WHERE t.x = :x_1 ORDER BY t.y",
            checkparams={"x_1": 5},
        )
        c = s.compile(dialect=mssql.dialect())
        eq_(len(c._result_columns), 2)
        assert t.c.x in set(c._create_result_map()["x"][1])
    def test_limit_offset_using_window(self):
        t = table("t", column("x", Integer), column("y", Integer))

        s = select([t]).where(t.c.x == 5).order_by(t.c.y).limit(10).offset(20)

        self.assert_compile(
            s,
            "SELECT anon_1.x, anon_1.y "
            "FROM (SELECT t.x AS x, t.y AS y, "
            "ROW_NUMBER() OVER (ORDER BY t.y) AS mssql_rn "
            "FROM t "
            "WHERE t.x = :x_1) AS anon_1 "
            "WHERE mssql_rn > :param_1 AND mssql_rn <= :param_2 + :param_1",
            checkparams={
                "param_1": 20,
                "param_2": 10,
                "x_1": 5
            },
        )
        c = s.compile(dialect=mssql.dialect())
        eq_(len(c._result_columns), 2)
        assert t.c.x in set(c._create_result_map()["x"][1])
        assert t.c.y in set(c._create_result_map()["y"][1])
    def test_offset_using_window(self):
        t = table("t", column("x", Integer), column("y", Integer))

        s = select([t]).where(t.c.x == 5).order_by(t.c.y).offset(20)

        # test that the select is not altered with subsequent compile
        # calls
        for i in range(2):
            self.assert_compile(
                s,
                "SELECT anon_1.x, anon_1.y FROM (SELECT t.x AS x, t.y "
                "AS y, ROW_NUMBER() OVER (ORDER BY t.y) AS "
                "mssql_rn FROM t WHERE t.x = :x_1) AS "
                "anon_1 WHERE mssql_rn > :param_1",
                checkparams={
                    "param_1": 20,
                    "x_1": 5
                },
            )

            c = s.compile(dialect=mssql.dialect())
            eq_(len(c._result_columns), 2)
            assert t.c.x in set(c._create_result_map()["x"][1])
class CompileTest(fixtures.TestBase, AssertsCompiledSQL):
    __dialect__ = mssql.dialect()

    def test_true_false(self):
        self.assert_compile(sql.false(), "0")
        self.assert_compile(sql.true(), "1")

    @testing.combinations(
        ("plain", "sometable", "sometable"),
        ("matched_square_brackets", "colo[u]r", "[colo[u]]r]"),
        ("unmatched_left_square_bracket", "colo[ur", "[colo[ur]"),
        ("unmatched_right_square_bracket", "colou]r", "[colou]]r]"),
        ("double quotes", 'Edwin "Buzz" Aldrin', '[Edwin "Buzz" Aldrin]'),
        ("dash", "Dash-8", "[Dash-8]"),
        ("slash", "tl/dr", "[tl/dr]"),
        ("space", "Red Deer", "[Red Deer]"),
        ("question mark", "OK?", "[OK?]"),
        ("percent", "GST%", "[GST%]"),
        id_="iaa",
    )
    def test_identifier_rendering(self, table_name, rendered_name):
        t = table(table_name, column("somecolumn"))
        self.assert_compile(
            t.select(), "SELECT {0}.somecolumn FROM {0}".format(rendered_name))

    def test_select_with_nolock(self):
        t = table("sometable", column("somecolumn"))
        self.assert_compile(
            t.select().with_hint(t, "WITH (NOLOCK)"),
            "SELECT sometable.somecolumn FROM sometable WITH (NOLOCK)",
        )

    def test_select_with_nolock_schema(self):
        m = MetaData()
        t = Table("sometable",
                  m,
                  Column("somecolumn", Integer),
                  schema="test_schema")
        self.assert_compile(
            t.select().with_hint(t, "WITH (NOLOCK)"),
            "SELECT test_schema.sometable.somecolumn "
            "FROM test_schema.sometable WITH (NOLOCK)",
        )

    def test_select_w_order_by_collate(self):
        m = MetaData()
        t = Table("sometable", m, Column("somecolumn", String))

        self.assert_compile(
            select([t]).order_by(
                t.c.somecolumn.collate("Latin1_General_CS_AS_KS_WS_CI").asc()),
            "SELECT sometable.somecolumn FROM sometable "
            "ORDER BY sometable.somecolumn COLLATE "
            "Latin1_General_CS_AS_KS_WS_CI ASC",
        )

    def test_join_with_hint(self):
        t1 = table(
            "t1",
            column("a", Integer),
            column("b", String),
            column("c", String),
        )
        t2 = table(
            "t2",
            column("a", Integer),
            column("b", Integer),
            column("c", Integer),
        )
        join = (t1.join(t2, t1.c.a == t2.c.a).select().with_hint(
            t1, "WITH (NOLOCK)"))
        self.assert_compile(
            join,
            "SELECT t1.a, t1.b, t1.c, t2.a, t2.b, t2.c "
            "FROM t1 WITH (NOLOCK) JOIN t2 ON t1.a = t2.a",
        )

    def test_insert(self):
        t = table("sometable", column("somecolumn"))
        self.assert_compile(
            t.insert(),
            "INSERT INTO sometable (somecolumn) VALUES "
            "(:somecolumn)",
        )

    def test_update(self):
        t = table("sometable", column("somecolumn"))
        self.assert_compile(
            t.update(t.c.somecolumn == 7),
            "UPDATE sometable SET somecolumn=:somecolum"
            "n WHERE sometable.somecolumn = "
            ":somecolumn_1",
            dict(somecolumn=10),
        )

    def test_insert_hint(self):
        t = table("sometable", column("somecolumn"))
        for targ in (None, t):
            for darg in ("*", "mssql"):
                self.assert_compile(
                    t.insert().values(somecolumn="x").with_hint(
                        "WITH (PAGLOCK)", selectable=targ, dialect_name=darg),
                    "INSERT INTO sometable WITH (PAGLOCK) "
                    "(somecolumn) VALUES (:somecolumn)",
                )

    def test_update_hint(self):
        t = table("sometable", column("somecolumn"))
        for targ in (None, t):
            for darg in ("*", "mssql"):
                self.assert_compile(
                    t.update().where(t.c.somecolumn == "q").values(
                        somecolumn="x").with_hint("WITH (PAGLOCK)",
                                                  selectable=targ,
                                                  dialect_name=darg),
                    "UPDATE sometable WITH (PAGLOCK) "
                    "SET somecolumn=:somecolumn "
                    "WHERE sometable.somecolumn = :somecolumn_1",
                )

    def test_update_exclude_hint(self):
        t = table("sometable", column("somecolumn"))
        self.assert_compile(
            t.update().where(t.c.somecolumn == "q").values(
                somecolumn="x").with_hint("XYZ", "mysql"),
            "UPDATE sometable SET somecolumn=:somecolumn "
            "WHERE sometable.somecolumn = :somecolumn_1",
        )

    def test_delete_hint(self):
        t = table("sometable", column("somecolumn"))
        for targ in (None, t):
            for darg in ("*", "mssql"):
                self.assert_compile(
                    t.delete().where(t.c.somecolumn == "q").with_hint(
                        "WITH (PAGLOCK)", selectable=targ, dialect_name=darg),
                    "DELETE FROM sometable WITH (PAGLOCK) "
                    "WHERE sometable.somecolumn = :somecolumn_1",
                )

    def test_delete_exclude_hint(self):
        t = table("sometable", column("somecolumn"))
        self.assert_compile(
            t.delete().where(t.c.somecolumn == "q").with_hint(
                "XYZ", dialect_name="mysql"),
            "DELETE FROM sometable WHERE "
            "sometable.somecolumn = :somecolumn_1",
        )

    def test_delete_extra_froms(self):
        t1 = table("t1", column("c1"))
        t2 = table("t2", column("c1"))
        q = sql.delete(t1).where(t1.c.c1 == t2.c.c1)
        self.assert_compile(q,
                            "DELETE FROM t1 FROM t1, t2 WHERE t1.c1 = t2.c1")

    def test_delete_extra_froms_alias(self):
        a1 = table("t1", column("c1")).alias("a1")
        t2 = table("t2", column("c1"))
        q = sql.delete(a1).where(a1.c.c1 == t2.c.c1)
        self.assert_compile(
            q, "DELETE FROM a1 FROM t1 AS a1, t2 WHERE a1.c1 = t2.c1")
        self.assert_compile(sql.delete(a1), "DELETE FROM t1 AS a1")

    def test_update_from(self):
        metadata = MetaData()
        table1 = Table(
            "mytable",
            metadata,
            Column("myid", Integer),
            Column("name", String(30)),
            Column("description", String(50)),
        )
        table2 = Table(
            "myothertable",
            metadata,
            Column("otherid", Integer),
            Column("othername", String(30)),
        )

        mt = table1.alias()

        u = (table1.update().values(name="foo").where(
            table2.c.otherid == table1.c.myid))

        # testing mssql.base.MSSQLCompiler.update_from_clause
        self.assert_compile(
            u,
            "UPDATE mytable SET name=:name "
            "FROM mytable, myothertable WHERE "
            "myothertable.otherid = mytable.myid",
        )

        self.assert_compile(
            u.where(table2.c.othername == mt.c.name),
            "UPDATE mytable SET name=:name "
            "FROM mytable, myothertable, mytable AS mytable_1 "
            "WHERE myothertable.otherid = mytable.myid "
            "AND myothertable.othername = mytable_1.name",
        )

    def test_update_from_hint(self):
        t = table("sometable", column("somecolumn"))
        t2 = table("othertable", column("somecolumn"))
        for darg in ("*", "mssql"):
            self.assert_compile(
                t.update().where(t.c.somecolumn == t2.c.somecolumn).values(
                    somecolumn="x").with_hint("WITH (PAGLOCK)",
                                              selectable=t2,
                                              dialect_name=darg),
                "UPDATE sometable SET somecolumn=:somecolumn "
                "FROM sometable, othertable WITH (PAGLOCK) "
                "WHERE sometable.somecolumn = othertable.somecolumn",
            )

    def test_update_to_select_schema(self):
        meta = MetaData()
        table = Table(
            "sometable",
            meta,
            Column("sym", String),
            Column("val", Integer),
            schema="schema",
        )
        other = Table("#other", meta, Column("sym", String),
                      Column("newval", Integer))
        stmt = table.update().values(val=select([other.c.newval]).where(
            table.c.sym == other.c.sym).as_scalar())

        self.assert_compile(
            stmt,
            "UPDATE [schema].sometable SET val="
            "(SELECT [#other].newval FROM [#other] "
            "WHERE [schema].sometable.sym = [#other].sym)",
        )

        stmt = (table.update().values(val=other.c.newval).where(
            table.c.sym == other.c.sym))
        self.assert_compile(
            stmt,
            "UPDATE [schema].sometable SET val="
            "[#other].newval FROM [schema].sometable, "
            "[#other] WHERE [schema].sometable.sym = [#other].sym",
        )

    # TODO: not supported yet.
    # def test_delete_from_hint(self):
    #    t = table('sometable', column('somecolumn'))
    #    t2 = table('othertable', column('somecolumn'))
    #    for darg in ("*", "mssql"):
    #        self.assert_compile(
    #            t.delete().where(t.c.somecolumn==t2.c.somecolumn).
    #                    with_hint("WITH (PAGLOCK)",
    #                            selectable=t2,
    #                            dialect_name=darg),
    #            ""
    #        )

    def test_strict_binds(self):
        """test the 'strict' compiler binds."""

        from sqlalchemy_1_3.dialects.mssql.base import MSSQLStrictCompiler

        mxodbc_dialect = mxodbc.dialect()
        mxodbc_dialect.statement_compiler = MSSQLStrictCompiler

        t = table("sometable", column("foo"))

        for expr, compiled in [
            (
                select([literal("x"), literal("y")]),
                "SELECT 'x' AS anon_1, 'y' AS anon_2",
            ),
            (
                select([t]).where(t.c.foo.in_(["x", "y", "z"])),
                "SELECT sometable.foo FROM sometable WHERE sometable.foo "
                "IN ('x', 'y', 'z')",
            ),
            (t.c.foo.in_([None]), "sometable.foo IN (NULL)"),
        ]:
            self.assert_compile(expr, compiled, dialect=mxodbc_dialect)

    def test_in_with_subqueries(self):
        """Test removal of legacy behavior that converted "x==subquery"
        to use IN.

        """

        t = table("sometable", column("somecolumn"))
        self.assert_compile(
            t.select().where(t.c.somecolumn == t.select()),
            "SELECT sometable.somecolumn FROM "
            "sometable WHERE sometable.somecolumn = "
            "(SELECT sometable.somecolumn FROM "
            "sometable)",
        )
        self.assert_compile(
            t.select().where(t.c.somecolumn != t.select()),
            "SELECT sometable.somecolumn FROM "
            "sometable WHERE sometable.somecolumn != "
            "(SELECT sometable.somecolumn FROM "
            "sometable)",
        )

    @testing.uses_deprecated
    def test_count(self):
        t = table("sometable", column("somecolumn"))
        self.assert_compile(
            t.count(),
            "SELECT count(sometable.somecolumn) AS "
            "tbl_row_count FROM sometable",
        )

    def test_noorderby_insubquery(self):
        """test that the ms-sql dialect removes ORDER BY clauses from
        subqueries"""

        table1 = table(
            "mytable",
            column("myid", Integer),
            column("name", String),
            column("description", String),
        )

        q = select([table1.c.myid], order_by=[table1.c.myid]).alias("foo")
        crit = q.c.myid == table1.c.myid
        self.assert_compile(
            select(["*"], crit),
            "SELECT * FROM (SELECT mytable.myid AS "
            "myid FROM mytable) AS foo, mytable WHERE "
            "foo.myid = mytable.myid",
        )

    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_schema_many_tokens_one(self):
        metadata = MetaData()
        tbl = Table(
            "test",
            metadata,
            Column("id", Integer, primary_key=True),
            schema="abc.def.efg.hij",
        )

        # for now, we don't really know what the above means, at least
        # don't lose the dot
        self.assert_compile(
            select([tbl]),
            "SELECT [abc.def.efg].hij.test.id FROM [abc.def.efg].hij.test",
        )

        dbname, owner = mssql_base._schema_elements("abc.def.efg.hij")
        eq_(dbname, "abc.def.efg")
        assert not isinstance(dbname, quoted_name)
        eq_(owner, "hij")

    def test_schema_many_tokens_two(self):
        metadata = MetaData()
        tbl = Table(
            "test",
            metadata,
            Column("id", Integer, primary_key=True),
            schema="[abc].[def].[efg].[hij]",
        )

        self.assert_compile(
            select([tbl]),
            "SELECT [abc].[def].[efg].hij.test.id "
            "FROM [abc].[def].[efg].hij.test",
        )

    def test_force_schema_quoted_name_w_dot_case_insensitive(self):
        metadata = MetaData()
        tbl = Table(
            "test",
            metadata,
            Column("id", Integer, primary_key=True),
            schema=quoted_name("foo.dbo", True),
        )
        self.assert_compile(select([tbl]),
                            "SELECT [foo.dbo].test.id FROM [foo.dbo].test")

    def test_force_schema_quoted_w_dot_case_insensitive(self):
        metadata = MetaData()
        tbl = Table(
            "test",
            metadata,
            Column("id", Integer, primary_key=True),
            schema=quoted_name("foo.dbo", True),
        )
        self.assert_compile(select([tbl]),
                            "SELECT [foo.dbo].test.id FROM [foo.dbo].test")

    def test_force_schema_quoted_name_w_dot_case_sensitive(self):
        metadata = MetaData()
        tbl = Table(
            "test",
            metadata,
            Column("id", Integer, primary_key=True),
            schema=quoted_name("Foo.dbo", True),
        )
        self.assert_compile(select([tbl]),
                            "SELECT [Foo.dbo].test.id FROM [Foo.dbo].test")

    def test_force_schema_quoted_w_dot_case_sensitive(self):
        metadata = MetaData()
        tbl = Table(
            "test",
            metadata,
            Column("id", Integer, primary_key=True),
            schema="[Foo.dbo]",
        )
        self.assert_compile(select([tbl]),
                            "SELECT [Foo.dbo].test.id FROM [Foo.dbo].test")

    def test_schema_autosplit_w_dot_case_insensitive(self):
        metadata = MetaData()
        tbl = Table(
            "test",
            metadata,
            Column("id", Integer, primary_key=True),
            schema="foo.dbo",
        )
        self.assert_compile(select([tbl]),
                            "SELECT foo.dbo.test.id FROM foo.dbo.test")

    def test_schema_autosplit_w_dot_case_sensitive(self):
        metadata = MetaData()
        tbl = Table(
            "test",
            metadata,
            Column("id", Integer, primary_key=True),
            schema="Foo.dbo",
        )
        self.assert_compile(select([tbl]),
                            "SELECT [Foo].dbo.test.id FROM [Foo].dbo.test")

    def test_delete_schema(self):
        metadata = MetaData()
        tbl = Table(
            "test",
            metadata,
            Column("id", Integer, primary_key=True),
            schema="paj",
        )
        self.assert_compile(
            tbl.delete(tbl.c.id == 1),
            "DELETE FROM paj.test WHERE paj.test.id = "
            ":id_1",
        )
        s = select([tbl.c.id]).where(tbl.c.id == 1)
        self.assert_compile(
            tbl.delete().where(tbl.c.id.in_(s)),
            "DELETE FROM paj.test WHERE paj.test.id IN "
            "(SELECT paj.test.id FROM paj.test "
            "WHERE paj.test.id = :id_1)",
        )

    def test_delete_schema_multipart(self):
        metadata = MetaData()
        tbl = Table(
            "test",
            metadata,
            Column("id", Integer, primary_key=True),
            schema="banana.paj",
        )
        self.assert_compile(
            tbl.delete(tbl.c.id == 1),
            "DELETE FROM banana.paj.test WHERE "
            "banana.paj.test.id = :id_1",
        )
        s = select([tbl.c.id]).where(tbl.c.id == 1)
        self.assert_compile(
            tbl.delete().where(tbl.c.id.in_(s)),
            "DELETE FROM banana.paj.test WHERE "
            "banana.paj.test.id IN (SELECT banana.paj.test.id "
            "FROM banana.paj.test WHERE "
            "banana.paj.test.id = :id_1)",
        )

    def test_delete_schema_multipart_needs_quoting(self):
        metadata = MetaData()
        tbl = Table(
            "test",
            metadata,
            Column("id", Integer, primary_key=True),
            schema="banana split.paj",
        )
        self.assert_compile(
            tbl.delete(tbl.c.id == 1),
            "DELETE FROM [banana split].paj.test WHERE "
            "[banana split].paj.test.id = :id_1",
        )
        s = select([tbl.c.id]).where(tbl.c.id == 1)
        self.assert_compile(
            tbl.delete().where(tbl.c.id.in_(s)),
            "DELETE FROM [banana split].paj.test WHERE "
            "[banana split].paj.test.id IN ("
            "SELECT [banana split].paj.test.id FROM "
            "[banana split].paj.test WHERE "
            "[banana split].paj.test.id = :id_1)",
        )

    def test_delete_schema_multipart_both_need_quoting(self):
        metadata = MetaData()
        tbl = Table(
            "test",
            metadata,
            Column("id", Integer, primary_key=True),
            schema="banana split.paj with a space",
        )
        self.assert_compile(
            tbl.delete(tbl.c.id == 1),
            "DELETE FROM [banana split].[paj with a "
            "space].test WHERE [banana split].[paj "
            "with a space].test.id = :id_1",
        )
        s = select([tbl.c.id]).where(tbl.c.id == 1)
        self.assert_compile(
            tbl.delete().where(tbl.c.id.in_(s)),
            "DELETE FROM [banana split].[paj with a space].test "
            "WHERE [banana split].[paj with a space].test.id IN "
            "(SELECT [banana split].[paj with a space].test.id "
            "FROM [banana split].[paj with a space].test "
            "WHERE [banana split].[paj with a space].test.id = :id_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",
        )

    def test_function(self):
        self.assert_compile(func.foo(1, 2), "foo(:foo_1, :foo_2)")
        self.assert_compile(func.current_time(), "CURRENT_TIME")
        self.assert_compile(func.foo(), "foo()")
        m = MetaData()
        t = Table("sometable", m, Column("col1", Integer),
                  Column("col2", Integer))
        self.assert_compile(
            select([func.max(t.c.col1)]),
            "SELECT max(sometable.col1) AS max_1 FROM "
            "sometable",
        )

    def test_function_overrides(self):
        self.assert_compile(func.current_date(), "GETDATE()")
        self.assert_compile(func.length(3), "LEN(:length_1)")

    def test_extract(self):
        t = table("t", column("col1"))

        for field in "day", "month", "year":
            self.assert_compile(
                select([extract(field, t.c.col1)]),
                "SELECT DATEPART(%s, t.col1) AS anon_1 FROM t" % field,
            )

    def test_update_returning(self):
        table1 = table(
            "mytable",
            column("myid", Integer),
            column("name", String(128)),
            column("description", String(128)),
        )
        u = update(table1,
                   values=dict(name="foo")).returning(table1.c.myid,
                                                      table1.c.name)
        self.assert_compile(
            u,
            "UPDATE mytable SET name=:name OUTPUT "
            "inserted.myid, inserted.name",
        )
        u = update(table1, values=dict(name="foo")).returning(table1)
        self.assert_compile(
            u,
            "UPDATE mytable SET name=:name OUTPUT "
            "inserted.myid, inserted.name, "
            "inserted.description",
        )
        u = (update(table1, values=dict(name="foo")).returning(table1).where(
            table1.c.name == "bar"))
        self.assert_compile(
            u,
            "UPDATE mytable SET name=:name OUTPUT "
            "inserted.myid, inserted.name, "
            "inserted.description WHERE mytable.name = "
            ":name_1",
        )
        u = update(table1, values=dict(name="foo")).returning(
            func.length(table1.c.name))
        self.assert_compile(
            u,
            "UPDATE mytable SET name=:name OUTPUT "
            "LEN(inserted.name) AS length_1",
        )

    def test_delete_returning(self):
        table1 = table(
            "mytable",
            column("myid", Integer),
            column("name", String(128)),
            column("description", String(128)),
        )
        d = delete(table1).returning(table1.c.myid, table1.c.name)
        self.assert_compile(
            d, "DELETE FROM mytable OUTPUT deleted.myid, "
            "deleted.name")
        d = (delete(table1).where(table1.c.name == "bar").returning(
            table1.c.myid, table1.c.name))
        self.assert_compile(
            d,
            "DELETE FROM mytable OUTPUT deleted.myid, "
            "deleted.name WHERE mytable.name = :name_1",
        )

    def test_insert_returning(self):
        table1 = table(
            "mytable",
            column("myid", Integer),
            column("name", String(128)),
            column("description", String(128)),
        )
        i = insert(table1,
                   values=dict(name="foo")).returning(table1.c.myid,
                                                      table1.c.name)
        self.assert_compile(
            i,
            "INSERT INTO mytable (name) OUTPUT "
            "inserted.myid, inserted.name VALUES "
            "(:name)",
        )
        i = insert(table1, values=dict(name="foo")).returning(table1)
        self.assert_compile(
            i,
            "INSERT INTO mytable (name) OUTPUT "
            "inserted.myid, inserted.name, "
            "inserted.description VALUES (:name)",
        )
        i = insert(table1, values=dict(name="foo")).returning(
            func.length(table1.c.name))
        self.assert_compile(
            i,
            "INSERT INTO mytable (name) OUTPUT "
            "LEN(inserted.name) AS length_1 VALUES "
            "(:name)",
        )

    def test_limit_using_top(self):
        t = table("t", column("x", Integer), column("y", Integer))

        s = select([t]).where(t.c.x == 5).order_by(t.c.y).limit(10)

        self.assert_compile(
            s,
            "SELECT TOP 10 t.x, t.y FROM t WHERE t.x = :x_1 ORDER BY t.y",
            checkparams={"x_1": 5},
        )

    def test_limit_zero_using_top(self):
        t = table("t", column("x", Integer), column("y", Integer))

        s = select([t]).where(t.c.x == 5).order_by(t.c.y).limit(0)

        self.assert_compile(
            s,
            "SELECT TOP 0 t.x, t.y FROM t WHERE t.x = :x_1 ORDER BY t.y",
            checkparams={"x_1": 5},
        )
        c = s.compile(dialect=mssql.dialect())
        eq_(len(c._result_columns), 2)
        assert t.c.x in set(c._create_result_map()["x"][1])

    def test_offset_using_window(self):
        t = table("t", column("x", Integer), column("y", Integer))

        s = select([t]).where(t.c.x == 5).order_by(t.c.y).offset(20)

        # test that the select is not altered with subsequent compile
        # calls
        for i in range(2):
            self.assert_compile(
                s,
                "SELECT anon_1.x, anon_1.y FROM (SELECT t.x AS x, t.y "
                "AS y, ROW_NUMBER() OVER (ORDER BY t.y) AS "
                "mssql_rn FROM t WHERE t.x = :x_1) AS "
                "anon_1 WHERE mssql_rn > :param_1",
                checkparams={
                    "param_1": 20,
                    "x_1": 5
                },
            )

            c = s.compile(dialect=mssql.dialect())
            eq_(len(c._result_columns), 2)
            assert t.c.x in set(c._create_result_map()["x"][1])

    def test_simple_limit_expression_offset_using_window(self):
        t = table("t", column("x", Integer), column("y", Integer))

        s = (select([t]).where(t.c.x == 5).order_by(t.c.y).limit(10).offset(
            literal_column("20")))

        self.assert_compile(
            s,
            "SELECT anon_1.x, anon_1.y "
            "FROM (SELECT t.x AS x, t.y AS y, "
            "ROW_NUMBER() OVER (ORDER BY t.y) AS mssql_rn "
            "FROM t "
            "WHERE t.x = :x_1) AS anon_1 "
            "WHERE mssql_rn > 20 AND mssql_rn <= :param_1 + 20",
            checkparams={
                "param_1": 10,
                "x_1": 5
            },
        )

    def test_limit_offset_using_window(self):
        t = table("t", column("x", Integer), column("y", Integer))

        s = select([t]).where(t.c.x == 5).order_by(t.c.y).limit(10).offset(20)

        self.assert_compile(
            s,
            "SELECT anon_1.x, anon_1.y "
            "FROM (SELECT t.x AS x, t.y AS y, "
            "ROW_NUMBER() OVER (ORDER BY t.y) AS mssql_rn "
            "FROM t "
            "WHERE t.x = :x_1) AS anon_1 "
            "WHERE mssql_rn > :param_1 AND mssql_rn <= :param_2 + :param_1",
            checkparams={
                "param_1": 20,
                "param_2": 10,
                "x_1": 5
            },
        )
        c = s.compile(dialect=mssql.dialect())
        eq_(len(c._result_columns), 2)
        assert t.c.x in set(c._create_result_map()["x"][1])
        assert t.c.y in set(c._create_result_map()["y"][1])

    def test_limit_offset_w_ambiguous_cols(self):
        t = table("t", column("x", Integer), column("y", Integer))

        cols = [t.c.x, t.c.x.label("q"), t.c.x.label("p"), t.c.y]
        s = select(cols).where(t.c.x == 5).order_by(t.c.y).limit(10).offset(20)

        self.assert_compile(
            s,
            "SELECT anon_1.x, anon_1.q, anon_1.p, anon_1.y "
            "FROM (SELECT t.x AS x, t.x AS q, t.x AS p, t.y AS y, "
            "ROW_NUMBER() OVER (ORDER BY t.y) AS mssql_rn "
            "FROM t "
            "WHERE t.x = :x_1) AS anon_1 "
            "WHERE mssql_rn > :param_1 AND mssql_rn <= :param_2 + :param_1",
            checkparams={
                "param_1": 20,
                "param_2": 10,
                "x_1": 5
            },
        )
        c = s.compile(dialect=mssql.dialect())
        eq_(len(c._result_columns), 4)

        result_map = c._create_result_map()

        for col in cols:
            is_(result_map[col.key][1][0], col)

    def test_limit_offset_with_correlated_order_by(self):
        t1 = table("t1", column("x", Integer), column("y", Integer))
        t2 = table("t2", column("x", Integer), column("y", Integer))

        order_by = select([t2.c.y]).where(t1.c.x == t2.c.x).as_scalar()
        s = (select(
            [t1]).where(t1.c.x == 5).order_by(order_by).limit(10).offset(20))

        self.assert_compile(
            s,
            "SELECT anon_1.x, anon_1.y "
            "FROM (SELECT t1.x AS x, t1.y AS y, "
            "ROW_NUMBER() OVER (ORDER BY "
            "(SELECT t2.y FROM t2 WHERE t1.x = t2.x)"
            ") AS mssql_rn "
            "FROM t1 "
            "WHERE t1.x = :x_1) AS anon_1 "
            "WHERE mssql_rn > :param_1 AND mssql_rn <= :param_2 + :param_1",
            checkparams={
                "param_1": 20,
                "param_2": 10,
                "x_1": 5
            },
        )

        c = s.compile(dialect=mssql.dialect())
        eq_(len(c._result_columns), 2)
        assert t1.c.x in set(c._create_result_map()["x"][1])
        assert t1.c.y in set(c._create_result_map()["y"][1])

    def test_offset_dont_misapply_labelreference(self):
        m = MetaData()

        t = Table("t", m, Column("x", Integer))

        expr1 = func.foo(t.c.x).label("x")
        expr2 = func.foo(t.c.x).label("y")

        stmt1 = select([expr1]).order_by(expr1.desc()).offset(1)
        stmt2 = select([expr2]).order_by(expr2.desc()).offset(1)

        self.assert_compile(
            stmt1,
            "SELECT anon_1.x FROM (SELECT foo(t.x) AS x, "
            "ROW_NUMBER() OVER (ORDER BY foo(t.x) DESC) AS mssql_rn FROM t) "
            "AS anon_1 WHERE mssql_rn > :param_1",
        )

        self.assert_compile(
            stmt2,
            "SELECT anon_1.y FROM (SELECT foo(t.x) AS y, "
            "ROW_NUMBER() OVER (ORDER BY foo(t.x) DESC) AS mssql_rn FROM t) "
            "AS anon_1 WHERE mssql_rn > :param_1",
        )

    def test_limit_zero_offset_using_window(self):
        t = table("t", column("x", Integer), column("y", Integer))

        s = select([t]).where(t.c.x == 5).order_by(t.c.y).limit(0).offset(0)

        # render the LIMIT of zero, but not the OFFSET
        # of zero, so produces TOP 0
        self.assert_compile(
            s,
            "SELECT TOP 0 t.x, t.y FROM t "
            "WHERE t.x = :x_1 ORDER BY t.y",
            checkparams={"x_1": 5},
        )

    def test_primary_key_no_identity(self):
        metadata = MetaData()
        tbl = Table(
            "test",
            metadata,
            Column("id", Integer, autoincrement=False, primary_key=True),
        )
        self.assert_compile(
            schema.CreateTable(tbl),
            "CREATE TABLE test (id INTEGER NOT NULL, "
            "PRIMARY KEY (id))",
        )

    def test_primary_key_defaults_to_identity(self):
        metadata = MetaData()
        tbl = Table("test", metadata, Column("id", Integer, primary_key=True))
        self.assert_compile(
            schema.CreateTable(tbl),
            "CREATE TABLE test (id INTEGER NOT NULL IDENTITY(1,1), "
            "PRIMARY KEY (id))",
        )

    def test_identity_no_primary_key(self):
        metadata = MetaData()
        tbl = Table("test", metadata, Column("id", Integer,
                                             autoincrement=True))
        self.assert_compile(
            schema.CreateTable(tbl),
            "CREATE TABLE test (id INTEGER NOT NULL IDENTITY(1,1)"
            ")",
        )

    def test_identity_separate_from_primary_key(self):
        metadata = MetaData()
        tbl = Table(
            "test",
            metadata,
            Column("id", Integer, autoincrement=False, primary_key=True),
            Column("x", Integer, autoincrement=True),
        )
        self.assert_compile(
            schema.CreateTable(tbl),
            "CREATE TABLE test (id INTEGER NOT NULL, "
            "x INTEGER NOT NULL IDENTITY(1,1), "
            "PRIMARY KEY (id))",
        )

    def test_identity_illegal_two_autoincrements(self):
        metadata = MetaData()
        tbl = Table(
            "test",
            metadata,
            Column("id", Integer, autoincrement=True),
            Column("id2", Integer, autoincrement=True),
        )
        # this will be rejected by the database, just asserting this is what
        # the two autoincrements will do right now
        self.assert_compile(
            schema.CreateTable(tbl),
            "CREATE TABLE test (id INTEGER NOT NULL IDENTITY(1,1), "
            "id2 INTEGER NOT NULL IDENTITY(1,1))",
        )

    def test_identity_start_0(self):
        metadata = MetaData()
        tbl = Table(
            "test",
            metadata,
            Column("id", Integer, mssql_identity_start=0, primary_key=True),
        )
        self.assert_compile(
            schema.CreateTable(tbl),
            "CREATE TABLE test (id INTEGER NOT NULL IDENTITY(0,1), "
            "PRIMARY KEY (id))",
        )

    def test_identity_increment_5(self):
        metadata = MetaData()
        tbl = Table(
            "test",
            metadata,
            Column("id", Integer, mssql_identity_increment=5,
                   primary_key=True),
        )
        self.assert_compile(
            schema.CreateTable(tbl),
            "CREATE TABLE test (id INTEGER NOT NULL IDENTITY(1,5), "
            "PRIMARY KEY (id))",
        )

    def test_sequence_start_0(self):
        metadata = MetaData()
        tbl = Table(
            "test",
            metadata,
            Column("id", Integer, Sequence("", 0), primary_key=True),
        )
        with testing.expect_deprecated(
                "Use of Sequence with SQL Server in order to affect "):
            self.assert_compile(
                schema.CreateTable(tbl),
                "CREATE TABLE test (id INTEGER NOT NULL IDENTITY(0,1), "
                "PRIMARY KEY (id))",
            )

    def test_sequence_non_primary_key(self):
        metadata = MetaData()
        tbl = Table(
            "test",
            metadata,
            Column("id", Integer, Sequence("", start=5), primary_key=False),
        )
        with testing.expect_deprecated(
                "Use of Sequence with SQL Server in order to affect "):
            self.assert_compile(
                schema.CreateTable(tbl),
                "CREATE TABLE test (id INTEGER NOT NULL IDENTITY(5,1))",
            )

    def test_sequence_ignore_nullability(self):
        metadata = MetaData()
        tbl = Table(
            "test",
            metadata,
            Column("id", Integer, Sequence("", start=5), nullable=True),
        )
        with testing.expect_deprecated(
                "Use of Sequence with SQL Server in order to affect "):
            self.assert_compile(
                schema.CreateTable(tbl),
                "CREATE TABLE test (id INTEGER NOT NULL IDENTITY(5,1))",
            )

    def test_table_pkc_clustering(self):
        metadata = MetaData()
        tbl = Table(
            "test",
            metadata,
            Column("x", Integer, autoincrement=False),
            Column("y", Integer, autoincrement=False),
            PrimaryKeyConstraint("x", "y", mssql_clustered=True),
        )
        self.assert_compile(
            schema.CreateTable(tbl),
            "CREATE TABLE test (x INTEGER NOT NULL, y INTEGER NOT NULL, "
            "PRIMARY KEY CLUSTERED (x, y))",
        )

    def test_table_pkc_explicit_nonclustered(self):
        metadata = MetaData()
        tbl = Table(
            "test",
            metadata,
            Column("x", Integer, autoincrement=False),
            Column("y", Integer, autoincrement=False),
            PrimaryKeyConstraint("x", "y", mssql_clustered=False),
        )
        self.assert_compile(
            schema.CreateTable(tbl),
            "CREATE TABLE test (x INTEGER NOT NULL, y INTEGER NOT NULL, "
            "PRIMARY KEY NONCLUSTERED (x, y))",
        )

    def test_table_idx_explicit_nonclustered(self):
        metadata = MetaData()
        tbl = Table(
            "test",
            metadata,
            Column("x", Integer, autoincrement=False),
            Column("y", Integer, autoincrement=False),
        )

        idx = Index("myidx", tbl.c.x, tbl.c.y, mssql_clustered=False)
        self.assert_compile(
            schema.CreateIndex(idx),
            "CREATE NONCLUSTERED INDEX myidx ON test (x, y)",
        )

    def test_table_uc_explicit_nonclustered(self):
        metadata = MetaData()
        tbl = Table(
            "test",
            metadata,
            Column("x", Integer, autoincrement=False),
            Column("y", Integer, autoincrement=False),
            UniqueConstraint("x", "y", mssql_clustered=False),
        )
        self.assert_compile(
            schema.CreateTable(tbl),
            "CREATE TABLE test (x INTEGER NULL, y INTEGER NULL, "
            "UNIQUE NONCLUSTERED (x, y))",
        )

    def test_table_uc_clustering(self):
        metadata = MetaData()
        tbl = Table(
            "test",
            metadata,
            Column("x", Integer, autoincrement=False),
            Column("y", Integer, autoincrement=False),
            PrimaryKeyConstraint("x"),
            UniqueConstraint("y", mssql_clustered=True),
        )
        self.assert_compile(
            schema.CreateTable(tbl),
            "CREATE TABLE test (x INTEGER NOT NULL, y INTEGER NULL, "
            "PRIMARY KEY (x), UNIQUE CLUSTERED (y))",
        )

    def test_index_clustering(self):
        metadata = MetaData()
        tbl = Table("test", metadata, Column("id", Integer))
        idx = Index("foo", tbl.c.id, mssql_clustered=True)
        self.assert_compile(schema.CreateIndex(idx),
                            "CREATE CLUSTERED INDEX foo ON test (id)")

    def test_index_where(self):
        metadata = MetaData()
        tbl = Table("test", metadata, Column("data", Integer))
        idx = Index("test_idx_data_1", tbl.c.data, mssql_where=tbl.c.data > 1)
        self.assert_compile(
            schema.CreateIndex(idx),
            "CREATE INDEX test_idx_data_1 ON test (data) WHERE data > 1",
        )

    def test_index_ordering(self):
        metadata = MetaData()
        tbl = Table(
            "test",
            metadata,
            Column("x", Integer),
            Column("y", Integer),
            Column("z", Integer),
        )
        idx = Index("foo", tbl.c.x.desc(), "y")
        self.assert_compile(schema.CreateIndex(idx),
                            "CREATE INDEX foo ON test (x DESC, y)")

    def test_create_index_expr(self):
        m = MetaData()
        t1 = Table("foo", m, Column("x", Integer))
        self.assert_compile(
            schema.CreateIndex(Index("bar", t1.c.x > 5)),
            "CREATE INDEX bar ON foo (x > 5)",
        )

    def test_drop_index_w_schema(self):
        m = MetaData()
        t1 = Table("foo", m, Column("x", Integer), schema="bar")
        self.assert_compile(
            schema.DropIndex(Index("idx_foo", t1.c.x)),
            "DROP INDEX idx_foo ON bar.foo",
        )

    def test_index_extra_include_1(self):
        metadata = MetaData()
        tbl = Table(
            "test",
            metadata,
            Column("x", Integer),
            Column("y", Integer),
            Column("z", Integer),
        )
        idx = Index("foo", tbl.c.x, mssql_include=["y"])
        self.assert_compile(schema.CreateIndex(idx),
                            "CREATE INDEX foo ON test (x) INCLUDE (y)")

    def test_index_extra_include_2(self):
        metadata = MetaData()
        tbl = Table(
            "test",
            metadata,
            Column("x", Integer),
            Column("y", Integer),
            Column("z", Integer),
        )
        idx = Index("foo", tbl.c.x, mssql_include=[tbl.c.y])
        self.assert_compile(schema.CreateIndex(idx),
                            "CREATE INDEX foo ON test (x) INCLUDE (y)")

    def test_try_cast(self):
        metadata = MetaData()
        t1 = Table("t1", metadata, Column("id", Integer, primary_key=True))

        self.assert_compile(
            select([try_cast(t1.c.id, Integer)]),
            "SELECT TRY_CAST (t1.id AS INTEGER) AS anon_1 FROM t1",
        )

    @testing.combinations(
        ("no_persisted", "", "ignore"),
        ("persisted_none", "", None),
        ("persisted_true", " PERSISTED", True),
        ("persisted_false", "", False),
        id_="iaa",
    )
    def test_column_computed(self, text, persisted):
        m = MetaData()
        kwargs = {"persisted": persisted} if persisted != "ignore" else {}
        t = Table(
            "t",
            m,
            Column("x", Integer),
            Column("y", Integer, Computed("x + 2", **kwargs)),
        )
        self.assert_compile(
            schema.CreateTable(t),
            "CREATE TABLE t (x INTEGER NULL, y AS (x + 2)%s)" % text,
        )