mozilla/moz-sql-parser

New failing test cases from a Redshift user (part 1)

Closed this issue · 5 comments

louisguitton commented

I've just found out about this great library, I read the tests and started using it on my team's codebase of 400 2800 SQL queries (Data Warehouse models that were developed for Redshift and are all valid and in production right now).

I've tried to parse 40 of them and ran into issues that I summarise here as minimal test cases just for documentation at first. There is potentially room for more corner cases to be found in the rest of our SQL codebase, but at this stage the following issues are coming back a lot so I'll stop here for now. They are ordered from the most queries impacted to the least.

I'd be very interested in support for these cases myself, and potentially others users are too?

casting

cast works but not ::

parse("select '' :: varchar as placeholder from table")

Ref: https://docs.aws.amazon.com/redshift/latest/dg/r_CAST_function.html#r_CAST_function-examples

convert epoch to datetime

parse("select timestamp 'epoch' + your_timestamp_column * interval '1 second' AS your_column_alias from your_table")

Ref: https://stackoverflow.com/questions/39815425/how-to-convert-epoch-to-datetime-redshift

window functions

parse("""
select
  listagg(sellerid) within group (order by sellerid) over()
from winsales;
""")

Ref:

Simple case expression vs Searched case expression

"searched" case works

parse("""
select
    CASE
        WHEN quantity > 30 THEN 'The quantity is greater than 30'
        WHEN quantity = 30 THEN 'The quantity is 30'
        ELSE 'The quantity is under 30'
    END AS quantitytext
from
    source
""")

But "simple" case doesn't work

parse("""
select 
    CASE quantity
        WHEN 30 THEN 'The quantity is 30'
        WHEN 31 THEN 'The quantity is 31'
        ELSE 'The quantity is not 30 or 31'
    END AS quantitytext
from
    source
""")

Ref:

more than 2 union

a union b works, but a union b union c doesn't.

parse("select * from a union all select * from b union all select * from c")
klahnakoski commented

thank you for this! I was hoping to use my holidays for some fun programming. I will get back to you in a day or two.

  • 🚀1
klahnakoski commented

work will proceed here: klahnakoski#4

klahnakoski commented

@louisguitton that went faster than I thought. Thank you for the detailed examples.

I do not have permissions to this repo, so please close this issue if you consider it done. If you have any more examples, please open an issue on my fork: https://github.com/klahnakoski/moz-sql-parser/issues

Happy New Year!

louisguitton commented

Wow, great job @klahnakoski 🚀
I've parsed our 2800 queries programmatically before and after the upgrade.

Before: 7.0357 % failure
After:  5.7142 % failure

I will hunt down more corner cases, and open another issue on the fork.