lwtdev commented on issue #6497: URL: https://github.com/apache/shardingsphere/issues/6497#issuecomment-701302611
@jingshanglu Hi The latest test results are here : - **Summary results** |SQL Demo Title |SQL Demo |Sharding Column |ShardingProxy(5.0.0.RC1)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 |Y | |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 = 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 > (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 | - **Unsuppot test case detail** ```sql 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 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 subqueryAsCorrelatedSubquery[hasShardingKey:none]; Support:false; SQL: SELECT * FROM customer WHERE status = ANY (SELECT status FROM customer_email WHERE customer.party_id = customer_email); java.sql.SQLException: 2Unknown exception: [Must have sharding column with subquery.] 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; java.sql.SQLException: 2Unknown exception: [Must have sharding column with subquery.] 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; java.sql.SQLException: 2Unknown exception: [Must have sharding column with subquery.] 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.] ``` You may have a look at this case (`customer` has sharding key, `customer` and `customer_email` are binding tables) : ```sql 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; 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: [email protected]
