trinofy 0.1.1

MySQL → Trino compiler for LLM-generated analytical SQL.

MySQL → Trino SQL compiler for LLM-generated analytical queries

Install • Quick Start • How It Works • Transforms • Warnings • License

---

trinofy is a compiler that rewrites MySQL-flavored SQL into valid Trino SQL. It is purpose-built for the LLM-to-SQL pipeline: when large language models generate analytical queries, they default to MySQL dialect. trinofy sits as a middleware layer, translating that output into Trino-compatible SQL while surfacing any lossy or ambiguous rewrites as structured warnings.

It extends sqlglot with a multi-phase rule engine that handles the real-world dialect gaps sqlglot doesn't cover.

Install

pip install trinofy

Requires Python 3.12+. The only dependency is sqlglot.

Quick Start

from trinofy import compile_mysql_to_trino

result = compile_mysql_to_trino(
    """
    SELECT
        SUBSTRING_INDEX(name, ',', 1)   AS last_name,
        GROUP_CONCAT(DISTINCT tag ORDER BY tag SEPARATOR ', ') AS tags,
        UNIX_TIMESTAMP(created_at)       AS epoch
    FROM users
    WHERE created_at >= '2026-01-01 00:00:00'
    """
)

print(result.trino_sql)

Output:

SELECT
    array_join(slice(split(name, ','), 1, 1), ',') AS last_name,
    array_join(array_distinct(array_agg(tag ORDER BY tag)), ', ') AS tags,
    to_unixtime(created_at) AS epoch
FROM users
WHERE created_at >= CAST('2026-01-01 00:00:00' AS TIMESTAMP)

for w in result.warnings:
    print(f"[{w.code}] {w.message}")

[length_semantics] MySQL LENGTH() counts bytes, Trino length() counts characters...

Pass pretty=True for formatted output: compile_mysql_to_trino(sql, pretty=True).

How It Works

trinofy compiles MySQL SQL to Trino SQL in three phases:

  MySQL SQL (from LLM)
         |
    ┌────┴────┐
    │ Phase 1 │  Pre-Parse Text Rules
    │         │  Normalize raw SQL before parsing
    └────┬────┘
         |
    ┌────┴────┐
    │ Phase 2 │  AST Transformation Rules
    │         │  Walk the sqlglot AST, rewrite nodes
    └────┬────┘
         |
    ┌────┴────┐
    │ Phase 3 │  Post-Emit Text Rules
    │         │  Patch the generated Trino SQL string
    └────┬────┘
         |
  Trino SQL + Warnings

Phase 1 — Pre-Parse

Raw text fixes before sqlglot parses the SQL:

Rule	What it does
C-escape detection	Flags MySQL escape sequences (\n, \t, etc.) inside string literals. Warns but does not rewrite to avoid silent data corruption.
Duplicate JOIN collapse	Fixes LLM typos like INNER INNER JOIN → INNER JOIN so sqlglot can parse the query.

Phase 2 — AST Transformations

The core compiler walks every node in the sqlglot AST and applies dialect-specific rewrites:

Date & Time

MySQL	Trino
WEEKDAY(d)	day_of_week(d) - 1 (preserves Mon=0..Sun=6)
DAYNAME(d)	date_format(d, '%W')
MAKEDATE(y, doy)	date_add('day', doy - 1, cast(format('%04d-01-01', y) as date))
TIME_TO_SEC(t)	hour(t)*3600 + minute(t)*60 + second(t)
UNIX_TIMESTAMP()	to_unixtime(current_timestamp)
UNIX_TIMESTAMP(ts)	to_unixtime(cast(ts AS TIMESTAMP)) (with warning)
REGEXP_INSTR(s, p)	regexp_position(s, p)
WEEK(d, mode)	week(d) — warns on non-ISO modes
DATE_FORMAT / STR_TO_DATE	Flags unsupported specifiers (%D, %U, %u, %V, %w, %X)

String Operations

MySQL	Trino
SUBSTRING_INDEX(s, delim, n)	array_join(slice(split(s, delim), ...), delim) — handles positive, negative, zero, and dynamic counts
FIELD(x, s1, s2, ...)	CASE x WHEN s1 THEN 1 WHEN s2 THEN 2 ... ELSE 0 END
FIND_IN_SET(str, csv)	coalesce(array_position(split(csv, ','), str), 0)
GROUP_CONCAT(col)	array_join(array_agg(col), sep) — supports DISTINCT and ORDER BY
TRUNCATE(x, d)	sign(x) * floor(abs(x) * power(10, d)) / power(10, d)
HEX(x)	to_hex(cast(x AS varbinary)) for strings; format('%X', n) for numeric literals
LENGTH(x)	Flags byte-vs-character semantics difference

Casts & Types

MySQL	Trino
CAST(x AS UNSIGNED)	CAST(x AS BIGINT) (Trino has no unsigned types)
CAST(x AS BINARY)	CAST(x AS VARBINARY)
String vs Timestamp comparison	Auto-wraps string literals in CAST(... AS TIMESTAMP) to prevent TYPE_MISMATCH errors

JSON

MySQL	Trino
JSON_UNQUOTE(JSON_EXTRACT(col, path))	json_extract_scalar(col, path)

Miscellaneous

MySQL	Trino
@var session variables	Flagged as unsupported
CROSS JOIN ... ON	Rewritten to JOIN ... ON (INNER JOIN)

Phase 3 — Post-Emit

After sqlglot generates Trino SQL, text-level patches fix output that the AST layer can't address:

Rule	What it does
AT TIME ZONE operator	Rewrites sqlglot's AT_TIMEZONE(expr, 'zone') function call into Trino's (expr AT TIME ZONE 'zone') operator syntax. Handles nesting iteratively.

Warning Codes

Every transform can emit structured CompileWarning(code, message, snippet) objects. These tell you when a rewrite may have changed semantics or needs human review.

Code	Severity	Meaning
mysql_c_escape	Review	String contains MySQL C-style escape (\n, \t, etc.). Trino doesn't interpret these.
duplicate_join_keyword_collapsed	Info	Doubled JOIN keyword collapsed (LLM typo).
week_mode_unsupported	Review	WEEK(d, mode) with a non-ISO mode. Trino only supports ISO week numbering.
date_format_specifier_unsupported	Review	DATE_FORMAT/STR_TO_DATE uses a format specifier Trino doesn't support.
unix_timestamp_string	Review	UNIX_TIMESTAMP(str) — string-to-timestamp format may need verification.
hex_polymorphic	Review	HEX() on a non-literal — type (number vs string) is ambiguous without a catalog.
length_semantics	Review	LENGTH() counts bytes in MySQL but characters in Trino.
substring_index_dynamic_count	Review	SUBSTRING_INDEX with a non-literal count — verify runtime value.
session_variable_unsupported	Error	MySQL session variable (@var) has no Trino equivalent.
cross_join_with_on_rewritten	Info	CROSS JOIN ... ON rewritten to INNER JOIN ... ON.

The Gotcha Catalog

trinofy ships with a comprehensive catalog.md documenting 50+ MySQL—Trino dialect differences across date/time, strings, JSON, comparison, regex, casts, math, booleans, identifiers, and session variables. Each entry records the MySQL syntax, Trino equivalent, and whether sqlglot handles it or trinofy's rule engine is needed.

Why Not Just Use sqlglot?

sqlglot is an excellent SQL transpiler and forms the foundation of trinofy. However, it doesn't cover every MySQL—Trino gap. trinofy adds value where sqlglot falls short:

• GROUP_CONCAT with ORDER BY or DISTINCT — sqlglot emits LISTAGG which doesn't support these in Trino. trinofy uses array_join(array_agg(...)) instead.
• SUBSTRING_INDEX — No direct Trino equivalent; requires split + slice + array_join.
• WEEKDAY / DAYNAME / MAKEDATE — Semantic differences in numbering or missing functions.
• String-to-timestamp comparison casting — MySQL auto-casts strings in comparisons; Trino throws TYPE_MISMATCH.
• AT TIME ZONE operator — sqlglot emits a function call; Trino requires operator syntax.
• Pre-parse normalization — LLM-specific typos like doubled JOIN keywords.
• Structured warnings — Every ambiguous or lossy transform is surfaced, not silently applied.

Development

# Clone the repository
git clone https://github.com/yourname/trinofy.git
cd trinofy

# Install in development mode
pip install -e .

# Build distribution
pip install build
python -m build

Project Structure

trinofy/
├── __init__.py          # Public API: compile_mysql_to_trino, CompileResult, CompileWarning
├── compile.py           # Core compiler pipeline (parse → transform → generate)
├── warnings.py          # CompileWarning dataclass
├── catalog.md           # MySQL ↔ Trino gotcha reference
└── rules/               # Translation rule engine
    ├── pre_parse.py     # Phase 1: raw SQL text fixes
    ├── datetime.py      # Date/time function rewrites
    ├── string_ops.py    # String function rewrites
    ├── casts.py         # Type cast rewrites
    ├── json_ops.py      # JSON function rewrites
    ├── misc.py          # Session vars, CROSS JOIN fix
    └── text_fallback.py # Phase 3: post-emit SQL patches

License

MIT — see LICENSE.

---

Built for the LLM-to-SQL generation pipeline.
Because LLMs speak MySQL, but your lakehouse speaks Trino.