def test_union_as_subquery(self):
        abc, efg = Tables('abc', 'efg')
        hij = Query.from_(abc).select(abc.t).union(Query.from_(efg).select(efg.t))
        q = Query.from_(hij).select(fn.Avg(hij.t))

        self.assertEqual('SELECT AVG("sq0"."t") FROM ((SELECT "t" FROM "abc") UNION (SELECT "t" FROM "efg")) "sq0"',
                         str(q))

    def test_select_from_nested_query_with_join(self):
        subquery1 = Query.from_(self.table_abc).select(
            self.table_abc.foo,
            fn.Sum(self.table_abc.fizz + self.table_abc.buzz).as_('fizzbuzz'),
        ).groupby(
            self.table_abc.foo
        )

        subquery2 = Query.from_(self.table_efg).select(
            self.table_efg.foo.as_('foo_two'),
            self.table_efg.bar,
        )

        query = Query.from_(subquery1).select(
            subquery1.foo, subquery1.fizzbuzz
        ).join(subquery2).on(subquery1.foo == subquery2.foo_two).select(
            subquery2.foo_two, subquery2.bar
        )

        self.assertEqual('SELECT '
                         '"sq0"."foo","sq0"."fizzbuzz",'
                         '"sq1"."foo_two","sq1"."bar" '
                         'FROM ('
                         'SELECT '
                         '"foo",SUM("fizz"+"buzz") "fizzbuzz" '
                         'FROM "abc" '
                         'GROUP BY "foo"'
                         ') "sq0" JOIN ('
                         'SELECT '
                         '"foo" "foo_two","bar" '
                         'FROM "efg"'
                         ') "sq1" ON "sq0"."foo"="sq1"."foo_two"', str(query))

    def test_where__in(self):
        main, nested = Tables('abc', 'efg')
        q = Query.from_(main).select('*').where(main.foo.isin(
            Query.from_(nested).select(nested.foo).where(nested.bar == 0)
        ))

        self.assertEqual('SELECT * FROM abc WHERE foo IN (SELECT foo FROM efg WHERE bar=0)', str(q))

    def test_select__subquery(self):
        subquery = Query.from_(self.table_abc).select("*")
        q = Query.from_(subquery) \
            .select(subquery.foo, subquery.bar)

        self.assertEqual('SELECT "sq0"."foo","sq0"."bar" '
                         'FROM (SELECT * FROM "abc") "sq0"', str(q))

    def test_inner_join(self):
        query = Query.from_(self.table0).join(self.table1).on(
            self.table0.foo == self.table1.bar).select('*')
        query_explicit = Query.from_(self.table0).join(self.table1, how=JoinType.inner).on(
            self.table0.foo == self.table1.bar).select('*')

        self.assertEqual('SELECT * FROM "abc" JOIN "efg" ON "abc"."foo"="efg"."bar"', str(query))
        self.assertEqual('SELECT * FROM "abc" JOIN "efg" ON "abc"."foo"="efg"."bar"', str(query_explicit))

    def test_left_join(self):
        query2 = Query.from_(self.table0).join(self.table1).on(
            self.table0.foo == self.table1.bar).select('*')
        q2 = Query.from_(self.table0).join(self.table1, how=JoinType.left).on(
            self.table0.foo == self.table1.bar).select('*')

        self.assertEqual('SELECT * FROM "abc" "t0" JOIN "efg" "t1" ON "t0"."foo"="t1"."bar"', str(query2))
        self.assertEqual('SELECT * FROM "abc" "t0" JOIN "efg" "t1" ON "t0"."foo"="t1"."bar"', str(q2))

    def test_cross_join_on_subquery(self):
        table_a, table_b = Tables('a', 'b')
        q_a = Query.from_(table_a).select('*')
        q_b = Query.from_(table_b).select('*').join(q_a).cross().select('*')

        self.assertEqual(
            'SELECT * FROM "b" CROSS JOIN (SELECT * FROM "a") "sq0"',
            str(q_b)
        )

    def test_immutable__tables(self):
        query1 = Query.from_(self.table_abc).select(self.table_abc.foo)
        query2 = Query.from_(self.table_abc).join(self.table_efg).on(self.table_abc.foo == self.table_efg.bar).select(
            self.table_abc.foo,
            self.table_efg.buz)

        self.assertEqual('SELECT "abc"."foo","efg"."buz" FROM "abc" '
                         'JOIN "efg" ON "abc"."foo"="efg"."bar"', str(query2))
        self.assertEqual('SELECT "foo" FROM "abc"', str(query1))

    def test_immutable__tables(self):
        query1 = Query.from_(self.table0).select(self.table0.foo)
        query2 = Query.from_(self.table0).join(self.table1).on(self.table0.foo == self.table1.bar).select(
            self.table0.foo,
            self.table1.buz)

        self.assertEqual('SELECT "t0"."foo","t1"."buz" FROM "abc" "t0" '
                         'JOIN "efg" "t1" ON "t0"."foo"="t1"."bar"', str(query2))
        self.assertEqual('SELECT "foo" FROM "abc"', str(query1))

    def test_join(self):
        subquery = Query.from_('efg').select('fiz', 'buz').where(F('buz') == 0)
        q = Query.from_(self.t).join(subquery).on(
            self.t.bar == subquery.buz
        ).select(self.t.foo, subquery.fiz)

        self.assertEqual('SELECT t0.foo,t1.fiz FROM abc t0 '
                         'JOIN (SELECT fiz,buz FROM efg WHERE buz=0) t1 '
                         'ON t0.bar=t1.buz', str(q))

    def test_where__equality(self):
        subquery = Query.from_('efg').select('fiz').where(F('buz') == 0)
        query = Query.from_(self.table_abc).select(
            self.table_abc.foo,
            self.table_abc.bar
        ).where(self.table_abc.bar == subquery)

        self.assertEqual('SELECT "foo","bar" FROM "abc" '
                         'WHERE "bar"=(SELECT "fiz" FROM "efg" WHERE "buz"=0)', str(query))

    def test_union_with_order_by_with_aliases(self):
        query1 = Query.from_(self.table1).select(self.table1.foo.as_('a')).as_('a')
        query2 = Query.from_(self.table2).select(self.table2.bar.as_('a')).as_('b')

        union_query = (query1 + query2).orderby(query1.field('a'))

        self.assertEqual('(SELECT "foo" "a" FROM "abc") "a" '
                         'UNION ALL '
                         '(SELECT "bar" "a" FROM "efg") "b" '
                         'ORDER BY "a"."a"', union_query)

    def test_join(self):
        subquery = Query.from_('efg').select('fiz', 'buz').where(F('buz') == 0)

        q = Query.from_(self.table_abc).join(subquery).on(
            self.table_abc.bar == subquery.buz
        ).select(self.table_abc.foo, subquery.fiz)

        self.assertEqual('SELECT "abc"."foo","sq0"."fiz" FROM "abc" '
                         'JOIN (SELECT "fiz","buz" FROM "efg" WHERE "buz"=0) "sq0" '
                         'ON "abc"."bar"="sq0"."buz"', str(q))

    def test_union_with_order_by_use_union_query_field(self):
        query1 = Query.from_(self.table1).select(self.table1.foo.as_('a'))
        query2 = Query.from_(self.table2).select(self.table2.bar.as_('a'))

        union_query = (query1 + query2)
        union_query = union_query.orderby(union_query.field('a'))

        self.assertEqual('(SELECT "foo" "a" FROM "abc") '
                         'UNION ALL '
                         '(SELECT "bar" "a" FROM "efg") '
                         'ORDER BY "a"', union_query)

    def test_from_subquery_without_alias(self):
        subquery = Query.from_(self.table_efg) \
            .select(self.table_efg.base_id.as_('x'), self.table_efg.fizz, self.table_efg.buzz)

        test_query = Query.from_(subquery) \
            .select(subquery.x, subquery.fizz, subquery.buzz)

        self.assertEqual('SELECT "sq0"."x","sq0"."fizz","sq0"."buzz" '
                         'FROM ('
                         'SELECT "base_id" "x","fizz","buzz" FROM "efg"'
                         ') "sq0"', str(test_query))

    def test_select__multiple_subqueries(self):
        subquery0 = Query.from_(self.table_abc).select("foo")
        subquery1 = Query.from_(self.table_efg).select("bar")
        q = Query \
            .from_(subquery0) \
            .from_(subquery1) \
            .select(subquery0.foo, subquery1.bar)

        self.assertEqual('SELECT "sq0"."foo","sq1"."bar" '
                         'FROM (SELECT "foo" FROM "abc") "sq0",'
                         '(SELECT "bar" FROM "efg") "sq1"', str(q))

    def test_insert_subquery_wrapped_in_brackets(self):
        purchase_order_item, part = Tables("purchase_order_item", "part")

        q = Query.into(purchase_order_item) \
            .columns(purchase_order_item.id_part, purchase_order_item.id_customer) \
            .insert(Query.from_(part)
                    .select(part.part_id)
                    .where(part.part_number == "FOOBAR"), 12345)

        self.assertEqual('INSERT INTO "purchase_order_item" '
                         '("id_part","id_customer") '
                         'VALUES '
                         '((SELECT "part_id" FROM "part" WHERE "part_number"=\'FOOBAR\'),12345)', str(q))

 def test_no_rollup_after_rollup(self):
     with self.assertRaises(AttributeError):
         Query.from_(self.table).select(
             self.table.foo,
             self.table.fiz,
             fn.Sum(self.table.bar)
         ).rollup(
             self.table.foo,
             vendor='mysql'
         ).rollup(
             self.table.fiz,
             vendor='mysql'
         )

    def test_join_query_without_alias(self):
        subquery = Query.from_(self.table_efg) \
            .select(self.table_efg.base_id.as_('x'), self.table_efg.fizz, self.table_efg.buzz)

        test_query = Query.from_(self.table_abc) \
            .join(subquery) \
            .on(subquery.x == self.table_abc.id) \
            .select(self.table_abc.foo, subquery.fizz, subquery.buzz)

        self.assertEqual('SELECT "abc"."foo","sq0"."fizz","sq0"."buzz" '
                         'FROM "abc" JOIN ('
                         'SELECT "base_id" "x","fizz","buzz" FROM "efg"'
                         ') "sq0" '
                         'ON "sq0"."x"="abc"."id"', str(test_query))

    def test_select__nested_subquery(self):
        subquery0 = Query.from_(self.table_abc).select("*")
        subquery1 = Query.from_(subquery0) \
            .select(subquery0.foo, subquery0.bar)
        subquery2 = Query.from_(subquery1) \
            .select(subquery1.foo)

        q = Query.from_(subquery2) \
            .select(subquery2.foo)

        self.assertEqual('SELECT "sq2"."foo" '
                         'FROM (SELECT "sq1"."foo" '
                         'FROM (SELECT "sq0"."foo","sq0"."bar" '
                         'FROM (SELECT * FROM "abc") "sq0") "sq1") "sq2"', str(q))