strongduanmu commented on issue #8076: URL: https://github.com/apache/shardingsphere/issues/8076#issuecomment-724060058
Hello @Ming5024, after testing, I found something wrong in your sharding config. The logical tables `t_order` and `t_order_test` are not binding tables. Binding tables are a couple of tables with the same sharding rules. You can refer to [official documents](https://shardingsphere.apache.org/document/current/cn/features/sharding/concept/sql/#%E7%BB%91%E5%AE%9A%E8%A1%A8) for more details. If you want to execute this sql with full route, you can remove the config of binding tables. ```yaml schemaName: sharding_db dataSources: shardingdb: url: jdbc:mysql://localhost:3306/shardingdb username: root password: ************ # autoCommit: true connectionTimeoutMilliseconds: 30000 idleTimeoutMilliseconds: 60000 maxLifetimeMilliseconds: 1800000 maxPoolSize: 50 shardingRule: tables: t_order: actualDataNodes: shardingdb.t_order_${0..1} tableStrategy: inline: shardingColumn: order_id algorithmExpression: t_order_${order_id % 2} keyGenerator: type: SNOWFLAKE column: order_id t_order_test: actualDataNodes: shardingdb.t_order_test_${0..9} tableStrategy: inline: shardingColumn: test_id algorithmExpression: t_order_test_${test_id % 10} keyGenerator: type: SNOWFLAKE column: test_id # bindingTables: # - t_order, t_order_test defaultDataSourceName: shardingdb defaultTableStrategy: none: defaultDatabaseStrategy: inline: shardingColumn: user_id algorithmExpression: shardingdb ``` And the results of SQL are as follows: | order\_id | user\_id | status | test\_id | user\_id | order\_id | | :--- | :--- | :--- | :--- | :--- | :--- | | 532687776211009536 | 1 | error | 532687974819692545 | 1 | 532687776211009536 | | 532687776211009536 | 1 | error | 532687978326130688 | 1 | 532687776211009536 | | 532687783093862401 | 1 | done | 532687970801549312 | 1 | 532687783093862401 | ``` [INFO ] 22:34:37.841 [ShardingSphere-Command-4] ShardingSphere-SQL - Logic SQL: /* ApplicationName=DataGrip 2020.2.3 */ select t1.*, t2.* from t_order t1 inner join t_order_test t2 on t1.order_id = t2.order_id [INFO ] 22:34:37.841 [ShardingSphere-Command-4] ShardingSphere-SQL - SQLStatement: SelectStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.SelectStatement@69f22f8a, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@366a1afa), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@366a1afa, projectionsContext=ProjectionsContext(startIndex=47, stopIndex=56, distinctRow=false, projections=[ShorthandProjection(owner=Optional[t1], actualColumns=[ColumnProjection(owner=t1, name=order_id, alias=Optional.empty), ColumnProjection(owner=t1, name=user_id, alias=Optional.empty), ColumnProjection(owner=t1, name=status, alias=Optional.empty)]), ShorthandProjection(owner=Optional[t2], actualColumns=[ColumnProjection(owner=t2, name=test_id, alias=Optional.empty), ColumnProjection(owner=t2, name=user_id, alias=Optional.empty), ColumnProjection(owner=t2, name=order_i d, alias=Optional.empty)])]), groupByContext=org.apache.shardingsphere.sql.parser.binder.segment.select.groupby.GroupByContext@778e421, orderByContext=org.apache.shardingsphere.sql.parser.binder.segment.select.orderby.OrderByContext@67c98daf, paginationContext=org.apache.shardingsphere.sql.parser.binder.segment.select.pagination.PaginationContext@1f3f70a3, containsSubquery=false) [INFO ] 22:34:37.842 [ShardingSphere-Command-4] ShardingSphere-SQL - Actual SQL: shardingdb ::: /* ApplicationName=DataGrip 2020.2.3 */ select t1.*, t2.* from t_order_0 t1 inner join t_order_test_1 t2 on t1.order_id = t2.order_id [INFO ] 22:34:37.842 [ShardingSphere-Command-4] ShardingSphere-SQL - Actual SQL: shardingdb ::: /* ApplicationName=DataGrip 2020.2.3 */ select t1.*, t2.* from t_order_0 t1 inner join t_order_test_0 t2 on t1.order_id = t2.order_id [INFO ] 22:34:37.842 [ShardingSphere-Command-4] ShardingSphere-SQL - Actual SQL: shardingdb ::: /* ApplicationName=DataGrip 2020.2.3 */ select t1.*, t2.* from t_order_0 t1 inner join t_order_test_3 t2 on t1.order_id = t2.order_id [INFO ] 22:34:37.842 [ShardingSphere-Command-4] ShardingSphere-SQL - Actual SQL: shardingdb ::: /* ApplicationName=DataGrip 2020.2.3 */ select t1.*, t2.* from t_order_0 t1 inner join t_order_test_2 t2 on t1.order_id = t2.order_id [INFO ] 22:34:37.842 [ShardingSphere-Command-4] ShardingSphere-SQL - Actual SQL: shardingdb ::: /* ApplicationName=DataGrip 2020.2.3 */ select t1.*, t2.* from t_order_0 t1 inner join t_order_test_5 t2 on t1.order_id = t2.order_id [INFO ] 22:34:37.842 [ShardingSphere-Command-4] ShardingSphere-SQL - Actual SQL: shardingdb ::: /* ApplicationName=DataGrip 2020.2.3 */ select t1.*, t2.* from t_order_0 t1 inner join t_order_test_4 t2 on t1.order_id = t2.order_id [INFO ] 22:34:37.842 [ShardingSphere-Command-4] ShardingSphere-SQL - Actual SQL: shardingdb ::: /* ApplicationName=DataGrip 2020.2.3 */ select t1.*, t2.* from t_order_0 t1 inner join t_order_test_7 t2 on t1.order_id = t2.order_id [INFO ] 22:34:37.842 [ShardingSphere-Command-4] ShardingSphere-SQL - Actual SQL: shardingdb ::: /* ApplicationName=DataGrip 2020.2.3 */ select t1.*, t2.* from t_order_0 t1 inner join t_order_test_6 t2 on t1.order_id = t2.order_id [INFO ] 22:34:37.842 [ShardingSphere-Command-4] ShardingSphere-SQL - Actual SQL: shardingdb ::: /* ApplicationName=DataGrip 2020.2.3 */ select t1.*, t2.* from t_order_0 t1 inner join t_order_test_9 t2 on t1.order_id = t2.order_id [INFO ] 22:34:37.842 [ShardingSphere-Command-4] ShardingSphere-SQL - Actual SQL: shardingdb ::: /* ApplicationName=DataGrip 2020.2.3 */ select t1.*, t2.* from t_order_0 t1 inner join t_order_test_8 t2 on t1.order_id = t2.order_id [INFO ] 22:34:37.842 [ShardingSphere-Command-4] ShardingSphere-SQL - Actual SQL: shardingdb ::: /* ApplicationName=DataGrip 2020.2.3 */ select t1.*, t2.* from t_order_1 t1 inner join t_order_test_1 t2 on t1.order_id = t2.order_id [INFO ] 22:34:37.842 [ShardingSphere-Command-4] ShardingSphere-SQL - Actual SQL: shardingdb ::: /* ApplicationName=DataGrip 2020.2.3 */ select t1.*, t2.* from t_order_1 t1 inner join t_order_test_0 t2 on t1.order_id = t2.order_id [INFO ] 22:34:37.842 [ShardingSphere-Command-4] ShardingSphere-SQL - Actual SQL: shardingdb ::: /* ApplicationName=DataGrip 2020.2.3 */ select t1.*, t2.* from t_order_1 t1 inner join t_order_test_3 t2 on t1.order_id = t2.order_id [INFO ] 22:34:37.842 [ShardingSphere-Command-4] ShardingSphere-SQL - Actual SQL: shardingdb ::: /* ApplicationName=DataGrip 2020.2.3 */ select t1.*, t2.* from t_order_1 t1 inner join t_order_test_2 t2 on t1.order_id = t2.order_id [INFO ] 22:34:37.842 [ShardingSphere-Command-4] ShardingSphere-SQL - Actual SQL: shardingdb ::: /* ApplicationName=DataGrip 2020.2.3 */ select t1.*, t2.* from t_order_1 t1 inner join t_order_test_5 t2 on t1.order_id = t2.order_id [INFO ] 22:34:37.842 [ShardingSphere-Command-4] ShardingSphere-SQL - Actual SQL: shardingdb ::: /* ApplicationName=DataGrip 2020.2.3 */ select t1.*, t2.* from t_order_1 t1 inner join t_order_test_4 t2 on t1.order_id = t2.order_id [INFO ] 22:34:37.843 [ShardingSphere-Command-4] ShardingSphere-SQL - Actual SQL: shardingdb ::: /* ApplicationName=DataGrip 2020.2.3 */ select t1.*, t2.* from t_order_1 t1 inner join t_order_test_7 t2 on t1.order_id = t2.order_id [INFO ] 22:34:37.843 [ShardingSphere-Command-4] ShardingSphere-SQL - Actual SQL: shardingdb ::: /* ApplicationName=DataGrip 2020.2.3 */ select t1.*, t2.* from t_order_1 t1 inner join t_order_test_6 t2 on t1.order_id = t2.order_id [INFO ] 22:34:37.843 [ShardingSphere-Command-4] ShardingSphere-SQL - Actual SQL: shardingdb ::: /* ApplicationName=DataGrip 2020.2.3 */ select t1.*, t2.* from t_order_1 t1 inner join t_order_test_9 t2 on t1.order_id = t2.order_id [INFO ] 22:34:37.843 [ShardingSphere-Command-4] ShardingSphere-SQL - Actual SQL: shardingdb ::: /* ApplicationName=DataGrip 2020.2.3 */ select t1.*, t2.* from t_order_1 t1 inner join t_order_test_8 t2 on t1.order_id = t2.order_id [INFO ] 22:34:37.942 [ShardingSphere-Command-5] ShardingSphere-SQL - Logic SQL: SHOW WARNINGS ``` ---------------------------------------------------------------- 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. For queries about this service, please contact Infrastructure at: [email protected]
