Column Default Propagation

[meret]

Hi 🙂 Today I found I somewhat strange behavior when it comes to default values in columns. Apparently, column defaults are propagated to a new table, when you select the column without any operation on it. This is a bit unexpected for me. Here a short example:

I have a table where one of the columns has a default:
create or replace table schema.test (
  id int default 0,
  name string
)
TBLPROPERTIES('delta.feature.allowColumnDefaults' = 'supported') -- needed as otherwise we can not define defaults
;

I insert some data and the use this as a basis for a new table:
create or replace table schema.test2
as
(select id, name from schema.test);

this fails with the following error: [WRONG_COLUMN_DEFAULTS_FOR_DELTA_FEATURE_NOT_ENABLED] Failed to execute CREATE TABLE command because it assigned a column DEFAULT value, but the corresponding table feature was not enabled. Please retry the command again after executing ALTER TABLE tableName SET TBLPROPERTIES('delta.feature.allowColumnDefaults' = 'supported').

When I enable the table properties it works:
create or replace table schema.test2
TBLPROPERTIES('delta.feature.allowColumnDefaults' = 'supported')
as (select id, name from schema.test);
in this case the default will also be set on the id column of the new table, even though I have not explicitly set this.

or when I do some operation on the column it works also without setting the table properties:
create or replace table schema.test2
as
(select cast(id as int), name from schema.test);

is this expected behavior? Is there a way to disable this?

[BigRoux]

The behavior you described regarding the propagation of default column values is expected and is tied to the specific usage of the delta.feature.allowColumnDefaults table property in Delta Lake. Here’s an explanation:

1. Default Propagation Without Transformation: When creating a table (schema.test2) via a CREATE TABLE AS SELECT (CTAS) query, default column values from the source table (schema.test) are transferred to the new table if the column is directly selected without applying transformations or type changes. This is why the id column in the new table retains its default value. However, for this to work, the delta.feature.allowColumnDefaults property must be enabled on the target table.
2. Error Without TBLPROPERTIES Set: If the target table does not have the delta.feature.allowColumnDefaults property explicitly set to 'supported', the CTAS operation fails with the error stating: Failed to execute CREATE TABLE command because it assigned a column DEFAULT value, but the corresponding table feature was not enabled.<ref id="doc_1"/><ref id="doc_2"/>
3. Bypassing Default Propagation with Transformations: If any operation (e.g., CAST(id AS INT)) is applied to the column in the SELECT statement, the default value does not propagate. As a result, the delta.feature.allowColumnDefaults property does not need to be enabled, and the CTAS operation succeeds without the error.

Can This Behavior Be Changed or Disabled?

Unfortunately, there isn’t a feature to disable this propagation behavior at the query level. However, you can handle this by: - Applying transformations to the columns being selected, as this avoids default propagation. - Preemptively ensuring the delta.feature.allowColumnDefaults property is enabled if maintaining defaults is the intended behavior for the target table.

This behavior is a design consideration in Delta Lake to ensure consistency between source and target schemas while using defaults when explicitly specified via the feature. Let me know if you'd like more information!

Hope this helps.  Big Roux.