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

Implicit column alias for single-column VALUES, and UNNEST of ARRAY and MULTISET constructors

    XMLWordPrintableJSON

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 1.27.0
    • Component/s: None
    • Labels:
      None

      Description

      Single-column UNNEST with a single alias should assign that alias to both a table and the unique column. For example, PostgreSQL returns a column called 'unnest' in the first, and 'fruit' in all of the rest:

      select * from UNNEST(array ['apple', 'banana', 'pear']);
      select * from UNNEST(array ['apple', 'banana', 'pear']) as fruit;
      select * from UNNEST(array ['apple', 'banana', 'pear']) as t(fruit);
      select t.* from UNNEST(array ['apple', 'banana', 'pear']) as t(fruit);
      select fruit.* from UNNEST(array ['apple', 'banana', 'pear']) as fruit;
      

      Thus FROM UNNEST(...) AS x is creating a table alias x and a column alias x.

      This only happens for UNNEST. When aliasing other single-column relations the column name is retained, such as a SELECT-FROM-UNNEST sub-query as follows:

      SELECT fruit.*
      FROM (SELECT * FROM UNNEST(array ['apple', 'banana', 'pear']) as x) as fruit;
      
      x
      ======
      apple
      banana
      pear
      

      BigQuery has similar behavior.

      As an extension to PostgreSQL and BigQuery, we also derive a column alias for UNNEST of single-column MULTISET and single-column VALUES. Examples:

      SELECT f FROM UNNEST(MULTISET ['apple', 'banana', 'pear']) AS f;
      
      SELECT f FROM (VALUES 'apple', 'banana', 'pear') AS f;
      

        Attachments

          Activity

            People

            • Assignee:
              julianhyde Julian Hyde
              Reporter:
              julianhyde Julian Hyde
            • Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: