apache / apache/gluten

[VL] ALTER TABLE ADD COLUMN with DEFAULT returns NULL for existing data instead of default value

Open
#11,494 2 comments 0 reactions 0 assignees View on GitHub
bug
Dominant language
Scala
Stars
1.6k
Forks
657
Avg merge
2d 14h
Merged PRs (30d)
80

Description

### Backend

VL (Velox)

### Bug description

**Expected behavior**: When using `ALTER TABLE ADD COLUMN` with a DEFAULT value, existing rows should return the default value for the newly added column when queried (via EXISTS_DEFAULT lazy backfill mechanism).

**Actual behavior**: Existing rows return `NULL` for the newly added column instead of the configured default value.

This affects the EXISTS_DEFAULT lazy backfill mechanism introduced in Spark 3.4.0 (SPARK-38334). While newly inserted data (after ALTER) correctly uses default values, old data (inserted before ALTER) returns NULL.

### Reproduction Steps

```scala
// Test case from GlutenInsertSuite
testGluten("ALTER ADD COLUMN DEFAULT - old INT data backfill") {
withTable("t") {
sql("create table t(a int) using parquet")

// Insert OLD data BEFORE ALTER
sql("insert into t values(1)")
sql("insert into t values(2)")

// ALTER TABLE ADD COLUMN with DEFAULT
sql("alter table t add column (b int default 99)")

// Query old data
spark.table("t").orderBy("a").show()
}
}
```

**Expected Result**:
```
+---+---+
| a| b|
+---+---+
| 1| 99|
| 2| 99|
+---+---+
```

**Actual Result**:
```
+---+----+
| a| b|
+---+----+
| 1|null|
| 2|null|
+---+----+
```

### Test Failure Output

```
== Results ==
!== Correct Answer - 2 == == Spark Answer - 2 ==
!struct<> struct
![1,99] [1,null]
![2,99] [2,null]
```

All 5 test cases in GlutenInsertSuite fail with the same pattern:
1. `ALTER ADD COLUMN DEFAULT - old INT data backfill` - returns NULL instead of 99
2. `ALTER ADD COLUMN DEFAULT - old STRING data backfill` - returns NULL instead of 'unknown'
3. `ALTER ADD COLUMN DEFAULT - old BOOLEAN data backfill` - returns NULL instead of true
4. `ALTER ADD COLUMN DEFAULT - old DATE data backfill` - returns NULL instead of date '2023-01-01'
5. `ALTER ADD COLUMN DEFAULT - multiple types old data backfill` - returns NULL for all default columns

### Gluten version

main branch

### Spark version

Spark-3.4.x, Spark-3.5.x, Spark-4.0.x, Spark-4.1.x

---

*This issue was generated with AI assistance (Claude Code).*

Contributor guide

Open the contributing guide

Research direction

Start with the five ALTER ADD COLUMN DEFAULT cases in GlutenInsertSuite and reproduce the mismatch between old and newly inserted rows. Trace the EXISTS_DEFAULT lazy backfill behavior through the Velox backend, comparing the failing old-data path with the working new-data path. Done means all listed types return their configured defaults for rows inserted before ALTER.

Written by the indexing model from the issue text.

Assessment

Tech stack
scala, spark
Domain
backend, databases
Issue type
Bug
Difficulty
4/5
Estimated time
3-5 days
Activity status
Stale
Clarity
Mostly clear
Newbie friendliness
42/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.