"""These tests are all about the "join rewriting" feature built
to support SQLite's lack of right-nested joins.  SQLite as of
version 3.7.16 no longer has this limitation.

"""
from sqlalchemy import Column
from sqlalchemy import exists
from sqlalchemy import ForeignKey
from sqlalchemy import Integer
from sqlalchemy import MetaData
from sqlalchemy import select
from sqlalchemy import Table
from sqlalchemy import testing
from sqlalchemy import union
from sqlalchemy import util
from sqlalchemy.engine import default
from sqlalchemy.testing import AssertsCompiledSQL
from sqlalchemy.testing import fixtures


m = MetaData()


a = Table("a", m, Column("id", Integer, primary_key=True))

b = Table(
    "b",
    m,
    Column("id", Integer, primary_key=True),
    Column("a_id", Integer, ForeignKey("a.id")),
)

b_a = Table("b_a", m, Column("id", Integer, primary_key=True))

b1 = Table(
    "b1",
    m,
    Column("id", Integer, primary_key=True),
    Column("a_id", Integer, ForeignKey("a.id")),
)

b2 = Table(
    "b2",
    m,
    Column("id", Integer, primary_key=True),
    Column("a_id", Integer, ForeignKey("a.id")),
)

a_to_b = Table(
    "a_to_b",
    m,
    Column("a_id", Integer, ForeignKey("a.id")),
    Column("b_id", Integer, ForeignKey("b.id")),
)

c = Table(
    "c",
    m,
    Column("id", Integer, primary_key=True),
    Column("b_id", Integer, ForeignKey("b.id")),
)

d = Table(
    "d",
    m,
    Column("id", Integer, primary_key=True),
    Column("c_id", Integer, ForeignKey("c.id")),
)

e = Table("e", m, Column("id", Integer, primary_key=True))

f = Table(
    "f",
    m,
    Column("id", Integer, primary_key=True),
    Column("a_id", ForeignKey("a.id")),
)

b_key = Table("b_key", m, Column("id", Integer, primary_key=True, key="bid"))

a_to_b_key = Table(
    "a_to_b_key",
    m,
    Column("aid", Integer, ForeignKey("a.id")),
    Column("bid", Integer, ForeignKey("b_key.bid")),
)


class _JoinRewriteTestBase(AssertsCompiledSQL):
    def _test(self, s, assert_):
        self.assert_compile(s, assert_)

        compiled = s.compile(dialect=self.__dialect__)

        # column name should be in result map, as we never render
        # .key in SQL
        for key, col in zip([c.name for c in s.c], s.inner_columns):
            key = key % compiled.anon_map
            assert col in compiled._create_result_map()[key][1]

    _a_bkeyselect_bkey = ""

    def test_a_bkeyselect_bkey(self):
        assoc = a_to_b_key.select().alias()
        j1 = assoc.join(b_key)
        j2 = a.join(j1)

        s = select([a, b_key], use_labels=True).select_from(j2)
        self._test(s, self._a_bkeyselect_bkey)

    def test_a_bc(self):
        j1 = b.join(c)
        j2 = a.join(j1)

        # TODO: if we remove 'b' or 'c', shouldn't we get just
        # the subset of cols from anon_1 ?

        # TODO: do this test also with individual cols, things change
        # lots based on how you go with this

        s = (
            select([a, b, c], use_labels=True)
            .select_from(j2)
            .where(b.c.id == 2)
            .where(c.c.id == 3)
            .order_by(a.c.id, b.c.id, c.c.id)
        )

        self._test(s, self._a_bc)

    def test_a_bkeyassoc(self):
        j1 = b_key.join(a_to_b_key)
        j2 = a.join(j1)

        s = select([a, b_key.c.bid], use_labels=True).select_from(j2)

        self._test(s, self._a_bkeyassoc)

    def test_a_bkeyassoc_aliased(self):
        bkey_alias = b_key.alias()
        a_to_b_key_alias = a_to_b_key.alias()

        j1 = bkey_alias.join(a_to_b_key_alias)
        j2 = a.join(j1)

        s = select([a, bkey_alias.c.bid], use_labels=True).select_from(j2)

        self._test(s, self._a_bkeyassoc_aliased)

    def test_a__b_dc(self):
        j1 = c.join(d)
        j2 = b.join(j1)
        j3 = a.join(j2)

        s = (
            select([a, b, c, d], use_labels=True)
            .select_from(j3)
            .where(b.c.id == 2)
            .where(c.c.id == 3)
            .where(d.c.id == 4)
            .order_by(a.c.id, b.c.id, c.c.id, d.c.id)
        )

        self._test(s, self._a__b_dc)

    def test_a_bc_comma_a1_selbc(self):
        # test here we're emulating is
        # test.orm.inheritance.test_polymorphic_rel:
        #  PolymorphicJoinsTest.test_multi_join
        j1 = b.join(c)
        j2 = b.join(c).select(use_labels=True).alias()
        j3 = a.join(j1)
        a_a = a.alias()
        j4 = a_a.join(j2)

        s = (
            select([a, a_a, b, c, j2], use_labels=True)
            .select_from(j3)
            .select_from(j4)
            .order_by(j2.c.b_id)
        )

        self._test(s, self._a_bc_comma_a1_selbc)

    def test_a_atobalias_balias_c_w_exists(self):
        a_to_b_alias = a_to_b.alias()
        b_alias = b.alias()

        j1 = a_to_b_alias.join(b_alias)
        j2 = a.outerjoin(j1, a.c.id == a_to_b_alias.c.a_id)

        # TODO: if we put straight a_to_b_alias here,
        # it fails to alias the columns clause.
        s = select(
            [
                a,
                a_to_b_alias.c.a_id,
                a_to_b_alias.c.b_id,
                b_alias.c.id,
                b_alias.c.a_id,
                exists()
                .select_from(c)
                .where(c.c.b_id == b_alias.c.id)
                .label(None),
            ],
            use_labels=True,
        ).select_from(j2)

        self._test(s, self._a_atobalias_balias_c_w_exists)

    def test_a_atobalias_balias(self):
        a_to_b_alias = a_to_b.alias()
        b_alias = b.alias()

        j1 = a_to_b_alias.join(b_alias)
        j2 = a.outerjoin(j1, a.c.id == a_to_b_alias.c.a_id)

        s = select([a, a_to_b_alias, b_alias], use_labels=True).select_from(j2)

        self._test(s, self._a_atobalias_balias)

    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_b_a_id_double_overlap_annotated(self):
        # test issue #3057
        # this involves annotations so try to loop those in.
        j1 = b.join(b_a, b.c.id == b_a.c.id)
        annot = [
            b.c.id._annotate({}),
            b.c.a_id._annotate({}),
            b_a.c.id._annotate({}),
        ]

        s = select(annot).select_from(j1).apply_labels().alias()

        s = select(list(s.c)).apply_labels()

        self._test(s, self._b_a_id_double_overlap_annotated)

    def test_f_b1a_where_in_b2a(self):
        # test issue #3130
        b1a = a.join(b1)
        b2a = a.join(b2)
        subq = select([b2.c.id]).select_from(b2a)
        s = select([f]).select_from(f.join(b1a)).where(b1.c.id.in_(subq))

        s = s.apply_labels()
        self._test(s, self._f_b1a_where_in_b2a)

    def test_anon_scalar_subqueries(self):
        s1 = select([1]).as_scalar()
        s2 = select([2]).as_scalar()

        s = select([s1, s2]).apply_labels()
        self._test(s, self._anon_scalar_subqueries)


class JoinRewriteTest(_JoinRewriteTestBase, fixtures.TestBase):

    """test rendering of each join with right-nested rewritten as
    aliased SELECT statements.."""

    @util.classproperty
    def __dialect__(cls):
        dialect = default.DefaultDialect()
        dialect.supports_right_nested_joins = False
        return dialect

    _a__b_dc = (
        "SELECT a.id AS a_id, anon_1.b_id AS b_id, "
        "anon_1.b_a_id AS b_a_id, anon_1.c_id AS c_id, "
        "anon_1.c_b_id AS c_b_id, anon_1.d_id AS d_id, "
        "anon_1.d_c_id AS d_c_id "
        "FROM a JOIN (SELECT b.id AS b_id, b.a_id AS b_a_id, "
        "anon_2.c_id AS c_id, anon_2.c_b_id AS c_b_id, "
        "anon_2.d_id AS d_id, anon_2.d_c_id AS d_c_id "
        "FROM b JOIN (SELECT c.id AS c_id, c.b_id AS c_b_id, "
        "d.id AS d_id, d.c_id AS d_c_id "
        "FROM c JOIN d ON c.id = d.c_id) AS anon_2 "
        "ON b.id = anon_2.c_b_id) AS anon_1 ON a.id = anon_1.b_a_id "
        "WHERE anon_1.b_id = :id_1 AND anon_1.c_id = :id_2 AND "
        "anon_1.d_id = :id_3 "
        "ORDER BY a.id, anon_1.b_id, anon_1.c_id, anon_1.d_id"
    )

    _a_bc = (
        "SELECT a.id AS a_id, anon_1.b_id AS b_id, "
        "anon_1.b_a_id AS b_a_id, anon_1.c_id AS c_id, "
        "anon_1.c_b_id AS c_b_id FROM a JOIN "
        "(SELECT b.id AS b_id, b.a_id AS b_a_id, "
        "c.id AS c_id, c.b_id AS c_b_id "
        "FROM b JOIN c ON b.id = c.b_id) AS anon_1 "
        "ON a.id = anon_1.b_a_id "
        "WHERE anon_1.b_id = :id_1 AND anon_1.c_id = :id_2 "
        "ORDER BY a.id, anon_1.b_id, anon_1.c_id"
    )

    _a_bc_comma_a1_selbc = (
        "SELECT a.id AS a_id, a_1.id AS a_1_id, anon_1.b_id AS b_id, "
        "anon_1.b_a_id AS b_a_id, anon_1.c_id AS c_id, "
        "anon_1.c_b_id AS c_b_id, anon_2.b_id AS anon_2_b_id, "
        "anon_2.b_a_id AS anon_2_b_a_id, anon_2.c_id AS anon_2_c_id, "
        "anon_2.c_b_id AS anon_2_c_b_id FROM a "
        "JOIN (SELECT b.id AS b_id, b.a_id AS b_a_id, c.id AS c_id, "
        "c.b_id AS c_b_id FROM b JOIN c ON b.id = c.b_id) AS anon_1 "
        "ON a.id = anon_1.b_a_id, "
        "a AS a_1 JOIN "
        "(SELECT b.id AS b_id, b.a_id AS b_a_id, "
        "c.id AS c_id, c.b_id AS c_b_id "
        "FROM b JOIN c ON b.id = c.b_id) AS anon_2 "
        "ON a_1.id = anon_2.b_a_id ORDER BY anon_2.b_id"
    )

    _a_bkeyassoc = (
        "SELECT a.id AS a_id, anon_1.b_key_id AS b_key_id "
        "FROM a JOIN "
        "(SELECT b_key.id AS b_key_id, a_to_b_key.aid AS a_to_b_key_aid, "
        "a_to_b_key.bid AS a_to_b_key_bid FROM b_key "
        "JOIN a_to_b_key ON b_key.id = a_to_b_key.bid) AS anon_1 "
        "ON a.id = anon_1.a_to_b_key_aid"
    )

    _a_bkeyassoc_aliased = (
        "SELECT a.id AS a_id, anon_1.b_key_1_id AS b_key_1_id "
        "FROM a JOIN (SELECT b_key_1.id AS b_key_1_id, "
        "a_to_b_key_1.aid AS a_to_b_key_1_aid, "
        "a_to_b_key_1.bid AS a_to_b_key_1_bid FROM b_key AS b_key_1 "
        "JOIN a_to_b_key AS a_to_b_key_1 ON b_key_1.id = a_to_b_key_1.bid) AS "
        "anon_1 ON a.id = anon_1.a_to_b_key_1_aid"
    )

    _a_bkeyselect_bkey = (
        "SELECT a.id AS a_id, anon_2.anon_1_aid AS anon_1_aid, "
        "anon_2.anon_1_bid AS anon_1_bid, anon_2.b_key_id AS b_key_id "
        "FROM a JOIN (SELECT anon_1.aid AS anon_1_aid, "
        "anon_1.bid AS anon_1_bid, "
        "b_key.id AS b_key_id "
        "FROM (SELECT a_to_b_key.aid AS aid, a_to_b_key.bid AS bid "
        "FROM a_to_b_key) AS anon_1 "
        "JOIN b_key ON b_key.id = anon_1.bid) AS anon_2 "
        "ON a.id = anon_2.anon_1_aid"
    )

    _a_atobalias_balias_c_w_exists = (
        "SELECT a.id AS a_id, "
        "anon_1.a_to_b_1_a_id AS a_to_b_1_a_id, "
        "anon_1.a_to_b_1_b_id AS a_to_b_1_b_id, "
        "anon_1.b_1_id AS b_1_id, anon_1.b_1_a_id AS b_1_a_id, "
        "EXISTS (SELECT * FROM c WHERE c.b_id = anon_1.b_1_id) AS anon_2 "
        "FROM a LEFT OUTER JOIN (SELECT a_to_b_1.a_id AS a_to_b_1_a_id, "
        "a_to_b_1.b_id AS a_to_b_1_b_id, b_1.id AS b_1_id, "
        "b_1.a_id AS b_1_a_id "
        "FROM a_to_b AS a_to_b_1 "
        "JOIN b AS b_1 ON b_1.id = a_to_b_1.b_id) AS anon_1 "
        "ON a.id = anon_1.a_to_b_1_a_id"
    )

    _a_atobalias_balias = (
        "SELECT a.id AS a_id, anon_1.a_to_b_1_a_id AS a_to_b_1_a_id, "
        "anon_1.a_to_b_1_b_id AS a_to_b_1_b_id, anon_1.b_1_id AS b_1_id, "
        "anon_1.b_1_a_id AS b_1_a_id FROM a LEFT OUTER JOIN "
        "(SELECT a_to_b_1.a_id AS a_to_b_1_a_id, "
        "a_to_b_1.b_id AS a_to_b_1_b_id, "
        "b_1.id AS b_1_id, b_1.a_id AS b_1_a_id FROM a_to_b AS a_to_b_1 "
        "JOIN b AS b_1 ON b_1.id = a_to_b_1.b_id) AS anon_1 "
        "ON a.id = anon_1.a_to_b_1_a_id"
    )

    _b_ab1_union_c_ab2 = (
        "SELECT b_id AS b_id, b_a_id AS b_a_id, a_id AS a_id, b1_id AS b1_id, "
        "b1_a_id AS b1_a_id FROM "
        "(SELECT b.id AS b_id, b.a_id AS b_a_id, anon_1.a_id AS a_id, "
        "anon_1.b1_id AS b1_id, anon_1.b1_a_id AS b1_a_id "
        "FROM b JOIN (SELECT a.id AS a_id, b1.id AS b1_id, b1.a_id AS b1_a_id "
        "FROM a JOIN b1 ON a.id = b1.a_id) AS anon_1 ON anon_1.a_id = b.a_id "
        "UNION "
        "SELECT b.id AS b_id, b.a_id AS b_a_id, anon_2.a_id AS a_id, "
        "anon_2.b2_id AS b2_id, anon_2.b2_a_id AS b2_a_id "
        "FROM b JOIN (SELECT a.id AS a_id, b2.id AS b2_id, b2.a_id AS b2_a_id "
        "FROM a JOIN b2 ON a.id = b2.a_id) AS anon_2 ON anon_2.a_id = b.a_id)"
    )

    _b_a_id_double_overlap_annotated = (
        "SELECT anon_1.b_id AS anon_1_b_id, anon_1.b_a_id AS anon_1_b_a_id, "
        "anon_1.id_1 AS anon_1_id_1 "
        "FROM (SELECT b.id AS b_id, b.a_id AS b_a_id, b_a.id AS id_1 "
        "FROM b JOIN b_a ON b.id = b_a.id) AS anon_1"
    )

    _f_b1a_where_in_b2a = (
        "SELECT f.id AS f_id, f.a_id AS f_a_id "
        "FROM f JOIN (SELECT a.id AS a_id, b1.id AS b1_id, b1.a_id AS b1_a_id "
        "FROM a JOIN b1 ON a.id = b1.a_id) AS anon_1 ON anon_1.a_id = f.a_id "
        "WHERE anon_1.b1_id IN (SELECT b2.id "
        "FROM a JOIN b2 ON a.id = b2.a_id)"
    )

    _anon_scalar_subqueries = (
        "SELECT (SELECT 1) AS anon_1, (SELECT 2) AS anon_2"
    )


class JoinPlainTest(_JoinRewriteTestBase, fixtures.TestBase):

    """test rendering of each join with normal nesting."""

    @util.classproperty
    def __dialect__(cls):
        dialect = default.DefaultDialect()
        return dialect

    _a_bkeyselect_bkey = (
        "SELECT a.id AS a_id, b_key.id AS b_key_id FROM a JOIN "
        "((SELECT a_to_b_key.aid AS aid, a_to_b_key.bid AS bid "
        "FROM a_to_b_key) AS anon_1 JOIN b_key ON b_key.id = anon_1.bid) "
        "ON a.id = anon_1.aid"
    )
    _a__b_dc = (
        "SELECT a.id AS a_id, b.id AS b_id, "
        "b.a_id AS b_a_id, c.id AS c_id, "
        "c.b_id AS c_b_id, d.id AS d_id, "
        "d.c_id AS d_c_id "
        "FROM a JOIN (b JOIN (c JOIN d ON c.id = d.c_id) "
        "ON b.id = c.b_id) ON a.id = b.a_id "
        "WHERE b.id = :id_1 AND c.id = :id_2 AND "
        "d.id = :id_3 "
        "ORDER BY a.id, b.id, c.id, d.id"
    )

    _a_bc = (
        "SELECT a.id AS a_id, b.id AS b_id, "
        "b.a_id AS b_a_id, c.id AS c_id, "
        "c.b_id AS c_b_id FROM a JOIN "
        "(b JOIN c ON b.id = c.b_id) "
        "ON a.id = b.a_id "
        "WHERE b.id = :id_1 AND c.id = :id_2 "
        "ORDER BY a.id, b.id, c.id"
    )

    _a_bc_comma_a1_selbc = (
        "SELECT a.id AS a_id, a_1.id AS a_1_id, b.id AS b_id, "
        "b.a_id AS b_a_id, c.id AS c_id, "
        "c.b_id AS c_b_id, anon_1.b_id AS anon_1_b_id, "
        "anon_1.b_a_id AS anon_1_b_a_id, anon_1.c_id AS anon_1_c_id, "
        "anon_1.c_b_id AS anon_1_c_b_id FROM a "
        "JOIN (b JOIN c ON b.id = c.b_id) "
        "ON a.id = b.a_id, "
        "a AS a_1 JOIN "
        "(SELECT b.id AS b_id, b.a_id AS b_a_id, "
        "c.id AS c_id, c.b_id AS c_b_id "
        "FROM b JOIN c ON b.id = c.b_id) AS anon_1 "
        "ON a_1.id = anon_1.b_a_id ORDER BY anon_1.b_id"
    )

    _a_bkeyassoc = (
        "SELECT a.id AS a_id, b_key.id AS b_key_id "
        "FROM a JOIN "
        "(b_key JOIN a_to_b_key ON b_key.id = a_to_b_key.bid) "
        "ON a.id = a_to_b_key.aid"
    )

    _a_bkeyassoc_aliased = (
        "SELECT a.id AS a_id, b_key_1.id AS b_key_1_id FROM a "
        "JOIN (b_key AS b_key_1 JOIN a_to_b_key AS a_to_b_key_1 "
        "ON b_key_1.id = a_to_b_key_1.bid) ON a.id = a_to_b_key_1.aid"
    )

    _a_atobalias_balias_c_w_exists = (
        "SELECT a.id AS a_id, a_to_b_1.a_id AS a_to_b_1_a_id, "
        "a_to_b_1.b_id AS a_to_b_1_b_id, b_1.id AS b_1_id, "
        "b_1.a_id AS b_1_a_id, "
        "EXISTS (SELECT * FROM c WHERE c.b_id = b_1.id) AS anon_1 "
        "FROM a LEFT OUTER JOIN "
        "(a_to_b AS a_to_b_1 JOIN b AS b_1 ON b_1.id = a_to_b_1.b_id) "
        "ON a.id = a_to_b_1.a_id"
    )

    _a_atobalias_balias = (
        "SELECT a.id AS a_id, a_to_b_1.a_id AS a_to_b_1_a_id, "
        "a_to_b_1.b_id AS a_to_b_1_b_id, b_1.id AS b_1_id, "
        "b_1.a_id AS b_1_a_id "
        "FROM a LEFT OUTER JOIN (a_to_b AS a_to_b_1 "
        "JOIN b AS b_1 ON b_1.id = a_to_b_1.b_id) ON a.id = a_to_b_1.a_id"
    )

    _b_ab1_union_c_ab2 = (
        "SELECT b_id AS b_id, b_a_id AS b_a_id, a_id AS a_id, b1_id AS b1_id, "
        "b1_a_id AS b1_a_id FROM "
        "(SELECT b.id AS b_id, b.a_id AS b_a_id, a.id AS a_id, "
        "b1.id AS b1_id, "
        "b1.a_id AS b1_a_id FROM b "
        "JOIN (a JOIN b1 ON a.id = b1.a_id) ON a.id = b.a_id "
        "UNION "
        "SELECT b.id AS b_id, b.a_id AS b_a_id, a.id AS a_id, b2.id AS b2_id, "
        "b2.a_id AS b2_a_id FROM b "
        "JOIN (a JOIN b2 ON a.id = b2.a_id) ON a.id = b.a_id)"
    )

    _b_a_id_double_overlap_annotated = (
        "SELECT anon_1.b_id AS anon_1_b_id, anon_1.b_a_id AS anon_1_b_a_id, "
        "anon_1.id_1 AS anon_1_id_1 FROM "
        "(SELECT b.id AS b_id, b.a_id AS b_a_id, b_a.id AS id_1 "
        "FROM b JOIN b_a ON b.id = b_a.id) AS anon_1"
    )

    _f_b1a_where_in_b2a = (
        "SELECT f.id AS f_id, f.a_id AS f_a_id "
        "FROM f JOIN (a JOIN b1 ON a.id = b1.a_id) ON a.id = f.a_id "
        "WHERE b1.id IN (SELECT b2.id "
        "FROM a JOIN b2 ON a.id = b2.a_id)"
    )

    _anon_scalar_subqueries = (
        "SELECT (SELECT 1) AS anon_1, (SELECT 2) AS anon_2"
    )


class JoinNoUseLabelsTest(_JoinRewriteTestBase, fixtures.TestBase):
    @util.classproperty
    def __dialect__(cls):
        dialect = default.DefaultDialect()
        dialect.supports_right_nested_joins = False
        return dialect

    def _test(self, s, assert_):
        s.use_labels = False
        self.assert_compile(s, assert_)

    _a_bkeyselect_bkey = (
        "SELECT a.id, b_key.id FROM a JOIN ((SELECT a_to_b_key.aid AS aid, "
        "a_to_b_key.bid AS bid FROM a_to_b_key) AS anon_1 "
        "JOIN b_key ON b_key.id = anon_1.bid) ON a.id = anon_1.aid"
    )

    _a__b_dc = (
        "SELECT a.id, b.id, "
        "b.a_id, c.id, "
        "c.b_id, d.id, "
        "d.c_id "
        "FROM a JOIN (b JOIN (c JOIN d ON c.id = d.c_id) "
        "ON b.id = c.b_id) ON a.id = b.a_id "
        "WHERE b.id = :id_1 AND c.id = :id_2 AND "
        "d.id = :id_3 "
        "ORDER BY a.id, b.id, c.id, d.id"
    )

    _a_bc = (
        "SELECT a.id, b.id, "
        "b.a_id, c.id, "
        "c.b_id FROM a JOIN "
        "(b JOIN c ON b.id = c.b_id) "
        "ON a.id = b.a_id "
        "WHERE b.id = :id_1 AND c.id = :id_2 "
        "ORDER BY a.id, b.id, c.id"
    )

    _a_bc_comma_a1_selbc = (
        "SELECT a.id, a_1.id, b.id, "
        "b.a_id, c.id, "
        "c.b_id, anon_1.b_id, "
        "anon_1.b_a_id, anon_1.c_id, "
        "anon_1.c_b_id FROM a "
        "JOIN (b JOIN c ON b.id = c.b_id) "
        "ON a.id = b.a_id, "
        "a AS a_1 JOIN "
        "(SELECT b.id AS b_id, b.a_id AS b_a_id, "
        "c.id AS c_id, c.b_id AS c_b_id "
        "FROM b JOIN c ON b.id = c.b_id) AS anon_1 "
        "ON a_1.id = anon_1.b_a_id ORDER BY anon_1.b_id"
    )

    _a_bkeyassoc = (
        "SELECT a.id, b_key.id FROM a JOIN (b_key JOIN a_to_b_key "
        "ON b_key.id = a_to_b_key.bid) ON a.id = a_to_b_key.aid"
    )

    _a_bkeyassoc_aliased = (
        "SELECT a.id, b_key_1.id FROM a JOIN (b_key AS b_key_1 "
        "JOIN a_to_b_key AS a_to_b_key_1 ON b_key_1.id = a_to_b_key_1.bid) "
        "ON a.id = a_to_b_key_1.aid"
    )

    _a_atobalias_balias_c_w_exists = (
        "SELECT a.id, a_to_b_1.a_id, a_to_b_1.b_id, b_1.id, b_1.a_id, "
        "EXISTS (SELECT * FROM c WHERE c.b_id = b_1.id) AS anon_1 "
        "FROM a LEFT OUTER JOIN "
        "(a_to_b AS a_to_b_1 JOIN b AS b_1 ON b_1.id = a_to_b_1.b_id) "
        "ON a.id = a_to_b_1.a_id"
    )

    _a_atobalias_balias = (
        "SELECT a.id, a_to_b_1.a_id, a_to_b_1.b_id, b_1.id, b_1.a_id "
        "FROM a LEFT OUTER JOIN (a_to_b AS a_to_b_1 "
        "JOIN b AS b_1 ON b_1.id = a_to_b_1.b_id) ON a.id = a_to_b_1.a_id"
    )

    _b_ab1_union_c_ab2 = (
        "SELECT b_id, b_a_id, a_id, b1_id, b1_a_id "
        "FROM (SELECT b.id AS b_id, b.a_id AS b_a_id, a.id AS a_id, "
        "b1.id AS b1_id, b1.a_id AS b1_a_id "
        "FROM b JOIN (a JOIN b1 ON a.id = b1.a_id) ON a.id = b.a_id "
        "UNION "
        "SELECT b.id AS b_id, b.a_id AS b_a_id, a.id AS a_id, b2.id AS b2_id, "
        "b2.a_id AS b2_a_id "
        "FROM b JOIN (a JOIN b2 ON a.id = b2.a_id) ON a.id = b.a_id)"
    )

    _b_a_id_double_overlap_annotated = (
        "SELECT anon_1.b_id, anon_1.b_a_id, anon_1.id_1 FROM "
        "(SELECT b.id AS b_id, b.a_id AS b_a_id, b_a.id AS id_1 "
        "FROM b JOIN b_a ON b.id = b_a.id) AS anon_1"
    )

    _f_b1a_where_in_b2a = (
        "SELECT f.id, f.a_id "
        "FROM f JOIN (a JOIN b1 ON a.id = b1.a_id) ON a.id = f.a_id "
        "WHERE b1.id IN (SELECT b2.id "
        "FROM a JOIN b2 ON a.id = b2.a_id)"
    )

    _anon_scalar_subqueries = (
        "SELECT (SELECT 1) AS anon_1, (SELECT 2) AS anon_2"
    )


class JoinExecTest(_JoinRewriteTestBase, fixtures.TestBase):

    """invoke the SQL on the current backend to ensure compatibility"""

    __backend__ = True

    _a_bc = (
        _a_bc_comma_a1_selbc
    ) = (
        _a__b_dc
    ) = (
        _a_bkeyassoc
    ) = (
        _a_bkeyassoc_aliased
    ) = (
        _a_atobalias_balias_c_w_exists
    ) = (
        _a_atobalias_balias
    ) = (
        _b_ab1_union_c_ab2
    ) = (
        _b_a_id_double_overlap_annotated
    ) = _f_b1a_where_in_b2a = _anon_scalar_subqueries = None

    @classmethod
    def setup_class(cls):
        m.create_all(testing.db)

    @classmethod
    def teardown_class(cls):
        m.drop_all(testing.db)

    def _test(self, selectable, assert_):
        result = testing.db.execute(selectable)
        result.close()
        for col in selectable.inner_columns:
            assert col in result._metadata._keymap

    @testing.skip_if("oracle", "oracle's cranky")
    @testing.skip_if(
        "mssql", "can't query EXISTS in the columns " "clause w/o subquery"
    )
    def test_a_atobalias_balias_c_w_exists(self):
        super(JoinExecTest, self).test_a_atobalias_balias_c_w_exists()

    @testing.only_on(
        "sqlite",
        "non-standard aliasing rules used at the moment, "
        "possibly fix this or add another test that uses "
        "cross-compatible aliasing",
    )
    def test_b_ab1_union_b_ab2(self):
        super(JoinExecTest, self).test_b_ab1_union_b_ab2()


class DialectFlagTest(fixtures.TestBase, AssertsCompiledSQL):
    def test_dialect_flag(self):
        d1 = default.DefaultDialect(supports_right_nested_joins=True)
        d2 = default.DefaultDialect(supports_right_nested_joins=False)

        j1 = b.join(c)
        j2 = a.join(j1)

        s = select([a, b, c], use_labels=True).select_from(j2)

        self.assert_compile(
            s,
            "SELECT a.id AS a_id, b.id AS b_id, b.a_id AS b_a_id, "
            "c.id AS c_id, "
            "c.b_id AS c_b_id FROM a JOIN (b JOIN c ON b.id = c.b_id) "
            "ON a.id = b.a_id",
            dialect=d1,
        )
        self.assert_compile(
            s,
            "SELECT a.id AS a_id, anon_1.b_id AS b_id, "
            "anon_1.b_a_id AS b_a_id, "
            "anon_1.c_id AS c_id, anon_1.c_b_id AS c_b_id "
            "FROM a JOIN (SELECT b.id AS b_id, b.a_id AS b_a_id, "
            "c.id AS c_id, "
            "c.b_id AS c_b_id FROM b JOIN c ON b.id = c.b_id) AS anon_1 "
            "ON a.id = anon_1.b_a_id",
            dialect=d2,
        )