extract(date from ...) formatter bug

[pblankley]

When I try to use the formatter on the following SQL (valid in BigQuery)

select  extract(date from cast(created_at as timestamp) at time zone 'America/New_York') as created_at from prod.orders

I get this result (which is invalid due to the fromcast string)

select extract(date
               fromcast(created_at as timestamp) at time zone 'America/New_York') as created_at
from prod.orders

Code to reproduce this:

import sqlparse 

test_sql = "select  extract(date from cast(created_at as timestamp) at time zone 'America/New_York') as created_at from prod.orders"

sqlparse.format(test_sql, reindent=True)

Thanks for taking a look at this!

[pblankley]

Any thoughts on this? I can also look into opening a PR for this if it could be accepted.

Let me know!

[wbmcdonald4]

I am also experiencing an issue with the CAST keyword getting pulled into the identifier.

For example, when i use the .get_real_name function on the following identifiers, it returns CAST, when it should return the original column name which is getting aliased.

As a result I've had to write custom regex to extract what I am looking for. Couldn't find anything online for how people have addressed parsing out columns which are getting typecasted.

Also it might be worth it to note that I am using BigQuery, so it only allows for conversion functions CAST rather than the two colon :: format for typecasting.

CAST(numberofemployees AS INT64) AS employee_count CAST(expectedrevenue*100 AS INT64) AS expected_revenue CAST(DATE(closedate) AS DATE) AS close_date

[Arash-Akh]

having a similar issue as above ^

[andialbrecht]

The formatting issue as described by the author of this issue was resolved with #563.

The get_real_name issue is a totally different one. @wbmcdonald4 would you mind opening a separate issue for that. At the moment that's not easy to resolve without some kind of guessing. And I try to avoid guessing. It's definitely not something that will go into the 0.4.3 release.

Closing this issue as the issue described originally is resolved.