summaryrefslogtreecommitdiff
path: root/mysql-test
diff options
context:
space:
mode:
Diffstat (limited to 'mysql-test')
-rw-r--r--mysql-test/include/have_bug25714.inc7
-rwxr-xr-xmysql-test/mysql-test-run.pl12
-rw-r--r--mysql-test/r/alter_table.result12
-rw-r--r--mysql-test/r/check.result8
-rw-r--r--mysql-test/r/create.result13
-rw-r--r--mysql-test/r/federated.result11
-rw-r--r--mysql-test/r/federated_bug_25714.result56
-rw-r--r--mysql-test/r/have_bug25714.require2
-rw-r--r--mysql-test/r/having.result19
-rw-r--r--mysql-test/r/innodb_mysql.result268
-rw-r--r--mysql-test/r/mysqldump.result38
-rw-r--r--mysql-test/r/sp.result34
-rw-r--r--mysql-test/r/type_enum.result24
-rw-r--r--mysql-test/t/alter_table.test12
-rw-r--r--mysql-test/t/check.test9
-rw-r--r--mysql-test/t/create.test10
-rw-r--r--mysql-test/t/federated.test9
-rw-r--r--mysql-test/t/federated_bug_25714.test47
-rw-r--r--mysql-test/t/having.test26
-rw-r--r--mysql-test/t/innodb_mysql.test86
-rw-r--r--mysql-test/t/mysqldump.test22
-rw-r--r--mysql-test/t/sp.test34
-rw-r--r--mysql-test/t/type_enum.test24
23 files changed, 769 insertions, 14 deletions
diff --git a/mysql-test/include/have_bug25714.inc b/mysql-test/include/have_bug25714.inc
new file mode 100644
index 00000000000..0c995cd0d4c
--- /dev/null
+++ b/mysql-test/include/have_bug25714.inc
@@ -0,0 +1,7 @@
+#
+# Check if the variable MYSQL_BUG25714 is set
+#
+--require r/have_bug25714.require
+disable_query_log;
+eval select LENGTH("MYSQL_BUG25714") > 0 as "have_bug25714_exe";
+enable_query_log;
diff --git a/mysql-test/mysql-test-run.pl b/mysql-test/mysql-test-run.pl
index c58ed308bd8..43b1ef7ac86 100755
--- a/mysql-test/mysql-test-run.pl
+++ b/mysql-test/mysql-test-run.pl
@@ -144,6 +144,7 @@ our $exe_mysqladmin;
our $exe_mysql_upgrade;
our $exe_mysqlbinlog;
our $exe_mysql_client_test;
+our $exe_bug25714;
our $exe_mysqld;
our $exe_mysqlcheck;
our $exe_mysqldump;
@@ -1630,6 +1631,12 @@ sub executable_setup () {
"$glob_basedir/tests/mysql_client_test",
"$glob_basedir/bin/mysql_client_test");
}
+
+ # Look for bug25714 executable which may _not_ exist in
+ # some versions, test using it should be skipped
+ $exe_bug25714=
+ mtr_exe_maybe_exists(vs_config_dirs('tests', 'bug25714'),
+ "$glob_basedir/tests/bug25714");
}
@@ -2011,6 +2018,11 @@ sub environment_setup () {
$ENV{'MYSQL'}= $cmdline_mysql;
# ----------------------------------------------------
+ # Setup env so childs can execute bug25714
+ # ----------------------------------------------------
+ $ENV{'MYSQL_BUG25714'}= $exe_bug25714;
+
+ # ----------------------------------------------------
# Setup env so childs can execute mysql_client_test
# ----------------------------------------------------
$ENV{'MYSQL_CLIENT_TEST'}= mysql_client_test_arguments();
diff --git a/mysql-test/r/alter_table.result b/mysql-test/r/alter_table.result
index 4eace9ac628..f24a1788404 100644
--- a/mysql-test/r/alter_table.result
+++ b/mysql-test/r/alter_table.result
@@ -903,3 +903,15 @@ f1 f2 f21 f4 f41
1 2000-01-01 00:00:00 2000-01-01 2002-02-02 00:00:00 2002-02-02
drop table t1;
set sql_mode= @orig_sql_mode;
+create table t1 (c char(10) default "Two");
+lock table t1 write;
+insert into t1 values ();
+alter table t1 modify c char(10) default "Three";
+unlock tables;
+select * from t1;
+c
+Two
+check table t1;
+Table Op Msg_type Msg_text
+test.t1 check status OK
+drop table t1;
diff --git a/mysql-test/r/check.result b/mysql-test/r/check.result
index 60806e7393e..03219d0977e 100644
--- a/mysql-test/r/check.result
+++ b/mysql-test/r/check.result
@@ -14,3 +14,11 @@ test.v1 check status OK
test.t2 check status OK
drop view v1;
drop table t1, t2;
+CREATE TEMPORARY TABLE t1(a INT);
+CHECK TABLE t1;
+Table Op Msg_type Msg_text
+test.t1 check status OK
+REPAIR TABLE t1;
+Table Op Msg_type Msg_text
+test.t1 repair status OK
+DROP TABLE t1;
diff --git a/mysql-test/r/create.result b/mysql-test/r/create.result
index a1843573794..3cba394189a 100644
--- a/mysql-test/r/create.result
+++ b/mysql-test/r/create.result
@@ -1519,4 +1519,17 @@ 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
diff --git a/mysql-test/r/federated.result b/mysql-test/r/federated.result
index 4bef92319fb..f4750fed201 100644
--- a/mysql-test/r/federated.result
+++ b/mysql-test/r/federated.result
@@ -40,17 +40,18 @@ CREATE TABLE federated.t1 (
)
ENGINE="FEDERATED" DEFAULT CHARSET=latin1
CONNECTION='mysql://root@127.0.0.1:SLAVE_PORT/federated/t3';
-ERROR HY000: Can't create federated table. Foreign data src error: error: 1146 'Table 'federated.t3' doesn't exist'
+SELECT * FROM federated.t1;
+ERROR HY000: The foreign data source you are trying to reference does not exist. Data source error: error: 1146 'Table 'federated.t3' doesn't exist'
+DROP TABLE federated.t1;
CREATE TABLE federated.t1 (
`id` int(20) NOT NULL,
`name` varchar(32) NOT NULL default ''
)
ENGINE="FEDERATED" DEFAULT CHARSET=latin1
CONNECTION='mysql://user:pass@127.0.0.1:SLAVE_PORT/federated/t1';
-ERROR HY000: Unable to connect to foreign data source: database: 'federated' username: 'user' hostname: '127.0.0.1'
-DROP TABLE IF EXISTS federated.t1;
-Warnings:
-Note 1051 Unknown table 't1'
+SELECT * FROM federated.t1;
+ERROR HY000: Unable to connect to foreign data source: Access denied for user 'user'@'localhost' (using password: YES)
+DROP TABLE federated.t1;
CREATE TABLE federated.t1 (
`id` int(20) NOT NULL,
`name` varchar(32) NOT NULL default ''
diff --git a/mysql-test/r/federated_bug_25714.result b/mysql-test/r/federated_bug_25714.result
new file mode 100644
index 00000000000..12554f7af3a
--- /dev/null
+++ b/mysql-test/r/federated_bug_25714.result
@@ -0,0 +1,56 @@
+stop slave;
+drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
+reset master;
+reset slave;
+drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
+start slave;
+stop slave;
+DROP DATABASE IF EXISTS federated;
+CREATE DATABASE federated;
+DROP DATABASE IF EXISTS federated;
+CREATE DATABASE federated;
+DROP TABLE IF EXISTS federated.bug_13118_table;
+CREATE TABLE federated.t1 (
+`id` int auto_increment primary key,
+`value` int
+) ENGINE=MyISAM;
+INSERT INTO federated.t1 SET value=1;
+INSERT INTO federated.t1 SET value=2;
+INSERT INTO federated.t1 SET value=2;
+DROP TABLE IF EXISTS federated.t1;
+CREATE TABLE federated.t1 (
+`id` int auto_increment primary key,
+`value` int
+) ENGINE=FEDERATED
+CONNECTION='mysql://root@127.0.0.1:SLAVE_PORT/federated/t1';
+SELECT * from federated.t1;
+id value
+1 1
+2 2
+3 2
+INSERT INTO federated.t1 SET value=4;
+SELECT LAST_INSERT_ID();
+LAST_INSERT_ID()
+4
+
+5 inserted
+6 inserted
+
+7 inserted
+8 inserted
+SELECT * from federated.t1;
+id value
+1 1
+2 2
+3 2
+4 4
+5 54
+6 55
+7 54
+8 55
+DROP TABLE federated.t1;
+DROP TABLE federated.t1;
+DROP TABLE IF EXISTS federated.t1;
+DROP DATABASE IF EXISTS federated;
+DROP TABLE IF EXISTS federated.t1;
+DROP DATABASE IF EXISTS federated;
diff --git a/mysql-test/r/have_bug25714.require b/mysql-test/r/have_bug25714.require
new file mode 100644
index 00000000000..5acc378dcf7
--- /dev/null
+++ b/mysql-test/r/have_bug25714.require
@@ -0,0 +1,2 @@
+have_bug25714_exe
+1
diff --git a/mysql-test/r/having.result b/mysql-test/r/having.result
index 9b131109809..f113304c767 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_mysql.result b/mysql-test/r/innodb_mysql.result
index 6fb98f509ef..7f60d785091 100644
--- a/mysql-test/r/innodb_mysql.result
+++ b/mysql-test/r/innodb_mysql.result
@@ -767,4 +767,272 @@ select * from t24918_access;
ERROR HY000: Table 't24918_access' was not locked with LOCK TABLES
unlock tables;
drop table t24918_access;
+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
diff --git a/mysql-test/r/mysqldump.result b/mysql-test/r/mysqldump.result
index 73841a2150f..42c437857b7 100644
--- a/mysql-test/r/mysqldump.result
+++ b/mysql-test/r/mysqldump.result
@@ -2361,6 +2361,8 @@ INSERT INTO `t1` VALUES (1,NULL),(2,NULL),(4,NULL),(11,NULL);
/*!40000 ALTER TABLE `t1` ENABLE KEYS */;
UNLOCK TABLES;
+/*!50003 SET @SAVE_SQL_MODE=@@SQL_MODE*/;
+
DELIMITER ;;
/*!50003 SET SESSION SQL_MODE="" */;;
/*!50003 CREATE */ /*!50017 DEFINER=`root`@`localhost` */ /*!50003 TRIGGER `trg1` BEFORE INSERT ON `t1` FOR EACH ROW begin
@@ -2383,7 +2385,7 @@ end if;
end */;;
DELIMITER ;
-/*!50003 SET SESSION SQL_MODE=@OLD_SQL_MODE */;
+/*!50003 SET SESSION SQL_MODE=@SAVE_SQL_MODE*/;
DROP TABLE IF EXISTS `t2`;
SET @saved_cs_client = @@character_set_client;
SET character_set_client = utf8;
@@ -2397,6 +2399,8 @@ LOCK TABLES `t2` WRITE;
/*!40000 ALTER TABLE `t2` ENABLE KEYS */;
UNLOCK TABLES;
+/*!50003 SET @SAVE_SQL_MODE=@@SQL_MODE*/;
+
DELIMITER ;;
/*!50003 SET SESSION SQL_MODE="STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER" */;;
/*!50003 CREATE */ /*!50017 DEFINER=`root`@`localhost` */ /*!50003 TRIGGER `trg4` BEFORE INSERT ON `t2` FOR EACH ROW begin
@@ -2406,7 +2410,7 @@ end if;
end */;;
DELIMITER ;
-/*!50003 SET SESSION SQL_MODE=@OLD_SQL_MODE */;
+/*!50003 SET SESSION SQL_MODE=@SAVE_SQL_MODE*/;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
@@ -2772,13 +2776,15 @@ INSERT INTO "t1 test" VALUES (1),(2),(3);
/*!40000 ALTER TABLE "t1 test" ENABLE KEYS */;
UNLOCK TABLES;
+/*!50003 SET @SAVE_SQL_MODE=@@SQL_MODE*/;
+
DELIMITER ;;
/*!50003 SET SESSION SQL_MODE="" */;;
/*!50003 CREATE */ /*!50017 DEFINER=`root`@`localhost` */ /*!50003 TRIGGER `test trig` BEFORE INSERT ON `t1 test` FOR EACH ROW BEGIN
INSERT INTO `t2 test` SET a2 = NEW.a1; END */;;
DELIMITER ;
-/*!50003 SET SESSION SQL_MODE=@OLD_SQL_MODE */;
+/*!50003 SET SESSION SQL_MODE=@SAVE_SQL_MODE*/;
DROP TABLE IF EXISTS "t2 test";
SET @saved_cs_client = @@character_set_client;
SET character_set_client = utf8;
@@ -2941,6 +2947,8 @@ LOCK TABLES `t1` WRITE;
/*!40000 ALTER TABLE `t1` ENABLE KEYS */;
UNLOCK TABLES;
+/*!50003 SET @SAVE_SQL_MODE=@@SQL_MODE*/;
+
DELIMITER ;;
/*!50003 SET SESSION SQL_MODE="IGNORE_SPACE" */;;
/*!50003 CREATE */ /*!50017 DEFINER=`root`@`localhost` */ /*!50003 TRIGGER `tr1` BEFORE INSERT ON `t1` FOR EACH ROW BEGIN
@@ -2948,7 +2956,7 @@ SET new.a = 0;
END */;;
DELIMITER ;
-/*!50003 SET SESSION SQL_MODE=@OLD_SQL_MODE */;
+/*!50003 SET SESSION SQL_MODE=@SAVE_SQL_MODE*/;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
@@ -3514,5 +3522,27 @@ 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
#
diff --git a/mysql-test/r/sp.result b/mysql-test/r/sp.result
index 7a4deb3ea5f..448428d07f4 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
@@ -6144,7 +6146,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;
@@ -6201,4 +6203,34 @@ Level Code Message
use test;
drop procedure sp_bug29050;
drop table t1;
+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/type_enum.result b/mysql-test/r/type_enum.result
index ca516f027ba..994001d94e5 100644
--- a/mysql-test/r/type_enum.result
+++ b/mysql-test/r/type_enum.result
@@ -1829,3 +1829,27 @@ 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;
diff --git a/mysql-test/t/alter_table.test b/mysql-test/t/alter_table.test
index 3ced1087757..bcca122f9f8 100644
--- a/mysql-test/t/alter_table.test
+++ b/mysql-test/t/alter_table.test
@@ -684,3 +684,15 @@ alter table t1 add column f4 datetime not null default '2002-02-02',
select * from t1;
drop table t1;
set sql_mode= @orig_sql_mode;
+
+#
+# BUG#29957 - alter_table.test fails
+#
+create table t1 (c char(10) default "Two");
+lock table t1 write;
+insert into t1 values ();
+alter table t1 modify c char(10) default "Three";
+unlock tables;
+select * from t1;
+check table t1;
+drop table t1;
diff --git a/mysql-test/t/check.test b/mysql-test/t/check.test
index 8d9d70bd29a..698f6538529 100644
--- a/mysql-test/t/check.test
+++ b/mysql-test/t/check.test
@@ -36,3 +36,12 @@ Create view v1 as Select * from t1;
Check Table v1,t2;
drop view v1;
drop table t1, t2;
+
+#
+# BUG#26325 - TEMPORARY TABLE "corrupt" after first read, according to CHECK
+# TABLE
+#
+CREATE TEMPORARY TABLE t1(a INT);
+CHECK TABLE t1;
+REPAIR TABLE t1;
+DROP TABLE t1;
diff --git a/mysql-test/t/create.test b/mysql-test/t/create.test
index a6679cd674a..64081c0248a 100644
--- a/mysql-test/t/create.test
+++ b/mysql-test/t/create.test
@@ -1138,4 +1138,14 @@ 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/federated.test b/mysql-test/t/federated.test
index bedb6b36d61..a3750cb572d 100644
--- a/mysql-test/t/federated.test
+++ b/mysql-test/t/federated.test
@@ -30,25 +30,28 @@ CREATE TABLE federated.t1 (
# test non-existant table
--replace_result $SLAVE_MYPORT SLAVE_PORT
---error 1434
eval CREATE TABLE federated.t1 (
`id` int(20) NOT NULL,
`name` varchar(32) NOT NULL default ''
)
ENGINE="FEDERATED" DEFAULT CHARSET=latin1
CONNECTION='mysql://root@127.0.0.1:$SLAVE_MYPORT/federated/t3';
+--error 1431
+SELECT * FROM federated.t1;
+DROP TABLE federated.t1;
# test bad user/password
--replace_result $SLAVE_MYPORT SLAVE_PORT
---error 1429
eval CREATE TABLE federated.t1 (
`id` int(20) NOT NULL,
`name` varchar(32) NOT NULL default ''
)
ENGINE="FEDERATED" DEFAULT CHARSET=latin1
CONNECTION='mysql://user:pass@127.0.0.1:$SLAVE_MYPORT/federated/t1';
+--error 1429
+SELECT * FROM federated.t1;
+DROP TABLE federated.t1;
-DROP TABLE IF EXISTS federated.t1;
# # correct connection, same named tables
--replace_result $SLAVE_MYPORT SLAVE_PORT
eval CREATE TABLE federated.t1 (
diff --git a/mysql-test/t/federated_bug_25714.test b/mysql-test/t/federated_bug_25714.test
new file mode 100644
index 00000000000..9c185181511
--- /dev/null
+++ b/mysql-test/t/federated_bug_25714.test
@@ -0,0 +1,47 @@
+--source include/have_bug25714.inc
+source include/federated.inc;
+
+
+connection slave;
+--disable_warnings
+DROP TABLE IF EXISTS federated.bug_13118_table;
+--enable_warnings
+
+CREATE TABLE federated.t1 (
+ `id` int auto_increment primary key,
+ `value` int
+ ) ENGINE=MyISAM;
+INSERT INTO federated.t1 SET value=1;
+INSERT INTO federated.t1 SET value=2;
+INSERT INTO federated.t1 SET value=2;
+
+connection master;
+--disable_warnings
+DROP TABLE IF EXISTS federated.t1;
+--enable_warnings
+
+--replace_result $SLAVE_MYPORT SLAVE_PORT
+eval CREATE TABLE federated.t1 (
+ `id` int auto_increment primary key,
+ `value` int
+ ) ENGINE=FEDERATED
+ CONNECTION='mysql://root@127.0.0.1:$SLAVE_MYPORT/federated/t1';
+
+SELECT * from federated.t1;
+
+INSERT INTO federated.t1 SET value=4;
+
+SELECT LAST_INSERT_ID();
+
+--exec $MYSQL_BUG25714 $SLAVE_MYPORT
+--exec $MYSQL_BUG25714 $MASTER_MYPORT
+
+SELECT * from federated.t1;
+
+DROP TABLE federated.t1;
+connection slave;
+DROP TABLE federated.t1;
+
+
+source include/federated_cleanup.inc;
+
diff --git a/mysql-test/t/having.test b/mysql-test/t/having.test
index 9bea78a7bca..827b83f11a0 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_mysql.test b/mysql-test/t/innodb_mysql.test
index 60d46863bfd..74c93b5e984 100644
--- a/mysql-test/t/innodb_mysql.test
+++ b/mysql-test/t/innodb_mysql.test
@@ -740,6 +740,8 @@ SELECT COUNT(*) FROM t1 FORCE INDEX(idx_b, idx_c)
set @@sort_buffer_size=default;
DROP TABLE t1,t2;
+
+
# Bug#27296 Assertion in ALTER TABLE SET DEFAULT in Linux Debug build
# (possible deadlock).
#
@@ -787,5 +789,89 @@ select * from t24918_access;
unlock tables;
drop table t24918_access;
+#
+# 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/t/mysqldump.test b/mysql-test/t/mysqldump.test
index d42162541de..3c62577e781 100644
--- a/mysql-test/t/mysqldump.test
+++ b/mysql-test/t/mysqldump.test
@@ -1555,5 +1555,27 @@ 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.test b/mysql-test/t/sp.test
index ef66f1c98f0..5aaf4f21ae0 100644
--- a/mysql-test/t/sp.test
+++ b/mysql-test/t/sp.test
@@ -23,6 +23,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 '',
@@ -7098,7 +7100,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;
@@ -7172,4 +7174,34 @@ use test;
drop procedure sp_bug29050;
drop table t1;
+#
+# 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/type_enum.test b/mysql-test/t/type_enum.test
index fbba38f926d..3dedb0018b1 100644
--- a/mysql-test/t/type_enum.test
+++ b/mysql-test/t/type_enum.test
@@ -200,3 +200,27 @@ 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;
+
+
+
+
+
View Lorry Controller Status