【 TiDB 使用环境】生产环境 /测试/ Poc
【 TiDB 版本】
【复现路径】做过哪些操作出现的问题
【遇到的问题：问题现象及影响】
【资源配置】进入到 TiDB Dashboard -集群信息 (Cluster Info) -主机(Hosts) 截图此页面
【附件：截图/日志/监控】
SQL语句是
SELECT sum(ph_pd) AS ph_pd
FROM
(SELECT if(contract_type_id IN (24,25),COALESCE(JSON_EXTRACT(terminal_share_extend,'$.ph_pd'),0),0) AS ph_pd
FROM puyu_fgtw_share_compute_result dist
WHERE is_only_compute = 0
AND income_date IS NOT NULL
AND dist.income_date='2024-10-01') t
表结构为
Table: puyu_fgtw_share_compute_result
Create Table: CREATE TABLE `puyu_fgtw_share_compute_result` (
`id` bigint(64) NOT NULL AUTO_INCREMENT COMMENT '主键id',
`data_type` tinyint(4) DEFAULT '1' ,
`reversed_id` bigint(64) DEFAULT '0' ,
`dt` varchar(30) DEFAULT '' ,
`batch_no` varchar(64) DEFAULT '' ,
`reverse_batch_no` varchar(64) DEFAULT '' ,
`album_name` varchar(255) DEFAULT '' ,
`acr_id` bigint(8) DEFAULT '0' COMMENT ,
`puyu_work_id` bigint(64) DEFAULT '-1' ,
`contract_id` bigint(64) DEFAULT '-1' ,
`contract_number` varchar(128) DEFAULT '' ,
`contract_type_id` bigint(64) DEFAULT '-1' ,
`is_market_sharing` int(4) DEFAULT NULL ,
`is_only_compute` int(4) DEFAULT NULL ,
`purchase_budget_type_id` bigint(255) DEFAULT '-1' ,
`owner_name` varchar(255) NOT NULL DEFAULT '' ,
`owner_id` bigint(64) NOT NULL DEFAULT '-1' ,
`business_model` varchar(255) DEFAULT NULL ,
`is_exclusive` int(4) DEFAULT '0' ,
`is_first_show` tinyint(4) DEFAULT '0' ,
`is_high_price_strategy` tinyint(4) DEFAULT '0' ,
`business_tag` int(255) DEFAULT '-1' ,
`issue_time` varchar(30) DEFAULT '',
`auth_begin_time` datetime DEFAULT NULL,
`auth_end_time` datetime DEFAULT NULL ,
`diff_days` int(255) DEFAULT NULL ,
`total_share_amount` decimal(20,4) DEFAULT '0' ,
`total_ev_amount` decimal(20,4) DEFAULT '0' ,
`total_ipm_amount` decimal(20,4) DEFAULT '0' ,
`total_pd_amount` decimal(20,4) DEFAULT '0' ,
`total_reward_amount` decimal(20,4) DEFAULT '0' ,
`total_ev` bigint(255) DEFAULT '0' ,
`total_ipm` bigint(255) DEFAULT '0' ,
`total_pd` decimal(18,2) DEFAULT '0',
`total_login_uv` bigint(20) DEFAULT '0' ,
`history_total_pd` decimal(18,2) DEFAULT '0',
`share_rate` decimal(15,4) NOT NULL DEFAULT '0' ,
`ipm_5_price` decimal(15,4) DEFAULT '0' ,
`ipm_15_price` decimal(15,4) DEFAULT '0'
`ipm_30_price` decimal(15,4) DEFAULT '0' ,
`ipm_45_price` decimal(15,4) DEFAULT '0' ,
`ipm_pause_price` decimal(15,4) DEFAULT NULL ,
`guarantee_value` decimal(15,4) DEFAULT NULL ,
`ipm_5` bigint(255) DEFAULT '0' ,
`ipm_15` bigint(255) DEFAULT '0' ,
`ipm_30` bigint(255) DEFAULT '0' ,
`ipm_45` bigint(255) DEFAULT '0' ,
`ipm_pause` bigint(255) DEFAULT '0' ,
`internal_ev` bigint(255) DEFAULT '0' ,
`oversea_ev` bigint(255) DEFAULT '0' ,
`hmt_ev` bigint(255) DEFAULT '0' ,
`hk_ev` bigint(255) DEFAULT '0' ,
`mo_ev` bigint(255) DEFAULT '0' ,
`tw_ev` bigint(255) DEFAULT '0' ,
`internal_pd` decimal(18,2) DEFAULT '0',
`oversea_pd` decimal(18,2) DEFAULT '0',
`hmt_pd` decimal(18,2) DEFAULT '0',
`hk_pd` decimal(18,2) DEFAULT '0',
`mo_pd` decimal(18,2) DEFAULT '0',
`tw_pd` decimal(18,2) DEFAULT '0',
`internal_ipm` bigint(255) DEFAULT '0' ,
`oversea_ipm` bigint(255) DEFAULT '0' ,
`hmt_ipm` bigint(255) DEFAULT '0' ,
`hk_ipm` bigint(255) DEFAULT '0' ,
`mo_ipm` bigint(255) DEFAULT '0' ,
`tw_ipm` bigint(255) DEFAULT '0' ,
`lock_status` int(4) DEFAULT '0' ,
`oversea_share_amount` decimal(20,4) DEFAULT '0' ,
`hmt_share_amount` decimal(20,4) DEFAULT '0' ,
`hk_share_amount` decimal(20,4) DEFAULT '0' ,
`mo_share_amount` decimal(20,4) DEFAULT '0' ,
`tw_share_amount` decimal(20,4) DEFAULT '0',
`internal_share_amount` decimal(20,4) DEFAULT '0' ,
`terminal_share_extend` varchar(1024) DEFAULT '' ,
`reverse_status` tinyint(4) DEFAULT '0' ,
`c_dt` datetime DEFAULT CURRENT_TIMESTAMP ,
`u_dt` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
`flag` tinyint(4) DEFAULT '1' ,
`accrual_date` date DEFAULT NULL ,
`income_date` date DEFAULT NULL ,
`origin_share_amount` decimal(20,4) DEFAULT '0' ,
`is_cloud_rake` tinyint(4) DEFAULT '0' ,
`participate_type` tinyint(4) DEFAULT '0' ,
`market_fee` decimal(18,2) DEFAULT '0' ,
`company_invest_rate` decimal(10,4) DEFAULT NULL ,
`owner_share_role` tinyint(4) DEFAULT '0' ,
`qipu_share_flag` tinyint(4) DEFAULT '2' ,
PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */,
UNIQUE KEY `uni_index` (`dt`,`puyu_work_id`,`contract_id`,`owner_id`,`batch_no`,`data_type`),
KEY `idx_dt` (`dt`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin AUTO_INCREMENT=1381423418868935550
在 v7.1.5 的版本中报错为
ERROR 1105 (HY000): invalid data type: Illegal Json text: Error("EOF while parsing a value", line: 1, column: 0)
数据2个集群是一样的。中间子查询在2个集群中也都可以正常查询，
SELECT if(contract_type_id IN (24,25),COALESCE(JSON_EXTRACT(terminal_share_extend,'$.ph_pd'),0),0) AS ph_pd
FROM puyu_fgtw_share_compute_result dist
WHERE is_only_compute = 0
AND income_date IS NOT NULL
AND dist.income_date='2024-10-01'
sum 就报错，麻烦看一下原因
。