Merge main 5.0 into 5.0-build

17 files changed, 357 insertions, 25 deletions

diff --git a/mysql-test/t/disabled.def b/mysql-test/t/disabled.def
index 49f422af298..e9de29e2c27 100644
--- a/mysql-test/t/disabled.def
+++ b/mysql-test/t/disabled.def
@@ -16,7 +16,6 @@ im_daemon_life_cycle : Bug#20294: Instance manager tests fail randomly
 im_options_set       : Bug#20294: Instance manager tests fail randomly
 im_options_unset     : Bug#20294: Instance manager tests fail randomly
 im_utils             : Bug#20294: Instance manager tests fail randomly
-grant_cache          : Bug#32651: grant_cache.test fails
 ndb_backup_print     : Bug#32357: ndb_backup_print test fails sometimes in pushbuild
 rpl_log_pos          : Bug#8693 Test 'rpl_log_pos' fails sometimes
 kill                 : Bug#29149 Test "kill" fails on Windows
diff --git a/mysql-test/t/func_compress.test b/mysql-test/t/func_compress.test
index 68f07f258bf..c84f22c5595 100644
--- a/mysql-test/t/func_compress.test
+++ b/mysql-test/t/func_compress.test
@@ -82,4 +82,24 @@ select *, uncompress(a) from t1;
 select *, uncompress(a), uncompress(a) is null from t1;
 drop table t1;
 
+#
+# Bug #44796: valgrind: too many my_longlong10_to_str_8bit warnings after 
+#             uncompressed_length
+#
+
+CREATE TABLE t1 (c1 INT);
+INSERT INTO t1 VALUES (1), (1111), (11111);
+
+# Disable warnings to avoid dependency on max_allowed_packet value
+--disable_warnings
+SELECT UNCOMPRESS(c1), UNCOMPRESSED_LENGTH(c1) FROM t1;
+--enable_warnings
+
+# We do not need the results, just make sure there are no valgrind errors
+--disable_result_log
+EXPLAIN EXTENDED SELECT * FROM (SELECT UNCOMPRESSED_LENGTH(c1) FROM t1) AS s;
+--enable_result_log
+
+DROP TABLE t1;
+
 --echo End of 5.0 tests
diff --git a/mysql-test/t/func_str.test b/mysql-test/t/func_str.test
index 389538c4cc0..4b0f91e4408 100644
--- a/mysql-test/t/func_str.test
+++ b/mysql-test/t/func_str.test
@@ -1168,4 +1168,14 @@ INSERT INTO t1 VALUES ('2008-12-31','aaaaaa');
 SELECT DATE_FORMAT(c, GET_FORMAT(DATE, 'eur')) h, CONCAT(UPPER(aa),', ', aa) i FROM t1;
 DROP TABLE t1;
 
+
+--echo #
+--echo # BUG#44774: load_file function produces valgrind warnings
+--echo #
+CREATE TABLE t1 (a TINYBLOB);
+INSERT INTO t1 VALUES ('aaaaaaaa');
+SELECT LOAD_FILE(a) FROM t1;
+DROP TABLE t1;
+
+
 --echo End of 5.0 tests
diff --git a/mysql-test/t/grant_cache.test b/mysql-test/t/grant_cache.test
index c2e31621744..616a2746148 100644
--- a/mysql-test/t/grant_cache.test
+++ b/mysql-test/t/grant_cache.test
@@ -88,7 +88,7 @@ select a from t1;
 select c from t1;
 select * from mysqltest.t1,test.t1;
 --replace_result 127.0.0.1 localhost
---error 1142
+--error ER_TABLEACCESS_DENIED_ERROR
 select * from t2;
 show status like "Qcache_queries_in_cache";
 show status like "Qcache_hits";
@@ -99,17 +99,17 @@ connect (user3,localhost,mysqltest_3,,mysqltest,$MASTER_MYPORT,$MASTER_MYSOCK);
 connection user3;
 select "user3";
 --replace_result 127.0.0.1 localhost
---error 1143
+--error ER_TABLEACCESS_DENIED_ERROR
 select * from t1;
 select a from t1;
 --replace_result 127.0.0.1 localhost
---error 1143
+--error ER_COLUMNACCESS_DENIED_ERROR
 select c from t1;
 --replace_result 127.0.0.1 localhost
---error 1142
+--error ER_TABLEACCESS_DENIED_ERROR
 select * from t2;
 --replace_result 127.0.0.1 localhost
---error 1143
+--error ER_COLUMNACCESS_DENIED_ERROR
 select mysqltest.t1.c from test.t1,mysqltest.t1;
 show status like "Qcache_queries_in_cache";
 show status like "Qcache_hits";
@@ -120,7 +120,7 @@ connect (user4,localhost,mysqltest_1,,*NO-ONE*,$MASTER_MYPORT,$MASTER_MYSOCK);
 connection user4;
 select "user4";
 show grants;
---error 1046
+--error ER_NO_DB_ERROR
 select a from t1;
 # The following query is not cached before (different database)
 select * from mysqltest.t1,test.t1;
diff --git a/mysql-test/t/heap_btree.test b/mysql-test/t/heap_btree.test
index 76c319c0bc1..710c8411751 100644
--- a/mysql-test/t/heap_btree.test
+++ b/mysql-test/t/heap_btree.test
@@ -253,5 +253,13 @@ insert into t1 values (1, 1), (3, 3), (2, 2), (NULL, 1), (NULL, NULL), (0, 0);
 select * from t1 where a is null;
 drop table t1;
 
+-- echo #
+-- echo # bug#39918 - memory (heap) engine crashing while executing self join with delete
+-- echo #
+
+CREATE TABLE t1(a INT, KEY USING BTREE (a)) ENGINE=MEMORY;
+INSERT INTO t1 VALUES(1),(1);
+DELETE a1 FROM t1 AS a1, t1 AS a2 WHERE a1.a=a2.a;
+DROP TABLE t1;
 --echo End of 5.0 tests
 
diff --git a/mysql-test/t/information_schema_db.test b/mysql-test/t/information_schema_db.test
index 6353e94fd51..0ff1d05f364 100644
--- a/mysql-test/t/information_schema_db.test
+++ b/mysql-test/t/information_schema_db.test
@@ -53,7 +53,7 @@ order by table_name;
 end|
 delimiter ;|
 
-create table t1 
+create table t1
 (f1 int(10) unsigned not null,
  f2 varchar(100) not null,
  primary key (f1), unique key (f2));
@@ -105,8 +105,8 @@ drop function f2;
 drop view v1, v2;
 
 #
-# Bug#20543: select on information_schema strange warnings, view, different
-#             schemas/users
+# Bug#20543 select on information_schema strange warnings, view, different
+#           schemas/users
 #
 #
 create database testdb_1;
@@ -125,7 +125,7 @@ grant insert on v1 to testdb_2@localhost;
 create view v5 as select f1 from t1;
 grant show view on v5 to testdb_2@localhost;
 
---error 1227
+--error ER_SPECIFIC_ACCESS_DENIED_ERROR
 create definer=`no_such_user`@`no_such_host` view v6 as select f1 from t1;
 
 connection default;
@@ -169,46 +169,53 @@ use testdb_1;
 revoke show view on v6 from testdb_2@localhost;
 connection testdb_2;
 
---error 1142
+--error ER_TABLEACCESS_DENIED_ERROR
 show fields from testdb_1.v5;
---error 1142
+--error ER_TABLEACCESS_DENIED_ERROR
 show create view testdb_1.v5;
 
---error 1142
+--error ER_TABLEACCESS_DENIED_ERROR
 show fields from testdb_1.v6;
---error 1142
+--error ER_TABLEACCESS_DENIED_ERROR
 show create view testdb_1.v6;
 
---error 1142
+--error ER_TABLEACCESS_DENIED_ERROR
 show fields from testdb_1.v7;
---error 1142
+--error ER_TABLEACCESS_DENIED_ERROR
 show create view testdb_1.v7;
 
---error 1345
+--error ER_VIEW_NO_EXPLAIN
 show create view v4;
-#--error 1345
+#--error ER_VIEW_NO_EXPLAIN
 show fields from v4;
 
 show fields from v2;
 show fields from testdb_1.v1;
 show create view v2;
---error 1142
+--error ER_TABLEACCESS_DENIED_ERROR
 show create view testdb_1.v1;
 
-select table_name from information_schema.columns a 
+select table_name from information_schema.columns a
 where a.table_name = 'v2';
-select view_definition from information_schema.views a 
+select view_definition from information_schema.views a
 where a.table_name = 'v2';
-select view_definition from information_schema.views a 
+select view_definition from information_schema.views a
 where a.table_name = 'testdb_1.v1';
 
---error 1356
+--error ER_VIEW_INVALID
 select * from v2;
 
 connection default;
 use test;
 drop view testdb_1.v1, v2, testdb_1.v3, v4;
 drop database testdb_1;
+connection testdb_1;
+disconnect testdb_1;
+--source include/wait_until_disconnected.inc
+connection testdb_2;
+disconnect testdb_2;
+--source include/wait_until_disconnected.inc
+connection default;
 drop user testdb_1@localhost;
 drop user testdb_2@localhost;
 
@@ -239,4 +246,7 @@ show create view testdb_1.v1;
 connection default;
 drop user mysqltest_1@localhost;
 drop database testdb_1;
+connection user1;
 disconnect user1;
+--source include/wait_until_disconnected.inc
+connection default;
diff --git a/mysql-test/t/innodb_mysql.test b/mysql-test/t/innodb_mysql.test
index 512f7fc7fe2..05be75fc368 100644
--- a/mysql-test/t/innodb_mysql.test
+++ b/mysql-test/t/innodb_mysql.test
@@ -1054,4 +1054,13 @@ ROLLBACK;
 --error 1305
 ROLLBACK TO SAVEPOINT s4;
 
+#
+# Bug#39793 Foreign keys not constructed when column has a '#' in a comment or default value
+#
+
+#This statement should be written on a single line for proper testing
+CREATE TABLE t1 (f1 INTEGER PRIMARY KEY COMMENT 'My ID#', f2 INTEGER DEFAULT NULL, f3 CHAR(10) DEFAULT 'My ID#', CONSTRAINT f2_ref FOREIGN KEY (f2) REFERENCES t1 (f1)) ENGINE=INNODB;
+SHOW CREATE TABLE t1;
+DROP TABLE t1;
+
 --echo End of 5.0 tests
diff --git a/mysql-test/t/mysql.test b/mysql-test/t/mysql.test
index 12431e26596..0425b4b9022 100644
--- a/mysql-test/t/mysql.test
+++ b/mysql-test/t/mysql.test
@@ -341,4 +341,11 @@ EOF
 
 remove_file $MYSQLTEST_VARDIR/tmp/bug31060.sql;
 
+#
+# Bug #39101: client -i (--ignore-spaces) option does not seem to work
+#
+--exec $MYSQL -i -e "SELECT COUNT (*)"
+--exec $MYSQL --ignore-spaces -e "SELECT COUNT (*)"
+--exec $MYSQL -b -i -e "SELECT COUNT (*)"
+
 --echo End of 5.0 tests
diff --git a/mysql-test/t/mysqlbinlog.test b/mysql-test/t/mysqlbinlog.test
index 1ca07a40df1..dd45f499866 100644
--- a/mysql-test/t/mysqlbinlog.test
+++ b/mysql-test/t/mysqlbinlog.test
@@ -294,4 +294,14 @@ echo *** Unsigned server_id $s_id_max is found: $s_id_unsigned ***;
 eval SET @@global.server_id= $save_server_id;
 --remove_file $binlog_file
 
+#
+# Bug #41943: mysqlbinlog.exe crashes if --hexdump option is used
+#
+
+RESET MASTER;
+FLUSH LOGS;
+
+# We do not need the results, just make sure that mysqlbinlog does not crash
+--exec $MYSQL_BINLOG --hexdump --read-from-remote-server --user=root --host=127.0.0.1 --port=$MASTER_MYPORT  master-bin.000001 >/dev/null
+
 --echo End of 5.0 tests
diff --git a/mysql-test/t/mysqldump.test b/mysql-test/t/mysqldump.test
index 1e9090fdd01..1aceb113b65 100644
--- a/mysql-test/t/mysqldump.test
+++ b/mysql-test/t/mysqldump.test
@@ -1,6 +1,5 @@
 # Embedded server doesn't support external clients
 --source include/not_embedded.inc
---source include/have_log_bin.inc
 
 # Binlog is required
 --source include/have_log_bin.inc
diff --git a/mysql-test/t/mysqldump_restore.test b/mysql-test/t/mysqldump_restore.test
new file mode 100644
index 00000000000..835ee3ee9e9
--- /dev/null
+++ b/mysql-test/t/mysqldump_restore.test
@@ -0,0 +1,111 @@
+###############################################################################
+# mysqldump_restore.test
+#
+# Purpose:  Tests if mysqldump output can be used to successfully restore
+#           tables and data.  
+#           We CREATE a table, mysqldump it to a file, ALTER the original
+#           table's name, recreate the table from the mysqldump file, then
+#           utilize include/diff_tables to compare the original and recreated
+#           tables.
+#
+#           We use several examples from mysqldump.test here and include
+#           the relevant bug numbers and headers from that test.
+#
+# NOTE:     This test is not currently complete and offers only basic
+#           cases of mysqldump output being restored. 
+#           Also, does NOT work with -X (xml) output!
+#           
+# Author:   pcrews
+# Created:  2009-05-21
+# Last Change:
+# Change date:
+###############################################################################
+
+# Embedded server doesn't support external clients
+--source include/not_embedded.inc
+--source include/have_log_bin.inc
+
+--echo # Set concurrent_insert = 0 to prevent random errors
+--echo # will reset to original value at the end of the test
+SET @old_concurrent_insert = @@global.concurrent_insert;
+SET @@global.concurrent_insert = 0;
+
+# Define mysqldumpfile here.  It is used to capture mysqldump output
+# in order to test the output's ability to restore an exact copy of the table
+let $mysqldumpfile = $MYSQLTEST_VARDIR/tmp/mysqldumpfile.sql;
+
+--echo # Pre-test cleanup
+--disable_warnings
+DROP TABLE IF EXISTS t1;
+--enable_warnings
+
+--echo # Begin tests
+--echo #
+--echo # Bug#2005 Long decimal comparison bug.
+--echo #
+CREATE TABLE t1 (a DECIMAL(64, 20));
+INSERT INTO t1 VALUES ("1234567890123456789012345678901234567890"),
+("0987654321098765432109876543210987654321");
+--exec $MYSQL_DUMP --compact test t1 > $mysqldumpfile
+let $table_name = test.t1;
+--source include/mysqldump.inc
+
+--echo #
+--echo # Bug#3361 mysqldump quotes DECIMAL values inconsistently
+--echo #
+CREATE TABLE t1 (a DECIMAL(10,5), b FLOAT);
+# Check at first how mysql work with quoted decimal
+INSERT INTO t1 VALUES (1.2345, 2.3456);
+INSERT INTO t1 VALUES ('1.2345', 2.3456);
+INSERT INTO t1 VALUES ("1.2345", 2.3456);
+SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='ANSI_QUOTES';
+INSERT INTO t1 VALUES (1.2345, 2.3456);
+INSERT INTO t1 VALUES ('1.2345', 2.3456);
+--error ER_BAD_FIELD_ERROR
+INSERT INTO t1 VALUES ("1.2345", 2.3456);
+SET SQL_MODE=@OLD_SQL_MODE;
+
+# check how mysqldump make quoting
+--exec $MYSQL_DUMP --compact test t1 > $mysqldumpfile
+let $table_name = test.t1;
+--source include/mysqldump.inc
+
+--echo #
+--echo # Bug#1994 mysqldump does not correctly dump UCS2 data
+--echo # Bug#4261 mysqldump 10.7 (mysql 4.1.2) --skip-extended-insert drops NULL from inserts
+--echo #
+CREATE TABLE t1 (a  VARCHAR(255)) DEFAULT CHARSET koi8r;
+INSERT INTO t1  VALUES (_koi8r x'C1C2C3C4C5'), (NULL);
+--exec $MYSQL_DUMP --skip-comments --skip-extended-insert test t1 > $mysqldumpfile
+let $table_name = test.t1;
+--source include/mysqldump.inc
+
+--echo #
+--echo # WL#2319 Exclude Tables from dump
+--echo #
+CREATE TABLE t1 (a INT);
+CREATE TABLE t2 (a INT);
+INSERT INTO t1 VALUES (1),(2),(3);
+INSERT INTO t2 VALUES (4),(5),(6);
+--exec $MYSQL_DUMP --skip-comments --ignore-table=test.t1 test > $mysqldumpfile
+let $table_name = test.t2;
+--source include/mysqldump.inc
+DROP TABLE t1;
+
+--echo #
+--echo # Bug#8830 mysqldump --skip-extended-insert causes --hex-blob to dump wrong values
+--echo #
+CREATE TABLE t1 (`b` blob);
+INSERT INTO `t1` VALUES (0x602010000280100005E71A);
+--exec $MYSQL_DUMP --skip-extended-insert --hex-blob test --skip-comments t1 > $mysqldumpfile
+let $table_name = test.t1;
+--source include/mysqldump.inc
+
+--echo # End tests
+
+--echo # Cleanup
+--echo # Reset concurrent_insert to its original value
+SET @@global.concurrent_insert = @old_concurrent_insert;
+--echo # remove mysqldumpfile
+--error 0,1
+--remove_file $mysqldumpfile
diff --git a/mysql-test/t/rpl_temporary.test b/mysql-test/t/rpl_temporary.test
index 516f3a026c9..4cd538877eb 100644
--- a/mysql-test/t/rpl_temporary.test
+++ b/mysql-test/t/rpl_temporary.test
@@ -217,4 +217,74 @@ drop table t1;
 # Delete the anonymous users
 source include/delete_anonymous_users.inc;
 
+# ##################################################################
+# BUG#41725: slave crashes when inserting into temporary table after
+#            stop/start slave
+#
+#  This test checks that both reported issues (assertion failure and
+#  crash) go away. It is implemented as follows:
+#    
+#    case 1: assertion failure
+#      i) create and insert into temporary table on master
+#     ii) sync slave with master
+#    iii) stop and restart slave
+#     iv) insert into master another value
+#      v) sync slave with master
+#
+#
+#    case 2: crash (SIGSEV)
+#      i) create and insert into temporary table on master (insert
+#         produces warnings)
+#     ii) sync slave with master
+#    iii) stop and restart slave
+#     iv) insert into master more values
+#      v) sync slave with master
+
+# case 1: Assertion in Field_string::store() failed because current 
+#         thread reference differed from table->in_use after slave 
+#         restart
+
+connection master;
+
+disable_warnings;
+DROP TABLE IF EXISTS t1;
+enable_warnings;
+
+CREATE TEMPORARY TABLE t1 (a char(1));
+INSERT INTO t1 VALUES ('a');
+sync_slave_with_master;
+
+source include/stop_slave.inc;
+source include/start_slave.inc;
+
+connection master;
+INSERT INTO t1 VALUES ('b');
+sync_slave_with_master;
+
+# case 2: crash on sp_rcontext::find_handler because it used 
+#         reference to invalid THD object after slave restart
+
+connection master;
+
+disable_warnings;
+DROP TABLE IF EXISTS t1;
+enable_warnings;
+CREATE TEMPORARY TABLE `t1`(`a` tinyint,`b` char(1))engine=myisam;
+INSERT INTO `t1` set `a`=128,`b`='128';
+
+sync_slave_with_master;
+
+source include/stop_slave.inc;
+source include/start_slave.inc;
+
+connection master;
+INSERT INTO `t1` set `a`=128,`b`='128';
+sync_slave_with_master;
+
+# cleanup
+
+connection master;
+DROP TABLE t1;
+sync_slave_with_master;
+
 # End of 5.0 tests
diff --git a/mysql-test/t/subselect3.test b/mysql-test/t/subselect3.test
index 2d88d1660b0..6f08ebef86d 100644
--- a/mysql-test/t/subselect3.test
+++ b/mysql-test/t/subselect3.test
@@ -669,4 +669,23 @@ SELECT ROW(1,2) = (SELECT NULL,    1), ROW(1,2) IN (SELECT NULL,    1);
 SELECT ROW(1,2) = (SELECT    1,    1), ROW(1,2) IN (SELECT    1,    1);
 SELECT ROW(1,2) = (SELECT    1,    2), ROW(1,2) IN (SELECT    1,    2);
 
+#
+# Bug #37362      Crash in do_field_eq
+#
+CREATE TABLE t1 (a INT, b INT, c INT);
+INSERT INTO t1 VALUES (1,1,1), (1,1,1);
+
+--error 1054
+EXPLAIN EXTENDED 
+  SELECT c FROM 
+    ( SELECT 
+      (SELECT COUNT(a) FROM 
+        (SELECT COUNT(b) FROM t1) AS x GROUP BY c
+      ) FROM t1 GROUP BY b
+    ) AS y;
+SHOW WARNINGS;
+
+DROP TABLE t1;
+
+
 --echo End of 5.0 tests
diff --git a/mysql-test/t/type_time.test b/mysql-test/t/type_time.test
index 5fc763be7fe..5bb521601e5 100644
--- a/mysql-test/t/type_time.test
+++ b/mysql-test/t/type_time.test
@@ -77,3 +77,16 @@ insert into t1 values('2007-07-02', 1);
 insert into t1 values('2007-07-02', 2);
 SELECT sum(f3) FROM t1 where f2='2007-07-01 00:00:00' group by f2;
 drop table t1;
+
+
+--echo #
+--echo # Bug #44792: valgrind warning when casting from time to time
+--echo #
+
+CREATE TABLE t1 (c TIME);
+INSERT INTO t1 VALUES ('0:00:00');
+SELECT CAST(c AS TIME) FROM t1;
+DROP TABLE t1;
+
+
+--echo End of 5.0 tests
diff --git a/mysql-test/t/union.test b/mysql-test/t/union.test
index 1dbbdae24b7..0c8e025e54e 100644
--- a/mysql-test/t/union.test
+++ b/mysql-test/t/union.test
@@ -1038,4 +1038,16 @@ CREATE TABLE t2 AS SELECT d FROM t1 UNION SELECT d FROM t1;
 SHOW FIELDS FROM t2;
 DROP TABLE t1, t2;
 
+#
+# Bug#43612 crash with explain extended, union, order by
+#
+CREATE TABLE t1(a INT);
+EXPLAIN EXTENDED
+SELECT a FROM t1
+UNION
+SELECT a FROM t1
+ORDER BY a;
+DROP TABLE t1;
+
+
 --echo End of 5.0 tests
diff --git a/mysql-test/t/user_var.test b/mysql-test/t/user_var.test
index 3a3e8f88f83..d39b49a0e87 100644
--- a/mysql-test/t/user_var.test
+++ b/mysql-test/t/user_var.test
@@ -237,3 +237,15 @@ select @a:=f2, count(f2) from t1 group by 1 desc;
 select @a:=f3, count(f3) from t1 group by 1 desc;
 select @a:=f4, count(f4) from t1 group by 1 desc;
 drop table t1;
+
+#
+# Bug#42009: SELECT into variable gives different results to direct SELECT
+#
+CREATE TABLE t1(a INT, b INT);
+INSERT INTO t1 VALUES (0, 0), (2, 1), (2, 3), (1, 1), (30, 20);
+SELECT a, b INTO @a, @b FROM t1 WHERE a=2 AND b=3 GROUP BY a, b;
+SELECT @a, @b;
+SELECT a, b FROM t1 WHERE a=2 AND b=3 GROUP BY a, b;
+DROP TABLE t1;
+
+--echo End of 5.0 tests
diff --git a/mysql-test/t/view.test b/mysql-test/t/view.test
index 6437e546697..89e68e4bd99 100644
--- a/mysql-test/t/view.test
+++ b/mysql-test/t/view.test
@@ -3681,5 +3681,28 @@ DROP VIEW v1;
 DROP TABLE t1;
 
 --echo # -----------------------------------------------------------------
+--echo # -- Bug#40825: Error 1356 while selecting from a view 
+--echo # --            with a "HAVING" clause though query works
+--echo # -----------------------------------------------------------------
+--echo
+
+CREATE TABLE t1 (c INT);
+
+--echo
+
+CREATE VIEW v1 (view_column) AS SELECT c AS alias FROM t1 HAVING alias;
+SHOW CREATE VIEW v1;
+SELECT * FROM v1;
+
+--echo
+
+DROP VIEW v1;
+DROP TABLE t1;
+
+--echo
+--echo # -- End of test case for Bug#40825
+--echo
+
+--echo # -----------------------------------------------------------------
 --echo # -- End of 5.0 tests.
 --echo # -----------------------------------------------------------------