<commit_before>import unittest

from pypika import (
    Field,
    Interval,
    JoinException,
    JoinType,
    MySQLQuery,
    Query,
    Table,
    Tables,
    UnionException,
    functions as fn,
)

__author__ = "<NAME>"
__email__ = "<EMAIL>"


class JoinTypeTests(unittest.TestCase):
    table0, table1, hij = Tables('abc', 'efg', 'hij')

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

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

    def test_right_join(self):
        q = Query.from_(self.table0).join(self.table1, how=JoinType.right).on(
            self.table0.foo == self.table1.bar).select('*')

        self.assertEqual('SELECT * FROM "abc" RIGHT JOIN "efg" ON "abc"."foo"="efg"."bar"', 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_outer_join(self):
        q = Query.from_(self.table0).join(self.table1, how=JoinType.outer).on(
              self.table0.foo == self.table1.bar).select('*')

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

    def test_left_outer_join(self):
        q = Query.from_(self.table0).join(self.table1, how=JoinType.left_outer).on(
              self.table0.foo == self.table1.bar).select('*')

        self.assertEqual('SELECT * FROM "abc" LEFT OUTER JOIN "efg" ON "abc"."foo"="efg"."bar"', str(q))

    def test_right_outer_join(self):
        q = Query.from_(self.table0).join(self.table1, how=JoinType.right_outer).on(
              self.table0.foo == self.table1.bar).select('*')

        self.assertEqual('SELECT * FROM "abc" RIGHT OUTER JOIN "efg" ON "abc"."foo"="efg"."bar"', str(q))

    def test_full_outer_join(self):
        q = Query.from_(self.table0).join(self.table1, how=JoinType.full_outer).on(
              self.table0.foo == self.table1.bar).select('*')

        self.assertEqual('SELECT * FROM "abc" FULL OUTER JOIN "efg" ON "abc"."foo"="efg"."bar"', str(q))

    def test_join_on_field_single(self):
        query = Query.from_(self.table0).join(self.table1).on_field("foo").select('*')
        self.assertEqual('SELECT * FROM "abc" JOIN "efg" ON "abc"."foo"="efg"."foo"', str(query))

    def test_join_on_field_multi(self):
        query = Query.from_(self.table0).join(self.table1).on_field("foo", "bar").select('*')
        self.assertEqual('SELECT * FROM "abc" JOIN "efg" ON "abc"."foo"="efg"."foo" '
                         'AND "abc"."bar"="efg"."bar"', str(query))

    def test_join_on_field_multi_with_extra_join(self):
        query = Query.from_(self.table0)\
            .join(self.hij).on_field("buzz")\
            .join(self.table1).on_field("foo", "bar").select('*')

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

    def test_join_using_string_field_name(self):
        query = Query.from_(self.table0).join(self.table1).using('id').select('*')

        self.assertEqual('SELECT * FROM "abc" JOIN "efg" USING ("id")', str(query))

    def test_join_using_multiple_fields(self):
        query = Query.from_(self.table0).join(self.table1).using('foo', 'bar').select('*')

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

    def test_join_arithmetic_field(self):
        q = Query.from_(self.table0).join(self.table1).on(
            self.table0.dt == (self.table1.dt - Interval(weeks=1))).select('*')

        self.assertEqual('SELECT * FROM "abc" '
                         'JOIN "efg" ON "abc"."dt"="efg"."dt"-INTERVAL \'1 WEEK\'', str(q))

    def test_join_with_arithmetic_function_in_select(self):
        q = Query.from_(
            self.table0,
        ).join(self.table1).on(
            self.table0.dt == (self.table1.dt - Interval(weeks=1))
        ).select(self.table0.fiz - self.table0.buz, self.table1.star)

        self.assertEqual('SELECT "abc"."fiz"-"abc"."buz","efg".* FROM "abc" '
                         'JOIN "efg" ON "abc"."dt"="efg"."dt"-INTERVAL \'1 WEEK\'', str(q))

    def test_join_on_complex_criteria(self):
        q = Query.from_(self.table0).join(self.table1, how=JoinType.right).on(
            (self.table0.foo == self.table1.fiz) & (self.table0.bar == self.table1.buz)
        ).select('*')

        self.assertEqual('SELECT * FROM "abc" '
                         'RIGHT JOIN "efg" ON "abc"."foo"="efg"."fiz" AND "abc"."bar"="efg"."buz"', str(q))

    def test_use_different_table_objects_for_same_table(self):
        table = Table("t")<commit_msg>Added some more unit tests.<commit_after>import unittest

from pypika import (
    Field,
    Interval,
    JoinException,
    JoinType,
    MySQLQuery,
    Query,
    Table,
    Tables,
    UnionException,
    functions as fn,
)

__author__ = "<NAME>"
__email__ = "<EMAIL>"


class JoinTypeTests(unittest.TestCase):
    table0, table1, hij = Tables('abc', 'efg', 'hij')

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

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

    def test_right_join(self):
        q = Query.from_(self.table0).join(self.table1, how=JoinType.right).on(
            self.table0.foo == self.table1.bar).select('*')

        self.assertEqual('SELECT * FROM "abc" RIGHT JOIN "efg" ON "abc"."foo"="efg"."bar"', 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_outer_join(self):
        q = Query.from_(self.table0).join(self.table1, how=JoinType.outer).on(
              self.table0.foo == self.table1.bar).select('*')

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

    def test_left_outer_join(self):
        q = Query.from_(self.table0).join(self.table1, how=JoinType.left_outer).on(
              self.table0.foo == self.table1.bar).select('*')

        self.assertEqual('SELECT * FROM "abc" LEFT OUTER JOIN "efg" ON "abc"."foo"="efg"."bar"', str(q))

    def test_right_outer_join(self):
        q = Query.from_(self.table0).join(self.table1, how=JoinType.right_outer).on(
              self.table0.foo == self.table1.bar).select('*')

        self.assertEqual('SELECT * FROM "abc" RIGHT OUTER JOIN "efg" ON "abc"."foo"="efg"."bar"', str(q))

    def test_full_outer_join(self):
        q = Query.from_(self.table0).join(self.table1, how=JoinType.full_outer).on(
              self.table0.foo == self.table1.bar).select('*')

        self.assertEqual('SELECT * FROM "abc" FULL OUTER JOIN "efg" ON "abc"."foo"="efg"."bar"', str(q))

    def test_join_on_field_single(self):
        query = Query.from_(self.table0).join(self.table1).on_field("foo").select('*')
        self.assertEqual('SELECT * FROM "abc" JOIN "efg" ON "abc"."foo"="efg"."foo"', str(query))

    def test_join_on_field_multi(self):
        query = Query.from_(self.table0).join(self.table1).on_field("foo", "bar").select('*')
        self.assertEqual('SELECT * FROM "abc" JOIN "efg" ON "abc"."foo"="efg"."foo" '
                         'AND "abc"."bar"="efg"."bar"', str(query))

    def test_join_on_field_multi_with_extra_join(self):
        query = Query.from_(self.table0)\
            .join(self.hij).on_field("buzz")\
            .join(self.table1).on_field("foo", "bar").select('*')

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

    def test_join_using_string_field_name(self):
        query = Query.from_(self.table0).join(self.table1).using('id').select('*')

        self.assertEqual('SELECT * FROM "abc" JOIN "efg" USING ("id")', str(query))

    def test_join_using_multiple_fields(self):
        query = Query.from_(self.table0).join(self.table1).using('foo', 'bar').select('*')

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

    def test_join_using_without_fields_raises_exception(self):
        with self.assertRaises(JoinException):
            query = Query.from_(self.table0).join(self.table1).using()

    def test_join_on_field_without_fields_raises_exception(self):
        with self.assertRaises(JoinException):
            query = Query.from_(self.table0).join(self.table1).on_field()

    def test_join_arithmetic_field(self):
        q = Query.from_(self.table0).join(self.table1).on(
            self.table0.dt == (self.table1.dt - Interval(weeks=1))).select('*')

        self.assertEqual('SELECT * FROM "abc" '
                         'JOIN "efg" ON "abc"."dt"="efg"."dt"-INTERVAL \'1 WEEK\'', str(q))

    def test_join_with_arithmetic_function_in_select(self):
        q = Query.from_(
            self.table0,
        ).join(self.table1).on(
            self.table0.dt == (self.table1.dt - Interval(weeks=1))
        ).select(self.table0.fiz - self.table0.buz, self.table1.star)

        self.assertEqual('SELECT "abc"."fiz"-"abc"."buz","efg".* FROM "abc" '
                         'JOIN "efg" ON "abc"."dt"="efg"."dt"-INTERVAL \'1 WEEK\'', str(q))

    def test_join_on_complex_criteria(self):
        q = Query.from_(self.table0).join(self.table1, how=JoinType.right).on(
            (self.table0.foo == self.table1.fiz) & (self.table0.bar == self.table1.buz)
        ).select('*')

        self.assertEqual('SELECT * FROM "abc" '
                         'RIGHT JOIN "efg" ON "abc"."foo"="efg"."fiz" AND "abc"."bar"="efg"."buz"', str(q))

    def test_use_different_table_objects_for_same_table(self):
        table = Table("t")
{'fraction_non_alphanumeric': 0.186826458565589, 'fraction_numerical': 0.01244890375325158, 'mean_word_length': 2.987037037037037, 'pattern_counts': {'":': 0, '<': 7, '<?xml version=': 0, '>': 7, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '1751003', 'n_tokens_mistral': 3917, 'n_tokens_neox': 3704, 'n_words': 601}