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 2021/10/17 00:47:53 UTC
[GitHub] [shardingsphere] zhangwangren opened a new issue #13056: in the 5.0.0-RC1 version the sharind table can't join broadcast table
zhangwangren opened a new issue #13056:
URL: https://github.com/apache/shardingsphere/issues/13056
## Bug Report
### Which version of ShardingSphere did you use?
5.0.0-RC1
### Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?
ShardingSphere-JDBC
### Expected behavior
i'd like to use normal table to JOIN broadcast table and i need use sharding table to JOIN broadcast table too.
### Actual behavior
in the 5.0.0-RC1 version, the normal table can JOIN broadcast table but sharding table can't.
### Reason analyze (If you can)
### Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc.
#### my normal table is:
reservoir_water_data_latest
#### my broadcast table config is:
spring.shardingsphere.sharding.broadcast-tables[0]=reservoir_flood_limit
spring.shardingsphere.sharding.tables.reservoir_flood_limit.actual-data-nodes=ds$->{0}.reservoir_flood_limit
#### my sharinding table config is:
spring.shardingsphere.rules.sharding.sharding-algorithms.db-class-based.type=CLASS_BASED
spring.shardingsphere.rules.sharding.sharding-algorithms.db-class-based.props.strategy=STANDARD
spring.shardingsphere.rules.sharding.sharding-algorithms.db-class-based.props.algorithmClassName=com.sky.dam.water.config.sharding.TimeDatabaseShardingAlgorithm
spring.shardingsphere.rules.sharding.sharding-algorithms.tb-class-based.type=CLASS_BASED
spring.shardingsphere.rules.sharding.sharding-algorithms.tb-class-based.props.strategy=STANDARD
spring.shardingsphere.rules.sharding.sharding-algorithms.tb-class-based.props.algorithmClassName=com.sky.dam.water.config.sharding.TimeTableShardingAlgorithm
spring.shardingsphere.rules.sharding.default-database-strategy.standard.sharding-column=TM
spring.shardingsphere.rules.sharding.default-database-strategy.standard.sharding-algorithm-name=db-class-based
spring.shardingsphere.rules.sharding.tables.reservoir_water_data.actual-data-nodes=ds$->{0..9}.reservoir_water_data_0$->{1..9},ds$->{0..9}.reservoir_water_data_1$->{0..2}
spring.shardingsphere.rules.sharding.tables.reservoir_water_data.table-strategy.standard.sharding-column=TM
spring.shardingsphere.rules.sharding.tables.reservoir_water_data.table-strategy.standard.sharding-algorithm-name=tb-class-based
#### the sql and problem is:
This sql is work:
SELECT wdl.STCD. wdl.TM
FROM reservoir_water_data_latest wdl
LEFT JOIN reservoir_flood_limit rfl ON rfl.STCD = wdl.STCD
WHERE 1=1
AND ...
but this sql doesn't work:
SELECT rwd.STCD. rwd.TM, rwd.ADDVCD
FROM reservoir_water_data rwd
LEFT JOIN reservoir_flood_limit rfl ON rfl.STCD = rwd.STCD
WHERE 1=1
AND rwd.TM > '2021-10-15 00:00:00' AND rwd.TM < '2021-10-15 11:00:00'
GROUP BY rwd.ADDVCD
### Example codes for reproduce this issue (such as a github link).
Throw a Exception like this:
at org.apache.calcite.avatica.Helper.createException(Helper.java:56) ~[avatica-core-1.18.0.jar:1.18.0]
at org.apache.calcite.avatica.Helper.createException(Helper.java:41) ~[avatica-core-1.18.0.jar:1.18.0]
at org.apache.calcite.jdbc.CalciteConnectionImpl.prepareStatement_(CalciteConnectionImpl.java:239) ~[calcite-core-1.27.0.jar:1.27.0]
at org.apache.calcite.jdbc.CalciteConnectionImpl.prepareStatement(CalciteConnectionImpl.java:218) ~[calcite-core-1.27.0.jar:1.27.0]
at org.apache.calcite.jdbc.CalciteConnectionImpl.prepareStatement(CalciteConnectionImpl.java:101) ~[calcite-core-1.27.0.jar:1.27.0]
at org.apache.calcite.avatica.AvaticaConnection.prepareStatement(AvaticaConnection.java:175) ~[avatica-core-1.18.0.jar:1.18.0]
at org.apache.shardingsphere.infra.executor.sql.federate.original.OriginalFilterableExecutor.execute(OriginalFilterableExecutor.java:84) ~[shardingsphere-infra-executor-5.0.0-RC1-SNAPSHOT.jar:5.0.0-RC1-SNAPSHOT]
at org.apache.shardingsphere.infra.executor.sql.federate.original.OriginalFilterableExecutor.executeQuery(OriginalFilterableExecutor.java:77) ~[shardingsphere-infra-executor-5.0.0-RC1-SNAPSHOT.jar:5.0.0-RC1-SNAPSHOT]
at org.apache.shardingsphere.driver.jdbc.core.statement.ShardingSpherePreparedStatement.executeFederatedQuery(ShardingSpherePreparedStatement.java:223) ~[shardingsphere-jdbc-core-5.0.0-RC1-SNAPSHOT.jar:5.0.0-RC1-SNAPSHOT]
at org.apache.shardingsphere.driver.jdbc.core.statement.ShardingSpherePreparedStatement.execute(ShardingSpherePreparedStatement.java:293) ~[shardingsphere-jdbc-core-5.0.0-RC1-SNAPSHOT.jar:5.0.0-RC1-SNAPSHOT]
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_172]
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_172]
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_172]
at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_172]
at org.apache.ibatis.logging.jdbc.PreparedStatementLogger.invoke(PreparedStatementLogger.java:59) ~[mybatis-3.5.7.jar:3.5.7]
at com.sun.proxy.$Proxy369.execute(Unknown Source) ~[na:na]
at org.apache.ibatis.executor.statement.PreparedStatementHandler.query(PreparedStatementHandler.java:64) ~[mybatis-3.5.7.jar:3.5.7]
at org.apache.ibatis.executor.statement.RoutingStatementHandler.query(RoutingStatementHandler.java:79) ~[mybatis-3.5.7.jar:3.5.7]
at org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:63) ~[mybatis-3.5.7.jar:3.5.7]
at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:325) ~[mybatis-3.5.7.jar:3.5.7]
at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:156) ~[mybatis-3.5.7.jar:3.5.7]
at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:109) ~[mybatis-3.5.7.jar:3.5.7]
at com.github.pagehelper.PageInterceptor.intercept(PageInterceptor.java:111) ~[pagehelper-5.2.0.jar:na]
at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:62) ~[mybatis-3.5.7.jar:3.5.7]
at com.sun.proxy.$Proxy367.query(Unknown Source) ~[na:na]
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:151) ~[mybatis-3.5.7.jar:3.5.7]
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:145) ~[mybatis-3.5.7.jar:3.5.7]
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:140) ~[mybatis-3.5.7.jar:3.5.7]
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_172]
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_172]
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_172]
at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_172]
at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:427) ~[mybatis-spring-2.0.6.jar:2.0.6]
... 88 common frames omitted
Caused by: org.apache.calcite.runtime.CalciteContextException: From line 3, column 5 to line 3, column 10: Expression 'rwd.TM' is not being grouped
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) ~[na:1.8.0_172]
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) ~[na:1.8.0_172]
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) ~[na:1.8.0_172]
at java.lang.reflect.Constructor.newInstance(Constructor.java:423) ~[na:1.8.0_172]
at org.apache.calcite.runtime.Resources$ExInstWithCause.ex(Resources.java:506) ~[calcite-core-1.27.0.jar:1.27.0]
at org.apache.calcite.sql.SqlUtil.newContextException(SqlUtil.java:917) ~[calcite-core-1.27.0.jar:1.27.0]
at org.apache.calcite.sql.SqlUtil.newContextException(SqlUtil.java:902) ~[calcite-core-1.27.0.jar:1.27.0]
at org.apache.calcite.sql.validate.SqlValidatorImpl.newValidationError(SqlValidatorImpl.java:5271) ~[calcite-core-1.27.0.jar:1.27.0]
at org.apache.calcite.sql.validate.AggChecker.visit(AggChecker.java:116) ~[calcite-core-1.27.0.jar:1.27.0]
at org.apache.calcite.sql.validate.AggChecker.visit(AggChecker.java:43) ~[calcite-core-1.27.0.jar:1.27.0]
at org.apache.calcite.sql.SqlIdentifier.accept(SqlIdentifier.java:325) ~[calcite-core-1.27.0.jar:1.27.0]
at org.apache.calcite.sql.validate.AggregatingSelectScope.checkAggregateExpr(AggregatingSelectScope.java:218) ~[calcite-core-1.27.0.jar:1.27.0]
at org.apache.calcite.sql.validate.AggregatingSelectScope.validateExpr(AggregatingSelectScope.java:227) ~[calcite-core-1.27.0.jar:1.27.0]
at org.apache.calcite.sql.validate.SqlValidatorImpl.validateExpr(SqlValidatorImpl.java:4472) ~[calcite-core-1.27.0.jar:1.27.0]
at org.apache.calcite.sql.validate.SqlValidatorImpl.validateSelectList(SqlValidatorImpl.java:4443) ~[calcite-core-1.27.0.jar:1.27.0]
at org.apache.calcite.sql.validate.SqlValidatorImpl.validateSelect(SqlValidatorImpl.java:3657) ~[calcite-core-1.27.0.jar:1.27.0]
at org.apache.calcite.sql.validate.SelectNamespace.validateImpl(SelectNamespace.java:64) ~[calcite-core-1.27.0.jar:1.27.0]
at org.apache.calcite.sql.validate.AbstractNamespace.validate(AbstractNamespace.java:89) ~[calcite-core-1.27.0.jar:1.27.0]
at org.apache.calcite.sql.validate.SqlValidatorImpl.validateNamespace(SqlValidatorImpl.java:1098) ~[calcite-core-1.27.0.jar:1.27.0]
at org.apache.calcite.sql.validate.SqlValidatorImpl.validateQuery(SqlValidatorImpl.java:1069) ~[calcite-core-1.27.0.jar:1.27.0]
at org.apache.calcite.sql.SqlSelect.validate(SqlSelect.java:247) ~[calcite-core-1.27.0.jar:1.27.0]
at org.apache.calcite.sql.validate.SqlValidatorImpl.validateScopedExpression(SqlValidatorImpl.java:1044) ~[calcite-core-1.27.0.jar:1.27.0]
at org.apache.calcite.sql.validate.SqlValidatorImpl.validate(SqlValidatorImpl.java:750) ~[calcite-core-1.27.0.jar:1.27.0]
at org.apache.calcite.sql2rel.SqlToRelConverter.convertQuery(SqlToRelConverter.java:585) ~[calcite-core-1.27.0.jar:1.27.0]
at org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:251) ~[calcite-core-1.27.0.jar:1.27.0]
at org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:215) ~[calcite-core-1.27.0.jar:1.27.0]
at org.apache.calcite.prepare.CalcitePrepareImpl.prepare2_(CalcitePrepareImpl.java:647) ~[calcite-core-1.27.0.jar:1.27.0]
at org.apache.calcite.prepare.CalcitePrepareImpl.prepare_(CalcitePrepareImpl.java:513) ~[calcite-core-1.27.0.jar:1.27.0]
at org.apache.calcite.prepare.CalcitePrepareImpl.prepareSql(CalcitePrepareImpl.java:483) ~[calcite-core-1.27.0.jar:1.27.0]
at org.apache.calcite.jdbc.CalciteConnectionImpl.parseQuery(CalciteConnectionImpl.java:249) ~[calcite-core-1.27.0.jar:1.27.0]
at org.apache.calcite.jdbc.CalciteConnectionImpl.prepareStatement_(CalciteConnectionImpl.java:229) ~[calcite-core-1.27.0.jar:1.27.0]
... 118 common frames omitted
Caused by: org.apache.calcite.sql.validate.SqlValidatorException: Expression 'rwd.TM' is not being grouped
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) ~[na:1.8.0_172]
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) ~[na:1.8.0_172]
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) ~[na:1.8.0_172]
at java.lang.reflect.Constructor.newInstance(Constructor.java:423) ~[na:1.8.0_172]
at org.apache.calcite.runtime.Resources$ExInstWithCause.ex(Resources.java:506) ~[calcite-core-1.27.0.jar:1.27.0]
at org.apache.calcite.runtime.Resources$ExInst.ex(Resources.java:600) ~[calcite-core-1.27.0.jar:1.27.0]
... 144 common frames omitted
--
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] zhangwangren closed issue #13056: in the 5.0.0-RC1 version the sharind table can't join broadcast table
Posted by GitBox <gi...@apache.org>.
zhangwangren closed issue #13056:
URL: https://github.com/apache/shardingsphere/issues/13056
--
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