tdfs4ds.agent.sql_qualify¶

tdfs4ds.agent.sql_qualify ¶

Deterministic column qualification for Teradata SQL.

Why this exists¶

The query optimizer's simplification pass flattens nesting: derived tables are merged into their parent, CTEs are inlined, wrapper SELECT layers are removed. That merges two previously-distinct name scopes into one.

A bare (unqualified) column that resolved uniquely in the original nested form can, after the merge, resolve to a different relation — or become outright ambiguous (Teradata Error 3809). Neither outcome is a syntax error the existing gate can see:

silent rebinding → EXPLAIN compiles, user_score is unaffected, the query now reads a different column. Business logic changed, nothing flagged.
Error 3809 → the syntax-repair loop in skill_simplify_query asks the LLM to fix it, and the LLM picks an alias with no information about which one was correct. A coin flip is written into the accepted SQL.

The fix is to remove the degree of freedom before the LLM ever sees the query. If a query compiles, every bare column in it already resolves uniquely in its own scope — Teradata rejects ambiguity outright. So the correct qualification is not a guess, it is derivable: resolve each bare column against the relations visible in its scope, and write the answer down explicitly. A later flatten then cannot rebind it, because the alias travels with the column.

Design rules¶

Never regenerate the SQL. These process views carry CURRENT VALIDTIME, NONSEQUENCED VALIDTIME, QUALIFY, PERIOD/INTERVAL and UAF constructs. Parsing to an AST and re-emitting would mangle exactly the grammar this codebase has spent dozens of fixes taming — the same class of silent corruption we are trying to prevent. This module only ever inserts alias. at computed character offsets; every other byte is preserved.
Only qualify what is provable. A prefix is written only when the name is a column of exactly one visible relation whose column list is fully known. Not found anywhere, found in two places, or any relation in scope with an unknown column list → leave the token alone. Unknown is always "don't touch", never "guess".
Fail open, per scope. A scope we cannot analyse is skipped; the rest of the query is still qualified. The worst case is the status quo.

The module is dependency-free (stdlib only) and DB-free — build_schema_map is the only function that touches Teradata, and it is optional.

logger `module-attribute` ¶

logger = getLogger(__name__)

all `module-attribute` ¶

__all__ = ['qualify_columns', 'build_schema_map', 'QualifyReport']

_IDENT_RE `module-attribute` ¶

_IDENT_RE = compile('[A-Za-z_][A-Za-z_0-9$#]*')

_NUM_RE `module-attribute` ¶

_NUM_RE = compile('\\d+(\\.\\d+)?([eE][+-]?\\d+)?')

_KEYWORDS `module-attribute` ¶

_KEYWORDS: set = {'SELECT', 'FROM', 'WHERE', 'GROUP', 'BY', 'ORDER', 'HAVING', 'QUALIFY', 'WITH', 'AS', 'ON', 'USING', 'DISTINCT', 'ALL', 'TOP', 'SAMPLE', 'UNION', 'INTERSECT', 'EXCEPT', 'MINUS', 'RECURSIVE', 'JOIN', 'INNER', 'LEFT', 'RIGHT', 'FULL', 'OUTER', 'CROSS', 'NATURAL', 'INSERT', 'UPDATE', 'DELETE', 'MERGE', 'INTO', 'VALUES', 'SET', 'CREATE', 'REPLACE', 'VIEW', 'TABLE', 'VOLATILE', 'PRIMARY', 'INDEX', 'LOCKING', 'ROW', 'ACCESS', 'FOR', 'LOCK', 'AND', 'OR', 'NOT', 'IN', 'EXISTS', 'BETWEEN', 'LIKE', 'IS', 'NULL', 'CASE', 'WHEN', 'THEN', 'ELSE', 'END', 'ASC', 'DESC', 'NULLS', 'FIRST', 'LAST', 'ESCAPE', 'SOME', 'ANY', 'OVER', 'PARTITION', 'ROWS', 'RANGE', 'PRECEDING', 'FOLLOWING', 'UNBOUNDED', 'CURRENT', 'PIVOT', 'UNPIVOT', 'VALIDTIME', 'TRANSACTIONTIME', 'NONSEQUENCED', 'SEQUENCED', 'OF', 'PERIOD', 'BEGIN', 'PORTION', 'CAST', 'INTERVAL', 'DATE', 'TIME', 'TIMESTAMP', 'YEAR', 'MONTH', 'DAY', 'HOUR', 'MINUTE', 'SECOND', 'ZONE', 'INT', 'INTEGER', 'SMALLINT', 'BIGINT', 'BYTEINT', 'DECIMAL', 'NUMERIC', 'FLOAT', 'REAL', 'DOUBLE', 'PRECISION', 'CHAR', 'VARCHAR', 'CHARACTER', 'VARYING', 'CLOB', 'BLOB', 'BYTE', 'VARBYTE', 'NUMBER', 'JSON', 'XML', 'DEFAULT', 'FORMAT', 'TITLE', 'CHARACTERS', 'UPPERCASE', 'CASESPECIFIC', 'COMPRESS', 'TRUE', 'FALSE', 'UNKNOWN', 'EXECUTE', 'FUNCTION', 'RETURNS', 'SCRIPT', 'TRIM', 'BOTH', 'LEADING', 'TRAILING', 'FROM_BYTES', 'SEL'}

_REL_TERMINATORS `module-attribute` ¶

_REL_TERMINATORS: set = {'WHERE', 'GROUP', 'ORDER', 'HAVING', 'QUALIFY', 'ON', 'USING', 'JOIN', 'INNER', 'LEFT', 'RIGHT', 'FULL', 'OUTER', 'CROSS', 'NATURAL', 'UNION', 'INTERSECT', 'EXCEPT', 'MINUS', 'SAMPLE', 'WITH', 'SET'}

_SELECT_LIST_END `module-attribute` ¶

_SELECT_LIST_END: set = {'FROM'}

_Tok ¶

slots `class-attribute` `instance-attribute` ¶

__slots__ = ('kind', 'value', 'start', 'end', 'depth')

kind `instance-attribute` ¶

kind = kind

value `instance-attribute` ¶

value = value

start `instance-attribute` ¶

start = start

end `instance-attribute` ¶

end = end

depth `instance-attribute` ¶

depth = 0

upper `property` ¶

upper: str

init ¶

__init__(kind: str, value: str, start: int, end: int)

repr ¶

__repr__() -> str

QualifyReport ¶

Outcome of a qualification pass.

Attributes:

Name	Type	Description
`sql`		The qualified SQL (identical to the input when nothing was provably qualifiable).
`changed`		True when at least one prefix was inserted.
`qualified`	`list`	List of `(column, alias)` pairs that were prefixed.
`skipped`	`list`	Human-readable reasons a scope or token was left alone.
`ambiguous`	`list`	Bare columns found in 2+ visible relations. These are the landmines: the original compiles, so one of our column lists is wrong OR the name is a select-list alias. Never qualified, always reported.

slots `class-attribute` `instance-attribute` ¶

__slots__ = ('sql', 'changed', 'qualified', 'skipped', 'ambiguous')

sql `instance-attribute` ¶

sql = sql

changed `instance-attribute` ¶

changed = False

qualified `instance-attribute` ¶

qualified: list = []

skipped `instance-attribute` ¶

skipped: list = []

ambiguous `instance-attribute` ¶

ambiguous: list = []

init ¶

__init__(sql: str)

summary ¶

summary() -> str

repr ¶

__repr__() -> str

_Relation ¶

One entry in a FROM/JOIN clause.

slots `class-attribute` `instance-attribute` ¶

__slots__ = ('alias', 'name', 'columns', 'kind')

alias `instance-attribute` ¶

alias = alias

name `instance-attribute` ¶

name = name

columns `instance-attribute` ¶

columns = columns

kind `instance-attribute` ¶

kind = kind

init ¶

__init__(alias: str, name: str, columns, kind: str)

_Scope ¶

A SELECT and the relations visible to it.

slots `class-attribute` `instance-attribute` ¶

__slots__ = ('select_idx', 'depth', 'start_idx', 'end_idx', 'relations', 'out_columns', 'local_aliases', 'usable')

select_idx `instance-attribute` ¶

select_idx = select_idx

depth `instance-attribute` ¶

depth = depth

start_idx `instance-attribute` ¶

start_idx = select_idx

end_idx `instance-attribute` ¶

end_idx = select_idx

relations `instance-attribute` ¶

relations: list = []

out_columns `instance-attribute` ¶

out_columns = None

local_aliases `instance-attribute` ¶

local_aliases: set = set()

usable `instance-attribute` ¶

usable = True

init ¶

__init__(select_idx: int, depth: int)

_tokenize ¶

_tokenize(sql: str) -> list

Split SQL into tokens, tracking paren depth.

Comments and whitespace are skipped. String literals and quoted identifiers are emitted as single opaque tokens so their contents can never be mistaken for column references.

_bare ¶

_bare(tok) -> str

Return the identifier text with surrounding double quotes stripped.

_find_matching_close ¶

_find_matching_close(toks: list, open_idx: int) -> int

Index of the ) matching the ( at open_idx (or len(toks)).

_scope_extent ¶

_scope_extent(toks: list, select_idx: int) -> int

Index one past the last token belonging to this SELECT's scope.

_parse_object_name ¶

_parse_object_name(toks: list, i: int, end: int)

Parse a, a.b or a.b.c starting at i.

Returns (fq_name, last_part, next_index) or (None, None, i).

_collect_ctes ¶

_collect_ctes(toks: list) -> dict

Map CTE name → index of the ( opening its body.

Handles WITH a AS (...), b AS (...). A CTE with an explicit column list (WITH a (c1, c2) AS (...)) is recorded with its declared columns.

_parse_from_relations ¶

_parse_from_relations(toks: list, scope: _Scope, schema: dict, derived_out: dict, ctes: dict, report: QualifyReport) -> None

Populate scope.relations from the FROM/JOIN clauses at scope depth.

_read_alias ¶

_read_alias(toks: list, i: int, end: int, depth: int)

Read an optional [AS] alias at i. Returns (alias|None, next).

_lookup_schema ¶

_lookup_schema(schema: dict, fq: str)

Resolve a table reference against the schema map (fully-qualified first).

_select_list_output ¶

_select_list_output(toks: list, scope: _Scope)

Compute the output column names of a scope's SELECT list.

Returns None when the list contains * (unknown shape) or cannot be determined. Also populates scope.local_aliases.

_build_scopes ¶

_build_scopes(toks: list, schema: dict, report: QualifyReport)

Build every SELECT scope, innermost first, resolving derived outputs.

_is_column_position ¶

_is_column_position(toks: list, idx: int) -> bool

True when the token at idx sits where a column reference may appear.

_relation_alias_names ¶

_relation_alias_names(scope: _Scope) -> set

qualify_columns ¶

qualify_columns(sql: str, schema: Optional[dict] = None) -> QualifyReport

Prefix every provably-unique bare column reference with its relation alias.

Parameters:

Name	Type	Description	Default
`sql`	`str`	Teradata SQL to qualify. Returned byte-identical except for the inserted `alias.` prefixes.	required
`schema`	`Optional[dict]`	Map of `"DB.OBJECT"` (or `"OBJECT"`) → iterable of column names. Case-insensitive. When omitted or incomplete, scopes whose relations cannot be resolved are skipped.	`None`

Returns:

Name	Type	Description
`A`	`QualifyReport`	class:`QualifyReport`. `report.sql` is always safe to use: on any
	`QualifyReport`	internal failure it is the unmodified input.

build_schema_map ¶

build_schema_map(refs, execute_sql=None) -> dict

Fetch column lists for refs via HELP COLUMN.

HELP COLUMN db.obj.* is used rather than DBC.ColumnsV because the latter reports NULL ColumnType for view columns — and process views are the common case here.

Parameters:

Name	Type	Description	Default
`refs`		Iterable of `{"database": str, "name": str}` dicts (the shape `_extract_table_refs` already returns) or `"db.obj"` strings.	required
`execute_sql`		Callable taking a SQL string; defaults to `teradataml.execute_sql`. Injected for testing.	`None`

Returns:

Type	Description
`dict`	`{"DB.OBJ": {"COL1", "COL2", ...}}`. Objects that cannot be read are
`dict`	omitted, which makes their scopes unqualifiable (fail-open) rather than
`dict`	wrongly qualified.

tdfs4ds.agent.sql_qualify¶