VOID column inside STRUCT fails to cast to VARIANT

[Dhruv-22]

Let's say I have the following dataframe

%sql
CREATE OR REPLACE TEMPORARY VIEW v_temp AS
VALUES (NULL, DATE'2025-12-31'), (NULL, DATE'2026-04-12'), (NULL, DATE'2026-06-23') AS T(PAYMENT_DATE, TRANSACTION_DATE);

SELECT PAYMENT_DATE, TYPEOF(PAYMENT_DATE), TRANSACTION_DATE, TYPEOF(TRANSACTION_DATE)
FROM v_temp;

If I try to cast the PAYMENT_DATE column to variant it works fine

But, if I put the column inside a struct and then cast it to variant, it fails.

Casting to string makes the code work. 

Since VOID is castable to VARIANT. Why is it failing?

[balajij8]

Hi Dhruv,

The cast to VARIANT generally fails because complex types (like STRUCT, ARRAY or MAP) require strict, resolvable data types for serialization. When a column is entirely NULL and implicitly defined, Spark infers it as VOID which lacks the representation needed to be packed into a VARIANT object.

You can declare the type explicitly (DATE etc) when constructing the view or DataFrame. If the exact target type is unknown, cast it to a STRING for now and amend the schema later once the data shape is confirmed.

CREATE OR REPLACE TEMPORARY VIEW v_temp AS
VALUES 
  (CAST(NULL AS STRING), DATE'2025-12-31'), 
  (CAST(NULL AS STRING), DATE'2026-04-12'), 
  (CAST(NULL AS STRING), DATE'2026-06-23') 
AS T(PAYMENT_DATE, TRANSACTION_DATE);

It's better to avoid VOID fields as passing VOID types into production pipelines as it introduces technical debt. You can see the other challenges below

• Compatibility Issues -  It has challenges when embedded in complex types for Delta writes and forces defensive casting.

• Type Safety - There is no validation of incoming data. If upstream systems suddenly start sending actual values, silent failures or truncation can occur because the pipeline lacks strict expectations.

• Unclear Intent - Future developers have no idea what the field is supposed to represent - is a NULL column meant to be a date, a string or a numeric type

• Schema Evolution Problems - While VOID can technically widen to any type later on, which one should it become? Different teams might assume different types for their specific needs, turning an eventual schema migration into guesswork.

• Downstream System Failures - External systems rely on strict metadata. BI tools (like Tableau or Power BI) might crash on VOID columns, and ETL pipelines might drop the column breaking integrations.

VOID should never be a deliberate design call. It should exist as a temporary state during early data exploration and must be resolved to a proper data type before hitting production.

[Dhruv-22]

Hi @balajij8 

I know about the drawbacks of VOID and the use of VOID column while converting to VARIANT is not deliberate. It is just that the data for the entire column came empty, so while reading spark converted it to VOID. But that is not my question.

I'm converting the columns into a VARIANT to combine data and store it. Why is it that the conversion is failing?

P.S.

Regarding storage, I'm storing the data with proper schema using other commands like `insertInto` which cast while inserting. This casting is for data quality table where we need to look at entire data once, so I'm combining columns into a struct.

[balajij8 · Accepted solution]

The cast to VARIANT generally fails because complex types (like STRUCT, ARRAY or MAP) require strict, resolvable data types for serialization as given above. You can open a support request if the current behavior need to be fixed.

[AbhilashNagilla · Accepted solution]

Your screenshot already uses TO_VARIANT_OBJECT; the VOID field inside the STRUCT is what causes the failure.

TO_VARIANT_OBJECT requires every field to be convertible to VARIANT, but VOID is not accepted in that nested path. Scalar CAST(PAYMENT_DATE AS VARIANT) works because untyped NULL can be cast directly to VARIANT.

1. If the intended type is known, cast it at the source, for example CAST(NULL AS DATE), then use NAMED_STRUCT to preserve the field name.

2. If the type is unknown, use TO_VARIANT_OBJECT(STRUCT(CAST(PAYMENT_DATE AS VARIANT) AS PAYMENT_DATE, TRANSACTION_DATE)).

3. For unknown or wide schemas, PARSE_JSON(TO_JSON(STRUCT(*), map('ignoreNullFields', 'false'))) handles VOID fields, but converts DATE and TIMESTAMP values to strings.

[Dhruv-22]

Hi @AbhilashNagilla 

Thanks for the suggestions. It helps in ways to work around it.

But also, I'm trying to understand the underlying engine behavior because there seems to be an inconsistency in how VOID is handled:

1. TO_VARIANT_OBJECT accepts VOID in ARRAYs. Here is an example
   
2. Also, even after casting to a different type, the PAYMENT_DATE column is still stored as a VOID type in the variant as seen below.
   

Since Variant natively supports VOID (as shown in both arrays and the final variant schema), why does TO_VARIANT_OBJECT strictly block VOID fields inside a STRUCT?

Is this an intentional schema-validation design choice for Structs?

[AbhilashNagilla]

By the time to_variant_object runs, that array has no VOID element left in it.

ARRAY(PAYMENT_DATE, TRANSACTION_DATE) resolves a common element type across its arguments, so the expression is already ARRAY<DATE>. The check walks array element types, map value types and struct field types against one accepted-type list that excludes the untyped NULL type, and a struct keeps each field's own declared type, so nothing promotes PAYMENT_DATE there.

1. Confirm the coercion: TYPEOF(ARRAY(PAYMENT_DATE, TRANSACTION_DATE)) returns array<date>, while TYPEOF(ARRAY(PAYMENT_DATE)) returns array<void>.

2. Confirm the container is not what decides it: TO_VARIANT_OBJECT(ARRAY(PAYMENT_DATE)) and TO_VARIANT_OBJECT(MAP('k', PAYMENT_DATE)) both fail with the same DATATYPE_MISMATCH.CAST_WITHOUT_SUGGESTION, and TO_VARIANT_OBJECT(ARRAY(INTERVAL '1' SECOND)) fails too, so VOID is not being singled out.

3. Confirm what SCHEMA_OF_VARIANT reports: SCHEMA_OF_VARIANT(TO_VARIANT_OBJECT(ARRAY(CAST(NULL AS DATE)))) returns ARRAY<VOID> even though the input type was ARRAY<DATE>. VARIANT stores one untyped null for every null value, so your cast did apply and the field simply holds that null.

On whether it is deliberate, I can only speak to observed behavior: the exclusion applies uniformly to arrays, maps and structs, and I have not found anything public stating whether leaving the untyped NULL type out of the accepted list was a design decision.