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 2020/11/08 07:27:43 UTC
[GitHub] [shardingsphere] Ming5024 opened a new issue #8076: Sharding Table inner join doesn't behave as expected
Ming5024 opened a new issue #8076:
URL: https://github.com/apache/shardingsphere/issues/8076
## Bug Report
### Steps to reproduce the behavior
execute below sqls:
```
CREATE TABLE `t_order_test` (
`test_id` bigint(20) NOT NULL AUTO_INCREMENT,
`user_id` int(11) NOT NULL,
`order_id` bigint(20) NOT NULL,
PRIMARY KEY (`test_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
CREATE TABLE `t_order_test` (
`test_id` bigint(20) NOT NULL AUTO_INCREMENT,
`user_id` int(11) NOT NULL,
`order_id` bigint(20) NOT NULL,
PRIMARY KEY (`test_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
INSERT INTO `sharding_db`.`t_order` (`user_id`, `status`) VALUES (1, 'error');
INSERT INTO `sharding_db`.`t_order` (`user_id`, `status`) VALUES (1, 'done');
```
the datas in t_order table are:
order_id|user_id|status
-|-|-
532211747042361344|1|done
532211587092578305|1 |error
```
INSERT INTO `sharding_db`.`t_order_test` (`user_id`, `order_id`) VALUES (1, 532211587092578305);
INSERT INTO `sharding_db`.`t_order_test` (`user_id`, `order_id`) VALUES (1, 532211747042361344);
INSERT INTO `sharding_db`.`t_order_test` (`user_id`, `order_id`) VALUES (1, 532211747042361344);
```
the datas in t_order_test table are:
test_id|user_id|order_id
-|-|-
532211883030085633|1|532211747042361344
532211838511742976|1|532211587092578305
532211888822419456|1|532211747042361344
execute:
```
select t1.*, t2.* from t_order t1 inner join t_order_test t2 on t1.order_id = t2.order_id;
```
### Expected behavior
When I go to the actual database to do the query on actual table, I get some rows:
```
select t1.*, t2.* from t_order_0 t1 inner join t_order_test_3 t2 on t1.order_id = t2.order_id;
```
order_id|user_id|status|test_id|user_id1|order_id1
-|-|-|-|-|-
532211747042361344|1|done|532211883030085633|1|532211747042361344
```
select t1.*, t2.* from t_order_0 t1 inner join t_order_test_6 t2 on t1.order_id = t2.order_id;
```
order_id|user_id|status|test_id|user_id1|order_id1
-|-|-|-|-|-
532211747042361344|1|done|532211888822419456|1|532211747042361344
And I want to get the union of the whole rows.
### Actual behavior
order_id|user_id|status|test_id|user_id1|order_id1
-|-|-|-|-|-
null|null|null|null|null|null
### Which version of ShardingSphere did you use?
shardingsphere-4.1.1
### Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?
ShardingSphere-Proxy
### Reason analyze (If you can)
I turned on the sql show, and found that actually there are only two queries:
And the inner join query isn't executed on the left tables like t_order_test_3、t_order_test_4...
But I want to do the query on all actualDataNodes(2*10) and get the unions
such as `select t1.*, t2.* from t_order_${0..1} t1 inner join t_order_test_${0..9} t2 on t1.order_id = t2.order_id;`
is there any usages wrong?
### config-sharding.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
```
----------------------------------------------------------------
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:
users@infra.apache.org
[GitHub] [shardingsphere] strongduanmu commented on issue #8076: Sharding Table inner join doesn't behave as expected
Posted by GitBox <gi...@apache.org>.
strongduanmu commented on issue #8076:
URL: https://github.com/apache/shardingsphere/issues/8076#issuecomment-733397030
Close this issue because there is no reply for long time.
----------------------------------------------------------------
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:
users@infra.apache.org
[GitHub] [shardingsphere] strongduanmu closed issue #8076: Sharding Table inner join doesn't behave as expected
Posted by GitBox <gi...@apache.org>.
strongduanmu closed issue #8076:
URL: https://github.com/apache/shardingsphere/issues/8076
----------------------------------------------------------------
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:
users@infra.apache.org
[GitHub] [shardingsphere] strongduanmu commented on issue #8076: Sharding Table inner join doesn't behave as expected
Posted by GitBox <gi...@apache.org>.
strongduanmu commented on issue #8076:
URL: https://github.com/apache/shardingsphere/issues/8076#issuecomment-723703745
@Ming5024 Thank you very much for your feedback, I will investigate and confirm this issue. 😀
----------------------------------------------------------------
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:
users@infra.apache.org
[GitHub] [shardingsphere] strongduanmu commented on issue #8076: Sharding Table inner join doesn't behave as expected
Posted by GitBox <gi...@apache.org>.
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:
users@infra.apache.org