Support for lateral view explode clause

abecus commented 3 years ago

getting error when trying to get the tables for sql below,

sql = """
WITH uisd_filter_table as (
    select
        session_id,
        srch_id,
        srch_ci,
        srch_co,
        srch_los,
        srch_sort_type,
        impr_list
    from
        uisd
    where
        datem <= date_sub(date_add(current_date(), 92), 7 * 52)
        and lower(srch_sort_type) in ('expertpicks', 'recommended')
        and srch_ci <= date_sub(date_add(current_date(), 92), 7 * 52)
        and srch_co >= date_sub(date_add(current_date(), 1), 7 * 52)
)
select
    DISTINCT session_id,
    srch_id,
    srch_ci,
    srch_co,
    srch_los,
    srch_sort_type,
    l.impr_property_id as expe_property_id,
    l.impr_position_across_pages
from
    uisd_filter_table lateral view explode(impr_list) table as l
"""

How to reproduce the error:

p = Parser(sql)
print(p.tables)

Error:

Traceback (most recent call last):
  File "/Users/asalam/vscode/new/source_analyser/utils.py", line 152, in <module>
    print(p.tables)
  File "/Users/asalam/Library/Python/3.8/lib/python/site-packages/sql_metadata/parser.py", line 398, in tables
    with_names = self.with_names
  File "/Users/asalam/Library/Python/3.8/lib/python/site-packages/sql_metadata/parser.py", line 544, in with_names
    if token.next_token.normalized in WITH_ENDING_KEYWORDS:
AttributeError: 'NoneType' object has no attribute 'normalized'

collerek commented 3 years ago

@macbre I need your opinion on this, we do not support lateral view explode keyword as of now, the question is if we should?

https://spark.apache.org/docs/latest/sql-ref-syntax-qry-select-lateral-view.html

in a part lateral view explode(impr_list) table as l :

the impr_list is now recognized as a table (and this actually is a column) #bug even after #162
table is an alias not an actual table so should NOT go into tables
in theory l is a column_alias but when you i.e. explode multiple arrays you can have multiple ones -> https://dwgeek.com/what-is-hive-lateral-view-and-how-to-use-it.html/ but they do not have to refer to actual db column

If i'm not mistaken this only refers to hive/spark sql dialect.

What should we do with this?

macbre commented 3 years ago

Tricky one. Then comes the question if we should for instance support PIVOT as well?

Let's deal with this one in a separate PR / GitHub discussions thread.

collerek commented 3 years ago

Created a discussion: https://github.com/macbre/sql-metadata/discussions/166

collerek commented 3 years ago

@macbre - I think we should either close this one and open a new one with enhancement for lateral view, or rename this one and change the labels, what do you think?

collerek commented 3 years ago

@abecus The original bug was resolved but the tables still will return wrong data as the lateral view explode is not yet supported. Therefore I changed the name and label of this issue.

macbre / sql-metadata

Support for lateral view explode clause #161