mirror of
https://github.com/MariaDB/server.git
synced 2025-11-21 04:59:40 +01:00
135 lines
4.7 KiB
Text
135 lines
4.7 KiB
Text
show create table seq_1_to_15_step_2;
|
|
Table Create Table
|
|
seq_1_to_15_step_2 CREATE TABLE `seq_1_to_15_step_2` (
|
|
`seq` bigint(20) unsigned NOT NULL,
|
|
PRIMARY KEY (`seq`)
|
|
) ENGINE=SEQUENCE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
|
|
select count(seq),sum(seq),1 from seq_1_to_15_step_2;
|
|
count(seq) sum(seq) 1
|
|
8 64 1
|
|
#
|
|
# The engine should be able to optimize the following requests
|
|
#
|
|
select count(*) from seq_1_to_15_step_2;
|
|
count(*)
|
|
8
|
|
explain select count(*) from seq_1_to_15_step_2;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Storage engine handles GROUP BY
|
|
select count(seq) from seq_1_to_15_step_2;
|
|
count(seq)
|
|
8
|
|
explain select count(seq) from seq_1_to_15_step_2;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Storage engine handles GROUP BY
|
|
select sum(seq) from seq_1_to_15_step_2;
|
|
sum(seq)
|
|
64
|
|
explain select sum(seq) from seq_1_to_15_step_2;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Storage engine handles GROUP BY
|
|
select count(seq),sum(seq) from seq_1_to_15_step_2;
|
|
count(seq) sum(seq)
|
|
8 64
|
|
explain select count(seq),sum(seq) from seq_1_to_15_step_2;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Storage engine handles GROUP BY
|
|
select count(seq) as c from seq_1_to_15_step_2 having c > 5;
|
|
c
|
|
8
|
|
explain select count(seq) as c from seq_1_to_15_step_2 having c > 5;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Storage engine handles GROUP BY
|
|
select count(seq) as c from seq_1_to_15_step_2 having c > 1000;
|
|
c
|
|
explain select count(seq) as c from seq_1_to_15_step_2 having c > 1000;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Storage engine handles GROUP BY
|
|
select distinct count(*) from seq_1_to_15_step_2;
|
|
count(*)
|
|
8
|
|
explain select distinct count(*) from seq_1_to_15_step_2;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Storage engine handles GROUP BY
|
|
select * from seq_1_to_15_step_2, (select count(*) from seq_1_to_15_step_2) as t1;
|
|
seq count(*)
|
|
1 8
|
|
3 8
|
|
5 8
|
|
7 8
|
|
9 8
|
|
11 8
|
|
13 8
|
|
15 8
|
|
explain select * from seq_1_to_15_step_2, (select count(*) from seq_1_to_15_step_2) as t1;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 PRIMARY seq_1_to_15_step_2 index NULL PRIMARY 8 NULL # Using index
|
|
1 PRIMARY <derived2> ALL NULL NULL NULL NULL # Using join buffer (flat, BNL join)
|
|
2 DERIVED NULL NULL NULL NULL NULL NULL # Storage engine handles GROUP BY
|
|
create view v1 as select count(*) from seq_1_to_15_step_2;
|
|
select * from v1;
|
|
count(*)
|
|
8
|
|
drop view v1;
|
|
#
|
|
# The engine can't optimize the following queries
|
|
#
|
|
select count(seq),sum(seq),1 from seq_1_to_15_step_2;
|
|
count(seq) sum(seq) 1
|
|
8 64 1
|
|
explain select count(seq),sum(seq),1 from seq_1_to_15_step_2;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE seq_1_to_15_step_2 index NULL PRIMARY 8 NULL 8 Using index
|
|
explain select count(*) from seq_1_to_15_step_2, seq_1_to_15_step_2 as t2;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE seq_1_to_15_step_2 index NULL PRIMARY 8 NULL 8 Using index
|
|
1 SIMPLE t2 index NULL PRIMARY 8 NULL 8 Using index; Using join buffer (flat, BNL join)
|
|
explain select count(*) from seq_1_to_15_step_2 where seq > 0;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE seq_1_to_15_step_2 range PRIMARY PRIMARY 8 NULL 8 Using where; Using index
|
|
explain select count(*) from seq_1_to_15_step_2 group by mod(seq,2);
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE seq_1_to_15_step_2 index NULL PRIMARY 8 NULL 8 Using index; Using temporary; Using filesort
|
|
create temporary table t1 select * from seq_1_to_3;
|
|
select count(NULL) from t1;
|
|
count(NULL)
|
|
0
|
|
select count(NULL) from seq_1_to_3;
|
|
count(NULL)
|
|
0
|
|
#
|
|
# MDEV-20753: Sequence with limit 0 crashes server
|
|
#
|
|
select count(NULL) from seq_1_to_3 limit 0;
|
|
count(NULL)
|
|
# End of 10.3 tests
|
|
#
|
|
# MDEV-16327: Server doesn't account for engines that supports
|
|
# OFFSET on their own.
|
|
#
|
|
select count(NULL) from seq_1_to_3 limit 1;
|
|
count(NULL)
|
|
0
|
|
explain format=json select count(NULL) from seq_1_to_3 limit 1;
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"table": {
|
|
"message": "Storage engine handles GROUP BY"
|
|
}
|
|
}
|
|
}
|
|
select count(NULL) from seq_1_to_3 limit 1 offset 1;
|
|
count(NULL)
|
|
explain format=json select count(NULL) from seq_1_to_3 limit 1 offset 1;
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"table": {
|
|
"message": "Storage engine handles GROUP BY"
|
|
}
|
|
}
|
|
}
|
|
# End of 10.5 tests
|