shardingsphere 🚀 - Can subqueries be supported in select？

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

tristaZero on 29 Jul 2020

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

jingshanglu on 29 Jul 2020

👍1

@jingshanglu Waiting for your fixing. :)

tristaZero on 29 Jul 2020

lwtdev on 29 Jul 2020

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

jingshanglu on 30 Jul 2020

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

tristaZero on 30 Jul 2020

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?

lwtdev on 30 Jul 2020

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

tristaZero on 30 Jul 2020

@tristaZero OK

lwtdev on 31 Jul 2020

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!

hnxphnxp on 28 Aug 2020

👍1

@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

tristaZero on 28 Aug 2020

@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 on 15 Sep 2020

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

tristaZero on 16 Sep 2020

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

Fujinzhong on 18 Sep 2020

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

tristaZero on 18 Sep 2020

@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.mms_order_$->{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

Fujinzhong on 18 Sep 2020

@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 on 18 Sep 2020

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

jingshanglu on 18 Sep 2020

@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条记录。

chana71 on 18 Sep 2020

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 on 22 Sep 2020

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;

lwtdev on 22 Sep 2020

👍2

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

tristaZero on 22 Sep 2020

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

jingshanglu on 24 Sep 2020

👍2

@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 on 25 Sep 2020

@jingshanglu Hi

The latest test results are here :

Summary results

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

lwtdev on 25 Sep 2020

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

jingshanglu on 27 Sep 2020

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

jingshanglu on 30 Sep 2020

@jingshanglu Hi
The latest test results are here :

Summary results

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

lwtdev on 30 Sep 2020

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

jingshanglu on 9 Oct 2020

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

but customer and customer_email are binding tables;

lwtdev on 9 Oct 2020

@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 on 10 Oct 2020

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

lwtdev on 10 Oct 2020

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

kimmking on 12 Oct 2020

Look forward to the release of this feature

coderJL on 12 Oct 2020

The latest test results with 5.0.0-beta :

Results Summary

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

lwtdev on 7 Nov 2020

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

tristaZero on 9 Nov 2020

Shardingsphere: Can subqueries be supported in select？

Most helpful comment

All 36 comments

Related issues