Can subqueries be supported in select？

apache / shardingsphere

Empowering Data Intelligence with Distributed SQL for Sharding, Scalability, and Security Across All Databases.

Apache License 2.0

19.97k stars 6.75k forks source link

Can subqueries be supported in select？ #6497

Closed jasper520 closed 4 years ago

jasper520 commented 4 years ago

for example：SELECT hos_province_id AS hosProvinceId, (SELECT zmmc FROM dict_city WHERE zd_id = hos_province_id) provinceName FROM hospital WHERE del_flag = 0 GROUP BY hos_province_id

When I use this query, the value of the provinceName field cannot be obtained

tristaZero commented 4 years ago

Hi @awnurrk 4.x releases have no great support for subquery, but our master branch has better support for them. Please be patient for our incoming release.

Thanks, Trista

jingshanglu commented 4 years ago

@awnurrk Now, subquery in fromclause is supported, others will be supported in the future.

tristaZero commented 4 years ago

@jingshanglu Waiting for your fixing. :)

lwtdev commented 4 years ago

@jingshanglu We have a plan to replace current sharding middleware with ShardingShpere-Proxy. But found that there are still a lot of subqueries not supported in ShardingShpere-Proxy 5.0.0-RC1. Does the following subqueries will be supported in the future ？	SQL Demo Title	SQL Demo	SQL Contains Sharding Column
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;	ALL	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

jingshanglu commented 4 years ago

@jingshanglu We have a plan to replace current sharding middleware with ShardingShpere-Proxy. But found that there are still a lot of subqueries not supported in ShardingShpere-Proxy 5.0.0-RC1. Does the following subqueries will be supported in the future ？

SQL Demo Title SQL Demo SQL Contains Sharding Column ShardingProxy(5.0.0.RC1) 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; ALL 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

Thank you very much for your feedback,i'll check it.

tristaZero commented 4 years ago

Hi @lwtdev

Very appreciated your tests and attention.

After discussing with @jingshanglu , we plan to support all Join Query and Subquery Query before 5.x release. However, you have to know that all these queries with more than one table can work well only when queried tables are in the same database instance(5.0.0.alpha)! Namely, join or subquery queries cross-instance are unsupported. Supporting cross-instance queries is in the future schedule.

BTW, would you like to join this community and help test all the SQL cases you mentioned? :)

Trista

lwtdev commented 4 years ago

Hi @lwtdev

Very appreciated your tests and attention.

After discussing with @jingshanglu , we plan to support all Join Query and Subquery Query before 5.x release. However, you have to know that all these queries with more than one table can work well only when queried tables are in the same database instance(5.0.0.alpha)! Namely, join or subquery queries cross-instance are unsupported. Supporting cross-instance queries is in the future schedule. Trista

@tristaZero Thanks so much for your reply， It's enough for supporting no cross-instance queries for us.

BTW, would you like to join this community and help test all the SQL cases you mentioned? :)

It's my pleasure, what do I need to do?

tristaZero commented 4 years ago

Hi @lwtdev Just watch this ISSUE. Any changes(PRs) will be linked here. If you see any PR linked, please give these changes a test. Besides, since the workload of this issue is heavy, I will consider seeking help from other contributors. Thanks,

Trista

lwtdev commented 4 years ago

@tristaZero OK

hnxphnxp commented 4 years ago

The same question with us,it's good news that this feature will be supported in the future.I have subscribed this issue as 5.x version hasn't due date.Come on!

tristaZero commented 4 years ago

@hnxphnxp Hi thanks for your attention.

I am glad to say we have support all the subquery parsing (#6837 Thanks to @jingshanglu 's effort) and Supported SQL showed us the supported subquery SQLs. But it has to clarify that we can not guarantee all the subquery could run well, especially subquery tables across different instances. Hence, we need your help to do some tests if it captures your interest. Moreover, the test report is welcomed as well to help this feature improved!@awnurrk @hnxphnxp

yanhj93 commented 4 years ago

@tristaZero @jingshanglu Hi, I have met these subquery sqls not working very well. Could you help with this? Following are errors given by proxy when executing the example sql. Tested on master branch.

error-must have sharding column in subquery ,select count(0) from (select * from t_order where createdDate > '2020-02-01') a;
error-Can not find owner from table , select count(*) from (select * from t_order) a join (select * from t_order)b on a.id = b.id where a.id=10;

limit syntax in subquery, not rewrite as the log shows and will return 40 rows finally ------------ select id from (select * from t_order limit 10)a

Actual SQL: ds0 ::: select id from (select * from t_order limit 10)a
Actual SQL: ds1 ::: select id from (select * from t_order limit 10)a
Actual SQL: ds2 ::: select id from (select * from t_order limit 10)a
Actual SQL: ds3 ::: select id from (select * from t_order limit 10)a

config


t_order:
        actualDataNodes: ds${0..3}.t_order
        databaseStrategy:
          standard:
            shardingAlgorithmName: database_mod
            shardingColumn: id

tristaZero commented 4 years ago

@chana71 Thanks for your feedback. @jingshanglu Please give them a check, thx.

Fujinzhong commented 4 years ago

Hi,all, This sharding-jdbc-4.1.1s log ShardingSphere-SQL - Logic SQL: SELECTdate, customer_namename, customer_accountaccount, customer_phonephone, count( 1 ) total, sum( CASE WHEN CODE IN ( 30,- 30 ) THEN 1 ELSE 0 END ) AS succ, sum( CASE WHEN CODE IN ( - 30,- 20 ) THEN 1 ELSE 0 END ) AS fail, sum( CASE WHEN CODE IN ( 20 ) THEN 1 ELSE 0 END ) AS excep, sum( CASE WHEN CODE IN ( 30 ) THEN 1 ELSE 0 END ) AS report_succ, sum( CASE WHEN CODE IN ( - 30 ) THEN 1 ELSE 0 END ) AS report_fail, sum( CASE WHEN CODE IN ( - 11 ) THEN 1 ELSE 0 END ) ASrejectFROM ( SELECT customer_account,customer_phone,phone phone_s, customer_name, date_format( create_time, '%Y-%m-%d' ) ASdate,max( CODE ) AS CODE FROM mms_order WHERE customer_account IS NOT NULL and create_time between ? and ? GROUP BY customer_account,send_id,customer_phone,phone,customer_name,date) a GROUP BYdate,customer_name,customer_account,customer_phone ShardingSphere-SQL - Actual SQL: master ::: SELECTdate, customer_namename, customer_accountaccount, customer_phonephone, count( 1 ) total, sum( CASE WHEN CODE IN ( 30,- 30 ) THEN 1 ELSE 0 END ) AS succ, sum( CASE WHEN CODE IN ( - 30,- 20 ) THEN 1 ELSE 0 END ) AS fail, sum( CASE WHEN CODE IN ( 20 ) THEN 1 ELSE 0 END ) AS excep, sum( CASE WHEN CODE IN ( 30 ) THEN 1 ELSE 0 END ) AS report_succ, sum( CASE WHEN CODE IN ( - 30 ) THEN 1 ELSE 0 END ) AS report_fail, sum( CASE WHEN CODE IN ( - 11 ) THEN 1 ELSE 0 END ) ASrejectFROM ( SELECT customer_account,customer_phone,phone phone_s, customer_name, date_format( create_time, '%Y-%m-%d' ) ASdate,max( CODE ) AS CODE FROM mms_order WHERE customer_account IS NOT NULL and create_time between ? and ? GROUP BY customer_account,send_id,customer_phone,phone,customer_name,date) a GROUP BYdate`,customer_name,customer_account,customer_phone ::: [2020-09-18 00:00:00, 2020-09-18 23:59:59]. I use create_time as sharding column,mms_order is sharding table,the database is MySql. Can you explain why this query can not use sharding table?(BTW,this is my first time to ask question in github and use English to raise question) @lwtdev @jingshanglu @chana71 @tristaZero

tristaZero commented 4 years ago

@Fujinzhong There is still some routing and parsing info in your log, which will show you how ShardingSphere handles your SQL. Could you give them a look?

Fujinzhong commented 4 years ago

@Fujinzhong There is still some routing and parsing info in your log, which will show you how ShardingSphere handles your SQL. Could you give them a look?

09:49:55.788 [main] INFO ShardingSphere-SQL - Logic SQL: SELECT date, customer_name name, customer_account account, customer_phone phone, count( 1 ) total, sum( CASE WHEN CODE IN ( 30,- 30 ) THEN 1 ELSE 0 END ) AS succ, sum( CASE WHEN CODE IN ( - 30,- 20 ) THEN 1 ELSE 0 END ) AS fail, sum( CASE WHEN CODE IN ( 20 ) THEN 1 ELSE 0 END ) AS excep, sum( CASE WHEN CODE IN ( 30 ) THEN 1 ELSE 0 END ) AS report_succ, sum( CASE WHEN CODE IN ( - 30 ) THEN 1 ELSE 0 END ) AS report_fail, sum( CASE WHEN CODE IN ( - 11 ) THEN 1 ELSE 0 END ) AS reject FROM ( SELECT customer_account,customer_phone,phone phone_s, customer_name, date_format( create_time, '%Y-%m-%d' ) AS date,max( CODE ) AS CODE FROM mms_order WHERE customer_account IS NOT NULL and create_time between ? and ? GROUP BY customer_account,send_id,customer_phone,phone,customer_name,date ) a GROUP BY date,customer_name,customer_account,customer_phone 09:49:55.789 [main] INFO ShardingSphere-SQL - SQLStatement: SelectStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.SelectStatement@1f1a57e8, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@5a056d39), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@5a056d39, projectionsContext=ProjectionsContext(startIndex=8, stopIndex=494, distinctRow=false, projections=[ColumnProjection(owner=null, name=date, alias=Optional.empty), ColumnProjection(owner=null, name=customer_name, alias=Optional[name]), ColumnProjection(owner=null, name=customer_account, alias=Optional[account]), ColumnProjection(owner=null, name=customer_phone, alias=Optional[phone]), AggregationProjection(type=COUNT, innerExpression=( 1 ), alias=Optional[total], derivedAggregationProjections=[], index=-1), AggregationProjection(type=SUM, innerExpression=( CASE WHEN CODE IN ( 30,- 30 ) THEN 1 ELSE 0 END ), alias=Optional[succ], derivedAggregationProjections=[], index=-1), AggregationProjection(type=SUM, innerExpression=( CASE WHEN CODE IN ( - 30,- 20 ) THEN 1 ELSE 0 END ), alias=Optional[fail], derivedAggregationProjections=[], index=-1), AggregationProjection(type=SUM, innerExpression=( CASE WHEN CODE IN ( 20 ) THEN 1 ELSE 0 END ), alias=Optional[excep], derivedAggregationProjections=[], index=-1), AggregationProjection(type=SUM, innerExpression=( CASE WHEN CODE IN ( 30 ) THEN 1 ELSE 0 END ), alias=Optional[report_succ], derivedAggregationProjections=[], index=-1), AggregationProjection(type=SUM, innerExpression=( CASE WHEN CODE IN ( - 30 ) THEN 1 ELSE 0 END ), alias=Optional[report_fail], derivedAggregationProjections=[], index=-1), AggregationProjection(type=SUM, innerExpression=( CASE WHEN CODE IN ( - 11 ) THEN 1 ELSE 0 END ), alias=Optional[reject], derivedAggregationProjections=[], index=-1)]), groupByContext=org.apache.shardingsphere.sql.parser.binder.segment.select.groupby.GroupByContext@4926f6d6, orderByContext=org.apache.shardingsphere.sql.parser.binder.segment.select.orderby.OrderByContext@351a39e7, paginationContext=org.apache.shardingsphere.sql.parser.binder.segment.select.pagination.PaginationContext@78f28dbb, containsSubquery=false) 09:49:55.791 [main] INFO ShardingSphere-SQL - Actual SQL: master ::: SELECT date, customer_name name, customer_account account, customer_phone phone, count( 1 ) total, sum( CASE WHEN CODE IN ( 30,- 30 ) THEN 1 ELSE 0 END ) AS succ, sum( CASE WHEN CODE IN ( - 30,- 20 ) THEN 1 ELSE 0 END ) AS fail, sum( CASE WHEN CODE IN ( 20 ) THEN 1 ELSE 0 END ) AS excep, sum( CASE WHEN CODE IN ( 30 ) THEN 1 ELSE 0 END ) AS report_succ, sum( CASE WHEN CODE IN ( - 30 ) THEN 1 ELSE 0 END ) AS report_fail, sum( CASE WHEN CODE IN ( - 11 ) THEN 1 ELSE 0 END ) AS reject FROM ( SELECT customer_account,customer_phone,phone phone_s, customer_name, date_format( create_time, '%Y-%m-%d' ) AS date,max( CODE ) AS CODE FROM mms_order WHERE customer_account IS NOT NULL and create_time between ? and ? GROUP BY customer_account,send_id,customer_phone,phone,customer_name,date ) a GROUP BY date,customer_name,customer_account,customer_phone ::: [2020-09-18 00:00:00, 2020-09-18 23:59:59] This is full log and in the following text is shardingsphere-jdbc configuration.

spring: shardingsphere: datasource: names: master,record master: jdbc-url: 'jdbc:mysql://xxxxxx:3306/XXXXXXXX?allowMultiQueries=true&useUnicode=true&characterEncoding=UTF-8&useSSL=false&serverTimezone=Asia/Shanghai' username: xxxx password: xxxxx type: com.zaxxer.hikari.HikariDataSource driver-class-name: com.mysql.cj.jdbc.Driver hikari: minimum-idle: 10 maximum-pool-size: 25 auto-commit: true record: jdbc-url: 'jdbc:mysql://xxxxxx:3306/XXXXXXXX?allowMultiQueries=true&useUnicode=true&characterEncoding=UTF-8&useSSL=false&serverTimezone=Asia/Shanghai' username: xxxx password: xxxx type: com.zaxxer.hikari.HikariDataSource driver-class-name: com.mysql.cj.jdbc.Driver hikari: minimum-idle: 10 maximum-pool-size: 25 auto-commit: true sharding: default-data-source-name: master binding-tables: mms_order tables: mms_order: actual-data-nodes: record.mmsorder$->{0..10} key-generator: column: id type: SNOWFLAKE table-strategy: standard: precise-algorithm-class-name: com.demo.common.config.database.DateShardingTableAlgorithm range-algorithm-class-name: com.demo.common.config.database.TableShardingRangeAlgorithm sharding-column: create_time props: sql: show: true

jingshanglu commented 4 years ago

@tristaZero @jingshanglu Hi, I have met these subquery sqls not working very well. Could you help with this? Following are errors given by proxy when executing the example sql. Tested on master branch.

error-must have sharding column in subquery ,select count(0) from (select * from t_order where createdDate > '2020-02-01') a;

error-Can not find owner from table , select count(*) from (select * from t_order) a join (select * from t_order)b on a.id = b.id where a.id=10;

limit syntax in subquery, not rewrite as the log shows and will return 40 rows finally ------------ select id from (select * from t_order limit 10)a
Actual SQL: ds0 ::: select id from (select * from t_order limit 10)a
Actual SQL: ds1 ::: select id from (select * from t_order limit 10)a
Actual SQL: ds2 ::: select id from (select * from t_order limit 10)a
Actual SQL: ds3 ::: select id from (select * from t_order limit 10)a
config
t_order:
        actualDataNodes: ds${0..3}.t_order
        databaseStrategy:
          standard:
            shardingAlgorithmName: database_mod
            shardingColumn: id

@chana71 Can you show the correct rewrited sql for this sql?

jingshanglu commented 4 years ago

@chana71 You mean the table t_order not been rewrited to actual table?

yanhj93 commented 4 years ago

@chana71 You mean the table t_order not been rewrited to actual table?

@jingshanglu Actually the log just shows as below. Tested sql is the 3rd one - select id from (select * from t_order limit 10)a .

Actual SQL: ds0 ::: select id from (select * from t_order limit 10)a
Actual SQL: ds1 ::: select id from (select * from t_order limit 10)a
Actual SQL: ds2 ::: select id from (select * from t_order limit 10)a
Actual SQL: ds3 ::: select id from (select * from t_order limit 10)a

but the sql query result will return 40 rows and the rewrite result seems wrong cause I think it should rewrite to limit 40 each ds as doc says.

...将偏移量前的记录全部取出，并仅获取排序后的最后10条记录。

lwtdev commented 4 years ago

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

lwtdev commented 4 years ago

These test case information.

prepare sql

-- create databases
create database spsqltest_sharding_00;
create database spsqltest_sharding_01;

-- create logic tables

-- @title:createTableCustomer
CREATE TABLE `customer` (
  `id` bigint(20) NOT NULL COMMENT '主键ID',
  `party_id` bigint(20) NOT NULL COMMENT '用户ID',
   `status` smallint(6) DEFAULT NULL COMMENT '状态：1-有效，0-无效  待删除字段',
  PRIMARY KEY (`id`),
  KEY `party_id_index` (`party_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='客户表';

-- @title:createTableCustomerEmail
CREATE TABLE customer_email (
  id bigint(20) NOT NULL COMMENT '主键ID',
  party_id bigint(20) NOT NULL COMMENT '用户ID',
  `status` smallint(6) DEFAULT NULL COMMENT '状态：1-有效，0-无效  待删除字段',
  PRIMARY KEY (`id`),
  KEY `party_id_index` (`party_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='客户邮箱表';

 -- @title:createFullFieldTypeTable
create table IF NOT EXISTS full_table
(
    id bigint(15) not null auto_increment primary key comment 'primary key',
    name varchar(255)  default 'tom' COLLATE utf8_bin comment 'name',
    class_id bigint(15) references class(id) on delete cascade,
    age int,
    t_bl bool,
    t_ti tinyint(1),
    t_si smallint(2),
    t_mi middleint(10),
    t_it int(10),
    t_bi bigint(20),
    t_dec decimal(15,2),
    t_ft float(5),
    t_db double(10,2),
    t_dt date,
    t_te time,
    t_de datetime,
    t_ts timestamp,
    t_yr year,
    t_ch char(10),
    t_vh varchar(255),
    t_by binary(2),
    t_vb varbinary(25),
    t_tb tinyblob,
    t_mb mediumblob,
    t_bb blob,
    t_lb longblob,
    t_tt tinytext,
    t_mt mediumtext,
    t_tx text,
    t_lt longtext,
    t_em enum('a', 'b') character set utf8 collate utf8_bin,
    t_st set('a', 'b'),
    t_gy geometry,
    t_pt point,
    t_ls linestring,
    t_pn polygon,
    t_mp multipoint,
    t_ml multilinestring,
    t_mn multipolygon,
    t_gn geometrycollection,
    t_jn json,
    index indx_name_and_class using hash (class_id, name(20) desc) ,
    index idx_class_id using btree (class_id asc) ,
    constraint unique key (age),
    check (age > 0)
) engine InnoDB  CHARACTER SET utf8 COLLATE utf8_bin
auto_increment = 100 checksum 1
compression = 'none'
delay_key_write = 0
max_rows = 1000
min_rows = 1
pack_keys = 0
password = 'abc'
STATS_AUTO_RECALC  = 0
STATS_PERSISTENT  = 1
STATS_SAMPLE_PAGES  = 4
ROW_FORMAT=REDUNDANT
union (class);

sharding config


schemaName: spsqltest_sharding
#
dataSourceCommon:
username: root
password: root135
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 50
minPoolSize: 1
maintenanceIntervalMilliseconds: 30000
#
dataSources:
ds_00:
url: jdbc:mysql://127.0.0.1:3306/spsqltest_sharding_00?serverTimezone=UTC&useSSL=false
ds_01:
url: jdbc:mysql://127.0.0.1:3306/spsqltest_sharding_01?serverTimezone=UTC&useSSL=false
#
rules:

!SHARDING tables: customer: actualDataNodes: ds_00.customer_000${0..1},ds_01.customer_000${2..3} databaseStrategy: standard: shardingColumn: id shardingAlgorithmName: database_inline tableStrategy: standard: shardingColumn: id shardingAlgorithmName: customer_inline keyGenerateStrategy: column: id keyGeneratorName: snowflake customer_email: actualDataNodes: ds_00.customer_email_000${0..1},ds_01.customer_email_000${2..3} databaseStrategy: standard: shardingColumn: id shardingAlgorithmName: database_inline tableStrategy: standard: shardingColumn: id shardingAlgorithmName: customer_email_inline keyGenerateStrategy: column: id keyGeneratorName: snowflake full_table: actualDataNodes: ds_00.full_table_000${0..1},ds_01.full_table_000${2..3} databaseStrategy: standard: shardingColumn: id shardingAlgorithmName: database_inline tableStrategy: standard: shardingColumn: id shardingAlgorithmName: full_table_inline keyGenerateStrategy: column: id keyGeneratorName: snowflake bindingTables:
- customer,customer_email,full_table defaultDatabaseStrategy: none: defaultTableStrategy: none:
shardingAlgorithms: database_inline: type: INLINE props: algorithm-expression: ds_0${(id % 4 ).intdiv(2)} customer_inline: type: INLINE props: algorithm-expression: customer_000${id % 4} customer_email_inline: type: INLINE props: algorithm-expression: customer_email_000${id % 4} full_table_inline: type: INLINE props: algorithm-expression: full_table_000${id % 4}
subquery test case sql

-- @title:subqueryAsScalarOperand,hasShardingKey:none SELECT (SELECT name FROM customer);

-- @title:subqueryAsScalarOperand,hasShardingKey:all SELECT (SELECT name FROM customer where id = 3);

-- @title:subqueryInColumns,hasShardingKey:none SELECT id AS c_id , (select status FROM customer_email ce WHERE ce.party_id = c.party_id) AS ec_status FROM customer c;

-- @title:subqueryInColumns,hasShardingKey:single 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;

-- @title:subqueryInColumns,hasShardingKey:all 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;

-- @title:subqueryInComparisons,hasShardingKey:none SELECT * FROM customer WHERE status < (SELECT MAX(status) FROM customer_email);

-- @title:subqueryInComparisons,hasShardingKey:single SELECT * FROM customer WHERE status < (SELECT MAX(status) FROM customer_email) and id = 3;

-- @title:subqueryInComparisons,hasShardingKey:all SELECT * FROM customer WHERE status < (SELECT MAX(status) FROM customer_email where id = 3) and id = 3;

-- @title:subqueryWithAny,hasShardingKey:none SELECT * FROM customer WHERE status = ANY (SELECT status FROM customer_email);

-- @title:subqueryWithAny,hasShardingKey:single SELECT * FROM customer WHERE status = ANY (SELECT status FROM customer_email) and id = 3;

-- @title:subqueryWithAny,hasShardingKey:all SELECT * FROM customer WHERE status = ANY (SELECT status FROM customer_email where id = 3) and id = 3;

-- @title:subqueryWithAll,hasShardingKey:none SELECT * FROM customer WHERE status > ALL (SELECT status FROM customer_email);

-- @title:subqueryWithAll,hasShardingKey:single SELECT * FROM customer WHERE status > ALL (SELECT status FROM customer_email) and id = 3;

-- @title:subqueryWithAll,hasShardingKey:all SELECT * FROM customer WHERE status > ALL (SELECT status FROM customer_email where id = 3) and id = 3;

-- @title:subqueryAsRowSubquery,hasShardingKey:none SELECT * FROM customer WHERE (party_id, status) = (SELECT party_id, status FROM customer_email WHERE party_id = 10);

-- @title:subqueryAsRowSubquery,hasShardingKey:single SELECT * FROM customer WHERE (party_id, status) = (SELECT party_id, status FROM customer_email WHERE party_id = 10) and id = 3;

-- @title:subqueryAsRowSubquery,hasShardingKey:all SELECT * FROM customer WHERE (party_id, status) = (SELECT party_id, status FROM customer_email WHERE party_id = 10 and id = 3) and id = 3;

-- @title:subqueryWithNotExist,hasShardingKey:none SELECT FROM customer where not exists (select from customer_email where c.status = status);

-- @title:subqueryWithNotExist,hasShardingKey:single SELECT FROM customer where not exists (select from customer_email where c.status = status) and id = 3;

-- @title:subqueryWithNotExist,hasShardingKey:all SELECT FROM customer where not exists (select from customer_email where c.status = status and id = 3) and id = 3;

-- @title:subqueryWithIn,hasShardingKey:none SELECT * FROM customer c WHERE status in (SELECT status FROM customer_email);

-- @title:subqueryWithIn,hasShardingKey:single SELECT * FROM customer c WHERE status in (SELECT status FROM customer_email) AND id = 3;

-- @title:subqueryWithIn,hasShardingKey:all SELECT * FROM customer c WHERE status in (SELECT status FROM customer_email WHERE id = 3 ) AND id = 3;

-- @title:subqueryAsCorrelatedSubquery,hasShardingKey:none SELECT * FROM customer WHERE status = ANY (SELECT status FROM customer_email WHERE customer.party_id = customer_email);

-- @title:subqueryAsCorrelatedSubquery,hasShardingKey:single SELECT * FROM customer WHERE status = ANY (SELECT status FROM customer_email WHERE customer.party_id = customer_email) and id = 3;

-- @title:subqueryAsCorrelatedSubquery,hasShardingKey:all 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;

-- @title:subqueryAsDerivedTableInFromClause,hasShardingKey:none SELECT sbf1, sbf2 FROM (SELECT party_id AS sbf1, status AS sbf2 FROM customer) AS sb WHERE sbf2 > 1;

-- @title:subqueryAsDerivedTableInFromClause,hasShardingKey:all SELECT sbf1, sbf2 FROM (SELECT party_id AS sbf1, status AS sbf2 FROM customer where id = 3) AS sb WHERE sbf2 > 1;

-- @title:subqueryAsDerivedTableWithCount,hasShardingKey:none SELECT COUNT() FROM (SELECT FROM customer) AS t;

-- @title:subqueryAsDerivedTableWithCount,hasShardingKey:all SELECT COUNT() FROM (SELECT FROM customer WHERE id = 3) AS t;

-- @title:subqueryInLeftJoin,hasShardingKey:none SELECT * FROM customer LEFT JOIN (SELECT party_id, status FROM customer_email WHERE is_deleted = 'N') email ON email.party_id = cusomter.party_id;

-- @title:subqueryInLeftJoin,hasShardingKey:single 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;

-- @title:subqueryInLeftJoin,hasShardingKey:all 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;

-- @title:subqueryInSubquery,hasShardingKey:none SELECT * FROM customer c WHERE status > (SELECT status FROM customer_email WHERE party_id > (SELECT id FROM full_table) LIMIT 1);

-- @title:subqueryInSubquery,hasShardingKey:single SELECT * FROM customer c WHERE status > (SELECT status FROM customer_email WHERE party_id > (SELECT id FROM full_table) LIMIT 1) AND id = 3;

-- @title:subqueryInSubquery,hasShardingKey:all 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;

tristaZero commented 4 years ago

Hi @lwtdev,

Very appreciated your detailed and valuable test result and scenario info! 👍

We will look through all the feedback you provided. Please leave us some time.

Best, Trista

jingshanglu commented 4 years ago

@lwtdev Now, I have fix some bug,but it is not support that query has no sharding column. what is you expect result for SELECT * FROM customer where not exists (select * from customer_email where c.status = status) and id = 3;? and can you test it again on master branch? @chana71 Now, select id from (select * from t_order limit 10)a the sql is not yet support, because it is broadcast to all table.

lwtdev commented 4 years ago

@jingshanglu Hi

@lwtdev Now, I have fix some bug,but it is not support that query has no sharding column.

There is a contradiction here， please have a look at these route result of subquery that has no sharding colunm:

subqueryAsDerivedTableInFromClause[hasShardingKey:none];     Support:true;  SQL: SELECT sbf1, sbf2 FROM (SELECT party_id AS sbf1, status AS sbf2 FROM customer) AS sb WHERE sbf2 > 1;
ds_00|SELECT sbf1, sbf2 FROM (SELECT party_id AS sbf1, status AS sbf2 FROM customer_0000) AS sb WHERE sbf2 > 1|
ds_00|SELECT sbf1, sbf2 FROM (SELECT party_id AS sbf1, status AS sbf2 FROM customer_0001) AS sb WHERE sbf2 > 1|
ds_01|SELECT sbf1, sbf2 FROM (SELECT party_id AS sbf1, status AS sbf2 FROM customer_0002) AS sb WHERE sbf2 > 1|
ds_01|SELECT sbf1, sbf2 FROM (SELECT party_id AS sbf1, status AS sbf2 FROM customer_0003) AS sb WHERE sbf2 > 1|

subqueryInComparisons[hasShardingKey:none];  Support:true;  SQL: SELECT * FROM customer WHERE status < (SELECT MAX(status) FROM customer_email);
ds_00|SELECT * FROM customer_0000 WHERE status < (SELECT MAX(status) FROM customer_email_0000)|
ds_00|SELECT * FROM customer_0001 WHERE status < (SELECT MAX(status) FROM customer_email_0001)|
ds_01|SELECT * FROM customer_0002 WHERE status < (SELECT MAX(status) FROM customer_email_0002)|
ds_01|SELECT * FROM customer_0003 WHERE status < (SELECT MAX(status) FROM customer_email_0003)|

what is you expect result for SELECT * FROM customer where not exists (select * from customer_email where c.status = status) and id = 3;?

Sorry, this sql is wrong , I missing a table alias. route result after fix this problem:

ShardingProxy(5.0.0.RC1)2DB2Table
subqueryWithNotExist[hasShardingKey:all];    Support:false; SQL: SELECT * FROM customer c where not exists (select * from customer_email where c.status = status and id = 3) and c.id = 3;
ds_00|SELECT * FROM customer_0000 c where not exists (select * from customer_email where c.status = status and id = 3) and c.id = 3|
ds_00|SELECT * FROM customer_0001 c where not exists (select * from customer_email where c.status = status and id = 3) and c.id = 3|
ds_01|SELECT * FROM customer_0002 c where not exists (select * from customer_email where c.status = status and id = 3) and c.id = 3|
ds_01|SELECT * FROM customer_0003 c where not exists (select * from customer_email where c.status = status and id = 3) and c.id = 3|

I think there are two problem in this scenario

Two table (cusomter and cusomter_email) both have sharding columns, but broadcast to all table.
Two table (cusomter and cusomter_email) are binding tables, so customer_email table name also should be replace with physical table name like customer_email_xxxx.

Base on it, My expect result is:

ds_01|SELECT * FROM customer_0003 c where not exists (select * from customer_email_0003 where c.status = status and id = 3) and c.id = 3|

and can you test it again on master branch?

I will run test cases later.

lwtdev commented 4 years ago

@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	N
subqueryWithNotExist	SELECT FROM customer c where not exists (select from customer_email where c.status = status) and c.id = 3;	SINGLE	N
subqueryWithNotExist	SELECT FROM customer c where not exists (select from customer_email where c.status = status and id = 3) and c.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	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	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

Unsuppot test case detail

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
subqueryWithNotExist[hasShardingKey:none];   Support:false; SQL: SELECT * FROM customer c where not exists (select * from customer_email where c.status = status);
ds_00|SELECT * FROM customer_0000 c where not exists (select * from customer_email where c.status = status)|
ds_00|SELECT * FROM customer_0001 c where not exists (select * from customer_email where c.status = status)|
ds_01|SELECT * FROM customer_0002 c where not exists (select * from customer_email where c.status = status)|
ds_01|SELECT * FROM customer_0003 c 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 c where not exists (select * from customer_email where c.status = status) and c.id = 3;
ds_00|SELECT * FROM customer_0000 c where not exists (select * from customer_email where c.status = status) and c.id = 3|
ds_00|SELECT * FROM customer_0001 c where not exists (select * from customer_email where c.status = status) and c.id = 3|
ds_01|SELECT * FROM customer_0002 c where not exists (select * from customer_email where c.status = status) and c.id = 3|
ds_01|SELECT * FROM customer_0003 c where not exists (select * from customer_email where c.status = status) and c.id = 3|

ShardingProxy(5.0.0.RC1)2DB2Table
subqueryWithNotExist[hasShardingKey:all];    Support:false; SQL: SELECT * FROM customer c where not exists (select * from customer_email where c.status = status and id = 3) and c.id = 3;
ds_00|SELECT * FROM customer_0000 c where not exists (select * from customer_email where c.status = status and id = 3) and c.id = 3|
ds_00|SELECT * FROM customer_0001 c where not exists (select * from customer_email where c.status = status and id = 3) and c.id = 3|
ds_01|SELECT * FROM customer_0002 c where not exists (select * from customer_email where c.status = status and id = 3) and c.id = 3|
ds_01|SELECT * FROM customer_0003 c where not exists (select * from customer_email where c.status = status and id = 3) and c.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);
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;
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.]

jingshanglu commented 4 years ago

@lwtdev Ok, thanks, i'll track these unsupported cases.

jingshanglu commented 4 years ago

@lwtdev Hi, now all of them are supported except for those that don't have a sharding key, can you provide a test report like above?

lwtdev commented 4 years ago

@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

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

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

jingshanglu commented 4 years ago

@lwtdev is_deleted is not a sharding column,so, the exception will be reported.

lwtdev commented 4 years ago

@lwtdev is_deleted is not a sharding column,so, the exception will be reported.

but customer and customer_email are binding tables;

jingshanglu commented 4 years ago

@lwtdev Now, customer.id=3 in On Expression is not yet supported, you can move it to whereClause, the future will support condition in table join condition.

lwtdev commented 4 years ago

@lwtdev Now, customer.id=3 in On Expression is not yet supported, you can move it to whereClause, the future will support condition in table join condition.

@jingshanglu It's works well after I move customer.id=3 to whereClause.

ShardingProxy(5.0.0.RC1)2DB2Table
subqueryInLeftJoin[hasShardingKey:single];   Support:true;  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 where customer.id = 3;
ds_01|SELECT * FROM customer_0003 LEFT JOIN (SELECT party_id, status FROM customer_email_0003 WHERE is_deleted = 'N') email ON email.party_id = cusomter.party_id where customer_0003.id = 3|

kimmking commented 4 years ago

Sounds great. It's a big deal and can be improving an amazing step forward for sql support.

coderJL commented 4 years ago

Look forward to the release of this feature

lwtdev commented 4 years ago

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

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

tristaZero commented 4 years ago

@lwtdev Thanks for the multiple test reports! Hi @jingshanglu @strongduanmu Does every one of you think that's in our expectation? Plus, thanks for your effort to narrow down the unsupported SQL list.