Athena error: INVALID_FUNCTION_ARGUMENT Invalid format: ""
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.
This type of error occurs when an invalid argument is passed to a function. Provided below is an example of how this error occurs.
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.
Invalid format for dateasked a month ago
Date_Parse INVALID_FUNCTION_ARGUMENT: Invalid formatasked 3 years ago
Athena Query timeoutasked 5 days ago
Athena permission error running a queryasked 3 months ago
Athena -- Query exhausted resources at this scale factorasked 3 years ago
How to query .json.gz files from Amazon Athena?Accepted Answerasked 3 years ago
ErrorCode: INTERNAL_ERROR_SERVICEasked 8 days ago
Athena error: INVALID_FUNCTION_ARGUMENT Invalid format: ""asked 4 months ago
GENERIC_USER_ERROR for Athena + Dynamodbasked 25 days ago
I cannot use current_date + interval in Athena boto3 query in LambdaAccepted Answerasked 3 months ago