Core functions for SQL formatting

General formatting

Basic formatting for SQL queries. Let's use an example throughout the core module.

This is how an input could look like

example_sql = """
create or replace table mytable as -- Mytable example
/* multi line
   comment */
seLecT a.asdf,
-- some line comment
b.qwer, -- some comment here
/* and here is a line comment inside select */
substr(c.asdf,1,2) as substr_asdf, 
/* some commenT 
there */
case when a.asdf= 1 then 'b' /* here a case comment */
when b.qwer =2 then 'c' else 'd' end as new_field, -- Some comment
/* and here some inline comment */
b.asdf2 frOm table1 as a leFt join 
table2 as b -- and here a comment
    on a.asdf = b.asdf  /* joiN this way */
    inner join table3 as c
on a.asdf=c.asdf and a.qwer= b.qwer
whEre a.asdf= 1 -- comment this
anD b.qwer =2 and a.asdf<=1 --comment that
or b.qwer>=5
groUp by a.asdf
"""

and this is how we would like to format it

expected_sql = """CREATE OR REPLACE TABLE mytable AS -- Mytable example
/* multi line
   comment */
SELECT a.asdf,
       -- some line comment
       b.qwer, -- some comment here
       /* and here is a line comment inside select */
       substr(c.asdf, 1, 2) as substr_asdf,
       /* some commenT
          there */
       case when a.asdf = 1 then 'b' /* here a case comment */
            when b.qwer = 2 then 'c'
            else 'd' end as new_field, -- Some comment
       /* and here some inline comment */
       b.asdf2
FROM   table1 as a
    LEFT JOIN table2 as b -- and here a comment
        ON a.asdf = b.asdf /* joiN this way */
    INNER JOIN table3 as c
        ON a.asdf = c.asdf and
           a.qwer = b.qwer
WHERE  a.asdf = 1 -- comment this
   and b.qwer = 2
   and a.asdf <= 1 --comment that
    or b.qwer >= 5
GROUP BY a.asdf"""

Let's start by defining the main statements. The main statements all require a new line and should be in upper case

Remark:For OVER and PARTITION BY we only capitalize without adding a newline

We need to remove newlines and multiple spaces because they may be arbitrary.

Before removing newlines we also need to mark the end of coments with the special token [C] because we would not know where they end

We also mark the begin of comments / / with the special token [CS] (comment start) if they start in a newline

clean_query[source]

clean_query(s)

Remove redundant whitespaces, mark comments boundaries and remove newlines afterwards in query s

assert_and_print(
    clean_query(
"""
SELECT asdf, qwer,
 qwer1,    qwer2
FROM table1
"""
    ), "SELECT asdf, qwer, qwer1, qwer2 FROM table1"
)

SELECT asdf, qwer, qwer1, qwer2 FROM table1

With usual comments

assert_and_print(
    clean_query("""
SELECT asdf, qwer, -- some comment
 qwer1,    qwer2
FROM table1
WHERE asdf=1
"""), "SELECT asdf, qwer, -- some comment[C]qwer1, qwer2 FROM table1 WHERE asdf=1"
)

SELECT asdf, qwer, -- some comment[C]qwer1, qwer2 FROM table1 WHERE asdf=1

With other comment form

assert_and_print(
    clean_query("""
SELECT asdf, qwer, /*  another comment */
qwer1,    qwer2
FROM table1
WHERE asdf=1
"""), "SELECT asdf, qwer, /* another comment */[C]qwer1, qwer2 FROM table1 WHERE asdf=1"
)

SELECT asdf, qwer, /* another comment */[C]qwer1, qwer2 FROM table1 WHERE asdf=1

assert_and_print(
    clean_query(
"""
SELECT asdf, qwer, /*  another comment */
qwer1,
/* inline comment */
qwer2
FROM table1
WHERE asdf=1
"""
    ), 
    "SELECT asdf, qwer, /* another comment */[C]qwer1,[CS]/* inline comment */[C]qwer2 FROM table1 WHERE asdf=1"
)

SELECT asdf, qwer, /* another comment */[C]qwer1,[CS]/* inline comment */[C]qwer2 FROM table1 WHERE asdf=1

assert_and_print(
    clean_query(
"""
SELECT asdf,
qwer1, -- comment 1
-- inline comment
qwer2
FROM table1
WHERE asdf=1
"""
    ), 
    "SELECT asdf, qwer1, -- comment 1[C][CS]-- inline comment[C]qwer2 FROM table1 WHERE asdf=1"
)

SELECT asdf, qwer1, -- comment 1[C][CS]-- inline comment[C]qwer2 FROM table1 WHERE asdf=1

assert_and_print(
    clean_query(
"""
create or replace my_table as
/* some comment
   some new comment line */
select asdf,
qwer,   qwer2

from table1
"""
    ),
"create or replace my_table as[CS]/* some comment[CI]some new comment line */[C]select asdf, qwer, qwer2 from table1"
)

create or replace my_table as[CS]/* some comment[CI]some new comment line */[C]select asdf, qwer, qwer2 from table1

assert_and_print(
    clean_query(
"""
create or replace table my_table as
select substr( asdf, 1, 2 ) as qwer,
qwer over (
PARTITION BY asdf
)
from table1
"""
    ), "create or replace table my_table as select substr(asdf, 1, 2) as qwer, qwer over (PARTITION BY asdf) from table1"
)

create or replace table my_table as select substr(asdf, 1, 2) as qwer, qwer over (PARTITION BY asdf) from table1

print(clean_query(example_sql))

create or replace table mytable as -- Mytable example[C][CS]/* multi line[CI]comment */[C]seLecT a.asdf,[CS]-- some line comment[C]b.qwer, -- some comment here[C][CS]/* and here is a line comment inside select */[C]substr(c.asdf, 1, 2) as substr_asdf,[CS]/* some commenT[CI]there */[C]case when a.asdf= 1 then 'b' /* here a case comment */[C]when b.qwer =2 then 'c' else 'd' end as new_field, -- Some comment[C][CS]/* and here some inline comment */[C]b.asdf2 frOm table1 as a leFt join table2 as b -- and here a comment[C]on a.asdf = b.asdf /* joiN this way */[C]inner join table3 as c on a.asdf=c.asdf and a.qwer= b.qwer whEre a.asdf= 1 -- comment this[C]anD b.qwer =2 and a.asdf<=1 --comment that[C]or b.qwer>=5 groUp by a.asdf

Preformatting queries

We would like to have each main statement (SELECT, FROM , ...) in a separate line and in uppercase, ignoring text in comments. This way we can then later format each query statement separately. Also for the special case with PARTITION BY in the SELECT statement we will not add a newline because the main statement in that case is SELECT and not PARTITION BY

preformat_statements[source]

preformat_statements(s)

Write a newline in s for all statements and uppercase them but not if they are inside a comment

assert_and_print(
    preformat_statements("select asdf, qwer as new_var from table1 where asdf = 1"),
    "SELECT asdf, qwer as new_var\nFROM table1\nWHERE asdf = 1"
)

SELECT asdf, qwer as new_var
FROM table1
WHERE asdf = 1

assert_and_print(preformat_statements("""
seLect asdf,
       /* some comment inside select */
       qwer
From   table1 where  asdf = 1
"""),
    "SELECT asdf,[CS]/* some comment inside select */[C]qwer\nFROM table1\nWHERE asdf = 1"
)

SELECT asdf,[CS]/* some comment inside select */[C]qwer
FROM table1
WHERE asdf = 1

assert_and_print(
    preformat_statements("""
seLect asdf, /* some comment inside select */
       qwer
From   table1 where  asdf = 1
"""),
    "SELECT asdf, /* some comment inside select */[C]qwer\nFROM table1\nWHERE asdf = 1"
)

SELECT asdf, /* some comment inside select */[C]qwer
FROM table1
WHERE asdf = 1

assert_and_print(
    preformat_statements("""
create or replace view my_view as
seLect asdf,
       /* some comment inside select */
       qwer
From   table1 where  asdf = 1
"""),
    "CREATE OR REPLACE VIEW my_view AS\nSELECT asdf,[CS]/* some comment inside select */[C]qwer\nFROM table1\nWHERE asdf = 1"
)

CREATE OR REPLACE VIEW my_view AS
SELECT asdf,[CS]/* some comment inside select */[C]qwer
FROM table1
WHERE asdf = 1

assert_and_print(
    preformat_statements("""
create or replace view my_view as
seLect asdf,
       qwer_function,
       qwer
From   table1 where  asdf = 1
"""),
    "CREATE OR REPLACE VIEW my_view AS\nSELECT asdf, qwer_function, qwer\nFROM table1\nWHERE asdf = 1"
)

CREATE OR REPLACE VIEW my_view AS
SELECT asdf, qwer_function, qwer
FROM table1
WHERE asdf = 1

assert_and_print(
    preformat_statements("""
create or replace view my_view as
seLect asdf, qwer_function,
       lead(asdf) over (Partition By asdf order BY qwer),
    qwer2
From   table1 where  asdf = 1 order by asdf
"""),
    """
CREATE OR REPLACE VIEW my_view AS
SELECT asdf, qwer_function, lead(asdf) OVER (PARTITION BY asdf ORDER BY qwer), qwer2
FROM table1
WHERE asdf = 1
ORDER BY asdf
""".strip()
)

CREATE OR REPLACE VIEW my_view AS
SELECT asdf, qwer_function, lead(asdf) OVER (PARTITION BY asdf ORDER BY qwer), qwer2
FROM table1
WHERE asdf = 1
ORDER BY asdf

assert_and_print(
    preformat_statements(
"""
create or replace table mytable as -- Mytable example
seLecT a.asdf, b.qwer, -- some comment here
/* and here is a line comment inside select */
substr(c.asdf, 1, 2) as substr_asdf
"""
    ),
"""
CREATE OR REPLACE TABLE mytable AS -- Mytable example[C]
SELECT a.asdf, b.qwer, -- some comment here[C][CS]/* and here is a line comment inside select */[C]substr(c.asdf, 1, 2) as substr_asdf
""".strip()
)

CREATE OR REPLACE TABLE mytable AS -- Mytable example[C]
SELECT a.asdf, b.qwer, -- some comment here[C][CS]/* and here is a line comment inside select */[C]substr(c.asdf, 1, 2) as substr_asdf

assert_and_print(
    preformat_statements(
"""
create table mytable as -- Mytable example
seLecT a.asdf, b.qwer, -- some comment here
/* and here is a line comment inside select */
substr(c.asdf, 1, 2) as substr_asdf
"""
    ),
"""
CREATE TABLE mytable AS -- Mytable example[C]
SELECT a.asdf, b.qwer, -- some comment here[C][CS]/* and here is a line comment inside select */[C]substr(c.asdf, 1, 2) as substr_asdf
""".strip()
)

CREATE TABLE mytable AS -- Mytable example[C]
SELECT a.asdf, b.qwer, -- some comment here[C][CS]/* and here is a line comment inside select */[C]substr(c.asdf, 1, 2) as substr_asdf

assert_and_print(
    preformat_statements(
"""
create or replace table  my_table as -- mytable
select distinct asdf, qwer, -- some comment
from table1
"""
    ),
"""
CREATE OR REPLACE TABLE my_table AS -- mytable[C]
SELECT DISTINCT asdf, qwer, -- some comment[C]
FROM table1
""".strip()
)

CREATE OR REPLACE TABLE my_table AS -- mytable[C]
SELECT DISTINCT asdf, qwer, -- some comment[C]
FROM table1

With additional keyword COMMENT

assert_and_print(
    preformat_statements(
"""
create or replace table  my_table comment='blabla' as -- mytable
select distinct asdf, qwer, -- some comment
from table1
"""
    ),
"""
CREATE OR REPLACE TABLE my_table comment='blabla' AS -- mytable[C]
SELECT DISTINCT asdf, qwer, -- some comment[C]
FROM table1
""".strip()
)

CREATE OR REPLACE TABLE my_table comment='blabla' AS -- mytable[C]
SELECT DISTINCT asdf, qwer, -- some comment[C]
FROM table1

assert_and_print(
    preformat_statements(
"""
create or replace transient table  my_table as -- mytable
select distinct asdf, qwer, -- some comment
from table1
"""
    ),
"""
CREATE OR REPLACE TRANSIENT TABLE my_table AS -- mytable[C]
SELECT DISTINCT asdf, qwer, -- some comment[C]
FROM table1
""".strip()
)

CREATE OR REPLACE TRANSIENT TABLE my_table AS -- mytable[C]
SELECT DISTINCT asdf, qwer, -- some comment[C]
FROM table1

assert_and_print(
    preformat_statements(
"""
Create view  my_table as -- mytable
select distinct asdf, qwer, -- some comment
from table1
"""
    ),
"""
CREATE VIEW my_table AS -- mytable[C]
SELECT DISTINCT asdf, qwer, -- some comment[C]
FROM table1
""".strip()
)

CREATE VIEW my_table AS -- mytable[C]
SELECT DISTINCT asdf, qwer, -- some comment[C]
FROM table1

print(preformat_statements(example_sql))

CREATE OR REPLACE TABLE mytable AS -- Mytable example[C][CS]/* multi line[CI]comment */[C]
SELECT a.asdf,[CS]-- some line comment[C]b.qwer, -- some comment here[C][CS]/* and here is a line comment inside select */[C]substr(c.asdf, 1, 2) as substr_asdf,[CS]/* some commenT[CI]there */[C]case when a.asdf= 1 then 'b' /* here a case comment */[C]when b.qwer =2 then 'c' else 'd' end as new_field, -- Some comment[C][CS]/* and here some inline comment */[C]b.asdf2
FROM table1 as a
LEFT JOIN table2 as b -- and here a comment[C]
ON a.asdf = b.asdf /* joiN this way */[C]
INNER JOIN table3 as c
ON a.asdf=c.asdf and a.qwer= b.qwer
WHERE a.asdf= 1 -- comment this[C]anD b.qwer =2 and a.asdf<=1 --comment that[C]or b.qwer>=5
GROUP BY a.asdf

Lowercasing query

lowercase_query[source]

lowercase_query(s)

Lowercase query but let comments and text in quotes untouched

assert_and_print(
    lowercase_query("""
--- My nice view 1 --
Create or Replace VieW view_1 as
seLect asdf, -- Some Comment
qwER,
qwerTy, -- Some other comment
FROM table1
"""),
    """
--- My nice view 1 --
create or replace view view_1 as
select asdf, -- Some Comment
qwer,
qwerty, -- Some other comment
from table1
"""
)

--- My nice view 1 --
create or replace view view_1 as
select asdf, -- Some Comment
qwer,
qwerty, -- Some other comment
from table1


assert_and_print(
    lowercase_query("""
-- Some comment --
Create Or rePlace tablE aS
sElEct asdf,
/* sOme CommEnt */
qwer
FroM table1
"""),
"""
-- Some comment --
create or replace table as
select asdf,
/* sOme CommEnt */
qwer
from table1
"""
)

-- Some comment --
create or replace table as
select asdf,
/* sOme CommEnt */
qwer
from table1


assert_and_print(
    lowercase_query("""
-- Some comment --
Create Or rePlace tablE aS
sElEct asdf, replace('J', 'N', Asdf2) as Asdf3
/* sOme CommEnt */
qwer
FroM table1
"""),
"""
-- Some comment --
create or replace table as
select asdf, replace('J', 'N', asdf2) as asdf3
/* sOme CommEnt */
qwer
from table1
"""
)

-- Some comment --
create or replace table as
select asdf, replace('J', 'N', asdf2) as asdf3
/* sOme CommEnt */
qwer
from table1


assert_and_print(
    lowercase_query("""
-- Some comment --
Create Or rePlace tablE aS
sElEct asdf, replace('J', 'N', Asdf2) as Asdf3
/* sOme CommEnt */
qwer
FroM table1 -- Some comment
"""),
"""
-- Some comment --
create or replace table as
select asdf, replace('J', 'N', asdf2) as asdf3
/* sOme CommEnt */
qwer
from table1 -- Some comment
"""
)

-- Some comment --
create or replace table as
select asdf, replace('J', 'N', asdf2) as asdf3
/* sOme CommEnt */
qwer
from table1 -- Some comment

Add whitespaces between symbols

add_whitespaces_query[source]

add_whitespaces_query(s)

Add whitespaces between symbols (=!<>) for query s but not for comments

assert_and_print(
    add_whitespaces_query(
"""
create or replace table my_table as /* some comment 1=1, 1 =1 */
select asdf, case when asdf= 1 then '=' else 0 end as qwerty
from table1 as a
left join table2 as b on a.asdf= b.asdf
where asdf=1 and qwer =2
"""
    ),
"""
create or replace table my_table as /* some comment 1=1, 1 =1 */
select asdf, case when asdf = 1 then '=' else 0 end as qwerty
from table1 as a
left join table2 as b on a.asdf = b.asdf
where asdf = 1 and qwer = 2
"""    
)

create or replace table my_table as /* some comment 1=1, 1 =1 */
select asdf, case when asdf = 1 then '=' else 0 end as qwerty
from table1 as a
left join table2 as b on a.asdf = b.asdf
where asdf = 1 and qwer = 2

Specific formatting and validation

Now we will format each statement individually

PARTITION BY

Helper function for format PARTITION BY Within SELECT

format_partition_by[source]

format_partition_by(s, base_indentation)

Format PARTITION BY line in SELECT (DISTINCT)

Remove (mistake) comma at end of SELECT

remove_wrong_end_comma[source]

remove_wrong_end_comma(split_s)

Remove mistakenly placed commas at the end of SELECT statement using split_s with keys "string", "comment" and "quote"

assert_and_print(
    split_apply_concat("select asdf, qwer, ", remove_wrong_end_comma),
    "select asdf, qwer"
)

select asdf, qwer

assert_and_print(
    split_apply_concat("select asdf, qwer, -- some comment", remove_wrong_end_comma),
    "select asdf, qwer -- some comment"
)

select asdf, qwer -- some comment

assert_and_print(
    split_apply_concat("select asdf, qwer, /* another comment */", remove_wrong_end_comma),
    "select asdf, qwer /* another comment */"
)

select asdf, qwer /* another comment */

assert_and_print(
    split_apply_concat("select asdf, qwer,,,, /* more than 1 comma */", remove_wrong_end_comma),    
    "select asdf, qwer /* more than 1 comma */"
)

select asdf, qwer /* more than 1 comma */

Helper function for case when

format_case_when[source]

format_case_when(s)

Format case when statement in line s

Helper function to reformat too long lines in SELECT statement

reformat_too_long_line[source]

reformat_too_long_line(li, max_len=82)

Reformat too long line li if it is longer than max_len characters after stripping

assert_and_print(
    reformat_too_long_line(
        "select concat(substr(concat(')0', substr(asdf, 1, 2)), -2, 2), substr(concat('(0', substr(asdf, 3, 2)), -2, 2)) as qwer"
    ),
"""
select concat(substr(concat(')0', substr(asdf, 1, 2)), -2, 2),
              substr(concat('(0', substr(asdf, 3, 2)), -2, 2)) as qwer
""".strip()
)

select concat(substr(concat(')0', substr(asdf, 1, 2)), -2, 2),
              substr(concat('(0', substr(asdf, 3, 2)), -2, 2)) as qwer

assert_and_print(
    reformat_too_long_line(
        "       concat(substr(concat(')0', substr(asdf, 1, 2)), -2, 2), substr(concat('(0', substr(asdf, 3, 2)), -2, 2)) as qwer"
    ),
"""       concat(substr(concat(')0', substr(asdf, 1, 2)), -2, 2),
              substr(concat('(0', substr(asdf, 3, 2)), -2, 2)) as qwer"""
)

       concat(substr(concat(')0', substr(asdf, 1, 2)), -2, 2),
              substr(concat('(0', substr(asdf, 3, 2)), -2, 2)) as qwer

assert_and_print(
    reformat_too_long_line(
        "            when concat('asdf', 'qwer', 'qwerqwerqwerqwerqwerqwerqwr', substr(concat('asdf', 'qwer'), 1, 2)) then 2"
    ),
"            when concat('asdf',\n"
"                        'qwer',\n"
"                        'qwerqwerqwerqwerqwerqwerqwr',\n"
"                        substr(concat('asdf', 'qwer'), 1, 2)) then 2"
)

            when concat('asdf',
                        'qwer',
                        'qwerqwerqwerqwerqwerqwerqwr',
                        substr(concat('asdf', 'qwer'), 1, 2)) then 2

assert_and_print(
    reformat_too_long_line(
        "select case when asdf in (1234, 2345, 1234, 2354, 2345, 2352345, 245623462, 124123412, 124312341234) then 1"
    ),
"""
select case when asdf in (1234, 2345, 1234, 2354, 2345, 2352345, 245623462, 124123412,
                          124312341234) then 1
""".strip()
)

select case when asdf in (1234, 2345, 1234, 2354, 2345, 2352345, 245623462, 124123412,
                          124312341234) then 1

assert_and_print(
    reformat_too_long_line(
        "select case when asdf in (1234, 2345, 1234, 2354, 2345, 2352345, 245623462, 124123412, 124312341234) then 1",
        max_len=40
    ),
"""
select case when asdf in (1234, 2345, 1234,
                          2354, 2345, 2352345,
                          245623462, 124123412,
                          124312341234) then 1
""".strip()
)

select case when asdf in (1234, 2345, 1234,
                          2354, 2345, 2352345,
                          245623462, 124123412,
                          124312341234) then 1

assert_and_print(
    reformat_too_long_line(
        "select case when asdf in ('1234', '2345', '1234', '2354', '2345', '2352345', '245623462', '124123412', '124312341234') then 1",
        max_len=40
    ),
"""
select case when asdf in ('1234', '2345',
                          '1234', '2354',
                          '2345', '2352345',
                          '245623462', '124123412',
                          '124312341234') then 1
""".strip()
)

select case when asdf in ('1234', '2345',
                          '1234', '2354',
                          '2345', '2352345',
                          '245623462', '124123412',
                          '124312341234') then 1

assert_and_print(
    reformat_too_long_line("select asdf"),
"select asdf"
)

select asdf

assert_and_print(
    reformat_too_long_line("select asdf + asdf1 + asdf2 + asdf3 + asdf4 + asdf5 + asdf6 + asdf7 + asdf8 + asdf9"),
"select asdf + asdf1 + asdf2 + asdf3 + asdf4 + asdf5 + asdf6 + asdf7 + asdf8 + asdf9"
)

select asdf + asdf1 + asdf2 + asdf3 + asdf4 + asdf5 + asdf6 + asdf7 + asdf8 + asdf9

SELECT

format_select[source]

format_select(s, max_len=82)

Format SELECT statement line s. If line is longer than max_len then reformat line

Simple usage without comments

assert_and_print(
    format_select("select aSdf, cast(qweR as numeric),  Asdf,qwer1"),
    "select aSdf,\n       cast(qweR as numeric),\n       Asdf,\n       qwer1"
)

select aSdf,
       cast(qweR as numeric),
       Asdf,
       qwer1

More advanced usage with comments in SELECT

assert_and_print(
    format_select("select asdf, cast(qwer as numeric), -- some comment[C]ASDF, qwer1"),
    "select asdf,\n       cast(qwer as numeric), -- some comment\n       ASDF,\n       qwer1"
)

select asdf,
       cast(qwer as numeric), -- some comment
       ASDF,
       qwer1

assert_and_print(
    format_select("select asdf, -- Some comment[C]asdforqwer -- Another comment[C]"),
"""
select asdf, -- Some comment
       asdforqwer -- Another comment
""".strip()
)

select asdf, -- Some comment
       asdforqwer -- Another comment

Correcting common mistake on the flow: comma at end of SELECT

assert_and_print(
    format_select("select qwer1,   asdf,"),
    "select qwer1,\n       asdf"
)

select qwer1,
       asdf

assert_and_print(
    format_select("SELECT a.asdf, b.qwer, -- some comment here[C][CS]/* and here is a line comment inside select */[C]qwer2"),
"""
SELECT a.asdf,
       b.qwer, -- some comment here
       /* and here is a line comment inside select */
       qwer2
""".strip()
)

SELECT a.asdf,
       b.qwer, -- some comment here
       /* and here is a line comment inside select */
       qwer2

assert_and_print(
    format_select("SELECT a.asdf,[CS]/* and here is a line comment inside select */"),
"""
SELECT a.asdf
       /* and here is a line comment inside select */
""".strip()
)

SELECT a.asdf
       /* and here is a line comment inside select */

Line comment with --

assert_and_print(
    format_select("SELECT a.asdf, b.qwer, -- some comment here[C][CS]-- and here is a line comment inside select[C]qwer2"),
"""
SELECT a.asdf,
       b.qwer, -- some comment here
       -- and here is a line comment inside select
       qwer2
""".strip()
)

SELECT a.asdf,
       b.qwer, -- some comment here
       -- and here is a line comment inside select
       qwer2

Correcting comma at end of SELECT but having a comment in the last field

assert_and_print(
    format_select("select qwer1 as qwer2,   asdf as asdf3, -- this field"),
    "select qwer1 as qwer2,\n       asdf as asdf3 -- this field"
)

select qwer1 as qwer2,
       asdf as asdf3 -- this field

assert_and_print(
    format_select("select qwer1,   asdf, /* this field */"),
    "select qwer1,\n       asdf /* this field */"
)

select qwer1,
       asdf /* this field */

With case when conditions

assert_and_print(
    format_select("select qwer1, case when abc = 1 then 'a' when abc = 2 then 'b' else 'c' end as qwer2"),
    """
select qwer1,
       case when abc = 1 then 'a'
            when abc = 2 then 'b'
            else 'c' end as qwer2
    """.strip()    
)

select qwer1,
       case when abc = 1 then 'a'
            when abc = 2 then 'b'
            else 'c' end as qwer2

assert_and_print(
    format_select("select qwer1, case when abc = 1 then 'a' -- first condition[C]" +
                  "when abc = 2 then 'b' -- second condition[C]" +
                  "else 'c' end as qwer2, /* else condition */[C]"
                  "asdf3"
),
    """
select qwer1,
       case when abc = 1 then 'a' -- first condition
            when abc = 2 then 'b' -- second condition
            else 'c' end as qwer2, /* else condition */
       asdf3
    """.strip()    
)

select qwer1,
       case when abc = 1 then 'a' -- first condition
            when abc = 2 then 'b' -- second condition
            else 'c' end as qwer2, /* else condition */
       asdf3

Bad formatted case when condition

assert_and_print(
    format_select("select qwer1, case when abc <= 1 then 'a' -- first condition[C]" +
                  "when abc = 2 then 'b' -- second condition[C]" +
                  "else 'c' end as qwer2, -- else condition[C]"
                  "asdf3"
),
    """
select qwer1,
       case when abc <= 1 then 'a' -- first condition
            when abc = 2 then 'b' -- second condition
            else 'c' end as qwer2, -- else condition
       asdf3
    """.strip()    
)

select qwer1,
       case when abc <= 1 then 'a' -- first condition
            when abc = 2 then 'b' -- second condition
            else 'c' end as qwer2, -- else condition
       asdf3

case when and comment after condition

assert_and_print(
    format_select("select qwer1, case when abc <= 1 and -- first condition[C]" +
                  "abc >= -1 then 'a' -- second condition[C]" +
                  "else 'c' end as qwer2, -- else condition[C]"
                  "asdf3"
),
    """
select qwer1,
       case when abc <= 1 and -- first condition
                 abc >= -1 then 'a' -- second condition
            else 'c' end as qwer2, -- else condition
       asdf3
    """.strip()    
)

select qwer1,
       case when abc <= 1 and -- first condition
                 abc >= -1 then 'a' -- second condition
            else 'c' end as qwer2, -- else condition
       asdf3

case when in comments

assert_and_print(
    format_select("select qwer1, case when abc <= 1 and -- first condition case when[C]" +
                  "abc >= -1 then 'a' -- second condition case when[C]" +
                  "else 'c' end as qwer2, -- else condition[C]"
                  "asdf3"
),
"""
select qwer1,
       case when abc <= 1 and -- first condition case when
                 abc >= -1 then 'a' -- second condition case when
            else 'c' end as qwer2, -- else condition
       asdf3
""".strip()    
)

select qwer1,
       case when abc <= 1 and -- first condition case when
                 abc >= -1 then 'a' -- second condition case when
            else 'c' end as qwer2, -- else condition
       asdf3

assert_and_print(
    format_select("select asdf, case when asdf >= 1 and asdf <= 10 and" +
                  " substr(qwer, 1, 2) = 'abc' and substr(qwer, 3, 2) = 'qwerty'" +
                  " then 1 else 0 end as case_field, asdf2"
    ),
"""
select asdf,
       case when asdf >= 1 and
                 asdf <= 10 and
                 substr(qwer, 1, 2) = 'abc' and
                 substr(qwer, 3, 2) = 'qwerty' then 1
            else 0 end as case_field,
       asdf2
""".strip()
)

select asdf,
       case when asdf >= 1 and
                 asdf <= 10 and
                 substr(qwer, 1, 2) = 'abc' and
                 substr(qwer, 3, 2) = 'qwerty' then 1
            else 0 end as case_field,
       asdf2

2 case when ... end

assert_and_print(
    format_select(
        "select asdf, cast(case when asdf = 1 then 0 else 1 end as int) as qwer, " +
        "case when asdf = 0 then 1 else 0 end as qwer2"
    ),
"""
select asdf,
       cast(case when asdf = 1 then 0
                 else 1 end as int) as qwer,
       case when asdf = 0 then 1
            else 0 end as qwer2
""".strip()
)

select asdf,
       cast(case when asdf = 1 then 0
                 else 1 end as int) as qwer,
       case when asdf = 0 then 1
            else 0 end as qwer2

With functions in SELECT

assert_and_print(
    format_select("select aSdf, substr(qweR, 2) as qwer,  Asdf,qwer1"),
    "select aSdf,\n       substr(qweR, 2) as qwer,\n       Asdf,\n       qwer1"
)

select aSdf,
       substr(qweR, 2) as qwer,
       Asdf,
       qwer1

assert_and_print(
    format_select(
"""
select car_id,
       avg(price) as avg_price,
"""
    ),
"""
select car_id,
       avg(price) as avg_price
""".strip()
)

select car_id,
       avg(price) as avg_price

With function in SELECT and case when

assert_and_print(
    format_select("select qwer1, cast(case when asdf = 'J' then 1 else 0 end) as qwer2, qwer3"),
"""
select qwer1,
       cast(case when asdf = 'J' then 1
                 else 0 end) as qwer2,
       qwer3
""".strip()    
)

select qwer1,
       cast(case when asdf = 'J' then 1
                 else 0 end) as qwer2,
       qwer3

assert_and_print(
    format_select("select qwer1, cast(substr(case when asdf = 'CASE WHEN' then 1 else 0 end, 2, 1)) as qwer2, qwer3"),
"""
select qwer1,
       cast(substr(case when asdf = 'CASE WHEN' then 1
                        else 0 end, 2, 1)) as qwer2,
       qwer3
""".strip()    
)

select qwer1,
       cast(substr(case when asdf = 'CASE WHEN' then 1
                        else 0 end, 2, 1)) as qwer2,
       qwer3

assert_and_print(
    format_select("select qwer1, substr(qwer, case when asdf = 'J' then 1 else 0 end, 4) as qwer2, qwer3"),
"""
select qwer1,
       substr(qwer, case when asdf = 'J' then 1
                         else 0 end, 4) as qwer2,
       qwer3
""".strip()    
)

select qwer1,
       substr(qwer, case when asdf = 'J' then 1
                         else 0 end, 4) as qwer2,
       qwer3

assert_and_print(
    format_select("select qwer1, substr('blabla', case when asdf = 'J' then 1 else 0 end, 4) as qwer2, qwer3"),
"""
select qwer1,
       substr('blabla', case when asdf = 'J' then 1
                             else 0 end, 4) as qwer2,
       qwer3
""".strip()    
)

select qwer1,
       substr('blabla', case when asdf = 'J' then 1
                             else 0 end, 4) as qwer2,
       qwer3

With SELECT DISTINCT

assert_and_print(
    format_select("select distinct asdf, qwer, qwer2,"),
"""
select distinct asdf,
                qwer,
                qwer2
""".strip()
)

select distinct asdf,
                qwer,
                qwer2

assert_and_print(
    format_select("select distinct asdf, case when asdf = 1 then 1 else 2 end as qwerty, qwer2,"),
"""
select distinct asdf,
                case when asdf = 1 then 1
                     else 2 end as qwerty,
                qwer2
""".strip()
)

select distinct asdf,
                case when asdf = 1 then 1
                     else 2 end as qwerty,
                qwer2

With PARTITION BY

assert_and_print(
    format_select("select asdf, lead(asdf) over (partition by qwer, asdf2 order by qwer2) as qwer3, qwerty,"),
"""
select asdf,
       lead(asdf) over (partition by qwer,
                                     asdf2
                        order by qwer2) as qwer3,
       qwerty
""".strip()
)

select asdf,
       lead(asdf) over (partition by qwer,
                                     asdf2
                        order by qwer2) as qwer3,
       qwerty

assert_and_print(
    format_select("select asdf, lead(asdf) over (partition by asdf, qwer order by qwer), cast(qwer as numeric), -- some comment[C]ASDF, "),
"""
select asdf,
       lead(asdf) over (partition by asdf,
                                     qwer
                        order by qwer),
       cast(qwer as numeric), -- some comment
       ASDF
""".strip()
)

select asdf,
       lead(asdf) over (partition by asdf,
                                     qwer
                        order by qwer),
       cast(qwer as numeric), -- some comment
       ASDF

assert_and_print(
    format_select("select asdf, lead(asdf) over (partition by asdf, qwer order by qwer, qwer2), cast(qwer as numeric), -- some comment[C]ASDF, "),
"""
select asdf,
       lead(asdf) over (partition by asdf,
                                     qwer
                        order by qwer, qwer2),
       cast(qwer as numeric), -- some comment
       ASDF
""".strip()
)

select asdf,
       lead(asdf) over (partition by asdf,
                                     qwer
                        order by qwer, qwer2),
       cast(qwer as numeric), -- some comment
       ASDF

assert_and_print(
    format_select("select asdf, lead(asdf, 1, 2) OVER (PARTITION BY snr, qwer ORDER BY asdf, qwer)"),
"""
select asdf,
       lead(asdf, 1, 2) OVER (PARTITION BY snr,
                                           qwer
                              ORDER BY asdf, qwer)
""".strip()
)

select asdf,
       lead(asdf, 1, 2) OVER (PARTITION BY snr,
                                           qwer
                              ORDER BY asdf, qwer)

assert_and_print(
    format_select("select DISTINCT asdf, lead(asdf) over (partition by asdf, qwer order by qwer), cast(qwer as numeric), -- some comment[C]ASDF, "),
"""
select DISTINCT asdf,
                lead(asdf) over (partition by asdf,
                                              qwer
                                 order by qwer),
                cast(qwer as numeric), -- some comment
                ASDF
""".strip()
)

select DISTINCT asdf,
                lead(asdf) over (partition by asdf,
                                              qwer
                                 order by qwer),
                cast(qwer as numeric), -- some comment
                ASDF

With comments within PARTITION BY

assert_and_print(
    format_select("select DISTINCT asdf, lead(asdf) over (partition by asdf, -- some comment[C]qwer order by qwer), cast(qwer as numeric), -- some comment[C]ASDF, "),
"""
select DISTINCT asdf,
                lead(asdf) over (partition by asdf, -- some comment
                                              qwer
                                 order by qwer),
                cast(qwer as numeric), -- some comment
                ASDF
""".strip()
)

select DISTINCT asdf,
                lead(asdf) over (partition by asdf, -- some comment
                                              qwer
                                 order by qwer),
                cast(qwer as numeric), -- some comment
                ASDF

With too long lines

assert_and_print(
    format_select(
        "select asdf, concat(substr(concat(')0', substr(asdf, 1, 2)), -2, 2), substr(concat('(0', substr(asdf, 3, 2)), -2, 2)) as qwer"
    ),
"""
select asdf,
       concat(substr(concat(')0', substr(asdf, 1, 2)), -2, 2),
              substr(concat('(0', substr(asdf, 3, 2)), -2, 2)) as qwer
""".strip()
)

select asdf,
       concat(substr(concat(')0', substr(asdf, 1, 2)), -2, 2),
              substr(concat('(0', substr(asdf, 3, 2)), -2, 2)) as qwer

assert_and_print(
    format_select(
        "select asdf, case when asdf in (1231, 123123, 12312, 21412431234, 12341234123, 12341234, 12341234, 1234) then 1 else 0 end as qwer, asdf2"
    ),
"""
select asdf,
       case when asdf in (1231, 123123, 12312, 21412431234, 12341234123, 12341234, 12341234,
                          1234) then 1
            else 0 end as qwer,
       asdf2
""".strip()
)

select asdf,
       case when asdf in (1231, 123123, 12312, 21412431234, 12341234123, 12341234, 12341234,
                          1234) then 1
            else 0 end as qwer,
       asdf2

Long line with in (...) and quotes

assert_and_print(
    format_select(
        "select asdf, case when asdf in ('1231', '123123', '12312', '21412431234', '12341234123', '12341234', '12341234', '1234') then 1 else 0 end as qwer, asdf2"
    ),
"""
select asdf,
       case when asdf in ('1231', '123123', '12312', '21412431234', '12341234123', '12341234',
                          '12341234', '1234') then 1
            else 0 end as qwer,
       asdf2
""".strip()
)

select asdf,
       case when asdf in ('1231', '123123', '12312', '21412431234', '12341234123', '12341234',
                          '12341234', '1234') then 1
            else 0 end as qwer,
       asdf2

FROM

format_from[source]

format_from(s, **kwargs)

Format FROM statement line s

assert_and_print(format_from("from table1"), "from   table1")

from   table1

(LEFT / RIGHT / INNER / OUTER) JOIN

format_join[source]

format_join(s, **kwargs)

Format JOIN statement line s

assert_and_print(format_join("inner join table1"), "    inner join table1")

    inner join table1

ON

format_on[source]

format_on(s, **kwargs)

Format ON statement line s

assert_and_print(
    format_on("on a.asdf = b.asdf /* some comment */[C]"), 
"""
        on a.asdf = b.asdf /* some comment */
""".strip("\n")
)

        on a.asdf = b.asdf /* some comment */

assert_and_print(
    format_on("on a.asdf = b.asdf and a.qwer = b.qwer"), 
"""
        on a.asdf = b.asdf and
           a.qwer = b.qwer
""".strip("\n")
)

        on a.asdf = b.asdf and
           a.qwer = b.qwer

assert_and_print(
    format_on("on a.asdf = b.asdf and a.qwer = b.qwer or a.qwer2 = b.qwer2"), 
"""
        on a.asdf = b.asdf and
           a.qwer = b.qwer or
           a.qwer2 = b.qwer2
""".strip("\n")
)

        on a.asdf = b.asdf and
           a.qwer = b.qwer or
           a.qwer2 = b.qwer2

With comments and bad formatted

assert_and_print(
    format_on("on a.asdf = b.asdf -- some comment[C]and a.qwer = b.qwer or /* another comment */[C]a.qwer2 = b.qwer2"), 
"""
        on a.asdf = b.asdf and -- some comment
           a.qwer = b.qwer or /* another comment */
           a.qwer2 = b.qwer2
""".strip("\n")
)

        on a.asdf = b.asdf and -- some comment
           a.qwer = b.qwer or /* another comment */
           a.qwer2 = b.qwer2

WHERE

format_where[source]

format_where(s, **kwargs)

Format WHERE statement line s

assert_and_print(
    format_where(
        "WHERE asdf = 1 and qwer = 1 or blabla = 'asdf'"
    ), "WHERE  asdf = 1\n   and qwer = 1\n    or blabla = 'asdf'"
)

WHERE  asdf = 1
   and qwer = 1
    or blabla = 'asdf'

assert_and_print(
    format_where(
        "WHERE asdf = 1 -- and some comment[C]and qwer = 1 or blabla = 'asdf'"
    ), "WHERE  asdf = 1 -- and some comment\n   and qwer = 1\n    or blabla = 'asdf'"
)

WHERE  asdf = 1 -- and some comment
   and qwer = 1
    or blabla = 'asdf'

assert_and_print(
    format_where(
        "WHERE asdf = 1 and -- and some comment[C]qwer = 1 or blabla = 'asdf'"
    ), "WHERE  asdf = 1 -- and some comment\n   and qwer = 1\n    or blabla = 'asdf'"
)

WHERE  asdf = 1 -- and some comment
   and qwer = 1
    or blabla = 'asdf'

Format all statements

format_statement_line[source]

format_statement_line(s, **kwargs)

Format statement line s

assert_and_print(
    format_statement_line("select asdf, qwer"),
    """
select asdf,
       qwer
""".strip())

select asdf,
       qwer

assert_and_print(
    format_statement_line("left join table1 as abc"),
    "    left join table1 as abc"
)

    left join table1 as abc

assert_and_print(
    format_statement_line("where asdf = 1 and qwer = 'things' and blabla = 0 or stuff = -1"),
    """
where  asdf = 1
   and qwer = 'things'
   and blabla = 0
    or stuff = -1
    """.strip())

where  asdf = 1
   and qwer = 'things'
   and blabla = 0
    or stuff = -1

format_statements[source]

format_statements(s, **kwargs)

Format statements lines s

assert_and_print(
    format_statements("select asdf, qwer\nfrom table1", max_len=82),
"""
select asdf,
       qwer
from   table1
""".strip()
)

select asdf,
       qwer
from   table1

Format multiline comments

format_multiline_comments[source]

format_multiline_comments(s)

Format multiline comments by replacing multiline comment [CI] by newline and adding indentation

Add semicolon at the end of query

add_semicolon[source]

add_semicolon(s)

Add a semicolon at the of query s

assert_and_print(
add_semicolon("""
CREATE OR REPLACE TABLE my_table AS
SELECT asdf,
       qwer
FROM   table1 /* something */
""".strip()),
"""
CREATE OR REPLACE TABLE my_table AS
SELECT asdf,
       qwer
FROM   table1; /* something */
""".strip()
)

CREATE OR REPLACE TABLE my_table AS
SELECT asdf,
       qwer
FROM   table1; /* something */

assert_and_print(
add_semicolon("""
CREATE OR REPLACE TABLE my_table AS
SELECT asdf,
       qwer
FROM   table1 -- some thing
""".strip()),
"""
CREATE OR REPLACE TABLE my_table AS
SELECT asdf,
       qwer
FROM   table1; -- some thing
""".strip()
)

CREATE OR REPLACE TABLE my_table AS
SELECT asdf,
       qwer
FROM   table1; -- some thing

assert_and_print(
add_semicolon("""
CREATE OR REPLACE TABLE my_table AS
SELECT asdf,
       qwer
FROM   table1
""".strip()),
"""
CREATE OR REPLACE TABLE my_table AS
SELECT asdf,
       qwer
FROM   table1;
""".strip()
)

CREATE OR REPLACE TABLE my_table AS
SELECT asdf,
       qwer
FROM   table1;

Putting everything together

to format a simple query without subqueries

format_simple_sql[source]

format_simple_sql(s, semicolon=False, max_len=82)

Format a simple SQL query without subqueries s

assert_and_print(
    format_simple_sql(example_sql),
    expected_sql
)

CREATE OR REPLACE TABLE mytable AS -- Mytable example
/* multi line
   comment */
SELECT a.asdf,
       -- some line comment
       b.qwer, -- some comment here
       /* and here is a line comment inside select */
       substr(c.asdf, 1, 2) as substr_asdf,
       /* some commenT
          there */
       case when a.asdf = 1 then 'b' /* here a case comment */
            when b.qwer = 2 then 'c'
            else 'd' end as new_field, -- Some comment
       /* and here some inline comment */
       b.asdf2
FROM   table1 as a
    LEFT JOIN table2 as b -- and here a comment
        ON a.asdf = b.asdf /* joiN this way */
    INNER JOIN table3 as c
        ON a.asdf = c.asdf and
           a.qwer = b.qwer
WHERE  a.asdf = 1 -- comment this
   and b.qwer = 2
   and a.asdf <= 1 --comment that
    or b.qwer >= 5
GROUP BY a.asdf

assert_and_print(
    format_simple_sql(
"""
create or replace table first_table as -- my first table
select car_id,
       avg(price) as avg_price,
from first_view
group by car_id
"""
    ),
"""
CREATE OR REPLACE TABLE first_table AS -- my first table
SELECT car_id,
       avg(price) as avg_price
FROM   first_view
GROUP BY car_id
""".strip()
)

CREATE OR REPLACE TABLE first_table AS -- my first table
SELECT car_id,
       avg(price) as avg_price
FROM   first_view
GROUP BY car_id

Queries with subqueries

This is how we could (badly) write a query with subqueries

example_with_subqueries = """
select asdf, cast(qwer as numeric), -- some comment
substr(qwer1, 3, 2) as substr_qwer /* some field */
from 
(select asdf, qwer, /* some nice field */ from table1 where asdf = 1) as a
left 
join (select asdf, qwer2 from table2 where qwer2 = 1) as b
on a.asdf = b.asdf
where qwer1 >= 0
"""

and this is the way we would like to have it nicely formatted

expected_with_subqueries = """
SELECT asdf,
       cast(qwer as numeric), -- some comment
       substr(qwer1, 3, 2) as substr_qwer /* some field */
FROM   (SELECT asdf,
               qwer /* some nice field */
        FROM   table1
        WHERE  asdf = 1) as a
    LEFT JOIN (SELECT asdf,
                      qwer2
               FROM   table2
               WHERE  qwer2 = 1) as b
        ON a.asdf = b.asdf
WHERE  qwer1 >= 0
""".strip()

Main function handling queries with subqueries

format_sql[source]

format_sql(s, semicolon=False, max_len=82)

Format SQL query with subqueries s

assert_and_print(
    format_sql(example_with_subqueries),
    expected_with_subqueries
)

SELECT asdf,
       cast(qwer as numeric), -- some comment
       substr(qwer1, 3, 2) as substr_qwer /* some field */
FROM   (SELECT asdf,
               qwer /* some nice field */
        FROM   table1
        WHERE  asdf = 1) as a
    LEFT JOIN (SELECT asdf,
                      qwer2
               FROM   table2
               WHERE  qwer2 = 1) as b
        ON a.asdf = b.asdf
WHERE  qwer1 >= 0

It even works with simple queries without subqueries, therefore generalizing the format_simple_sql() function

assert_and_print(
    format_sql(example_sql),
    expected_sql
)

CREATE OR REPLACE TABLE mytable AS -- Mytable example
/* multi line
   comment */
SELECT a.asdf,
       -- some line comment
       b.qwer, -- some comment here
       /* and here is a line comment inside select */
       substr(c.asdf, 1, 2) as substr_asdf,
       /* some commenT
          there */
       case when a.asdf = 1 then 'b' /* here a case comment */
            when b.qwer = 2 then 'c'
            else 'd' end as new_field, -- Some comment
       /* and here some inline comment */
       b.asdf2
FROM   table1 as a
    LEFT JOIN table2 as b -- and here a comment
        ON a.asdf = b.asdf /* joiN this way */
    INNER JOIN table3 as c
        ON a.asdf = c.asdf and
           a.qwer = b.qwer
WHERE  a.asdf = 1 -- comment this
   and b.qwer = 2
   and a.asdf <= 1 --comment that
    or b.qwer >= 5
GROUP BY a.asdf

Nested subqueries

The function is also robust against nested subqueries

example_nested_subqueries = """
select asdf, qwer
from (select a.asdf,  lead(a.substr_qwer) over (partition by a.asdf, asdf2 order by qwer) as lead_qwerty
    from (select asdf, substr(qwer, 3, 2) as substr_qwer from table2) as a
        inner join (select asdf, qwer from table3) as b
            on a.qwer = b.qwer
)
"""

expected_nested = """SELECT asdf,
       qwer
FROM   (SELECT a.asdf,
               lead(a.substr_qwer) OVER (PARTITION BY a.asdf,
                                                      asdf2
                                         ORDER BY qwer) as lead_qwerty
        FROM   (SELECT asdf,
                       substr(qwer, 3, 2) as substr_qwer
                FROM   table2) as a
            INNER JOIN (SELECT asdf,
                               qwer
                        FROM   table3) as b
                ON a.qwer = b.qwer)"""

assert_and_print(
    format_sql(example_nested_subqueries),
    expected_nested
)

SELECT asdf,
       qwer
FROM   (SELECT a.asdf,
               lead(a.substr_qwer) OVER (PARTITION BY a.asdf,
                                                      asdf2
                                         ORDER BY qwer) as lead_qwerty
        FROM   (SELECT asdf,
                       substr(qwer, 3, 2) as substr_qwer
                FROM   table2) as a
            INNER JOIN (SELECT asdf,
                               qwer
                        FROM   table3) as b
                ON a.qwer = b.qwer)

With SELECT DISTINCT 

assert_and_print(
    format_sql(
"""
select asdf, qwer from (select distinct asdf, qwer from table1)
"""
    ),
"""
SELECT asdf,
       qwer
FROM   (SELECT DISTINCT asdf,
                        qwer
        FROM   table1)
""".strip()
)

SELECT asdf,
       qwer
FROM   (SELECT DISTINCT asdf,
                        qwer
        FROM   table1)

More convoluted nested subquery 

example_convoluted = """
select asdf
from (
    select asdf, qwer, /* some comment */
    from (select a.asdf, b.qwer, --some comment
          from (select asdf 
                from table1) as a 
            right join (select qwer 
                        from table2) as b
                on a.asdf = b.asdf)
)
"""

expected_convoluted = """SELECT asdf
FROM   (SELECT asdf,
               qwer /* some comment */
        FROM   (SELECT a.asdf,
                       b.qwer --some comment
                FROM   (SELECT asdf
                        FROM   table1) as a
                    RIGHT JOIN (SELECT qwer
                                FROM   table2) as b
                        ON a.asdf = b.asdf))"""

assert_and_print(
    format_sql(example_convoluted),
    expected_convoluted
)

SELECT asdf
FROM   (SELECT asdf,
               qwer /* some comment */
        FROM   (SELECT a.asdf,
                       b.qwer --some comment
                FROM   (SELECT asdf
                        FROM   table1) as a
                    RIGHT JOIN (SELECT qwer
                                FROM   table2) as b
                        ON a.asdf = b.asdf))

assert_and_print(
    format_sql(
"""
select asdf,
qwer
from table1 union select qwer,
asdf, asdf2 from table3
where asdf2 >=2
"""
    ),
"""
SELECT asdf,
       qwer
FROM   table1
UNION
SELECT qwer,
       asdf,
       asdf2
FROM   table3
WHERE  asdf2 >= 2
""".strip()
)

SELECT asdf,
       qwer
FROM   table1
UNION
SELECT qwer,
       asdf,
       asdf2
FROM   table3
WHERE  asdf2 >= 2

Partition By with newline

assert_and_print(
format_sql("""
create or replace table asdf as
select asdf, qwer over (
partition by asdf, qwer order by qwerty
)
from table1
"""),
"""
CREATE OR REPLACE TABLE asdf AS
SELECT asdf,
       qwer OVER (PARTITION BY asdf,
                               qwer
                  ORDER BY qwerty)
FROM   table1
""".strip()
)

CREATE OR REPLACE TABLE asdf AS
SELECT asdf,
       qwer OVER (PARTITION BY asdf,
                               qwer
                  ORDER BY qwerty)
FROM   table1

Too long lines query

assert_and_print(
format_sql("""
create or replace table asdf as
select asdf, 
case when asdf in (123412341234, 12341234123412, 123412341234, 512351235132, 123412341, 1234) then 1 else 0 end as qwerty,
qwer over (
partition by asdf, qwer order by qwerty
)
from table1
"""),
"""
CREATE OR REPLACE TABLE asdf AS
SELECT asdf,
       case when asdf in (123412341234, 12341234123412, 123412341234, 512351235132, 123412341,
                          1234) then 1
            else 0 end as qwerty,
       qwer OVER (PARTITION BY asdf,
                               qwer
                  ORDER BY qwerty)
FROM   table1
""".strip()
)

CREATE OR REPLACE TABLE asdf AS
SELECT asdf,
       case when asdf in (123412341234, 12341234123412, 123412341234, 512351235132, 123412341,
                          1234) then 1
            else 0 end as qwerty,
       qwer OVER (PARTITION BY asdf,
                               qwer
                  ORDER BY qwerty)
FROM   table1