mirror/mariadb
1
0
Fork 0
mirror of https://github.com/MariaDB/server.git synced 2025-04-17 04:35:34 +02:00
Code Issues Projects Releases Packages Wiki Activity Actions 1
mariadb/mysql-test/main/desc_index_range.test
Yuchen Pei c17f0252bc MDEV-32732 Support DESC indexes in loose scan optimization 
Extend loose index scan to support descending indexes.

This is achieved by removing a block skipping creating loose index
scan plan for descending index, as well as generalising the execution
of such plans.

The generalisation applies to all levels looking for min/max in loose
index scan. In the highest level (get_next), generalise min and max to
first and last, so that it still proceeds in the direction agreeing
with the index parity. In the lower levels, combine next_min and
next_max methods into next_min_max, and combine next_min_in_range and
next_max_in_range into next_min_max_in_range. This retains existing
logic of these functions and reduces code duplication, while allowing
handling of all four combinations (min, max) x (asc index, desc
index).
2025-03-18 18:28:18 +01:00

397 lines
10 KiB
Text
Raw Permalink Blame History

#
# Tests for range access and descending indexes
#
--source include/have_sequence.inc
--source include/have_innodb.inc
# The test uses optimizer trace:
--source include/not_embedded.inc
create table t1 (
a int,
key (a desc)
);
insert into t1 select seq from seq_1_to_1000;
set optimizer_trace=1;
explain select * from t1 force index(a) where a in (2, 4, 6);
#Enable after fix MDEV-32034
--disable_view_protocol
select json_detailed(json_extract(trace, '$**.range_access_plan.ranges')) as jd
from information_schema.optimizer_trace;
set optimizer_trace=default;
--enable_view_protocol
--echo # These should go in reverse order:
select * from t1 force index(a) where a in (2, 4, 6);
drop table t1;
--echo #
--echo # Multi-part key tests
--echo #
create table t1 (
a int not null,
b int not null,
key ab(a, b desc)
);
insert into t1 select A.seq, B.seq*10 from seq_1_to_10 A, seq_1_to_10 B;
set optimizer_trace=1;
explain select * from t1 force index(ab) where a>=8 and b>=50;
#Enable after fix MDEV-32034
--disable_view_protocol
select json_detailed(json_extract(trace, '$**.range_access_plan.ranges')) as jd
from information_schema.optimizer_trace;
--enable_view_protocol
explain select * from t1 force index(ab) where a>=8 and b<=50;
#Enable after fix MDEV-32034
--disable_view_protocol
select json_detailed(json_extract(trace, '$**.range_access_plan.ranges')) as jd
from information_schema.optimizer_trace;
--enable_view_protocol
select * from t1 force index(ab) where a>=8 and b<=50;
select * from t1 ignore index(ab) where a>=8 and b<=50 order by a, b desc;
explain
select * from t1 where a between 2 and 4 and b between 50 and 80;
#Enable after fix MDEV-32034
--disable_view_protocol
select json_detailed(json_extract(trace, '$**.range_access_plan.ranges')) as jd
from information_schema.optimizer_trace;
--enable_view_protocol
select * from t1 where a between 2 and 4 and b between 50 and 80;
drop table t1;
create table t2 (
a int not null,
b int not null,
key ab(a desc, b desc)
);
insert into t2 select A.seq, B.seq*10 from seq_1_to_10 A, seq_1_to_10 B;
explain
select * from t2 where a between 2 and 4;
#Enable after fix MDEV-32034
--disable_view_protocol
select json_detailed(json_extract(trace, '$**.range_access_plan.ranges')) as jd
from information_schema.optimizer_trace;
--enable_view_protocol
explain
select * from t2 where a between 2 and 4 and b between 50 and 80;
#Enable after fix MDEV-32034
--disable_view_protocol
select json_detailed(json_extract(trace, '$**.range_access_plan.ranges')) as jd
from information_schema.optimizer_trace;
--enable_view_protocol
drop table t2;
--echo #
--echo # Check that "Using index for group-by" is disabled (it's not supported, yet)
--echo #
CREATE TABLE t1 (p int NOT NULL, a int NOT NULL, PRIMARY KEY (p,a desc));
insert into t1 select 2,seq from seq_0_to_1000;
EXPLAIN select MIN(a) from t1 where p = 2 group by p;
#Enable after fix MDEV-32034
--disable_view_protocol
select json_detailed(json_extract(trace, '$**.potential_group_range_indexes')) as jd
from information_schema.optimizer_trace;
--enable_view_protocol
drop table t1;
set optimizer_trace=default;
--echo #
--echo # MDEV-27426: Wrong result upon query using index_merge with DESC key
--echo #
CREATE TABLE t1 (pk INT, a INT, b int, KEY(a), PRIMARY KEY(pk DESC))
ENGINE=InnoDB;
INSERT INTO t1 VALUES (1,4,5),(2,9,6),(3,NULL,7),(4,NULL,8);
SELECT * FROM t1 WHERE pk > 10 OR a > 0;
DROP TABLE t1;
--echo #
--echo # MDEV-27529: Wrong result upon query using index_merge with DESC key (#2)
--echo #
create table t1 (
pk int,
a int,
b int,
primary key(pk desc),
key(a),
key(b)
) engine=innodb;
insert into t1 values (0, 111111, 255);
insert into t1 select seq+50000, NULL, seq+1000 from seq_1_to_260;
insert into t1 values (10000, NULL, 255);
insert into t1 select seq+20000, seq+20000, seq+20000 from seq_1_to_1500;
analyze table t1;
--echo # Must use ROR-intersect:
explain select * from t1 where b = 255 AND a IS NULL;
select * from t1 where b = 255 AND a IS NULL;
drop table t1;
--echo #
--echo # MDEV-32732 Support DESC indexes in loose scan optimization
--echo #
create table t1 (a int, c int, key (a, c desc));
insert into t1 values (1, 9), (1, 6), (1, 3);
let $query=
SELECT MAX(c), MIN(c) FROM t1 group by a;
eval explain $query;
eval $query;
drop table t1;
create table t1 (a int, b int, c int, key (a desc, b, c desc));
insert into t1 values (1, 2, 9), (1, 2, 6), (2, 1, 3), (2, 1, 12);
let $query=
SELECT a, b, MAX(c), MIN(c) FROM t1 group by a, b;
eval explain $query;
eval $query;
drop table t1;
# innodb is more cost "sensitive" here somehow, and could result in
# not choosing loose index scan without
# set debug="+d,force_group_by":
# --source include/have_innodb.inc
# CREATE TABLE t1 (a int, b int, KEY (a, b desc)) engine=innodb;
CREATE TABLE t1 (a int, b int, KEY (a, b desc));
insert into t1 values (1, 23), (3, 45), (1, 11), (3, 88), (3, 70), (4, NULL), (1, 14), (4, NULL);
let $query=
SELECT MIN(b) FROM t1 GROUP BY a;
eval EXPLAIN $query;
eval $query;
let $query=
SELECT MIN(b) FROM t1 WHERE b < 68 GROUP BY a;
eval EXPLAIN $query;
eval $query;
let $query=
SELECT MIN(b) FROM t1 WHERE b > 13 GROUP BY a;
eval EXPLAIN $query;
eval $query;
let $query=
SELECT MIN(b) FROM t1 WHERE b > 13 AND b < 68 GROUP BY a;
eval EXPLAIN $query;
eval $query;
let $query=
SELECT MIN(b) FROM t1 WHERE b >= 14 AND b < 68 GROUP BY a;
eval EXPLAIN $query;
eval $query;
let $query=
SELECT MIN(b) FROM t1 WHERE b > 14 AND b < 68 GROUP BY a;
eval EXPLAIN $query;
eval $query;
let $query=
SELECT MIN(b) FROM t1 WHERE (b > 47 AND b < 91) OR (b > 11 AND b < 30) GROUP BY a;
eval EXPLAIN $query;
eval $query;
let $query=
SELECT MIN(b) FROM t1 WHERE (b > 13 AND b < 68) OR (b IS NULL) GROUP BY a;
eval EXPLAIN $query;
eval $query;
let $query=
SELECT MIN(b) FROM t1 WHERE (b > 13 AND b < 68) OR (b = 100) GROUP BY a;
eval EXPLAIN $query;
eval $query;
let $query=
SELECT MAX(b) FROM t1 GROUP BY a;
eval EXPLAIN $query;
eval $query;
let $query=
SELECT MAX(b) FROM t1 WHERE b < 68 GROUP BY a;
eval EXPLAIN $query;
eval $query;
let $query=
SELECT MAX(b) FROM t1 WHERE b > 13 GROUP BY a;
eval EXPLAIN $query;
eval $query;
let $query=
SELECT MAX(b) FROM t1 WHERE b > 13 AND b < 68 GROUP BY a;
eval EXPLAIN $query;
eval $query;
let $query=
SELECT MAX(b) FROM t1 WHERE b >= 14 AND b < 68 GROUP BY a;
eval EXPLAIN $query;
eval $query;
let $query=
SELECT MAX(b) FROM t1 WHERE (b > 47 AND b < 91) OR (b > 11 AND b < 30) GROUP BY a;
eval EXPLAIN $query;
eval $query;
let $query=
SELECT MAX(b) FROM t1 WHERE (b > 13 AND b < 68) OR (b IS NULL) GROUP BY a;
eval EXPLAIN $query;
eval $query;
let $query=
SELECT MAX(b) FROM t1 WHERE (b > 13 AND b < 68) OR (b = 100) GROUP BY a;
eval EXPLAIN $query;
eval $query;
insert into t1 values (4, 8);
let $query=
SELECT MIN(b) FROM t1 GROUP BY a;
eval EXPLAIN $query;
eval $query;
let $query=
SELECT MIN(b) FROM t1 WHERE b < 68 GROUP BY a;
eval EXPLAIN $query;
eval $query;
let $query=
SELECT MIN(b) FROM t1 WHERE b > 13 GROUP BY a;
eval EXPLAIN $query;
eval $query;
let $query=
SELECT MIN(b) FROM t1 WHERE b > 13 AND b < 68 GROUP BY a;
eval EXPLAIN $query;
eval $query;
let $query=
SELECT MIN(b) FROM t1 WHERE b >= 14 AND b < 68 GROUP BY a;
eval EXPLAIN $query;
eval $query;
let $query=
SELECT MIN(b) FROM t1 WHERE (b > 47 AND b < 91) OR (b > 11 AND b < 30) GROUP BY a;
eval EXPLAIN $query;
eval $query;
let $query=
SELECT MIN(b) FROM t1 WHERE (b > 13 AND b < 68) OR (b IS NULL) GROUP BY a;
eval EXPLAIN $query;
eval $query;
let $query=
SELECT MIN(b) FROM t1 WHERE (b > 13 AND b < 68) OR (b = 100) GROUP BY a;
eval EXPLAIN $query;
eval $query;
let $query=
SELECT MAX(b) FROM t1 GROUP BY a;
eval EXPLAIN $query;
eval $query;
let $query=
SELECT MAX(b) FROM t1 WHERE b < 68 GROUP BY a;
eval EXPLAIN $query;
eval $query;
let $query=
SELECT MAX(b) FROM t1 WHERE b > 13 GROUP BY a;
eval EXPLAIN $query;
eval $query;
let $query=
SELECT MAX(b) FROM t1 WHERE b > 13 AND b < 68 GROUP BY a;
eval EXPLAIN $query;
eval $query;
let $query=
SELECT MAX(b) FROM t1 WHERE b >= 14 AND b < 68 GROUP BY a;
eval EXPLAIN $query;
eval $query;
let $query=
SELECT MAX(b) FROM t1 WHERE (b > 47 AND b < 91) OR (b > 11 AND b < 30) GROUP BY a;
eval EXPLAIN $query;
eval $query;
let $query=
SELECT MAX(b) FROM t1 WHERE (b > 13 AND b < 68) OR (b IS NULL) GROUP BY a;
eval EXPLAIN $query;
eval $query;
let $query=
SELECT MAX(b) FROM t1 WHERE (b > 13 AND b < 68) OR (b = 100) GROUP BY a;
eval EXPLAIN $query;
eval $query;
drop table t1;
create table t1(c1 int, c2 int, c3 int, c4 int, key (c1 desc, c2 desc, c3 desc));
insert into t1 values (1, 2, 9, 2), (3, 2, 6, 9), (1, 2, 6, 9), (4, 1, 3, 38), (4, 1, 12, 12);
let $query=
select distinct c1 from t1;
eval EXPLAIN $query;
eval $query;
let $query=
select distinct c1 from t1 where c2 = 2;
eval EXPLAIN $query;
eval $query;
let $query=
select c1 from t1 group by c1;
eval EXPLAIN $query;
eval $query;
let $query=
select distinct c1, c2 from t1;
eval EXPLAIN $query;
eval $query;
let $query=
select c1, c2 from t1 group by c1, c2;
eval EXPLAIN $query;
eval $query;
let $query=
SELECT c1, MIN(c2) FROM t1 GROUP BY c1;
eval EXPLAIN $query;
eval $query;
let $query=
SELECT c1, c2, min(c3), max(c3) FROM t1 WHERE c2 = 2 GROUP BY c1;
eval EXPLAIN $query;
eval $query;
let $query=
SELECT c1, c2 FROM t1 WHERE c1 < 3 GROUP BY c1, c2;
eval EXPLAIN $query;
eval $query;
let $query=
SELECT MAX(c3), MIN(c3), c1, c2 FROM t1 WHERE c2 > 1 GROUP BY c1, c2;
eval EXPLAIN $query;
eval $query;
let $query=
SELECT c2 FROM t1 WHERE c1 < 3 GROUP BY c1, c2;
eval EXPLAIN $query;
eval $query;
let $query=
SELECT c1, c2 FROM t1 WHERE c3 = 6 GROUP BY c1, c2;
eval EXPLAIN $query;
eval $query;
let $query=
SELECT c1, c3 FROM t1 WHERE c3 = 6 GROUP BY c1, c2;
eval EXPLAIN $query;
eval $query;
insert into t1 values (1, 3, 3, 4);
let $query=
SELECT c1, c2, min(c3), max(c3) FROM t1 WHERE c2 = 2 GROUP BY c1;
eval EXPLAIN $query;
eval $query;
insert into t1 values (1, 4, NULL, 4);
let $query=
SELECT c1, c2, min(c3), max(c3) FROM t1 WHERE c2 = 2 GROUP BY c1;
eval EXPLAIN $query;
eval $query;
drop table t1;
create table t20 (
kp1 int,
kp2 int,
index (kp1 desc, kp2 desc)
);
insert into t20 select A.seq, B.seq from seq_1_to_10 A, seq_1_to_10 B;
insert into t20 values (1, NULL);
insert into t20 values (10, NULL);
let $query=
select min(kp2) from t20 where kp2=3 or kp2=5 or kp2 is null group by kp1;
eval EXPLAIN $query;
eval $query;
drop table t20;
Reference in a new issue View git blame Copy permalink