summaryrefslogtreecommitdiff
path: root/mysql-test/r
diff options
context:
space:
mode:
Diffstat (limited to 'mysql-test/r')
-rw-r--r--mysql-test/r/ctype_ldml.result8
-rw-r--r--mysql-test/r/func_group.result44
-rw-r--r--mysql-test/r/group_min_max.result6
-rw-r--r--mysql-test/r/innodb-consistent.result35
-rw-r--r--mysql-test/r/innodb_bug34300.result1
-rw-r--r--mysql-test/r/innodb_bug44369.result14
-rw-r--r--mysql-test/r/innodb_bug44571.result9
-rw-r--r--mysql-test/r/innodb_bug46000.result17
-rw-r--r--mysql-test/r/myisam.result21
-rw-r--r--mysql-test/r/order_by.result31
-rw-r--r--mysql-test/r/partition.result15
-rw-r--r--mysql-test/r/partition_open_files_limit.result22
-rw-r--r--mysql-test/r/range.result179
-rw-r--r--mysql-test/r/type_bit.result10
14 files changed, 409 insertions, 3 deletions
diff --git a/mysql-test/r/ctype_ldml.result b/mysql-test/r/ctype_ldml.result
index 711921eb526..fe870f82743 100644
--- a/mysql-test/r/ctype_ldml.result
+++ b/mysql-test/r/ctype_ldml.result
@@ -321,3 +321,11 @@ Vv
Xx
YyÝýỲỳỴỵỶỷỸỹ
drop table t1;
+Bug#46448 trailing spaces are not ignored when user collation maps space != 0x20
+set names latin1;
+show collation like 'latin1_test';
+Collation Charset Id Default Compiled Sortlen
+latin1_test latin1 99 Yes 1
+select "foo" = "foo " collate latin1_test;
+"foo" = "foo " collate latin1_test
+1
diff --git a/mysql-test/r/func_group.result b/mysql-test/r/func_group.result
index 3d989ad1730..94147640cde 100644
--- a/mysql-test/r/func_group.result
+++ b/mysql-test/r/func_group.result
@@ -1477,3 +1477,47 @@ COUNT(*)
SET SQL_MODE=default;
DROP TABLE t1;
End of 5.0 tests
+#
+# BUG#47280 - strange results from count(*) with order by multiple
+# columns without where/group
+#
+#
+# Initialize test
+#
+CREATE TABLE t1 (
+pk INT NOT NULL,
+i INT,
+PRIMARY KEY (pk)
+);
+INSERT INTO t1 VALUES (1,11),(2,12),(3,13);
+#
+# Start test
+# All the following queries shall return 1 record
+#
+
+# Masking all correct values {11...13} for column i in this result.
+SELECT MAX(pk) as max, i
+FROM t1
+ORDER BY max;
+max i
+3 #
+
+EXPLAIN
+SELECT MAX(pk) as max, i
+FROM t1
+ORDER BY max;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using temporary
+
+# Only 11 is correct for collumn i in this result
+SELECT MAX(pk) as max, i
+FROM t1
+WHERE pk<2
+ORDER BY max;
+max i
+1 11
+#
+# Cleanup
+#
+DROP TABLE t1;
+End of 5.1 tests
diff --git a/mysql-test/r/group_min_max.result b/mysql-test/r/group_min_max.result
index c4841ee57a0..5b1699686c9 100644
--- a/mysql-test/r/group_min_max.result
+++ b/mysql-test/r/group_min_max.result
@@ -876,10 +876,10 @@ id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 range NULL idx_t1_1 163 NULL 17 Using where; Using index for group-by
explain select a1,a2,b, max(c) from t1 where (c > 'b1') or (c <= 'g1') group by a1,a2,b;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range NULL idx_t1_1 147 NULL 17 Using where; Using index for group-by
+1 SIMPLE t1 range NULL idx_t1_1 163 NULL 17 Using where; Using index for group-by
explain select a1,a2,b,min(c),max(c) from t1 where (c > 'b1') or (c <= 'g1') group by a1,a2,b;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range NULL idx_t1_1 147 NULL 17 Using where; Using index for group-by
+1 SIMPLE t1 range NULL idx_t1_1 163 NULL 17 Using where; Using index for group-by
explain select a1,a2,b,min(c),max(c) from t1 where (c > 'b111') and (c <= 'g112') group by a1,a2,b;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 range NULL idx_t1_1 163 NULL 17 Using where; Using index for group-by
@@ -924,7 +924,7 @@ id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t2 range NULL idx_t2_1 163 NULL # Using where; Using index for group-by
explain select a1,a2,b, max(c) from t2 where (c > 'b1') or (c <= 'g1') group by a1,a2,b;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t2 range NULL idx_t2_1 146 NULL # Using where; Using index for group-by
+1 SIMPLE t2 range NULL idx_t2_1 163 NULL # Using where; Using index for group-by
explain select a1,a2,b,min(c),max(c) from t2 where (c > 'b1') or (c <= 'g1') group by a1,a2,b;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t2 range NULL idx_t2_1 163 NULL # Using where; Using index for group-by
diff --git a/mysql-test/r/innodb-consistent.result b/mysql-test/r/innodb-consistent.result
new file mode 100644
index 00000000000..9115791b99c
--- /dev/null
+++ b/mysql-test/r/innodb-consistent.result
@@ -0,0 +1,35 @@
+drop table if exists t1;
+set session transaction isolation level read committed;
+create table t1(a int not null) engine=innodb DEFAULT CHARSET=latin1;
+create table t2 like t1;
+insert into t2 values (1),(2),(3),(4),(5),(6),(7);
+set autocommit=0;
+begin;
+replace into t1 select * from t2;
+set session transaction isolation level read committed;
+set autocommit=0;
+delete from t2 where a=5;
+commit;
+delete from t2;
+commit;
+commit;
+begin;
+insert into t1 select * from t2;
+set session transaction isolation level read committed;
+set autocommit=0;
+delete from t2 where a=5;
+commit;
+delete from t2;
+commit;
+commit;
+select * from t1;
+a
+1
+2
+3
+4
+5
+6
+7
+drop table t1;
+drop table t2;
diff --git a/mysql-test/r/innodb_bug34300.result b/mysql-test/r/innodb_bug34300.result
index 03ae355de7b..4e3d3c17040 100644
--- a/mysql-test/r/innodb_bug34300.result
+++ b/mysql-test/r/innodb_bug34300.result
@@ -6,4 +6,5 @@ SELECT f4, f8 FROM bug34300;
f4 f8
xxx zzz
DROP TABLE bug34300;
+SET @@global.max_allowed_packet=1048576;
SET @@global.max_allowed_packet=default;
diff --git a/mysql-test/r/innodb_bug44369.result b/mysql-test/r/innodb_bug44369.result
new file mode 100644
index 00000000000..e4b84ecac19
--- /dev/null
+++ b/mysql-test/r/innodb_bug44369.result
@@ -0,0 +1,14 @@
+create table bug44369 (DB_ROW_ID int) engine=innodb;
+ERROR HY000: Can't create table 'test.bug44369' (errno: -1)
+create table bug44369 (db_row_id int) engine=innodb;
+ERROR HY000: Can't create table 'test.bug44369' (errno: -1)
+show errors;
+Level Code Message
+Error 1005 Error creating table 'test/bug44369' with column name 'db_row_id'. 'db_row_id' is a reserved name. Please try to re-create the table with a different column name.
+Error 1005 Can't create table 'test.bug44369' (errno: -1)
+create table bug44369 (db_TRX_Id int) engine=innodb;
+ERROR HY000: Can't create table 'test.bug44369' (errno: -1)
+show errors;
+Level Code Message
+Error 1005 Error creating table 'test/bug44369' with column name 'db_TRX_Id'. 'db_TRX_Id' is a reserved name. Please try to re-create the table with a different column name.
+Error 1005 Can't create table 'test.bug44369' (errno: -1)
diff --git a/mysql-test/r/innodb_bug44571.result b/mysql-test/r/innodb_bug44571.result
new file mode 100644
index 00000000000..36374edcb3e
--- /dev/null
+++ b/mysql-test/r/innodb_bug44571.result
@@ -0,0 +1,9 @@
+CREATE TABLE bug44571 (foo INT) ENGINE=InnoDB;
+ALTER TABLE bug44571 CHANGE foo bar INT;
+ALTER TABLE bug44571 ADD INDEX bug44571b (foo);
+ERROR 42000: Key column 'foo' doesn't exist in table
+ALTER TABLE bug44571 ADD INDEX bug44571b (bar);
+ERROR HY000: Incorrect key file for table 'bug44571'; try to repair it
+CREATE INDEX bug44571b ON bug44571 (bar);
+ERROR HY000: Incorrect key file for table 'bug44571'; try to repair it
+DROP TABLE bug44571;
diff --git a/mysql-test/r/innodb_bug46000.result b/mysql-test/r/innodb_bug46000.result
new file mode 100644
index 00000000000..ccff888a48d
--- /dev/null
+++ b/mysql-test/r/innodb_bug46000.result
@@ -0,0 +1,17 @@
+create table bug46000(`id` int,key `GEN_CLUST_INDEX`(`id`))engine=innodb;
+ERROR HY000: Can't create table 'test.bug46000' (errno: -1)
+create table bug46000(`id` int, key `GEN_clust_INDEX`(`id`))engine=innodb;
+ERROR HY000: Can't create table 'test.bug46000' (errno: -1)
+show errors;
+Level Code Message
+Error 1005 Cannot Create Index with name 'GEN_CLUST_INDEX'. The name is reserved for the system default primary index.
+Error 1005 Can't create table 'test.bug46000' (errno: -1)
+create table bug46000(id int) engine=innodb;
+create index GEN_CLUST_INDEX on bug46000(id);
+ERROR HY000: Can't create table '#sql-temporary' (errno: -1)
+show errors;
+Level Code Message
+Error 1005 Cannot Create Index with name 'GEN_CLUST_INDEX'. The name is reserved for the system default primary index.
+Error 1005 Can't create table '#sql-temporary' (errno: -1)
+create index idx on bug46000(id);
+drop table bug46000;
diff --git a/mysql-test/r/myisam.result b/mysql-test/r/myisam.result
index 58e2e451a0d..f5050ccf1c0 100644
--- a/mysql-test/r/myisam.result
+++ b/mysql-test/r/myisam.result
@@ -2271,4 +2271,25 @@ checksum table t3;
Table Checksum
test.t3 326284887
drop table t1,t2,t3;
+CREATE TABLE t1(a INT, b CHAR(10), KEY(a), KEY(b));
+INSERT INTO t1 VALUES(1,'0'),(2,'0'),(3,'0'),(4,'0'),(5,'0'),
+(6,'0'),(7,'0');
+INSERT INTO t1 SELECT a+10,b FROM t1;
+INSERT INTO t1 SELECT a+20,b FROM t1;
+INSERT INTO t1 SELECT a+40,b FROM t1;
+INSERT INTO t1 SELECT a+80,b FROM t1;
+INSERT INTO t1 SELECT a+160,b FROM t1;
+INSERT INTO t1 SELECT a+320,b FROM t1;
+INSERT INTO t1 SELECT a+640,b FROM t1;
+INSERT INTO t1 SELECT a+1280,b FROM t1;
+INSERT INTO t1 SELECT a+2560,b FROM t1;
+INSERT INTO t1 SELECT a+5120,b FROM t1;
+SET myisam_sort_buffer_size=4;
+REPAIR TABLE t1;
+Table Op Msg_type Msg_text
+test.t1 repair error myisam_sort_buffer_size is too small
+test.t1 repair warning Number of rows changed from 0 to 7168
+test.t1 repair status OK
+SET myisam_sort_buffer_size=@@global.myisam_sort_buffer_size;
+DROP TABLE t1;
End of 5.1 tests
diff --git a/mysql-test/r/order_by.result b/mysql-test/r/order_by.result
index 306fce1f3c2..0c72f816c21 100644
--- a/mysql-test/r/order_by.result
+++ b/mysql-test/r/order_by.result
@@ -1557,3 +1557,34 @@ a
2001
1991
DROP TABLE t1;
+#
+# Bug #43029: FORCE INDEX FOR ORDER BY is ignored when join buffering
+# is used
+#
+CREATE TABLE t1 (a INT, b INT, KEY (a));
+INSERT INTO t1 VALUES (0, NULL), (1, NULL), (2, NULL), (3, NULL);
+INSERT INTO t1 SELECT a+4, b FROM t1;
+INSERT INTO t1 SELECT a+8, b FROM t1;
+CREATE TABLE t2 (a INT, b INT);
+INSERT INTO t2 VALUES (0,NULL), (1,NULL), (2,NULL), (3,NULL), (4,NULL);
+INSERT INTO t2 SELECT a+4, b FROM t2;
+# shouldn't have "using filesort"
+EXPLAIN
+SELECT * FROM t1 FORCE INDEX FOR ORDER BY (a), t2 WHERE t1.a < 2 ORDER BY t1.a;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 range a a 5 NULL 2 Using where
+1 SIMPLE t2 ALL NULL NULL NULL NULL 10
+# should have "using filesort"
+EXPLAIN
+SELECT * FROM t1 USE INDEX FOR ORDER BY (a), t2 WHERE t1.a < 2 ORDER BY t1.a;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 range a a 5 NULL 2 Using where; Using temporary; Using filesort
+1 SIMPLE t2 ALL NULL NULL NULL NULL 10 Using join buffer
+# should have "using filesort"
+EXPLAIN
+SELECT * FROM t1 FORCE INDEX FOR JOIN (a), t2 WHERE t1.a < 2 ORDER BY t1.a;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 range a a 5 NULL 2 Using where; Using temporary; Using filesort
+1 SIMPLE t2 ALL NULL NULL NULL NULL 10 Using join buffer
+DROP TABLE t1, t2;
+End of 5.1 tests
diff --git a/mysql-test/r/partition.result b/mysql-test/r/partition.result
index a76cb2ba225..065c427bfd3 100644
--- a/mysql-test/r/partition.result
+++ b/mysql-test/r/partition.result
@@ -50,6 +50,21 @@ t1 CREATE TABLE `t1` (
PARTITION p3 VALUES LESS THAN (733969) ENGINE = MyISAM,
PARTITION pmax VALUES LESS THAN MAXVALUE ENGINE = MyISAM) */
DROP TABLE t1;
+create table t1 (a int, b int, key(a))
+partition by list (a)
+( partition p0 values in (1),
+partition p1 values in (2));
+insert into t1 values (1,1),(2,1),(2,2),(2,3);
+show indexes from t1;
+Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment
+t1 1 a 1 a A NULL NULL NULL YES BTREE
+analyze table t1;
+Table Op Msg_type Msg_text
+test.t1 analyze status OK
+show indexes from t1;
+Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment
+t1 1 a 1 a A 1 NULL NULL YES BTREE
+drop table t1;
CREATE TABLE t1 (a INT, FOREIGN KEY (a) REFERENCES t0 (a))
ENGINE=MyISAM
PARTITION BY HASH (a);
diff --git a/mysql-test/r/partition_open_files_limit.result b/mysql-test/r/partition_open_files_limit.result
new file mode 100644
index 00000000000..1441ba4e78e
--- /dev/null
+++ b/mysql-test/r/partition_open_files_limit.result
@@ -0,0 +1,22 @@
+DROP TABLE IF EXISTS `t1`;
+# Bug#46922: crash when adding partitions and open_files_limit is reached
+CREATE TABLE t1 (a INT PRIMARY KEY)
+ENGINE=MyISAM PARTITION BY KEY () PARTITIONS 1;
+INSERT INTO t1 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11);
+# if the bug exists, then crash will happen here
+ALTER TABLE t1 ADD PARTITION PARTITIONS 511;
+ERROR HY000: Out of resources when opening file '<partition file>' (Errcode: 24)
+SELECT * FROM t1;
+a
+1
+10
+11
+2
+3
+4
+5
+6
+7
+8
+9
+DROP TABLE t1;
diff --git a/mysql-test/r/range.result b/mysql-test/r/range.result
index cc5e8d2be96..c98a7696ea6 100644
--- a/mysql-test/r/range.result
+++ b/mysql-test/r/range.result
@@ -1219,3 +1219,182 @@ explain select * from t2 where a=1000 and b<11;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t2 ref a a 5 const 502 Using where
drop table t1, t2;
+CREATE TABLE t1( a INT, b INT, KEY( a, b ) );
+CREATE TABLE t2( a INT, b INT, KEY( a, b ) );
+CREATE TABLE t3( a INT, b INT, KEY( a, b ) );
+INSERT INTO t1( a, b )
+VALUES (0, 1), (1, 2), (1, 4), (2, 3), (5, 0), (9, 7);
+INSERT INTO t2( a, b )
+VALUES ( 1, 1), ( 2, 1), ( 3, 1), ( 4, 1), ( 5, 1),
+( 6, 1), ( 7, 1), ( 8, 1), ( 9, 1), (10, 1),
+(11, 1), (12, 1), (13, 1), (14, 1), (15, 1),
+(16, 1), (17, 1), (18, 1), (19, 1), (20, 1);
+INSERT INTO t2 SELECT a, 2 FROM t2 WHERE b = 1;
+INSERT INTO t2 SELECT a, 3 FROM t2 WHERE b = 1;
+INSERT INTO t2 SELECT -1, -1 FROM t2;
+INSERT INTO t2 SELECT -1, -1 FROM t2;
+INSERT INTO t2 SELECT -1, -1 FROM t2;
+INSERT INTO t3
+VALUES (1, 0), (2, 0), (3, 0), (4, 0), (5, 0),
+(6, 0), (7, 0), (8, 0), (9, 0), (10, 0);
+INSERT INTO t3 SELECT * FROM t3 WHERE a = 10;
+INSERT INTO t3 SELECT * FROM t3 WHERE a = 10;
+SELECT * FROM t1 WHERE
+3 <= a AND a < 5 OR
+5 < a AND b = 3 OR
+3 <= a;
+a b
+5 0
+9 7
+EXPLAIN
+SELECT * FROM t1 WHERE
+3 <= a AND a < 5 OR
+5 < a AND b = 3 OR
+3 <= a;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 range a a 5 NULL 3 Using where; Using index
+SELECT * FROM t1 WHERE
+3 <= a AND a < 5 OR
+5 <= a AND b = 3 OR
+3 <= a;
+a b
+5 0
+9 7
+EXPLAIN
+SELECT * FROM t1 WHERE
+3 <= a AND a < 5 OR
+5 <= a AND b = 3 OR
+3 <= a;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 range a a 5 NULL 4 Using where; Using index
+SELECT * FROM t1 WHERE
+3 <= a AND a <= 5 OR
+5 <= a AND b = 3 OR
+3 <= a;
+a b
+5 0
+9 7
+EXPLAIN
+SELECT * FROM t1 WHERE
+3 <= a AND a <= 5 OR
+5 <= a AND b = 3 OR
+3 <= a;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 range a a 5 NULL 3 Using where; Using index
+SELECT * FROM t1 WHERE
+3 <= a AND a <= 5 OR
+3 <= a;
+a b
+5 0
+9 7
+EXPLAIN
+SELECT * FROM t1 WHERE
+3 <= a AND a <= 5 OR
+3 <= a;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 range a a 5 NULL 3 Using where; Using index
+SELECT * FROM t2 WHERE
+5 <= a AND a < 10 AND b = 1 OR
+15 <= a AND a < 20 AND b = 3
+OR
+1 <= a AND b = 1;
+a b
+1 1
+2 1
+3 1
+4 1
+5 1
+6 1
+7 1
+8 1
+9 1
+10 1
+11 1
+12 1
+13 1
+14 1
+15 1
+15 3
+16 1
+16 3
+17 1
+17 3
+18 1
+18 3
+19 1
+19 3
+20 1
+EXPLAIN
+SELECT * FROM t2 WHERE
+5 <= a AND a < 10 AND b = 1 OR
+15 <= a AND a < 20 AND b = 3
+OR
+1 <= a AND b = 1;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t2 range a a 10 NULL 50 Using where; Using index
+SELECT * FROM t2 WHERE
+5 <= a AND a < 10 AND b = 2 OR
+15 <= a AND a < 20 AND b = 3
+OR
+1 <= a AND b = 1;
+a b
+1 1
+2 1
+3 1
+4 1
+5 1
+5 2
+6 1
+6 2
+7 1
+7 2
+8 1
+8 2
+9 1
+9 2
+10 1
+11 1
+12 1
+13 1
+14 1
+15 1
+15 3
+16 1
+16 3
+17 1
+17 3
+18 1
+18 3
+19 1
+19 3
+20 1
+EXPLAIN
+SELECT * FROM t2 WHERE
+5 <= a AND a < 10 AND b = 2 OR
+15 <= a AND a < 20 AND b = 3
+OR
+1 <= a AND b = 1;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t2 range a a 10 NULL 50 Using where; Using index
+SELECT * FROM t3 WHERE
+5 <= a AND a < 10 AND b = 3 OR
+a < 5 OR
+a < 10;
+a b
+1 0
+2 0
+3 0
+4 0
+5 0
+6 0
+7 0
+8 0
+9 0
+EXPLAIN
+SELECT * FROM t3 WHERE
+5 <= a AND a < 10 AND b = 3 OR
+a < 5 OR
+a < 10;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t3 range a a 5 NULL 8 Using where; Using index
+DROP TABLE t1, t2, t3;
diff --git a/mysql-test/r/type_bit.result b/mysql-test/r/type_bit.result
index b831771d9c5..51feab0a421 100644
--- a/mysql-test/r/type_bit.result
+++ b/mysql-test/r/type_bit.result
@@ -749,6 +749,16 @@ bin(a1)
110000111111111
110001011111111
drop table t1bit7, t2bit7;
+#
+# Bug42803: Field_bit does not have unsigned_flag field,
+# can lead to bad memory access
+#
+CREATE TABLE t1 (a BIT(7), b BIT(9), KEY(a, b));
+INSERT INTO t1 VALUES(0, 0), (5, 3), (5, 6), (6, 4), (7, 0);
+EXPLAIN SELECT a+0, b+0 FROM t1 WHERE a > 4 and b < 7 ORDER BY 2;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 range a a 2 NULL 4 Using where; Using index; Using filesort
+DROP TABLE t1;
End of 5.0 tests
create table t1(a bit(7));
insert into t1 values(0x40);
View Lorry Controller Status