You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by GitBox <gi...@apache.org> on 2022/10/09 01:57:30 UTC
[GitHub] [shardingsphere] mushroom0V0 opened a new issue, #21406: pg database, the aggregate function in XML does not divide the table normally
mushroom0V0 opened a new issue, #21406:
URL: https://github.com/apache/shardingsphere/issues/21406
## Bug Report
### Which version of ShardingSphere did you use?
version 4.0.1 & 4.1.1 has been tried
### Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?
ShardingSphere-JDBC
### Expected behavior
return query results
### Actual behavior
√：select file_store_id, extension from file f
×：select file_store_id, extension, count(*) count, sum(IFNULL(file_size,0)) size from file f
When the statement contains no aggregate function, it can work normally;
When the aggregate function is included, the following error is prompted:
```
org.springframework.jdbc.BadSqlGrammarException:
### Error querying database. Cause: org.postgresql.util.PSQLException: ERROR: relation "file" does not exist
位置：87
### The error may exist in file [E:\com-hunp\venus\file\file-core\target\classes\mapper\FileMapper.xml]
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### SQL: select file_store_id, extension, count(*) count, sum(IFNULL(file_size,0)) size from file f where f.file_store_id in (select file_store_id from file_config) and f.create_time < ? group by file_store_id, extension
### Cause: org.postgresql.util.PSQLException: ERROR: relation "file" does not exist
位置：87
; bad SQL grammar []; nested exception is org.postgresql.util.PSQLException: ERROR: relation "file" does not exist
位置：87
at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:234) ~[spring-jdbc-5.1.5.RELEASE.jar:5.1.5.RELEASE]
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72) ~[spring-jdbc-5.1.5.RELEASE.jar:5.1.5.RELEASE]
at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:88) ~[mybatis-spring-2.0.3.jar:2.0.3]
at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:440) ~[mybatis-spring-2.0.3.jar:2.0.3]
at com.sun.proxy.$Proxy199.selectList(Unknown Source) ~[na:na]
at org.mybatis.spring.SqlSessionTemplate.selectList(SqlSessionTemplate.java:223) ~[mybatis-spring-2.0.3.jar:2.0.3]
at com.baomidou.mybatisplus.core.override.MybatisMapperMethod.executeForMany(MybatisMapperMethod.java:177) ~[mybatis-plus-core-3.3.1.jar:3.3.1]
at com.baomidou.mybatisplus.core.override.MybatisMapperMethod.execute(MybatisMapperMethod.java:78) ~[mybatis-plus-core-3.3.1.jar:3.3.1]
at com.baomidou.mybatisplus.core.override.MybatisMapperProxy.invoke(MybatisMapperProxy.java:96) ~[mybatis-plus-core-3.3.1.jar:3.3.1]
at com.sun.proxy.$Proxy204.getStatisticType(Unknown Source) ~[na:na]
at com.hnup.common.file.core.repository.dao.impl.FileDAOImpl.getStatisticType(FileDAOImpl.java:102) ~[classes/:na]
at com.hnup.common.file.core.repository.dao.impl.FileDAOImpl$$FastClassBySpringCGLIB$$106a49e8.invoke(<generated>) ~[classes/:na]
at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218) ~[spring-core-5.1.5.RELEASE.jar:5.1.5.RELEASE]
at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:749) ~[spring-aop-5.1.5.RELEASE.jar:5.1.5.RELEASE]
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163) ~[spring-aop-5.1.5.RELEASE.jar:5.1.5.RELEASE]
at com.baomidou.dynamic.datasource.aop.DynamicDataSourceAnnotationInterceptor.invoke(DynamicDataSourceAnnotationInterceptor.java:51) ~[dynamic-datasource-spring-boot-starter-3.3.1.jar:3.3.1]
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) ~[spring-aop-5.1.5.RELEASE.jar:5.1.5.RELEASE]
at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:688) ~[spring-aop-5.1.5.RELEASE.jar:5.1.5.RELEASE]
at com.hnup.common.file.core.repository.dao.impl.FileDAOImpl$$EnhancerBySpringCGLIB$$81c64ae9.getStatisticType(<generated>) ~[classes/:na]
at com.hnup.common.file.admin.service.FileStatisticsService.test(FileStatisticsService.java:170) ~[classes/:na]
at com.hnup.common.file.admin.controller.FileStatisticsController.test(FileStatisticsController.java:74) ~[classes/:na]
at com.hnup.common.file.admin.controller.FileStatisticsController$$FastClassBySpringCGLIB$$9f97a4bf.invoke(<generated>) ~[classes/:na]
```
### Reason analyze (If you can)
it looks like org.apache.shardingsphere.sql.parser.core.SQLParseKernel#parse() Failed to resolve a TableReferences type element when the statement contains aggregate function.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] mushroom0V0 commented on issue #21406: pg database, the aggregate function in XML does not divide the table normally
Posted by GitBox <gi...@apache.org>.
mushroom0V0 commented on issue #21406:
URL: https://github.com/apache/shardingsphere/issues/21406#issuecomment-1272521097
bingo!
change
```select file_store_id, extension, count(*) count, sum(IFNULL(file_size,0)) size from file f```
to
```select file_store_id, extension, count(*) as count, sum(COALESCE(file_size,0)) as size from file f```
it works!!!!!
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] mushroom0V0 commented on issue #21406: pg database, the aggregate function in XML does not divide the table normally
Posted by GitBox <gi...@apache.org>.
mushroom0V0 commented on issue #21406:
URL: https://github.com/apache/shardingsphere/issues/21406#issuecomment-1272434805
您的邮件已收到，谢谢~
Best wishes to you~!!
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] mushroom0V0 commented on issue #21406: pg database, the aggregate function in XML does not divide the table normally
Posted by GitBox <gi...@apache.org>.
mushroom0V0 commented on issue #21406:
URL: https://github.com/apache/shardingsphere/issues/21406#issuecomment-1272432360
Maybe I should try the latest version and see the results
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] strongduanmu commented on issue #21406: pg database, the aggregate function in XML does not divide the table normally
Posted by GitBox <gi...@apache.org>.
strongduanmu commented on issue #21406:
URL: https://github.com/apache/shardingsphere/issues/21406#issuecomment-1272434740
Yes, you can try the latest 5.2.0 version. This version has do a great enhancement in sql parse.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] mushroom0V0 commented on issue #21406: pg database, the aggregate function in XML does not divide the table normally
Posted by GitBox <gi...@apache.org>.
mushroom0V0 commented on issue #21406:
URL: https://github.com/apache/shardingsphere/issues/21406#issuecomment-1272440746
😂Since there are many projects affected, is there any way not to upgrade the version and fix the error
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org