summaryrefslogtreecommitdiff
path: root/mysql-test
diff options
context:
space:
mode:
Diffstat (limited to 'mysql-test')
-rw-r--r--mysql-test/include/mix1.inc85
-rw-r--r--mysql-test/r/create.result29
-rw-r--r--mysql-test/r/having.result19
-rw-r--r--mysql-test/r/innodb.result2
-rw-r--r--mysql-test/r/innodb_mysql.result268
-rw-r--r--mysql-test/r/log_state.result2
-rw-r--r--mysql-test/r/mysqldump.result22
-rw-r--r--mysql-test/r/sp-prelocking.result22
-rw-r--r--mysql-test/r/sp.result35
-rw-r--r--mysql-test/r/trigger.result28
-rw-r--r--mysql-test/r/type_enum.result24
-rw-r--r--mysql-test/t/create.test29
-rw-r--r--mysql-test/t/having.test26
-rw-r--r--mysql-test/t/innodb.test2
-rw-r--r--mysql-test/t/mysqldump.test22
-rw-r--r--mysql-test/t/sp-prelocking.test23
-rw-r--r--mysql-test/t/sp.test34
-rw-r--r--mysql-test/t/trigger.test23
-rw-r--r--mysql-test/t/type_enum.test20
19 files changed, 710 insertions, 5 deletions
diff --git a/mysql-test/include/mix1.inc b/mysql-test/include/mix1.inc
index e863e59fad6..591971a32a4 100644
--- a/mysql-test/include/mix1.inc
+++ b/mysql-test/include/mix1.inc
@@ -814,6 +814,91 @@ create table t1 (a int) engine=innodb;
alter table t1 alter a set default 1;
drop table t1;
+#
+# Bug #28591: MySQL need not sort the records in case of ORDER BY
+# primary_key on InnoDB table
+#
+
+CREATE TABLE t1 (a int, b int, PRIMARY KEY (a), KEY bkey (b)) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (1,2),(3,2),(2,2),(4,2),(5,2),(6,2),(7,2),(8,2);
+INSERT INTO t1 SELECT a + 8, 2 FROM t1;
+INSERT INTO t1 SELECT a + 16, 1 FROM t1;
+query_vertical EXPLAIN SELECT * FROM t1 WHERE b=2 ORDER BY a;
+SELECT * FROM t1 WHERE b=2 ORDER BY a;
+query_vertical EXPLAIN SELECT * FROM t1 WHERE b BETWEEN 1 AND 2 ORDER BY a;
+SELECT * FROM t1 WHERE b BETWEEN 1 AND 2 ORDER BY a;
+query_vertical EXPLAIN SELECT * FROM t1 WHERE b BETWEEN 1 AND 2 ORDER BY b,a;
+SELECT * FROM t1 WHERE b BETWEEN 1 AND 2 ORDER BY b,a;
+
+CREATE TABLE t2 (a int, b int, c int, PRIMARY KEY (a), KEY bkey (b,c))
+ ENGINE=InnoDB;
+INSERT INTO t2 VALUES (1,1,1),(3,1,1),(2,1,1),(4,1,1);
+INSERT INTO t2 SELECT a + 4, 1, 1 FROM t2;
+INSERT INTO t2 SELECT a + 8, 1, 1 FROM t2;
+
+query_vertical EXPLAIN SELECT * FROM t2 WHERE b=1 ORDER BY a;
+SELECT * FROM t2 WHERE b=1 ORDER BY a;
+query_vertical EXPLAIN SELECT * FROM t2 WHERE b=1 AND c=1 ORDER BY a;
+SELECT * FROM t2 WHERE b=1 AND c=1 ORDER BY a;
+query_vertical EXPLAIN SELECT * FROM t2 WHERE b=1 AND c=1 ORDER BY b,c,a;
+SELECT * FROM t2 WHERE b=1 AND c=1 ORDER BY b,c,a;
+query_vertical EXPLAIN SELECT * FROM t2 WHERE b=1 AND c=1 ORDER BY c,a;
+SELECT * FROM t2 WHERE b=1 AND c=1 ORDER BY c,a;
+
+DROP TABLE t1,t2;
+
+
+#
+# Bug #29644: alter table hangs if records locked in share mode by long
+# running transaction
+#
+
+CREATE TABLE t1 (a INT, PRIMARY KEY (a)) ENGINE=InnoDB;
+
+INSERT INTO t1 VALUES (1),(2),(3),(4),(5),(6),(7),(8);
+INSERT INTO t1 SELECT a + 8 FROM t1;
+INSERT INTO t1 SELECT a + 16 FROM t1;
+
+DELIMITER |;
+CREATE PROCEDURE p1 ()
+BEGIN
+ DECLARE i INT DEFAULT 50;
+ DECLARE cnt INT;
+ START TRANSACTION;
+ ALTER TABLE t1 ENGINE=InnoDB;
+ COMMIT;
+ START TRANSACTION;
+ WHILE (i > 0) DO
+ SET i = i - 1;
+ SELECT COUNT(*) INTO cnt FROM t1 LOCK IN SHARE MODE;
+ END WHILE;
+ COMMIT;
+END;|
+
+DELIMITER ;|
+
+CONNECT (con1,localhost,root,,);
+CONNECT (con2,localhost,root,,);
+
+CONNECTION con1;
+SEND CALL p1();
+CONNECTION con2;
+SEND CALL p1();
+CONNECTION default;
+CALL p1();
+
+CONNECTION con1;
+REAP;
+CONNECTION con2;
+REAP;
+CONNECTION default;
+DISCONNECT con1;
+DISCONNECT con2;
+
+DROP PROCEDURE p1;
+DROP TABLE t1;
+
+
--echo End of 5.0 tests
diff --git a/mysql-test/r/create.result b/mysql-test/r/create.result
index cdbb767dd9f..84a620336dc 100644
--- a/mysql-test/r/create.result
+++ b/mysql-test/r/create.result
@@ -1516,6 +1516,35 @@ t1 CREATE TABLE `t1` (
`c17` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
+
+Bug #26104 Bug on foreign key class constructor
+
+Check that ref_columns is initalized correctly in the constructor
+and semantic checks in mysql_prepare_table work.
+
+We do not need a storage engine that supports foreign keys
+for this test, as the checks are purely syntax-based, and the
+syntax is supported for all engines.
+
+drop table if exists t1,t2;
+create table t1(a int not null, b int not null, primary key (a, b));
+create table t2(a int not null, b int not null, c int not null, primary key (a),
+foreign key fk_bug26104 (b,c) references t1(a));
+ERROR 42000: Incorrect foreign key definition for 'fk_bug26104': Key reference and table reference don't match
+drop table t1;
+create table t1(f1 int,f2 int);
+insert into t1 value(1,1),(1,2),(1,3),(2,1),(2,2),(2,3);
+flush status;
+create table t2 select sql_big_result f1,count(f2) from t1 group by f1;
+show status like 'handler_read%';
+Variable_name Value
+Handler_read_first 0
+Handler_read_key 0
+Handler_read_next 0
+Handler_read_prev 0
+Handler_read_rnd 0
+Handler_read_rnd_next 7
+drop table t1,t2;
End of 5.0 tests
CREATE TABLE t1 (a int, b int);
insert into t1 values (1,1),(1,2);
diff --git a/mysql-test/r/having.result b/mysql-test/r/having.result
index 149391396b6..a5a96fd4958 100644
--- a/mysql-test/r/having.result
+++ b/mysql-test/r/having.result
@@ -158,6 +158,25 @@ EXPLAIN SELECT 0 AS x, a FROM t1 GROUP BY x,a HAVING x=1 AND a > 1;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible HAVING
DROP table t1;
+CREATE TABLE t1 (a int PRIMARY KEY);
+CREATE TABLE t2 (b int PRIMARY KEY, a int);
+CREATE TABLE t3 (b int, flag int);
+INSERT INTO t1 VALUES (1);
+INSERT INTO t2 VALUES (1,1), (2,1), (3,1);
+INSERT INTO t3(b,flag) VALUES (2, 1);
+SELECT t1.a
+FROM t1 INNER JOIN t2 ON t1.a=t2.a LEFT JOIN t3 ON t2.b=t3.b
+GROUP BY t1.a, t2.b HAVING MAX(t3.flag)=0;
+a
+SELECT DISTINCT t1.a, MAX(t3.flag)
+FROM t1 INNER JOIN t2 ON t1.a=t2.a LEFT JOIN t3 ON t2.b=t3.b
+GROUP BY t1.a, t2.b HAVING MAX(t3.flag)=0;
+a MAX(t3.flag)
+SELECT DISTINCT t1.a
+FROM t1 INNER JOIN t2 ON t1.a=t2.a LEFT JOIN t3 ON t2.b=t3.b
+GROUP BY t1.a, t2.b HAVING MAX(t3.flag)=0;
+a
+DROP TABLE t1,t2,t3;
create table t1 (col1 int, col2 varchar(5), col_t1 int);
create table t2 (col1 int, col2 varchar(5), col_t2 int);
create table t3 (col1 int, col2 varchar(5), col_t3 int);
diff --git a/mysql-test/r/innodb.result b/mysql-test/r/innodb.result
index 9a96145ef06..804c4b81c17 100644
--- a/mysql-test/r/innodb.result
+++ b/mysql-test/r/innodb.result
@@ -1640,7 +1640,7 @@ t2 CREATE TABLE `t2` (
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t2;
create table t2 (id int(11) not null, id2 int(11) not null, constraint t1_id_fk foreign key (id2,id) references t1 (id)) engine = innodb;
-ERROR HY000: Can't create table 'test.t2' (errno: 150)
+ERROR 42000: Incorrect foreign key definition for 't1_id_fk': Key reference and table reference don't match
create table t2 (a int auto_increment primary key, b int, index(b), foreign key (b) references t1(id), unique(b)) engine=innodb;
show create table t2;
Table Create Table
diff --git a/mysql-test/r/innodb_mysql.result b/mysql-test/r/innodb_mysql.result
index 3dc134352db..273374d016b 100644
--- a/mysql-test/r/innodb_mysql.result
+++ b/mysql-test/r/innodb_mysql.result
@@ -814,6 +814,274 @@ drop table if exists t1;
create table t1 (a int) engine=innodb;
alter table t1 alter a set default 1;
drop table t1;
+CREATE TABLE t1 (a int, b int, PRIMARY KEY (a), KEY bkey (b)) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (1,2),(3,2),(2,2),(4,2),(5,2),(6,2),(7,2),(8,2);
+INSERT INTO t1 SELECT a + 8, 2 FROM t1;
+INSERT INTO t1 SELECT a + 16, 1 FROM t1;
+EXPLAIN SELECT * FROM t1 WHERE b=2 ORDER BY a;
+id 1
+select_type SIMPLE
+table t1
+type ref
+possible_keys bkey
+key bkey
+key_len 5
+ref const
+rows 16
+Extra Using where; Using index
+SELECT * FROM t1 WHERE b=2 ORDER BY a;
+a b
+1 2
+2 2
+3 2
+4 2
+5 2
+6 2
+7 2
+8 2
+9 2
+10 2
+11 2
+12 2
+13 2
+14 2
+15 2
+16 2
+EXPLAIN SELECT * FROM t1 WHERE b BETWEEN 1 AND 2 ORDER BY a;
+id 1
+select_type SIMPLE
+table t1
+type range
+possible_keys bkey
+key bkey
+key_len 5
+ref NULL
+rows 16
+Extra Using where; Using index; Using filesort
+SELECT * FROM t1 WHERE b BETWEEN 1 AND 2 ORDER BY a;
+a b
+1 2
+2 2
+3 2
+4 2
+5 2
+6 2
+7 2
+8 2
+9 2
+10 2
+11 2
+12 2
+13 2
+14 2
+15 2
+16 2
+17 1
+18 1
+19 1
+20 1
+21 1
+22 1
+23 1
+24 1
+25 1
+26 1
+27 1
+28 1
+29 1
+30 1
+31 1
+32 1
+EXPLAIN SELECT * FROM t1 WHERE b BETWEEN 1 AND 2 ORDER BY b,a;
+id 1
+select_type SIMPLE
+table t1
+type range
+possible_keys bkey
+key bkey
+key_len 5
+ref NULL
+rows 16
+Extra Using where; Using index
+SELECT * FROM t1 WHERE b BETWEEN 1 AND 2 ORDER BY b,a;
+a b
+17 1
+18 1
+19 1
+20 1
+21 1
+22 1
+23 1
+24 1
+25 1
+26 1
+27 1
+28 1
+29 1
+30 1
+31 1
+32 1
+1 2
+2 2
+3 2
+4 2
+5 2
+6 2
+7 2
+8 2
+9 2
+10 2
+11 2
+12 2
+13 2
+14 2
+15 2
+16 2
+CREATE TABLE t2 (a int, b int, c int, PRIMARY KEY (a), KEY bkey (b,c))
+ENGINE=InnoDB;
+INSERT INTO t2 VALUES (1,1,1),(3,1,1),(2,1,1),(4,1,1);
+INSERT INTO t2 SELECT a + 4, 1, 1 FROM t2;
+INSERT INTO t2 SELECT a + 8, 1, 1 FROM t2;
+EXPLAIN SELECT * FROM t2 WHERE b=1 ORDER BY a;
+id 1
+select_type SIMPLE
+table t2
+type ref
+possible_keys bkey
+key bkey
+key_len 5
+ref const
+rows 8
+Extra Using where; Using index; Using filesort
+SELECT * FROM t2 WHERE b=1 ORDER BY a;
+a b c
+1 1 1
+2 1 1
+3 1 1
+4 1 1
+5 1 1
+6 1 1
+7 1 1
+8 1 1
+9 1 1
+10 1 1
+11 1 1
+12 1 1
+13 1 1
+14 1 1
+15 1 1
+16 1 1
+EXPLAIN SELECT * FROM t2 WHERE b=1 AND c=1 ORDER BY a;
+id 1
+select_type SIMPLE
+table t2
+type ref
+possible_keys bkey
+key bkey
+key_len 10
+ref const,const
+rows 8
+Extra Using where; Using index
+SELECT * FROM t2 WHERE b=1 AND c=1 ORDER BY a;
+a b c
+1 1 1
+2 1 1
+3 1 1
+4 1 1
+5 1 1
+6 1 1
+7 1 1
+8 1 1
+9 1 1
+10 1 1
+11 1 1
+12 1 1
+13 1 1
+14 1 1
+15 1 1
+16 1 1
+EXPLAIN SELECT * FROM t2 WHERE b=1 AND c=1 ORDER BY b,c,a;
+id 1
+select_type SIMPLE
+table t2
+type ref
+possible_keys bkey
+key bkey
+key_len 10
+ref const,const
+rows 8
+Extra Using where; Using index
+SELECT * FROM t2 WHERE b=1 AND c=1 ORDER BY b,c,a;
+a b c
+1 1 1
+2 1 1
+3 1 1
+4 1 1
+5 1 1
+6 1 1
+7 1 1
+8 1 1
+9 1 1
+10 1 1
+11 1 1
+12 1 1
+13 1 1
+14 1 1
+15 1 1
+16 1 1
+EXPLAIN SELECT * FROM t2 WHERE b=1 AND c=1 ORDER BY c,a;
+id 1
+select_type SIMPLE
+table t2
+type ref
+possible_keys bkey
+key bkey
+key_len 10
+ref const,const
+rows 8
+Extra Using where; Using index
+SELECT * FROM t2 WHERE b=1 AND c=1 ORDER BY c,a;
+a b c
+1 1 1
+2 1 1
+3 1 1
+4 1 1
+5 1 1
+6 1 1
+7 1 1
+8 1 1
+9 1 1
+10 1 1
+11 1 1
+12 1 1
+13 1 1
+14 1 1
+15 1 1
+16 1 1
+DROP TABLE t1,t2;
+CREATE TABLE t1 (a INT, PRIMARY KEY (a)) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (1),(2),(3),(4),(5),(6),(7),(8);
+INSERT INTO t1 SELECT a + 8 FROM t1;
+INSERT INTO t1 SELECT a + 16 FROM t1;
+CREATE PROCEDURE p1 ()
+BEGIN
+DECLARE i INT DEFAULT 50;
+DECLARE cnt INT;
+START TRANSACTION;
+ALTER TABLE t1 ENGINE=InnoDB;
+COMMIT;
+START TRANSACTION;
+WHILE (i > 0) DO
+SET i = i - 1;
+SELECT COUNT(*) INTO cnt FROM t1 LOCK IN SHARE MODE;
+END WHILE;
+COMMIT;
+END;|
+CALL p1();
+CALL p1();
+CALL p1();
+DROP PROCEDURE p1;
+DROP TABLE t1;
End of 5.0 tests
CREATE TABLE `t2` (
`k` int(11) NOT NULL auto_increment,
diff --git a/mysql-test/r/log_state.result b/mysql-test/r/log_state.result
index 946797aeec7..38b3ebd6e98 100644
--- a/mysql-test/r/log_state.result
+++ b/mysql-test/r/log_state.result
@@ -131,7 +131,7 @@ set global general_log=ON;
set global log_output=default;
show variables like 'log_output';
Variable_name Value
-log_output TABLE
+log_output FILE
set global general_log=OFF;
set global log_output=FILE;
truncate table mysql.general_log;
diff --git a/mysql-test/r/mysqldump.result b/mysql-test/r/mysqldump.result
index e8c971a41bc..1fa18482b9c 100644
--- a/mysql-test/r/mysqldump.result
+++ b/mysql-test/r/mysqldump.result
@@ -3573,6 +3573,28 @@ SELECT * FROM v1;
1
DROP VIEW v1;
#
+# Bug #29788: mysqldump discards the NO_AUTO_VALUE_ON_ZERO value of
+# the SQL_MODE variable after the dumping of triggers.
+#
+CREATE TABLE t1 (c1 INT);
+CREATE TRIGGER t1bd BEFORE DELETE ON t1 FOR EACH ROW BEGIN END;
+CREATE TABLE t2 (c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
+SET @TMP_SQL_MODE = @@SQL_MODE;
+SET SQL_MODE = 'NO_AUTO_VALUE_ON_ZERO';
+INSERT INTO t2 VALUES (0), (1), (2);
+SET SQL_MODE = @TMP_SQL_MODE;
+SELECT * FROM t2;
+c1
+0
+1
+2
+SELECT * FROM t2;
+c1
+0
+1
+2
+DROP TABLE t1,t2;
+#
# End of 5.0 tests
#
drop table if exists t1;
diff --git a/mysql-test/r/sp-prelocking.result b/mysql-test/r/sp-prelocking.result
index 5eac54803f0..c19bd1abd26 100644
--- a/mysql-test/r/sp-prelocking.result
+++ b/mysql-test/r/sp-prelocking.result
@@ -267,4 +267,26 @@ drop table bug_27907_logs;
insert into bug_27907_t1(a) values (1);
ERROR 42S02: Table 'test.bug_27907_logs' doesn't exist
drop table bug_27907_t1;
+
+Bug#22427 create table if not exists + stored function results in
+inconsistent behavior
+
+Add a test case, the bug itself was fixed by the patch for
+Bug#20662
+
+drop table if exists t1;
+drop function if exists f_bug22427;
+create table t1 (i int);
+insert into t1 values (1);
+create function f_bug22427() returns int return (select max(i) from t1);
+select f_bug22427();
+f_bug22427()
+1
+create table if not exists t1 select f_bug22427() as i;
+Warnings:
+Note 1050 Table 't1' already exists
+create table t1 select f_bug22427() as i;
+ERROR 42S01: Table 't1' already exists
+drop table t1;
+drop function f_bug22427;
End of 5.0 tests
diff --git a/mysql-test/r/sp.result b/mysql-test/r/sp.result
index 273c60110b3..07661f9ae54 100644
--- a/mysql-test/r/sp.result
+++ b/mysql-test/r/sp.result
@@ -1,5 +1,7 @@
use test;
drop table if exists t1,t2,t3,t4;
+drop function if exists f1;
+drop function if exists f2;
create table t1 (
id char(16) not null default '',
data int not null
@@ -6249,7 +6251,7 @@ drop table t1,t2;
CREATE TABLE t1 (a int auto_increment primary key) engine=MyISAM;
CREATE TABLE t2 (a int auto_increment primary key, b int) engine=innodb;
set @a=0;
-CREATE function bug27354() RETURNS int deterministic
+CREATE function bug27354() RETURNS int not deterministic
begin
insert into t1 values (null);
set @a=@a+1;
@@ -6433,3 +6435,34 @@ where ROUTINE_NAME = "proc_26302";
ROUTINE_NAME ROUTINE_DEFINITION
proc_26302 select 1 /* testing */
drop procedure proc_26302;
+CREATE FUNCTION f1() RETURNS INT DETERMINISTIC RETURN 2;
+CREATE FUNCTION f2(I INT) RETURNS INT DETERMINISTIC RETURN 3;
+CREATE TABLE t1 (c1 INT, INDEX(c1));
+INSERT INTO t1 VALUES (1), (2), (3), (4), (5);
+CREATE VIEW v1 AS SELECT c1 FROM t1;
+EXPLAIN SELECT * FROM t1 WHERE c1=1;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ref c1 c1 5 const 1 Using where; Using index
+EXPLAIN SELECT * FROM t1 WHERE c1=f1();
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ref c1 c1 5 const 1 Using where; Using index
+EXPLAIN SELECT * FROM v1 WHERE c1=1;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ref c1 c1 5 const 1 Using where; Using index
+EXPLAIN SELECT * FROM v1 WHERE c1=f1();
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ref c1 c1 5 const 1 Using where; Using index
+EXPLAIN SELECT * FROM t1 WHERE c1=f2(10);
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ref c1 c1 5 const 1 Using where; Using index
+EXPLAIN SELECT * FROM t1 WHERE c1=f2(c1);
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index NULL c1 5 NULL 5 Using where; Using index
+EXPLAIN SELECT * FROM t1 WHERE c1=f2(rand());
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index NULL c1 5 NULL 5 Using where; Using index
+DROP VIEW v1;
+DROP FUNCTION f1;
+DROP FUNCTION f2;
+DROP TABLE t1;
+End of 5.0 tests
diff --git a/mysql-test/r/trigger.result b/mysql-test/r/trigger.result
index 5d41d60c37a..189722bfe9b 100644
--- a/mysql-test/r/trigger.result
+++ b/mysql-test/r/trigger.result
@@ -1933,6 +1933,34 @@ Before UPDATE, new=multi-UPDATE, SET for t2, but the trigger is fired, old=multi
After UPDATE, new=multi-UPDATE, SET for t2, but the trigger is fired, old=multi-UPDATE
drop view v1;
drop table t1, t2, t1_op_log;
+
+Bug#27248 Triggers: error if insert affects temporary table
+
+The bug was fixed by the fix for Bug#26141
+
+drop table if exists t1;
+drop temporary table if exists t2;
+create table t1 (s1 int);
+create temporary table t2 (s1 int);
+create trigger t1_bi before insert on t1 for each row insert into t2 values (0);
+create trigger t1_bd before delete on t1 for each row delete from t2;
+insert into t1 values (0);
+insert into t1 values (0);
+select * from t1;
+s1
+0
+0
+select * from t2;
+s1
+0
+0
+delete from t1;
+select * from t1;
+s1
+select * from t2;
+s1
+drop table t1;
+drop temporary table t2;
End of 5.0 tests
drop table if exists table_25411_a;
drop table if exists table_25411_b;
diff --git a/mysql-test/r/type_enum.result b/mysql-test/r/type_enum.result
index ec72a6eb995..051cfe61384 100644
--- a/mysql-test/r/type_enum.result
+++ b/mysql-test/r/type_enum.result
@@ -1829,4 +1829,28 @@ c1 + 0
0
2
DROP TABLE t1,t2;
+CREATE TABLE t1(a enum('a','b','c','d'));
+INSERT INTO t1 VALUES (4),(1),(0),(3);
+Warnings:
+Warning 1265 Data truncated for column 'a' at row 3
+SELECT a FROM t1;
+a
+d
+a
+
+c
+EXPLAIN SELECT a FROM t1 WHERE a=0;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 4 Using where
+SELECT a FROM t1 WHERE a=0;
+a
+
+ALTER TABLE t1 ADD PRIMARY KEY (a);
+EXPLAIN SELECT a FROM t1 WHERE a=0;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 const PRIMARY PRIMARY 1 const 1 Using index
+SELECT a FROM t1 WHERE a=0;
+a
+
+DROP TABLE t1;
End of 5.1 tests
diff --git a/mysql-test/t/create.test b/mysql-test/t/create.test
index dd3037ce88d..341c019af6e 100644
--- a/mysql-test/t/create.test
+++ b/mysql-test/t/create.test
@@ -1122,6 +1122,35 @@ show create table t1;
drop table t1;
+--echo
+--echo Bug #26104 Bug on foreign key class constructor
+--echo
+--echo Check that ref_columns is initalized correctly in the constructor
+--echo and semantic checks in mysql_prepare_table work.
+--echo
+--echo We do not need a storage engine that supports foreign keys
+--echo for this test, as the checks are purely syntax-based, and the
+--echo syntax is supported for all engines.
+--echo
+--disable_warnings
+drop table if exists t1,t2;
+--enable_warnings
+
+create table t1(a int not null, b int not null, primary key (a, b));
+--error ER_WRONG_FK_DEF
+create table t2(a int not null, b int not null, c int not null, primary key (a),
+foreign key fk_bug26104 (b,c) references t1(a));
+drop table t1;
+
+#
+# Bug#15130:CREATE .. SELECT was denied to use advantages of the SQL_BIG_RESULT.
+#
+create table t1(f1 int,f2 int);
+insert into t1 value(1,1),(1,2),(1,3),(2,1),(2,2),(2,3);
+flush status;
+create table t2 select sql_big_result f1,count(f2) from t1 group by f1;
+show status like 'handler_read%';
+drop table t1,t2;
--echo End of 5.0 tests
diff --git a/mysql-test/t/having.test b/mysql-test/t/having.test
index 41266cc6d32..683abfd3783 100644
--- a/mysql-test/t/having.test
+++ b/mysql-test/t/having.test
@@ -151,6 +151,32 @@ EXPLAIN SELECT 0 AS x, a FROM t1 GROUP BY x,a HAVING x=1 AND a > 1;
DROP table t1;
+#
+# Bug #29911: HAVING clause depending on constant table and evaluated to false
+#
+
+CREATE TABLE t1 (a int PRIMARY KEY);
+CREATE TABLE t2 (b int PRIMARY KEY, a int);
+CREATE TABLE t3 (b int, flag int);
+
+INSERT INTO t1 VALUES (1);
+INSERT INTO t2 VALUES (1,1), (2,1), (3,1);
+INSERT INTO t3(b,flag) VALUES (2, 1);
+
+SELECT t1.a
+ FROM t1 INNER JOIN t2 ON t1.a=t2.a LEFT JOIN t3 ON t2.b=t3.b
+ GROUP BY t1.a, t2.b HAVING MAX(t3.flag)=0;
+
+SELECT DISTINCT t1.a, MAX(t3.flag)
+ FROM t1 INNER JOIN t2 ON t1.a=t2.a LEFT JOIN t3 ON t2.b=t3.b
+ GROUP BY t1.a, t2.b HAVING MAX(t3.flag)=0;
+
+SELECT DISTINCT t1.a
+ FROM t1 INNER JOIN t2 ON t1.a=t2.a LEFT JOIN t3 ON t2.b=t3.b
+ GROUP BY t1.a, t2.b HAVING MAX(t3.flag)=0;
+
+DROP TABLE t1,t2,t3;
+
# End of 4.1 tests
#
diff --git a/mysql-test/t/innodb.test b/mysql-test/t/innodb.test
index 96b2ffda7d6..140aac3b9c3 100644
--- a/mysql-test/t/innodb.test
+++ b/mysql-test/t/innodb.test
@@ -1178,7 +1178,7 @@ drop table t2;
# Clean up filename -- embedded server reports whole path without .frm,
# regular server reports relative path with .frm (argh!)
--replace_result \\ / $MYSQL_TEST_DIR . /var/master-data/ / t2.frm t2
---error 1005
+--error ER_WRONG_FK_DEF
create table t2 (id int(11) not null, id2 int(11) not null, constraint t1_id_fk foreign key (id2,id) references t1 (id)) engine = innodb;
# bug#3749
diff --git a/mysql-test/t/mysqldump.test b/mysql-test/t/mysqldump.test
index 38d8a07fe48..4e8d3ae916e 100644
--- a/mysql-test/t/mysqldump.test
+++ b/mysql-test/t/mysqldump.test
@@ -1555,6 +1555,28 @@ SELECT * FROM v1;
DROP VIEW v1;
--echo #
+--echo # Bug #29788: mysqldump discards the NO_AUTO_VALUE_ON_ZERO value of
+--echo # the SQL_MODE variable after the dumping of triggers.
+--echo #
+
+CREATE TABLE t1 (c1 INT);
+CREATE TRIGGER t1bd BEFORE DELETE ON t1 FOR EACH ROW BEGIN END;
+
+CREATE TABLE t2 (c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
+
+SET @TMP_SQL_MODE = @@SQL_MODE;
+SET SQL_MODE = 'NO_AUTO_VALUE_ON_ZERO';
+INSERT INTO t2 VALUES (0), (1), (2);
+SET SQL_MODE = @TMP_SQL_MODE;
+SELECT * FROM t2;
+
+--exec $MYSQL_DUMP --routines test >$MYSQLTEST_VARDIR/tmp/bug29788.sql
+--exec $MYSQL test < $MYSQLTEST_VARDIR/tmp/bug29788.sql
+SELECT * FROM t2;
+
+DROP TABLE t1,t2;
+
+--echo #
--echo # End of 5.0 tests
--echo #
diff --git a/mysql-test/t/sp-prelocking.test b/mysql-test/t/sp-prelocking.test
index ec5b7fbad7c..60e97260839 100644
--- a/mysql-test/t/sp-prelocking.test
+++ b/mysql-test/t/sp-prelocking.test
@@ -333,4 +333,27 @@ insert into bug_27907_t1(a) values (1);
drop table bug_27907_t1;
+--echo
+--echo Bug#22427 create table if not exists + stored function results in
+--echo inconsistent behavior
+--echo
+--echo Add a test case, the bug itself was fixed by the patch for
+--echo Bug#20662
+--echo
+--disable_warnings
+drop table if exists t1;
+drop function if exists f_bug22427;
+--enable_warnings
+create table t1 (i int);
+insert into t1 values (1);
+create function f_bug22427() returns int return (select max(i) from t1);
+select f_bug22427();
+# Until this bug was fixed, the following emitted error
+# ERROR 1213: Deadlock found when trying to get lock
+create table if not exists t1 select f_bug22427() as i;
+--error ER_TABLE_EXISTS_ERROR
+create table t1 select f_bug22427() as i;
+drop table t1;
+drop function f_bug22427;
+
--echo End of 5.0 tests
diff --git a/mysql-test/t/sp.test b/mysql-test/t/sp.test
index 7bd20d226c2..dfda1b7a6ab 100644
--- a/mysql-test/t/sp.test
+++ b/mysql-test/t/sp.test
@@ -24,6 +24,8 @@ use test;
#
--disable_warnings
drop table if exists t1,t2,t3,t4;
+drop function if exists f1;
+drop function if exists f2;
--enable_warnings
create table t1 (
id char(16) not null default '',
@@ -7214,7 +7216,7 @@ CREATE TABLE t2 (a int auto_increment primary key, b int) engine=innodb;
set @a=0;
delimiter |;
-CREATE function bug27354() RETURNS int deterministic
+CREATE function bug27354() RETURNS int not deterministic
begin
insert into t1 values (null);
set @a=@a+1;
@@ -7368,3 +7370,33 @@ where ROUTINE_NAME = "proc_26302";
drop procedure proc_26302;
+# Bug #29338: no optimization for stored functions with a trivial body
+# always returning constant.
+#
+
+CREATE FUNCTION f1() RETURNS INT DETERMINISTIC RETURN 2;
+CREATE FUNCTION f2(I INT) RETURNS INT DETERMINISTIC RETURN 3;
+
+CREATE TABLE t1 (c1 INT, INDEX(c1));
+
+INSERT INTO t1 VALUES (1), (2), (3), (4), (5);
+
+CREATE VIEW v1 AS SELECT c1 FROM t1;
+
+EXPLAIN SELECT * FROM t1 WHERE c1=1;
+EXPLAIN SELECT * FROM t1 WHERE c1=f1();
+
+EXPLAIN SELECT * FROM v1 WHERE c1=1;
+EXPLAIN SELECT * FROM v1 WHERE c1=f1();
+
+EXPLAIN SELECT * FROM t1 WHERE c1=f2(10);
+EXPLAIN SELECT * FROM t1 WHERE c1=f2(c1);
+EXPLAIN SELECT * FROM t1 WHERE c1=f2(rand());
+
+
+DROP VIEW v1;
+DROP FUNCTION f1;
+DROP FUNCTION f2;
+DROP TABLE t1;
+
+--echo End of 5.0 tests
diff --git a/mysql-test/t/trigger.test b/mysql-test/t/trigger.test
index 8db432b27c3..c7251040544 100644
--- a/mysql-test/t/trigger.test
+++ b/mysql-test/t/trigger.test
@@ -2194,6 +2194,29 @@ drop table t1, t2, t1_op_log;
#
# TODO: test LOAD DATA INFILE
+#
+--echo
+--echo Bug#27248 Triggers: error if insert affects temporary table
+--echo
+--echo The bug was fixed by the fix for Bug#26141
+--echo
+--disable_warnings
+drop table if exists t1;
+drop temporary table if exists t2;
+--enable_warnings
+create table t1 (s1 int);
+create temporary table t2 (s1 int);
+create trigger t1_bi before insert on t1 for each row insert into t2 values (0);
+create trigger t1_bd before delete on t1 for each row delete from t2;
+insert into t1 values (0);
+insert into t1 values (0);
+select * from t1;
+select * from t2;
+delete from t1;
+select * from t1;
+select * from t2;
+drop table t1;
+drop temporary table t2;
--echo End of 5.0 tests
#
diff --git a/mysql-test/t/type_enum.test b/mysql-test/t/type_enum.test
index 4d5d5ec8a86..2043342e2c8 100644
--- a/mysql-test/t/type_enum.test
+++ b/mysql-test/t/type_enum.test
@@ -200,4 +200,24 @@ CREATE TABLE t2 SELECT * FROM t1;
SELECT c1 + 0 FROM t2;
DROP TABLE t1,t2;
+
+#
+# Bug#29661: Lookup by 0 for a primary index over a enum type
+#
+
+CREATE TABLE t1(a enum('a','b','c','d'));
+INSERT INTO t1 VALUES (4),(1),(0),(3);
+
+SELECT a FROM t1;
+
+EXPLAIN SELECT a FROM t1 WHERE a=0;
+SELECT a FROM t1 WHERE a=0;
+
+ALTER TABLE t1 ADD PRIMARY KEY (a);
+
+EXPLAIN SELECT a FROM t1 WHERE a=0;
+SELECT a FROM t1 WHERE a=0;
+
+DROP TABLE t1;
+
--echo End of 5.1 tests
View Lorry Controller Status