lwtdev edited a comment on issue #6497: URL: https://github.com/apache/shardingsphere/issues/6497#issuecomment-696590455
Hi~ @jingshanglu @tristaZero I Just run some subquery cases with newest version. Test results and detail as follows: |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 | - UnSupport Case Detail ``` 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: [email protected]
