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

[chenghui9527]

Search before asking

• I had searched in the issues 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

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

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

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:

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

Incoming JSON:

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

Notice that

lastContactTimeStr

is completely absent.

---

Expected Behavior

Since

lastContactTimeStr

does not exist in the JSON,

we expect

last_contact_time

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

Example:

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

NULL

through jsonpaths.

Eventually Doris performs

last_contact_time = NULL

and overwrites the existing value.

Result:

before

2026-08-01 10:00:00

after

NULL

---

Additional Investigation

We performed several experiments.

Experiment 1

Originally we used

last_contact_time = FROM_MILLISECOND(temp_last_contact_time)

We suspected FROM_MILLISECOND() might be the reason.

To verify, we changed it to

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

2099-01-01

This proves

temp_last_contact_time == NULL

when the JSON field is absent.

---

Experiment 2

We completely removed

FROM_MILLISECOND(...)

and changed the Routine Load to

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

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

• I agree to follow this project's Code of Conduct

[Doris-Breakwater]

Breakwater-GitHub-Analysis-Slot: slot_a59f1916a612

Preliminary assessment

Classification: expected behavior of fixed-column partial update, not evidence of a 4.0.11 regression. Confidence: high. The issue currently has no labels.

partial_columns=true is the backward-compatible spelling of unique_key_update_mode=UPDATE_FIXED_COLUMNS; the 4.0 Routine Load analyzer maps it explicitly to that mode (source). In fixed-column mode, the COLUMNS clause defines one update-column set for the whole load job/batch. It is not recalculated from the keys present in each JSON object.

The JSON reader then processes every declared JSONPath. When a path does not match, it calls _fill_missing_column (source); for a nullable column with no configured default, that function inserts the nullable default, i.e. NULL (source). Because last_contact_time is part of the job's fixed update set, that NULL participates in the update. This also explains why removing FROM_MILLISECOND() did not change the result and why replacing it with IF(... IS NULL, '2099-01-01', ...) wrote the sentinel: a load expression produces a value; it cannot produce the per-row "do not update this column" state.

This matches the documented distinction: fixed partial update requires every row in a load to update the same columns, while flexible partial update determines the columns from each JSON row (column-update documentation).

Answers to the questions

1. Yes, this is expected under partial_columns=true / UPDATE_FIXED_COLUMNS.
2. Yes, a column included in the fixed import mapping participates even when its JSONPath is absent. For a nullable column without a configured default, the extracted value is NULL.
3. There is no supported way in one fixed-column Routine Load job to keep jsonpaths and make a mapped column conditional on per-message field presence. Removing last_contact_time from both the input mapping and the update set makes that job never update the column, but cannot provide "update only when present" semantics. An IF expression cannot preserve the old value because it has no access to or skip marker for that stored value.
4. For one Routine Load job whose rows have different update-column sets, UPDATE_FLEXIBLE_COLUMNS is the supported mode. It records absent fields in a skip bitmap. Explicit JSON null remains an update to NULL; an absent key is skipped. If flexible mode cannot be used, the practical supported alternative is to route records with different fixed schemas to separate topics/jobs, with each job declaring only its own fixed update columns.

Important migration constraints

Routine Load intentionally rejects UPDATE_FLEXIBLE_COLUMNS when either jsonpaths or a COLUMNS clause is present (validation). Therefore the shown job cannot simply change one property:

• The table must be a Merge-on-Write Unique Key table with light schema change and the hidden skip-bitmap column, and it must not contain a VARIANT column. For an eligible existing table, the analyzer suggests ALTER TABLE data_wework_customer ENABLE FEATURE "UPDATE_FLEXIBLE_COLUMNS"; (table validation). Verify completion with SHOW CREATE TABLE.
• Recreate the Routine Load job with "format"="json" and "unique_key_update_mode"="UPDATE_FLEXIBLE_COLUMNS", without partial_columns, jsonpaths, COLUMNS, or fuzzy_parse.
• Every JSON object must contain all non-auto-increment key columns. JSON key names must directly match Doris column names, and values must already be load-compatible. In this case, the producer/upstream transformation would need to convert camelCase names to the table's snake_case names and convert millisecond timestamps to values accepted by the target DATETIME columns, because the current JSONPath renaming and FROM_MILLISECOND mappings cannot be retained in flexible mode.

Missing information / maintainer next step

No BE log, load profile, or Doris query profile is needed to establish the current semantics. Before prescribing an exact migration, please provide:

• the unabridged SHOW CREATE TABLE data_wework_customer output, to verify the skip-bitmap/light-schema-change prerequisites and absence of unsupported table features;
• SHOW CREATE ROUTINE LOAD customer (or the complete actual job definition), because the issue's experiment mentions temp_last_contact_time while the displayed definition maps last_contact_time directly;
• the full SELECT VERSION() output including the build/Git commit if exact binary-to-source verification is desired.

The available public source has no 4.0.11 tag, so the code inspection above used public branch-4.0 commit 68261b8583982d09a8129556acdfdb457e132dba dated 2026-08-01. The reported behavior is fully consistent with that implementation. I recommend maintainers answer this as a documented fixed-vs-flexible mode distinction and close it as expected behavior; if support for per-row omission together with JSONPath renaming/load expressions is desired, that should be tracked as a separate enhancement because the current analyzer and reader explicitly exclude that combination.