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

报错日志：

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)

分析原因

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