Type Annotation Discrepancy: Generic vs. Dialect-Specific Types

[ragunandhant]

@tobymao
I'm working on a system that involves SQL query parsing and type annotation. I have a SQL query and a corresponding schema, and I'm using a type annotation function (annotate_types) to infer and assign data types to the expressions within the Abstract Syntax Tree (AST).

The Problem:

While the type annotation process correctly identifies the base type (e.g., Type.INT for an INT column), I've noticed that the resulting this attribute of the annotated type is a generic enum value (e.g., <Type.INT: 'INT'>).

My Goal:

I want the annotated type's this attribute to reflect the dialect-specific type name. For instance, if the dialect is 'tsql' and the original column type is INT, I'd like the annotated type to show INTEGER (or whatever the precise T-SQL equivalent is) instead of the generic INT.

Example:

Given the following schema and SQL query:

schema = {
    'customers': {
        'customerid': DataType(this=Type.INT, nested=False),
        # ... other columns
    }
}

sql_query = """
SELECT
  [customers].[customerid] AS [customerid]
FROM [customers] AS [customers]
"""

dialect = 'tsql'

After annotation:

annotated_exprs = annotate_types(expression=ast, schema=schema, dialect="tsql")
annotated_exprs.expressions[0].type.this
# Current Result: <Type.INT: 'INT'>
# Desired Result: 'INTEGER' (or similar T-SQL specific representation)

Question:

Is it possible to configure or extend the annotate_types function (or the underlying type system) to produce dialect-specific type names instead of generic ones? If so, what approach would be recommended? Are there common patterns or libraries that handle this kind of type mapping in SQL AST processing?

Any guidance or examples would be greatly appreciated!

---

[georgesittas]

You can get the dialect-specific name by generating SQL for the annotated type targeting said dialect:

annotated_exprs = annotate_types(expression=ast, schema=schema, dialect="tsql")
annotated_exprs.expressions[0].type.sql("tsql")

The current AST structure is expected. We intentionally don't tie any transformations or ASTs to a specific dialect.