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: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
