Summary/Reported Issue
A job/query fails with the following error :
SYSTEM ERROR: GandivaException: Failed to cast the string to int64_t
Relevant Versions
All versions
Troubleshooting Steps
The error returned on the failed query does not automatically advise you of the column name that fails the conversion, it only advises you of the column value that fails. These troubleshooting steps are designed to help you identify the column name that can be used to identify the record containing the problem string value.
1. Identify the failed job in the job history page and click on the "Job ID"
2. Select the "Raw Profile" tab at the top of the page
3. Select the "Error" tab .
4. This will present you with the reported error and details of the internal "Operator" that failed with this error , an example being:
SqlOperatorImpl PROJECT
Location 2:10:2
Fragment 2:0
Where "Location" refers to the PHASE:THREAD:OPERATOR of the plan at the point of failure.
In the example the "Location" tells us PHASE 2: THREAD 10 : OPERATOR 2.
5. Take note of the PHASE and OPERATOR values
6. Select the "Planning" tab
7. Scroll down the page until you reach the "Final Physical Transformation" section. An example being
The Final Physical Transformation identifies each operation by PHASE-OPERATOR notation. In the example in step 4 we had PHASE 2 and OPERATOR 2 so the relevant entry in the Final Physical Transformation section will have an entry labelled "02-02" like so
8. Once you have located the entry in the Final Physical Transformation scroll along the line to identify any columns that are being CAST to an INT or BIGINT. In this example we can see an entry that looks like so:
<column_name>=[CAST($14):BIGINT]
Now review your SQL ( or the parent SQL if this a VDS ) where a CAST is used on this column or where a JOIN on this column and between two different data types occur.
Cause
The error occurs when trying to convert or cast an incompatible STRING to an INT datatype. This conversion may be occurring because of an explicit CAST function in the sql or an implicit CAST by Dremio to satisfy the query.
Steps to Resolve
1. If the identified CAST or JOIN in the SQL suggest compatible types first try and re-run the query using the JAVA codegenerator at the session level by adding the following line at the top of your SQL
ALTER SESSION SET "exec.preferred.codegenerator" = 'java';
<your query>;
If this query is successful it may represent a problem with how gandiva evaluates the expression. Please raise a ticket with Dremio Support for further review.
2. If the query in step 1) still fails, you are likely dealing with a genuine incompatibility between data types on the columns being joined or CAST. To overcome this you will need to select against the underlying tables to identify where column values do not meet expected values. Once identified you would need to clean the data to ensure the CAST or JOIN succeeds.