def test_distinct(self):
        query = Query().from_table(table=Account).distinct()

        query_str = query.get_sql()
        expected_query = "SELECT DISTINCT tests_account.* FROM tests_account"
        self.assertEqual(query_str, expected_query, get_comparison_str(query_str, expected_query))

        query.distinct(use_distinct=False)

        query_str = query.get_sql()
        expected_query = "SELECT tests_account.* FROM tests_account"
        self.assertEqual(query_str, expected_query, get_comparison_str(query_str, expected_query))

    def test_inner(self):
        inner_query = Query().from_table(Account)
        query = Query().from_table(inner_query)

        query_str = query.get_sql()
        expected_query = "WITH T0 AS (SELECT tests_account.* FROM tests_account) SELECT T0.* FROM T0"
        self.assertEqual(query_str, expected_query, get_comparison_str(query_str, expected_query))

        inner_query = Query().from_table(Account)

        query = Query().with_query(inner_query, "s3").from_table("s3")
        query_str = query.get_sql()
        expected_query = "WITH s3 AS (SELECT tests_account.* FROM tests_account) SELECT s3.* FROM s3"
        self.assertEqual(query_str, expected_query, get_comparison_str(query_str, expected_query))

    def test_num_stddev(self):
        query = Query().from_table(
            table=Order,
            fields=[
                '*',
                NumStdDevField(
                    'margin',
                    over=QueryWindow()
                )
            ]
        ).order_by(
            '-margin_num_stddev'
        )

        query_str = query.get_sql()
        expected_query = (
            'SELECT tests_order.*, '
            '(CASE WHEN (STDDEV(tests_order.margin) OVER ()) <> 0 '
            'THEN ((tests_order.margin - ('
            'AVG(tests_order.margin) OVER ())) / (STDDEV(tests_order.margin) OVER ())) '
            'ELSE 0 '
            'END) '
            'AS "margin_num_stddev" '
            'FROM tests_order '
            'ORDER BY margin_num_stddev '
            'DESC'
        )
        self.assertEqual(query_str, expected_query, get_comparison_str(query_str, expected_query))

    def test_join_model_fields(self):
        query = Query().from_table(
            table=Account,
            fields=[
                'one',
                'two',
            ]
        ).join(
            Order,
            fields=[{
                'three': 'one'
            }, {
                'four': 'two'
            }]
        )

        query_str = query.get_sql()
        expected_query = (
            'SELECT tests_account.one, '
            'tests_account.two, '
            'tests_order.one AS "three", '
            'tests_order.two AS "four" '
            'FROM tests_account '
            'JOIN tests_order ON tests_order.account_id = tests_account.id'
        )
        self.assertEqual(query_str, expected_query, get_comparison_str(query_str, expected_query))

    def test_inner_alias(self):
        inner_query = Query().from_table(Account)
        query = Query().from_table({"Q0": inner_query})

        query_str = query.get_sql()
        expected_query = "WITH Q0 AS (SELECT tests_account.* FROM tests_account) SELECT Q0.* FROM Q0"
        self.assertEqual(query_str, expected_query, get_comparison_str(query_str, expected_query))

 def test_limit(self):
     query = Query().from_table(
         table='test_table'
     ).limit(10)
     query_str = query.get_sql()
     expected_query = 'SELECT test_table.* FROM test_table LIMIT 10'
     self.assertEqual(query_str, expected_query, get_comparison_str(query_str, expected_query))

    def test_where_complex(self):
        query = Query().from_table(
            table='test_table'
        ).where(Q(
            one=1
        )).where(Q(
            two__gt=2
        )).where(~Q(
            three__gte=3
        )).where(~Q(
            four__lt=4
        ), OR).where(Q(
            five__lte=5
        ), OR).where(Q(
            six__contains='six'
        )).where(~Q(
            seven__startswith='seven'
        )).where(Q(
            eight=8
        ) & Q(
            nine=9
        ) | Q(
            ten=10
        ) | ~Q(
            eleven=11
        ))

        query_str = query.get_sql()
        expected_query = ''.join([
            'SELECT test_table.* FROM test_table WHERE ',
            '(((one = %(A0)s AND two > %(A1)s AND (NOT(three >= %(A2)s))) OR (NOT(four < %(A3)s)) ',
            'OR five <= %(A4)s) AND (six LIKE %(A5)s) AND (NOT(seven LIKE %(A6)s)) AND ',
            '((eight = %(A7)s AND nine = %(A8)s) OR ten = %(A9)s OR (NOT(eleven = %(A10)s))))'
        ])
        self.assertEqual(query_str, expected_query, get_comparison_str(query_str, expected_query))

 def test_select_all_from_model(self):
     query = Query().from_table(
         table=Account
     )
     query_str = query.get_sql()
     expected_query = 'SELECT tests_account.* FROM tests_account'
     self.assertEqual(query_str, expected_query, get_comparison_str(query_str, expected_query))

 def test_select_fields_two_tables_alias(self):
     query = Query().from_table(
         table={
             'table_one': Account
         },
         fields=[{
             'f1': 'field_one'
         }, {
             'f2': 'field_two'
         }]
     ).from_table(
         table={
             'table_two': 'second_table'
         },
         fields=[{
             'f3': 'field_three'
         }, {
             'f4': 'field_four'
         }]
     )
     query_str = query.get_sql()
     expected_query = (
         'SELECT table_one.field_one AS "f1", '
         'table_one.field_two AS "f2", '
         'table_two.field_three AS "f3", '
         'table_two.field_four AS "f4" '
         'FROM tests_account AS table_one, '
         'second_table AS table_two'
     )
     self.assertEqual(query_str, expected_query, get_comparison_str(query_str, expected_query))

    def test_inner_outer_args_many(self):
        inner_query = Query().from_table(
            Account
        ).where(
            Q(id__gt=1) & Q(id__lt=10)
        )

        inner_query2 = Query().from_table(
            Account
        ).where(
            Q(id__gt=1) & Q(id__lt=10)
        )

        query = Query().from_table(
            inner_query
        ).from_table(
            inner_query2
        ).where(
            ~Q(id=0)
        )

        query_str = query.get_sql()
        expected_query = (
            'WITH T1 AS '
            '(SELECT tests_account.* FROM tests_account WHERE (id > %(T1A0)s AND id < %(T1A1)s)), '
            'T0 AS ('
            'SELECT tests_account.* '
            'FROM tests_account '
            'WHERE (id > %(T0A0)s AND id < %(T0A1)s)) '
            'SELECT T0.*, T1.* '
            'FROM T0, T1 '
            'WHERE ((NOT(id = %(A0)s)))'
        )
        self.assertEqual(query_str, expected_query, get_comparison_str(query_str, expected_query))

 def test_select_all_from_string(self):
     query = Query().from_table(
         table='test_table'
     )
     query_str = query.get_sql()
     expected_query = 'SELECT test_table.* FROM test_table'
     self.assertEqual(query_str, expected_query, get_comparison_str(query_str, expected_query))

 def test_rank(self):
     query = Query().from_table(
         table=Order,
         fields=[
             'id',
             RankField(
                 over=QueryWindow().partition_by(
                     'account_id'
                 ).order_by(
                     'id'
                 )
             )
         ]
     ).order_by(
         '-rank'
     )
     query_str = query.get_sql()
     expected_query = (
         'SELECT tests_order.id, '
         'RANK() OVER (PARTITION BY account_id ORDER BY id ASC) AS "rank" '
         'FROM tests_order '
         'ORDER BY rank '
         'DESC'
     )
     self.assertEqual(query_str, expected_query, get_comparison_str(query_str, expected_query))

    def test_join_model_fields_extract(self):
        query = Query().from_table(
            table=Account,
            fields=[
                '*',
            ]
        ).join(
            Order,
            fields=[
                '*'
            ]
        )

        query_str = query.get_sql()
        expected_query = (
            'SELECT tests_account.*, '
            'tests_order.id, '
            'tests_order.account_id, '
            'tests_order.revenue, '
            'tests_order.margin, '
            'tests_order.margin_percent, '
            'tests_order.time '
            'FROM tests_account '
            'JOIN tests_order ON tests_order.account_id = tests_account.id'
        )
        self.assertEqual(query_str, expected_query, get_comparison_str(query_str, expected_query))

    def test_distinct_on_many_fields(self):
        query = Query().from_table(
            table=Account
        ).distinct_on('field1', 'field2', 'field3')

        query_str = query.get_sql()
        expected_query = 'SELECT DISTINCT ON (field1, field2, field3) tests_account.* FROM tests_account'
        self.assertEqual(query_str, expected_query, get_comparison_str(query_str, expected_query))

 def test_query_window_partition_order(self):
     query_window = QueryWindow().partition_by(
         'field_one'
     ).order_by(
         'field_one'
     )
     query_str = query_window.get_sql()
     expected_query = 'OVER (PARTITION BY field_one ORDER BY field_one ASC)'
     self.assertEqual(query_str, expected_query, get_comparison_str(query_str, expected_query))

 def test_order_by_single_asc(self):
     query = Query().from_table(
         table='test_table'
     ).order_by(
         'field_one'
     )
     query_str = query.get_sql()
     expected_query = 'SELECT test_table.* FROM test_table ORDER BY field_one ASC'
     self.assertEqual(query_str, expected_query, get_comparison_str(query_str, expected_query))

 def test_group_by_table_id(self):
     query = (
         Query()
         .from_table(table="test_table", fields=[{"num": CountField("id")}])
         .group_by(field="id", table="test_table")
     )
     query_str = query.get_sql()
     expected_query = "SELECT COUNT(test_table.id) AS num FROM test_table GROUP BY test_table.id"
     self.assertEqual(query_str, expected_query, get_comparison_str(query_str, expected_query))

 def test_select_all_from_string_alias(self):
     query = Query().from_table(
         table={
             'table_alias': 'test_table'
         }
     )
     query_str = query.get_sql()
     expected_query = 'SELECT table_alias.* FROM test_table AS table_alias'
     self.assertEqual(query_str, expected_query, get_comparison_str(query_str, expected_query))

 def test_variance(self):
     query = Query().from_table(
         table=Order,
         fields=[
             VarianceField('margin')
         ]
     )
     query_str = query.get_sql()
     expected_query = 'SELECT VARIANCE(tests_order.margin) AS margin_variance FROM tests_order'
     self.assertEqual(query_str, expected_query, get_comparison_str(query_str, expected_query))

 def test_avg(self):
     query = Query().from_table(
         table=Order,
         fields=[
             AvgField('margin')
         ]
     )
     query_str = query.get_sql()
     expected_query = 'SELECT AVG(tests_order.margin) AS margin_avg FROM tests_order'
     self.assertEqual(query_str, expected_query, get_comparison_str(query_str, expected_query))