[GitHub] [shardingsphere] lwtdev commented on issue #6497: Can subqueries be supported in select？

[GitBox <gi...@apache.org>]

lwtdev commented on issue #6497:
URL: https://github.com/apache/shardingsphere/issues/6497#issuecomment-723421718


   The latest test results with 5.0.0-beta : 
   
   - **Results Summary**
   
   |SQL Demo Title |SQL Demo |Sharding Column |ShardingProxy(5.0.0-beta)2DB2Table |
   |---- |---- |---- |---- |
   |subqueryAsScalarOperand |SELECT (SELECT name FROM customer); |NONE |Y |
   |subqueryAsScalarOperand |SELECT (SELECT name FROM customer where id = 3); |ALL |Y |
   |subqueryInColumns |SELECT id AS c_id , (select status FROM customer_email ce WHERE ce.party_id = c.party_id) AS ec_status FROM customer c; |NONE |N |
   |subqueryInColumns |SELECT id AS c_id , (select status FROM customer_email ce WHERE ce.party_id = c.party_id) AS ec_status FROM customer c WHERE id = 3; |SINGLE |Y |
   |subqueryInColumns |SELECT id AS c_id , (select status FROM customer_email ce WHERE ce.party_id = c.party_id and id = 3) AS ec_status FROM customer c WHERE id = 3; |ALL |Y |
   |subqueryInComparisons |SELECT * FROM customer WHERE status < (SELECT MAX(status) FROM customer_email); |NONE |Y |
   |subqueryInComparisons |SELECT * FROM customer WHERE status < (SELECT MAX(status) FROM customer_email) and id = 3; |SINGLE |Y |
   |subqueryInComparisons |SELECT * FROM customer WHERE status < (SELECT MAX(status) FROM customer_email where id = 3) and id = 3; |ALL |Y |
   |subqueryWithAny |SELECT * FROM customer WHERE status = ANY (SELECT status FROM customer_email); |NONE |N |
   |subqueryWithAny |SELECT * FROM customer WHERE status = ANY (SELECT status FROM customer_email) and id = 3; |SINGLE |Y |
   |subqueryWithAny |SELECT * FROM customer WHERE status = ANY (SELECT status FROM customer_email where id = 3) and id = 3; |ALL |Y |
   |subqueryWithAll |SELECT * FROM customer WHERE status > ALL (SELECT status FROM customer_email); |NONE |Y |
   |subqueryWithAll |SELECT * FROM customer WHERE status > ALL (SELECT status FROM customer_email) and id = 3; |SINGLE |Y |
   |subqueryWithAll |SELECT * FROM customer WHERE status > ALL (SELECT status FROM customer_email where id = 3) and id = 3; |ALL |Y |
   |subqueryAsRowSubquery |SELECT * FROM customer WHERE (party_id, status) = (SELECT party_id, status FROM customer_email WHERE party_id = 10); |NONE |N |
   |subqueryAsRowSubquery |SELECT * FROM customer WHERE (party_id, status) = (SELECT party_id, status FROM customer_email WHERE party_id = 10) and id = 3; |SINGLE |Y |
   |subqueryAsRowSubquery |SELECT * FROM customer WHERE (party_id, status) = (SELECT party_id, status FROM customer_email WHERE party_id = 10 and id = 3) and id = 3; |ALL |Y |
   |subqueryWithNotExist |SELECT * FROM customer c where not exists (select * from customer_email where c.status = status); |NONE |Y |
   |subqueryWithNotExist |SELECT * FROM customer c where not exists (select * from customer_email where c.status = status) and c.id = 3; |SINGLE |Y |
   |subqueryWithNotExist |SELECT * FROM customer c where not exists (select * from customer_email where c.status = status and id = 3) and c.id = 3; |ALL |Y |
   |subqueryWithIn |SELECT * FROM customer c WHERE status in (SELECT status FROM customer_email); |NONE |Y |
   |subqueryWithIn |SELECT * FROM customer c WHERE status in (SELECT status FROM customer_email) AND id = 3; |SINGLE |Y |
   |subqueryWithIn |SELECT * FROM customer c WHERE status in (SELECT status FROM customer_email WHERE id = 3 ) AND id = 3; |ALL |Y |
   |subqueryAsCorrelatedSubquery |SELECT * FROM customer WHERE status = ANY (SELECT status FROM customer_email WHERE customer.party_id = customer_email); |NONE |N |
   |subqueryAsCorrelatedSubquery |SELECT * FROM customer WHERE status = ANY (SELECT status FROM customer_email WHERE customer.party_id = customer_email) and id = 3; |SINGLE |Y |
   |subqueryAsCorrelatedSubquery |SELECT * FROM customer WHERE status = ANY (SELECT status FROM customer_email WHERE customer.party_id = customer_email.party_id and id = 3) and id = 3; |ALL |Y |
   |subqueryAsDerivedTableInFromClause |SELECT sbf1, sbf2 FROM (SELECT party_id AS sbf1, status AS sbf2 FROM customer) AS sb WHERE sbf2 > 1; |NONE |Y |
   |subqueryAsDerivedTableInFromClause |SELECT sbf1, sbf2 FROM (SELECT party_id AS sbf1, status AS sbf2 FROM customer where id = 3) AS sb WHERE sbf2 > 1; |ALL |Y |
   |subqueryAsDerivedTableWithCount |SELECT COUNT(*) FROM (SELECT * FROM customer) AS t; |NONE |Y |
   |subqueryAsDerivedTableWithCount |SELECT COUNT(*) FROM (SELECT * FROM customer WHERE id = 3) AS t; |ALL |Y |
   |subqueryInLeftJoin |SELECT * FROM customer LEFT JOIN (SELECT party_id, status FROM customer_email WHERE is_deleted = 'N') email ON email.party_id = customer.party_id; |NONE |N |
   |subqueryInLeftJoin |SELECT * FROM customer LEFT JOIN (SELECT party_id, status FROM customer_email WHERE is_deleted = 'N') email ON email.party_id = customer.party_id where customer.id = 3; |SINGLE |Y |
   |subqueryInLeftJoin |SELECT * FROM customer LEFT JOIN (SELECT party_id, status FROM customer_email WHERE is_deleted = 'N' and id = 3) email ON email.party_id = customer.party_id where customer.id = 3; |ALL |Y |
   |subqueryInSubquery |SELECT * FROM customer c WHERE status > (SELECT status FROM customer_email WHERE party_id > (SELECT id FROM full_table) LIMIT 1); |NONE |N |
   |subqueryInSubquery |SELECT * FROM customer c WHERE status > (SELECT status FROM customer_email WHERE party_id > (SELECT id FROM full_table) LIMIT 1) AND id = 3; |SINGLE |Y |
   |subqueryInSubquery |SELECT * FROM customer c WHERE status > (SELECT status FROM customer_email WHERE id = 3 AND party_id > (SELECT id FROM full_table WHERE id = 3) LIMIT 1) AND id = 3; |ALL |Y |
   
   - **Unsupport Detail**
   
   ```sql
   ShardingProxy(5.0.0-beta)2DB2Table
   subqueryInColumns[hasShardingKey:none];	 Support:false;	SQL: SELECT id AS c_id , (select status FROM customer_email ce WHERE ce.party_id = c.party_id) AS ec_status FROM customer c;
   java.sql.SQLException: 2Unknown exception: [Must have sharding column with subquery.]
   
   ShardingProxy(5.0.0-beta)2DB2Table
   subqueryWithAny[hasShardingKey:none];	 Support:false;	SQL: SELECT * FROM customer WHERE status = ANY (SELECT status FROM customer_email);
   ds_00|SELECT * FROM customer_0000 WHERE status = ANY (SELECT status FROM customer_email)|
   ds_00|SELECT * FROM customer_0001 WHERE status = ANY (SELECT status FROM customer_email)|
   ds_01|SELECT * FROM customer_0002 WHERE status = ANY (SELECT status FROM customer_email)|
   ds_01|SELECT * FROM customer_0003 WHERE status = ANY (SELECT status FROM customer_email)|
   
   
   ShardingProxy(5.0.0-beta)2DB2Table
   subqueryAsRowSubquery[hasShardingKey:none];	 Support:false;	SQL: SELECT * FROM customer WHERE (party_id, status) = (SELECT party_id, status FROM customer_email WHERE party_id = 10);
   java.sql.SQLException: 2Unknown exception: [Must have sharding column with subquery.]
   
   ShardingProxy(5.0.0-beta)2DB2Table
   subqueryAsCorrelatedSubquery[hasShardingKey:none];	 Support:false;	SQL: SELECT * FROM customer WHERE status = ANY (SELECT status FROM customer_email WHERE customer.party_id = customer_email);
   ds_00|SELECT * FROM customer_0000 WHERE status = ANY (SELECT status FROM customer_email WHERE customer.party_id = customer_email)|
   ds_00|SELECT * FROM customer_0001 WHERE status = ANY (SELECT status FROM customer_email WHERE customer.party_id = customer_email)|
   ds_01|SELECT * FROM customer_0002 WHERE status = ANY (SELECT status FROM customer_email WHERE customer.party_id = customer_email)|
   ds_01|SELECT * FROM customer_0003 WHERE status = ANY (SELECT status FROM customer_email WHERE customer.party_id = customer_email)|
   
   ```


----------------------------------------------------------------
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