RE: RelToSqlConverter doesn't work on UNNEST?

[mbudiu]

I can't answer to this particular question, but I have been working on a 
Calcite test fixture (a tool which helps run many tests) for RelToSqlConverter 
and I found tens of bugs, which I haven't had yet time to file as issues. So 
it's very possible that this is a genuine bug.

Mihai

-----Original Message-----
From: Lino Rosa <l...@narrative.io.INVALID> 
Sent: Friday, September 22, 2023 3:06 PM
To: dev@calcite.apache.org
Subject: RelToSqlConverter doesn't work on UNNEST?

Hi,

I'm ultimately trying to write a query in calcite with an UNNEST that gets 
translated to spark sql. But the resulting query is invalid.

So starting with something like the query below, which runs fine as is on
Postgres:

SELECT t.test_name, res
FROM test_table as t,
UNNEST(t.test_array) as res

After converting to logical plan and passing it through RelToSqlConverter I get 
this, which is invalid sparkSQL:

SELECT
  `$cor0`.`test_name`,
  `$cor0`.`test_array0` `res`
FROM
  test_table `$cor0`,
  LATERAL UNNEST (SELECT
      `$cor0`.`test_array`
    FROM
      (VALUES
          (0)) `t` (`ZERO`)) `t0` (`test_array`) `t00`

with the accompanying logical plan:

[Logical plan]
LogicalProject(test_name=[$1], res=[$3])
  LogicalCorrelate(correlation=[$cor0], joinType=[inner],
requiredColumns=[{2}])
    LogicalTableScan(table=[[test_table]])
    Uncollect
      LogicalProject(test_array=[$cor0.test_array])
        LogicalValues(tuples=[[{ 0 }]])

A result for a Postgres dialect has similar results (just exchanges ` for ").

In order to isolate whether my environment is the culprit, I've cloned 
Calcite's codebase and hijacked a test under RelToSqlConverterTest:

final String sql = "select did from \"department\", unnest(array[1, 2, 3]) as 
t(did)\n"; final String result = 
sql(sql).dialect(DatabaseProduct.POSTGRESQL.getDialect()).exec();

What I get as result is the below, which is invalid posgresql:

SELECT t1.DID
FROM foodmart.department,
(
  SELECT col_0 AS DID
  FROM UNNEST (
    SELECT ARRAY[1, 2, 3] FROM (VALUES (0)) AS t (ZERO)
  ) AS t0 (col_0)
) AS t1

I'm fairly new to Calcite, so it's very possible this is user error. There are 
currently two tests under RelToSqlConverterTest that cover UNNEST, but they 
both do `from unnest(select collect...`, so I wonder if UNNEST only works in 
that scenario?