Scoping Question

[Homer]

Hi,

When using calcite to generate a plan I am trying to understand scope of 
the $n variables

I have a simple table

tab 3 table is a super simple table

i1 integer
i2 integer
t3 text
t4 text

if I do

select i1 as fred from tab3;

I get

LogicalProject(fred=[$0])
  LogicalTableScan(table=[[CATALOG, mapd, tab3]])

if I look at my catalog layout I see that i1 is the first column in the 
catalog for this table thus the $0. make sense.

If I give calcite a kind of silly query for testing and trying to 
understand the scoping of the result it expects me to execute it gets a 
little more confusing

select count(*) from tab3 as alias1 where (select max(alias2.i1) from 
tab3 alias2, tab3 as alias3 where alias2.i1 = alias3.i1) > alias1.i1;

the execution plan I get back looks like this:

LogicalAggregate(group=[{}], fred=[COUNT()])
  LogicalProject($f0=[0])
    LogicalFilter(condition=[>($5, $0)])
      LogicalJoin(condition=[true], joinType=[left])
        LogicalTableScan(table=[[CATALOG, mapd, tab3]])
        LogicalAggregate(group=[{}], EXPR$0=[MAX($0)])
          LogicalProject(i1=[$0])
            LogicalFilter(condition=[=($0, $5)])
              LogicalJoin(condition=[true], joinType=[inner])
                LogicalTableScan(table=[[CATALOG, mapd, tab3]])
                LogicalTableScan(table=[[CATALOG, mapd, tab3]])

My question is about understanding the mapping and scoping of the $n 
variables in the plan

The plan uses $0 and $5 in various different scopes here, initially in 
this part

          LogicalProject(i1=[$0])
            LogicalFilter(condition=[=($0, $5)])
              LogicalJoin(condition=[true], joinType=[inner])
                LogicalTableScan(table=[[CATALOG, mapd, tab3]])
                LogicalTableScan(table=[[CATALOG, mapd, tab3]])

I understand it as the two scans are of the same table, so $0 for 
alias2.i1 $5 for the second table alias3.i1.

we then move into my shakey ground

LogicalAggregate(group=[{}], fred=[COUNT()])
  LogicalProject($f0=[0])
    LogicalFilter(condition=[>($5, $0)])
      LogicalJoin(condition=[true], joinType=[left])
        LogicalTableScan(table=[[CATALOG, mapd, tab3]])
        LogicalAggregate(group=[{}], EXPR$0=[MAX($0)])


We see another join here referring to $5 and $0 but these are different 
$5 and $0.  In this scope I think $5 is referring to the MAX() 
projection from below and $0 is referring to alias1.i1

I am wondering what is the best way to consider the scoping of these $n 
placeholders.  What is the best way to think of this model?  At what 
point does $0 and $5 move to the new meaning?

thanks
Homer