UNPIVOT Function Returns Fewer Rows or Columns Than Expected in Dremio

Summary

When using the UNPIVOT function in Dremio to transform columns into rows, users may observe that only a subset of the columns listed in the IN clause appear in the output. This behavior can appear misleading, especially when changing the order of columns in the IN list seems to affect which values are returned.

This is not a limitation of the UNPIVOT implementation but expected behavior related to how UNPIVOT handles NULL values, data type compatibility, and query structure.

Relevant Versions

All Dremio Active Versions

Symptoms

• UNPIVOT output contains fewer rows or columns than expected.

• Only some elements from the IN clause appear in results.

• Reordering columns in the IN clause changes which values are returned.

• Rows with all NULL values disappear from the output.

• Errors such as:

Incompatible precision and scale (common precision is greater than 38 digits)

Oracle ORA-00923: FROM keyword not found where expected

• Unexpected behavior when combining UNPIVOT with nested SELECT statements or aliases.
   

Cause

The observed behavior is due to a combination of the following factors:

1. Default UNPIVOT behavior excludes NULL values
   By default, UNPIVOT drops any row/column pairs where the value is NULL. This can make it appear as though only the “first” columns are being returned, when in reality only the first non-null columns are included.

2. Decimal type incompatibility
   When columns involved in UNPIVOT have differing decimal precisions or scales, Dremio may not unify them, resulting in precision-related errors.

3. Query structure limitations
   Dremio does not support placing an alias or additional SELECT ... FROM wrapper between a subquery and the UNPIVOT clause. Doing so can lead to SQL parsing errors.

Troubleshooting Steps

• Check whether columns listed in the UNPIVOT IN (...) clause contain NULL values.

• Review decimal precision and scale for all columns being unpivoted.

• Confirm that UNPIVOT is applied directly to the subquery without an intermediate alias or wrapper.

• Test whether UNPIVOT INCLUDE NULLS changes the result set.

Steps to Resolve

1. Include NULL Values Explicitly

To preserve rows where values are NULL, use:

UNPIVOT INCLUDE NULLS (
[value-column-name] FOR [name-column-name] IN ([column1], [column2], ...)
)

This ensures all specified columns appear in the output, even when their values are NULL.

2. Normalize Decimal Precision

Ensure all columns involved in UNPIVOT use a common decimal precision and scale:

CAST( Column ) AS DECIMAL(15,2)) 

3. Use UNION ALL for Predictable Results

For complex transformations or when consistent row output is required, UNION ALL may be preferable:

• Preserves all rows, including NULL values

• Avoids UNPIVOT alias and precision limitations

• Provides more predictable behavior for complex aggregations

Best Practices

• Use UNPIVOT INCLUDE NULLS when missing rows are unacceptable.

• Ensure all unpivoted columns share the same data type and precision.

• Avoid relying on column order in the IN clause to control output.

• Prefer UNION ALL for complex or critical reporting workflows where determinism is required.

• Reserve UNPIVOT for simpler, well-controlled datasets.

Additional Resources

 UNPIVOT Documentation