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.

贡献者指南