Repository navigation
[BUG] JSQLParser 5.1 : SQL-99 : unnest(...) with ordinality #2218
Description
Failing SQL Feature:
- The unnest function which can have an optional
with ordinalityqualifier, 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)
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
davyboyhayes commentedon Apr 7, 2025
I would be happy to submit a PR, the changes to
TableFunction.javawould be simple enough, adding an optionalString clauseto it. Where I am less certain is how to change theJSqlParser.jjtfile to modify the TableFunction part to take in potentially a multi token clause. Or should it be a case of declaring awithClauseand 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)Add support for TableFunction [WITH OFFSET|ORDINALITY]
Add support for TableFunction [WITH OFFSET|ORDINALITY] (#2219)