Uploaded image for project: 'Calcite'
  1. Calcite
  2. CALCITE-4033

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

    XMLWordPrintableJSON

    Details

    • 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
    • Labels:
      None

      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.

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                amaliujia Rui Wang
                Reporter:
                amaliujia Rui Wang
              • Votes:
                0 Vote for this issue
                Watchers:
                5 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved:

                  Time Tracking

                  Estimated:
                  Original Estimate - Not Specified
                  Not Specified
                  Remaining:
                  Remaining Estimate - 0h
                  0h
                  Logged:
                  Time Spent - 1h 20m
                  1h 20m