An unqualified name finds a temporary table from another schema after USE SCHEMA #479

Summary

An unqualified name still finds a temporary table after USE SCHEMA moves the session to another schema. In Snowflake a temporary table belongs to the database and schema it was created in, so from another schema the unqualified name doesn't find it. DuckDB keeps every temporary table in one temp catalog and searches it first, whatever the current schema.

Reproduce (0.12.1)

import fakesnow, snowflake.connector

with fakesnow.patch():
    cur = snowflake.connector.connect(database="db1", schema="s1").cursor()
    cur.execute("create schema s2")  # also makes S2 the current schema, in Snowflake and fakesnow
    cur.execute("use schema s1")
    cur.execute("create temporary table tt (x int)")
    cur.execute("use schema s2")
    cur.execute("select * from tt")
    print(cur.fetchall())

fakesnow: [], reading S1's temporary table.
Snowflake: 002003 (42S02): SQL compilation error: Object 'TT' does not exist or not authorized.

Checked against a live Snowflake account (10.38): the select fails as above, and fakesnow returns [], on 0.12.1 and with #472.

The same single namespace means one session can't have temporary tables with the same name in two schemas. #472 resolves qualified references to temporary tables but leaves both of these as they are.

Why it matters

A test that switches schema can read a temporary table it couldn't on Snowflake, so it passes here and fails there.