MySQL报错caused by com.dtwave.common.exception.InnerException: Data truncation: Truncated incorrect DO_data truncation: truncated incorrect double value:

MySQL报错caused by com.dtwave.common.exception.InnerException: Data truncation: Truncated incorrect DOUBLE value: ‘null’

错误sql

select 
	qywyh,
	tbrq,
	nbnd 
from xxx.xxx 
where zt = 1 and nbnd >= (YEAR(NOW())-5) and qywyh is not null
1
2
3
4
5
6

报错日志：

start to executing sql: [create table temp_t0_20200702 as 
select qywyh,tbrq,nbnd from x360_rawdata_kpy.yw_qyjdglk_qyndbgxx where zt = 1 and nbnd >= (YEAR(NOW())-5)].
error occured in executing dml clause create table temp_t0_20200702 as 
select qywyh,tbrq,nbnd from x360_rawdata_kpy.yw_qyjdglk_qyndbgxx where zt = 1 and nbnd >= (YEAR(NOW())-5) of task-T_83f7cf325f20402b8e0f9f75246702de_20200702110507700_1_1,caused by com.dtwave.common.exception.InnerException: Data truncation: Truncated incorrect DOUBLE value: 'null'
	at com.dtwave.dipper.jdbc.node.operate.JdbcTemplate.execute(JdbcTemplate.java:55)
	at com.dtwave.dipper.jdbc.node.operate.JdbcTemplate.execute(JdbcTemplate.java:36)
	at com.dtwave.dipper.jdbc.JdbcTaskRunner.doRun(JdbcTaskRunner.java:224)
	at com.dtwave.dipper.spi.bean.node.BasicTaskRunnerPlugin.execute(BasicTaskRunnerPlugin.java:111)
	at com.dtwave.dipper.spi.plugin.proxy.TaskRunnerPluginProxy.execute(TaskRunnerPluginProxy.java:41)
	at com.dtwave.dipper.dubhe.node.executor.TaskExecutor.run(TaskExecutor.java:29)
	at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
	at java.util.concurrent.FutureTask.run(FutureTask.java:266)
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
	at java.lang.Thread.run(Thread.java:748)
.
作业运行失败(Failed)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

分析原因

double value ： ‘null’？？？

字段类型错误么？

检查字段类型：nbnd 为varchr(20)

判断条件nbnd >= (YEAR(NOW())-5)？

改一个固定的值：nbnd >= ‘2020’,SQL正常。

解决方法

采用MySQL强制类型转换

nbnd >=cast( (YEAR(NOW())-5) as char(20)) 即可解决

修改后的SQL

select 
	qywyh,
	tbrq,
	nbnd 
from xxx.xxx 
where zt = 1 and nbnd >= cast((YEAR(NOW())-5) as char(20)) and qywyh is not null
1
2
3
4
5
6