Type annotation treats a non-recursive self-named CTE as recursive

[kvlonge]

Summary

Type annotation treats a non-recursive CTE as recursive when its second UNION ALL branch reads a physical table with the same name as the CTE. The CTE then shadows that table, and analyze_query() reports the first branch's type instead of the widened type.

Reproduction

pip install polyglot-sql==0.12.1

import polyglot_sql

schema = {"tables": [{"name": "orders", "columns": [{"name": "id", "type": "BIGINT"}]}]}
sql = """
WITH orders AS (
    SELECT CAST(1 AS INT) AS id
    UNION ALL
    SELECT id FROM orders
)
SELECT id FROM orders
"""
projection = polyglot_sql.analyze_query(
    sql, {"dialect": "duckdb", "schema": schema}
)["projections"][0]
print(projection["typeHint"])

Actual

INT

Expected

BIGINT. Without WITH RECURSIVE, the inner orders refers to the physical orders table (BIGINT), so the union widens to BIGINT. DuckDB agrees:

import duckdb

connection = duckdb.connect()
connection.execute("CREATE TABLE orders (id BIGINT)")
result = connection.execute(
    "WITH orders AS (SELECT CAST(1 AS INT) AS id UNION ALL SELECT id FROM orders) "
    "SELECT id FROM orders"
)
print(result.description[0][1])  # BIGINT

The early anchor binding in annotate_with (optimizer/annotate_types.rs) runs for any self-named reference. Gating it on with.recursive, as the scope builder already does, resolves this.

[tobilg]

Thanks for reporting, this will be fixed in the 0.13.0 release that will be done today. Please check and close if this fixed your issue.