SQL执行结果与mysql不一致,如果只是select没报错,但insert到另一张表时mysql未报错,TiDB报错

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

  • 【TiDB 版本】: Release Version: v2.1.13 Git Commit Hash: 6b5b1a6802f9b8f5a22d8aab24ac80729331e1bc Git Branch: HEAD UTC Build Time: 2019-06-21 12:27:08 GoVersion: go version go1.12 linux/amd64 Race Enabled: false TiKV Min Version: 2.1.0-alpha.1-ff3dd160846b7d1aed9079c389fc188f7f5ea13e Check Table Before Drop: false
  • 【MySQL 版本】: 5.7.28-log
  • 【问题描述】: SQL执行结果与mysql不一致,如果只是select没问题,但insert时mysql未报错,TiDB报错如图: image 初步发现是SUBSTRING_INDEX(’’,’,’,2)中的第一个参数为非数字字符(或空白)时会报错。
  • 【建表SQL】: DROP TABLE IF EXISTS tmp_test_tag_user; CREATE TABLE tmp_test_tag_user ( tag_id varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT ‘标签唯一id’, tag_value int(11) NULL DEFAULT NULL COMMENT ‘标签值,对应0、1、2等’ ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin COMMENT = ‘用户标签统计表’ ROW_FORMAT = Dynamic;
  • 【插入SQL】: INSERT INTO tmp_test_tag_user SELECT ‘tag_1’ AS tag_id, CASE WHEN SUBSTRING_INDEX(’’,’,’,2)>30 THEN 1 ELSE 0 END AS tag_value ;
  • 【TiDB show create table tmp_test_tag_user】: CREATE TABLE tmp_test_tag_user ( tag_id varchar(50) DEFAULT NULL COMMENT ‘标签唯一id’, tag_value int(11) DEFAULT NULL COMMENT ‘标签值,对应0、1、2等’ ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT=‘用户标签统计表’
  • 【MySQL show create table tmp_test_tag_user】: CREATE TABLE tmp_test_tag_user ( tag_id varchar(50) COLLATE utf8mb4_bin DEFAULT NULL COMMENT ‘标签唯一id’, tag_value int(11) DEFAULT NULL COMMENT ‘标签值,对应0、1、2等’ ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=DYNAMIC COMMENT=‘用户标签统计表’

若提问为性能优化、故障排查类问题,请下载脚本运行。终端输出的打印结果,请务必全选并复制粘贴上传。

感谢反馈,测试了 2.1.13 以及以上版本(包括最新的 3.0.5 )都有这个问题,我们修复下。

那这个是SUBSTRING_INDEX函数还是哪里的问题呢,现在是只能分开执行select和insert两个SQL操作吗,还是有其他方法可以临时代替修复?

insert into tmp_test_tag_user SELECT 'tag_1' AS tag_id, CASE WHEN if(SUBSTRING_INDEX('31.1.1.1','.',1) = '',0,SUBSTRING_INDEX('31.1.1.1','.',1))>30 THEN '1' Else '0' END AS tag_value ;

可以通过上面的写法先对 substring_index 的结果为 ‘’ 的添加判断。绕过这个问题。