经过初步的排查,确定问题数据以及相关问题字段:es中id=1790943549378789460的数据,es中相关字段:market_price、settlement_price、suggested_retail_price
备注:该文档中的这三个字段的值 之前都是""空字符,后来改成了null或者0后,还是报错
问题详细描述:
问题sql:
SELECT
id,
IFNULL(product_id, '') AS product_id,
IFNULL(source_system, '') AS source_system,
IFNULL(source_system_id, '') AS source_system_id,
IFNULL(data_type, '') AS data_type,
REPLACE(json_extract_string(sys_create_time, '$.value'), 'T', ' ') AS sys_create_time,
REPLACE(json_extract_string(lastupdate_time, '$.value'), 'T', ' ') AS lastupdate_time,
is_delete AS is_delete ,
IFNULL(app_introduce, '') AS app_introduce ,
IFNULL(aroma_type, '') AS aroma_type,
IFNULL(bottle_box_ratio, '') AS bottle_box_ratio ,
IFNULL(color_des, '') AS color_des ,
IFNULL(market_price, 0) AS market_price ,
IFNULL(mixture_ratio, '') AS mixture_ratio ,
IFNULL(net_content, '') AS net_content ,
IFNULL(old_id, '') AS old_id ,
IFNULL(pc_introduce, '') AS pc_introduce ,
IFNULL(picture, '') AS picture ,
IFNULL(pinlei_name, '') AS pinlei_name ,
IFNULL(prdt_imgs, '') AS prdt_imgs ,
IFNULL(prdt_main_img, '') AS prdt_main_img ,
IFNULL(product_category_code, '') AS product_category_code ,
IFNULL(product_category_id, '') AS product_category_id ,
IFNULL(product_category_name, '') AS product_category_name ,
IFNULL(product_code, '') AS product_code ,
REPLACE(json_extract_string(product_create_time, '$.value'), 'T', ' ') AS product_create_time ,
REPLACE(json_extract_string(product_last_update_time, '$.value'), 'T', ' ') AS product_last_update_time ,
IFNULL(product_name, '') AS product_name ,
IFNULL(product_series, '') AS product_series ,
IFNULL(product_source, '') AS product_source ,
IFNULL(product_spec, '') AS product_spec ,
IFNULL(product_types, '') AS product_types ,
IFNULL(proof, '') AS proof ,
IFNULL(sell_point, '') AS sell_point ,
IFNULL(settlement_price, 0) AS settlement_price ,
IFNULL(store_type_name, '') AS store_type_name ,
IFNULL(suggested_retail_price, 0) AS suggested_retail_price ,
IFNULL(up_state, '') AS up_state ,
IFNULL(up_state_code, '') AS up_state_code
FROM
es_catalog.default_db.product_52
where id = 1790943549378789460;
es中的文档信息:
es中的相关字段类型:
索引为product_52的mapping结构:
{
"product_52": {
"mappings": {
"_doc": {
"properties": {
"240104_import": {
"type": "long"
},
"app_introduce": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword"
}
}
},
"aroma_type": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword"
}
}
},
"bottle_box_ratio": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword"
}
}
},
"box_ratio": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
}
},
"cap_task_id": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
}
},
"capbox_task_id": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
}
},
"caplink_time": {
"type": "long"
},
"code_source": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
}
},
"color_des": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword"
}
}
},
"data_type": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
}
},
"id": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
}
},
"import": {
"type": "long"
},
"in_id": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
}
},
"inter_code": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
}
},
"is_delete": {
"type": "long"
},
"lastupdate_time": {
"properties": {
"day": {
"type": "long"
},
"dayOfWeek": {
"type": "long"
},
"hour": {
"type": "long"
},
"isLastDayOfMonth": {
"type": "long"
},
"month": {
"type": "long"
},
"monthLength": {
"type": "long"
},
"timestamp": {
"type": "long"
},
"value": {
"type": "date"
},
"year": {
"type": "long"
}
}
},
"m_code_type": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
}
},
"market_price": {
"type": "double"
},
"mixture_ratio": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword"
}
}
},
"net_content": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
}
},
"net_weight": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword"
}
}
},
"old_id": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword"
}
}
},
"outer_code": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
}
},
"pc_introduce": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword"
}
}
},
"picture": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword"
}
}
},
"picture_info": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
}
},
"pinlei_code": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
}
},
"pinlei_name": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword"
}
}
},
"prdt_imgs": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword"
}
}
},
"prdt_main_img": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword"
}
}
},
"product_category_code": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword"
}
}
},
"product_category_id": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword"
}
}
},
"product_category_name": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword"
}
}
},
"product_code": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword"
}
}
},
"product_code_17": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
}
},
"product_create_time": {
"properties": {
"date": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
}
},
"day": {
"type": "integer"
},
"dayOfWeek": {
"type": "integer"
},
"hour": {
"type": "integer"
},
"isLastDayOfMonth": {
"type": "short"
},
"localDate": {
"type": "date"
},
"month": {
"type": "integer"
},
"monthLength": {
"type": "integer"
},
"timestamp": {
"type": "long"
},
"value": {
"type": "date"
},
"year": {
"type": "integer"
}
}
},
"product_create_user": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
}
},
"product_id": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword"
}
}
},
"product_id_init": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
}
},
"product_last_update_time": {
"properties": {
"date": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
}
},
"day": {
"type": "integer"
},
"dayOfWeek": {
"type": "integer"
},
"hour": {
"type": "integer"
},
"isLastDayOfMonth": {
"type": "short"
},
"localDate": {
"type": "date"
},
"month": {
"type": "integer"
},
"monthLength": {
"type": "integer"
},
"timestamp": {
"type": "long"
},
"value": {
"type": "date"
},
"year": {
"type": "integer"
}
}
},
"product_last_update_user": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
}
},
"product_name": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword"
}
}
},
"product_name_17": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
}
},
"product_series": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword"
}
}
},
"product_source": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword"
}
}
},
"product_spec": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword"
}
}
},
"product_types": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword"
}
}
},
"proof": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword"
}
}
},
"properties": {
"properties": {
"product_series": {
"properties": {
"fields": {
"properties": {
"keyword": {
"properties": {
"type": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
}
}
}
}
}
},
"type": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
}
}
}
}
}
},
"sell_point": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword"
}
}
},
"settlement_price": {
"type": "double"
},
"source_system": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
}
},
"source_system_id": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
}
},
"store_type_name": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword"
}
}
},
"suggested_retail_price": {
"type": "double"
},
"sys_create_time": {
"properties": {
"date": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
}
},
"day": {
"type": "long"
},
"dayOfWeek": {
"type": "long"
},
"hour": {
"type": "long"
},
"isLastDayOfMonth": {
"type": "long"
},
"localDate": {
"type": "date"
},
"month": {
"type": "long"
},
"monthLength": {
"type": "long"
},
"timestamp": {
"type": "long"
},
"value": {
"type": "date"
},
"year": {
"type": "long"
}
}
},
"tasted": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
}
},
"up_state": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword"
}
}
},
"up_state_code": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword"
}
}
}
}
}
}
}
}
doris es catlog表结构: