Athena error: INVALID_FUNCTION_ARGUMENT Invalid format: ""

0

I am troubleshooting a long query with many joins, and getting the error in the title. I've found a part of the query that appears to trigger the error; it's a query that is joined to the rest of tables and subqueries with a CROSS JOIN, as the result is a single row of average values to be attached to every row in the full result set. The first of the averages makes use of to_unixtime(from_iso8601_date(date)) to calculate an average date. This average uses a subquery that explicitly excludes null and empty string values for the calculation of the average. This part of the query runs fine on its own. It's when that result is combined with other averages calculated from columns in the same source table that the error occurs. Originally, this first average and the others were all part of a single SELECT statement inside the CROSS JOIN; I've also tried separating that first average into its own CROSS JOIN, and keeping the rest of the averages (which also run fine on their own) in another CROSS JOIN. Same error though. This full query formerly ran without error; it's only in the last week or two that this has happened.

jsteele
asked 2 years ago5840 views
1 Answer
0

This type of error occurs when an invalid argument is passed to a function. Provided below is an example of how this error occurs.

Correct query:

SELECT date_parse('2010-10-23 00:00:00','%Y-%m-%d %H:%i:%s');
Result:
        _col0
1	2010-10-23 00:00:00.000

Query with error:

SELECT date_parse('2010--23 00:00:00','%Y-%m-%d %H:%i:%s');
Result:

INVALID_FUNCTION_ARGUMENT: Invalid format: "2010--23 00:00:00" is malformed at "-23 00:00:00"

From the error message, it looks like an empty string is being passed to a function. Please check your query to see if there are any such functions being used and the values being passed to them.

If your query was working before and started to fail now, please reach out to to AWS Premium Support with successful and failure Athena query ids.

AWS
SUPPORT ENGINEER
Manu_G
answered 2 years ago
AWS
EXPERT
reviewed 2 years ago

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions