The problem might be how Postgres handles table functions in the SELECT clause. They have a special behavior that if the function returns N rows then multiple rows are emitted. Effectively the flatMap operator (aka SelectMany).
This behavior is non-standard and Calcite doesn’t support it. We could. I don’t recall whether there is a jira. Julian > On Sep 23, 2025, at 11:54, Mihai Budiu <[email protected]> wrote: > > UNNEST has a surprising behavior when the argument is an array of ROW > values: it will actually expand each element of the ROW into a separate > column. pg_expandarray doesn't do that, it produces a single column with a > ROW. That's why you can use the dot syntax to access a field. > > UNNEST(ARRAY[ROW(2, 3), ROW(3, 4)] WITH ORDINALITY produces as result a table > with 3 columns, the last being the ordinality: > > 2 | 3 | 1 > 3 | 4 | 2 > > However, the postgres function would produce a table with 2 columns, the last > being the ordinality: > > ROW(2, 3) | 1 > ROW(3, 4) | 2 > > You can name the columns produced by UNNEST using this syntax: > > UNNEST(array) WITH ORDINALITY AS R(c0, c1, o) > > This will produce a table with three named columns: > > c0 | c1 | o > > And then you can refer to the field you need by name. > > So you should be able to emulate this function if you rewrite your programs > manually. > > I am pretty sure that with some work you can convince Calcite to accept this > function using the postgres syntax. I think the trick is to parse the program > using the function as a normal function call (no changes needed in the > parser), then rewrite the SqlNode IR by replacing this RexCall with a call to > the SqlUnnestOperator, before validating the program. > > Mihai > > ________________________________ > From: Maxime Sechehaye <[email protected]> > Sent: Tuesday, September 23, 2025 12:51 AM > To: [email protected] <[email protected]> > Subject: Re: Support for field access on a table expression > > Hello Mihai, > > Thank you for your quick response! I tried redirecting the _pg_expandarray > operator to UNNEST WITH ORDINALITY, but it didn’t work—particularly when > used with the dot operator. > > Here’s what I attempted: > > - > > Creating a custom parsing rule: didn’t work because Calcite’s default > parser handles it first > - > > Manually rewriting the query to replace _pg_expandarray with UNNEST > - > > Implementing a custom SqlUserDefinedTableFunction _pg_expandarray whose > implementation is UNNEST > > At the moment, I can’t think of any other solutions. > > Best regards, > Maxime > >>
