为提高效率,提问时请提供以下信息,问题描述清晰可优先响应。
- 【TiDB 版本】:4.0.8
- 【问题描述】:该问题在3.0.3版本不存在。升级到4.0.8之后出现。
查询语句:
select JSON_EXTRACT(all_json,‘$.“properties”.“video_id”’) as video_id,
sum(if(JSON_EXTRACT(all_json,‘$.“properties”.“action”’)=168,1,0)) as play_count,
sum(if(JSON_EXTRACT(all_json,‘$.“properties”.“action”’)=216,1,0)) as played_count,
sum(if(JSON_EXTRACT(all_json,‘$.“properties”.“action”’)=221,1,0)) as played95_count,
sum(if(JSON_EXTRACT(all_json,‘$.“properties”.“action”’)=229,1,0)) as originality_count,
sum(if(JSON_EXTRACT(all_json,‘$.“properties”.“action”’)=223,1,0)) as fav_count,
sum(if(JSON_EXTRACT(all_json,‘$.“properties”.“action”’)=242,1,0)) as download_count,
sum(if(JSON_EXTRACT(all_json,‘$.“properties”.“action”’)=168 and JSON_EXTRACT(all_json,‘$.“properties”.“recommend”’)>0,1,0)) as recommand_count,
sum(case when JSON_EXTRACT(all_json,‘$.“properties”.“action”’)=168 and JSON_EXTRACT(all_json,‘$.“properties”.“author_vip_id”’)=2 then 0.5 when JSON_EXTRACT(all_json,‘$.“properties”.“action”’)=168 and JSON_EXTRACT(all_json,‘$.“properties”.“author_vip_id”’)=3 then 1 else 0 end) as play_count_pro,
sum(case when JSON_EXTRACT(all_json,‘$.“properties”.“action”’)=216 and JSON_EXTRACT(all_json,‘$.“properties”.“author_vip_id”’)=2 then 0.5 when JSON_EXTRACT(all_json,‘$.“properties”.“action”’)=168 and JSON_EXTRACT(all_json,‘$.“properties”.“author_vip_id”’)=3 then 1 else 0 end) as played_count_pro,
sum(case when JSON_EXTRACT(all_json,‘$.“properties”.“action”’)=221 and JSON_EXTRACT(all_json,‘$.“properties”.“author_vip_id”’)=2 then 0.5 when JSON_EXTRACT(all_json,‘$.“properties”.“action”’)=168 and JSON_EXTRACT(all_json,‘$.“properties”.“author_vip_id”’)=3 then 1 else 0 end) as played95_count_pro
from tvcbook where event=‘videoPlay’ and date in (“2020-12-14”,“2020-12-15”,“2020-12-16”,“2020-12-17”,“2020-12-18”,“2020-12-19”,“2020-12-20”) and remark=‘production’ and
JSON_EXTRACT(all_json,‘$.“properties”.“author_user_id”’) not in (‘17255d5e7ae38559’,‘1d6e526952b43de9’,‘7b0abb9b6fc5d193’)
group by video_id
其中斜体字部分,如果括号内只保留一个,则sql执行正常,如果多于一个,则报错
1105 - Illegal Json text: Error(“trailing characters”, line: 1, column: 6)
还有一个问题比较奇怪,但不是这次bug的重点,mysql查询都是 date>= and date<= 这样用性能高于 date in ()。但是在tidb上同样的表结构,就是date in ()性能更好。
临时解决方案:
该问题目前把 not in 换成 多个!= 用and链接跑了,如下方的sql。但是在v3.0.3版本是可以支持not in 多个的,是升级v4.0.8后出现的问题。
select JSON_EXTRACT(all_json,‘$.“properties”.“video_id”’) as video_id,
sum(if(JSON_EXTRACT(all_json,‘$.“properties”.“action”’)=168,1,0)) as play_count,
sum(if(JSON_EXTRACT(all_json,‘$.“properties”.“action”’)=216,1,0)) as played_count,
sum(if(JSON_EXTRACT(all_json,‘$.“properties”.“action”’)=221,1,0)) as played95_count,
sum(if(JSON_EXTRACT(all_json,‘$.“properties”.“action”’)=229,1,0)) as originality_count,
sum(if(JSON_EXTRACT(all_json,‘$.“properties”.“action”’)=223,1,0)) as fav_count,
sum(if(JSON_EXTRACT(all_json,‘$.“properties”.“action”’)=242,1,0)) as download_count,
sum(if(JSON_EXTRACT(all_json,‘$.“properties”.“action”’)=168 and JSON_EXTRACT(all_json,‘$.“properties”.“recommend”’)>0,1,0)) as recommand_count,
sum(case when JSON_EXTRACT(all_json,‘$.“properties”.“action”’)=168 and JSON_EXTRACT(all_json,‘$.“properties”.“author_vip_id”’)=2 then 0.5 when JSON_EXTRACT(all_json,‘$.“properties”.“action”’)=168 and JSON_EXTRACT(all_json,‘$.“properties”.“author_vip_id”’)=3 then 1 else 0 end) as play_count_pro,
sum(case when JSON_EXTRACT(all_json,‘$.“properties”.“action”’)=216 and JSON_EXTRACT(all_json,‘$.“properties”.“author_vip_id”’)=2 then 0.5 when JSON_EXTRACT(all_json,‘$.“properties”.“action”’)=168 and JSON_EXTRACT(all_json,‘$.“properties”.“author_vip_id”’)=3 then 1 else 0 end) as played_count_pro,
sum(case when JSON_EXTRACT(all_json,‘$.“properties”.“action”’)=221 and JSON_EXTRACT(all_json,‘$.“properties”.“author_vip_id”’)=2 then 0.5 when JSON_EXTRACT(all_json,‘$.“properties”.“action”’)=168 and JSON_EXTRACT(all_json,‘$.“properties”.“author_vip_id”’)=3 then 1 else 0 end) as played95_count_pro
from tvcbook where event=‘videoPlay’ and date in (“2020-12-14”,“2020-12-15”,“2020-12-16”,“2020-12-17”,“2020-12-18”,“2020-12-19”,“2020-12-20”) and remark=‘production’
and JSON_EXTRACT(all_json,‘$.“properties”.“author_user_id”’) !=‘17255d5e7ae38559’
and JSON_EXTRACT(all_json,‘$.“properties”.“author_user_id”’) !=‘1d6e526952b43de9’
and JSON_EXTRACT(all_json,‘$.“properties”.“author_user_id”’) !=‘7b0abb9b6fc5d193’
group by video_id
若提问为性能优化、故障排查类问题,请下载脚本运行。终端输出打印结果,请务必全选并复制粘贴上传。