Column not found in correlated subquery, when referencing outer column from LEFT JOIN .. ON clause

[lukaseder]

I'm using H2 2.2.224

This query works:

with
  a(a) as (values (1)),
  b(b) as (values (1)),
  c(c) as (values (1))
select 
  a.a,
  (
    select b.b
    from b
    join c
    on b.b = a.a
    and c.c = b.b
  )
from a;

This one doesn't

with
  a(a) as (values (1)),
  b(b) as (values (1)),
  c(c) as (values (1))
select 
  a.a,
  (
    select b.b
    from b
    left join c -- Difference here
    on b.b = a.a
    and c.c = b.b
  )
from a;

The error is:

Column "A.A" not found; SQL statement:

I don't think there's a good reason for A.A being out of scope at that location?

[grandinj]

In general, the entirety of the WITH functionality is dodgy and needs to be rewritten, but no-one currently feels up to that.

[lukaseder]

OK, sure, but it's not related to WITH:

create table a (a int);
create table b (b int);
create table c (c int);

select 
  a.a,
  (
    select b.b
    from b
    left join c
    on b.b = a.a
    and c.c = b.b
  )
from a;

[manticore-projects]

This is still relevant after @katzyn's latest work on WITH and CTE, I have just re-tested it against latest GIT 4497462

[manticore-projects]

Could be a duplicate of #3716