You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by GitBox <gi...@apache.org> on 2020/07/29 07:53:54 UTC
[GitHub] [shardingsphere] awnurrk opened a new issue #6497: Can subqueries be supported in select？
awnurrk opened a new issue #6497:
URL: https://github.com/apache/shardingsphere/issues/6497
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
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] tristaZero commented on issue #6497: Can subqueries be supported in select？
Posted by GitBox <gi...@apache.org>.
tristaZero commented on issue #6497:
URL: https://github.com/apache/shardingsphere/issues/6497#issuecomment-665558535
@jingshanglu Waiting for your fixing. :)
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] chana71 commented on issue #6497: Can subqueries be supported in select？
Posted by GitBox <gi...@apache.org>.
chana71 commented on issue #6497:
URL: https://github.com/apache/shardingsphere/issues/6497#issuecomment-692548058
@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.
1. error-must have sharding column in subquery ,`select count(0) from (select * from t_order where createdDate > '2020-02-01') a;`
2. 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;`
3. 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
```
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] tristaZero commented on issue #6497: Can subqueries be supported in select？
Posted by GitBox <gi...@apache.org>.
tristaZero commented on issue #6497:
URL: https://github.com/apache/shardingsphere/issues/6497#issuecomment-694620323
@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?
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] lwtdev commented on issue #6497: Can subqueries be supported in select？
Posted by GitBox <gi...@apache.org>.
lwtdev commented on issue #6497:
URL: https://github.com/apache/shardingsphere/issues/6497#issuecomment-698708296
@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
1. Two table (`cusomter` and `cusomter_email`) both have sharding columns, but broadcast to all table.
2. 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:
```sql
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.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] jingshanglu commented on issue #6497: Can subqueries be supported in select？
Posted by GitBox <gi...@apache.org>.
jingshanglu commented on issue #6497:
URL: https://github.com/apache/shardingsphere/issues/6497#issuecomment-699590973
@lwtdev Ok, thanks, i'll track these unsupported cases.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] tristaZero edited a comment on issue #6497: Can subqueries be supported in select？
Posted by GitBox <gi...@apache.org>.
tristaZero edited a comment on issue #6497:
URL: https://github.com/apache/shardingsphere/issues/6497#issuecomment-666304948
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, we will consider seeking help from other contributors.
Thanks,
Trista
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] lwtdev commented on issue #6497: Can subqueries be supported in select？
Posted by GitBox <gi...@apache.org>.
lwtdev commented on issue #6497:
URL: https://github.com/apache/shardingsphere/issues/6497#issuecomment-666880298
@tristaZero OK
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] tristaZero commented on issue #6497: Can subqueries be supported in select？
Posted by GitBox <gi...@apache.org>.
tristaZero commented on issue #6497:
URL: https://github.com/apache/shardingsphere/issues/6497#issuecomment-665393007
Hi 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
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] jingshanglu commented on issue #6497: Can subqueries be supported in select？
Posted by GitBox <gi...@apache.org>.
jingshanglu commented on issue #6497:
URL: https://github.com/apache/shardingsphere/issues/6497#issuecomment-694741539
@chana71 You mean the table `t_order` not been rewrited to actual table?
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] tristaZero edited a comment on issue #6497: Can subqueries be supported in select？
Posted by GitBox <gi...@apache.org>.
tristaZero edited a comment on issue #6497:
URL: https://github.com/apache/shardingsphere/issues/6497#issuecomment-665393007
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
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] chana71 commented on issue #6497: Can subqueries be supported in select？
Posted by GitBox <gi...@apache.org>.
chana71 commented on issue #6497:
URL: https://github.com/apache/shardingsphere/issues/6497#issuecomment-694774625
> @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条记录。
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] tristaZero commented on issue #6497: Can subqueries be supported in select？
Posted by GitBox <gi...@apache.org>.
tristaZero commented on issue #6497:
URL: https://github.com/apache/shardingsphere/issues/6497#issuecomment-693156031
@chana71 Thanks for your feedback.
@jingshanglu Please give them a check, thx.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] jingshanglu commented on issue #6497: Can subqueries be supported in select？
Posted by GitBox <gi...@apache.org>.
jingshanglu commented on issue #6497:
URL: https://github.com/apache/shardingsphere/issues/6497#issuecomment-705928545
@lwtdev `is_deleted` is not a sharding column,so, the exception will be reported.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] tristaZero closed issue #6497: Can subqueries be supported in select？
Posted by GitBox <gi...@apache.org>.
tristaZero closed issue #6497:
URL: https://github.com/apache/shardingsphere/issues/6497
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] jingshanglu commented on issue #6497: Can subqueries be supported in select？
Posted by GitBox <gi...@apache.org>.
jingshanglu commented on issue #6497:
URL: https://github.com/apache/shardingsphere/issues/6497#issuecomment-706465861
@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`.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] lwtdev edited a comment on issue #6497: Can subqueries be supported in select？
Posted by GitBox <gi...@apache.org>.
lwtdev edited a comment on issue #6497:
URL: https://github.com/apache/shardingsphere/issues/6497#issuecomment-666271681
> 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?
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] lwtdev commented on issue #6497: Can subqueries be supported in select？
Posted by GitBox <gi...@apache.org>.
lwtdev commented on issue #6497:
URL: https://github.com/apache/shardingsphere/issues/6497#issuecomment-696593223
These test case information.
- prepare sql
```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',
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',
PRIMARY KEY (`id`),
KEY `party_id_index` (`party_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='客户邮箱表';
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
```yaml
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
```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;
```
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] lwtdev edited a comment on issue #6497: Can subqueries be supported in select？
Posted by GitBox <gi...@apache.org>.
lwtdev edited a comment on issue #6497:
URL: https://github.com/apache/shardingsphere/issues/6497#issuecomment-666271681
> 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 you replay， 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?
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] lwtdev commented on issue #6497: Can subqueries be supported in select？
Posted by GitBox <gi...@apache.org>.
lwtdev commented on issue #6497:
URL: https://github.com/apache/shardingsphere/issues/6497#issuecomment-696584030
Hi~ @jingshanglu @tristaZero
I have test some subquery case just now
|SQL Demo Title |SQL Demo |Sharding Column |ShardingProxy(5.0.0.RC1)2DB2Table |
|---- |---- |---- |---- |
|subqueryAsScalarOperand |SELECT (SELECT name FROM customer); |NONE |N |
|subqueryAsScalarOperand |SELECT (SELECT name FROM customer where id = 3); |ALL |Y |
|subqueryInColumns |SELECT id AS c_id , (select status FROM customer_email ce WHERE ce.party_id = c.party_id) AS ec_status FROM customer c; |NONE |N |
|subqueryInColumns |SELECT id AS c_id , (select status FROM customer_email ce WHERE ce.party_id = c.party_id) AS ec_status FROM customer c WHERE id = 3; |SINGLE |Y |
|subqueryInColumns |SELECT id AS c_id , (select status FROM customer_email ce WHERE ce.party_id = c.party_id and id = 3) AS ec_status FROM customer c WHERE id = 3; |ALL |Y |
|subqueryInComparisons |SELECT * FROM customer WHERE status < (SELECT MAX(status) FROM customer_email); |NONE |Y |
|subqueryInComparisons |SELECT * FROM customer WHERE status < (SELECT MAX(status) FROM customer_email) and id = 3; |SINGLE |Y |
|subqueryInComparisons |SELECT * FROM customer WHERE status < (SELECT MAX(status) FROM customer_email where id = 3) and id = 3; |ALL |Y |
|subqueryWithAny |SELECT * FROM customer WHERE status = ANY (SELECT status FROM customer_email); |NONE |N |
|subqueryWithAny |SELECT * FROM customer WHERE status = ANY (SELECT status FROM customer_email) and id = 3; |SINGLE |N |
|subqueryWithAny |SELECT * FROM customer WHERE status = ANY (SELECT status FROM customer_email where id = 3) and id = 3; |ALL |N |
|subqueryWithAll |SELECT * FROM customer WHERE status > ALL (SELECT status FROM customer_email); |NONE |N |
|subqueryWithAll |SELECT * FROM customer WHERE status > ALL (SELECT status FROM customer_email) and id = 3; |SINGLE |N |
|subqueryWithAll |SELECT * FROM customer WHERE status > ALL (SELECT status FROM customer_email where id = 3) and id = 3; |ALL |N |
|subqueryAsRowSubquery |SELECT * FROM customer WHERE (party_id, status) = (SELECT party_id, status FROM customer_email WHERE party_id = 10); |NONE |N |
|subqueryAsRowSubquery |SELECT * FROM customer WHERE (party_id, status) = (SELECT party_id, status FROM customer_email WHERE party_id = 10) and id = 3; |SINGLE |Y |
|subqueryAsRowSubquery |SELECT * FROM customer WHERE (party_id, status) = (SELECT party_id, status FROM customer_email WHERE party_id = 10 and id = 3) and id = 3; |ALL |Y |
|subqueryWithNotExist |SELECT * FROM customer where not exists (select * from customer_email where c.status = status); |NONE |N |
|subqueryWithNotExist |SELECT * FROM customer where not exists (select * from customer_email where c.status = status) and id = 3; |SINGLE |N |
|subqueryWithNotExist |SELECT * FROM customer where not exists (select * from customer_email where c.status = status and id = 3) and id = 3; |ALL |N |
|subqueryWithIn |SELECT * FROM customer c WHERE status in (SELECT status FROM customer_email); |NONE |Y |
|subqueryWithIn |SELECT * FROM customer c WHERE status in (SELECT status FROM customer_email) AND id = 3; |SINGLE |Y |
|subqueryWithIn |SELECT * FROM customer c WHERE status in (SELECT status FROM customer_email WHERE id = 3 ) AND id = 3; |ALL |Y |
|subqueryAsCorrelatedSubquery |SELECT * FROM customer WHERE status = ANY (SELECT status FROM customer_email WHERE customer.party_id = customer_email); |NONE |N |
|subqueryAsCorrelatedSubquery |SELECT * FROM customer WHERE status = ANY (SELECT status FROM customer_email WHERE customer.party_id = customer_email) and id = 3; |SINGLE |N |
|subqueryAsCorrelatedSubquery |SELECT * FROM customer WHERE status = ANY (SELECT status FROM customer_email WHERE customer.party_id = customer_email.party_id and id = 3) and id = 3; |ALL |N |
|subqueryAsDerivedTableInFromClause |SELECT sbf1, sbf2 FROM (SELECT party_id AS sbf1, status AS sbf2 FROM customer) AS sb WHERE sbf2 > 1; |NONE |Y |
|subqueryAsDerivedTableInFromClause |SELECT sbf1, sbf2 FROM (SELECT party_id AS sbf1, status AS sbf2 FROM customer where id = 3) AS sb WHERE sbf2 > 1; |ALL |Y |
|subqueryAsDerivedTableWithCount |SELECT COUNT(*) FROM (SELECT * FROM customer) AS t; |NONE |Y |
|subqueryAsDerivedTableWithCount |SELECT COUNT(*) FROM (SELECT * FROM customer WHERE id = 3) AS t; |ALL |Y |
|subqueryInLeftJoin |SELECT * FROM customer LEFT JOIN (SELECT party_id, status FROM customer_email WHERE is_deleted = 'N') email ON email.party_id = cusomter.party_id; |NONE |N |
|subqueryInLeftJoin |SELECT * FROM customer LEFT JOIN (SELECT party_id, status FROM customer_email WHERE is_deleted = 'N') email ON email.party_id = cusomter.party_id and customer.id = 3; |SINGLE |N |
|subqueryInLeftJoin |SELECT * FROM customer LEFT JOIN (SELECT party_id, status FROM customer_email WHERE is_deleted = 'N' and id = 3) email ON email.party_id = cusomter.party_id and customer.id = 3; |ALL |N |
|subqueryInSubquery |SELECT * FROM customer c WHERE status > (SELECT status FROM customer_email WHERE party_id > (SELECT id FROM full_table) LIMIT 1); |NONE |N |
|subqueryInSubquery |SELECT * FROM customer c WHERE status > (SELECT status FROM customer_email WHERE party_id > (SELECT id FROM full_table) LIMIT 1) AND id = 3; |SINGLE |Y |
|subqueryInSubquery |SELECT * FROM customer c WHERE status > (SELECT status FROM customer_email WHERE id = 3 AND party_id > (SELECT id FROM full_table WHERE id = 3) LIMIT 1) AND id = 3; |ALL |Y |
And not support sql detail as follow:
```
ShardingProxy(5.0.0.RC1)2DB2Table
subqueryAsScalarOperand[hasShardingKey:none]; Support:false; SQL: SELECT (SELECT name FROM customer);
java.sql.SQLException: 2Unknown exception: [String index out of range: 35]
ShardingProxy(5.0.0.RC1)2DB2Table
subqueryInColumns[hasShardingKey:none]; Support:false; SQL: SELECT id AS c_id , (select status FROM customer_email ce WHERE ce.party_id = c.party_id) AS ec_status FROM customer c;
java.sql.SQLException: 2Unknown exception: [Must have sharding column with subquery.]
ShardingProxy(5.0.0.RC1)2DB2Table
subqueryWithAny[hasShardingKey:none]; Support:false; SQL: SELECT * FROM customer WHERE status = ANY (SELECT status FROM customer_email);
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: This version of ShardingProxy doesn't yet support this SQL. 'Unsupported SQL of `SELECT * FROM customer WHERE status = ANY (SELECT status FROM customer_email)`'
ShardingProxy(5.0.0.RC1)2DB2Table
subqueryWithAny[hasShardingKey:single]; Support:false; SQL: SELECT * FROM customer WHERE status = ANY (SELECT status FROM customer_email) and id = 3;
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: This version of ShardingProxy doesn't yet support this SQL. 'Unsupported SQL of `SELECT * FROM customer WHERE status = ANY (SELECT status FROM customer_email) and id = 3`'
ShardingProxy(5.0.0.RC1)2DB2Table
subqueryWithAny[hasShardingKey:all]; Support:false; SQL: SELECT * FROM customer WHERE status = ANY (SELECT status FROM customer_email where id = 3) and id = 3;
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: This version of ShardingProxy doesn't yet support this SQL. 'Unsupported SQL of `SELECT * FROM customer WHERE status = ANY (SELECT status FROM customer_email where id = 3) and id = 3`'
ShardingProxy(5.0.0.RC1)2DB2Table
subqueryWithAll[hasShardingKey:none]; Support:false; SQL: SELECT * FROM customer WHERE status > ALL (SELECT status FROM customer_email);
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: This version of ShardingProxy doesn't yet support this SQL. 'Unsupported SQL of `SELECT * FROM customer WHERE status > ALL (SELECT status FROM customer_email)`'
ShardingProxy(5.0.0.RC1)2DB2Table
subqueryWithAll[hasShardingKey:single]; Support:false; SQL: SELECT * FROM customer WHERE status > ALL (SELECT status FROM customer_email) and id = 3;
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: This version of ShardingProxy doesn't yet support this SQL. 'Unsupported SQL of `SELECT * FROM customer WHERE status > ALL (SELECT status FROM customer_email) and id = 3`'
ShardingProxy(5.0.0.RC1)2DB2Table
subqueryWithAll[hasShardingKey:all]; Support:false; SQL: SELECT * FROM customer WHERE status > ALL (SELECT status FROM customer_email where id = 3) and id = 3;
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: This version of ShardingProxy doesn't yet support this SQL. 'Unsupported SQL of `SELECT * FROM customer WHERE status > ALL (SELECT status FROM customer_email where id = 3) and id = 3`'
ShardingProxy(5.0.0.RC1)2DB2Table
subqueryAsRowSubquery[hasShardingKey:none]; Support:false; SQL: SELECT * FROM customer WHERE (party_id, status) = (SELECT party_id, status FROM customer_email WHERE party_id = 10);
java.sql.SQLException: 2Unknown exception: [Must have sharding column with subquery.]
ShardingProxy(5.0.0.RC1)2DB2Table
subqueryWithNotExist[hasShardingKey:none]; Support:false; SQL: SELECT * FROM customer where not exists (select * from customer_email where c.status = status);
ds_00|SELECT * FROM customer_0000 where not exists (select * from customer_email where c.status = status)|
ds_00|SELECT * FROM customer_0001 where not exists (select * from customer_email where c.status = status)|
ds_01|SELECT * FROM customer_0002 where not exists (select * from customer_email where c.status = status)|
ds_01|SELECT * FROM customer_0003 where not exists (select * from customer_email where c.status = status)|
ShardingProxy(5.0.0.RC1)2DB2Table
subqueryWithNotExist[hasShardingKey:single]; Support:false; SQL: SELECT * FROM customer where not exists (select * from customer_email where c.status = status) and id = 3;
ds_00|SELECT * FROM customer_0000 where not exists (select * from customer_email where c.status = status) and id = 3|
ds_00|SELECT * FROM customer_0001 where not exists (select * from customer_email where c.status = status) and id = 3|
ds_01|SELECT * FROM customer_0002 where not exists (select * from customer_email where c.status = status) and id = 3|
ds_01|SELECT * FROM customer_0003 where not exists (select * from customer_email where c.status = status) and id = 3|
ShardingProxy(5.0.0.RC1)2DB2Table
subqueryWithNotExist[hasShardingKey:all]; Support:false; SQL: SELECT * FROM customer where not exists (select * from customer_email where c.status = status and id = 3) and id = 3;
ds_00|SELECT * FROM customer_0000 where not exists (select * from customer_email where c.status = status and id = 3) and id = 3|
ds_00|SELECT * FROM customer_0001 where not exists (select * from customer_email where c.status = status and id = 3) and id = 3|
ds_01|SELECT * FROM customer_0002 where not exists (select * from customer_email where c.status = status and id = 3) and id = 3|
ds_01|SELECT * FROM customer_0003 where not exists (select * from customer_email where c.status = status and id = 3) and id = 3|
ShardingProxy(5.0.0.RC1)2DB2Table
subqueryAsCorrelatedSubquery[hasShardingKey:none]; Support:false; SQL: SELECT * FROM customer WHERE status = ANY (SELECT status FROM customer_email WHERE customer.party_id = customer_email);
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: This version of ShardingProxy doesn't yet support this SQL. 'Unsupported SQL of `SELECT * FROM customer WHERE status = ANY (SELECT status FROM customer_email WHERE customer.party_id = customer_email)`'
ShardingProxy(5.0.0.RC1)2DB2Table
subqueryAsCorrelatedSubquery[hasShardingKey:single]; Support:false; SQL: SELECT * FROM customer WHERE status = ANY (SELECT status FROM customer_email WHERE customer.party_id = customer_email) and id = 3;
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: This version of ShardingProxy doesn't yet support this SQL. 'Unsupported SQL of `SELECT * FROM customer WHERE status = ANY (SELECT status FROM customer_email WHERE customer.party_id = customer_email) and id = 3`'
ShardingProxy(5.0.0.RC1)2DB2Table
subqueryAsCorrelatedSubquery[hasShardingKey:all]; Support:false; SQL: SELECT * FROM customer WHERE status = ANY (SELECT status FROM customer_email WHERE customer.party_id = customer_email.party_id and id = 3) and id = 3;
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: This version of ShardingProxy doesn't yet support this SQL. 'Unsupported SQL of `SELECT * FROM customer WHERE status = ANY (SELECT status FROM customer_email WHERE customer.party_id = customer_email.party_id and id = 3) and id = 3`'
ShardingProxy(5.0.0.RC1)2DB2Table
subqueryInLeftJoin[hasShardingKey:none]; Support:false; SQL: SELECT * FROM customer LEFT JOIN (SELECT party_id, status FROM customer_email WHERE is_deleted = 'N') email ON email.party_id = cusomter.party_id;
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: This version of ShardingProxy doesn't yet support this SQL. 'Unsupported SQL of `SELECT * FROM customer LEFT JOIN (SELECT party_id, status FROM customer_email WHERE is_deleted = 'N') email ON email.party_id = cusomter.party_id`'
ShardingProxy(5.0.0.RC1)2DB2Table
subqueryInLeftJoin[hasShardingKey:single]; Support:false; SQL: SELECT * FROM customer LEFT JOIN (SELECT party_id, status FROM customer_email WHERE is_deleted = 'N') email ON email.party_id = cusomter.party_id and customer.id = 3;
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: This version of ShardingProxy doesn't yet support this SQL. 'Unsupported SQL of `SELECT * FROM customer LEFT JOIN (SELECT party_id, status FROM customer_email WHERE is_deleted = 'N') email ON email.party_id = cusomter.party_id and customer.id = 3`'
ShardingProxy(5.0.0.RC1)2DB2Table
subqueryInLeftJoin[hasShardingKey:all]; Support:false; SQL: SELECT * FROM customer LEFT JOIN (SELECT party_id, status FROM customer_email WHERE is_deleted = 'N' and id = 3) email ON email.party_id = cusomter.party_id and customer.id = 3;
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: This version of ShardingProxy doesn't yet support this SQL. 'Unsupported SQL of `SELECT * FROM customer LEFT JOIN (SELECT party_id, status FROM customer_email WHERE is_deleted = 'N' and id = 3) email ON email.party_id = cusomter.party_id and customer.id = 3`'
ShardingProxy(5.0.0.RC1)2DB2Table
subqueryInSubquery[hasShardingKey:none]; Support:false; SQL: SELECT * FROM customer c WHERE status > (SELECT status FROM customer_email WHERE party_id > (SELECT id FROM full_table) LIMIT 1);
java.sql.SQLException: 2Unknown exception: [Must have sharding column with subquery.]
```
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] lwtdev commented on issue #6497: Can subqueries be supported in select？
Posted by GitBox <gi...@apache.org>.
lwtdev commented on issue #6497:
URL: https://github.com/apache/shardingsphere/issues/6497#issuecomment-701302611
@jingshanglu Hi
The latest test results are here :
- **Summary results**
|SQL Demo Title |SQL Demo |Sharding Column |ShardingProxy(5.0.0.RC1)2DB2Table |
|---- |---- |---- |---- |
|subqueryAsScalarOperand |SELECT (SELECT name FROM customer); |NONE |Y |
|subqueryAsScalarOperand |SELECT (SELECT name FROM customer where id = 3); |ALL |Y |
|subqueryInColumns |SELECT id AS c_id , (select status FROM customer_email ce WHERE ce.party_id = c.party_id) AS ec_status FROM customer c; |NONE |N |
|subqueryInColumns |SELECT id AS c_id , (select status FROM customer_email ce WHERE ce.party_id = c.party_id) AS ec_status FROM customer c WHERE id = 3; |SINGLE |Y |
|subqueryInColumns |SELECT id AS c_id , (select status FROM customer_email ce WHERE ce.party_id = c.party_id and id = 3) AS ec_status FROM customer c WHERE id = 3; |ALL |Y |
|subqueryInComparisons |SELECT * FROM customer WHERE status < (SELECT MAX(status) FROM customer_email); |NONE |Y |
|subqueryInComparisons |SELECT * FROM customer WHERE status < (SELECT MAX(status) FROM customer_email) and id = 3; |SINGLE |Y |
|subqueryInComparisons |SELECT * FROM customer WHERE status < (SELECT MAX(status) FROM customer_email where id = 3) and id = 3; |ALL |Y |
|subqueryWithAny |SELECT * FROM customer WHERE status = ANY (SELECT status FROM customer_email); |NONE |Y |
|subqueryWithAny |SELECT * FROM customer WHERE status = ANY (SELECT status FROM customer_email) and id = 3; |SINGLE |Y |
|subqueryWithAny |SELECT * FROM customer WHERE status = ANY (SELECT status FROM customer_email where id = 3) and id = 3; |ALL |Y |
|subqueryWithAll |SELECT * FROM customer WHERE status > ALL (SELECT status FROM customer_email); |NONE |Y |
|subqueryWithAll |SELECT * FROM customer WHERE status > ALL (SELECT status FROM customer_email) and id = 3; |SINGLE |Y |
|subqueryWithAll |SELECT * FROM customer WHERE status > ALL (SELECT status FROM customer_email where id = 3) and id = 3; |ALL |Y |
|subqueryAsRowSubquery |SELECT * FROM customer WHERE (party_id, status) = (SELECT party_id, status FROM customer_email WHERE party_id = 10); |NONE |N |
|subqueryAsRowSubquery |SELECT * FROM customer WHERE (party_id, status) = (SELECT party_id, status FROM customer_email WHERE party_id = 10) and id = 3; |SINGLE |Y |
|subqueryAsRowSubquery |SELECT * FROM customer WHERE (party_id, status) = (SELECT party_id, status FROM customer_email WHERE party_id = 10 and id = 3) and id = 3; |ALL |Y |
|subqueryWithNotExist |SELECT * FROM customer c where not exists (select * from customer_email where c.status = status); |NONE |Y |
|subqueryWithNotExist |SELECT * FROM customer c where not exists (select * from customer_email where c.status = status) and c.id = 3; |SINGLE |Y |
|subqueryWithNotExist |SELECT * FROM customer c where not exists (select * from customer_email where c.status = status and id = 3) and c.id = 3; |ALL |Y |
|subqueryWithIn |SELECT * FROM customer c WHERE status in (SELECT status FROM customer_email); |NONE |Y |
|subqueryWithIn |SELECT * FROM customer c WHERE status in (SELECT status FROM customer_email) AND id = 3; |SINGLE |Y |
|subqueryWithIn |SELECT * FROM customer c WHERE status in (SELECT status FROM customer_email WHERE id = 3 ) AND id = 3; |ALL |Y |
|subqueryAsCorrelatedSubquery |SELECT * FROM customer WHERE status = ANY (SELECT status FROM customer_email WHERE customer.party_id = customer_email); |NONE |N |
|subqueryAsCorrelatedSubquery |SELECT * FROM customer WHERE status = ANY (SELECT status FROM customer_email WHERE customer.party_id = customer_email) and id = 3; |SINGLE |Y |
|subqueryAsCorrelatedSubquery |SELECT * FROM customer WHERE status = ANY (SELECT status FROM customer_email WHERE customer.party_id = customer_email.party_id and id = 3) and id = 3; |ALL |Y |
|subqueryAsDerivedTableInFromClause |SELECT sbf1, sbf2 FROM (SELECT party_id AS sbf1, status AS sbf2 FROM customer) AS sb WHERE sbf2 > 1; |NONE |Y |
|subqueryAsDerivedTableInFromClause |SELECT sbf1, sbf2 FROM (SELECT party_id AS sbf1, status AS sbf2 FROM customer where id = 3) AS sb WHERE sbf2 > 1; |ALL |Y |
|subqueryAsDerivedTableWithCount |SELECT COUNT(*) FROM (SELECT * FROM customer) AS t; |NONE |Y |
|subqueryAsDerivedTableWithCount |SELECT COUNT(*) FROM (SELECT * FROM customer WHERE id = 3) AS t; |ALL |Y |
|subqueryInLeftJoin |SELECT * FROM customer LEFT JOIN (SELECT party_id, status FROM customer_email WHERE is_deleted = 'N') email ON email.party_id = cusomter.party_id; |NONE |N |
|subqueryInLeftJoin |SELECT * FROM customer LEFT JOIN (SELECT party_id, status FROM customer_email WHERE is_deleted = 'N') email ON email.party_id = cusomter.party_id and customer.id = 3; |SINGLE |N |
|subqueryInLeftJoin |SELECT * FROM customer LEFT JOIN (SELECT party_id, status FROM customer_email WHERE is_deleted = 'N' and id = 3) email ON email.party_id = cusomter.party_id and customer.id = 3; |ALL |Y |
|subqueryInSubquery |SELECT * FROM customer c WHERE status > (SELECT status FROM customer_email WHERE party_id > (SELECT id FROM full_table) LIMIT 1); |NONE |N |
|subqueryInSubquery |SELECT * FROM customer c WHERE status > (SELECT status FROM customer_email WHERE party_id > (SELECT id FROM full_table) LIMIT 1) AND id = 3; |SINGLE |Y |
|subqueryInSubquery |SELECT * FROM customer c WHERE status > (SELECT status FROM customer_email WHERE id = 3 AND party_id > (SELECT id FROM full_table WHERE id = 3) LIMIT 1) AND id = 3; |ALL |Y |
- **Unsuppot test case detail**
```sql
ShardingProxy(5.0.0.RC1)2DB2Table
subqueryInColumns[hasShardingKey:none]; Support:false; SQL: SELECT id AS c_id , (select status FROM customer_email ce WHERE ce.party_id = c.party_id) AS ec_status FROM customer c;
java.sql.SQLException: 2Unknown exception: [Must have sharding column with subquery.]
ShardingProxy(5.0.0.RC1)2DB2Table
subqueryAsRowSubquery[hasShardingKey:none]; Support:false; SQL: SELECT * FROM customer WHERE (party_id, status) = (SELECT party_id, status FROM customer_email WHERE party_id = 10);
java.sql.SQLException: 2Unknown exception: [Must have sharding column with subquery.]
ShardingProxy(5.0.0.RC1)2DB2Table
subqueryAsCorrelatedSubquery[hasShardingKey:none]; Support:false; SQL: SELECT * FROM customer WHERE status = ANY (SELECT status FROM customer_email WHERE customer.party_id = customer_email);
java.sql.SQLException: 2Unknown exception: [Must have sharding column with subquery.]
ShardingProxy(5.0.0.RC1)2DB2Table
subqueryInLeftJoin[hasShardingKey:none]; Support:false; SQL: SELECT * FROM customer LEFT JOIN (SELECT party_id, status FROM customer_email WHERE is_deleted = 'N') email ON email.party_id = cusomter.party_id;
java.sql.SQLException: 2Unknown exception: [Must have sharding column with subquery.]
ShardingProxy(5.0.0.RC1)2DB2Table
subqueryInLeftJoin[hasShardingKey:single]; Support:false; SQL: SELECT * FROM customer LEFT JOIN (SELECT party_id, status FROM customer_email WHERE is_deleted = 'N') email ON email.party_id = cusomter.party_id and customer.id = 3;
java.sql.SQLException: 2Unknown exception: [Must have sharding column with subquery.]
ShardingProxy(5.0.0.RC1)2DB2Table
subqueryInSubquery[hasShardingKey:none]; Support:false; SQL: SELECT * FROM customer c WHERE status > (SELECT status FROM customer_email WHERE party_id > (SELECT id FROM full_table) LIMIT 1);
java.sql.SQLException: 2Unknown exception: [Must have sharding column with subquery.]
```
You may have a look at this case (`customer` has sharding key, `customer` and `customer_email` are binding tables) :
```sql
ShardingProxy(5.0.0.RC1)2DB2Table
subqueryInLeftJoin[hasShardingKey:single]; Support:false; SQL: SELECT * FROM customer LEFT JOIN (SELECT party_id, status FROM customer_email WHERE is_deleted = 'N') email ON email.party_id = cusomter.party_id and customer.id = 3;
java.sql.SQLException: 2Unknown exception: [Must have sharding column with subquery.]
```
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] Fujinzhong edited a comment on issue #6497: Can subqueries be supported in select？
Posted by GitBox <gi...@apache.org>.
Fujinzhong edited a comment on issue #6497:
URL: https://github.com/apache/shardingsphere/issues/6497#issuecomment-694634942
> @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 ) T
HEN 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.segmen
t.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
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] lwtdev commented on issue #6497: Can subqueries be supported in select？
Posted by GitBox <gi...@apache.org>.
lwtdev commented on issue #6497:
URL: https://github.com/apache/shardingsphere/issues/6497#issuecomment-705931943
> @lwtdev `is_deleted` is not a sharding column,so, the exception will be reported.
but `customer` and `customer_email` are binding tables;
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] Fujinzhong commented on issue #6497: Can subqueries be supported in select？
Posted by GitBox <gi...@apache.org>.
Fujinzhong commented on issue #6497:
URL: https://github.com/apache/shardingsphere/issues/6497#issuecomment-694610519
Hi,all, This sharding-jdbc-4.1.1`s log
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
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].
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
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] hnxphnxp commented on issue #6497: Can subqueries be supported in select？
Posted by GitBox <gi...@apache.org>.
hnxphnxp commented on issue #6497:
URL: https://github.com/apache/shardingsphere/issues/6497#issuecomment-682296621
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!
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] lwtdev commented on issue #6497: Can subqueries be supported in select？
Posted by GitBox <gi...@apache.org>.
lwtdev commented on issue #6497:
URL: https://github.com/apache/shardingsphere/issues/6497#issuecomment-698838541
@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.]
```
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] lwtdev commented on issue #6497: Can subqueries be supported in select？
Posted by GitBox <gi...@apache.org>.
lwtdev commented on issue #6497:
URL: https://github.com/apache/shardingsphere/issues/6497#issuecomment-696590455
Hi~ @jingshanglu @tristaZero
I Just run some subquery cases with newest version. Test result as fellow:
|SQL Demo Title |SQL Demo |Sharding Column |ShardingProxy(5.0.0.RC1)2DB2Table |
|---- |---- |---- |---- |
|subqueryAsScalarOperand |SELECT (SELECT name FROM customer); |NONE |N |
|subqueryAsScalarOperand |SELECT (SELECT name FROM customer where id = 3); |ALL |Y |
|subqueryInColumns |SELECT id AS c_id , (select status FROM customer_email ce WHERE ce.party_id = c.party_id) AS ec_status FROM customer c; |NONE |N |
|subqueryInColumns |SELECT id AS c_id , (select status FROM customer_email ce WHERE ce.party_id = c.party_id) AS ec_status FROM customer c WHERE id = 3; |SINGLE |Y |
|subqueryInColumns |SELECT id AS c_id , (select status FROM customer_email ce WHERE ce.party_id = c.party_id and id = 3) AS ec_status FROM customer c WHERE id = 3; |ALL |Y |
|subqueryInComparisons |SELECT * FROM customer WHERE status < (SELECT MAX(status) FROM customer_email); |NONE |Y |
|subqueryInComparisons |SELECT * FROM customer WHERE status < (SELECT MAX(status) FROM customer_email) and id = 3; |SINGLE |Y |
|subqueryInComparisons |SELECT * FROM customer WHERE status < (SELECT MAX(status) FROM customer_email where id = 3) and id = 3; |ALL |Y |
|subqueryWithAny |SELECT * FROM customer WHERE status = ANY (SELECT status FROM customer_email); |NONE |N |
|subqueryWithAny |SELECT * FROM customer WHERE status = ANY (SELECT status FROM customer_email) and id = 3; |SINGLE |N |
|subqueryWithAny |SELECT * FROM customer WHERE status = ANY (SELECT status FROM customer_email where id = 3) and id = 3; |ALL |N |
|subqueryWithAll |SELECT * FROM customer WHERE status > ALL (SELECT status FROM customer_email); |NONE |N |
|subqueryWithAll |SELECT * FROM customer WHERE status > ALL (SELECT status FROM customer_email) and id = 3; |SINGLE |N |
|subqueryWithAll |SELECT * FROM customer WHERE status > ALL (SELECT status FROM customer_email where id = 3) and id = 3; |ALL |N |
|subqueryAsRowSubquery |SELECT * FROM customer WHERE (party_id, status) = (SELECT party_id, status FROM customer_email WHERE party_id = 10); |NONE |N |
|subqueryAsRowSubquery |SELECT * FROM customer WHERE (party_id, status) = (SELECT party_id, status FROM customer_email WHERE party_id = 10) and id = 3; |SINGLE |Y |
|subqueryAsRowSubquery |SELECT * FROM customer WHERE (party_id, status) = (SELECT party_id, status FROM customer_email WHERE party_id = 10 and id = 3) and id = 3; |ALL |Y |
|subqueryWithNotExist |SELECT * FROM customer where not exists (select * from customer_email where c.status = status); |NONE |N |
|subqueryWithNotExist |SELECT * FROM customer where not exists (select * from customer_email where c.status = status) and id = 3; |SINGLE |N |
|subqueryWithNotExist |SELECT * FROM customer where not exists (select * from customer_email where c.status = status and id = 3) and id = 3; |ALL |N |
|subqueryWithIn |SELECT * FROM customer c WHERE status in (SELECT status FROM customer_email); |NONE |Y |
|subqueryWithIn |SELECT * FROM customer c WHERE status in (SELECT status FROM customer_email) AND id = 3; |SINGLE |Y |
|subqueryWithIn |SELECT * FROM customer c WHERE status in (SELECT status FROM customer_email WHERE id = 3 ) AND id = 3; |ALL |Y |
|subqueryAsCorrelatedSubquery |SELECT * FROM customer WHERE status = ANY (SELECT status FROM customer_email WHERE customer.party_id = customer_email); |NONE |N |
|subqueryAsCorrelatedSubquery |SELECT * FROM customer WHERE status = ANY (SELECT status FROM customer_email WHERE customer.party_id = customer_email) and id = 3; |SINGLE |N |
|subqueryAsCorrelatedSubquery |SELECT * FROM customer WHERE status = ANY (SELECT status FROM customer_email WHERE customer.party_id = customer_email.party_id and id = 3) and id = 3; |ALL |N |
|subqueryAsDerivedTableInFromClause |SELECT sbf1, sbf2 FROM (SELECT party_id AS sbf1, status AS sbf2 FROM customer) AS sb WHERE sbf2 > 1; |NONE |Y |
|subqueryAsDerivedTableInFromClause |SELECT sbf1, sbf2 FROM (SELECT party_id AS sbf1, status AS sbf2 FROM customer where id = 3) AS sb WHERE sbf2 > 1; |ALL |Y |
|subqueryAsDerivedTableWithCount |SELECT COUNT(*) FROM (SELECT * FROM customer) AS t; |NONE |Y |
|subqueryAsDerivedTableWithCount |SELECT COUNT(*) FROM (SELECT * FROM customer WHERE id = 3) AS t; |ALL |Y |
|subqueryInLeftJoin |SELECT * FROM customer LEFT JOIN (SELECT party_id, status FROM customer_email WHERE is_deleted = 'N') email ON email.party_id = cusomter.party_id; |NONE |N |
|subqueryInLeftJoin |SELECT * FROM customer LEFT JOIN (SELECT party_id, status FROM customer_email WHERE is_deleted = 'N') email ON email.party_id = cusomter.party_id and customer.id = 3; |SINGLE |N |
|subqueryInLeftJoin |SELECT * FROM customer LEFT JOIN (SELECT party_id, status FROM customer_email WHERE is_deleted = 'N' and id = 3) email ON email.party_id = cusomter.party_id and customer.id = 3; |ALL |N |
|subqueryInSubquery |SELECT * FROM customer c WHERE status > (SELECT status FROM customer_email WHERE party_id > (SELECT id FROM full_table) LIMIT 1); |NONE |N |
|subqueryInSubquery |SELECT * FROM customer c WHERE status > (SELECT status FROM customer_email WHERE party_id > (SELECT id FROM full_table) LIMIT 1) AND id = 3; |SINGLE |Y |
|subqueryInSubquery |SELECT * FROM customer c WHERE status > (SELECT status FROM customer_email WHERE id = 3 AND party_id > (SELECT id FROM full_table WHERE id = 3) LIMIT 1) AND id = 3; |ALL |Y |
- UnSupport Case Detail
```
ShardingProxy(5.0.0.RC1)2DB2Table
subqueryAsScalarOperand[hasShardingKey:none]; Support:false; SQL: SELECT (SELECT name FROM customer);
java.sql.SQLException: 2Unknown exception: [String index out of range: 35]
ShardingProxy(5.0.0.RC1)2DB2Table
subqueryInColumns[hasShardingKey:none]; Support:false; SQL: SELECT id AS c_id , (select status FROM customer_email ce WHERE ce.party_id = c.party_id) AS ec_status FROM customer c;
java.sql.SQLException: 2Unknown exception: [Must have sharding column with subquery.]
ShardingProxy(5.0.0.RC1)2DB2Table
subqueryWithAny[hasShardingKey:none]; Support:false; SQL: SELECT * FROM customer WHERE status = ANY (SELECT status FROM customer_email);
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: This version of ShardingProxy doesn't yet support this SQL. 'Unsupported SQL of `SELECT * FROM customer WHERE status = ANY (SELECT status FROM customer_email)`'
ShardingProxy(5.0.0.RC1)2DB2Table
subqueryWithAny[hasShardingKey:single]; Support:false; SQL: SELECT * FROM customer WHERE status = ANY (SELECT status FROM customer_email) and id = 3;
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: This version of ShardingProxy doesn't yet support this SQL. 'Unsupported SQL of `SELECT * FROM customer WHERE status = ANY (SELECT status FROM customer_email) and id = 3`'
ShardingProxy(5.0.0.RC1)2DB2Table
subqueryWithAny[hasShardingKey:all]; Support:false; SQL: SELECT * FROM customer WHERE status = ANY (SELECT status FROM customer_email where id = 3) and id = 3;
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: This version of ShardingProxy doesn't yet support this SQL. 'Unsupported SQL of `SELECT * FROM customer WHERE status = ANY (SELECT status FROM customer_email where id = 3) and id = 3`'
ShardingProxy(5.0.0.RC1)2DB2Table
subqueryWithAll[hasShardingKey:none]; Support:false; SQL: SELECT * FROM customer WHERE status > ALL (SELECT status FROM customer_email);
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: This version of ShardingProxy doesn't yet support this SQL. 'Unsupported SQL of `SELECT * FROM customer WHERE status > ALL (SELECT status FROM customer_email)`'
ShardingProxy(5.0.0.RC1)2DB2Table
subqueryWithAll[hasShardingKey:single]; Support:false; SQL: SELECT * FROM customer WHERE status > ALL (SELECT status FROM customer_email) and id = 3;
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: This version of ShardingProxy doesn't yet support this SQL. 'Unsupported SQL of `SELECT * FROM customer WHERE status > ALL (SELECT status FROM customer_email) and id = 3`'
ShardingProxy(5.0.0.RC1)2DB2Table
subqueryWithAll[hasShardingKey:all]; Support:false; SQL: SELECT * FROM customer WHERE status > ALL (SELECT status FROM customer_email where id = 3) and id = 3;
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: This version of ShardingProxy doesn't yet support this SQL. 'Unsupported SQL of `SELECT * FROM customer WHERE status > ALL (SELECT status FROM customer_email where id = 3) and id = 3`'
ShardingProxy(5.0.0.RC1)2DB2Table
subqueryAsRowSubquery[hasShardingKey:none]; Support:false; SQL: SELECT * FROM customer WHERE (party_id, status) = (SELECT party_id, status FROM customer_email WHERE party_id = 10);
java.sql.SQLException: 2Unknown exception: [Must have sharding column with subquery.]
ShardingProxy(5.0.0.RC1)2DB2Table
subqueryWithNotExist[hasShardingKey:none]; Support:false; SQL: SELECT * FROM customer where not exists (select * from customer_email where c.status = status);
ds_00|SELECT * FROM customer_0000 where not exists (select * from customer_email where c.status = status)|
ds_00|SELECT * FROM customer_0001 where not exists (select * from customer_email where c.status = status)|
ds_01|SELECT * FROM customer_0002 where not exists (select * from customer_email where c.status = status)|
ds_01|SELECT * FROM customer_0003 where not exists (select * from customer_email where c.status = status)|
ShardingProxy(5.0.0.RC1)2DB2Table
subqueryWithNotExist[hasShardingKey:single]; Support:false; SQL: SELECT * FROM customer where not exists (select * from customer_email where c.status = status) and id = 3;
ds_00|SELECT * FROM customer_0000 where not exists (select * from customer_email where c.status = status) and id = 3|
ds_00|SELECT * FROM customer_0001 where not exists (select * from customer_email where c.status = status) and id = 3|
ds_01|SELECT * FROM customer_0002 where not exists (select * from customer_email where c.status = status) and id = 3|
ds_01|SELECT * FROM customer_0003 where not exists (select * from customer_email where c.status = status) and id = 3|
ShardingProxy(5.0.0.RC1)2DB2Table
subqueryWithNotExist[hasShardingKey:all]; Support:false; SQL: SELECT * FROM customer where not exists (select * from customer_email where c.status = status and id = 3) and id = 3;
ds_00|SELECT * FROM customer_0000 where not exists (select * from customer_email where c.status = status and id = 3) and id = 3|
ds_00|SELECT * FROM customer_0001 where not exists (select * from customer_email where c.status = status and id = 3) and id = 3|
ds_01|SELECT * FROM customer_0002 where not exists (select * from customer_email where c.status = status and id = 3) and id = 3|
ds_01|SELECT * FROM customer_0003 where not exists (select * from customer_email where c.status = status and id = 3) and id = 3|
ShardingProxy(5.0.0.RC1)2DB2Table
subqueryAsCorrelatedSubquery[hasShardingKey:none]; Support:false; SQL: SELECT * FROM customer WHERE status = ANY (SELECT status FROM customer_email WHERE customer.party_id = customer_email);
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: This version of ShardingProxy doesn't yet support this SQL. 'Unsupported SQL of `SELECT * FROM customer WHERE status = ANY (SELECT status FROM customer_email WHERE customer.party_id = customer_email)`'
ShardingProxy(5.0.0.RC1)2DB2Table
subqueryAsCorrelatedSubquery[hasShardingKey:single]; Support:false; SQL: SELECT * FROM customer WHERE status = ANY (SELECT status FROM customer_email WHERE customer.party_id = customer_email) and id = 3;
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: This version of ShardingProxy doesn't yet support this SQL. 'Unsupported SQL of `SELECT * FROM customer WHERE status = ANY (SELECT status FROM customer_email WHERE customer.party_id = customer_email) and id = 3`'
ShardingProxy(5.0.0.RC1)2DB2Table
subqueryAsCorrelatedSubquery[hasShardingKey:all]; Support:false; SQL: SELECT * FROM customer WHERE status = ANY (SELECT status FROM customer_email WHERE customer.party_id = customer_email.party_id and id = 3) and id = 3;
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: This version of ShardingProxy doesn't yet support this SQL. 'Unsupported SQL of `SELECT * FROM customer WHERE status = ANY (SELECT status FROM customer_email WHERE customer.party_id = customer_email.party_id and id = 3) and id = 3`'
ShardingProxy(5.0.0.RC1)2DB2Table
subqueryInLeftJoin[hasShardingKey:none]; Support:false; SQL: SELECT * FROM customer LEFT JOIN (SELECT party_id, status FROM customer_email WHERE is_deleted = 'N') email ON email.party_id = cusomter.party_id;
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: This version of ShardingProxy doesn't yet support this SQL. 'Unsupported SQL of `SELECT * FROM customer LEFT JOIN (SELECT party_id, status FROM customer_email WHERE is_deleted = 'N') email ON email.party_id = cusomter.party_id`'
ShardingProxy(5.0.0.RC1)2DB2Table
subqueryInLeftJoin[hasShardingKey:single]; Support:false; SQL: SELECT * FROM customer LEFT JOIN (SELECT party_id, status FROM customer_email WHERE is_deleted = 'N') email ON email.party_id = cusomter.party_id and customer.id = 3;
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: This version of ShardingProxy doesn't yet support this SQL. 'Unsupported SQL of `SELECT * FROM customer LEFT JOIN (SELECT party_id, status FROM customer_email WHERE is_deleted = 'N') email ON email.party_id = cusomter.party_id and customer.id = 3`'
ShardingProxy(5.0.0.RC1)2DB2Table
subqueryInLeftJoin[hasShardingKey:all]; Support:false; SQL: SELECT * FROM customer LEFT JOIN (SELECT party_id, status FROM customer_email WHERE is_deleted = 'N' and id = 3) email ON email.party_id = cusomter.party_id and customer.id = 3;
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: This version of ShardingProxy doesn't yet support this SQL. 'Unsupported SQL of `SELECT * FROM customer LEFT JOIN (SELECT party_id, status FROM customer_email WHERE is_deleted = 'N' and id = 3) email ON email.party_id = cusomter.party_id and customer.id = 3`'
ShardingProxy(5.0.0.RC1)2DB2Table
subqueryInSubquery[hasShardingKey:none]; Support:false; SQL: SELECT * FROM customer c WHERE status > (SELECT status FROM customer_email WHERE party_id > (SELECT id FROM full_table) LIMIT 1);
java.sql.SQLException: 2Unknown exception: [Must have sharding column with subquery.]
```
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] tristaZero edited a comment on issue #6497: Can subqueries be supported in select？
Posted by GitBox <gi...@apache.org>.
tristaZero edited a comment on issue #6497:
URL: https://github.com/apache/shardingsphere/issues/6497#issuecomment-696661621
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
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] lwtdev edited a comment on issue #6497: Can subqueries be supported in select？
Posted by GitBox <gi...@apache.org>.
lwtdev edited a comment on issue #6497:
URL: https://github.com/apache/shardingsphere/issues/6497#issuecomment-666271681
> 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?
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] tristaZero edited a comment on issue #6497: Can subqueries be supported in select？
Posted by GitBox <gi...@apache.org>.
tristaZero edited a comment on issue #6497:
URL: https://github.com/apache/shardingsphere/issues/6497#issuecomment-682345851
@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](https://github.com/apache/shardingsphere/blob/master/shardingsphere-sql-parser/shardingsphere-sql-parser-test/src/main/resources/sql/supported/dml/select.xml) 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
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] lwtdev edited a comment on issue #6497: Can subqueries be supported in select？
Posted by GitBox <gi...@apache.org>.
lwtdev edited a comment on issue #6497:
URL: https://github.com/apache/shardingsphere/issues/6497#issuecomment-696590455
Hi~ @jingshanglu @tristaZero
I Just run some subquery cases with newest version. Test result as fallow:
|SQL Demo Title |SQL Demo |Sharding Column |ShardingProxy(5.0.0.RC1)2DB2Table |
|---- |---- |---- |---- |
|subqueryAsScalarOperand |SELECT (SELECT name FROM customer); |NONE |N |
|subqueryAsScalarOperand |SELECT (SELECT name FROM customer where id = 3); |ALL |Y |
|subqueryInColumns |SELECT id AS c_id , (select status FROM customer_email ce WHERE ce.party_id = c.party_id) AS ec_status FROM customer c; |NONE |N |
|subqueryInColumns |SELECT id AS c_id , (select status FROM customer_email ce WHERE ce.party_id = c.party_id) AS ec_status FROM customer c WHERE id = 3; |SINGLE |Y |
|subqueryInColumns |SELECT id AS c_id , (select status FROM customer_email ce WHERE ce.party_id = c.party_id and id = 3) AS ec_status FROM customer c WHERE id = 3; |ALL |Y |
|subqueryInComparisons |SELECT * FROM customer WHERE status < (SELECT MAX(status) FROM customer_email); |NONE |Y |
|subqueryInComparisons |SELECT * FROM customer WHERE status < (SELECT MAX(status) FROM customer_email) and id = 3; |SINGLE |Y |
|subqueryInComparisons |SELECT * FROM customer WHERE status < (SELECT MAX(status) FROM customer_email where id = 3) and id = 3; |ALL |Y |
|subqueryWithAny |SELECT * FROM customer WHERE status = ANY (SELECT status FROM customer_email); |NONE |N |
|subqueryWithAny |SELECT * FROM customer WHERE status = ANY (SELECT status FROM customer_email) and id = 3; |SINGLE |N |
|subqueryWithAny |SELECT * FROM customer WHERE status = ANY (SELECT status FROM customer_email where id = 3) and id = 3; |ALL |N |
|subqueryWithAll |SELECT * FROM customer WHERE status > ALL (SELECT status FROM customer_email); |NONE |N |
|subqueryWithAll |SELECT * FROM customer WHERE status > ALL (SELECT status FROM customer_email) and id = 3; |SINGLE |N |
|subqueryWithAll |SELECT * FROM customer WHERE status > ALL (SELECT status FROM customer_email where id = 3) and id = 3; |ALL |N |
|subqueryAsRowSubquery |SELECT * FROM customer WHERE (party_id, status) = (SELECT party_id, status FROM customer_email WHERE party_id = 10); |NONE |N |
|subqueryAsRowSubquery |SELECT * FROM customer WHERE (party_id, status) = (SELECT party_id, status FROM customer_email WHERE party_id = 10) and id = 3; |SINGLE |Y |
|subqueryAsRowSubquery |SELECT * FROM customer WHERE (party_id, status) = (SELECT party_id, status FROM customer_email WHERE party_id = 10 and id = 3) and id = 3; |ALL |Y |
|subqueryWithNotExist |SELECT * FROM customer where not exists (select * from customer_email where c.status = status); |NONE |N |
|subqueryWithNotExist |SELECT * FROM customer where not exists (select * from customer_email where c.status = status) and id = 3; |SINGLE |N |
|subqueryWithNotExist |SELECT * FROM customer where not exists (select * from customer_email where c.status = status and id = 3) and id = 3; |ALL |N |
|subqueryWithIn |SELECT * FROM customer c WHERE status in (SELECT status FROM customer_email); |NONE |Y |
|subqueryWithIn |SELECT * FROM customer c WHERE status in (SELECT status FROM customer_email) AND id = 3; |SINGLE |Y |
|subqueryWithIn |SELECT * FROM customer c WHERE status in (SELECT status FROM customer_email WHERE id = 3 ) AND id = 3; |ALL |Y |
|subqueryAsCorrelatedSubquery |SELECT * FROM customer WHERE status = ANY (SELECT status FROM customer_email WHERE customer.party_id = customer_email); |NONE |N |
|subqueryAsCorrelatedSubquery |SELECT * FROM customer WHERE status = ANY (SELECT status FROM customer_email WHERE customer.party_id = customer_email) and id = 3; |SINGLE |N |
|subqueryAsCorrelatedSubquery |SELECT * FROM customer WHERE status = ANY (SELECT status FROM customer_email WHERE customer.party_id = customer_email.party_id and id = 3) and id = 3; |ALL |N |
|subqueryAsDerivedTableInFromClause |SELECT sbf1, sbf2 FROM (SELECT party_id AS sbf1, status AS sbf2 FROM customer) AS sb WHERE sbf2 > 1; |NONE |Y |
|subqueryAsDerivedTableInFromClause |SELECT sbf1, sbf2 FROM (SELECT party_id AS sbf1, status AS sbf2 FROM customer where id = 3) AS sb WHERE sbf2 > 1; |ALL |Y |
|subqueryAsDerivedTableWithCount |SELECT COUNT(*) FROM (SELECT * FROM customer) AS t; |NONE |Y |
|subqueryAsDerivedTableWithCount |SELECT COUNT(*) FROM (SELECT * FROM customer WHERE id = 3) AS t; |ALL |Y |
|subqueryInLeftJoin |SELECT * FROM customer LEFT JOIN (SELECT party_id, status FROM customer_email WHERE is_deleted = 'N') email ON email.party_id = cusomter.party_id; |NONE |N |
|subqueryInLeftJoin |SELECT * FROM customer LEFT JOIN (SELECT party_id, status FROM customer_email WHERE is_deleted = 'N') email ON email.party_id = cusomter.party_id and customer.id = 3; |SINGLE |N |
|subqueryInLeftJoin |SELECT * FROM customer LEFT JOIN (SELECT party_id, status FROM customer_email WHERE is_deleted = 'N' and id = 3) email ON email.party_id = cusomter.party_id and customer.id = 3; |ALL |N |
|subqueryInSubquery |SELECT * FROM customer c WHERE status > (SELECT status FROM customer_email WHERE party_id > (SELECT id FROM full_table) LIMIT 1); |NONE |N |
|subqueryInSubquery |SELECT * FROM customer c WHERE status > (SELECT status FROM customer_email WHERE party_id > (SELECT id FROM full_table) LIMIT 1) AND id = 3; |SINGLE |Y |
|subqueryInSubquery |SELECT * FROM customer c WHERE status > (SELECT status FROM customer_email WHERE id = 3 AND party_id > (SELECT id FROM full_table WHERE id = 3) LIMIT 1) AND id = 3; |ALL |Y |
- UnSupport Case Detail
```
ShardingProxy(5.0.0.RC1)2DB2Table
subqueryAsScalarOperand[hasShardingKey:none]; Support:false; SQL: SELECT (SELECT name FROM customer);
java.sql.SQLException: 2Unknown exception: [String index out of range: 35]
ShardingProxy(5.0.0.RC1)2DB2Table
subqueryInColumns[hasShardingKey:none]; Support:false; SQL: SELECT id AS c_id , (select status FROM customer_email ce WHERE ce.party_id = c.party_id) AS ec_status FROM customer c;
java.sql.SQLException: 2Unknown exception: [Must have sharding column with subquery.]
ShardingProxy(5.0.0.RC1)2DB2Table
subqueryWithAny[hasShardingKey:none]; Support:false; SQL: SELECT * FROM customer WHERE status = ANY (SELECT status FROM customer_email);
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: This version of ShardingProxy doesn't yet support this SQL. 'Unsupported SQL of `SELECT * FROM customer WHERE status = ANY (SELECT status FROM customer_email)`'
ShardingProxy(5.0.0.RC1)2DB2Table
subqueryWithAny[hasShardingKey:single]; Support:false; SQL: SELECT * FROM customer WHERE status = ANY (SELECT status FROM customer_email) and id = 3;
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: This version of ShardingProxy doesn't yet support this SQL. 'Unsupported SQL of `SELECT * FROM customer WHERE status = ANY (SELECT status FROM customer_email) and id = 3`'
ShardingProxy(5.0.0.RC1)2DB2Table
subqueryWithAny[hasShardingKey:all]; Support:false; SQL: SELECT * FROM customer WHERE status = ANY (SELECT status FROM customer_email where id = 3) and id = 3;
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: This version of ShardingProxy doesn't yet support this SQL. 'Unsupported SQL of `SELECT * FROM customer WHERE status = ANY (SELECT status FROM customer_email where id = 3) and id = 3`'
ShardingProxy(5.0.0.RC1)2DB2Table
subqueryWithAll[hasShardingKey:none]; Support:false; SQL: SELECT * FROM customer WHERE status > ALL (SELECT status FROM customer_email);
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: This version of ShardingProxy doesn't yet support this SQL. 'Unsupported SQL of `SELECT * FROM customer WHERE status > ALL (SELECT status FROM customer_email)`'
ShardingProxy(5.0.0.RC1)2DB2Table
subqueryWithAll[hasShardingKey:single]; Support:false; SQL: SELECT * FROM customer WHERE status > ALL (SELECT status FROM customer_email) and id = 3;
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: This version of ShardingProxy doesn't yet support this SQL. 'Unsupported SQL of `SELECT * FROM customer WHERE status > ALL (SELECT status FROM customer_email) and id = 3`'
ShardingProxy(5.0.0.RC1)2DB2Table
subqueryWithAll[hasShardingKey:all]; Support:false; SQL: SELECT * FROM customer WHERE status > ALL (SELECT status FROM customer_email where id = 3) and id = 3;
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: This version of ShardingProxy doesn't yet support this SQL. 'Unsupported SQL of `SELECT * FROM customer WHERE status > ALL (SELECT status FROM customer_email where id = 3) and id = 3`'
ShardingProxy(5.0.0.RC1)2DB2Table
subqueryAsRowSubquery[hasShardingKey:none]; Support:false; SQL: SELECT * FROM customer WHERE (party_id, status) = (SELECT party_id, status FROM customer_email WHERE party_id = 10);
java.sql.SQLException: 2Unknown exception: [Must have sharding column with subquery.]
ShardingProxy(5.0.0.RC1)2DB2Table
subqueryWithNotExist[hasShardingKey:none]; Support:false; SQL: SELECT * FROM customer where not exists (select * from customer_email where c.status = status);
ds_00|SELECT * FROM customer_0000 where not exists (select * from customer_email where c.status = status)|
ds_00|SELECT * FROM customer_0001 where not exists (select * from customer_email where c.status = status)|
ds_01|SELECT * FROM customer_0002 where not exists (select * from customer_email where c.status = status)|
ds_01|SELECT * FROM customer_0003 where not exists (select * from customer_email where c.status = status)|
ShardingProxy(5.0.0.RC1)2DB2Table
subqueryWithNotExist[hasShardingKey:single]; Support:false; SQL: SELECT * FROM customer where not exists (select * from customer_email where c.status = status) and id = 3;
ds_00|SELECT * FROM customer_0000 where not exists (select * from customer_email where c.status = status) and id = 3|
ds_00|SELECT * FROM customer_0001 where not exists (select * from customer_email where c.status = status) and id = 3|
ds_01|SELECT * FROM customer_0002 where not exists (select * from customer_email where c.status = status) and id = 3|
ds_01|SELECT * FROM customer_0003 where not exists (select * from customer_email where c.status = status) and id = 3|
ShardingProxy(5.0.0.RC1)2DB2Table
subqueryWithNotExist[hasShardingKey:all]; Support:false; SQL: SELECT * FROM customer where not exists (select * from customer_email where c.status = status and id = 3) and id = 3;
ds_00|SELECT * FROM customer_0000 where not exists (select * from customer_email where c.status = status and id = 3) and id = 3|
ds_00|SELECT * FROM customer_0001 where not exists (select * from customer_email where c.status = status and id = 3) and id = 3|
ds_01|SELECT * FROM customer_0002 where not exists (select * from customer_email where c.status = status and id = 3) and id = 3|
ds_01|SELECT * FROM customer_0003 where not exists (select * from customer_email where c.status = status and id = 3) and id = 3|
ShardingProxy(5.0.0.RC1)2DB2Table
subqueryAsCorrelatedSubquery[hasShardingKey:none]; Support:false; SQL: SELECT * FROM customer WHERE status = ANY (SELECT status FROM customer_email WHERE customer.party_id = customer_email);
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: This version of ShardingProxy doesn't yet support this SQL. 'Unsupported SQL of `SELECT * FROM customer WHERE status = ANY (SELECT status FROM customer_email WHERE customer.party_id = customer_email)`'
ShardingProxy(5.0.0.RC1)2DB2Table
subqueryAsCorrelatedSubquery[hasShardingKey:single]; Support:false; SQL: SELECT * FROM customer WHERE status = ANY (SELECT status FROM customer_email WHERE customer.party_id = customer_email) and id = 3;
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: This version of ShardingProxy doesn't yet support this SQL. 'Unsupported SQL of `SELECT * FROM customer WHERE status = ANY (SELECT status FROM customer_email WHERE customer.party_id = customer_email) and id = 3`'
ShardingProxy(5.0.0.RC1)2DB2Table
subqueryAsCorrelatedSubquery[hasShardingKey:all]; Support:false; SQL: SELECT * FROM customer WHERE status = ANY (SELECT status FROM customer_email WHERE customer.party_id = customer_email.party_id and id = 3) and id = 3;
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: This version of ShardingProxy doesn't yet support this SQL. 'Unsupported SQL of `SELECT * FROM customer WHERE status = ANY (SELECT status FROM customer_email WHERE customer.party_id = customer_email.party_id and id = 3) and id = 3`'
ShardingProxy(5.0.0.RC1)2DB2Table
subqueryInLeftJoin[hasShardingKey:none]; Support:false; SQL: SELECT * FROM customer LEFT JOIN (SELECT party_id, status FROM customer_email WHERE is_deleted = 'N') email ON email.party_id = cusomter.party_id;
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: This version of ShardingProxy doesn't yet support this SQL. 'Unsupported SQL of `SELECT * FROM customer LEFT JOIN (SELECT party_id, status FROM customer_email WHERE is_deleted = 'N') email ON email.party_id = cusomter.party_id`'
ShardingProxy(5.0.0.RC1)2DB2Table
subqueryInLeftJoin[hasShardingKey:single]; Support:false; SQL: SELECT * FROM customer LEFT JOIN (SELECT party_id, status FROM customer_email WHERE is_deleted = 'N') email ON email.party_id = cusomter.party_id and customer.id = 3;
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: This version of ShardingProxy doesn't yet support this SQL. 'Unsupported SQL of `SELECT * FROM customer LEFT JOIN (SELECT party_id, status FROM customer_email WHERE is_deleted = 'N') email ON email.party_id = cusomter.party_id and customer.id = 3`'
ShardingProxy(5.0.0.RC1)2DB2Table
subqueryInLeftJoin[hasShardingKey:all]; Support:false; SQL: SELECT * FROM customer LEFT JOIN (SELECT party_id, status FROM customer_email WHERE is_deleted = 'N' and id = 3) email ON email.party_id = cusomter.party_id and customer.id = 3;
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: This version of ShardingProxy doesn't yet support this SQL. 'Unsupported SQL of `SELECT * FROM customer LEFT JOIN (SELECT party_id, status FROM customer_email WHERE is_deleted = 'N' and id = 3) email ON email.party_id = cusomter.party_id and customer.id = 3`'
ShardingProxy(5.0.0.RC1)2DB2Table
subqueryInSubquery[hasShardingKey:none]; Support:false; SQL: SELECT * FROM customer c WHERE status > (SELECT status FROM customer_email WHERE party_id > (SELECT id FROM full_table) LIMIT 1);
java.sql.SQLException: 2Unknown exception: [Must have sharding column with subquery.]
```
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] kimmking commented on issue #6497: Can subqueries be supported in select？
Posted by GitBox <gi...@apache.org>.
kimmking commented on issue #6497:
URL: https://github.com/apache/shardingsphere/issues/6497#issuecomment-706841110
Sounds great.
It's a big deal and can be improving an amazing step forward for sql support.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] tristaZero commented on issue #6497: Can subqueries be supported in select？
Posted by GitBox <gi...@apache.org>.
tristaZero commented on issue #6497:
URL: https://github.com/apache/shardingsphere/issues/6497#issuecomment-696661621
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
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] lwtdev commented on issue #6497: Can subqueries be supported in select？
Posted by GitBox <gi...@apache.org>.
lwtdev commented on issue #6497:
URL: https://github.com/apache/shardingsphere/issues/6497#issuecomment-696584030
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] jingshanglu commented on issue #6497: Can subqueries be supported in select？
Posted by GitBox <gi...@apache.org>.
jingshanglu commented on issue #6497:
URL: https://github.com/apache/shardingsphere/issues/6497#issuecomment-665393819
@awnurrk Now, subquery in fromclause is supported, others will be supported in the future.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] lwtdev commented on issue #6497: Can subqueries be supported in select？
Posted by GitBox <gi...@apache.org>.
lwtdev commented on issue #6497:
URL: https://github.com/apache/shardingsphere/issues/6497#issuecomment-698708296
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] lwtdev edited a comment on issue #6497: Can subqueries be supported in select？
Posted by GitBox <gi...@apache.org>.
lwtdev edited a comment on issue #6497:
URL: https://github.com/apache/shardingsphere/issues/6497#issuecomment-696590455
Hi~ @jingshanglu @tristaZero
I Just run some subquery cases with newest version. Test results and detail as follows:
|SQL Demo Title |SQL Demo |Sharding Column |ShardingProxy(5.0.0.RC1)2DB2Table |
|---- |---- |---- |---- |
|subqueryAsScalarOperand |SELECT (SELECT name FROM customer); |NONE |N |
|subqueryAsScalarOperand |SELECT (SELECT name FROM customer where id = 3); |ALL |Y |
|subqueryInColumns |SELECT id AS c_id , (select status FROM customer_email ce WHERE ce.party_id = c.party_id) AS ec_status FROM customer c; |NONE |N |
|subqueryInColumns |SELECT id AS c_id , (select status FROM customer_email ce WHERE ce.party_id = c.party_id) AS ec_status FROM customer c WHERE id = 3; |SINGLE |Y |
|subqueryInColumns |SELECT id AS c_id , (select status FROM customer_email ce WHERE ce.party_id = c.party_id and id = 3) AS ec_status FROM customer c WHERE id = 3; |ALL |Y |
|subqueryInComparisons |SELECT * FROM customer WHERE status < (SELECT MAX(status) FROM customer_email); |NONE |Y |
|subqueryInComparisons |SELECT * FROM customer WHERE status < (SELECT MAX(status) FROM customer_email) and id = 3; |SINGLE |Y |
|subqueryInComparisons |SELECT * FROM customer WHERE status < (SELECT MAX(status) FROM customer_email where id = 3) and id = 3; |ALL |Y |
|subqueryWithAny |SELECT * FROM customer WHERE status = ANY (SELECT status FROM customer_email); |NONE |N |
|subqueryWithAny |SELECT * FROM customer WHERE status = ANY (SELECT status FROM customer_email) and id = 3; |SINGLE |N |
|subqueryWithAny |SELECT * FROM customer WHERE status = ANY (SELECT status FROM customer_email where id = 3) and id = 3; |ALL |N |
|subqueryWithAll |SELECT * FROM customer WHERE status > ALL (SELECT status FROM customer_email); |NONE |N |
|subqueryWithAll |SELECT * FROM customer WHERE status > ALL (SELECT status FROM customer_email) and id = 3; |SINGLE |N |
|subqueryWithAll |SELECT * FROM customer WHERE status > ALL (SELECT status FROM customer_email where id = 3) and id = 3; |ALL |N |
|subqueryAsRowSubquery |SELECT * FROM customer WHERE (party_id, status) = (SELECT party_id, status FROM customer_email WHERE party_id = 10); |NONE |N |
|subqueryAsRowSubquery |SELECT * FROM customer WHERE (party_id, status) = (SELECT party_id, status FROM customer_email WHERE party_id = 10) and id = 3; |SINGLE |Y |
|subqueryAsRowSubquery |SELECT * FROM customer WHERE (party_id, status) = (SELECT party_id, status FROM customer_email WHERE party_id = 10 and id = 3) and id = 3; |ALL |Y |
|subqueryWithNotExist |SELECT * FROM customer where not exists (select * from customer_email where c.status = status); |NONE |N |
|subqueryWithNotExist |SELECT * FROM customer where not exists (select * from customer_email where c.status = status) and id = 3; |SINGLE |N |
|subqueryWithNotExist |SELECT * FROM customer where not exists (select * from customer_email where c.status = status and id = 3) and id = 3; |ALL |N |
|subqueryWithIn |SELECT * FROM customer c WHERE status in (SELECT status FROM customer_email); |NONE |Y |
|subqueryWithIn |SELECT * FROM customer c WHERE status in (SELECT status FROM customer_email) AND id = 3; |SINGLE |Y |
|subqueryWithIn |SELECT * FROM customer c WHERE status in (SELECT status FROM customer_email WHERE id = 3 ) AND id = 3; |ALL |Y |
|subqueryAsCorrelatedSubquery |SELECT * FROM customer WHERE status = ANY (SELECT status FROM customer_email WHERE customer.party_id = customer_email); |NONE |N |
|subqueryAsCorrelatedSubquery |SELECT * FROM customer WHERE status = ANY (SELECT status FROM customer_email WHERE customer.party_id = customer_email) and id = 3; |SINGLE |N |
|subqueryAsCorrelatedSubquery |SELECT * FROM customer WHERE status = ANY (SELECT status FROM customer_email WHERE customer.party_id = customer_email.party_id and id = 3) and id = 3; |ALL |N |
|subqueryAsDerivedTableInFromClause |SELECT sbf1, sbf2 FROM (SELECT party_id AS sbf1, status AS sbf2 FROM customer) AS sb WHERE sbf2 > 1; |NONE |Y |
|subqueryAsDerivedTableInFromClause |SELECT sbf1, sbf2 FROM (SELECT party_id AS sbf1, status AS sbf2 FROM customer where id = 3) AS sb WHERE sbf2 > 1; |ALL |Y |
|subqueryAsDerivedTableWithCount |SELECT COUNT(*) FROM (SELECT * FROM customer) AS t; |NONE |Y |
|subqueryAsDerivedTableWithCount |SELECT COUNT(*) FROM (SELECT * FROM customer WHERE id = 3) AS t; |ALL |Y |
|subqueryInLeftJoin |SELECT * FROM customer LEFT JOIN (SELECT party_id, status FROM customer_email WHERE is_deleted = 'N') email ON email.party_id = cusomter.party_id; |NONE |N |
|subqueryInLeftJoin |SELECT * FROM customer LEFT JOIN (SELECT party_id, status FROM customer_email WHERE is_deleted = 'N') email ON email.party_id = cusomter.party_id and customer.id = 3; |SINGLE |N |
|subqueryInLeftJoin |SELECT * FROM customer LEFT JOIN (SELECT party_id, status FROM customer_email WHERE is_deleted = 'N' and id = 3) email ON email.party_id = cusomter.party_id and customer.id = 3; |ALL |N |
|subqueryInSubquery |SELECT * FROM customer c WHERE status > (SELECT status FROM customer_email WHERE party_id > (SELECT id FROM full_table) LIMIT 1); |NONE |N |
|subqueryInSubquery |SELECT * FROM customer c WHERE status > (SELECT status FROM customer_email WHERE party_id > (SELECT id FROM full_table) LIMIT 1) AND id = 3; |SINGLE |Y |
|subqueryInSubquery |SELECT * FROM customer c WHERE status > (SELECT status FROM customer_email WHERE id = 3 AND party_id > (SELECT id FROM full_table WHERE id = 3) LIMIT 1) AND id = 3; |ALL |Y |
- UnSupport Case Detail
```
ShardingProxy(5.0.0.RC1)2DB2Table
subqueryAsScalarOperand[hasShardingKey:none]; Support:false; SQL: SELECT (SELECT name FROM customer);
java.sql.SQLException: 2Unknown exception: [String index out of range: 35]
ShardingProxy(5.0.0.RC1)2DB2Table
subqueryInColumns[hasShardingKey:none]; Support:false; SQL: SELECT id AS c_id , (select status FROM customer_email ce WHERE ce.party_id = c.party_id) AS ec_status FROM customer c;
java.sql.SQLException: 2Unknown exception: [Must have sharding column with subquery.]
ShardingProxy(5.0.0.RC1)2DB2Table
subqueryWithAny[hasShardingKey:none]; Support:false; SQL: SELECT * FROM customer WHERE status = ANY (SELECT status FROM customer_email);
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: This version of ShardingProxy doesn't yet support this SQL. 'Unsupported SQL of `SELECT * FROM customer WHERE status = ANY (SELECT status FROM customer_email)`'
ShardingProxy(5.0.0.RC1)2DB2Table
subqueryWithAny[hasShardingKey:single]; Support:false; SQL: SELECT * FROM customer WHERE status = ANY (SELECT status FROM customer_email) and id = 3;
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: This version of ShardingProxy doesn't yet support this SQL. 'Unsupported SQL of `SELECT * FROM customer WHERE status = ANY (SELECT status FROM customer_email) and id = 3`'
ShardingProxy(5.0.0.RC1)2DB2Table
subqueryWithAny[hasShardingKey:all]; Support:false; SQL: SELECT * FROM customer WHERE status = ANY (SELECT status FROM customer_email where id = 3) and id = 3;
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: This version of ShardingProxy doesn't yet support this SQL. 'Unsupported SQL of `SELECT * FROM customer WHERE status = ANY (SELECT status FROM customer_email where id = 3) and id = 3`'
ShardingProxy(5.0.0.RC1)2DB2Table
subqueryWithAll[hasShardingKey:none]; Support:false; SQL: SELECT * FROM customer WHERE status > ALL (SELECT status FROM customer_email);
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: This version of ShardingProxy doesn't yet support this SQL. 'Unsupported SQL of `SELECT * FROM customer WHERE status > ALL (SELECT status FROM customer_email)`'
ShardingProxy(5.0.0.RC1)2DB2Table
subqueryWithAll[hasShardingKey:single]; Support:false; SQL: SELECT * FROM customer WHERE status > ALL (SELECT status FROM customer_email) and id = 3;
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: This version of ShardingProxy doesn't yet support this SQL. 'Unsupported SQL of `SELECT * FROM customer WHERE status > ALL (SELECT status FROM customer_email) and id = 3`'
ShardingProxy(5.0.0.RC1)2DB2Table
subqueryWithAll[hasShardingKey:all]; Support:false; SQL: SELECT * FROM customer WHERE status > ALL (SELECT status FROM customer_email where id = 3) and id = 3;
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: This version of ShardingProxy doesn't yet support this SQL. 'Unsupported SQL of `SELECT * FROM customer WHERE status > ALL (SELECT status FROM customer_email where id = 3) and id = 3`'
ShardingProxy(5.0.0.RC1)2DB2Table
subqueryAsRowSubquery[hasShardingKey:none]; Support:false; SQL: SELECT * FROM customer WHERE (party_id, status) = (SELECT party_id, status FROM customer_email WHERE party_id = 10);
java.sql.SQLException: 2Unknown exception: [Must have sharding column with subquery.]
ShardingProxy(5.0.0.RC1)2DB2Table
subqueryWithNotExist[hasShardingKey:none]; Support:false; SQL: SELECT * FROM customer where not exists (select * from customer_email where c.status = status);
ds_00|SELECT * FROM customer_0000 where not exists (select * from customer_email where c.status = status)|
ds_00|SELECT * FROM customer_0001 where not exists (select * from customer_email where c.status = status)|
ds_01|SELECT * FROM customer_0002 where not exists (select * from customer_email where c.status = status)|
ds_01|SELECT * FROM customer_0003 where not exists (select * from customer_email where c.status = status)|
ShardingProxy(5.0.0.RC1)2DB2Table
subqueryWithNotExist[hasShardingKey:single]; Support:false; SQL: SELECT * FROM customer where not exists (select * from customer_email where c.status = status) and id = 3;
ds_00|SELECT * FROM customer_0000 where not exists (select * from customer_email where c.status = status) and id = 3|
ds_00|SELECT * FROM customer_0001 where not exists (select * from customer_email where c.status = status) and id = 3|
ds_01|SELECT * FROM customer_0002 where not exists (select * from customer_email where c.status = status) and id = 3|
ds_01|SELECT * FROM customer_0003 where not exists (select * from customer_email where c.status = status) and id = 3|
ShardingProxy(5.0.0.RC1)2DB2Table
subqueryWithNotExist[hasShardingKey:all]; Support:false; SQL: SELECT * FROM customer where not exists (select * from customer_email where c.status = status and id = 3) and id = 3;
ds_00|SELECT * FROM customer_0000 where not exists (select * from customer_email where c.status = status and id = 3) and id = 3|
ds_00|SELECT * FROM customer_0001 where not exists (select * from customer_email where c.status = status and id = 3) and id = 3|
ds_01|SELECT * FROM customer_0002 where not exists (select * from customer_email where c.status = status and id = 3) and id = 3|
ds_01|SELECT * FROM customer_0003 where not exists (select * from customer_email where c.status = status and id = 3) and id = 3|
ShardingProxy(5.0.0.RC1)2DB2Table
subqueryAsCorrelatedSubquery[hasShardingKey:none]; Support:false; SQL: SELECT * FROM customer WHERE status = ANY (SELECT status FROM customer_email WHERE customer.party_id = customer_email);
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: This version of ShardingProxy doesn't yet support this SQL. 'Unsupported SQL of `SELECT * FROM customer WHERE status = ANY (SELECT status FROM customer_email WHERE customer.party_id = customer_email)`'
ShardingProxy(5.0.0.RC1)2DB2Table
subqueryAsCorrelatedSubquery[hasShardingKey:single]; Support:false; SQL: SELECT * FROM customer WHERE status = ANY (SELECT status FROM customer_email WHERE customer.party_id = customer_email) and id = 3;
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: This version of ShardingProxy doesn't yet support this SQL. 'Unsupported SQL of `SELECT * FROM customer WHERE status = ANY (SELECT status FROM customer_email WHERE customer.party_id = customer_email) and id = 3`'
ShardingProxy(5.0.0.RC1)2DB2Table
subqueryAsCorrelatedSubquery[hasShardingKey:all]; Support:false; SQL: SELECT * FROM customer WHERE status = ANY (SELECT status FROM customer_email WHERE customer.party_id = customer_email.party_id and id = 3) and id = 3;
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: This version of ShardingProxy doesn't yet support this SQL. 'Unsupported SQL of `SELECT * FROM customer WHERE status = ANY (SELECT status FROM customer_email WHERE customer.party_id = customer_email.party_id and id = 3) and id = 3`'
ShardingProxy(5.0.0.RC1)2DB2Table
subqueryInLeftJoin[hasShardingKey:none]; Support:false; SQL: SELECT * FROM customer LEFT JOIN (SELECT party_id, status FROM customer_email WHERE is_deleted = 'N') email ON email.party_id = cusomter.party_id;
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: This version of ShardingProxy doesn't yet support this SQL. 'Unsupported SQL of `SELECT * FROM customer LEFT JOIN (SELECT party_id, status FROM customer_email WHERE is_deleted = 'N') email ON email.party_id = cusomter.party_id`'
ShardingProxy(5.0.0.RC1)2DB2Table
subqueryInLeftJoin[hasShardingKey:single]; Support:false; SQL: SELECT * FROM customer LEFT JOIN (SELECT party_id, status FROM customer_email WHERE is_deleted = 'N') email ON email.party_id = cusomter.party_id and customer.id = 3;
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: This version of ShardingProxy doesn't yet support this SQL. 'Unsupported SQL of `SELECT * FROM customer LEFT JOIN (SELECT party_id, status FROM customer_email WHERE is_deleted = 'N') email ON email.party_id = cusomter.party_id and customer.id = 3`'
ShardingProxy(5.0.0.RC1)2DB2Table
subqueryInLeftJoin[hasShardingKey:all]; Support:false; SQL: SELECT * FROM customer LEFT JOIN (SELECT party_id, status FROM customer_email WHERE is_deleted = 'N' and id = 3) email ON email.party_id = cusomter.party_id and customer.id = 3;
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: This version of ShardingProxy doesn't yet support this SQL. 'Unsupported SQL of `SELECT * FROM customer LEFT JOIN (SELECT party_id, status FROM customer_email WHERE is_deleted = 'N' and id = 3) email ON email.party_id = cusomter.party_id and customer.id = 3`'
ShardingProxy(5.0.0.RC1)2DB2Table
subqueryInSubquery[hasShardingKey:none]; Support:false; SQL: SELECT * FROM customer c WHERE status > (SELECT status FROM customer_email WHERE party_id > (SELECT id FROM full_table) LIMIT 1);
java.sql.SQLException: 2Unknown exception: [Must have sharding column with subquery.]
```
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] lwtdev commented on issue #6497: Can subqueries be supported in select？
Posted by GitBox <gi...@apache.org>.
lwtdev commented on issue #6497:
URL: https://github.com/apache/shardingsphere/issues/6497#issuecomment-705931943
> @lwtdev `is_deleted` is not a sharding column,so, the exception will be reported.
but `customer` and `customer_email` are binding tables;
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] Fujinzhong edited a comment on issue #6497: Can subqueries be supported in select？
Posted by GitBox <gi...@apache.org>.
Fujinzhong edited a comment on issue #6497:
URL: https://github.com/apache/shardingsphere/issues/6497#issuecomment-694634942
> @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 ) T
HEN 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.segmen
t.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://172.16.40.217: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
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] lwtdev commented on issue #6497: Can subqueries be supported in select？
Posted by GitBox <gi...@apache.org>.
lwtdev commented on issue #6497:
URL: https://github.com/apache/shardingsphere/issues/6497#issuecomment-665620178
@jingshanglu
I just test some subquery with ShardingShpere-Proxy 5.0.0-RC1, found that there are still a lot of scenarios that are not supported.
Which of the following scenarios will be supported in the future ？
|SQL Demo Title |SQL Demo |SQL Contains Sharding Column |ShardingProxy(5.0.0.RC1) |
|---- |---- |---- |---- |
|subqueryAsScalarOperand |SELECT (SELECT name FROM customer); |NONE |N |
|subqueryAsScalarOperand |SELECT (SELECT name FROM customer where id = 3); |ALL |N |
|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; |SINGLE |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 |
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] jingshanglu edited a comment on issue #6497: Can subqueries be supported in select？
Posted by GitBox <gi...@apache.org>.
jingshanglu edited a comment on issue #6497:
URL: https://github.com/apache/shardingsphere/issues/6497#issuecomment-698300703
@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.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] jingshanglu commented on issue #6497: Can subqueries be supported in select？
Posted by GitBox <gi...@apache.org>.
jingshanglu commented on issue #6497:
URL: https://github.com/apache/shardingsphere/issues/6497#issuecomment-698300703
@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.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] lwtdev removed a comment on issue #6497: Can subqueries be supported in select？
Posted by GitBox <gi...@apache.org>.
lwtdev removed a comment on issue #6497:
URL: https://github.com/apache/shardingsphere/issues/6497#issuecomment-696584030
Hi~ @jingshanglu @tristaZero
I have test some subquery case just now
|SQL Demo Title |SQL Demo |Sharding Column |ShardingProxy(5.0.0.RC1)2DB2Table |
|---- |---- |---- |---- |
|subqueryAsScalarOperand |SELECT (SELECT name FROM customer); |NONE |N |
|subqueryAsScalarOperand |SELECT (SELECT name FROM customer where id = 3); |ALL |Y |
|subqueryInColumns |SELECT id AS c_id , (select status FROM customer_email ce WHERE ce.party_id = c.party_id) AS ec_status FROM customer c; |NONE |N |
|subqueryInColumns |SELECT id AS c_id , (select status FROM customer_email ce WHERE ce.party_id = c.party_id) AS ec_status FROM customer c WHERE id = 3; |SINGLE |Y |
|subqueryInColumns |SELECT id AS c_id , (select status FROM customer_email ce WHERE ce.party_id = c.party_id and id = 3) AS ec_status FROM customer c WHERE id = 3; |ALL |Y |
|subqueryInComparisons |SELECT * FROM customer WHERE status < (SELECT MAX(status) FROM customer_email); |NONE |Y |
|subqueryInComparisons |SELECT * FROM customer WHERE status < (SELECT MAX(status) FROM customer_email) and id = 3; |SINGLE |Y |
|subqueryInComparisons |SELECT * FROM customer WHERE status < (SELECT MAX(status) FROM customer_email where id = 3) and id = 3; |ALL |Y |
|subqueryWithAny |SELECT * FROM customer WHERE status = ANY (SELECT status FROM customer_email); |NONE |N |
|subqueryWithAny |SELECT * FROM customer WHERE status = ANY (SELECT status FROM customer_email) and id = 3; |SINGLE |N |
|subqueryWithAny |SELECT * FROM customer WHERE status = ANY (SELECT status FROM customer_email where id = 3) and id = 3; |ALL |N |
|subqueryWithAll |SELECT * FROM customer WHERE status > ALL (SELECT status FROM customer_email); |NONE |N |
|subqueryWithAll |SELECT * FROM customer WHERE status > ALL (SELECT status FROM customer_email) and id = 3; |SINGLE |N |
|subqueryWithAll |SELECT * FROM customer WHERE status > ALL (SELECT status FROM customer_email where id = 3) and id = 3; |ALL |N |
|subqueryAsRowSubquery |SELECT * FROM customer WHERE (party_id, status) = (SELECT party_id, status FROM customer_email WHERE party_id = 10); |NONE |N |
|subqueryAsRowSubquery |SELECT * FROM customer WHERE (party_id, status) = (SELECT party_id, status FROM customer_email WHERE party_id = 10) and id = 3; |SINGLE |Y |
|subqueryAsRowSubquery |SELECT * FROM customer WHERE (party_id, status) = (SELECT party_id, status FROM customer_email WHERE party_id = 10 and id = 3) and id = 3; |ALL |Y |
|subqueryWithNotExist |SELECT * FROM customer where not exists (select * from customer_email where c.status = status); |NONE |N |
|subqueryWithNotExist |SELECT * FROM customer where not exists (select * from customer_email where c.status = status) and id = 3; |SINGLE |N |
|subqueryWithNotExist |SELECT * FROM customer where not exists (select * from customer_email where c.status = status and id = 3) and id = 3; |ALL |N |
|subqueryWithIn |SELECT * FROM customer c WHERE status in (SELECT status FROM customer_email); |NONE |Y |
|subqueryWithIn |SELECT * FROM customer c WHERE status in (SELECT status FROM customer_email) AND id = 3; |SINGLE |Y |
|subqueryWithIn |SELECT * FROM customer c WHERE status in (SELECT status FROM customer_email WHERE id = 3 ) AND id = 3; |ALL |Y |
|subqueryAsCorrelatedSubquery |SELECT * FROM customer WHERE status = ANY (SELECT status FROM customer_email WHERE customer.party_id = customer_email); |NONE |N |
|subqueryAsCorrelatedSubquery |SELECT * FROM customer WHERE status = ANY (SELECT status FROM customer_email WHERE customer.party_id = customer_email) and id = 3; |SINGLE |N |
|subqueryAsCorrelatedSubquery |SELECT * FROM customer WHERE status = ANY (SELECT status FROM customer_email WHERE customer.party_id = customer_email.party_id and id = 3) and id = 3; |ALL |N |
|subqueryAsDerivedTableInFromClause |SELECT sbf1, sbf2 FROM (SELECT party_id AS sbf1, status AS sbf2 FROM customer) AS sb WHERE sbf2 > 1; |NONE |Y |
|subqueryAsDerivedTableInFromClause |SELECT sbf1, sbf2 FROM (SELECT party_id AS sbf1, status AS sbf2 FROM customer where id = 3) AS sb WHERE sbf2 > 1; |ALL |Y |
|subqueryAsDerivedTableWithCount |SELECT COUNT(*) FROM (SELECT * FROM customer) AS t; |NONE |Y |
|subqueryAsDerivedTableWithCount |SELECT COUNT(*) FROM (SELECT * FROM customer WHERE id = 3) AS t; |ALL |Y |
|subqueryInLeftJoin |SELECT * FROM customer LEFT JOIN (SELECT party_id, status FROM customer_email WHERE is_deleted = 'N') email ON email.party_id = cusomter.party_id; |NONE |N |
|subqueryInLeftJoin |SELECT * FROM customer LEFT JOIN (SELECT party_id, status FROM customer_email WHERE is_deleted = 'N') email ON email.party_id = cusomter.party_id and customer.id = 3; |SINGLE |N |
|subqueryInLeftJoin |SELECT * FROM customer LEFT JOIN (SELECT party_id, status FROM customer_email WHERE is_deleted = 'N' and id = 3) email ON email.party_id = cusomter.party_id and customer.id = 3; |ALL |N |
|subqueryInSubquery |SELECT * FROM customer c WHERE status > (SELECT status FROM customer_email WHERE party_id > (SELECT id FROM full_table) LIMIT 1); |NONE |N |
|subqueryInSubquery |SELECT * FROM customer c WHERE status > (SELECT status FROM customer_email WHERE party_id > (SELECT id FROM full_table) LIMIT 1) AND id = 3; |SINGLE |Y |
|subqueryInSubquery |SELECT * FROM customer c WHERE status > (SELECT status FROM customer_email WHERE id = 3 AND party_id > (SELECT id FROM full_table WHERE id = 3) LIMIT 1) AND id = 3; |ALL |Y |
And not support sql detail as follow:
```
ShardingProxy(5.0.0.RC1)2DB2Table
subqueryAsScalarOperand[hasShardingKey:none]; Support:false; SQL: SELECT (SELECT name FROM customer);
java.sql.SQLException: 2Unknown exception: [String index out of range: 35]
ShardingProxy(5.0.0.RC1)2DB2Table
subqueryInColumns[hasShardingKey:none]; Support:false; SQL: SELECT id AS c_id , (select status FROM customer_email ce WHERE ce.party_id = c.party_id) AS ec_status FROM customer c;
java.sql.SQLException: 2Unknown exception: [Must have sharding column with subquery.]
ShardingProxy(5.0.0.RC1)2DB2Table
subqueryWithAny[hasShardingKey:none]; Support:false; SQL: SELECT * FROM customer WHERE status = ANY (SELECT status FROM customer_email);
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: This version of ShardingProxy doesn't yet support this SQL. 'Unsupported SQL of `SELECT * FROM customer WHERE status = ANY (SELECT status FROM customer_email)`'
ShardingProxy(5.0.0.RC1)2DB2Table
subqueryWithAny[hasShardingKey:single]; Support:false; SQL: SELECT * FROM customer WHERE status = ANY (SELECT status FROM customer_email) and id = 3;
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: This version of ShardingProxy doesn't yet support this SQL. 'Unsupported SQL of `SELECT * FROM customer WHERE status = ANY (SELECT status FROM customer_email) and id = 3`'
ShardingProxy(5.0.0.RC1)2DB2Table
subqueryWithAny[hasShardingKey:all]; Support:false; SQL: SELECT * FROM customer WHERE status = ANY (SELECT status FROM customer_email where id = 3) and id = 3;
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: This version of ShardingProxy doesn't yet support this SQL. 'Unsupported SQL of `SELECT * FROM customer WHERE status = ANY (SELECT status FROM customer_email where id = 3) and id = 3`'
ShardingProxy(5.0.0.RC1)2DB2Table
subqueryWithAll[hasShardingKey:none]; Support:false; SQL: SELECT * FROM customer WHERE status > ALL (SELECT status FROM customer_email);
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: This version of ShardingProxy doesn't yet support this SQL. 'Unsupported SQL of `SELECT * FROM customer WHERE status > ALL (SELECT status FROM customer_email)`'
ShardingProxy(5.0.0.RC1)2DB2Table
subqueryWithAll[hasShardingKey:single]; Support:false; SQL: SELECT * FROM customer WHERE status > ALL (SELECT status FROM customer_email) and id = 3;
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: This version of ShardingProxy doesn't yet support this SQL. 'Unsupported SQL of `SELECT * FROM customer WHERE status > ALL (SELECT status FROM customer_email) and id = 3`'
ShardingProxy(5.0.0.RC1)2DB2Table
subqueryWithAll[hasShardingKey:all]; Support:false; SQL: SELECT * FROM customer WHERE status > ALL (SELECT status FROM customer_email where id = 3) and id = 3;
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: This version of ShardingProxy doesn't yet support this SQL. 'Unsupported SQL of `SELECT * FROM customer WHERE status > ALL (SELECT status FROM customer_email where id = 3) and id = 3`'
ShardingProxy(5.0.0.RC1)2DB2Table
subqueryAsRowSubquery[hasShardingKey:none]; Support:false; SQL: SELECT * FROM customer WHERE (party_id, status) = (SELECT party_id, status FROM customer_email WHERE party_id = 10);
java.sql.SQLException: 2Unknown exception: [Must have sharding column with subquery.]
ShardingProxy(5.0.0.RC1)2DB2Table
subqueryWithNotExist[hasShardingKey:none]; Support:false; SQL: SELECT * FROM customer where not exists (select * from customer_email where c.status = status);
ds_00|SELECT * FROM customer_0000 where not exists (select * from customer_email where c.status = status)|
ds_00|SELECT * FROM customer_0001 where not exists (select * from customer_email where c.status = status)|
ds_01|SELECT * FROM customer_0002 where not exists (select * from customer_email where c.status = status)|
ds_01|SELECT * FROM customer_0003 where not exists (select * from customer_email where c.status = status)|
ShardingProxy(5.0.0.RC1)2DB2Table
subqueryWithNotExist[hasShardingKey:single]; Support:false; SQL: SELECT * FROM customer where not exists (select * from customer_email where c.status = status) and id = 3;
ds_00|SELECT * FROM customer_0000 where not exists (select * from customer_email where c.status = status) and id = 3|
ds_00|SELECT * FROM customer_0001 where not exists (select * from customer_email where c.status = status) and id = 3|
ds_01|SELECT * FROM customer_0002 where not exists (select * from customer_email where c.status = status) and id = 3|
ds_01|SELECT * FROM customer_0003 where not exists (select * from customer_email where c.status = status) and id = 3|
ShardingProxy(5.0.0.RC1)2DB2Table
subqueryWithNotExist[hasShardingKey:all]; Support:false; SQL: SELECT * FROM customer where not exists (select * from customer_email where c.status = status and id = 3) and id = 3;
ds_00|SELECT * FROM customer_0000 where not exists (select * from customer_email where c.status = status and id = 3) and id = 3|
ds_00|SELECT * FROM customer_0001 where not exists (select * from customer_email where c.status = status and id = 3) and id = 3|
ds_01|SELECT * FROM customer_0002 where not exists (select * from customer_email where c.status = status and id = 3) and id = 3|
ds_01|SELECT * FROM customer_0003 where not exists (select * from customer_email where c.status = status and id = 3) and id = 3|
ShardingProxy(5.0.0.RC1)2DB2Table
subqueryAsCorrelatedSubquery[hasShardingKey:none]; Support:false; SQL: SELECT * FROM customer WHERE status = ANY (SELECT status FROM customer_email WHERE customer.party_id = customer_email);
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: This version of ShardingProxy doesn't yet support this SQL. 'Unsupported SQL of `SELECT * FROM customer WHERE status = ANY (SELECT status FROM customer_email WHERE customer.party_id = customer_email)`'
ShardingProxy(5.0.0.RC1)2DB2Table
subqueryAsCorrelatedSubquery[hasShardingKey:single]; Support:false; SQL: SELECT * FROM customer WHERE status = ANY (SELECT status FROM customer_email WHERE customer.party_id = customer_email) and id = 3;
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: This version of ShardingProxy doesn't yet support this SQL. 'Unsupported SQL of `SELECT * FROM customer WHERE status = ANY (SELECT status FROM customer_email WHERE customer.party_id = customer_email) and id = 3`'
ShardingProxy(5.0.0.RC1)2DB2Table
subqueryAsCorrelatedSubquery[hasShardingKey:all]; Support:false; SQL: SELECT * FROM customer WHERE status = ANY (SELECT status FROM customer_email WHERE customer.party_id = customer_email.party_id and id = 3) and id = 3;
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: This version of ShardingProxy doesn't yet support this SQL. 'Unsupported SQL of `SELECT * FROM customer WHERE status = ANY (SELECT status FROM customer_email WHERE customer.party_id = customer_email.party_id and id = 3) and id = 3`'
ShardingProxy(5.0.0.RC1)2DB2Table
subqueryInLeftJoin[hasShardingKey:none]; Support:false; SQL: SELECT * FROM customer LEFT JOIN (SELECT party_id, status FROM customer_email WHERE is_deleted = 'N') email ON email.party_id = cusomter.party_id;
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: This version of ShardingProxy doesn't yet support this SQL. 'Unsupported SQL of `SELECT * FROM customer LEFT JOIN (SELECT party_id, status FROM customer_email WHERE is_deleted = 'N') email ON email.party_id = cusomter.party_id`'
ShardingProxy(5.0.0.RC1)2DB2Table
subqueryInLeftJoin[hasShardingKey:single]; Support:false; SQL: SELECT * FROM customer LEFT JOIN (SELECT party_id, status FROM customer_email WHERE is_deleted = 'N') email ON email.party_id = cusomter.party_id and customer.id = 3;
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: This version of ShardingProxy doesn't yet support this SQL. 'Unsupported SQL of `SELECT * FROM customer LEFT JOIN (SELECT party_id, status FROM customer_email WHERE is_deleted = 'N') email ON email.party_id = cusomter.party_id and customer.id = 3`'
ShardingProxy(5.0.0.RC1)2DB2Table
subqueryInLeftJoin[hasShardingKey:all]; Support:false; SQL: SELECT * FROM customer LEFT JOIN (SELECT party_id, status FROM customer_email WHERE is_deleted = 'N' and id = 3) email ON email.party_id = cusomter.party_id and customer.id = 3;
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: This version of ShardingProxy doesn't yet support this SQL. 'Unsupported SQL of `SELECT * FROM customer LEFT JOIN (SELECT party_id, status FROM customer_email WHERE is_deleted = 'N' and id = 3) email ON email.party_id = cusomter.party_id and customer.id = 3`'
ShardingProxy(5.0.0.RC1)2DB2Table
subqueryInSubquery[hasShardingKey:none]; Support:false; SQL: SELECT * FROM customer c WHERE status > (SELECT status FROM customer_email WHERE party_id > (SELECT id FROM full_table) LIMIT 1);
java.sql.SQLException: 2Unknown exception: [Must have sharding column with subquery.]
```
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] jingshanglu commented on issue #6497: Can subqueries be supported in select？
Posted by GitBox <gi...@apache.org>.
jingshanglu commented on issue #6497:
URL: https://github.com/apache/shardingsphere/issues/6497#issuecomment-694739900
> @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.
>
> 1. error-must have sharding column in subquery ,`select count(0) from (select * from t_order where createdDate > '2020-02-01') a;`
> 2. 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;`
> 3. 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?
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] tristaZero commented on issue #6497: Can subqueries be supported in select？
Posted by GitBox <gi...@apache.org>.
tristaZero commented on issue #6497:
URL: https://github.com/apache/shardingsphere/issues/6497#issuecomment-666304948
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.
Thanks,
Trista
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] tristaZero edited a comment on issue #6497: Can subqueries be supported in select？
Posted by GitBox <gi...@apache.org>.
tristaZero edited a comment on issue #6497:
URL: https://github.com/apache/shardingsphere/issues/6497#issuecomment-666304948
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
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] lwtdev commented on issue #6497: Can subqueries be supported in select？
Posted by GitBox <gi...@apache.org>.
lwtdev commented on issue #6497:
URL: https://github.com/apache/shardingsphere/issues/6497#issuecomment-723421718
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**
```sql
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)|
```
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] lwtdev edited a comment on issue #6497: Can subqueries be supported in select？
Posted by GitBox <gi...@apache.org>.
lwtdev edited a comment on issue #6497:
URL: https://github.com/apache/shardingsphere/issues/6497#issuecomment-698708296
@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
1. Two table (`cusomter` and `cusomter_email`) both have sharding columns, but broadcast to all table.
2. 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:
```sql
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.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] Fujinzhong commented on issue #6497: Can subqueries be supported in select？
Posted by GitBox <gi...@apache.org>.
Fujinzhong commented on issue #6497:
URL: https://github.com/apache/shardingsphere/issues/6497#issuecomment-694634942
> @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 ) T
HEN 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.segmen
t.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:
# shardingjdbc config
shardingsphere:
datasource:
names: master,record
master:
jdbc-url: 'jdbc:mysql://172.16.40.217: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
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] lwtdev edited a comment on issue #6497: Can subqueries be supported in select？
Posted by GitBox <gi...@apache.org>.
lwtdev edited a comment on issue #6497:
URL: https://github.com/apache/shardingsphere/issues/6497#issuecomment-696590455
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] tristaZero commented on issue #6497: Can subqueries be supported in select？
Posted by GitBox <gi...@apache.org>.
tristaZero commented on issue #6497:
URL: https://github.com/apache/shardingsphere/issues/6497#issuecomment-682345851
@hnxphnxp Hi thanks for your attention.
I am glad to say we have support all the subquery **parsing** (#6837) and [Supported SQL](https://github.com/apache/shardingsphere/blob/master/shardingsphere-sql-parser/shardingsphere-sql-parser-test/src/main/resources/sql/supported/dml/select.xml) 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
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] tristaZero edited a comment on issue #6497: Can subqueries be supported in select？
Posted by GitBox <gi...@apache.org>.
tristaZero edited a comment on issue #6497:
URL: https://github.com/apache/shardingsphere/issues/6497#issuecomment-696661621
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
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] jingshanglu edited a comment on issue #6497: Can subqueries be supported in select？
Posted by GitBox <gi...@apache.org>.
jingshanglu edited a comment on issue #6497:
URL: https://github.com/apache/shardingsphere/issues/6497#issuecomment-698300703
@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.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] jingshanglu commented on issue #6497: Can subqueries be supported in select？
Posted by GitBox <gi...@apache.org>.
jingshanglu commented on issue #6497:
URL: https://github.com/apache/shardingsphere/issues/6497#issuecomment-698300703
@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.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] tristaZero commented on issue #6497: Can subqueries be supported in select？
Posted by GitBox <gi...@apache.org>.
tristaZero commented on issue #6497:
URL: https://github.com/apache/shardingsphere/issues/6497#issuecomment-696661621
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
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] lwtdev edited a comment on issue #6497: Can subqueries be supported in select？
Posted by GitBox <gi...@apache.org>.
lwtdev edited a comment on issue #6497:
URL: https://github.com/apache/shardingsphere/issues/6497#issuecomment-665620178
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] jingshanglu commented on issue #6497: Can subqueries be supported in select？
Posted by GitBox <gi...@apache.org>.
jingshanglu commented on issue #6497:
URL: https://github.com/apache/shardingsphere/issues/6497#issuecomment-701247498
@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?
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] lwtdev commented on issue #6497: Can subqueries be supported in select？
Posted by GitBox <gi...@apache.org>.
lwtdev commented on issue #6497:
URL: https://github.com/apache/shardingsphere/issues/6497#issuecomment-666271681
> 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 you replay， It's enough for supporing 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?
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] lwtdev commented on issue #6497: Can subqueries be supported in select？
Posted by GitBox <gi...@apache.org>.
lwtdev commented on issue #6497:
URL: https://github.com/apache/shardingsphere/issues/6497#issuecomment-706474605
> @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.
```sql
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|
```
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] lwtdev removed a comment on issue #6497: Can subqueries be supported in select？
Posted by GitBox <gi...@apache.org>.
lwtdev removed a comment on issue #6497:
URL: https://github.com/apache/shardingsphere/issues/6497#issuecomment-696584030
Hi~ @jingshanglu @tristaZero
I have test some subquery case just now
|SQL Demo Title |SQL Demo |Sharding Column |ShardingProxy(5.0.0.RC1)2DB2Table |
|---- |---- |---- |---- |
|subqueryAsScalarOperand |SELECT (SELECT name FROM customer); |NONE |N |
|subqueryAsScalarOperand |SELECT (SELECT name FROM customer where id = 3); |ALL |Y |
|subqueryInColumns |SELECT id AS c_id , (select status FROM customer_email ce WHERE ce.party_id = c.party_id) AS ec_status FROM customer c; |NONE |N |
|subqueryInColumns |SELECT id AS c_id , (select status FROM customer_email ce WHERE ce.party_id = c.party_id) AS ec_status FROM customer c WHERE id = 3; |SINGLE |Y |
|subqueryInColumns |SELECT id AS c_id , (select status FROM customer_email ce WHERE ce.party_id = c.party_id and id = 3) AS ec_status FROM customer c WHERE id = 3; |ALL |Y |
|subqueryInComparisons |SELECT * FROM customer WHERE status < (SELECT MAX(status) FROM customer_email); |NONE |Y |
|subqueryInComparisons |SELECT * FROM customer WHERE status < (SELECT MAX(status) FROM customer_email) and id = 3; |SINGLE |Y |
|subqueryInComparisons |SELECT * FROM customer WHERE status < (SELECT MAX(status) FROM customer_email where id = 3) and id = 3; |ALL |Y |
|subqueryWithAny |SELECT * FROM customer WHERE status = ANY (SELECT status FROM customer_email); |NONE |N |
|subqueryWithAny |SELECT * FROM customer WHERE status = ANY (SELECT status FROM customer_email) and id = 3; |SINGLE |N |
|subqueryWithAny |SELECT * FROM customer WHERE status = ANY (SELECT status FROM customer_email where id = 3) and id = 3; |ALL |N |
|subqueryWithAll |SELECT * FROM customer WHERE status > ALL (SELECT status FROM customer_email); |NONE |N |
|subqueryWithAll |SELECT * FROM customer WHERE status > ALL (SELECT status FROM customer_email) and id = 3; |SINGLE |N |
|subqueryWithAll |SELECT * FROM customer WHERE status > ALL (SELECT status FROM customer_email where id = 3) and id = 3; |ALL |N |
|subqueryAsRowSubquery |SELECT * FROM customer WHERE (party_id, status) = (SELECT party_id, status FROM customer_email WHERE party_id = 10); |NONE |N |
|subqueryAsRowSubquery |SELECT * FROM customer WHERE (party_id, status) = (SELECT party_id, status FROM customer_email WHERE party_id = 10) and id = 3; |SINGLE |Y |
|subqueryAsRowSubquery |SELECT * FROM customer WHERE (party_id, status) = (SELECT party_id, status FROM customer_email WHERE party_id = 10 and id = 3) and id = 3; |ALL |Y |
|subqueryWithNotExist |SELECT * FROM customer where not exists (select * from customer_email where c.status = status); |NONE |N |
|subqueryWithNotExist |SELECT * FROM customer where not exists (select * from customer_email where c.status = status) and id = 3; |SINGLE |N |
|subqueryWithNotExist |SELECT * FROM customer where not exists (select * from customer_email where c.status = status and id = 3) and id = 3; |ALL |N |
|subqueryWithIn |SELECT * FROM customer c WHERE status in (SELECT status FROM customer_email); |NONE |Y |
|subqueryWithIn |SELECT * FROM customer c WHERE status in (SELECT status FROM customer_email) AND id = 3; |SINGLE |Y |
|subqueryWithIn |SELECT * FROM customer c WHERE status in (SELECT status FROM customer_email WHERE id = 3 ) AND id = 3; |ALL |Y |
|subqueryAsCorrelatedSubquery |SELECT * FROM customer WHERE status = ANY (SELECT status FROM customer_email WHERE customer.party_id = customer_email); |NONE |N |
|subqueryAsCorrelatedSubquery |SELECT * FROM customer WHERE status = ANY (SELECT status FROM customer_email WHERE customer.party_id = customer_email) and id = 3; |SINGLE |N |
|subqueryAsCorrelatedSubquery |SELECT * FROM customer WHERE status = ANY (SELECT status FROM customer_email WHERE customer.party_id = customer_email.party_id and id = 3) and id = 3; |ALL |N |
|subqueryAsDerivedTableInFromClause |SELECT sbf1, sbf2 FROM (SELECT party_id AS sbf1, status AS sbf2 FROM customer) AS sb WHERE sbf2 > 1; |NONE |Y |
|subqueryAsDerivedTableInFromClause |SELECT sbf1, sbf2 FROM (SELECT party_id AS sbf1, status AS sbf2 FROM customer where id = 3) AS sb WHERE sbf2 > 1; |ALL |Y |
|subqueryAsDerivedTableWithCount |SELECT COUNT(*) FROM (SELECT * FROM customer) AS t; |NONE |Y |
|subqueryAsDerivedTableWithCount |SELECT COUNT(*) FROM (SELECT * FROM customer WHERE id = 3) AS t; |ALL |Y |
|subqueryInLeftJoin |SELECT * FROM customer LEFT JOIN (SELECT party_id, status FROM customer_email WHERE is_deleted = 'N') email ON email.party_id = cusomter.party_id; |NONE |N |
|subqueryInLeftJoin |SELECT * FROM customer LEFT JOIN (SELECT party_id, status FROM customer_email WHERE is_deleted = 'N') email ON email.party_id = cusomter.party_id and customer.id = 3; |SINGLE |N |
|subqueryInLeftJoin |SELECT * FROM customer LEFT JOIN (SELECT party_id, status FROM customer_email WHERE is_deleted = 'N' and id = 3) email ON email.party_id = cusomter.party_id and customer.id = 3; |ALL |N |
|subqueryInSubquery |SELECT * FROM customer c WHERE status > (SELECT status FROM customer_email WHERE party_id > (SELECT id FROM full_table) LIMIT 1); |NONE |N |
|subqueryInSubquery |SELECT * FROM customer c WHERE status > (SELECT status FROM customer_email WHERE party_id > (SELECT id FROM full_table) LIMIT 1) AND id = 3; |SINGLE |Y |
|subqueryInSubquery |SELECT * FROM customer c WHERE status > (SELECT status FROM customer_email WHERE id = 3 AND party_id > (SELECT id FROM full_table WHERE id = 3) LIMIT 1) AND id = 3; |ALL |Y |
And not support sql detail as follow:
```
ShardingProxy(5.0.0.RC1)2DB2Table
subqueryAsScalarOperand[hasShardingKey:none]; Support:false; SQL: SELECT (SELECT name FROM customer);
java.sql.SQLException: 2Unknown exception: [String index out of range: 35]
ShardingProxy(5.0.0.RC1)2DB2Table
subqueryInColumns[hasShardingKey:none]; Support:false; SQL: SELECT id AS c_id , (select status FROM customer_email ce WHERE ce.party_id = c.party_id) AS ec_status FROM customer c;
java.sql.SQLException: 2Unknown exception: [Must have sharding column with subquery.]
ShardingProxy(5.0.0.RC1)2DB2Table
subqueryWithAny[hasShardingKey:none]; Support:false; SQL: SELECT * FROM customer WHERE status = ANY (SELECT status FROM customer_email);
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: This version of ShardingProxy doesn't yet support this SQL. 'Unsupported SQL of `SELECT * FROM customer WHERE status = ANY (SELECT status FROM customer_email)`'
ShardingProxy(5.0.0.RC1)2DB2Table
subqueryWithAny[hasShardingKey:single]; Support:false; SQL: SELECT * FROM customer WHERE status = ANY (SELECT status FROM customer_email) and id = 3;
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: This version of ShardingProxy doesn't yet support this SQL. 'Unsupported SQL of `SELECT * FROM customer WHERE status = ANY (SELECT status FROM customer_email) and id = 3`'
ShardingProxy(5.0.0.RC1)2DB2Table
subqueryWithAny[hasShardingKey:all]; Support:false; SQL: SELECT * FROM customer WHERE status = ANY (SELECT status FROM customer_email where id = 3) and id = 3;
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: This version of ShardingProxy doesn't yet support this SQL. 'Unsupported SQL of `SELECT * FROM customer WHERE status = ANY (SELECT status FROM customer_email where id = 3) and id = 3`'
ShardingProxy(5.0.0.RC1)2DB2Table
subqueryWithAll[hasShardingKey:none]; Support:false; SQL: SELECT * FROM customer WHERE status > ALL (SELECT status FROM customer_email);
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: This version of ShardingProxy doesn't yet support this SQL. 'Unsupported SQL of `SELECT * FROM customer WHERE status > ALL (SELECT status FROM customer_email)`'
ShardingProxy(5.0.0.RC1)2DB2Table
subqueryWithAll[hasShardingKey:single]; Support:false; SQL: SELECT * FROM customer WHERE status > ALL (SELECT status FROM customer_email) and id = 3;
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: This version of ShardingProxy doesn't yet support this SQL. 'Unsupported SQL of `SELECT * FROM customer WHERE status > ALL (SELECT status FROM customer_email) and id = 3`'
ShardingProxy(5.0.0.RC1)2DB2Table
subqueryWithAll[hasShardingKey:all]; Support:false; SQL: SELECT * FROM customer WHERE status > ALL (SELECT status FROM customer_email where id = 3) and id = 3;
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: This version of ShardingProxy doesn't yet support this SQL. 'Unsupported SQL of `SELECT * FROM customer WHERE status > ALL (SELECT status FROM customer_email where id = 3) and id = 3`'
ShardingProxy(5.0.0.RC1)2DB2Table
subqueryAsRowSubquery[hasShardingKey:none]; Support:false; SQL: SELECT * FROM customer WHERE (party_id, status) = (SELECT party_id, status FROM customer_email WHERE party_id = 10);
java.sql.SQLException: 2Unknown exception: [Must have sharding column with subquery.]
ShardingProxy(5.0.0.RC1)2DB2Table
subqueryWithNotExist[hasShardingKey:none]; Support:false; SQL: SELECT * FROM customer where not exists (select * from customer_email where c.status = status);
ds_00|SELECT * FROM customer_0000 where not exists (select * from customer_email where c.status = status)|
ds_00|SELECT * FROM customer_0001 where not exists (select * from customer_email where c.status = status)|
ds_01|SELECT * FROM customer_0002 where not exists (select * from customer_email where c.status = status)|
ds_01|SELECT * FROM customer_0003 where not exists (select * from customer_email where c.status = status)|
ShardingProxy(5.0.0.RC1)2DB2Table
subqueryWithNotExist[hasShardingKey:single]; Support:false; SQL: SELECT * FROM customer where not exists (select * from customer_email where c.status = status) and id = 3;
ds_00|SELECT * FROM customer_0000 where not exists (select * from customer_email where c.status = status) and id = 3|
ds_00|SELECT * FROM customer_0001 where not exists (select * from customer_email where c.status = status) and id = 3|
ds_01|SELECT * FROM customer_0002 where not exists (select * from customer_email where c.status = status) and id = 3|
ds_01|SELECT * FROM customer_0003 where not exists (select * from customer_email where c.status = status) and id = 3|
ShardingProxy(5.0.0.RC1)2DB2Table
subqueryWithNotExist[hasShardingKey:all]; Support:false; SQL: SELECT * FROM customer where not exists (select * from customer_email where c.status = status and id = 3) and id = 3;
ds_00|SELECT * FROM customer_0000 where not exists (select * from customer_email where c.status = status and id = 3) and id = 3|
ds_00|SELECT * FROM customer_0001 where not exists (select * from customer_email where c.status = status and id = 3) and id = 3|
ds_01|SELECT * FROM customer_0002 where not exists (select * from customer_email where c.status = status and id = 3) and id = 3|
ds_01|SELECT * FROM customer_0003 where not exists (select * from customer_email where c.status = status and id = 3) and id = 3|
ShardingProxy(5.0.0.RC1)2DB2Table
subqueryAsCorrelatedSubquery[hasShardingKey:none]; Support:false; SQL: SELECT * FROM customer WHERE status = ANY (SELECT status FROM customer_email WHERE customer.party_id = customer_email);
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: This version of ShardingProxy doesn't yet support this SQL. 'Unsupported SQL of `SELECT * FROM customer WHERE status = ANY (SELECT status FROM customer_email WHERE customer.party_id = customer_email)`'
ShardingProxy(5.0.0.RC1)2DB2Table
subqueryAsCorrelatedSubquery[hasShardingKey:single]; Support:false; SQL: SELECT * FROM customer WHERE status = ANY (SELECT status FROM customer_email WHERE customer.party_id = customer_email) and id = 3;
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: This version of ShardingProxy doesn't yet support this SQL. 'Unsupported SQL of `SELECT * FROM customer WHERE status = ANY (SELECT status FROM customer_email WHERE customer.party_id = customer_email) and id = 3`'
ShardingProxy(5.0.0.RC1)2DB2Table
subqueryAsCorrelatedSubquery[hasShardingKey:all]; Support:false; SQL: SELECT * FROM customer WHERE status = ANY (SELECT status FROM customer_email WHERE customer.party_id = customer_email.party_id and id = 3) and id = 3;
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: This version of ShardingProxy doesn't yet support this SQL. 'Unsupported SQL of `SELECT * FROM customer WHERE status = ANY (SELECT status FROM customer_email WHERE customer.party_id = customer_email.party_id and id = 3) and id = 3`'
ShardingProxy(5.0.0.RC1)2DB2Table
subqueryInLeftJoin[hasShardingKey:none]; Support:false; SQL: SELECT * FROM customer LEFT JOIN (SELECT party_id, status FROM customer_email WHERE is_deleted = 'N') email ON email.party_id = cusomter.party_id;
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: This version of ShardingProxy doesn't yet support this SQL. 'Unsupported SQL of `SELECT * FROM customer LEFT JOIN (SELECT party_id, status FROM customer_email WHERE is_deleted = 'N') email ON email.party_id = cusomter.party_id`'
ShardingProxy(5.0.0.RC1)2DB2Table
subqueryInLeftJoin[hasShardingKey:single]; Support:false; SQL: SELECT * FROM customer LEFT JOIN (SELECT party_id, status FROM customer_email WHERE is_deleted = 'N') email ON email.party_id = cusomter.party_id and customer.id = 3;
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: This version of ShardingProxy doesn't yet support this SQL. 'Unsupported SQL of `SELECT * FROM customer LEFT JOIN (SELECT party_id, status FROM customer_email WHERE is_deleted = 'N') email ON email.party_id = cusomter.party_id and customer.id = 3`'
ShardingProxy(5.0.0.RC1)2DB2Table
subqueryInLeftJoin[hasShardingKey:all]; Support:false; SQL: SELECT * FROM customer LEFT JOIN (SELECT party_id, status FROM customer_email WHERE is_deleted = 'N' and id = 3) email ON email.party_id = cusomter.party_id and customer.id = 3;
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: This version of ShardingProxy doesn't yet support this SQL. 'Unsupported SQL of `SELECT * FROM customer LEFT JOIN (SELECT party_id, status FROM customer_email WHERE is_deleted = 'N' and id = 3) email ON email.party_id = cusomter.party_id and customer.id = 3`'
ShardingProxy(5.0.0.RC1)2DB2Table
subqueryInSubquery[hasShardingKey:none]; Support:false; SQL: SELECT * FROM customer c WHERE status > (SELECT status FROM customer_email WHERE party_id > (SELECT id FROM full_table) LIMIT 1);
java.sql.SQLException: 2Unknown exception: [Must have sharding column with subquery.]
```
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] coderJL commented on issue #6497: Can subqueries be supported in select？
Posted by GitBox <gi...@apache.org>.
coderJL commented on issue #6497:
URL: https://github.com/apache/shardingsphere/issues/6497#issuecomment-706844143
Look forward to the release of this feature
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] jingshanglu commented on issue #6497: Can subqueries be supported in select？
Posted by GitBox <gi...@apache.org>.
jingshanglu commented on issue #6497:
URL: https://github.com/apache/shardingsphere/issues/6497#issuecomment-705928545
@lwtdev `is_deleted` is not a sharding column,so, the exception will be reported.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] lwtdev edited a comment on issue #6497: Can subqueries be supported in select？
Posted by GitBox <gi...@apache.org>.
lwtdev edited a comment on issue #6497:
URL: https://github.com/apache/shardingsphere/issues/6497#issuecomment-698708296
@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
1. Two table (`cusomter` and `cusomter_email`) both have sharding columns, but broadcast to all table.
2. 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:
```sql
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.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] jingshanglu closed issue #6497: Can subqueries be supported in select？
Posted by GitBox <gi...@apache.org>.
jingshanglu closed issue #6497:
URL: https://github.com/apache/shardingsphere/issues/6497
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] tristaZero commented on issue #6497: Can subqueries be supported in select？
Posted by GitBox <gi...@apache.org>.
tristaZero commented on issue #6497:
URL: https://github.com/apache/shardingsphere/issues/6497#issuecomment-723734860
@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.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org