Incorrect column qualification in an incorrect subquery

[staskikotx]

Version 28.5.0

import sqlglot
from sqlglot.optimizer.qualify import qualify

schema = {
    "frpm": {
        "FRPM Count (Ages 5-17)": "INT",
        "cdscode": "INT"
    },
    "satscores": {
        "cds": "INT",
        "avgscrread": "INT"
    }
}

sql_query = '''SELECT "FRPM Count (Ages 5-17)" FROM frpm WHERE cdscode = (SELECT cdscode FROM satscores ORDER BY avgscrread DESC NULLS LAST LIMIT 1)'''

# Parse and qualify
expression = sqlglot.parse_one(sql_query, read="postgres")
qualified = qualify(expression, schema=schema, dialect="postgres")

print("Qualified SQL:")
print(qualified.sql(dialect="postgres"))

The output is

Qualified SQL:
SELECT "frpm"."FRPM Count (Ages 5-17)" AS "FRPM Count (Ages 5-17)" FROM "frpm" AS "frpm" WHERE "frpm"."cdscode" = (SELECT "frpm"."cdscode" AS "cdscode" FROM "satscores" AS "satscores" ORDER BY "satscores"."avgscrread" DESC NULLS LAST LIMIT 1)

with incorrect column qualification "frpm"."cdscode" in the subquery.

I think that the column qualifier should throw an error here because the column cdscode does not exist in satscores.

[abesticode]

Hi @staskikotx ,

I looked into this issue and I believe this is actually expected behavior that follows standard SQL semantics for correlated subqueries. Let me explain:

What's happening

In your query:

SELECT "FRPM Count (Ages 5-17)" FROM frpm 
WHERE cdscode = (SELECT cdscode FROM satscores ORDER BY avgscrread DESC NULLS LAST LIMIT 1)

1. The column cdscode in the subquery doesn't exist in satscores (which only has cds and avgscrread)
2. According to SQL standards, when a column cannot be resolved in the subquery's own sources, the database will look for it in the outer (parent) scope
3. SQLGlot finds cdscode in the outer table frpm and correctly qualifies it as a correlated reference

This is the same pattern as this commonly-used correlated subquery:

SELECT a FROM x WHERE b IN (SELECT c FROM y WHERE y.b = a)
-- Here, `a` in the subquery is a correlated reference to x.a, not a column from y

Why no error is raised

SQLGlot cannot distinguish between:

1. ✅ An intentional correlated reference to an outer table's column
2. ❌ A typo where you meant cds instead of cdscode

Both are syntactically and semantically valid SQL. Without additional context about your intent, SQLGlot must accept the query as written.

How to get the error you expect

If you want to ensure columns exist in the subquery's direct source, explicitly qualify them:

sql_query = '''SELECT "FRPM Count (Ages 5-17)" FROM frpm WHERE cdscode = (SELECT satscores.cdscode FROM satscores ORDER BY avgscrread DESC NULLS LAST LIMIT 1)'''

This will correctly raise: OptimizeError: Unknown column: cdscode

Alternatively

If you intended to use the cds column from satscores:

sql_query = '''SELECT "FRPM Count (Ages 5-17)" FROM frpm WHERE cdscode = (SELECT cds FROM satscores ORDER BY avgscrread DESC NULLS LAST LIMIT 1)'''

This will work correctly and qualify to "satscores"."cds".

---

Summary: This is standard SQL correlated subquery behavior. The qualification "frpm"."cdscode" is technically correct for a correlated reference. If this wasn't your intent, I recommend either explicitly qualifying subquery columns or double-checking column names against your schema.

Hope this helps clarify the behavior! Let me know if you have any questions.

[VaggelisD]

The LLM answer seems right, this is valid Postgres even though t2 does not have b:

WITH 
  t1 AS (SELECT 2 AS a, 2 AS b), 
  t2 AS (SELECT 3 AS c) 
SELECT a FROM t1 WHERE b = (SELECT a FROM t2 ORDER BY c);

 a
---
 2
(1 row)