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

RelToSqlConverter should generate double parentheses when the input to Unnest is a query statement

    XMLWordPrintableJSON

Details

    • Bug
    • Status: Closed
    • Major
    • Resolution: Fixed
    • None
    • 1.39.0
    • None

    Description

      ReloSqlConverter should generate double parentheses when the input to Unnest is a query statement.

      For unit tests in RelToSqlConverterTest:

      @Test void testUnnest() {
        final String sql = "select * from UNNEST(array [1, 2, 3])";
        final String expected = "SELECT *\n" +
            "FROM UNNEST (SELECT ARRAY[1, 2, 3]\n" +
            "FROM (VALUES (0)) AS \"t\" (\"ZERO\")) AS \"t0\" (\"col_0\")";
        sql(sql).ok(expected).withPostgresql().ok(expected);
      }

      The generated SQL is valid in CALCITE but not in PG.

      In PostgreSQL:

      CREATE TABLE IF NOT EXISTS public."unnestTable"
      (
          array_int integer[],
          "valueInt" integer
      );
      SELECT "valueInt","t10"."col_0" FROM public."unnestTable",unnest(( SELECT "array_int" FROM (VALUES (0)) AS "t" ("ZERO")))  AS "t10" ("col_0");
      
      select * from unnest((SELECT "array_int" from  public."unnestTable"))  as "t" ("ZERO");
      
      select * from unnest((SELECT ARRAY_AGG("valueInt") from  public."unnestTable"))  as "t" ("ZERO");
      
      select * from unnest((SELECT ARRAY_AGG("valueInt") from  public."unnestTable"))  WITH ORDINALITY AS  "t" ("ZERO","ZERO1");

      The PG always needs double parentheses.

      The Calcite supports double parentheses too.

      Attachments

        Issue Links

          Activity

            People

              nobigo xiong duan
              nobigo xiong duan
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved: