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


   Hi~ @jingshanglu @tristaZero 
   I have test some subquery case just now
   |SQL Demo Title |SQL Demo |Sharding Column |ShardingProxy(5.0.0.RC1)2DB2Table |
   |---- |---- |---- |---- |
   |subqueryAsScalarOperand |SELECT (SELECT name FROM customer); |NONE |N |
   |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 |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 |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 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 |
   |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 |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 |
   |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 = 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 |N |
   |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 |
   
   And not support sql detail as follow:
   ```
   ShardingProxy(5.0.0.RC1)2DB2Table
   subqueryAsScalarOperand[hasShardingKey:none];	 Support:false;	SQL: SELECT (SELECT name FROM customer);
   java.sql.SQLException: 2Unknown exception: [String index out of range: 35]
   
   ShardingProxy(5.0.0.RC1)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.RC1)2DB2Table
   subqueryWithAny[hasShardingKey:none];	 Support:false;	SQL: SELECT * FROM customer WHERE status = ANY (SELECT status FROM customer_email);
   com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: This version of ShardingProxy doesn't yet support this SQL. 'Unsupported SQL of `SELECT * FROM customer WHERE status = ANY (SELECT status FROM customer_email)`'
   
   ShardingProxy(5.0.0.RC1)2DB2Table
   subqueryWithAny[hasShardingKey:single];	 Support:false;	SQL: SELECT * FROM customer WHERE status = ANY (SELECT status FROM customer_email) and id = 3;
   com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: This version of ShardingProxy doesn't yet support this SQL. 'Unsupported SQL of `SELECT * FROM customer WHERE status = ANY (SELECT status FROM customer_email) and id = 3`'
   
   ShardingProxy(5.0.0.RC1)2DB2Table
   subqueryWithAny[hasShardingKey:all];	 Support:false;	SQL: SELECT * FROM customer WHERE status = ANY (SELECT status FROM customer_email where id = 3) and id = 3;
   com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: This version of ShardingProxy doesn't yet support this SQL. 'Unsupported SQL of `SELECT * FROM customer WHERE status = ANY (SELECT status FROM customer_email where id = 3) and id = 3`'
   
   ShardingProxy(5.0.0.RC1)2DB2Table
   subqueryWithAll[hasShardingKey:none];	 Support:false;	SQL: SELECT * FROM customer WHERE status > ALL (SELECT status FROM customer_email);
   com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: This version of ShardingProxy doesn't yet support this SQL. 'Unsupported SQL of `SELECT * FROM customer WHERE status > ALL (SELECT status FROM customer_email)`'
   
   ShardingProxy(5.0.0.RC1)2DB2Table
   subqueryWithAll[hasShardingKey:single];	 Support:false;	SQL: SELECT * FROM customer WHERE status > ALL (SELECT status FROM customer_email) and id = 3;
   com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: This version of ShardingProxy doesn't yet support this SQL. 'Unsupported SQL of `SELECT * FROM customer WHERE status > ALL (SELECT status FROM customer_email) and id = 3`'
   
   ShardingProxy(5.0.0.RC1)2DB2Table
   subqueryWithAll[hasShardingKey:all];	 Support:false;	SQL: SELECT * FROM customer WHERE status > ALL (SELECT status FROM customer_email where id = 3) and id = 3;
   com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: This version of ShardingProxy doesn't yet support this SQL. 'Unsupported SQL of `SELECT * FROM customer WHERE status > ALL (SELECT status FROM customer_email where id = 3) and id = 3`'
   
   ShardingProxy(5.0.0.RC1)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.RC1)2DB2Table
   subqueryWithNotExist[hasShardingKey:none];	 Support:false;	SQL: SELECT * FROM customer where not exists (select * from customer_email where c.status = status);
   ds_00|SELECT * FROM customer_0000 where not exists (select * from customer_email where c.status = status)|
   ds_00|SELECT * FROM customer_0001 where not exists (select * from customer_email where c.status = status)|
   ds_01|SELECT * FROM customer_0002 where not exists (select * from customer_email where c.status = status)|
   ds_01|SELECT * FROM customer_0003 where not exists (select * from customer_email where c.status = status)|
   
   
   ShardingProxy(5.0.0.RC1)2DB2Table
   subqueryWithNotExist[hasShardingKey:single];	 Support:false;	SQL: SELECT * FROM customer where not exists (select * from customer_email where c.status = status) and id = 3;
   ds_00|SELECT * FROM customer_0000 where not exists (select * from customer_email where c.status = status) and id = 3|
   ds_00|SELECT * FROM customer_0001 where not exists (select * from customer_email where c.status = status) and id = 3|
   ds_01|SELECT * FROM customer_0002 where not exists (select * from customer_email where c.status = status) and id = 3|
   ds_01|SELECT * FROM customer_0003 where not exists (select * from customer_email where c.status = status) and id = 3|
   
   
   ShardingProxy(5.0.0.RC1)2DB2Table
   subqueryWithNotExist[hasShardingKey:all];	 Support:false;	SQL: SELECT * FROM customer where not exists (select * from customer_email where c.status = status and id = 3) and id = 3;
   ds_00|SELECT * FROM customer_0000 where not exists (select * from customer_email where c.status = status and id = 3) and id = 3|
   ds_00|SELECT * FROM customer_0001 where not exists (select * from customer_email where c.status = status and id = 3) and id = 3|
   ds_01|SELECT * FROM customer_0002 where not exists (select * from customer_email where c.status = status and id = 3) and id = 3|
   ds_01|SELECT * FROM customer_0003 where not exists (select * from customer_email where c.status = status and id = 3) and id = 3|
   
   ShardingProxy(5.0.0.RC1)2DB2Table
   subqueryAsCorrelatedSubquery[hasShardingKey:none];	 Support:false;	SQL: SELECT * FROM customer WHERE status = ANY (SELECT status FROM customer_email WHERE customer.party_id = customer_email);
   com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: This version of ShardingProxy doesn't yet support this SQL. 'Unsupported SQL of `SELECT * FROM customer WHERE status = ANY (SELECT status FROM customer_email WHERE customer.party_id = customer_email)`'
   
   ShardingProxy(5.0.0.RC1)2DB2Table
   subqueryAsCorrelatedSubquery[hasShardingKey:single];	 Support:false;	SQL: SELECT * FROM customer WHERE status = ANY (SELECT status FROM customer_email WHERE customer.party_id = customer_email) and id = 3;
   com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: This version of ShardingProxy doesn't yet support this SQL. 'Unsupported SQL of `SELECT * FROM customer WHERE status = ANY (SELECT status FROM customer_email WHERE customer.party_id = customer_email) and id = 3`'
   
   ShardingProxy(5.0.0.RC1)2DB2Table
   subqueryAsCorrelatedSubquery[hasShardingKey:all];	 Support:false;	SQL: 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;
   com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: This version of ShardingProxy doesn't yet support this SQL. 'Unsupported SQL of `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`'
   
   ShardingProxy(5.0.0.RC1)2DB2Table
   subqueryInLeftJoin[hasShardingKey:none];	 Support:false;	SQL: SELECT * FROM customer LEFT JOIN (SELECT party_id, status FROM customer_email WHERE is_deleted = 'N') email ON email.party_id = cusomter.party_id;
   com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: This version of ShardingProxy doesn't yet support this SQL. 'Unsupported SQL of `SELECT * FROM customer LEFT JOIN (SELECT party_id, status FROM customer_email WHERE is_deleted = 'N') email ON email.party_id = cusomter.party_id`'
   
   ShardingProxy(5.0.0.RC1)2DB2Table
   subqueryInLeftJoin[hasShardingKey:single];	 Support:false;	SQL: 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;
   com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: This version of ShardingProxy doesn't yet support this SQL. 'Unsupported SQL of `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`'
   
   ShardingProxy(5.0.0.RC1)2DB2Table
   subqueryInLeftJoin[hasShardingKey:all];	 Support:false;	SQL: 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;
   com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: This version of ShardingProxy doesn't yet support this SQL. 'Unsupported SQL of `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`'
   
   ShardingProxy(5.0.0.RC1)2DB2Table
   subqueryInSubquery[hasShardingKey:none];	 Support:false;	SQL: SELECT * FROM customer c WHERE status > (SELECT status FROM customer_email WHERE party_id > (SELECT id FROM full_table) LIMIT 1);
   java.sql.SQLException: 2Unknown exception: [Must have sharding column with subquery.]
   
   ```


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