lwtdev edited a comment on issue #6497:
URL: https://github.com/apache/shardingsphere/issues/6497#issuecomment-698708296
@jingshanglu Hi
> @lwtdev Now, I have fix some bug,but it is not support that query has no
sharding column.
There is a contradiction here， please have a look at these route result of
subquery that has no sharding colunm:
```
subqueryAsDerivedTableInFromClause[hasShardingKey:none]; Support:true;
SQL: SELECT sbf1, sbf2 FROM (SELECT party_id AS sbf1, status AS sbf2 FROM
customer) AS sb WHERE sbf2 > 1;
ds_00|SELECT sbf1, sbf2 FROM (SELECT party_id AS sbf1, status AS sbf2 FROM
customer_0000) AS sb WHERE sbf2 > 1|
ds_00|SELECT sbf1, sbf2 FROM (SELECT party_id AS sbf1, status AS sbf2 FROM
customer_0001) AS sb WHERE sbf2 > 1|
ds_01|SELECT sbf1, sbf2 FROM (SELECT party_id AS sbf1, status AS sbf2 FROM
customer_0002) AS sb WHERE sbf2 > 1|
ds_01|SELECT sbf1, sbf2 FROM (SELECT party_id AS sbf1, status AS sbf2 FROM
customer_0003) AS sb WHERE sbf2 > 1|
subqueryInComparisons[hasShardingKey:none]; Support:true; SQL: SELECT *
FROM customer WHERE status < (SELECT MAX(status) FROM customer_email);
ds_00|SELECT * FROM customer_0000 WHERE status < (SELECT MAX(status) FROM
customer_email_0000)|
ds_00|SELECT * FROM customer_0001 WHERE status < (SELECT MAX(status) FROM
customer_email_0001)|
ds_01|SELECT * FROM customer_0002 WHERE status < (SELECT MAX(status) FROM
customer_email_0002)|
ds_01|SELECT * FROM customer_0003 WHERE status < (SELECT MAX(status) FROM
customer_email_0003)|
```
> what is you expect result for ` SELECT * FROM customer where not exists
(select * from customer_email where c.status = status) and id = 3;`?
Sorry, this sql is wrong , I missing a table alias. route result after fix
this problem:
```
ShardingProxy(5.0.0.RC1)2DB2Table
subqueryWithNotExist[hasShardingKey:all]; Support:false; SQL: SELECT *
FROM customer c where not exists (select * from customer_email where c.status =
status and id = 3) and c.id = 3;
ds_00|SELECT * FROM customer_0000 c where not exists (select * from
customer_email where c.status = status and id = 3) and c.id = 3|
ds_00|SELECT * FROM customer_0001 c where not exists (select * from
customer_email where c.status = status and id = 3) and c.id = 3|
ds_01|SELECT * FROM customer_0002 c where not exists (select * from
customer_email where c.status = status and id = 3) and c.id = 3|
ds_01|SELECT * FROM customer_0003 c where not exists (select * from
customer_email where c.status = status and id = 3) and c.id = 3|
```
I think there are two problem in this scenario
1. Two table (`cusomter` and `cusomter_email`) both have sharding columns,
but broadcast to all table.
2. Two table (`cusomter` and `cusomter_email`) are binding tables, so
`customer_email` table name also should be
replace with physical table name like `customer_email_xxxx`.
Base on it, My expect result is:
```sql
ds_01|SELECT * FROM customer_0003 c where not exists (select * from
customer_email_0003 where c.status = status and id = 3) and c.id = 3|
```
> and can you test it again on master branch?
I will run test cases later.
----------------------------------------------------------------
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]
