Upgrading runtime 10.4 to 11.3 causing errors in my code (CASTING issues?)

[biafch]

Hi all,

We have our medallion architecture transformation on databricks.

Im currently testing upgrading to 11.3 as 10.4 won't be supported anymore from March 2025.

However, I keep getting errors like this:

• Error inserting data into table. Type AnalysisException: cannot resolve 'CAST(v_staging_silverinsert_silver_docusign_docusign.RawDataStartDate AS BOOLEAN)' due to data type mismatch: cannot cast timestamp to boolean with ANSI mode on.
• [CAST_INVALID_INPUT] The value 'Default' of the type "STRING" cannot be cast to "BOOLEAN" because it is malformed.

I don't understand why this is happening, it seems to happen on this piece of code:

Raw.createOrReplaceTempView("v_staging_silverInsert_{0}_{1}".format(TargetSchema,RawObject))

  print('Inserting data into table: {0}.{1}'.format(TargetSchema,RawObject))

  try:

    sqlstmt = 'INSERT INTO {0}.{1} select * from v_staging_silverInsert_{0}_{1}'.format(TargetSchema,RawObject)

    sqlContext.sql(sqlstmt)

    Continue = False

    WriteDeletes = False

    print('Silver has been written. Continue : {0}'.format(str(Continue)))

    IsFinal = 'True'

  except Exception as e:

    hasError = True

    Continue = False

    Exception_type = type(e).__name__

    ErrorMessages = '** Error inserting data into table. Type {}: {}'.format(Exception_type,e)

    print(ErrorMessages)

    Logging_Error(ErrorMessages)

What I don't understand is. I'm never casting these fields. So why is it that on databricks runtime 11.3 it tries to cast these fields?

I tried turning off the ANSI enforcement but unfortunately that is causing me other issues.

[Alberto_Umana]

Hello @biafch,

The error you are encountering is due to the DBR 11.3 enforcing stricter type casting rules under ANSI mode. Specifically, the error message indicates that there is an attempt to cast a timestamp to a boolean, which is not allowed.

Here are a few steps you can take to resolve this issue:

1. Identify the Source of the Cast: Review your code and the schema of the tables involved to identify where the timestamp might be incorrectly cast to a boolean. This could be happening implicitly in your SQL statements or transformations.
2. Check for Implicit Casts: Ensure that there are no implicit casts happening in your SQL statements. For example, if you have any expressions that might be causing the timestamp to be interpreted as a boolean, you need to correct them.
3. Review Data Types: Verify the data types of the columns in your source and target tables. Ensure that the data types are consistent and that no timestamp columns are being used in a context where a boolean is expected.
4. Use try_cast: If you need to handle malformed data gracefully, consider using try_cast instead of CAST. This function will return NULL for invalid casts instead of throwing an error.

Here is an example of how you might modify your SQL statement to use try_cast:

SELECT try_cast(RawDataStartDate AS BOOLEAN) AS RawDataStartDateBoolean

FROM v_staging_silverInsert_{0}_{1}

[biafch]

Hi @Alberto_Umana 

Thank you for your response.

That's the weird thing. The RawDataStartDate only consists of records with datetime stamps. Furthermore I am nowhere in my code casting anything of this to a boolean, or casting anything at all. All I am literally doing is creating a temp table from the raw layer, dropping the silver layer and inserted data from the temp table into the silver layer.

Upon checking the schema of all tables the fieldtype remains a timestamp field. So I just don't understand why I'm getting the error of that it cannot convert timestamp to a boolean while im never casting/converting a timestamp to a boolean field...

Do you have any other suggestions?