Table aliases defined in subquery can't be resolved

[michaelst]

Fully reproducible code snippet

schema = {'users': {'id': '', 'name': '', 'email': '', 'metadata': ''}}

sql = """
SELECT id
FROM users
WHERE EXISTS (
  SELECT 1
  FROM jsonb_array_elements(metadata) AS elem
  WHERE elem ? 'test'
)
"""

tree = parse_one(sql, read="postgres")

# this step fails (ERROR:root:Error parsing SQL: Column '"elem"' could not be resolved)
qualify(tree, schema=schema, dialect="postgres")

[tobymao]

how is this a legal query? content comes from posts which you never select from?

[michaelst]

Oh sorry you are right, the column being selected in jsonb_array_elements is from the same table, not a different one, I updated the description

[VaggelisD]

Fixed by #6373:

>>> import sqlglot
>>> from sqlglot.optimizer.qualify import qualify
>>> schema = {'users': {'id': '', 'name': '', 'email': '', 'metadata': ''}}
... 
... sql = """
... SELECT id
... FROM users
... WHERE EXISTS (
...   SELECT 1
...   FROM jsonb_array_elements(metadata) AS elem
...   WHERE elem ? 'test'
... )
... """
... 
>>> ast = sqlglot.parse_one(sql, dialect='postgres')
>>> ast = qualify(ast, schema=schema, dialect='postgres')
>>> ast.sql('postgres')
'SELECT "users"."id" AS "id" FROM "users" AS "users" WHERE EXISTS(SELECT 1 AS "1" FROM JSONB_ARRAY_ELEMENTS("metadata") AS "elem" WHERE "elem" ? \'test\')'