[CALCITE-6231] JDBC adapter generates "UNNEST" when it should generate "UNNEST ... WITH ORDINALITY"

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 1.37.0
• Labels: pull-request-available
• Assignee: EveyWu
• Reporter: EveyWu
• Due: 28/Jan/24
• Created: 27/Jan/24 07:50
• Updated: 06/May/24 22:19
• Resolved: 31/Jan/24 07:16

Description

The syntax UNNEST() WITH ORDINALITY is missing the ORDINALITY keyword.

For example sql：

select did + 1 from 
unnest(select collect("department_id") as deptid from "department") 
with ordinality as t(did, pos)

 
current planned sql：

SELECT DEPTID + 1 FROM UNNEST (
SELECT COLLECT("department_id") AS "DEPTID" FROM "foodmart"."department") 
AS "t0" ("DEPTID", "ORDINALITY") 

 
fixed planned sql：

SELECT "DEPTID" + 1 FROM UNNEST (
SELECT COLLECT("department_id") AS "DEPTID" FROM "foodmart"."department") 
WITH ORDINALITY AS "t0" ("DEPTID", "ORDINALITY") 

 

Issue Links

• links to: GitHub Pull Request #3654