Field from outer query not in scope in correlated subquery

[scharris]

I'm having trouble with the query below, where the reference to "d.ID" in the inner query's "where" condition results in "column D.ID not found". I think d.ID should be in scope here, and similar queries work fine in other databases like oracle and postgres. Replacing that reference with a constant, the query would be accepted.

select
  d.ID "id",
  (
    select
      json_arrayagg(json_object('partType' value q."partType", 'description' value q."description")) json
    from (
      select dp.PART_TYPE "partType", dp.DESCRIPTION "description"
      from DOC_PART dp
      where dp.DOC_ID = d.ID -- <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< 'Column "D.ID" not found'
    ) q
  ) "parts"
from DOC d

Schema:

create table doc (
  id int
);
create table doc_part (
  doc_id int,
  part_type varchar(1),
  description varchar(2000)
);

[grandinj]

I suspect our field resolution is not great when the query is inside the select.

If you need a workaround, you could try putting it inside the FROM, which we might handle better.

[manticore-projects]

Could be a duplicate of #3945.
It is still relevant with latest GIT 4497462, I have tested it.