Bug: Athena TO_ISO8601 remains unmapped when transpiled to DuckDB

[refacktor]

Confirmed on current upstream HEAD

Polyglot leaves Athena TO_ISO8601 unchanged, producing DuckDB SQL that fails because the function does not exist.

Environment

Reproduced on 2026-09-20 directly through native Polyglot FFI 0.12.0, built from current upstream commit 8b728c154648fd3e92d328183df1e5ac5008746f. Source dialect athena; target dialect duckdb. Generated SQL was executed unchanged in clean DuckDB 1.5.5.

Reproduction

Build libpolyglot_sql_ffi.so from the commit specified in Environment. Save this test as test_athena_duckdb_issue_5.py, set POLYGLOT_LIB_PATH to that library, and run:

python -m pip install 'pytest>=8.0' 'duckdb==1.5.5'
POLYGLOT_LIB_PATH=/absolute/path/libpolyglot_sql_ffi.so python -m pytest -q test_athena_duckdb_issue_5.py

import ctypes
import json
import os

import duckdb


class Result(ctypes.Structure):
    _fields_ = [
        ("data", ctypes.c_void_p),
        ("error", ctypes.c_void_p),
        ("status", ctypes.c_int32),
    ]


def transpile_athena(sql):
    lib = ctypes.CDLL(os.environ["POLYGLOT_LIB_PATH"])
    lib.polyglot_transpile.argtypes = [ctypes.c_char_p] * 3
    lib.polyglot_transpile.restype = Result
    lib.polyglot_free_result.argtypes = [Result]
    lib.polyglot_free_result.restype = None
    result = lib.polyglot_transpile(sql.encode(), b"athena", b"duckdb")
    try:
        error = ctypes.string_at(result.error).decode() if result.error else None
        assert result.status == 0, error
        assert error is None
        assert result.data
        statements = json.loads(ctypes.string_at(result.data))
        assert isinstance(statements, list) and len(statements) == 1
        assert isinstance(statements[0], str) and statements[0].strip()
        return statements[0]
    finally:
        lib.polyglot_free_result(result)


def test_athena_to_iso8601_executes_in_duckdb():
    sql = "SELECT to_iso8601(TIMESTAMP '2026-05-26 02:08:02.930') AS answer"
    generated_sql = transpile_athena(sql)
    with duckdb.connect() as connection:
        rows = connection.execute(generated_sql).fetchall()
    assert rows == [('2026-05-26T02:08:02.930',)], generated_sql

Input SQL

SELECT to_iso8601(TIMESTAMP '2026-05-26 02:08:02.930') AS answer

Generated SQL

polyglot_transpile returned status 0, no error, and the following SQL:

SELECT TO_ISO8601(CAST('2026-05-26 02:08:02.930' AS TIMESTAMP)) AS answer

Actual and expected

Actual:

Catalog Error: Scalar Function with name to_iso8601 does not exist!
Did you mean "to_json"?

LINE 1: SELECT TO_ISO8601(CAST('2026-05-26 02:08:02.930' AS TIMESTAMP)...
               ^

Expected (JSON notation; null means SQL NULL):

"2026-05-26T02:08:02.930"

Map this documented function with correct timestamp precision and timezone semantics, or explicitly reject unsupported translation.

Confirmed against AWS Athena

The same input query succeeded in us-west-2 using Athena engine version 3 (selected AUTO). Executed at 20:59:31 UTC on September 20, 2026. The query scanned 0 bytes and did not reuse a previous result.

Observed Athena result (JSON notation; null means SQL NULL):

"2026-05-26T02:08:02.930"

The live Athena result establishes the source-dialect behavior without relying on an interpretation of the written specification. No external table data was used.

Reference

Trino source semantics

[tobilg]

This will be fixed in version 0.12.1 that is going to be release in about two hours. Please check and close accordingly.

[refacktor]

I see it now returns an explicit error. I see DuckDB doesn't store the original TZ offset or subsecond precision, preventing an exact conversion at runtime. Could permissive mode instead use strftime with %n for nine fractional digits, keeping timestamps without time zones unzoned and normalizing TIMESTAMPTZ to UTC with a Z suffix? That would provide "technically correct" (but differently formatted) ISO 8601 output, while strict mode could retain the error.

[tobilg]

Thanks for reporting, this will be fixed in the 0.13.0 release that will be done today. Please check and close if this fixed your issue.