奇怪的慢查全表扫描

[TiDB_C罗]

【 TiDB 使用环境】生产环境 /测试/ Poc
【 TiDB 版本】
【复现路径】做过哪些操作出现的问题
【遇到的问题：问题现象及影响】
【资源配置】

select * from db1.account where id in (select
case when JSON_EXTRACT(response,'$.status') = 'OK' then JSON_EXTRACT(response,'$.result.one_face_with_multi_cards[0].user_id') 
else ' ' end   as concat_top_max_user_id 
from db1.face where account_id = 1000000
and option_type = 2
order by id desc limit 1)

执行计划造成db1.account全表扫描
db1.account.id, decimal(20,0) BINARY)->Column#65，明显发生了强制转换。
如果先把结果查出来再执行select * from db1.account where id (xxxx)走的Batch_Point_Get_1
随后把子查询改为这样cast(case …when… end as unsigned)

使用了conver也是一样
最后又在case分支执行cast转换，最终这样

cast(case when JSON_EXTRACT(response,'$.status') = 'OK' then cast(JSON_EXTRACT(response,'$.result.one_face_with_multi_cards[0].user_id') as  unsigned)
else '' end as unsigned)

最终语句是这样：

select * from db1.account where id in (select
cast(case when JSON_EXTRACT(response,'$.status') = 'OK' then cast(JSON_EXTRACT(response,'$.result.one_face_with_multi_cards[0].user_id') as  unsigned)
else '' end as unsigned)  as concat_top_max_user_id 
from db1.face where account_id = 1000000
and option_type = 2
order by id desc limit 1)

终于解决了，为啥需要两次cast转换？

[RenlySir]

@TiDB_C罗

as

因为then ，else可能对应两个不同数据，分别都要cast吧

[tidb菜鸟一只 (小菜一颗)]

因为else ‘’ 是字符串啊

[Jellybean (李文杰)]

case when的语法有下面两种：

1 CASE WHEN [expr] THEN [result1]…
  ELSE [default] END

2 CASE [col_name] WHEN [value1/expr1] THEN [result1]…
  ELSE [default] END

你的SQL要使用两个cast主要是为了保证case when出来的数据格式一致：
SELECT * FROM db1.account WHERE id IN
(SELECT cast(
CASE WHEN JSON_EXTRACT(response,‘$.status’) = ‘OK’ THEN cast(JSON_EXTRACT(response,‘$.result.one_face_with_multi_cards[0].user_id’) AS unsigned)
ELSE ‘’ # 第二个cast是转义这里的空字符串，如果你在这里设置一个默认是 unsigned 类型的user_id，第二个cast也可以去掉
END
AS unsigned) AS concat_top_max_user_id
FROM db1.face
WHERE account_id = 1000000 AND option_type = 2
ORDER BY id DESC
limit 1)

[redgame (Ti D Ber Pa Amoi Ul)]

嗯，else ’ '这里的事

[TiDB_C罗]

果然是需要每个when … then …需要单独cast，最外层就不需要再cast了，各位回答的都非常正确，最佳答案给第一个回复的了 。