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.
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.
Relevant questions
Invalid format for date
asked a month agoDate_Parse INVALID_FUNCTION_ARGUMENT: Invalid format
asked 3 years agoAthena Query timeout
asked 5 days agoAthena permission error running a query
asked 3 months agoAthena -- Query exhausted resources at this scale factor
asked 3 years agoHow to query .json.gz files from Amazon Athena?
Accepted Answerasked 3 years agoErrorCode: INTERNAL_ERROR_SERVICE
asked 8 days agoAthena error: INVALID_FUNCTION_ARGUMENT Invalid format: ""
asked 4 months agoGENERIC_USER_ERROR for Athena + Dynamodb
asked 25 days agoI cannot use current_date + interval in Athena boto3 query in Lambda
Accepted Answerasked 3 months ago