[BUG] 4.5: SNOWFLAKE : Lateral flatten failing parsing

[anilpuliyeril]

Always check against the Latest SNAPSHOT of JSQLParser and the Syntax Diagram

Failing SQL Feature:

• Snowflake LATERAL FLATTEN function fails
• LATERAL FLATTEN net.sf.jsqlparser.JSQLParserException: Encountered unexpected token: "FLATTEN" "FLATTEN"

SQL Example:

WITH t AS (
  SELECT 
    'ABC' AS dim, 
    ARRAY_CONSTRUCT('item1', 'item2', 'item3') AS user_items
)
SELECT DIM, count(value) as COUNT_
FROM t a,
LATERAL FLATTEN(input => a.user_items) b
group by 1

Software Information:

• JSqlParser version 4.5, 4.6, 4.7-SNAPSHOT
• Database SNOWFLAKE

Tips:

Lateral flatten is similar to explode used to flatten a json structure. https://docs.snowflake.com/en/sql-reference/functions/flatten

http://jsqlformatter.manticore-projects.com/jsqlformatter/demo.html?args=-c%20OoSQKgEgBALlCCBlKAKAUFKiCiAZbAwmFBplAOTwBCB5CyAJgJYC2ANCWQgErfwCaAfQIB5AHKIw3AKpEU5JjACmLAIzkOC5SwBMGiopUBmcgEp6UAK4BnJQCdBhltbSm0OfESgAREAFkOAGMAe0sAOxgUADcAQwAbSyVzGOsoUWkxMEE0ADFuET9YKBi2NFx4MGw+XCgc8rBKsRQmMIAHSzgAXgA+YoA6G3tHbWtzACM0AHM7UNaoMYBPKFUgA

[manticore-projects]

Greetings,

only LATERAL subquery is supported: http://217.160.215.75:8080/jsqlformatter/JSQLParser/syntax_snapshot.html#lateralview although this certainly can be changed into a LATERAL expression which would cover Function and Select objects.

[manticore-projects]

This one works already

-- subquery
select * from lateral (select 1)

-- expression
select * from lateral (select * from flatten(input => a.user_items))

[anilpuliyeril]

Thanks that worked. Do you think adding support for lateral flatten would be done in the future? I can try looking through the code to see if i can create a PR for the same.

[manticore-projects]

Certainly today in the afternoon.

Changing Query to Expression is a minor thing. I was not aware that LATERAL works with both Query and Function. The documentation is never really verbose.

[anilpuliyeril]

Hi @manticore-projects were you able to add it in? Is there a PR i can probably use for my local testing? Thanks in advance

[anilpuliyeril]

@manticore-projects quickly checking again if this is still being planned

[manticore-projects]

Thank you for the reminders and sorry for taking that long. Cheers!

[anilpuliyeril]

Thanks a lot 🙏