Format Query within Column List

[kfordaccela]

Describe the bug

When formatting a query that has a query within the column list

To Reproduce

python
.from sql_formatter.core import format_sql

print( format_sql( "SELECT Column1, Column2, (SELECT column3 from SubTable) Column 4 FROM TABLE" ) )

output:
SELECT_column1,
_______column2,
_______(SELECT_column3
_FROM___subtable)_column4
FROM___table

Expected behavior

SELECT
_____column1,
_____column2,
_____(
__________SELECT
_______________column3
__________FROM__subtable
_____)_column4
FROM table

Screenshots

Sorry for "_" but the 'code' in the editor was dropping formatting.

[PabloRMira]

Hi @kfordaccela , actually I'm still wondering if such a query is valid in the first place. What SQL dialect are you using?

What if subtable has more rows than table?

As a workaround I would propose you the more classical way with a join, e.g.

SELECT a.column1, 
       a.column2, 
       b.column3
FROM table as a
    LEFT JOIN subtable as b
        ON a.column1 = b.column1

[kfordaccela]

I have been writing SQL for a number of years and recognize the fact that it would have been better to use a LEFT JOIN, however often in the use case that I provided the result would be a sum or an average that would have required a grouping or a partition by that the original coder wasn't in favor of for some reason so they did it as a sub-query within the list of columns to be pulled into the report.

I have been playing also with the following as a comparison, which does do the formatting as I suggested above.
https://zeroturnaround.github.io/sql-formatter/

The whole reason for carefully formatting the code for me is the fact that I'm using it to parse out the tables and columns that are used within the query so that I can create a list for further work within python. When formatted properly I'm able to find the table aliases much faster.