Binder Error when alias is used in JOIN clause

[rogalski]

MCVE in Snowflake:

CREATE OR REPLACE TEMPORARY TABLE TEST (COL VARCHAR);
INSERT INTO TEST (COL) VALUES ('VARCHAR1'), ('VARCHAR2');

CREATE OR REPLACE TEMPORARY TABLE JOINED (COL VARCHAR, ANOTHER VARCHAR);
INSERT INTO JOINED (COL, ANOTHER) VALUES ('CHAR1', 'JOIN');

SELECT
    T.COL
    , SUBSTR(T.COL, 4) AS ALIAS
    , J.ANOTHER
FROM TEST AS T
LEFT JOIN JOINED AS J
ON ALIAS = J.COL;

Result:

COL	ALIAS	ANOTHER
VARCHAR1	CHAR1	JOIN
VARCHAR2	CHAR2

MCVE in fakesnow:

import fakesnow
import snowflake.connector


def main():
    with fakesnow.patch():
        conn = snowflake.connector.connect(database="X", schema="Y")
        conn.execute_string("CREATE OR REPLACE TEMPORARY TABLE TEST (COL VARCHAR)")
        conn.execute_string("INSERT INTO TEST (COL) VALUES ('VARCHAR1'), ('VARCHAR2')")
        conn.execute_string("CREATE OR REPLACE TEMPORARY TABLE JOINED (COL VARCHAR, ANOTHER VARCHAR)")
        conn.execute_string("INSERT INTO JOINED (COL, ANOTHER) VALUES ('CHAR1', 'JOIN')")
        rows = conn.cursor().execute("""
        SELECT
            T.COL
            , SUBSTR(T.COL, 4) AS ALIAS
            , J.ANOTHER
        FROM TEST AS T
        LEFT JOIN JOINED AS J
        ON ALIAS = J.COL;
        """).fetchall()


if __name__ == "__main__":
    main()

Error:

snowflake.connector.errors.ProgrammingError: 002043 (02000): Binder Error: Referenced column "ALIAS" not found in FROM clause!
Candidate bindings: "J.COL"
LINE 1: ...ROM TEST AS T LEFT JOIN JOINED AS J ON ALIAS = J.COL

Version:

$ pip freeze | grep snow
fakesnow==0.9.12
snowflake-connector-python==3.2.1
snowflake-sqlalchemy==1.5.0

FAKESNOW_DEBUG=1:

CREATE OR REPLACE TEMPORARY TABLE TEST (COL TEXT);
INSERT INTO TEST (COL) VALUES ('VARCHAR1'), ('VARCHAR2');
CREATE OR REPLACE TEMPORARY TABLE JOINED (COL TEXT, ANOTHER TEXT);
INSERT INTO JOINED (COL, ANOTHER) VALUES ('CHAR1', 'JOIN');
SELECT T.COL, SUBSTR(T.COL, 4) AS ALIAS, J.ANOTHER FROM TEST AS T LEFT JOIN JOINED AS J ON ALIAS = J.COL;

[tekumara]

Duckdb doesn't support this convenience yet (raised here) but this could be supported in fakesnow with a transform.