[Bug] Routine Load + partial_columns=true updates missing JSON fields to NULL when using jsonpaths

### Search before asking

- [x] I had searched in the [issues](https://github.com/apache/doris/issues?q=is%3Aissue) and found no similar issues.

### Version

4.0.11

### What's Wrong?

---

# Title

**Routine Load + partial_columns=true updates missing JSON fields to NULL when using jsonpaths**

---

# Environment

```text
Doris Version:
Apache Doris 4.0.11

Table Type:
UNIQUE KEY
Merge-On-Write (enable_unique_key_merge_on_write=true)

Load Type:
Routine Load

Data Format:
JSON

Properties:
partial_columns=true
```

---

# Problem Description

We are using **Routine Load** with **partial_columns=true** to perform partial updates into a UNIQUE KEY Merge-On-Write table.

Our expectation is:

> If a field is **not present** in the incoming JSON, that column should **not participate in the partial update**.

However, the actual behavior is:

> If a field is declared in `jsonpaths` but is absent in the JSON message, Doris converts it to `NULL`, and this `NULL` value overwrites the existing value in the target table.

We would like to confirm whether this behavior is **expected by design** or a **bug**.

---

# Table Definition

```sql
CREATE TABLE data_wework_customer (
device_id VARCHAR(64),
profile_id VARCHAR(128),
owner_wework_id VARCHAR(128),
customer_key VARCHAR(64),
...
last_contact_time DATETIME,
update_time DATETIME,
is_deleted INT
)
UNIQUE KEY(device_id, profile_id, owner_wework_id, customer_key)
PROPERTIES (
"enable_unique_key_merge_on_write" = "true"
);
```

---

# Routine Load

```sql
CREATE ROUTINE LOAD customer
ON data_wework_customer
COLUMNS(
device_id,
profile_id,
owner_wework_id,
customer_key,
display_name,
avatar_url,
nickname,
remark,
description,
description_image,
phone,
label_ids,
label_names,
source_code,

temp_add_time,
add_time = FROM_MILLISECOND(temp_add_time),

temp_first_sync_time,
first_sync_time = FROM_MILLISECOND(temp_first_sync_time),

temp_last_sync_time,
last_sync_time = FROM_MILLISECOND(temp_last_sync_time),

temp_create_time,
create_time = FROM_MILLISECOND(temp_create_time),

temp_update_time,
update_time = FROM_MILLISECOND(temp_update_time),

last_contact_time,

is_deleted
)
PROPERTIES(
"format"="json",
"partial_columns"="true",
"jsonpaths"='[
"$.deviceId",
"$.profileId",
"$.ownerWeworkId",
"$.customerKey",
"$.displayName",
"$.avatarUrl",
"$.nickname",
"$.remark",
"$.description",
"$.descriptionImage",
"$.phone",
"$.labelIds",
"$.labelNames",
"$.sourceCode",
"$.addTime",
"$.firstSyncTime",
"$.lastSyncTime",
"$.createTime",
"$.updateTime",
"$.lastContactTimeStr",
"$.isDeleted"
]'
);
```

---

# Test Data

Existing row:

```text
last_contact_time = 2026-08-01 10:00:00
```

Incoming JSON:

```json
{
"deviceId":"2355201980776448",
"profileId":"3F4071247BDD4AE3507D7C9EECCEAB38",
"ownerWeworkId":"1688855530802795",
"customerKey":"7881301504069950",
"displayName":"xxx",
"updateTime":1785850560048,
"isDeleted":0
}
```

Notice that

```text
lastContactTimeStr
```

is **completely absent**.

---

# Expected Behavior

Since

```text
lastContactTimeStr
```

does not exist in the JSON,

we expect

```text
last_contact_time
```

to **not participate in this partial update**, and therefore the existing value should remain unchanged.

Example:

```text
before

last_contact_time = 2026-08-01 10:00:00

after

last_contact_time = 2026-08-01 10:00:00
```

---

# Actual Behavior

The missing JSON field is converted into

```text
NULL
```

through jsonpaths.

Eventually Doris performs

```text
last_contact_time = NULL
```

and overwrites the existing value.

Result:

```text
before

2026-08-01 10:00:00

after

NULL
```

---

# Additional Investigation

We performed several experiments.

## Experiment 1

Originally we used

```sql
last_contact_time = FROM_MILLISECOND(temp_last_contact_time)
```

We suspected `FROM_MILLISECOND()` might be the reason.

To verify, we changed it to

```sql
last_contact_time =
IF(
temp_last_contact_time IS NULL,
'2099-01-01 00:00:00',
FROM_MILLISECOND(temp_last_contact_time)
)
```

The result became

```text
2099-01-01
```

This proves

```text
temp_last_contact_time == NULL
```

when the JSON field is absent.

---

## Experiment 2

We completely removed

```sql
FROM_MILLISECOND(...)
```

and changed the Routine Load to

```sql
last_contact_time
```

without any expression.

The overwrite still occurred.

Therefore,

the issue is **not related to FROM_MILLISECOND()**.

---

# Our Understanding

It appears the execution flow is

```text
JSON

↓

jsonpaths

↓

missing field

↓

NULL

↓

Output Row

↓

partial update

↓

overwrite existing value
```

In other words,

`partial_columns=true` seems to update the columns generated by `jsonpaths/COLUMNS`, rather than only the fields that actually exist in the incoming JSON.

---

# Questions

Could you please clarify:

1. Is this behavior expected by design?

2. With `partial_columns=true`, should a missing JSON field participate in the partial update?

3. Is there any supported way to skip updating a column when its JSON field is absent while still using Routine Load + jsonpaths?

4. Is `UPDATE_FLEXIBLE_COLUMNS` the only supported solution for this scenario?

---

## We can reproduce this behavior **100% consistently** on Doris 4.0.11 with the SQL and JSON shown above. If needed, we can also provide a minimal reproducible example.

### What You Expected?

fix

### How to Reproduce?

_No response_

### Anything Else?

_No response_

### Are you willing to submit PR?

- [ ] Yes I am willing to submit a PR!

### Code of Conduct

- [x] I agree to follow this project's [Code of Conduct](https://www.apache.org/foundation/policies/conduct)

Start by running the Routine Load SQL and JSON reproduction from the issue on Doris 4.0.11, then trace the jsonpaths/COLUMNS handling into partial_columns updates. Done means determining whether an absent JSON field should preserve the existing value and identifying the supported behavior or fix for this reproducible case.

Written by the indexing model from the issue text.