Multiple join seems buggy

[eitsupi]

Sorry if this has already been reported.
Queries containing multiple joins do not seem to translate well to SQL?

In the following example, I think it need to use WITH one more time.

from tbl_a
group [id, col_a] (
  sort col_a
  take 1
)
select [
  id_a = id,
  col_b
]
join side:left tbl_b id_a
select [
  id_b = id,
  id_a,
  col_b
]
join side:left tbl_c id_b
select [
  id_a,
  id_b,
  col_b,
  col_c
]

WITH table_0 AS (
  SELECT
    tbl_a.*,
    ROW_NUMBER() OVER (
      PARTITION BY id,
      col_a
      ORDER BY
        col_a
    ) AS _rn_88
  FROM
    tbl_a
),
table_1 AS (
  SELECT
    id AS id_a,
    col_b
  FROM
    table_0
  WHERE
    _rn_88 <= 1
)
SELECT
  id_a,
  id_b,
  table_1.col_b,
  tbl_c.col_c
FROM
  table_1
  LEFT JOIN tbl_b USING(id_a)
  LEFT JOIN tbl_c USING(id_b)

[max-sixty]

Thanks for the issue @eitsupi

There are a couple of issues on this that we're working through, as part of a broader refactor. #820 is an example.

It's useful to have additional examples. Could you show the query you think it should produce? I wasn't sure what "use WITH one more time" referred to. Thanks!

[aljazerzen]

I don't think this was reported already.

It seems that the problem is that id_a should be in a SELECT preceeding the SELECT with JOIN. Is this right?

If so, this will probably be solved by #593.

[eitsupi]

Thank you for your reply and sorry for the lack of explanation.
I was assuming the following SQL.

WITH table_0 AS (
  SELECT
    tbl_a.*,
    ROW_NUMBER() OVER (
      PARTITION BY id,
      col_a
      ORDER BY
        col_a
    ) AS _rn_88
  FROM
    tbl_a
),
table_1 AS (
  SELECT
    id AS id_a,
    col_b
  FROM
    table_0
  WHERE
    _rn_88 <= 1
),
table_2 AS (
  SELECT
    id AS id_b,
    id_a,
    col_b
  FROM
    table_1
    LEFT JOIN tbl_b USING(id_a)
),
SELECT
  id_a,
  id_b,
  col_b,
  col_c
FROM
  table_2
  LEFT JOIN tbl_c USING(id_b)

I think the generated SQL's last join will fail because the left table does not have id_b.

Sorry the column names are confusing, but the purpose is to join multiple tables by traversing foreign keys.

[aljazerzen]

Yes, this is a bug. I don't think it a problem with multiple joins, but how USING is implemented. Here is a minimal example:

from tbl_a
select id_b = id
join tbl_b id_b

I'm fairly certain that #593 indeed solves this, let's revisit this issue when it merges in a few weeks.

[aljazerzen]

This has indeed been solved:

from tbl_a
select id_b = id
join tbl_b [~id_b]

SELECT
  tbl_b.*,
  tbl_a.id AS id_b
FROM
  tbl_a
  JOIN tbl_b ON tbl_a.id = tbl_b.id_b