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


   @jingshanglu 
   I just test some subquery with ShardingShpere-Proxy 5.0.0-RC1, found that there are still a lot of scenarios that are not supported.
    Which of the following scenarios will be supported in the future ？
   |SQL Demo Title |SQL Demo |SQL Contains Sharding Column |ShardingProxy(5.0.0.RC1) |
   |---- |---- |---- |---- |
   |subqueryAsScalarOperand |SELECT (SELECT name FROM customer); |NONE |N |
   |subqueryAsScalarOperand |SELECT (SELECT name FROM customer where id = 3); |ALL |N |
   |subqueryInComparisons |SELECT * FROM customer WHERE status < (SELECT MAX(status) FROM customer_email); |NONE |N |
   |subqueryInComparisons |SELECT * FROM customer WHERE status < (SELECT MAX(status) FROM customer_email) and id = 3; |SINGLE |N |
   |subqueryInComparisons |SELECT * FROM customer WHERE status < (SELECT MAX(status) FROM customer_email where id = 3) and id = 3; |ALL |N |
   |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 |N |
   |subqueryWithAny |SELECT * FROM customer WHERE status = ANY (SELECT status FROM customer_email where id = 3) and id = 3; |ALL |N |
   |subqueryWithAll |SELECT * FROM customer WHERE status > ALL (SELECT status FROM customer_email); |NONE |N |
   |subqueryWithAll |SELECT * FROM customer WHERE status > ALL (SELECT status FROM customer_email) and id = 3; |SINGLE |N |
   |subqueryWithAll |SELECT * FROM customer WHERE status > ALL (SELECT status FROM customer_email where id = 3) and id = 3; |ALL |N |
   |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 |N |
   |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; |SINGLE |N |
   |subqueryWithNotExist |SELECT * FROM customer where not exists (select * from customer_email where c.status = status); |NONE |N |
   |subqueryWithNotExist |SELECT * FROM customer where not exists (select * from customer_email where c.status = status) and id = 3; |SINGLE |N |
   |subqueryWithNotExist |SELECT * FROM customer where not exists (select * from customer_email where c.status = status and id = 3) and id = 3; |ALL |N |
   |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 |N |
   |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 |N |
   |subqueryInFromClause |SELECT sbf1, sbf2 FROM (SELECT party_id AS sbf1, status AS sbf2 FROM customer) AS sb WHERE sbf2 > 1; |NONE |Y |
   |subqueryInFromClause |SELECT sbf1, sbf2 FROM (SELECT party_id AS sbf1, status AS sbf2 FROM customer) AS sb WHERE sbf2 > 1 and id = 3; |SINGLE |Y |
   |subqueryInFromClause |SELECT sbf1, sbf2 FROM (SELECT party_id AS sbf1, status AS sbf2 FROM customer where id = 3) AS sb WHERE sbf2 > 1 and id = 3; |ALL |Y |
   |subqueryInLeftJoin |SELECT * FROM customer LEFT JOIN (SELECT party_id, status FROM customer_email WHERE is_deleted = 'N') email ON email.party_id = cusomter.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 = cusomter.party_id and customer.id = 3; |SINGLE |N |
   |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 = cusomter.party_id and customer.id = 3; |ALL |Y |
   |subqueryInSubquery |SELECT * FROM customer c WHERE status in (SELECT status FROM customer_email where status > (select id from full_table)); |NONE |N |
   |subqueryInSubquery |SELECT * FROM customer c WHERE status in (SELECT status FROM customer_email where status > (select id from full_table)) and customer.id = 3; |SINGLE |N |
   |subqueryInSubquery |SELECT * FROM customer c WHERE status in (SELECT status FROM customer_email where id = 3 and status > (select id from full_table where id = 3)) and customer.id = 3; |ALL |N |


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