`scope.columns()` does not collect column references from JOIN ON clauses

[karakanb]

build_scope() followed by scope.columns() does not return column references from JOIN ON clauses. Only columns in SELECT, WHERE, HAVING, ORDER BY, and GROUP BY are collected.

sqlglot repro

# /// script
# requires-python = ">=3.9"
# dependencies = ["sqlglot>=26.0"]
# ///
from sqlglot import exp
from sqlglot.optimizer.scope import build_scope

query = "SELECT o.total FROM orders o JOIN customers c ON c.id = o.customer_id"
print(f"Query: {query}\n")

parsed = exp.maybe_parse(query, dialect="bigquery")
root = build_scope(parsed)
all_cols = [f"{col.table}.{col.name}" if col.table else col.name for col in root.columns]

print(f"scope.columns: {all_cols}")
print(f"expected:      ['o.total', 'c.id', 'o.customer_id']")

polyglot repro

/// scope.columns() does not collect column references from JOIN ON clauses.
use polyglot_sql::{self as pgsql, build_scope, DialectType};

fn main() {
    let query = "SELECT o.total FROM orders o JOIN customers c ON c.id = o.customer_id";
    println!("Query: {query}\n");

    let exprs = pgsql::parse(query, DialectType::BigQuery).unwrap();
    let mut scope = build_scope(&exprs[0]);
    let all_cols: Vec<String> = scope.columns().iter().map(|c| {
        match &c.table {
            Some(t) => format!("{}.{}", t, c.name),
            None => c.name.clone(),
        }
    }).collect();

    println!("scope.columns(): {all_cols:?}");
    println!("expected:        [\"o.total\", \"c.id\", \"o.customer_id\"]");
}

[tobilg]

Thanks for reporting, this will be fixed in version 0.1.7.