Description

davyboyhayes

opened on Apr 7, 2025

Issue body actions

Failing SQL Feature:

• The unnest function which can have an optional with ordinality qualifier, is an unsupported statement when the qualifier is supplied.

SQL Example:

select * from unnest(array[4,5,6]) with ordinality;

Software Information:

• JSqlParser 5.1
• Database (e. g. SQL-99 )

It is part of the SQL-99 spec, although this is parsed into an UnsupportedStatement, and once in an unsupported statement, the statement can't be parsed and tree walked using JSQLParser.

https://www.iso.org/obp/ui/en/#iso:std:iso-iec:19075:-8:ed-1:v1:en (5.6.2)

http://jsqlformatter.manticore-projects.com/jsqlformatter/demo.html?args=-c%20M4UwNiDGAuAEBUsBmAnA9gW1gVwHa5GGgAoBDFFUgTwG0AWAGgFYGA2AXQEpYB3AS2gALWGhQATPrlJgBVANxA$$

Subclause 7.6, "<table reference>":
  
 <collection derived table> ::=
 UNNEST <left paren> <collection value expression>
 [ { <comma> <collection value expression> }... ] <right paren>
 [ WITH ORDINALITY ]

This is present in a number of Databases:
H2 - https://www.h2database.com/html/functions.html#unnest
HSQLDB - https://hsqldb.org/doc/guide/sqlgeneral-chapt.html (grep for unnest)
Postgres - https://www.postgresql.org/docs/current/queries-table-expressions.html#QUERIES-TABLEFUNCTIONS although this declares the with ordinality to be a property of a Table Function, which probably makes more sense in a general sense
DB2 - https://www.ibm.com/docs/en/db2/11.1.0?topic=functions-unnest
GoogleSQL - https://cloud.google.com/bigquery/docs/arrays#flattening_arrays (although they have with offset)

SQL Server doesn't support this - https://learn.microsoft.com/en-us/openspecs/sql_standards/ms-tsqliso02/698d73c4-1410-4a75-affe-cef7323bb0d8

Activity

1. davyboyhayes commented on Apr 7, 2025

   

   davyboyhayes

   on Apr 7, 2025

   ContributorAuthor

   More actions

   I would be happy to submit a PR, the changes to TableFunction.java would be simple enough, adding an optional String clause to it. Where I am less certain is how to change the JSqlParser.jjt file to modify the TableFunction part to take in potentially a multi token clause. Or should it be a case of declaring a withClause and only allowing a singular word to be present after WITH in a TableFunction (which again, I am less certain on how to achieve in the jjt file)
2. davyboyhayes

   added a commit that references this issue on Apr 7, 2025

   Add support for TableFunction [WITH OFFSET|ORDINALITY]

   This issue will close once commit 348e82f is merged into the 'master' branch.

   348e82f
3. manticore-projects

   closed this as completedin #2219on Apr 8, 2025
4. manticore-projects

   added a commit that references this issue on Apr 8, 2025

   Add support for TableFunction [WITH OFFSET|ORDINALITY] (#2219)

   This issue will close once commit e5fa004 is merged into the 'master' branch.

   e5fa004

Issue actions