Based on the behavior you described and the StarRocks 4.1 release notes, the issue you encountered is likely due to internal data layout and metadata changes introduced between version 4.0.5 and 4.1. What caused the problem? The root cause is a mismatch between the physical data layout (transferred via

BACKUP/RESTORE

) and the metadata expectations of the 4.1 engine. 1. Flat JSON Metadata Mismatch: Starting in StarRocks 4.0, Flat JSON is enabled by default. This feature extracts common fields from JSON blobs into optimized internal columnar structures (with their own Zone Maps and indices). 2. Version Incompatibility: The release notes for StarRocks 4.1 explicitly mention that version 4.1 introduces significant changes to internal data layout (including tablet splitting and distribution mechanisms). Specifically, data generated in 4.1 is not fully backward compatible with versions earlier than 4.0.6. 3. The Symptom: When you performed a

RESTORE

from 4.0.5 to 4.1, you effectively moved the physical segments and metadata exactly as they existed in the old layout. Because the 4.1 engine expects the new layout/metadata format: * The query

WHERE metadata IS NULL

likely hit an optimized index or "flattened" sub-column metadata that incorrectly reported a

NULL

state (due to the format mismatch). * A direct query by

id

often forces a more thorough row-level read or bypasses certain metadata-level optimizations, allowing the engine to find the actual data in the original segment. Is

INSERT INTO ... SELECT

a safe resolution? Yes, it is the safest and recommended way to resolve this specific inconsistency. When you run `INSERT INTO new_table SELECT * FROM restored_table`: * Data Re-interpretation: StarRocks reads the data from the restored table, correctly parsing the JSON and rows according to the old layout. * Fresh Writing: It writes that data into the new table using the version 4.1 native format. This process recalculates all internal metadata, refills the "Flat JSON" sub-columns, and rebuilds all Zone Maps and indices according to the 4.1 specifications. Recommendations * Verify with

flat_json_meta

: You can check if the sub-columns are correctly extracted in your new table by running:

sql
    SELECT flat_json_meta(metadata) FROM test_table[_META_];

* Check for 4.1.1+: If you are in a containerized environment, note that StarRocks 4.1.0 had a known "unstable load order" issue; upgrading to 4.1.1 or later is generally recommended for production stability. * Migration Strategy: For future migrations between major versions with layout changes (like 4.0 to 4.1), using StarRocks'

DataX

connector or

INSERT INTO ... SELECT

(cross-cluster) is often more robust than

BACKUP/RESTORE

, as it ensures data is written natively into the target version's format. References • using_starrocks/Flat_json.md • administration/management/BE_configuration.md