Athena $path / $file_modified_time / $file_size hidden columns are not translated to Spark

[DaxterXS]

version checked 30.10.0

import sqlglot

print(sqlglot.transpile('SELECT "$path" FROM t', read="athena", write="spark")[0])
actual: SELECT $path FROM t
expected: SELECT INPUT_FILE_NAME() FROM t

print(sqlglot.transpile('SELECT substring("$path", -24, 24) FROM t', read="athena", write="spark")[0])
actual: SELECT SUBSTRING($path, -24, 24) FROM t
expected: SELECT SUBSTRING(INPUT_FILE_NAME(), -24, 24) FROM t

print(sqlglot.transpile('SELECT "$file_modified_time" FROM t', read="athena", write="spark")[0])
actual: SELECT $file_modified_time FROM t

Athena (Trino on Presto-backed Hive tables) exposes hidden metadata columns "$path", "$file_modified_time", and "$file_size". When transpiling athena → spark, sqlglot renders "$path" as the backtick-quoted identifier $path, which Spark interprets as a (nonexistent) column reference and fails at runtime with an unresolved-column error.

Spark's equivalent for "$path" is the built-in input_file_name(). The other two have no exact Spark equivalent, but at minimum $path should map to input_file_name().

Expected: "$path" → INPUT_FILE_NAME() when writing to the Spark dialect.

Actual: "$path" → $path (invalid Spark column reference).

[georgesittas]

Thanks for the report.

This is low priority for the core team, but we'll be happy to accept a well-tested & documented PR.