insert select写法报错Incorrect integer value的坑

一、问题

最近项目的mysql数据库迁移到了oceanbase数据库，同样的sql，mysql可以执行，但是oceanbase里执行就报错：

[Err] 1366 - Incorrect integer value

错误sql样例如下：

INSERT into student_sign_statistics 
SELECT
t.student_id,
t.class_id,
t.riqi,
SXW,
COUNT(1),
1,
NOW()
FROM
(
SELECT
s.class_id class_id,
s.user_id student_id,
DATE_FORMAT(s.sign_time, '%Y-%m-%d') riqi,
CASE WHEN 
(
DATE_FORMAT(s.sign_time, '%H') >= 0
AND DATE_FORMAT(s.sign_time, '%H') <= 11
) THEN
0
ELSE
1
END SXW
FROM
signin s  JOIN subject_student c
 ON s.subject_id=c.class_subject_id AND s.user_id=c.user_id AND s.sign_type='2' 
 AND IFNULL(c.isdel, 0) != 1
 JOIN classinfo cf ON s.class_id=cf.class_id 
 AND cf.fact_startdate > DATE_SUB(DATE_FORMAT(CURRENT_TIME (),'%Y-%m-%d'),INTERVAL 31 DAY)
 AND cf.status='1' GROUP BY
s.class_id,s.user_id,
riqi,
SXW
) t GROUP BY t.student_id ,t.class_id ON DUPLICATE KEY UPDATE student_id=VALUES(student_id), classid=VALUES(classid), sign_date=VALUES(sign_date),afternoon=VALUES(afternoon),sign_num=VALUES(sign_num) ,type=VALUES(type) ,update_time=VALUES(update_time) ;

总之就是这么一大堆sql，mysql数据库执行没事，但是oceanbase数据库执行就报错：

[Err] 1366 - Incorrect integer value

并且如果单独把select的sql拿出来，那么在oceanbase数据库也是可以正常执行的，如下：

SELECT
t.student_id,
t.class_id,
t.riqi,
SXW,
COUNT(1),
1,
NOW()
FROM
(
SELECT
s.class_id class_id,
s.user_id student_id,
DATE_FORMAT(s.sign_time, '%Y-%m-%d') riqi,
CASE WHEN 
(
DATE_FORMAT(s.sign_time, '%H') >= 0
AND DATE_FORMAT(s.sign_time, '%H') <= 11
) THEN
0
ELSE
1
END SXW
FROM
signin s  JOIN subject_student c
 ON s.subject_id=c.class_subject_id AND s.user_id=c.user_id AND s.sign_type='2' 
 AND IFNULL(c.isdel, 0) != 1
 JOIN classinfo cf ON s.class_id=cf.class_id 
 AND cf.fact_startdate > DATE_SUB(DATE_FORMAT(CURRENT_TIME (),'%Y-%m-%d'),INTERVAL 31 DAY)
 AND cf.status='1' GROUP BY
s.class_id,s.user_id,
riqi,
SXW
) t GROUP BY t.student_id ,t.class_id

这样单独执行select，也没有问题；
把结果集的数据复制出insert来执行，也没有问题，就很奇怪。

二、解决方法

后来发现，把AND IFNULL(c.isdel, 0) != 1换成AND IFNULL(c.isdel, 0) != '1'，就可以了。如下：

INSERT into student_sign_statistics 
SELECT
t.student_id,
t.class_id,
t.riqi,
SXW,
COUNT(1),
1,
NOW()
FROM
(
SELECT
s.class_id class_id,
s.user_id student_id,
DATE_FORMAT(s.sign_time, '%Y-%m-%d') riqi,
CASE WHEN 
(
DATE_FORMAT(s.sign_time, '%H') >= 0
AND DATE_FORMAT(s.sign_time, '%H') <= 11
) THEN
0
ELSE
1
END SXW
FROM
signin s  JOIN subject_student c
 ON s.subject_id=c.class_subject_id AND s.user_id=c.user_id AND s.sign_type='2' 
 AND IFNULL(c.isdel, 0) != '1'
 JOIN classinfo cf ON s.class_id=cf.class_id 
 AND cf.fact_startdate > DATE_SUB(DATE_FORMAT(CURRENT_TIME (),'%Y-%m-%d'),INTERVAL 31 DAY)
 AND cf.status='1' GROUP BY
s.class_id,s.user_id,
riqi,
SXW
) t GROUP BY t.student_id ,t.class_id ON DUPLICATE KEY UPDATE student_id=VALUES(student_id), classid=VALUES(classid), sign_date=VALUES(sign_date),afternoon=VALUES(afternoon),sign_num=VALUES(sign_num) ,type=VALUES(type) ,update_time=VALUES(update_time) ;

不知道为什么，总之把AND IFNULL(c.isdel, 0) != 1换成AND IFNULL(c.isdel, 0) != '1'，就可以正常执行了。

三、备注

可能是之前数据库是mysql5，语法要求不严格；换成oceanbase数据库后（是基于mysql8的），语法严格了，导致报错；具体原因还不清楚。