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:
[email protected]
