diff options
| author | Sergei Golubchik <serg@mariadb.org> | 2021-11-24 16:50:21 +0100 |
|---|---|---|
| committer | Sergei Golubchik <serg@mariadb.org> | 2021-12-11 17:25:28 +0100 |
| commit | db4628bf97acd253ff6f137206181dcfd6ee94f9 (patch) | |
| tree | 292ae0087672d3abb26fe5e00aeed9fd07b3d524 | |
| parent | 52a9d82ecb5b14b89dc47549c45b88e6f07a53bb (diff) | |
| download | mariadb-git-preview-10.8-MDEV-26938-desc-indexes.tar.gz | |
MDEV-26938 Support descending indexes internally in InnoDB (server part)preview-10.8-MDEV-26938-desc-indexes
* preserve DESC index property in the parser
* store it in the frm (only for HA_KEY_ALG_BTREE)
* read it from the frm
* show it in SHOW CREATE
* skip DESC indexes in opt_range.cc and opt_sum.cc
* ORDER BY test
26 files changed, 227 insertions, 80 deletions
diff --git a/mysql-test/main/func_group.result b/mysql-test/main/func_group.result index 177f0950a77..aecfb35d284 100644 --- a/mysql-test/main/func_group.result +++ b/mysql-test/main/func_group.result @@ -318,7 +318,7 @@ a3 char(3), a4 real, a5 date, key k1(a2,a3), -key k2(a4 desc,a1), +key k2(a4 /*desc*/,a1), key k3(a5,a1) ); create table t2( diff --git a/mysql-test/main/func_group.test b/mysql-test/main/func_group.test index 2d26861c710..8ff3689f452 100644 --- a/mysql-test/main/func_group.test +++ b/mysql-test/main/func_group.test @@ -206,7 +206,7 @@ create table t1( a4 real, a5 date, key k1(a2,a3), - key k2(a4 desc,a1), + key k2(a4 /*desc*/,a1), key k3(a5,a1) ); create table t2( diff --git a/mysql-test/main/key.result b/mysql-test/main/key.result index 5d84068d8af..ff35288a6ea 100644 --- a/mysql-test/main/key.result +++ b/mysql-test/main/key.result @@ -1,4 +1,3 @@ -drop table if exists t1,t2,t3; SET SQL_WARNINGS=1; CREATE TABLE t1 ( ID CHAR(32) NOT NULL, @@ -685,3 +684,8 @@ c c 9 10 10 11 drop table t1,t2; +# +# MDEV-13756 Implement descending index: KEY (a DESC, b ASC) +# +create table t1 (a int, b int, key(a), key(a desc)); +drop table t1; diff --git a/mysql-test/main/key.test b/mysql-test/main/key.test index 4e3e02c8add..c95fc017a43 100644 --- a/mysql-test/main/key.test +++ b/mysql-test/main/key.test @@ -1,6 +1,3 @@ ---disable_warnings -drop table if exists t1,t2,t3; ---enable_warnings --source include/have_sequence.inc SET SQL_WARNINGS=1; @@ -603,3 +600,9 @@ EXPLAIN SELECT t1.c, t2.c FROM t1, t2 WHERE t1.b=t2.a and t1.c=t2.b; SELECT t1.c, t2.c FROM t1, t2 WHERE t1.b=t2.a and t1.c=t2.b; drop table t1,t2; + +--echo # +--echo # MDEV-13756 Implement descending index: KEY (a DESC, b ASC) +--echo # +create table t1 (a int, b int, key(a), key(a desc)); +drop table t1; diff --git a/mysql-test/main/opt_trace.result b/mysql-test/main/opt_trace.result index f7f5476ab23..4913aac6c30 100644 --- a/mysql-test/main/opt_trace.result +++ b/mysql-test/main/opt_trace.result @@ -1203,8 +1203,8 @@ EXPLAIN SELECT DISTINCT a FROM t1 { }, { "index": "a", - "usable": true, - "key_parts": ["a"] + "key_parts": ["a"], + "usable": true } ], "best_covering_index_scan": { @@ -1386,8 +1386,8 @@ EXPLAIN SELECT MIN(d) FROM t1 where b=2 and c=3 group by a { "potential_range_indexes": [ { "index": "a", - "usable": true, - "key_parts": ["a", "b", "c", "d"] + "key_parts": ["a", "b", "c", "d"], + "usable": true } ], "best_covering_index_scan": { @@ -1585,8 +1585,8 @@ EXPLAIN SELECT id,MIN(a),MAX(a) FROM t1 WHERE a>=20010104e0 GROUP BY id { "potential_range_indexes": [ { "index": "id", - "usable": true, - "key_parts": ["id", "a"] + "key_parts": ["id", "a"], + "usable": true } ], "best_covering_index_scan": { @@ -1773,8 +1773,8 @@ EXPLAIN SELECT * FROM t1 WHERE a = 20010104e0 GROUP BY id { "potential_range_indexes": [ { "index": "id", - "usable": true, - "key_parts": ["id", "a"] + "key_parts": ["id", "a"], + "usable": true } ], "best_covering_index_scan": { @@ -2012,13 +2012,13 @@ explain select * from t1 where a=1 and b=2 order by c limit 1 { }, { "index": "a_c", - "usable": true, - "key_parts": ["a", "c"] + "key_parts": ["a", "c"], + "usable": true }, { "index": "a_b", - "usable": true, - "key_parts": ["a", "b"] + "key_parts": ["a", "b"], + "usable": true } ], "setup_range_conditions": [], @@ -2215,8 +2215,8 @@ explain select * from t1 where a=1 and b=2 order by c limit 1 { }, { "index": "a_c", - "usable": true, - "key_parts": ["a", "c"] + "key_parts": ["a", "c"], + "usable": true }, { "index": "a_b", @@ -3231,18 +3231,18 @@ explain select * from t1 where pk = 2 and a=5 and b=1 { "potential_range_indexes": [ { "index": "pk", - "usable": true, - "key_parts": ["pk"] + "key_parts": ["pk"], + "usable": true }, { "index": "pk_a", - "usable": true, - "key_parts": ["pk", "a"] + "key_parts": ["pk", "a"], + "usable": true }, { "index": "pk_a_b", - "usable": true, - "key_parts": ["pk", "a", "b"] + "key_parts": ["pk", "a", "b"], + "usable": true } ], "best_covering_index_scan": { @@ -3749,8 +3749,8 @@ explain delete from t0 where t0.a<3 { "potential_range_indexes": [ { "index": "a", - "usable": true, - "key_parts": ["a"] + "key_parts": ["a"], + "usable": true } ], "setup_range_conditions": [], @@ -3887,8 +3887,8 @@ explain delete t0,t1 from t0, t1 where t0.a=t1.a and t1.a<3 { "potential_range_indexes": [ { "index": "a", - "usable": true, - "key_parts": ["a"] + "key_parts": ["a"], + "usable": true } ], "best_covering_index_scan": { @@ -3952,8 +3952,8 @@ explain delete t0,t1 from t0, t1 where t0.a=t1.a and t1.a<3 { "potential_range_indexes": [ { "index": "a", - "usable": true, - "key_parts": ["a"] + "key_parts": ["a"], + "usable": true } ], "best_covering_index_scan": { diff --git a/mysql-test/main/opt_trace_index_merge.result b/mysql-test/main/opt_trace_index_merge.result index f1e13586eda..011875762d1 100644 --- a/mysql-test/main/opt_trace_index_merge.result +++ b/mysql-test/main/opt_trace_index_merge.result @@ -78,13 +78,13 @@ explain select * from t1 where a=1 or b=1 { "potential_range_indexes": [ { "index": "a", - "usable": true, - "key_parts": ["a"] + "key_parts": ["a"], + "usable": true }, { "index": "b", - "usable": true, - "key_parts": ["b"] + "key_parts": ["b"], + "usable": true }, { "index": "c", diff --git a/mysql-test/main/opt_trace_index_merge_innodb.result b/mysql-test/main/opt_trace_index_merge_innodb.result index 0ddaaeae89d..d372be85bd8 100644 --- a/mysql-test/main/opt_trace_index_merge_innodb.result +++ b/mysql-test/main/opt_trace_index_merge_innodb.result @@ -93,13 +93,13 @@ explain select * from t1 where pk1 != 0 and key1 = 1 { "potential_range_indexes": [ { "index": "PRIMARY", - "usable": true, - "key_parts": ["pk1", "pk2"] + "key_parts": ["pk1", "pk2"], + "usable": true }, { "index": "key1", - "usable": true, - "key_parts": ["key1"] + "key_parts": ["key1"], + "usable": true }, { "index": "key2", diff --git a/mysql-test/main/order_by.result b/mysql-test/main/order_by.result index 81f173e764d..17234eecc58 100644 --- a/mysql-test/main/order_by.result +++ b/mysql-test/main/order_by.result @@ -1,5 +1,4 @@ call mtr.add_suppression("Sort aborted.*"); -drop table if exists t1,t2,t3; call mtr.add_suppression("Out of sort memory; increase server sort buffer size"); CREATE TABLE t1 ( id int(6) DEFAULT '0' NOT NULL, @@ -4487,3 +4486,38 @@ a group_concat(t1.b) 58 1 DROP TABLE t1, t2; # End of 10.6 tests +# +# MDEV-13756 Implement descending index: KEY (a DESC, b ASC) +# +create table t1 (a int, b int, c int, key r (a desc, b asc)); +insert t1 select seq % 10, seq div 10, seq from seq_1_to_55; +insert t1 values (NULL, NULL, NULL), (9, NULL, NULL); +explain select * from t1 force index(r) order by a,b limit 20; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 57 Using filesort +explain select * from t1 force index(r) order by a desc,b limit 20; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index NULL r 10 NULL 20 +select * from t1 force index(r) order by a desc,b limit 20; +a b c +9 NULL NULL +9 0 9 +9 1 19 +9 2 29 +9 3 39 +9 4 49 +8 0 8 +8 1 18 +8 2 28 +8 3 38 +8 4 48 +7 0 7 +7 1 17 +7 2 27 +7 3 37 +7 4 47 +6 0 6 +6 1 16 +6 2 26 +6 3 36 +drop table t1; diff --git a/mysql-test/main/order_by.test b/mysql-test/main/order_by.test index 9ad0af1d21f..6944a282e4c 100644 --- a/mysql-test/main/order_by.test +++ b/mysql-test/main/order_by.test @@ -3,11 +3,6 @@ # call mtr.add_suppression("Sort aborted.*"); - ---disable_warnings -drop table if exists t1,t2,t3; ---enable_warnings - call mtr.add_suppression("Out of sort memory; increase server sort buffer size"); --source include/have_sequence.inc @@ -15,8 +10,6 @@ call mtr.add_suppression("Out of sort memory; increase server sort buffer size") # Test old ORDER BY bug # ---source include/have_sequence.inc - CREATE TABLE t1 ( id int(6) DEFAULT '0' NOT NULL, idservice int(5), @@ -2649,3 +2642,14 @@ eval $query; DROP TABLE t1, t2; --echo # End of 10.6 tests + +--echo # +--echo # MDEV-13756 Implement descending index: KEY (a DESC, b ASC) +--echo # +create table t1 (a int, b int, c int, key r (a desc, b asc)); +insert t1 select seq % 10, seq div 10, seq from seq_1_to_55; +insert t1 values (NULL, NULL, NULL), (9, NULL, NULL); +explain select * from t1 force index(r) order by a,b limit 20; +explain select * from t1 force index(r) order by a desc,b limit 20; + select * from t1 force index(r) order by a desc,b limit 20; +drop table t1; diff --git a/mysql-test/main/order_by_innodb.result b/mysql-test/main/order_by_innodb.result index 14b9b861a14..7083f04f7c4 100644 --- a/mysql-test/main/order_by_innodb.result +++ b/mysql-test/main/order_by_innodb.result @@ -200,3 +200,38 @@ id id 3 3 drop table t1,t2; # End of 10.2 tests +# +# MDEV-26938 Support descending indexes internally in InnoDB +# +create table t1 (a int, b int, c int, key r (a desc, b asc)); +insert t1 select seq % 10, seq div 10, seq from seq_1_to_55; +insert t1 values (NULL, NULL, NULL), (9, NULL, NULL); +explain select * from t1 force index(r) order by a,b limit 20; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 57 Using filesort +explain select * from t1 force index(r) order by a desc,b limit 20; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index NULL r 10 NULL 20 +select * from t1 force index(r) order by a desc,b limit 20; +a b c +9 NULL NULL +9 0 9 +9 1 19 +9 2 29 +9 3 39 +9 4 49 +8 0 8 +8 1 18 +8 2 28 +8 3 38 +8 4 48 +7 0 7 +7 1 17 +7 2 27 +7 3 37 +7 4 47 +6 0 6 +6 1 16 +6 2 26 +6 3 36 +drop table t1; diff --git a/mysql-test/main/order_by_innodb.test b/mysql-test/main/order_by_innodb.test index 97c043b8dbc..db801ef79f3 100644 --- a/mysql-test/main/order_by_innodb.test +++ b/mysql-test/main/order_by_innodb.test @@ -1,7 +1,8 @@ # # ORDER BY handling (e.g. filesort) tests that require innodb # --- source include/have_innodb.inc +--source include/have_innodb.inc +--source include/have_sequence.inc --disable_warnings drop table if exists t0,t1,t2,t3; @@ -187,3 +188,14 @@ from drop table t1,t2; --echo # End of 10.2 tests + +--echo # +--echo # MDEV-26938 Support descending indexes internally in InnoDB +--echo # +create table t1 (a int, b int, c int, key r (a desc, b asc)); +insert t1 select seq % 10, seq div 10, seq from seq_1_to_55; +insert t1 values (NULL, NULL, NULL), (9, NULL, NULL); +explain select * from t1 force index(r) order by a,b limit 20; +explain select * from t1 force index(r) order by a desc,b limit 20; + select * from t1 force index(r) order by a desc,b limit 20; +drop table t1; diff --git a/mysql-test/main/partition_order.result b/mysql-test/main/partition_order.result index cecfc90eefb..d4a0c133d10 100644 --- a/mysql-test/main/partition_order.result +++ b/mysql-test/main/partition_order.result @@ -1,4 +1,3 @@ -drop table if exists t1; CREATE TABLE t1 ( a int not null, b int not null, @@ -783,3 +782,17 @@ a b c 1 1 1 1 NULL NULL drop table t1; +# +# MDEV-13756 Implement descending index: KEY (a DESC, b ASC) +# +create table t1 (a int, b int, key(a desc)) partition by hash(a) partitions 4; +insert into t1 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6); +explain select * from t1 order by a limit 3; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index NULL a 5 NULL 3 +select * from t1 order by a limit 3; +a b +1 1 +2 2 +3 3 +drop table t1; diff --git a/mysql-test/main/partition_order.test b/mysql-test/main/partition_order.test index ad956361d00..f5cd4c25074 100644 --- a/mysql-test/main/partition_order.test +++ b/mysql-test/main/partition_order.test @@ -1,14 +1,9 @@ -#--disable_abort_on_error # # Simple test for the partition storage engine # Focuses on tests of ordered index read # -- source include/have_partition.inc ---disable_warnings -drop table if exists t1; ---enable_warnings - # # Ordered index read, int type # @@ -842,3 +837,12 @@ INSERT into t1 values (1, NULL, NULL), (2, NULL, '10'); select * from t1 where a = 1 order by a desc, b desc; select * from t1 where a = 1 order by b desc; drop table t1; + +--echo # +--echo # MDEV-13756 Implement descending index: KEY (a DESC, b ASC) +--echo # +create table t1 (a int, b int, key(a desc)) partition by hash(a) partitions 4; +insert into t1 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6); +explain select * from t1 order by a limit 3; +select * from t1 order by a limit 3; +drop table t1; diff --git a/mysql-test/main/type_timestamp.result b/mysql-test/main/type_timestamp.result index e6fa9756b1d..52d0be675e2 100644 --- a/mysql-test/main/type_timestamp.result +++ b/mysql-test/main/type_timestamp.result @@ -575,7 +575,7 @@ a 2010-02-01 09:31:02 2010-02-01 09:31:03 2010-02-01 09:31:04 -CREATE TABLE t2 ( a TIMESTAMP, KEY ( a DESC ) ); +CREATE TABLE t2 ( a TIMESTAMP, KEY ( a ) ); INSERT INTO t2 VALUES( '2010-02-01 09:31:01' ); INSERT INTO t2 VALUES( '2010-02-01 09:31:02' ); INSERT INTO t2 VALUES( '2010-02-01 09:31:03' ); diff --git a/mysql-test/main/type_timestamp.test b/mysql-test/main/type_timestamp.test index 9c5b57b8885..80bc0bd0332 100644 --- a/mysql-test/main/type_timestamp.test +++ b/mysql-test/main/type_timestamp.test @@ -386,7 +386,7 @@ EXPLAIN SELECT * FROM t1 WHERE a >= '2010-02-01 09:31:02.0'; SELECT * FROM t1 WHERE a >= '2010-02-01 09:31:02.0'; -CREATE TABLE t2 ( a TIMESTAMP, KEY ( a DESC ) ); +CREATE TABLE t2 ( a TIMESTAMP, KEY ( a ) ); INSERT INTO t2 VALUES( '2010-02-01 09:31:01' ); INSERT INTO t2 VALUES( '2010-02-01 09:31:02' ); diff --git a/mysql-test/suite/innodb_fts/r/create.result b/mysql-test/suite/innodb_fts/r/create.result index 7be9333e647..9d16bffb1ef 100644 --- a/mysql-test/suite/innodb_fts/r/create.result +++ b/mysql-test/suite/innodb_fts/r/create.result @@ -194,9 +194,20 @@ ERROR HY000: Index 'FTS_DOC_ID_INDEX' is of wrong type for an InnoDB FULLTEXT in CREATE TABLE t1(a INT PRIMARY KEY, b TEXT, FTS_DOC_ID BIGINT UNSIGNED NOT NULL, UNIQUE KEY FTS_DOC_ID_INDEX(FTS_DOC_ID DESC), FULLTEXT(b)) ENGINE=InnoDB; -DROP TABLE t1; +ERROR HY000: Index 'FTS_DOC_ID_INDEX' is of wrong type for an InnoDB FULLTEXT index CREATE TABLE t1(a INT PRIMARY KEY, b TEXT, FTS_DOC_ID BIGINT UNSIGNED NOT NULL, UNIQUE KEY FTS_DOC_ID_INDEX(FTS_DOC_ID DESC)) ENGINE=InnoDB; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) NOT NULL, + `b` text DEFAULT NULL, + `FTS_DOC_ID` bigint(20) unsigned NOT NULL, + PRIMARY KEY (`a`), + UNIQUE KEY `FTS_DOC_ID_INDEX` (`FTS_DOC_ID` DESC) +) ENGINE=InnoDB DEFAULT CHARSET=latin1 ALTER TABLE t1 ADD FULLTEXT INDEX(b), ALGORITHM=INPLACE; +ERROR HY000: Index 'FTS_DOC_ID_INDEX' is of wrong type for an InnoDB FULLTEXT index ALTER TABLE t1 ADD FULLTEXT INDEX(b), ALGORITHM=COPY; +ERROR HY000: Index 'FTS_DOC_ID_INDEX' is of wrong type for an InnoDB FULLTEXT index DROP TABLE t1; diff --git a/mysql-test/suite/innodb_fts/t/create.test b/mysql-test/suite/innodb_fts/t/create.test index 6f5da11a66c..edecef64589 100644 --- a/mysql-test/suite/innodb_fts/t/create.test +++ b/mysql-test/suite/innodb_fts/t/create.test @@ -122,17 +122,16 @@ engine=innodb; --echo # MDEV-26938 Support descending indexes internally in InnoDB --echo # -# Unfortunately, the HA_REVERSE_SORT flag is not being stored in the .frm file. -#FIXME: --error ER_INNODB_FT_WRONG_DOCID_INDEX +--error ER_INNODB_FT_WRONG_DOCID_INDEX CREATE TABLE t1(a INT PRIMARY KEY, b TEXT, FTS_DOC_ID BIGINT UNSIGNED NOT NULL, UNIQUE KEY FTS_DOC_ID_INDEX(FTS_DOC_ID DESC), FULLTEXT(b)) ENGINE=InnoDB; -DROP TABLE t1; CREATE TABLE t1(a INT PRIMARY KEY, b TEXT, FTS_DOC_ID BIGINT UNSIGNED NOT NULL, UNIQUE KEY FTS_DOC_ID_INDEX(FTS_DOC_ID DESC)) ENGINE=InnoDB; -#FIXME: --error ER_INNODB_FT_WRONG_DOCID_INDEX +SHOW CREATE TABLE t1; +--error ER_INNODB_FT_WRONG_DOCID_INDEX ALTER TABLE t1 ADD FULLTEXT INDEX(b), ALGORITHM=INPLACE; -#FIXME: --error ER_INNODB_FT_WRONG_DOCID_INDEX +--error ER_INNODB_FT_WRONG_DOCID_INDEX ALTER TABLE t1 ADD FULLTEXT INDEX(b), ALGORITHM=COPY; DROP TABLE t1; diff --git a/mysql-test/suite/innodb_gis/r/geometry.result b/mysql-test/suite/innodb_gis/r/geometry.result index 437ff23e334..1f5a8aba00c 100644 --- a/mysql-test/suite/innodb_gis/r/geometry.result +++ b/mysql-test/suite/innodb_gis/r/geometry.result @@ -342,7 +342,7 @@ tab CREATE TABLE `tab` ( `c7` geometrycollection DEFAULT NULL, `c8` geometry DEFAULT NULL, UNIQUE KEY `idx2` (`c8`(5)), - KEY `idx1` (`c2`(5)) USING BTREE, + KEY `idx1` (`c2`(5) DESC) USING BTREE, KEY `idx3` (`c3`(5)) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=latin1 #check the data after modify @@ -778,7 +778,7 @@ tab3 CREATE TABLE `tab3` ( `c7` geometrycollection DEFAULT NULL, `c8` geometry DEFAULT NULL, UNIQUE KEY `idx2` (`c8`(5)), - KEY `idx1` (`c2`(5)) USING BTREE, + KEY `idx1` (`c2`(5) DESC) USING BTREE, KEY `idx3` (`c3`(5)) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC KEY_BLOCK_SIZE=16 #check index with WKB function diff --git a/sql/key.cc b/sql/key.cc index f2cebfe6d82..bda028a5ab7 100644 --- a/sql/key.cc +++ b/sql/key.cc @@ -1,5 +1,5 @@ /* Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved. - Copyright (c) 2018, 2020, MariaDB + Copyright (c) 2018, 2021, MariaDB This program is free software; you can redistribute it and/or modify it under the terms of the GNU General Public License as published by @@ -573,6 +573,9 @@ int key_rec_cmp(void *key_p, uchar *first_rec, uchar *second_rec) /* loop over every key part */ do { + const int GREATER= key_part->key_part_flag & HA_REVERSE_SORT ? -1 : +1; + const int LESS= -GREATER; + field= key_part->field; if (key_part->null_bit) @@ -593,12 +596,12 @@ int key_rec_cmp(void *key_p, uchar *first_rec, uchar *second_rec) ; /* Fall through, no NULL fields */ else { - DBUG_RETURN(+1); + DBUG_RETURN(GREATER); } } else if (!sec_is_null) { - DBUG_RETURN(-1); + DBUG_RETURN(LESS); } else goto next_loop; /* Both were NULL */ @@ -612,7 +615,7 @@ int key_rec_cmp(void *key_p, uchar *first_rec, uchar *second_rec) */ if ((result= field->cmp_prefix(field->ptr+first_diff, field->ptr+sec_diff, key_part->length))) - DBUG_RETURN(result); + DBUG_RETURN(result * GREATER); next_loop: key_part++; key_part_num++; diff --git a/sql/opt_range.cc b/sql/opt_range.cc index 06063cb9ae1..86539046a32 100644 --- a/sql/opt_range.cc +++ b/sql/opt_range.cc @@ -1,5 +1,5 @@ /* Copyright (c) 2000, 2015, Oracle and/or its affiliates. - Copyright (c) 2008, 2020, MariaDB + Copyright (c) 2008, 2021, MariaDB This program is free software; you can redistribute it and/or modify it under the terms of the GNU General Public License as published by @@ -2805,11 +2805,11 @@ int SQL_SELECT::test_quick_select(THD *thd, key_map keys_to_use, continue; // ToDo: ft-keys in non-ft ranges, if possible SerG } - trace_idx_details.add("usable", true); param.key[param.keys]=key_parts; key_part_info= key_info->key_part; uint cur_key_len= 0; Json_writer_array trace_keypart(thd, "key_parts"); + bool unusable_has_desc_keyparts= false; for (uint part= 0 ; part < n_key_parts ; part++, key_parts++, key_part_info++) { @@ -2824,8 +2824,17 @@ int SQL_SELECT::test_quick_select(THD *thd, key_map keys_to_use, (key_info->flags & HA_SPATIAL) ? Field::itMBR : Field::itRAW; /* Only HA_PART_KEY_SEG is used */ key_parts->flag= (uint8) key_part_info->key_part_flag; + if (key_part_info->key_part_flag & HA_REVERSE_SORT) + unusable_has_desc_keyparts= true; trace_keypart.add(key_parts->field->field_name); } + trace_keypart.end(); + trace_idx_details.add("usable", !unusable_has_desc_keyparts); + if (unusable_has_desc_keyparts) // TODO MDEV-13756 + { + key_parts= param.key[param.keys]; + continue; + } param.real_keynr[param.keys++]=idx; if (cur_key_len > max_key_len) max_key_len= cur_key_len; diff --git a/sql/opt_sum.cc b/sql/opt_sum.cc index b3871254e6e..5798e4cea28 100644 --- a/sql/opt_sum.cc +++ b/sql/opt_sum.cc @@ -1,5 +1,5 @@ /* Copyright (c) 2000, 2011, Oracle and/or its affiliates. - Copyright (c) 2008, 2017, MariaDB Corporation. + Copyright (c) 2008, 2021, MariaDB Corporation. This program is free software; you can redistribute it and/or modify it under the terms of the GNU General Public License as published by @@ -953,6 +953,9 @@ static bool find_key_for_maxmin(bool max_fl, TABLE_REF *ref, part->length < part_field->key_length()) break; + if (part->key_part_flag & HA_REVERSE_SORT) + break; // TODO MDEV-13756 + if (field->eq(part->field)) { ref->key= idx; diff --git a/sql/sql_class.h b/sql/sql_class.h index df9d89b5aff..f998b21856b 100644 --- a/sql/sql_class.h +++ b/sql/sql_class.h @@ -290,9 +290,9 @@ class Key_part_spec :public Sql_alloc { public: LEX_CSTRING field_name; uint length; - bool generated; + bool generated, asc; Key_part_spec(const LEX_CSTRING *name, uint len, bool gen= false) - : field_name(*name), length(len), generated(gen) + : field_name(*name), length(len), generated(gen), asc(1) {} bool operator==(const Key_part_spec& other) const; /** diff --git a/sql/sql_show.cc b/sql/sql_show.cc index d01f84fe7d1..877bfada417 100644 --- a/sql/sql_show.cc +++ b/sql/sql_show.cc @@ -2369,6 +2369,8 @@ int show_create_table_ex(THD *thd, TABLE_LIST *table_list, packet->append_parenthesized((long) key_part->length / key_part->field->charset()->mbmaxlen); } + if (key_part->key_part_flag & HA_REVERSE_SORT) + packet->append(STRING_WITH_LEN(" DESC")); } if (key_info->without_overlaps) diff --git a/sql/sql_table.cc b/sql/sql_table.cc index b07efb29bba..452c03b67fa 100644 --- a/sql/sql_table.cc +++ b/sql/sql_table.cc @@ -2281,7 +2281,7 @@ void promote_first_timestamp_column(List<Create_field> *column_definitions) static bool key_cmp(const Key_part_spec &a, const Key_part_spec &b) { - return a.length == b.length && + return a.length == b.length && a.asc == b.asc && !lex_string_cmp(system_charset_info, &a.field_name, &b.field_name); } @@ -3324,6 +3324,7 @@ mysql_prepare_create_table(THD *thd, HA_CREATE_INFO *create_info, key_part_info->fieldnr= field; key_part_info->offset= (uint16) sql_field->offset; key_part_info->key_type=sql_field->pack_flag; + key_part_info->key_part_flag= column->asc ? 0 : HA_REVERSE_SORT; uint key_part_length= sql_field->type_handler()-> calc_key_length(*sql_field); @@ -8381,9 +8382,10 @@ mysql_prepare_alter_table(THD *thd, TABLE *table, key_part_length= 0; // Use whole field } key_part_length /= kfield->charset()->mbmaxlen; - key_parts.push_back(new (thd->mem_root) Key_part_spec(&cfield->field_name, - key_part_length, true), - thd->mem_root); + Key_part_spec *kps= new (thd->mem_root) Key_part_spec(&cfield->field_name, + key_part_length, true); + kps->asc= !(key_part->key_part_flag & HA_REVERSE_SORT); + key_parts.push_back(kps, thd->mem_root); if (!(cfield->invisible == INVISIBLE_SYSTEM && cfield->vers_sys_field())) user_keyparts= true; } diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy index 442644eddd6..faea2120c06 100644 --- a/sql/sql_yacc.yy +++ b/sql/sql_yacc.yy @@ -6844,10 +6844,12 @@ ignorability: key_list: key_list ',' key_part order_dir { + $3->asc= $4; Lex->last_key->columns.push_back($3, thd->mem_root); } | key_part order_dir { + $1->asc= $2; Lex->last_key->columns.push_back($1, thd->mem_root); } ; diff --git a/sql/unireg.cc b/sql/unireg.cc index 2726b9a68c2..6c342f1373f 100644 --- a/sql/unireg.cc +++ b/sql/unireg.cc @@ -1,6 +1,6 @@ /* Copyright (c) 2000, 2011, Oracle and/or its affiliates. - Copyright (c) 2009, 2020, MariaDB Corporation. + Copyright (c) 2009, 2021, MariaDB Corporation. This program is free software; you can redistribute it and/or modify it under the terms of the GNU General Public License as published by @@ -685,6 +685,13 @@ static uint pack_keys(uchar *keybuff, uint key_count, KEY *keyinfo, DBUG_PRINT("loop", ("flags: %lu key_parts: %d key_part: %p", key->flags, key->user_defined_key_parts, key->key_part)); + + /* For SPATIAL, FULLTEXT and HASH indexes (anything other than B-tree), + ignore the ASC/DESC attribute of columns. */ + const uchar ha_reverse_sort= + key->algorithm > HA_KEY_ALG_BTREE || key->flags & (HA_FULLTEXT|HA_SPATIAL) + ? 0 : HA_REVERSE_SORT; + for (key_part=key->key_part,key_part_end=key_part+key->user_defined_key_parts ; key_part != key_part_end ; key_part++) @@ -697,7 +704,7 @@ static uint pack_keys(uchar *keybuff, uint key_count, KEY *keyinfo, int2store(pos,key_part->fieldnr+1+FIELD_NAME_USED); offset= (uint) (key_part->offset+data_offset+1); int2store(pos+2, offset); - pos[4]=0; // Sort order + pos[4]= key_part->key_part_flag & ha_reverse_sort; int2store(pos+5,key_part->key_type); int2store(pos+7,key_part->length); pos+=9; |