HOW TO: Resolve Casting Errors in UNION queries with NULL and String Concatenation – Dremio Support

Summary

You may encounter a casting error when running UNION queries that involve string concatenation and NULL values. This occurs when NULL is not explicitly cast to a compatible type, leading to a type mismatch between UNION branches.

Relevant Versions

All supported versions of Dremio (Cloud and on-prem)

Troubleshooting Steps

In Dremio, all branches of a UNION must return the same column types.
When using the string concatenation operator ||, Dremio promotes all operands to VARCHAR, so the resulting column type is always VARCHAR.
If one UNION branch includes a NULL without an explicit cast, Dremio cannot reliably infer its type and throws a casting or type mismatch error.

Cause

Uncast NULL values in UNION queries introduce ambiguity. Dremio cannot guarantee consistent output types unless all branches return explicitly typed values. This is especially critical when other branches involve expressions that implicitly return VARCHAR (e.g., CAST(... AS VARCHAR) || ':' || LPAD(...)).

Steps to Resolve

Always explicitly cast NULL values in UNION queries to ensure consistent column types across all branches.

Working Example:

SELECT 
 CAST(TRUNC(3262279 / 60) AS VARCHAR) || ':' || 
 LPAD(CAST(MOD(3262279, 60) AS VARCHAR), 2, '0') AS TA
UNION
SELECT 
 CAST(TRUNC(3262279 / 60) AS VARCHAR) || ':' || 
 LPAD(CAST(MOD(3262279, 60) AS VARCHAR), 2, '0') AS TA
UNION
SELECT CAST(NULL AS VARCHAR) AS TA;

Incorrect (Fails Due to Uncast NULL):

SELECT 
 CAST(TRUNC(3262279 / 60) AS VARCHAR) || ':' || 
 LPAD(CAST(MOD(3262279, 60) AS VARCHAR), 2, '0') AS TA
UNION
SELECT 
 CAST(TRUNC(3262279 / 60) AS VARCHAR) || ':' || 
 LPAD(CAST(MOD(3262279, 60) AS VARCHAR), 2, '0') AS TA
UNION
SELECT NULL AS TA;

This version fails because Dremio cannot infer the type of the NULL value, resulting in a type mismatch.

Next Steps

Review any UNION queries that include NULL values.
Ensure all NULLs are explicitly cast to the expected type (e.g., CAST(NULL AS VARCHAR)).
For complex views or nested queries, verify column consistency at each layer of the query.

Additional Resources

https://docs.dremio.com/current/reference/sql/sql-functions/functions/CAST/

https://docs.dremio.com/current/data-products/develop/#handle-invalid-empty-and-null-values