chenghui9527 opened a new issue, #66450: URL: https://github.com/apache/doris/issues/66450
### 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) -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: [email protected] For queries about this service, please contact Infrastructure at: [email protected] --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
