TiFlash CastStringAsInt is not supported

[elvizlai]

为提高效率，提问时请提供以下信息，问题描述清晰可优先响应。

• 【TiDB 版本】：
  v4.0.4

• 【问题描述】：

某表开启了 TiFlash 后，查询报错，提示：[HY000][1105] CastStringAsInt is not supported.
关闭该表的 TiFlash，使用 TiKV 查询正常。

疑似语句:

explain analyze select /*+ read_from_storage(tiflash[wo_workorder_schedule]) */ * 
from wo_workorder_schedule 
where column1 LIKE concat('%', '', '%') order by CONVERT(plan_day, SIGNED) limit 1;

将 where column1 LIKE concat('%', '', '%') 替换为 where 1=1 则正常

explain analyze select /*+ read_from_storage(tiflash[wo_workorder_schedule]) */ * 
from wo_workorder_schedule 
where 1=1 order by CONVERT(plan_day, SIGNED) limit 1;

疑似 LIKE 或者这类查询导致执行计划异常。

由于本身 TiFlash 未开源，具体原因未知。

业务数据表结构无法提供，请自行构建类似schema复现。

play_day 为 vchar，存储的是数字字符串。

[windtaker]

@elvizlai

开启了 TiFlash 后，查询

开启 TiFlash 之后 explain 看看呢？

[elvizlai]

报错了，没办法 explain

[windtaker]

explain不涉及到执行，如果explain就报错那就要怀疑是TiDB的问题。你是explain报错还是explain analyze报错呢？

[elvizlai]

explain analyze 报错

其实比较好复现。构造一个有2~3个字段的表即可。

[windtaker]

explain analyze报错的时候explain结果能提供吗？

[elvizlai]

Projection_18,1.00,root,"","dbName.wo_workorder_schedule.id, dbName.wo_workorder_schedule.schedule_code, dbName.wo_workorder_schedule.order_code, dbName.wo_workorder_schedule.product_code, dbName.wo_workorder_schedule.product_name, dbName.wo_workorder_schedule.product_type, dbName.wo_workorder_schedule.org_code, dbName.wo_workorder_schedule.org_name, dbName.wo_workorder_schedule.care_receiver_code, dbName.wo_workorder_schedule.care_receiver_name, dbName.wo_workorder_schedule.care_receiver_gender, dbName.wo_workorder_schedule.care_receiver_birthday, dbName.wo_workorder_schedule.care_receiver_id_card, dbName.wo_workorder_schedule.care_receiver_id_card_photo, dbName.wo_workorder_schedule.care_receiver_tel, dbName.wo_workorder_schedule.live_province_code, dbName.wo_workorder_schedule.live_province_name, dbName.wo_workorder_schedule.live_city_code, dbName.wo_workorder_schedule.live_city_name, dbName.wo_workorder_schedule.live_district_code, dbName.wo_workorder_schedule.live_district_name, dbName.wo_workorder_schedule.live_subdistrict_code, dbName.wo_workorder_schedule.live_subdistrict_name, dbName.wo_workorder_schedule.live_detail_address, dbName.wo_workorder_schedule.live_longitude, dbName.wo_workorder_schedule.live_latitude, dbName.wo_workorder_schedule.service_position_code, dbName.wo_workorder_schedule.service_position_name, dbName.wo_workorder_schedule.care_giver_code, dbName.wo_workorder_schedule.care_giver_name, dbName.wo_workorder_schedule.service_frequency_type, dbName.wo_workorder_schedule.plan_day, dbName.wo_workorder_schedule.plan_start_time, dbName.wo_workorder_schedule.plan_end_time, dbName.wo_workorder_schedule.plan_service_duration, dbName.wo_workorder_schedule.is_sign, dbName.wo_workorder_schedule.schedule_status, dbName.wo_workorder_schedule.remark, dbName.wo_workorder_schedule.create_code, dbName.wo_workorder_schedule.create_name, dbName.wo_workorder_schedule.create_date, dbName.wo_workorder_schedule.update_code, dbName.wo_workorder_schedule.update_name, dbName.wo_workorder_schedule.update_date"
└─TopN_8,1.00,root,"","Column#46:asc, offset:0, count:1"
  └─Projection_19,1.00,root,"","dbName.wo_workorder_schedule.id, dbName.wo_workorder_schedule.schedule_code, dbName.wo_workorder_schedule.order_code, dbName.wo_workorder_schedule.product_code, dbName.wo_workorder_schedule.product_name, dbName.wo_workorder_schedule.product_type, dbName.wo_workorder_schedule.org_code, dbName.wo_workorder_schedule.org_name, dbName.wo_workorder_schedule.care_receiver_code, dbName.wo_workorder_schedule.care_receiver_name, dbName.wo_workorder_schedule.care_receiver_gender, dbName.wo_workorder_schedule.care_receiver_birthday, dbName.wo_workorder_schedule.care_receiver_id_card, dbName.wo_workorder_schedule.care_receiver_id_card_photo, dbName.wo_workorder_schedule.care_receiver_tel, dbName.wo_workorder_schedule.live_province_code, dbName.wo_workorder_schedule.live_province_name, dbName.wo_workorder_schedule.live_city_code, dbName.wo_workorder_schedule.live_city_name, dbName.wo_workorder_schedule.live_district_code, dbName.wo_workorder_schedule.live_district_name, dbName.wo_workorder_schedule.live_subdistrict_code, dbName.wo_workorder_schedule.live_subdistrict_name, dbName.wo_workorder_schedule.live_detail_address, dbName.wo_workorder_schedule.live_longitude, dbName.wo_workorder_schedule.live_latitude, dbName.wo_workorder_schedule.service_position_code, dbName.wo_workorder_schedule.service_position_name, dbName.wo_workorder_schedule.care_giver_code, dbName.wo_workorder_schedule.care_giver_name, dbName.wo_workorder_schedule.service_frequency_type, dbName.wo_workorder_schedule.plan_day, dbName.wo_workorder_schedule.plan_start_time, dbName.wo_workorder_schedule.plan_end_time, dbName.wo_workorder_schedule.plan_service_duration, dbName.wo_workorder_schedule.is_sign, dbName.wo_workorder_schedule.schedule_status, dbName.wo_workorder_schedule.remark, dbName.wo_workorder_schedule.create_code, dbName.wo_workorder_schedule.create_name, dbName.wo_workorder_schedule.create_date, dbName.wo_workorder_schedule.update_code, dbName.wo_workorder_schedule.update_name, dbName.wo_workorder_schedule.update_date, cast(dbName.wo_workorder_schedule.plan_day, bigint(22) BINARY)->Column#46"
    └─TableReader_14,1.00,root,"",data:TopN_13
      └─TopN_13,1.00,cop[tiflash],"","cast(dbName.wo_workorder_schedule.plan_day):asc, offset:0, count:1"
        └─Selection_12,206.40,cop[tiflash],"","like(dbName.wo_workorder_schedule.care_giver_name, ""%%"", 92)"
          └─TableFullScan_11,258.00,cop[tiflash],table:wo_workorder_schedule,"keep order:false, stats:pseudo"

[windtaker]

应该是 TopN 里面的那个 cast 报错了，把 like 去掉可能plan变了，topN
不下推给 TiFlash了。我看看为什么 TiDB 会把不支持的函数下推给 TiFlash 吧

[windtaker]

TiDB 在 TopN 中存在 TiFlash 不支持的 function 仍然将 TopN 下推给 TiFlash 这个应该是 TiDB 的一个 bug，我这两天 fix 一下吧

[windtaker]

@elvizlai，这个 pr 能 fix 相关的 bug。
https://github.com/pingcap/tidb/pull/19363