[CALCITE-4033] Unparser should not apply parentheses for UNNEST (as a built-in table operator)

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 1.22.0, 1.23.0
• Fix Version/s: 1.24.0
• Component/s: core
• Assignee: Rui Wang
• Reporter: Rui Wang
• Created: 30/May/20 19:52
• Updated: 25/Jul/20 14:59
• Resolved: 01/Jul/20 02:34

Description

Reported from: https://issues.apache.org/jira/browse/DRILL-7722

// Updated based on Julian's comment.

If write the following test in Calcite,

  @Test void testUnnestWithoutLateral() {
    final String sql1 = ""
        + "SELECT\n"
        + "  item.name,\n"
        + "  relations.*\n"
        + "FROM dfs.tmp item\n"
        + "JOIN (\n"
        + "  SELECT * FROM UNNEST(item.related) i(rels)\n"
        + ") relations\n"
        + "ON TRUE";
    final String expected = "SELECT `ITEM`.`NAME`, `RELATIONS`.*\n"
        + "FROM `DFS`.`TMP` AS `ITEM`\n"
        + "INNER JOIN (SELECT *\n"
        + "FROM (UNNEST(`ITEM`.`RELATED`)) AS `I` (`RELS`)) AS `RELATIONS` ON TRUE";
    sql(sql1).ok(expected);
  }

The expected SQL query (thus the unparsed one) is

SELECT `ITEM`.`NAME`, `RELATIONS`.*
    FROM `DFS`.`TMP` AS `ITEM`
    INNER JOIN (SELECT *
    FROM (UNNEST(`ITEM`.`RELATED`)) AS `I` (`RELS`)) AS `RELATIONS` ON TRUE");

Note that there are extra parentheses around the UNNEST. And then the generated query won't pass Calcite's parser because the parentheses around the UNNEST.

Issue Links

• relates to: DRILL-7722 CREATE VIEW with LATERAL UNNEST creates an invalid view
• links to: Github Pull Request #2025