trinodb/trino

Make UNNEST with JOIN clearer in the documentation.

Open

#17,897 建立於 2023年6月14日

在 GitHub 查看
 (10 留言) (0 反應) (2 負責人)Java (9,113 star) (2,678 fork)batch import
docsgood first issue

描述

Taken from this awesome example from @kasiafi, I was initially confused when I ran the following query, I got the error message, Column 't2.y' cannot be resolved:

WITH
  t (x, y) AS (
    VALUES
      (1, ARRAY['a', 'b', 'c'])
  ),
  t2 (x, y) AS (
    VALUES
      (1, ARRAY['a', 'b', 'c'])
  ),
  t3 (x, y) AS (
    SELECT
      x,
      y_unnested
    FROM
      t
      CROSS JOIN UNNEST (t2.y) t4 (y_unnested)
  )
SELECT
  *
FROM
  t3;

Eventually I figured out that t3 query's FROM statement I was joining between two different tables. UNNEST (according to the docs) references relations from the left side of the join (which apparently means the tables in the join need to be the same). So I needed to be joining t2 on the left side with UNNEST(t2.y) rather than t on the left side.

  t3 (x, y) AS (
    SELECT
      x,
      y_unnested
    FROM
      t2
      CROSS JOIN UNNEST (t2.y) t4 (y_unnested)
  )

We should add a cleaned up example of this and make that requirement clearer.

貢獻者指南