mycat2 CONCAT函数 NullPointerException 问题

诙谐的猫头鹰

已于 2023-06-26 14:12:29 修改

阅读量177

点赞数

分类专栏： java 文章标签： java mysql

于 2023-06-26 13:57:36 首次发布

本文链接：https://blog.csdn.net/weixin_43003720/article/details/131395744

版权

java 专栏收录该内容

11 篇文章 0 订阅

订阅专栏

在Mycat2环境下执行SQL时遇到Java.lang.NullPointerException问题，原因是CONCAT函数处理YEAR和QUARTER函数返回的整数类型数据不匹配。解决方案是在CONCAT中对YEAR和QUARTER函数的结果使用CAST转换为VARCHAR类型。修改后的SQL可以正常运行。

摘要由CSDN通过智能技术生成

示例sql

SELECT
	tab_1.`project_id` AS field_0,
	tab_2.`project_id` AS field_1,
	tab_1.`project_name` AS field_2,
	tab_1.`del_flag` AS field_3,
	CONCAT(
		YEAR ( tab_2.`create_time` ),
		'Q',
	QUARTER ( tab_2.`create_time` )) AS field_4,
	DATE_FORMAT ( tab_2.`create_time`, '%Y%m%d' ) AS field_5,
	DATE_FORMAT ( tab_2.`create_time`, '%Y%m%d %H:%i:%S' ) AS field_6,
	tab_2.`del_flag` AS field_7,
	tab_2.`problem_briefly` AS field_8,
	tab_2.`information_id` AS field_9,
	DATE_FORMAT ( tab_2.`create_time`, '%H:%i:00' ) AS field_10,
	DATE_FORMAT ( tab_2.`create_time`, '%H:%i:%S' ) AS field_11,
	DATE_FORMAT( tab_2.`create_time`, '%x-%v' ) AS field_12,
	DATE_FORMAT ( tab_2.`create_time`, '%Y' ) AS field_13,
	DATE_FORMAT ( tab_2.`create_time`, '%H:00:00' ) AS field_14,
	DATE_FORMAT ( tab_2.`create_time`, '%Y%m' ) AS field_15 
FROM
	data_1.data_project AS tab_1
	LEFT JOIN data_2.data_inspect AS tab_2 ON tab_2.`project_id` = tab_1.`project_id` 
WHERE
	tab_1.`del_flag` = '0' 
	AND tab_2.`del_flag` = '0' 
	LIMIT 0,
	100

这段sql放到navcat连接的mycat2里执行时，返回> 0 - java.lang.NullPointerException的问题，但是在mysql里面执行时，没有这个问题，之后排查了一下mycat2的日志，发现了问题

org.codehaus.commons.compiler.CompileException: 
Line 80, Column 83: 
No applicable constructor/method found for actual parameters 
"java.lang.Integer, java.lang.String, java.lang.Integer";
 candidates are: 
"public static java.lang.String 
io.mycat.calcite.sqlfunction.stringfunction
.ConcatFunction.concat(java.lang.String[])"

大致的问题是mycat2调用CONCAT的时候，用的是方法，sql里面的值放入时，会自动识别类型，但是concat里面的YEAR 和QUARTER 函数返回的都是Int类型的数据，这时候mycat2去找符合要求的函数时，没有找到相应的函数，所以返回了空指针

问题解决：

在concat里面调用函数时，先套一个CAST(YEAR ( tab_2.`create_time` ) as varchar) 即可解决

SELECT
	tab_1.`project_id` AS field_0,
	tab_2.`project_id` AS field_1,
	tab_1.`project_name` AS field_2,
	tab_1.`del_flag` AS field_3,
	CONCAT(
		CAST( YEAR ( tab_2.`create_time` ) AS VARCHAR ),
		'Q',
	CAST( QUARTER ( tab_2.create_time ) AS VARCHAR )) AS field_4,
	DATE_FORMAT ( tab_2.`create_time`, '%Y%m%d' ) AS field_5,
	DATE_FORMAT ( tab_2.`create_time`, '%Y%m%d %H:%i:%S' ) AS field_6,
	tab_2.`del_flag` AS field_7,
	tab_2.`problem_briefly` AS field_8,
	tab_2.`information_id` AS field_9,
	DATE_FORMAT ( tab_2.`create_time`, '%H:%i:00' ) AS field_10,
	DATE_FORMAT ( tab_2.`create_time`, '%H:%i:%S' ) AS field_11,
	DATE_FORMAT( tab_2.`create_time`, '%x-%v' ) AS field_12,
	DATE_FORMAT ( tab_2.`create_time`, '%Y' ) AS field_13,
	DATE_FORMAT ( tab_2.`create_time`, '%H:00:00' ) AS field_14,
	DATE_FORMAT ( tab_2.`create_time`, '%Y%m' ) AS field_15 
FROM
	data_1.data_project AS tab_1
	LEFT JOIN data_2.data_inspect AS tab_2 ON tab_2.`project_id` = tab_1.`project_id` 
WHERE
	tab_1.`del_flag` = '0' 
	AND tab_2.`del_flag` = '0' 
	LIMIT 0,
	100

ps:如果还是出现部分sql有问题，就将 varchar改成char试试