http://starrocks.io logo
Join Slack
Powered by
l
@Rocky hello
r
Hello! I'm Rocky. How can I help you with StarRocks today?
l
@Rocky INSERT INTO dwh.af_extract_mapping (ID, PROJECT_NAME, AF_SRC_CONN_ID, SRC_SCHEMA, SRC_TABLE, AF_DEST_CONN_ID, DEST_SCHEMA, DEST_TABLE, DEST_PRE_SQL, DEST_CHECKPOINT_SQL, SRC_EXTRACT_SQL, DEST_POST_SQL, DESCRIPTION, STATUS_FLAG, DEST_STAGING_TABLE, BATCH_SIZE, DEPENDS_ON) *VALUES*((SELECT *IFNULL*(*MAX*(ID), 228) + 1 FROM dwh.af_extract_mapping) , 'DMAPP_DATA_02', 'DTM_PRO', 'QTDM', 'CIC_SKIPPING_RISK_INDICATORS_AVC', 'SR_DMAPP_PRO', 'qtdm', 'CIC_SKIPPING_RISK_INDICATORS_AVC', 'truncate table qtdm.CIC_SKIPPING_RISK_INDICATORS_AVC', NULL, 'SELECT * FROM QTDM.CIC_SKIPPING_RISK_INDICATORS_AVC', NULL, NULL, 1, NULL, 100000, NULL); SQL Error [1064] [42000]: Required field 'node_type' was not present! Struct: TExprNode(node_type:null, type:TTypeDesc(types:[TTypeNode(type:SCALAR, scalar_type:TScalarType(type:INVALID_TYPE))]), num_children:0, output_scale:-1, has_nullable_child:false, is_nullable:true, is_monotonic:false, is_index_only_filter:false) backend [id=1567985] [ Required field 'node_type' was not present! Struct: TExprNode(node_type:null, type:TTypeDesc(types:[TTypeNode(type:SCALAR, scalar_type:TScalarType(type:INVALID_TYPE))]), num_children:0, output_scale:-1, has_nullable_child:false, is_nullable:true, is_monotonic:false, is_index_only_filter:false) backend [id=1567985] [
r
The error 
SQL Error [1064] [42000]: Required field 'node_type' was not present!
 occurs because StarRocks does not support scalar subqueries within the 
VALUES
clause of an 
INSERT
statement. In StarRocks, the 
VALUES
clause expects constant expressions or simple functions. When a subquery like 
(SELECT IFNULL(MAX(ID), 228) + 1 ...)
is placed inside 
VALUES
, the query planner fails to serialize the expression for the backend, resulting in the 
INVALID_TYPE
and 
node_type:null
errors you see in the Thrift (
TExprNode
) structure. Solution: Use 
INSERT INTO ... SELECT
 To fix this, you should rewrite your 
INSERT
statement to use the 
SELECT
syntax. This allows you to combine the results of a subquery with constant values in a single row. Rewritten SQL:
Copy code
sql
INSERT INTO dwh.af_extract_mapping 
(
    ID, PROJECT_NAME, AF_SRC_CONN_ID, SRC_SCHEMA, SRC_TABLE, 
    AF_DEST_CONN_ID, DEST_SCHEMA, DEST_TABLE, DEST_PRE_SQL, 
    DEST_CHECKPOINT_SQL, SRC_EXTRACT_SQL, DEST_POST_SQL, 
    DESCRIPTION, STATUS_FLAG, DEST_STAGING_TABLE, BATCH_SIZE, DEPENDS_ON
)
SELECT 
    IFNULL(MAX(ID), 228) + 1,              -- Calculated ID
    'DMAPP_DATA_02',                       -- Constant values begin here
    'DTM_PRO', 
    'QTDM', 
    'CIC_SKIPPING_RISK_INDICATORS_AVC', 
    'SR_DMAPP_PRO', 
    'qtdm', 
    'CIC_SKIPPING_RISK_INDICATORS_AVC', 
    'truncate table qtdm.CIC_SKIPPING_RISK_INDICATORS_AVC', 
    NULL, 
    'SELECT * FROM QTDM.CIC_SKIPPING_RISK_INDICATORS_AVC', 
    NULL, 
    NULL, 
    1, 
    NULL, 
    100000, 
    NULL 
FROM dwh.af_extract_mapping;
Key Differences: * 
INSERT ... VALUES
: Designed for inserting literal data/constants (mostly for demos or small tests). * 
INSERT ... SELECT
: Designed for inserting data derived from queries or combining subqueries with constants. This is the recommended approach for your use case. For more details on 
INSERT
syntax, see the StarRocks INSERT documentation. References • sql-reference/data-types/semi_structured/STRUCT.md • sql-reference/sql-functions/variant-functions/get_variant.md • administration/management/FE_configuration.md • developers/type_checker_xml_configuration.md
l
@Rocky "INSERT INTO dmapp.dtm_los_approval (id, strategy_decision, strategy_name, strategy_version) SELECT id, -- created_date, -- last_updated_date, strategy_decision, strategy_name, strategy_version FROM losrep.approval WHERE strategy_name IS NOT NULL AND strategy_name <> '' AND ((created_date > DATE_SUB(CURDATE(), INTERVAL 49 HOUR)) or (last_updated_date > DATE_SUB(CURDATE(), INTERVAL 49 HOUR))) AND ((created_date < CURDATE()) and (last_updated_date < CURDATE()))" 10.0.27.71: partial update on table with sort key must provide all sort key columns
r
This error occurs because the target table 
dmapp.dtm_los_approval
is a Primary Key table with a Sort Key (
ORDER BY
clause) that includes columns not present in your 
INSERT
statement. In StarRocks (v3.0+), Primary Key tables decouple the Primary Key from the Sort Key. When you perform a partial update (by inserting into only a subset of columns), StarRocks requires that all columns defined in the Sort Key must be included in the write operation. This is because the Sort Key determines the physical storage order and prefix index of the data. How to fix it 1. Identify the Sort Key columns Run the following command to see which columns are part of the 
ORDER BY
clause:
Copy code
sql
SHOW CREATE TABLE dmapp.dtm_los_approval;
Look for the 
ORDER BY (...)
section at the bottom of the output. 2. Include the missing Sort Key columns Update your 
INSERT
statement to include those columns. For example, if 
created_date
and 
last_updated_date
are part of the Sort Key, you must include them in the column list:
Copy code
sql
INSERT INTO dmapp.dtm_los_approval (
    id, 
    strategy_decision, 
    strategy_name, 
    strategy_version,
    created_date,     -- Add missing sort key column
    last_updated_date -- Add missing sort key column
) 
SELECT 
    id, 
    strategy_decision, 
    strategy_name, 
    strategy_version,
    created_date,     -- Select the value from source
    last_updated_date -- Select the value from source
FROM losrep.approval 
WHERE ...
Why is this required? * Row Mode vs. Column Mode: By default, 
INSERT INTO ... SELECT
uses "row mode" for partial updates. In this mode, StarRocks needs the sort key values to correctly place and index the new version of the row. * Decoupled Keys: If your table was created with something like 
PRIMARY KEY (id) ORDER BY (created_date)
, the 
id
is used for uniqueness, but 
created_date
is used for sorting. You cannot update the row without providing the information that dictates its position in storage. Alternative: Column Mode (If supported) If you are using StarRocks v3.1 or later, you can sometimes use Column Mode for partial updates, which has fewer restrictions on sort keys but is typically designed for batch updates of a few columns across many rows. However, for standard 
INSERT
statements, the simplest and most reliable fix is to include the sort key columns. For more details, see the Primary Key Table documentation. References • table_design/table_types/primary_key_table.md • loading/Load_to_Primary_Key_tables.md • sql-reference/sql-statements/data-definition/CREATE_TABLE.md References • table_design/table_types/primary_key_table.md • knowledge/usage/best-practice.md • loading/Load_to_Primary_Key_tables.md • deployment/shared_data/feature-support-shared-data.md
l
@Rocky -- dmapp.dtm_los_approval definition CREATE TABLE 
dtm_los_approval
( 
id
varchar(20) NOT NULL COMMENT "", 
app_code
varchar(20) NULL COMMENT "", 
sys_run_date
datetime NULL COMMENT "Ngày chạy hệ thống", 
app_uid
varchar(50) NULL COMMENT "", 
number_loan_at_credit_company
decimal(3, 0) NULL COMMENT "", 
number_loan_at_bank
decimal(3, 0) NULL COMMENT "", 
total_number_loan_at_ci
decimal(3, 0) NULL COMMENT "", 
loan_amount_at_credit_company
decimal(18, 2) NULL COMMENT "", 
loan_amount_at_bank
decimal(18, 2) NULL COMMENT "", 
total_loan_amount_at_ci
decimal(18, 2) NULL COMMENT "", 
total_score
varchar(10) NULL COMMENT "", 
rating
varchar(10) NULL COMMENT "", 
cut_off
varchar(10) NULL COMMENT "", 
decision
varchar(50) NULL COMMENT "", 
decision_lbl
varchar(250) NULL COMMENT "", 
reason_reject
varchar(50) NULL COMMENT "", 
reason_reject_lbl
varchar(250) NULL COMMENT "", 
reason_reject_other
varchar(2000) NULL COMMENT "", 
reason_reject_detail
varchar(250) NULL COMMENT "", 
reason_reject_detail_lbl
varchar(250) NULL COMMENT "", 
loan_amount
decimal(18, 2) NULL COMMENT "", 
insurance_fee
decimal(18, 2) NULL COMMENT "", 
loan_consumer
decimal(18, 2) NULL COMMENT "", 
monthly_payment
varchar(50) NULL COMMENT "", 
loan_term
varchar(50) NULL COMMENT "", 
credit_history
varchar(50) NULL COMMENT "", 
credit_history_lbl
varchar(100) NULL COMMENT "", 
dti
decimal(18, 2) NULL COMMENT "", 
pti
decimal(18, 2) NULL COMMENT "", 
created_date
datetime NULL COMMENT "", 
created_by
varchar(50) NULL COMMENT "", 
last_updated_date
datetime NULL COMMENT "", 
last_updated_by
varchar(50) NULL COMMENT "", 
current_emi
decimal(18, 2) NULL COMMENT "", 
suspect_fraud
varchar(2500) NULL COMMENT "", 
reason_suspect_fraud
varchar(2500) NULL COMMENT "", 
reason_suspect_fraud_lbl
varchar(250) NULL COMMENT "", 
fraud_result
varchar(50) NULL COMMENT "", 
fraud_result_lbl
varchar(250) NULL COMMENT "", 
note_for_af
varchar(4000) NULL COMMENT "", 
customer_rank
varchar(100) NULL COMMENT "", 
cbscore_score
varchar(50) NULL COMMENT "", 
cbscore_rank
varchar(50) NULL COMMENT "", 
cbscore_hit_type
varchar(50) NULL COMMENT "", 
re_offer
varchar(50) NULL COMMENT "", 
emi_cic_pcb
decimal(18, 2) NULL COMMENT "", 
customer_main_income
decimal(18, 2) NULL COMMENT "", 
interpolation_income
decimal(18, 2) NULL COMMENT "", 
strategy_decision
varchar(150) NULL COMMENT "Quyết định chiến lược", 
strategy_name
varchar(150) NULL COMMENT "Tên chiến lược", 
strategy_version
varchar(150) NULL COMMENT "Phiên bản chiến lược" ) ENGINE=OLAP PRIMARY KEY(
id
) COMMENT "Thông tin phê duyệt khoản vay LOS" DISTRIBUTED BY HASH(
id
) BUCKETS 8 ORDER BY(
created_date
, 
app_code
) PROPERTIES ( "compression" = "LZ4", "enable_persistent_index" = "true", "fast_schema_evolution" = "true", "replicated_storage" = "true", "replication_num" = "2" );
r
Based on your table definition, the Sort Key is defined as: 
ORDER BY(created_date, app_code)
Because this is a Primary Key table, any 
INSERT
that only updates a subset of columns (a partial update) must include all columns defined in the 
ORDER BY
clause. Your previous query failed because it was missing 
created_date
and 
app_code
. To fix this, you must add these two columns to both the 
INSERT
column list and the 
SELECT
statement: Fixed SQL
Copy code
sql
INSERT INTO dmapp.dtm_los_approval (
    id, 
    strategy_decision, 
    strategy_name, 
    strategy_version,
    created_date,   -- Required: part of Sort Key
    app_code        -- Required: part of Sort Key
) 
SELECT 
    id, 
    strategy_decision, 
    strategy_name, 
    strategy_version,
    created_date,   -- Fetch from source
    app_code        -- Fetch from source
FROM losrep.approval 
WHERE strategy_name IS NOT NULL 
  AND strategy_name <> '' 
  AND (
      (created_date > DATE_SUB(CURDATE(), INTERVAL 49 HOUR)) OR 
      (last_updated_date > DATE_SUB(CURDATE(), INTERVAL 49 HOUR))
  ) 
  AND (
      (created_date < CURDATE()) AND 
      (last_updated_date < CURDATE())
  );
Why this is necessary: In StarRocks Primary Key tables, the 
ORDER BY
columns define how the data is physically sorted on disk and how the prefix index is built. * When performing a partial update (writing to some columns but not all), StarRocks needs the sort key values to correctly locate and re-index the row. * Even if you only want to update the 
strategy_name
, you must provide the 
created_date
and 
app_code
so StarRocks knows where that specific row "lives" in the sorted storage. Note: If 
app_code
is not available in your source table 
losrep.approval
, you will need to join against the existing target table to retrieve it, or ensure it is provided, as StarRocks cannot perform the update without it.
Open in Slack
PreviousNext
Powered by