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:
[email protected]
