search

andialbrecht / sqlparse

A non-validating SQL parser module for Python
BSD 3-Clause "New" or "Revised" License
3.76k stars 700 forks source link

extract(date from ...) formatter bug #670

Closed pblankley closed 2 years ago

pblankley commented 2 years ago

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 commented 2 years ago

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

Let me know!

wbmcdonald4 commented 2 years ago

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 commented 2 years ago

having a similar issue as above ^

andialbrecht commented 2 years ago

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.