def test_partitioning_function_order_by_no_partition_with_frame(): sql = "SELECT row_number() over (order by x desc, z ASC RANGE between UNBOUNDED preceding AND unbounded FOLLOWING) from t;" # noqa expected = """ SELECT ROW_NUMBER() OVER ( ORDER BY x DESC, z ASC RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) FROM t; """ assert format_sql(sql) == expected.strip()
def test_partitioning_function_order_by(): sql = "SELECT *, row_number() over (partition by x order BY x) from t;" expected = """ SELECT *, ROW_NUMBER() OVER ( PARTITION BY x ORDER BY x ) FROM t; """ assert format_sql(sql) == expected.strip()
def test_partitioning_function_multiple_params(): sql = "SELECT row_number() over (partition by x,z, x+z) from t;" expected = """ SELECT ROW_NUMBER() OVER ( PARTITION BY x, z, x + z ) FROM t; """ assert format_sql(sql) == expected.strip()
def test_basic_join(): sql = """ SELECT field FROM table JOIN other_table USING (field) """ expected = """ SELECT field FROM table JOIN other_table USING (field) """ assert format_sql(sql) == expected.strip()
def test_long_function_calls(): sql = """select STRUCT(LENGTH(a_very_very_very_long_field_name_that_takes_quite_some_space) AS len, TO_CODE_POINTS(some_other_very_very_long_field_name) AS cq) a_very_very_long_result_field from t; """ # NOQA expected = """ SELECT STRUCT( LENGTH(a_very_very_very_long_field_name_that_takes_quite_some_space) AS len, TO_CODE_POINTS(some_other_very_very_long_field_name) AS cq ) a_very_very_long_result_field FROM t; """ assert format_sql(sql) == expected.strip()
def test_array_with_subquery(): sql = "select ARRAY(SELECT struct(dimension, IFNULL(sum(metric),0) ) FROM unnest(f_2) group BY dimension ORDER by dimension) from table" # noqa expected = """ SELECT ARRAY( SELECT STRUCT(dimension, IFNULL(SUM(metric), 0)) FROM UNNEST(f_2) GROUP BY dimension ORDER BY dimension ) FROM table """ # NOQA assert format_sql(sql) == expected.strip()
def test_case_multiple_fields(): sql = "select col, case when c = 1 THEN 'test' when c <= 1+3 then 'other' else 'none' end from t;" # noqa expected = """ SELECT col, CASE WHEN c = 1 THEN 'test' WHEN c <= 1 + 3 THEN 'other' ELSE 'none' END FROM t; """ # NOQA assert format_sql(sql) == expected.strip()
def test_case_expr(): sql = ( "select case c when 1 THEN 'test' when 1+3 then 'other' else 'none' end from t;" ) expected = """ SELECT CASE c WHEN 1 THEN 'test' WHEN 1 + 3 THEN 'other' ELSE 'none' END FROM t; """ # NOQA assert format_sql(sql) == expected.strip()
def test_where_in_subquery(): sql = "select * from t where id in (select id from other_t where field <> 0);" expected = """ SELECT * FROM t WHERE id IN ( SELECT id FROM other_t WHERE field <> 0 ); """ assert format_sql(sql) == expected.strip()
def test_partitioning_function_multiple_params_with_frame(): sql = "SELECT row_number() over (partition by x,z, x+z rows BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) from t;" # noqa expected = """ SELECT ROW_NUMBER() OVER ( PARTITION BY x, z, x + z ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) FROM t; """ assert format_sql(sql) == expected.strip()
def test_break_long_where(): sql = "select * from t where this > 0 AND that < 0 AND foo = 4 AND veryveryverylonglonglongname = 'test' OR other_long_long_long_name < 10" # noqa expected = """ SELECT * FROM t WHERE this > 0 AND that < 0 AND foo = 4 AND veryveryverylonglonglongname = 'test' OR other_long_long_long_name < 10 """ assert format_sql(sql) == expected.strip()
def test_partitioning_function_order_by_frame(): sql = "SELECT *, row_number() over (partition by x order BY x rows BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) from t;" # noqa expected = """ SELECT *, ROW_NUMBER() OVER ( PARTITION BY x ORDER BY x ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) FROM t; """ assert format_sql(sql) == expected.strip()
def test_join_on_clause(): sql = """ SELECT field FROM table JOIN (other_table) ON f1 = f2 """ expected = """ SELECT field FROM table JOIN ( other_table ) ON f1 = f2 """ assert format_sql(sql) == expected.strip()
def test_nested_queries_multiple_columns(): sql = "select field, f2 from (select field, f2 from table_stmt);" expected = """ SELECT field, f2 FROM ( SELECT field, f2 FROM table_stmt ); """ assert format_sql(sql) == expected.strip()
def test_join_on_clause_boolean(): sql = """ SELECT field FROM table JOIN (other_table) ON f1 = f2 and f3=f4 """ expected = """ SELECT field FROM table JOIN ( other_table ) ON f1 = f2 AND f3 = f4 """ assert format_sql(sql) == expected.strip()
def test_parenthesis_join(): sql = """ SELECT field FROM table JOIN (other_table) USING (field) """ expected = """ SELECT field FROM table JOIN ( other_table ) USING (field) """ assert format_sql(sql) == expected.strip()
def test_nested_multiple_joins_unnest(): sql = ( "select field from (select x from (select * from t1) a, unnest(t2) as b," "unnest(t3) as c);") expected = """ SELECT field FROM ( SELECT x FROM ( SELECT * FROM t1 ) a, UNNEST(t2) AS b, UNNEST(t3) AS c ); """ assert format_sql(sql) == expected.strip()
def test_two_nested_queries(): sql = "select field from (select field, f2 from (select * from t));" expected = """ SELECT field FROM ( SELECT field, f2 FROM ( SELECT * FROM t ) ); """ assert format_sql(sql) == expected.strip()
def test_partitioning_function_order_by_multiple(): sql = "SELECT row_number() over (partition by x,z, x+z order by x desc, z ASC) from t;" # NOQA expected = """ SELECT ROW_NUMBER() OVER ( PARTITION BY x, z, x + z ORDER BY x DESC, z ASC ) FROM t; """ assert format_sql(sql) == expected.strip()
def test_parenthesis_join_subquery(): sql = """ SELECT field FROM table JOIN (SELECT * from other_table WHERE date > "2020-01-01") USING (field) """ expected = """ SELECT field FROM table JOIN ( SELECT * FROM other_table WHERE date > "2020-01-01" ) USING (field) """ assert format_sql(sql) == expected.strip()
def test_join_long_on_clause(): sql = """ SELECT field FROM table JOIN (other_table) ON the__first__very__long__field__name__to__join = the__second__very__long__field__name__to__join and f1 = f2 """ # noqa expected = """ SELECT field FROM table JOIN ( other_table ) ON the__first__very__long__field__name__to__join = the__second__very__long__field__name__to__join AND f1 = f2 """ # noqa assert format_sql(sql) == expected.strip()
def test_unnest_with_offset_both_aliased(): sql = """ SELECT some_field f0, (SELECT SUM(IF(hh = structure.cq[SAFE_OFFSET(o)], 1, 0)) FROM UNNEST(other_struct.cq) hh WITH offset o) * 100 / LEAST(other_struct.len, structure.len) f1 from table;""" expected = """ SELECT some_field f0, ( SELECT SUM(IF(hh = structure.cq[SAFE_OFFSET(o)], 1, 0)) FROM UNNEST(other_struct.cq) hh WITH OFFSET o ) * 100 / LEAST(other_struct.len, structure.len) f1 FROM table; """ assert format_sql(sql) == expected.strip()
def test_where_boolean_followed_by_group(): sql = """ SELECT * FROM t WHERE col1 = col0 and col2 = col0 and col3 = col0 GROUP BY field, col0 """ expected = """ SELECT * FROM t WHERE col1 = col0 AND col2 = col0 AND col3 = col0 GROUP BY field, col0 """ assert format_sql(sql) == expected.strip()
def test_where_not_in_subquery(): sql = """select field FROM t WHERE id NOT IN (SELECT id FROM other_table WHERE col <> 0)""" expected = """ SELECT field FROM t WHERE id NOT IN ( SELECT id FROM other_table WHERE col <> 0 ) """ assert format_sql(sql) == expected.strip()
def test_break_long_parenthesis_where_nested_operations(): sql = "select * from t where (this > 0 AND that < 0 AND foo = 4 AND (veryveryveryveryveryveryverylonglonglongname = 'test' OR other_long_long_long_long_name < 10) OR x < 0)" # noqa expected = """ SELECT * FROM t WHERE ( this > 0 AND that < 0 AND foo = 4 AND ( veryveryveryveryveryveryverylonglonglongname = 'test' OR other_long_long_long_long_name < 10 ) OR x < 0 ) """ assert format_sql(sql) == expected.strip()
def test_aliased_as_subquery(): sql = """ SELECT AGG(subquery.field) FROM (SELECT * FROM `table` WHERE value <> 0) as subquery GROUP BY subquery.col """ expected = """ SELECT AGG(subquery.field) FROM ( SELECT * FROM `table` WHERE value <> 0 ) AS subquery GROUP BY subquery.col """ assert format_sql(sql) == expected.strip()
def test_subquery_where(): sql = """ SELECT any_value(url) f_0 FROM (SELECT * FROM `toto` WHERE http_code <> 0 AND (STARTS_WITH(url, 'https') OR url = 'http://example.com')) GROUP BY url_hash """ expected = """ SELECT ANY_VALUE(url) f_0 FROM ( SELECT * FROM `toto` WHERE http_code <> 0 AND (STARTS_WITH(url, 'https') OR url = 'http://example.com') ) GROUP BY url_hash """ assert format_sql(sql) == expected.strip()
def test_parentheses_nested_select(): sql = """select (SELECT SUM(IF(hh = b.some_field.cq[SAFE_OFFSET(o)],1,0)) FROM UNNEST(a.other_field.cq) hh OFFSET o) * 100 / LEAST(a.other_field.len, b.some_field.len) result_field from t1 a join t2 b USING (f1) """ # NOQA expected = """ SELECT ( SELECT SUM(IF(hh = b.some_field.cq[SAFE_OFFSET(o)], 1, 0)) FROM UNNEST(a.other_field.cq) hh OFFSET o ) * 100 / LEAST(a.other_field.len, b.some_field.len) result_field FROM t1 a JOIN t2 b USING (f1) """ assert format_sql(sql) == expected.strip()
def test_function_calls(): sql = "select SAFE_DIVIDE(SUM(SUM(met)) OVER (PARTITION BY ANY_VALUE(hash) ORDER BY SUM(met) DESC, ANY_VALUE(hash2) ASC) ,SUM(SUM(met)) OVER (PARTITION BY ANY_VALUE(hash))) from t group by hash3" # noqa expected = """ SELECT SAFE_DIVIDE( SUM(SUM(met)) OVER ( PARTITION BY ANY_VALUE(hash) ORDER BY SUM(met) DESC, ANY_VALUE(hash2) ASC ), SUM(SUM(met)) OVER ( PARTITION BY ANY_VALUE(hash) ) ) FROM t GROUP BY hash3 """ # NOQA assert format_sql(sql) == expected.strip()
def test_query_combinations(): sql = """SELECT A FROM TA INTERSECT SELECT B FROM TB EXCEPT (SELECT C FROM TB) UNION SELECT D FROM TD""" expected = """ SELECT A FROM TA INTERSECT SELECT B FROM TB EXCEPT ( SELECT C FROM TB ) UNION SELECT D FROM TD """ assert format_sql(sql) == expected.strip()