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()