Description
Describe the bug
When the model substitutes, we use sqlglot to transpile the dialect SQL to our dialect
However, after transforming to the sqlgot IR, the function name could be mapped to its function name.
In this case, TIEMSTAMP_MILLIS
will be transformed to UNIX_TO_TIME
when the output dialect is None
(used by our v3 API). If the output dialect is trino
(used by our v2 API), it will be transformed to FROM_UNIXTIME(CAST(timestamp AS DOUBLE) / POW(10, 3))
. After the v2 wren core planning, the function name will be transformed to TIEMSTAMP_SECONDS(CAST(timestamp AS DOUBLE) / POW(10, 3))
, which function signature is wrong. TIMESTAMP_SECONDS
only accepts INT64
, but the result SQL tries to input a divided number (float64).
To Reproduce
Steps to reproduce the behavior:
For v2 case:
import sqlglot
sql = """
SELECT timestamp_millis(timestamp) from ask_question_traces
"""
ast = sqlglot.parse_one(sql, read="bigquery")
bq = ast.sql(dialect="trino")
result = sqlglot.transpile(bq, read="trino", write="bigquery")[0]
print(result)
The result is
SELECT TIMESTAMP_SECONDS(CAST(timestamp AS FLOAT64) / POWER(10, 3)) FROM ask_question_traces
For v3 case:
import sqlglot
sql = """
SELECT timestamp_millis(timestamp) from ask_question_traces
"""
ast = sqlglot.parse_one(sql, read="bigquery")
ir_sql = ast.sql(dialect=None)
print(ir_sql)
result = sqlglot.transpile(ir_sql, read=None, write="bigquery")[0]
print(result)
The result is
SELECT UNIX_TO_TIME(timestamp, 3) FROM ask_question_traces
SELECT TIMESTAMP_MILLIS(timestamp) FROM ask_question_traces
The v3 one is better. There is a only problem for UNIX_TO_TIME
isn't included in our function lists.