summaryrefslogtreecommitdiff
path: root/mysql-test
diff options
context:
space:
mode:
Diffstat (limited to 'mysql-test')
-rw-r--r--mysql-test/main/func_group.result2
-rw-r--r--mysql-test/main/func_group.test2
-rw-r--r--mysql-test/main/key.result6
-rw-r--r--mysql-test/main/key.test9
-rw-r--r--mysql-test/main/opt_trace.result52
-rw-r--r--mysql-test/main/opt_trace_index_merge.result8
-rw-r--r--mysql-test/main/opt_trace_index_merge_innodb.result8
-rw-r--r--mysql-test/main/order_by.result36
-rw-r--r--mysql-test/main/order_by.test18
-rw-r--r--mysql-test/main/order_by_innodb.result35
-rw-r--r--mysql-test/main/order_by_innodb.test14
-rw-r--r--mysql-test/main/partition_order.result15
-rw-r--r--mysql-test/main/partition_order.test14
-rw-r--r--mysql-test/main/type_timestamp.result2
-rw-r--r--mysql-test/main/type_timestamp.test2
-rw-r--r--mysql-test/suite/innodb_fts/r/create.result13
-rw-r--r--mysql-test/suite/innodb_fts/t/create.test9
-rw-r--r--mysql-test/suite/innodb_gis/r/geometry.result4
18 files changed, 184 insertions, 65 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
View Lorry Controller Status