MyBatis version
3.5.3
Expected result
mysql example:
When param is not included, parameters will be automatically added, resulting in too many parameters, so SQL execution fails
Actual result
SQL execution fails
Comment From: newbirdking
public Object getNamedParams(Object[] args) {
final int paramCount = names.size();
if (args == null || paramCount == 0) {
return null;
} else if (!hasParamAnnotation && paramCount == 1) {
return args[names.firstKey()];
} else {
final Map
There are actually two parameters, but when executed, the parameters become 4
Comment From: harawata
Hello, @newbirdking .
It should not matter if the SQL contains UNION ALL or not.
If there is an exception, please post the full stack trace.
It is better if you could provide a test case or small demo project.
There are actually two parameters, but when executed, the parameters become 4
Yes, it is the expected behavior.
Comment From: newbirdking
But if you separate two sql statements instead of union all, the entire query is normal
sql sechaml:
INSERT INTO pub_house (id, building_id, del_flag, enbaled, rechargeable_area) VALUES ('022692ec91055d385d1ab8ff1b81bc4e', '036e0530-9e66-4437-8610-3c3ae04d1278', '0', '0', '354.000');
INSERT INTO pub_house (id, building_id, del_flag, enbaled, rechargeable_area) VALUES ('04cc1863c154fbada4ee4c8e9a463c7d', '036e0530-9e66-4437-8610-3c3ae04d1278', '0', '0', '248.000');
INSERT INTO pub_house (id, building_id, del_flag, enbaled, rechargeable_area) VALUES ('0a63e311b20668e87ddbb63efe1e3e1c', '036e0530-9e66-4437-8610-3c3ae04d1278', '0', '0', '414.000');
INSERT INTO pub_house (id, building_id, del_flag, enbaled, rechargeable_area) VALUES ('0b344b04cf6349f55660b3cfe729dc52', '036e0530-9e66-4437-8610-3c3ae04d1278', '0', '0', '347.000');
INSERT INTO pub_house (id, building_id, del_flag, enbaled, rechargeable_area) VALUES ('0cba4c80151447aeba3ddd91c26facdc', '036e0530-9e66-4437-8610-3c3ae04d1278', '0', '0', '205.000');
INSERT INTO pub_customer_house (id, bhistory, nchargearea, house_id, del_flag) VALUES ('552a0cca-09b0-4289-87bf-b36bbbe7ada5', '0', '414', '0a63e311b20668e87ddbb63efe1e3e1c', '0');
INSERT INTO pub_customer_house (id, bhistory, nchargearea, house_id, del_flag) VALUES ('5f2b8963-3804-44ba-a74e-d99fe6bb0589', '0', '205', '0cba4c80151447aeba3ddd91c26facdc', '0');
(SELECT IFNULL(SUM(rechargeable_area),0) as area,COUNT(id) as amount FROM pub_house WHERE enbaled = '0'
AND del_flag = '0'
AND FIND_IN_SET(building_id, '036e0530-9e66-4437-8610-3c3ae04d1278')
)
union ALL
SELECT
IFNULL(
SUM(
IFNULL(house.rechargeable_area, 0)
),
0
) AS area,COUNT(house.id) as amount
FROM
(SELECT DISTINCT
ph.rechargeable_area,ph.id
FROM
pub_house ph
INNER JOIN pub_customer_house pch
ON ph.id = pch.house_id
WHERE ph.enbaled = '0'
AND ph.del_flag = '0'
AND pch.del_flag = '0'
AND pch.bhistory = '0'
AND FIND_IN_SET(ph.building_id, '036e0530-9e66-4437-8610-3c3ae04d1278')
) house;
Comment From: harawata
To investigate, we need to reproduce the issue on our end first, so please create an executable test case or small demo project and share it on your GItHub repo. Thank you!
Comment From: newbirdking
I have tried, and if I make a simple project alone, there is no problem. This problem can only occur in the project. So it is very strange
Comment From: harawata
Yeah, there must be another reason than UNION ALL.
Is there is any error message? If you post the stack trace, it might give us a hint (please post the entire stack trace and use text instead of image).
Comment From: newbirdking
Hi, A third-party framework is used to encapsulate the operation, but the bottom layer uses mybatis, and druid reports errors. here is some stack information. Original SQL: com.alibaba.druid.sql.ast.statement.SQLUnionQuery@402b7cf3 Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@58d36e32] 2020-11-18 15:10:32.451 ERROR 11388 --- [ XNIO-1 task-2] c.j.c.s.c.GlobalExceptionHandlerResolver : request addressL:/pubhouse/;Request parameter:{};error: ex=nested exception is org.apache.ibatis.exceptions.PersistenceException:
Error querying database. Cause: com.baomidou.mybatisplus.core.exceptions.MybatisPlusException: Failed to process, please exclude the tableName or statementId.
Error SQL: com.alibaba.druid.sql.ast.statement.SQLUnionQuery@402b7cf3
The error may exist in file [E:\junsi-data\git\2\public\target\classes\mapper\house\PubHouseMapper.xml]
The error may involve cn.com.junsisoft.publicbiz.house.mapper.PubHouseMapper.countHouseAreaByParkAndBuild
The error occurred while executing a query
Cause: com.baomidou.mybatisplus.core.exceptions.MybatisPlusException: Failed to process, please exclude the tableName or statementId.
Error SQL: com.alibaba.druid.sql.ast.statement.SQLUnionQuery@402b7cf3; Specific exception:org.mybatis.spring.MyBatisSystemException: nested exception is org.apache.ibatis.exceptions.PersistenceException:
Error querying database. Cause: com.baomidou.mybatisplus.core.exceptions.MybatisPlusException: Failed to process, please exclude the tableName or statementId.
Error SQL: com.alibaba.druid.sql.ast.statement.SQLUnionQuery@402b7cf3
The error may exist in file [E:\junsi-data\git\2\public\target\classes\mapper\house\PubHouseMapper.xml]
The error may involve cn.com.publicbiz.house.mapper.PubHouseMapper.countHouseAreaByParkAndBuild
The error occurred while executing a query
Cause: com.baomidou.mybatisplus.core.exceptions.MybatisPlusException: Failed to process, please exclude the tableName or statementId.
Error SQL: com.alibaba.druid.sql.ast.statement.SQLUnionQuery@402b7cf3
at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:92)
at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:440)
at com.sun.proxy.$Proxy192.selectList(Unknown Source)
at org.mybatis.spring.SqlSessionTemplate.selectList(SqlSessionTemplate.java:223)
at com.baomidou.mybatisplus.core.override.MybatisMapperMethod.executeForMany(MybatisMapperMethod.java:177)
at com.baomidou.mybatisplus.core.override.MybatisMapperMethod.execute(MybatisMapperMethod.java:78)
at com.baomidou.mybatisplus.core.override.MybatisMapperProxy.invoke(MybatisMapperProxy.java:96)
at com.sun.proxy.$Proxy213.countHouseAreaByParkAndBuild(Unknown Source)
at cn.com.junsisoft.publicbiz.house.service.impl.PubHouseServiceImpl.test(PubHouseServiceImpl.java:1694)
at cn.com.publicbiz.house.service.impl.PubHouseServiceImpl$$FastClassBySpringCGLIB$$982be92a.invoke(
Comment From: harawata
Thank you for the update, @newbirdking !
It's just the first part of the stack trace (all stacks are important ☝️), but the exception seems to be thrown from Druid indeed. You should upgrade to the latest version if you haven't. And see if the same issue is reported. There are some possibly related issues on their tracker : https://github.com/alibaba/druid/search?q=union+all&type=issues
I'll close this issue as I believe it is not a MyBatis issue, but if you could reproduce the issue without druid or mybatis-plus, please add a comment and I'll reopen.