diff options
| author | unknown <knielsen@knielsen-hq.org> | 2010-04-28 14:52:24 +0200 |
|---|---|---|
| committer | unknown <knielsen@knielsen-hq.org> | 2010-04-28 14:52:24 +0200 |
| commit | b1e00b6be81c80b09d11085d77d86978e26df988 (patch) | |
| tree | bb1fdd7363fbf2580572ac9a56dbd4c933cc4c0d /mysql-test/t | |
| parent | 1f683a7270e63abfadce20c6f51370621ff065e1 (diff) | |
| parent | c9cfd2df5f2f58c2cdf716999ebea252c307333f (diff) | |
| download | mariadb-git-b1e00b6be81c80b09d11085d77d86978e26df988.tar.gz | |
Merge MySQL 5.1.46 into MariaDB.
Still two test failures to be solved: main.myisam and main.subselect.
Diffstat (limited to 'mysql-test/t')
59 files changed, 2000 insertions, 54 deletions
diff --git a/mysql-test/t/archive.test b/mysql-test/t/archive.test index ba166571076..6704257f9af 100644 --- a/mysql-test/t/archive.test +++ b/mysql-test/t/archive.test @@ -1646,3 +1646,37 @@ INSERT INTO t1 (col1, col2) VALUES (1, "value"); REPAIR TABLE t1; DROP TABLE t1; remove_file $MYSQLD_DATADIR/test/t1.ARM; + +--echo # +--echo # BUG#48757 - missing .ARZ file causes server crash +--echo # +CREATE TABLE t1(a INT) ENGINE=ARCHIVE; +FLUSH TABLE t1; +--remove_file $MYSQLD_DATADIR/test/t1.ARZ +--error ER_FILE_NOT_FOUND +SELECT * FROM t1; +--error ER_BAD_TABLE_ERROR +DROP TABLE t1; + + +--echo # +--echo # BUG#46565 - repair of partition fail for archive engine +--echo # +--echo # Installing corrupted table files for t1. +# bug46565 was created, filled and damaged as following: +# CREATE TABLE bug46565(a INT) ENGINE=archive; +# INSERT INTO bug46565 VALUES(1); +# FLUSH TABLE bug46565; +# INSERT INTO bug46565 VALUES(2),(3); +# FLUSH TABLE bug46565; +# dd if=bug46565.ARZ of=std_data/bug46565.ARZ bs=1 count=8670 +copy_file std_data/bug46565.frm $MYSQLD_DATADIR/test/t1.frm; +copy_file std_data/bug46565.ARZ $MYSQLD_DATADIR/test/t1.ARZ; +--error ER_CRASHED_ON_USAGE +SELECT * FROM t1; +REPAIR TABLE t1; +--error ER_CRASHED_ON_USAGE +SELECT * FROM t1; +REPAIR TABLE t1 EXTENDED; +SELECT * FROM t1; +DROP TABLE t1; diff --git a/mysql-test/t/bigint.test b/mysql-test/t/bigint.test index 5a589816dcd..e19bba971f9 100644 --- a/mysql-test/t/bigint.test +++ b/mysql-test/t/bigint.test @@ -327,3 +327,38 @@ drop table t1; create table t1 select -9223372036854775809 bi; describe t1; drop table t1; + +--echo # +--echo # Bug #45360: wrong results +--echo # + +CREATE TABLE t1 (id INT AUTO_INCREMENT PRIMARY KEY, + a BIGINT(20) UNSIGNED, + b VARCHAR(20)); + +INSERT INTO t1 (a) VALUES + (0), + (CAST(0x7FFFFFFFFFFFFFFF AS UNSIGNED)), + (CAST(0x8000000000000000 AS UNSIGNED)), + (CAST(0xFFFFFFFFFFFFFFFF AS UNSIGNED)); + +UPDATE t1 SET b = a; + +let $n = `SELECT MAX(id) FROM t1`; +while($n) { + let $x = `SELECT a FROM t1 WHERE id = $n`; + dec $n; + let $hex = `SELECT HEX($x)`; + echo # $hex; + + --disable_result_log + eval EXPLAIN EXTENDED SELECT 1 FROM t1 WHERE a = $x AND TRIM(a) = b; + --enable_result_log + SHOW WARNINGS; +} + +DROP TABLE t1; + +--echo # End of 5.1 tests + + diff --git a/mysql-test/t/bug39022.test b/mysql-test/t/bug39022.test new file mode 100644 index 00000000000..268b207e0e5 --- /dev/null +++ b/mysql-test/t/bug39022.test @@ -0,0 +1,63 @@ +-- source include/have_log_bin.inc +-- source include/have_innodb.inc +-- source include/not_binlog_format_row.inc + +--echo # +--echo # Bug #39022: Mysql randomly crashing in lock_sec_rec_cons_read_sees +--echo # + +CREATE TABLE t1(a TINYINT NOT NULL,b TINYINT,PRIMARY KEY(b)) ENGINE=innodb; +CREATE TABLE t2(d TINYINT NOT NULL,UNIQUE KEY(d)) ENGINE=innodb; +INSERT INTO t1 VALUES (13,0),(8,1),(9,2),(6,3), +(11,5),(11,6),(7,7),(7,8),(4,9),(6,10),(3,11),(11,12), +(12,13),(7,14); +INSERT INTO t2 VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10), +(11),(12),(13),(14); + +connect (thread1, localhost, root,,); +connect (thread2, localhost, root,,); + +connection thread1; +--echo # in thread1 +START TRANSACTION; + +connection thread2; +--echo # in thread2 +REPLACE INTO t2 VALUES (-17); +SELECT d FROM t2,t1 WHERE d=(SELECT MAX(a) FROM t1 WHERE t1.a > t2.d); + +connection thread1; +--echo # in thread1 +REPLACE INTO t1(a,b) VALUES (67,20); + +connection thread2; +--echo # in thread2 +COMMIT; +START TRANSACTION; +REPLACE INTO t1(a,b) VALUES (65,-50); +REPLACE INTO t2 VALUES (-91); +send; +SELECT d FROM t2,t1 WHERE d=(SELECT MAX(a) FROM t1 WHERE t1.a > t2.d); #waits + +connection thread1; +--echo # in thread1 + +--echo # should not crash +--error ER_LOCK_DEADLOCK +SELECT d FROM t2,t1 WHERE d=(SELECT MAX(a) FROM t1 WHERE t1.a > t2.d); #crashes + +connection thread2; +--echo # in thread2 +REAP; + +disconnect thread2; +--source include/wait_until_disconnected.inc + +connection thread1; +--echo # in thread1; +disconnect thread1; +--source include/wait_until_disconnected.inc + +connection default; + +DROP TABLE t1,t2; diff --git a/mysql-test/t/csv.test b/mysql-test/t/csv.test index cdf274190dd..b31fa83588b 100644 --- a/mysql-test/t/csv.test +++ b/mysql-test/t/csv.test @@ -1807,16 +1807,16 @@ unlock tables; drop table t1; # -# Bug#41441 repair csv table crashes debug server -# -# Note: The test should be removed after Bug#33717 is fixed +# Bug#33717 INSERT...(default) fails for enum. Crashes CSV tables, loads spaces for MyISAM +# +CREATE TABLE t1 (e enum('foo','bar') NOT NULL) ENGINE = CSV; +INSERT INTO t1 VALUES(); +INSERT INTO t1 VALUES(default); +INSERT INTO t1 VALUES(0); +INSERT INTO t1 VALUES(3); +INSERT INTO t1 VALUES(-1); +SELECT * FROM t1; +DROP TABLE t1; -create table t1(a enum ('a') not null) engine=csv; -insert into t1 values (2); ---error ER_CRASHED_ON_USAGE -select * from t1 limit 1; -repair table t1; -select * from t1 limit 1; -drop table t1; --echo End of 5.1 tests diff --git a/mysql-test/t/ctype_ldml.test b/mysql-test/t/ctype_ldml.test index 0395de273de..9ab458ba516 100644 --- a/mysql-test/t/ctype_ldml.test +++ b/mysql-test/t/ctype_ldml.test @@ -46,6 +46,14 @@ SELECT * FROM t1 WHERE LOWER(a)=LOWER('N'); DROP TABLE t1; --echo # +--echo # Bug#51976 LDML collations issue (cyrillic example) +--echo # +CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET utf8 COLLATE utf8_test_ci); +INSERT INTO t1 (a) VALUES ('Hello'); +SELECT a, UPPER(a), LOWER(a) FROM t1; +DROP TABLE t1; + +--echo # --echo # Bug#43827 Server closes connections and restarts --echo # # Crash happened with a user-defined utf8 collation, diff --git a/mysql-test/t/delete.test b/mysql-test/t/delete.test index 2f51fafd6a6..7bbc470137a 100644 --- a/mysql-test/t/delete.test +++ b/mysql-test/t/delete.test @@ -291,6 +291,21 @@ DELETE FROM t1 ORDER BY (f1(10)) LIMIT 1; DROP TABLE t1; DROP FUNCTION f1; + +--echo # +--echo # Bug #49552 : sql_buffer_result cause crash + not found records +--echo # in multitable delete/subquery +--echo # + +CREATE TABLE t1(a INT); +INSERT INTO t1 VALUES (1),(2),(3); +SET SESSION SQL_BUFFER_RESULT=1; +DELETE t1 FROM (SELECT SUM(a) a FROM t1) x,t1; + +SET SESSION SQL_BUFFER_RESULT=DEFAULT; +SELECT * FROM t1; +DROP TABLE t1; + --echo End of 5.0 tests --echo # @@ -360,18 +375,4 @@ DELETE IGNORE FROM t1; DROP TABLE t1; ---echo # ---echo # Bug #49552 : sql_buffer_result cause crash + not found records ---echo # in multitable delete/subquery ---echo # - -CREATE TABLE t1(a INT); -INSERT INTO t1 VALUES (1),(2),(3); -SET SESSION SQL_BUFFER_RESULT=1; -DELETE t1 FROM (SELECT SUM(a) a FROM t1) x,t1; - -SET SESSION SQL_BUFFER_RESULT=DEFAULT; -SELECT * FROM t1; -DROP TABLE t1; - --echo End of 5.1 tests diff --git a/mysql-test/t/explain.test b/mysql-test/t/explain.test index 77b49a8b1a5..3c2f7bbbe96 100644 --- a/mysql-test/t/explain.test +++ b/mysql-test/t/explain.test @@ -147,7 +147,27 @@ EXPLAIN SELECT DISTINCT 1 FROM t1, WHERE t1.a = d1.a; DROP TABLE t1; -# End of 5.0 tests. +--echo # +--echo # Bug#48295: +--echo # explain extended crash with subquery and ONLY_FULL_GROUP_BY sql_mode +--echo # + +CREATE TABLE t1 (f1 INT); + +SELECT @@session.sql_mode INTO @old_sql_mode; +SET SESSION sql_mode='ONLY_FULL_GROUP_BY'; + +# EXPLAIN EXTENDED (with subselect). used to crash. should give NOTICE. +--error ER_MIX_OF_GROUP_FUNC_AND_FIELDS +EXPLAIN EXTENDED SELECT 1 FROM t1 + WHERE f1 > ALL( SELECT t.f1 FROM t1,t1 AS t ); +SHOW WARNINGS; + +SET SESSION sql_mode=@old_sql_mode; + +DROP TABLE t1; + +--echo End of 5.0 tests. --echo # --echo # Bug#37870: Usage of uninitialized value caused failed assertion. @@ -168,23 +188,14 @@ SELECT OUTR.dt FROM t1 AS OUTR WHERE OUTR.dt IN ( SELECT INNR.dt FROM t2 AS INNR drop tables t1, t2; --echo # ---echo # Bug#48295: ---echo # explain extended crash with subquery and ONLY_FULL_GROUP_BY sql_mode +--echo # Bug#47669: Query showed by EXPLAIN EXTENDED gives different result from original query --echo # -CREATE TABLE t1 (f1 INT); - -SELECT @@session.sql_mode INTO @old_sql_mode; -SET SESSION sql_mode='ONLY_FULL_GROUP_BY'; - -# EXPLAIN EXTENDED (with subselect). used to crash. should give NOTICE. ---error ER_MIX_OF_GROUP_FUNC_AND_FIELDS -EXPLAIN EXTENDED SELECT 1 FROM t1 - WHERE f1 > ALL( SELECT t.f1 FROM t1,t1 AS t ); -SHOW WARNINGS; - -SET SESSION sql_mode=@old_sql_mode; - -DROP TABLE t1; +CREATE TABLE t1 (c int); +INSERT INTO t1 VALUES (NULL); +CREATE TABLE t2 (d int); +INSERT INTO t2 VALUES (NULL), (0); +EXPLAIN EXTENDED SELECT (SELECT 1 FROM t2 WHERE d = c) FROM t1; +DROP TABLE t1, t2; --echo End of 5.1 tests. diff --git a/mysql-test/t/fulltext.test b/mysql-test/t/fulltext.test index 4df910d366b..71a7f3bbc62 100644 --- a/mysql-test/t/fulltext.test +++ b/mysql-test/t/fulltext.test @@ -556,6 +556,27 @@ SELECT count(*) FROM t1 WHERE DROP TABLE t1,t2,t3; +# +# BUG#50351 - ft_min_word_len=2 Causes query to hang +# +CREATE TABLE t1 (a VARCHAR(4), FULLTEXT(a)); +INSERT INTO t1 VALUES +('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'), +('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'), +('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'), +('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'), +('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'), +('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'), +('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'), +('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'), +('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'), +('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'), +('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'), +('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'), +('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('awrd'),('cwrd'), +('awrd'); +SELECT COUNT(*) FROM t1 WHERE MATCH(a) AGAINST("+awrd bwrd* +cwrd*" IN BOOLEAN MODE); +DROP TABLE t1; --echo # --echo # Bug #49445: Assertion failed: 0, file .\item_row.cc, line 55 with @@ -566,5 +587,14 @@ CREATE TABLE t1(a CHAR(1),FULLTEXT(a)); SELECT 1 FROM t1 WHERE MATCH(a) AGAINST ('') AND ROW(a,a) > ROW(1,1); DROP TABLE t1; +--echo # +--echo # BUG#51866 - crash with repair by sort and fulltext keys +--echo # +CREATE TABLE t1(a CHAR(4), FULLTEXT(a)); +INSERT INTO t1 VALUES('aaaa'); +SET myisam_sort_buffer_size=4; +REPAIR TABLE t1; +SET myisam_sort_buffer_size=@@global.myisam_sort_buffer_size; +DROP TABLE t1; --echo End of 5.1 tests diff --git a/mysql-test/t/func_concat.test b/mysql-test/t/func_concat.test index e24b4354b61..e56d1121808 100644 --- a/mysql-test/t/func_concat.test +++ b/mysql-test/t/func_concat.test @@ -124,4 +124,24 @@ SELECT @query; DROP PROCEDURE p1; +--echo # +--echo # Bug #40625: Concat fails on DOUBLE values in a Stored Procedure, +--echo # while DECIMAL works +--echo # + +DELIMITER //; +CREATE PROCEDURE p1() +BEGIN + DECLARE v1 DOUBLE(10,3); + SET v1= 100; + SET @s = CONCAT('########################################', 40 , v1); + SELECT @s; +END;// +DELIMITER ;// + +CALL p1(); +CALL p1(); + +DROP PROCEDURE p1; + --echo # End of 5.1 tests diff --git a/mysql-test/t/func_gconcat.test b/mysql-test/t/func_gconcat.test index e92f3e96303..e832ea316eb 100644 --- a/mysql-test/t/func_gconcat.test +++ b/mysql-test/t/func_gconcat.test @@ -693,4 +693,31 @@ SELECT 1 FROM t1 WHERE t1.a NOT IN DROP TABLE t1, t2; +# +# Bug #49487: crash with explain extended and group_concat in a derived table +# + +CREATE TABLE t1 (f1 INT); +INSERT INTO t1 VALUES (),(); + +EXPLAIN EXTENDED SELECT 1 FROM + (SELECT DISTINCT GROUP_CONCAT(td.f1) FROM t1,t1 AS td GROUP BY td.f1) AS d,t1; + +SELECT 1 FROM + (SELECT DISTINCT GROUP_CONCAT(td.f1) FROM t1,t1 AS td GROUP BY td.f1) AS d,t1; + +DROP TABLE t1; + + +--echo # +--echo # Bug #52397: another crash with explain extended and group_concat +--echo # +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (0), (0); +EXPLAIN EXTENDED SELECT 1 FROM + (SELECT GROUP_CONCAT(t1.a ORDER BY t1.a ASC) FROM + t1 t2, t1 GROUP BY t1.a) AS d; +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 8942b0a2faf..3392a41519b 100644 --- a/mysql-test/t/func_str.test +++ b/mysql-test/t/func_str.test @@ -1352,3 +1352,14 @@ SELECT DECODE((SELECT ENCODE('secret', t1.a) FROM t1,t2 WHERE t1.a = t2.a GROUP FROM t2 WHERE t2.b = 1 GROUP BY t2.b; DROP TABLE t1, t2; + +--echo # +--echo # Bug#52164 Assertion failed: param.sort_length, file .\filesort.cc, line 149 +--echo # +CREATE TABLE t1 (a LONGBLOB NOT NULL); +INSERT INTO t1 VALUES (''),(''); +SELECT 1 FROM t1, t1 t2 +ORDER BY QUOTE(t1.a); +DROP TABLE t1; + +--echo End of 5.1 tests diff --git a/mysql-test/t/gis-rtree.test b/mysql-test/t/gis-rtree.test index 8ce4abf45d4..9e6002a1faf 100644 --- a/mysql-test/t/gis-rtree.test +++ b/mysql-test/t/gis-rtree.test @@ -914,4 +914,29 @@ SELECT 1 FROM t1 WHERE a >= GEOMFROMTEXT('LINESTRING(-1 -1, 1 -1, -1 -1, -1 1, 1 DROP TABLE t1; +--echo # +--echo # Bug #51357: crash when using handler commands on spatial indexes +--echo # + +CREATE TABLE t1(a GEOMETRY NOT NULL,SPATIAL INDEX a(a)); +HANDLER t1 OPEN; +HANDLER t1 READ a FIRST; +HANDLER t1 READ a NEXT; +HANDLER t1 READ a PREV; +HANDLER t1 READ a LAST; +HANDLER t1 CLOSE; + +# second crash fixed when the tree has changed since the last search. +HANDLER t1 OPEN; +HANDLER t1 READ a FIRST; +INSERT INTO t1 VALUES (GeomFromText('Polygon((40 40,60 40,60 60,40 60,40 40))')); +--echo # should not crash +--disable_result_log +HANDLER t1 READ a NEXT; +--enable_result_log +HANDLER t1 CLOSE; + +DROP TABLE t1; + + --echo End of 5.0 tests. diff --git a/mysql-test/t/group_by.test b/mysql-test/t/group_by.test index 5b96213034a..e6ea5ecc7f6 100644 --- a/mysql-test/t/group_by.test +++ b/mysql-test/t/group_by.test @@ -1158,3 +1158,53 @@ SELECT COUNT(i) FROM t1 WHERE i > 1; DROP TABLE t1; SET @@sql_mode = @old_sql_mode; +--echo # +--echo # Bug #45640: optimizer bug produces wrong results +--echo # + +CREATE TABLE t1 (a INT, b INT); +INSERT INTO t1 VALUES (4, 40), (1, 10), (2, 20), (2, 20), (3, 30); + +--echo # should return 4 ordered records: +SELECT (SELECT t1.a) aa, COUNT(DISTINCT b) FROM t1 GROUP BY aa; + +SELECT (SELECT (SELECT t1.a)) aa, COUNT(DISTINCT b) FROM t1 GROUP BY aa; + +SELECT (SELECT t1.a) aa, COUNT(DISTINCT b) FROM t1 GROUP BY aa+0; + +--echo # should return the same result in a reverse order: +SELECT (SELECT t1.a) aa, COUNT(DISTINCT b) FROM t1 GROUP BY -aa; + +--echo # execution plan should not use temporary table: +EXPLAIN EXTENDED +SELECT (SELECT t1.a) aa, COUNT(DISTINCT b) FROM t1 GROUP BY aa+0; + +EXPLAIN EXTENDED +SELECT (SELECT t1.a) aa, COUNT(DISTINCT b) FROM t1 GROUP BY -aa; + +--echo # should return only one record +SELECT (SELECT tt.a FROM t1 tt LIMIT 1) aa, COUNT(DISTINCT b) FROM t1 + GROUP BY aa; + +CREATE TABLE t2 SELECT DISTINCT a FROM t1; + +--echo # originally reported queries (1st two columns of next two query +--echo # results should be same): + +SELECT (SELECT t2.a FROM t2 WHERE t2.a = t1.a) aa, b, COUNT(DISTINCT b) + FROM t1 GROUP BY aa, b; +SELECT (SELECT t2.a FROM t2 WHERE t2.a = t1.a) aa, b, COUNT( b) + FROM t1 GROUP BY aa, b; + +--echo # ORDER BY for sure: + +SELECT (SELECT t2.a FROM t2 WHERE t2.a = t1.a) aa, b, COUNT(DISTINCT b) + FROM t1 GROUP BY aa, b ORDER BY -aa, -b; +SELECT (SELECT t2.a FROM t2 WHERE t2.a = t1.a) aa, b, COUNT( b) + FROM t1 GROUP BY aa, b ORDER BY -aa, -b; + +DROP TABLE t1, t2; + +--echo # + +--echo # End of 5.1 tests diff --git a/mysql-test/t/group_min_max.test b/mysql-test/t/group_min_max.test index f623df372fa..b06f0c09fc5 100644 --- a/mysql-test/t/group_min_max.test +++ b/mysql-test/t/group_min_max.test @@ -1044,4 +1044,45 @@ SELECT a, MAX(b) FROM t WHERE b GROUP BY a; DROP TABLE t; +# +# BUG#49902 - SELECT returns incorrect results +# +CREATE TABLE t1(a INT NOT NULL, b INT NOT NULL, KEY (b)); +INSERT INTO t1 VALUES(1,1),(2,1); +ANALYZE TABLE t1; +SELECT 1 AS c, b FROM t1 WHERE b IN (1,2) GROUP BY c, b; +SELECT a FROM t1 WHERE b=1; +DROP TABLE t1; + +--echo # +--echo # Bug#47762: Incorrect result from MIN() when WHERE tests NOT NULL column +--echo # for NULL +--echo # + +--echo ## Test for NULLs allowed +CREATE TABLE t1 ( a INT, KEY (a) ); +INSERT INTO t1 VALUES (1), (2), (3); +--source include/min_null_cond.inc +INSERT INTO t1 VALUES (NULL), (NULL); +--source include/min_null_cond.inc +DROP TABLE t1; + +--echo ## Test for NOT NULLs +CREATE TABLE t1 ( a INT NOT NULL PRIMARY KEY); +INSERT INTO t1 VALUES (1), (2), (3); +--echo # +--echo # NULL-safe operator test disabled for non-NULL indexed columns. +--echo # +--echo # See bugs +--echo # +--echo # - Bug#52173: Reading NULL value from non-NULL index gives +--echo # wrong result in embedded server +--echo # +--echo # - Bug#52174: Sometimes wrong plan when reading a MAX value from +--echo # non-NULL index +--echo # +--let $skip_null_safe_test= 1 +--source include/min_null_cond.inc +DROP TABLE t1; + --echo End of 5.1 tests diff --git a/mysql-test/t/handler_myisam.test b/mysql-test/t/handler_myisam.test index da02a90af0f..868ba14480a 100644 --- a/mysql-test/t/handler_myisam.test +++ b/mysql-test/t/handler_myisam.test @@ -37,4 +37,15 @@ TRUNCATE t1; HANDLER t1 READ FIRST; DROP TABLE t1; +--echo # +--echo # BUG#51877 - HANDLER interface causes invalid memory read +--echo # +CREATE TABLE t1(a INT, KEY(a)); +HANDLER t1 OPEN; +HANDLER t1 READ a FIRST; +INSERT INTO t1 VALUES(1); +HANDLER t1 READ a NEXT; +HANDLER t1 CLOSE; +DROP TABLE t1; + --echo End of 5.1 tests diff --git a/mysql-test/t/having.test b/mysql-test/t/having.test index af9af4fe1fc..65bf9518a5c 100644 --- a/mysql-test/t/having.test +++ b/mysql-test/t/having.test @@ -442,4 +442,105 @@ INSERT INTO t1 VALUES (1, 1), (2,2), (3, NULL); SELECT b, COUNT(DISTINCT a) FROM t1 GROUP BY b HAVING b is NULL; DROP TABLE t1; + +--echo # +--echo # Bug#50995 Having clause on subquery result produces incorrect results. +--echo # + +CREATE TABLE t1 +( + id1 INT, + id2 INT NOT NULL, + INDEX id1(id2) +); + +INSERT INTO t1 SET id1=1, id2=1; +INSERT INTO t1 SET id1=2, id2=1; +INSERT INTO t1 SET id1=3, id2=1; + +SELECT t1.id1, +(SELECT 0 FROM DUAL + WHERE t1.id1=t1.id1) AS amount FROM t1 +WHERE t1.id2 = 1 +HAVING amount > 0 +ORDER BY t1.id1; + +DROP TABLE t1; + +--echo # +--echo # Bug#48916 Server incorrectly processing HAVING clauses with an ORDER BY clause +--echo # +CREATE TABLE t1 (f1 INT PRIMARY KEY, f2 INT, f3 INT); +INSERT INTO t1 VALUES (2,7,9), (4,7,9), (6,2,9), (17,0,9); + +SELECT table1.f1, table2.f2 +FROM t1 AS table1 +JOIN t1 AS table2 ON table1.f3 = table2.f3 +WHERE table2.f1 = 2 +GROUP BY table1.f1, table2.f2 +HAVING (table2.f2 = 8 AND table1.f1 >= 6); + +EXPLAIN EXTENDED +SELECT table1.f1, table2.f2 +FROM t1 AS table1 +JOIN t1 AS table2 ON table1.f3 = table2.f3 +WHERE table2.f1 = 2 +GROUP BY table1.f1, table2.f2 +HAVING (table2.f2 = 8 AND table1.f1 >= 6); + +EXPLAIN EXTENDED +SELECT table1.f1, table2.f2 +FROM t1 AS table1 +JOIN t1 AS table2 ON table1.f3 = table2.f3 +WHERE table2.f1 = 2 +GROUP BY table1.f1, table2.f2 +HAVING (table2.f2 = 8); + +DROP TABLE t1; + +--echo # +--echo # Bug#52336 Segfault / crash in 5.1 copy_fields (param=0x9872980) at sql_select.cc:15355 +--echo # +CREATE TABLE t1(f1 INT, f2 INT); +INSERT INTO t1 VALUES (10,8); +CREATE TABLE t2 (f1 INT); +INSERT INTO t2 VALUES (5); + +SELECT COUNT(f1) FROM t2 +HAVING (7, 9) IN (SELECT f1, MIN(f2) FROM t1); + +DROP TABLE t1, t2; + +CREATE TABLE t1 (f1 INT, f2 VARCHAR(1)); +INSERT INTO t1 VALUES (16,'f'); +INSERT INTO t1 VALUES (16,'f'); +CREATE TABLE t2 (f1 INT, f2 VARCHAR(1)); +INSERT INTO t2 VALUES (13,'f'); +INSERT INTO t2 VALUES (20,'f'); +CREATE TABLE t3 (f1 INT, f2 VARCHAR(1)); +INSERT INTO t3 VALUES (7,'f'); + +SELECT t1.f2 FROM t1 +STRAIGHT_JOIN (t2 JOIN t3 ON t3.f2 = t2.f2 ) ON t3 .f2 = t2 .f2 +HAVING ('v', 'i') NOT IN (SELECT f2, MIN(f2) FROM t1) +ORDER BY f2; + +DROP TABLES t1,t2,t3; + +--echo # +--echo # Bug#52340 Segfault: read_cached_record (tab=0x94a2634) at sql_select.cc:14411 +--echo # +CREATE TABLE t1 (f1 INT, f2 VARCHAR(1)); +INSERT INTO t1 VALUES (16,'d'); +CREATE TABLE t2 (f1 INT, f2 VARCHAR(1)); +INSERT INTO t2 VALUES (13,'e'); +INSERT INTO t2 VALUES (20,'d'); + +SELECT MAX(t2.f2) FROM t2 JOIN t1 ON t1.f2 +HAVING ('e' , 'd') IN +(SELECT ts1.f2, ts2.f2 FROM t2 ts1 JOIN t2 ts2 ON ts1.f1) +ORDER BY t1.f2; + +DROP TABLE t1,t2; + --echo End of 5.0 tests diff --git a/mysql-test/t/innodb-autoinc.test b/mysql-test/t/innodb-autoinc.test index 37ca1e5a2ab..68e10fa3095 100644 --- a/mysql-test/t/innodb-autoinc.test +++ b/mysql-test/t/innodb-autoinc.test @@ -632,6 +632,37 @@ SELECT * FROM t1; SHOW CREATE TABLE t1; DROP TABLE t1; +## +# 49497: Error 1467 (ER_AUTOINC_READ_FAILED) on inserting a negative value +# +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 (c1 INTEGER AUTO_INCREMENT, PRIMARY KEY (c1)) ENGINE=InnoDB; +INSERT INTO t1 VALUES (-685113344), (1), (NULL), (NULL); +SELECT * FROM t1; +SHOW CREATE TABLE t1; +DROP TABLE t1; +CREATE TABLE t1 (c1 INTEGER AUTO_INCREMENT, PRIMARY KEY (c1)) ENGINE=InnoDB; +INSERT INTO t1 VALUES (-685113344), (2), (NULL), (NULL); +SELECT * FROM t1; +SHOW CREATE TABLE t1; +DROP TABLE t1; +CREATE TABLE t1 (c1 INTEGER AUTO_INCREMENT, PRIMARY KEY (c1)) ENGINE=InnoDB; +INSERT INTO t1 VALUES (NULL), (2), (-685113344), (NULL); +INSERT INTO t1 VALUES (4), (5), (6), (NULL); +SELECT * FROM t1; +SHOW CREATE TABLE t1; +DROP TABLE t1; +CREATE TABLE t1 (c1 INTEGER AUTO_INCREMENT, PRIMARY KEY (c1)) ENGINE=InnoDB; +INSERT INTO t1 VALUES (NULL), (2), (-685113344), (5); +SELECT * FROM t1; +SHOW CREATE TABLE t1; +DROP TABLE t1; +CREATE TABLE t1 (c1 INTEGER AUTO_INCREMENT, PRIMARY KEY (c1)) ENGINE=InnoDB; +INSERT INTO t1 VALUES (1), (2), (-685113344), (NULL); +SELECT * FROM t1; +SHOW CREATE TABLE t1; +DROP TABLE t1; + --disable_query_log EVAL SET GLOBAL innodb_file_format_check=$file_format_check; --enable_query_log diff --git a/mysql-test/t/innodb_bug38231.test b/mysql-test/t/innodb_bug38231.test index b3fcd89f371..1611cb56203 100644 --- a/mysql-test/t/innodb_bug38231.test +++ b/mysql-test/t/innodb_bug38231.test @@ -16,6 +16,7 @@ CREATE TABLE bug38231 (a INT); -- connect (con1,localhost,root,,) -- connect (con2,localhost,root,,) +-- connect (con3,localhost,root,,) -- connection con1 SET autocommit=0; @@ -26,15 +27,45 @@ SET autocommit=0; -- send LOCK TABLE bug38231 WRITE; +# When con1 does UNLOCK below this will release either con2 or con3 which are +# both waiting on LOCK. At the end we must first --reap and UNLOCK the +# connection that has been released, otherwise it will wait forever. We assume +# that the released connection will be the first one that has gained the LOCK, +# thus we force the order here - con2 does LOCK first, then con3. In other +# words we wait for LOCK from con2 above to be exected before doing LOCK in +# con3. +-- connection con1 +let $wait_condition = + SELECT COUNT(*) = 1 FROM information_schema.processlist + WHERE info = 'LOCK TABLE bug38231 WRITE'; +-- source include/wait_condition.inc +# the above enables query log, re-disable it +-- disable_query_log + +-- connection con3 +SET autocommit=0; +-- send +LOCK TABLE bug38231 WRITE; + -- connection default -- send TRUNCATE TABLE bug38231; -- connection con1 -# give time to TRUNCATE and others to be executed; without sleep, sometimes -# UNLOCK executes before TRUNCATE -# TODO: Replace with wait_condition once possible under embedded server. --- sleep 0.2 +# Wait for TRUNCATE and the other two LOCKs to be executed; without this, +# sometimes UNLOCK executes before them. We assume there are no other +# sessions executing at the same time with the same SQL commands. +let $wait_condition = + SELECT COUNT(*) = 1 FROM information_schema.processlist + WHERE info = 'TRUNCATE TABLE bug38231'; +-- source include/wait_condition.inc +let $wait_condition = + SELECT COUNT(*) = 2 FROM information_schema.processlist + WHERE info = 'LOCK TABLE bug38231 WRITE'; +-- source include/wait_condition.inc +# the above enables query log, re-disable it +-- disable_query_log + # this crashes the server if the bug is present UNLOCK TABLES; @@ -44,10 +75,16 @@ UNLOCK TABLES; -- reap UNLOCK TABLES; +-- connection con3 +-- reap +UNLOCK TABLES; + -- connection default -- reap + -- disconnect con1 -- disconnect con2 +-- disconnect con3 # test that TRUNCATE works with with row-level locks diff --git a/mysql-test/t/innodb_bug47621.test b/mysql-test/t/innodb_bug47621.test new file mode 100644 index 00000000000..4863cc6bba1 --- /dev/null +++ b/mysql-test/t/innodb_bug47621.test @@ -0,0 +1,57 @@ +# This is the test for bug #47621, column rename operation should +# not result in column definition inconsistency between MySQL and +# InnoDB + +--source include/have_innodb.inc + +CREATE TABLE bug47621 (salesperson INT) ENGINE=InnoDB; + +# Change the column name +ALTER TABLE bug47621 CHANGE salesperson sales_acct_id INT; + +# If there is inconsistency of column name definition +# in MySQL or InnoDB, following create index would fail +create index orgs on bug47621(sales_acct_id); + +# Change the column name back with the index defined on it. +ALTER TABLE bug47621 CHANGE sales_acct_id salesperson INT; + +drop table bug47621; + +CREATE TABLE bug47621_sale ( + salesperson INT, + PRIMARY KEY(salesperson)) engine = innodb; + +CREATE TABLE bug47621_shirt( + id SMALLINT, + owner INT, + FOREIGN KEY(owner) + references bug47621_sale(salesperson) ON DELETE RESTRICT) + engine = innodb; + +insert into bug47621_sale values(9); + +insert into bug47621_shirt values(1, 9); + +# Any rename operation on columns involved in a reference constraint will +# fail, as it will be rejected by InnoDB row_rename_table_for_mysql(). +# In above example, any rename on column "salesperson" for table +# "bug47621_sale", or on column "owner" for table "bug47621_shirt will +# be blocked. We do not put such rename in the test since InnoDB error +# message will be printed in the error log, and result in test failure. +# +# ALTER TABLE bug47621_sale CHANGE salesperson sales_acct_id INT; + +# Any rename on columns not involved in the foreign key constraint +# could still proceed +ALTER TABLE bug47621_shirt CHANGE id new_id INT; + +# Referencing table dropped, the rename operation on related columns +# could proceed +drop table bug47621_shirt; + +ALTER TABLE bug47621_sale CHANGE salesperson sales_acct_id INT; + +ALTER TABLE bug47621_sale ADD INDEX idx (sales_acct_id); + +drop table bug47621_sale; diff --git a/mysql-test/t/innodb_mysql.test b/mysql-test/t/innodb_mysql.test index 4a06abfe3c8..1585895f8ba 100644 --- a/mysql-test/t/innodb_mysql.test +++ b/mysql-test/t/innodb_mysql.test @@ -538,4 +538,57 @@ DROP PROCEDURE p1; DROP VIEW v1; DROP TABLE t1,t2; + +--echo # +--echo # Bug #49324: more valgrind errors in test_if_skip_sort_order +--echo # +CREATE TABLE t1 (a INT PRIMARY KEY) ENGINE=innodb ; +--echo #should not cause valgrind warnings +SELECT 1 FROM t1 JOIN t1 a USING(a) GROUP BY t1.a,t1.a; +DROP TABLE t1; + +--echo # +--echo # Bug#50843: Filesort used instead of clustered index led to +--echo # performance degradation. +--echo # +create table t1(f1 int not null primary key, f2 int) engine=innodb; +create table t2(f1 int not null, key (f1)) engine=innodb; +insert into t1 values (1,1),(2,2),(3,3); +insert into t2 values (1),(2),(3); +explain select t1.* from t1 left join t2 using(f1) group by t1.f1; +drop table t1,t2; +--echo # + + +--echo # +--echo # Bug #39653: find_shortest_key in sql_select.cc does not consider +--echo # clustered primary keys +--echo # + +CREATE TABLE t1 (a INT PRIMARY KEY, b INT, c INT, d INT, e INT, f INT, + KEY (b,c)) ENGINE=INNODB; + +INSERT INTO t1 VALUES (1,1,1,1,1,1), (2,2,2,2,2,2), (3,3,3,3,3,3), + (4,4,4,4,4,4), (5,5,5,5,5,5), (6,6,6,6,6,6), + (7,7,7,7,7,7), (8,8,8,8,8,8), (9,9,9,9,9,9), + (11,11,11,11,11,11); + +--query_vertical EXPLAIN SELECT COUNT(*) FROM t1 + +DROP TABLE t1; + +--echo # +--echo # Bug #49838: DROP INDEX and ADD UNIQUE INDEX for same index may +--echo # corrupt definition at engine +--echo # + +CREATE TABLE t1 (a INT NOT NULL, b INT NOT NULL, KEY k (a,b)) + ENGINE=InnoDB; + +ALTER TABLE t1 DROP INDEX k, ADD UNIQUE INDEX k (a,b); + +--query_vertical SHOW INDEXES FROM t1; + +DROP TABLE t1; + --echo End of 5.1 tests diff --git a/mysql-test/t/join.test b/mysql-test/t/join.test index dbf36dedec8..09464c4d926 100644 --- a/mysql-test/t/join.test +++ b/mysql-test/t/join.test @@ -784,6 +784,29 @@ FROM DROP TABLE t1,t2,t3,t4,t5,t6; +--echo # +--echo # Bug#48483: crash in get_best_combination() +--echo # + +CREATE TABLE t1(f1 INT); +INSERT INTO t1 VALUES (1),(2); +CREATE VIEW v1 AS SELECT 1 FROM t1 LEFT JOIN t1 AS t2 on 1=1; +EXPLAIN EXTENDED +SELECT 1 FROM v1 right join v1 AS v2 ON RAND(); +DROP VIEW v1; +DROP TABLE t1; + +--echo # +--echo # Bug#52177 crash with explain, row comparison, join, text field +--echo # +CREATE TABLE t1 (a TINYINT, b TEXT, KEY (a)); +INSERT INTO t1 VALUES (0,''),(0,''); +FLUSH TABLES; +EXPLAIN SELECT 1 FROM t1 LEFT JOIN t1 a ON 1 +WHERE ROW(t1.a, 1111.11) = ROW(1111.11, 1111.11) AND +ROW(t1.b, 1111.11) <=> ROW('',''); +DROP TABLE t1; + --echo End of 5.0 tests. @@ -804,3 +827,28 @@ DEALLOCATE PREPARE stmt; DROP VIEW v1; DROP TABLE t1, t2; + +# +# Bug#45195 valgrind warnings about uninitialized values in store_record_in_cache() +# +CREATE TABLE t1(a CHAR(9),b INT,KEY(b),KEY(a)) ENGINE=MYISAM; +CREATE TABLE t2(a CHAR(9),b INT,KEY(b),KEY(a)) ENGINE=MYISAM; +INSERT INTO t1 VALUES ('1',null),(null,null); +INSERT INTO t2 VALUES ('1',null),(null,null); +CREATE TABLE mm1(a CHAR(9),b INT,KEY(b),KEY(a)) +ENGINE=MERGE UNION=(t1,t2); +SELECT t1.a FROM mm1,t1; +DROP TABLE t1, t2, mm1; + +--echo # +--echo # Bug #50335: Assertion `!(order->used & map)' in eq_ref_table +--echo # + +CREATE TABLE t1 (a INT NOT NULL, b INT NOT NULL, PRIMARY KEY (a,b)); +INSERT INTO t1 VALUES (0,0), (1,1); + +SELECT * FROM t1 STRAIGHT_JOIN t1 t2 ON t1.a=t2.a AND t1.a=t2.b ORDER BY t2.a, t1.a; + +DROP TABLE t1; + +--echo End of 5.1 tests diff --git a/mysql-test/t/join_outer.test b/mysql-test/t/join_outer.test index aeaa69657c6..e3d68d71603 100644 --- a/mysql-test/t/join_outer.test +++ b/mysql-test/t/join_outer.test @@ -896,3 +896,21 @@ FROM t1 JOIN t2 USING( a ) GROUP BY t1.a WITH ROLLUP; DROP TABLE t1, t2; + +--echo # +--echo # Bug#51598 Inconsistent behaviour with a COALESCE statement inside an IN comparison +--echo # +CREATE TABLE t1(f1 INT, f2 INT, f3 INT); +INSERT INTO t1 VALUES (1, NULL, 3); +CREATE TABLE t2(f1 INT, f2 INT); +INSERT INTO t2 VALUES (2, 1); + +EXPLAIN EXTENDED SELECT * FROM t1 LEFT JOIN t2 ON t1.f2 = t2.f2 +WHERE (COALESCE(t1.f1, t2.f1), f3) IN ((1, 3), (2, 2)); + +SELECT * FROM t1 LEFT JOIN t2 ON t1.f2 = t2.f2 +WHERE (COALESCE(t1.f1, t2.f1), f3) IN ((1, 3), (2, 2)); + +DROP TABLE t1, t2; + +--echo End of 5.1 tests diff --git a/mysql-test/t/loaddata.test b/mysql-test/t/loaddata.test index 7bfe2491c7c..e5f0a1d7eba 100644 --- a/mysql-test/t/loaddata.test +++ b/mysql-test/t/loaddata.test @@ -502,6 +502,13 @@ set session sql_mode=@OLD_SQL_MODE; DROP TABLE t1,t2; +--echo # +--echo # Bug #51893: crash with certain characters given to load_file +--echo # function on windows +--echo # + +select load_file(0x0A9FB76C661B409C4BEC88098C5DD71B1072F9691F2E827D7EC8F092B299868A3CE196C04F0FB18CAB4E1557EB72331D812379DE7A75CA21C32E7C722C59E5CC33EF262EF04187B0F0EE756FA984DF2EAD37B1E4ADB064C3C5038F2E3B2D661B1C1150AAEB5425512E14D7506166D92D4533872E662F4B2D1428AAB5CCA72E75AA2EF325E196A5A02E2E8278873C64375845994B0F39BE2FF7B478332A7B0AA5E48877C47B6F513E997848AF8CCB8A899F3393AB35333CF0871E36698193862D486B4B9078B70C0A0A507B8A250F3F876F5A067632D5E65193E4445A1EC3A2C9B4C6F07AC334F0F62BC33357CB502E9B1C19D2398B6972AEC2EF21630F8C9134C4F7DD662D8AD7BDC9E19C46720F334B66C22D4BF32ED275144E20E7669FFCF6FC143667C9F02A577F32960FA9F2371BE1FA90E49CBC69C01531F140556854D588DD0E55E1307D78CA38E975CD999F9AEA604266329EE62BFB5ADDA67F549E211ECFBA906C60063696352ABB82AA782D25B17E872EA587871F450446DB1BAE0123D20404A8F2D2698B371002E986C8FCB969A99FF0E150A2709E2ED7633D02ADA87D5B3C9487D27B2BD9D21E2EC3215DCC3CDCD884371281B95A2E9987AAF82EB499C058D9C3E7DC1B66635F60DB121C72F929622DD47B6B2E69F59FF2AE6B63CC2EC60FFBA20EA50569DBAB5DAEFAEB4F03966C9637AB55662EDD28439155A82D053A5299448EDB2E7BEB0F62889E2F84E6C7F34B3212C9AAC32D521D5AB8480993F1906D5450FAB342A0FA6ED223E178BAC036B81E15783604C32A961EA1EF20BE2EBB93D34ED37BC03142B7583303E4557E48551E4BD7CBDDEA146D5485A5D212C35189F0BD6497E66912D2780A59A53B532E12C0A5ED1EC0445D96E8F2DD825221CFE4A65A87AA21DC8750481B9849DD81694C3357A0ED9B78D608D8EDDE28FAFBEC17844DE5709F41E121838DB55639D77E32A259A416D7013B2EB1259FDE1B498CBB9CAEE1D601DF3C915EA91C69B44E6B72062F5F4B3C73F06F2D5AD185E1692E2E0A01E7DD5133693681C52EE13B2BE42D03BDCF48E4E133CF06662339B778E1C3034F9939A433E157449172F7969ACCE1F5D2F65A4E09E4A5D5611EBEDDDBDB0C0C0A); + --echo End of 5.0 tests @@ -532,5 +539,19 @@ SET character_set_filesystem=default; select @@character_set_filesystem; +--echo # +--echo # Bug #51850: crash/memory overlap when using load data infile and set +--echo # col equal to itself! +--echo # + +CREATE TABLE t1(col0 LONGBLOB); +SELECT 'test' INTO OUTFILE 't1.txt'; +LOAD DATA INFILE 't1.txt' IGNORE INTO TABLE t1 SET col0=col0; +SELECT * FROM t1; + +DROP TABLE t1; +let $MYSQLD_DATADIR= `select @@datadir`; +remove_file $MYSQLD_DATADIR/test/t1.txt; + --echo End of 5.1 tests diff --git a/mysql-test/t/log_tables_upgrade.test b/mysql-test/t/log_tables_upgrade.test new file mode 100644 index 00000000000..5d1b2b5aed6 --- /dev/null +++ b/mysql-test/t/log_tables_upgrade.test @@ -0,0 +1,32 @@ +--source include/not_embedded.inc +--source include/have_csv.inc + +# Only run test if "mysql_upgrade" is found +--require r/have_mysql_upgrade.result +--disable_query_log +select LENGTH("$MYSQL_UPGRADE")>0 as have_mysql_upgrade; +--enable_query_log + +--echo # +--echo # Bug#49823: mysql_upgrade fatal error due to general_log / slow_low CSV NULL +--echo # + +USE test; + +let $MYSQLD_DATADIR= `SELECT @@datadir`; +copy_file std_data/bug49823.frm $MYSQLD_DATADIR/test/bug49823.frm; +copy_file std_data/bug49823.CSM $MYSQLD_DATADIR/test/bug49823.CSM; +copy_file std_data/bug49823.CSV $MYSQLD_DATADIR/test/bug49823.CSV; + +SET @saved_general_log = @@GLOBAL.general_log; +SET GLOBAL general_log = OFF; +USE mysql; +FLUSH TABLES; +REPAIR TABLE test.bug49823; +RENAME TABLE general_log TO renamed_general_log; +RENAME TABLE test.bug49823 TO general_log; +--exec $MYSQL_UPGRADE --skip-verbose --force 2>&1 +DROP TABLE general_log; +RENAME TABLE renamed_general_log TO general_log; +SET GLOBAL general_log = @saved_general_log; +USE test; diff --git a/mysql-test/t/merge.test b/mysql-test/t/merge.test index f44f4649099..df99d6150ab 100644 --- a/mysql-test/t/merge.test +++ b/mysql-test/t/merge.test @@ -12,6 +12,8 @@ drop table if exists t1,t2,t3,t4,t5,t6; drop database if exists mysqltest; --enable_warnings +let $MYSQLD_DATADIR= `select @@datadir`; + create table t1 (a int not null primary key auto_increment, message char(20)); create table t2 (a int not null primary key auto_increment, message char(20)); INSERT INTO t1 (message) VALUES ("Testing"),("table"),("t1"); @@ -1643,6 +1645,76 @@ SHOW CREATE TRIGGER tr1; DROP TRIGGER tr1; DROP TABLE t1, t2, t3; +--echo # +--echo # BUG#48265 - MRG_MYISAM problem (works in 5.0.85, does't work in 5.1.40) +--echo # +CREATE DATABASE `test/1`; + +CREATE TABLE `test/1`.`t/1`(a INT); +CREATE TABLE m1(a INT) ENGINE=MERGE UNION=(`test/1`.`t/1`); +SELECT * FROM m1; +SHOW CREATE TABLE m1; +DROP TABLE m1; + +CREATE TABLE `test/1`.m1(a INT) ENGINE=MERGE UNION=(`test/1`.`t/1`); +SELECT * FROM `test/1`.m1; +SHOW CREATE TABLE `test/1`.m1; +DROP TABLE `test/1`.m1; +DROP TABLE `test/1`.`t/1`; + +CREATE TEMPORARY TABLE `test/1`.`t/1`(a INT); +CREATE TEMPORARY TABLE m1(a INT) ENGINE=MERGE UNION=(`test/1`.`t/1`); +SELECT * FROM m1; +SHOW CREATE TABLE m1; +DROP TABLE m1; + +CREATE TEMPORARY TABLE `test/1`.m1(a INT) ENGINE=MERGE UNION=(`test/1`.`t/1`); +SELECT * FROM `test/1`.m1; +SHOW CREATE TABLE `test/1`.m1; +DROP TABLE `test/1`.m1; +DROP TABLE `test/1`.`t/1`; + +DROP DATABASE `test/1`; + +# Test compatibility. Use '@' instead of '/' (was not allowed in 5.0) + +CREATE TABLE `t@1`(a INT); +copy_file std_data/bug48265.frm $MYSQLD_DATADIR/test/m1.frm; +write_file $MYSQLD_DATADIR/test/m1.MRG; +t@1 +EOF +SELECT * FROM m1; +SHOW CREATE TABLE m1; +DROP TABLE `t@1`; + +CREATE DATABASE `test@1`; +CREATE TABLE `test@1`.`t@1`(a INT); +FLUSH TABLE m1; +remove_file $MYSQLD_DATADIR/test/m1.MRG; +write_file $MYSQLD_DATADIR/test/m1.MRG; +./test@1/t@1 +EOF +SELECT * FROM m1; +SHOW CREATE TABLE m1; +DROP TABLE m1; +DROP TABLE `test@1`.`t@1`; +DROP DATABASE `test@1`; + +--echo # +--echo # Bug#51494c rash with join, explain and 'sounds like' operator +--echo # + +CREATE TABLE t1 (a INT) ENGINE=MYISAM; +INSERT INTO t1 VALUES(1); + +CREATE TABLE t2 (b INT NOT NULL,c INT,d INT,e BLOB NOT NULL, +KEY idx0 (d, c)) ENGINE=MERGE; + +EXPLAIN SELECT * FROM t1 NATURAL RIGHT JOIN +t2 WHERE b SOUNDS LIKE e AND d = 1; + +DROP TABLE t2, t1; + --echo End of 5.1 tests --disable_result_log diff --git a/mysql-test/t/metadata.test b/mysql-test/t/metadata.test index a10767579fb..9bfb47c53be 100644 --- a/mysql-test/t/metadata.test +++ b/mysql-test/t/metadata.test @@ -129,4 +129,17 @@ SELECT COALESCE(d, d), IFNULL(d, d), IF(i, d, d), DROP TABLE t1; +--echo # +--echo # Bug#41788 mysql_fetch_field returns org_table == table by a view +--echo # + +CREATE TABLE t1 (f1 INT); +CREATE VIEW v1 AS SELECT f1 FROM t1; +--enable_metadata +SELECT f1 FROM v1 va; +--disable_metadata + +DROP VIEW v1; +DROP TABLE t1; + --echo End of 5.0 tests diff --git a/mysql-test/t/multi_update.test b/mysql-test/t/multi_update.test index ebf6a6219fc..b32a329f827 100644 --- a/mysql-test/t/multi_update.test +++ b/mysql-test/t/multi_update.test @@ -641,5 +641,17 @@ drop table t1, t2, t3; # Add further tests from here # +--echo # +--echo # Bug#49534: multitable IGNORE update with sql_safe_updates error +--echo # causes debug assertion +--echo # +CREATE TABLE t1( a INT, KEY( a ) ); +INSERT INTO t1 VALUES (1), (2), (3); +SET SESSION sql_safe_updates = 1; +--echo # Must not cause failed assertion +--error ER_UPDATE_WITHOUT_KEY_IN_SAFE_MODE +UPDATE IGNORE t1, t1 t1a SET t1.a = 1 WHERE t1a.a = 1; +DROP TABLE t1; + --echo end of tests diff --git a/mysql-test/t/myisam.test b/mysql-test/t/myisam.test index e1982a623da..27b5a38978b 100644 --- a/mysql-test/t/myisam.test +++ b/mysql-test/t/myisam.test @@ -1204,6 +1204,17 @@ CHECKSUM TABLE t1 EXTENDED; DROP TABLE t1; +--echo # +--echo # BUG#48438 - crash with error in unioned query against merge table and view... +--echo # +SET GLOBAL table_open_cache=3; +CREATE TABLE t1(a INT); +SELECT 1 FROM t1 AS a1, t1 AS a2, t1 AS a3, t1 AS a4 FOR UPDATE; +SELECT TABLE_ROWS, DATA_LENGTH FROM INFORMATION_SCHEMA.TABLES + WHERE TABLE_SCHEMA='test' AND TABLE_NAME='t1'; +DROP TABLE t1; +SET GLOBAL table_open_cache=DEFAULT; + --echo End of 5.0 tests @@ -1609,4 +1620,75 @@ SET myisam_sort_buffer_size=@@global.myisam_sort_buffer_size; CHECK TABLE t1; DROP TABLE t1; + +--echo # +--echo # BUG#49628 - corrupt table after legal SQL, LONGTEXT column +--echo # +CREATE TABLE t1(a INT, b LONGTEXT, UNIQUE(a)); +REPLACE INTO t1 VALUES +(1, REPEAT('a', 129015)),(1, NULL), +(2, NULL),(3, NULL),(4, NULL),(5, NULL),(6, NULL),(7, NULL), +(1, REPEAT('b', 129016)),(1, NULL), +(1, REPEAT('c', 129015)),(1, REPEAT('d', 129015)); +CHECK TABLE t1; +DROP TABLE t1; + + +--echo # +--echo # Bug#51304: checksum table gives different results +--echo # for same data when using bit fields +--echo # +CREATE TABLE t1(a INT, b BIT(1)); +INSERT INTO t1 VALUES(1, 0), (2, 1); +CREATE TABLE t2 SELECT * FROM t1; +--copy_file $MYSQLD_DATADIR/test/t1.frm $MYSQLD_DATADIR/test/t3.frm +--copy_file $MYSQLD_DATADIR/test/t1.MYD $MYSQLD_DATADIR/test/t3.MYD +--copy_file $MYSQLD_DATADIR/test/t1.MYI $MYSQLD_DATADIR/test/t3.MYI +CHECKSUM TABLE t1 EXTENDED; +CHECKSUM TABLE t2 EXTENDED; +CHECKSUM TABLE t3 EXTENDED; +DROP TABLE t1, t2, t3; + +--echo # +--echo # BUG#51307 - widespread corruption with partitions and insert...select +--echo # +CREATE TABLE t1(a CHAR(255), KEY(a)); +SELECT * FROM t1, t1 AS a1; +SET myisam_sort_buffer_size=4; +INSERT INTO t1 VALUES +('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'), +('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'), +('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'), +('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'), +('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'), +('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'), +('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'), +('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'), +('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'), +('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'), +('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'), +('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'), +('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'), +('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'), +('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'),('0'), +('0'),('0'),('0'),('0'),('0'),('0'),('0'); +SET myisam_sort_buffer_size=@@global.myisam_sort_buffer_size; +INSERT INTO t1 VALUES('1'); +SELECT * FROM t1, t1 AS a1 WHERE t1.a=1 AND a1.a=1; +DROP TABLE t1; + +--echo # +--echo # BUG#47444 - --myisam_repair_threads>1can result in all index +--echo # cardinalities=1 +--echo # +SET myisam_repair_threads=2; +SET myisam_sort_buffer_size=4096; +CREATE TABLE t1(a CHAR(255), KEY(a), KEY(a), KEY(a)); +INSERT INTO t1 VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(0),(1),(2),(3); +REPAIR TABLE t1; +SELECT CARDINALITY FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA='test' AND TABLE_NAME='t1'; +DROP TABLE t1; +SET myisam_sort_buffer_size=@@global.myisam_sort_buffer_size; +SET myisam_repair_threads=@@global.myisam_repair_threads; + --echo End of 5.1 tests diff --git a/mysql-test/t/mysql_upgrade.test b/mysql-test/t/mysql_upgrade.test index 87457c36382..485302600c1 100644 --- a/mysql-test/t/mysql_upgrade.test +++ b/mysql-test/t/mysql_upgrade.test @@ -33,7 +33,7 @@ if (`SELECT $VALGRIND_TEST`) # expected binaries it uses. # --echo Run mysql_upgrade once ---exec $MYSQL_UPGRADE --skip-verbose 2>&1 +--exec $MYSQL_UPGRADE --skip-verbose --force 2>&1 # It should have created a file in the MySQL Servers datadir let $MYSQLD_DATADIR= `select @@datadir`; diff --git a/mysql-test/t/mysqlbinlog.test b/mysql-test/t/mysqlbinlog.test index 687ad62b17c..7c9fbf031bb 100644 --- a/mysql-test/t/mysqlbinlog.test +++ b/mysql-test/t/mysqlbinlog.test @@ -434,12 +434,43 @@ FLUSH LOGS; # Transaction3 end --echo # ---echo # Test if the 'BEGIN', 'ROLLBACK' and 'COMMIT' are output if the database specified is exist +--echo # Test if the 'BEGIN', 'ROLLBACK' and 'COMMIT' are output if the database specified exists --exec $MYSQL_BINLOG --database=test --short-form $MYSQLTEST_VARDIR/std_data/binlog_transaction.000001 --echo # ---echo # Test if the 'BEGIN', 'ROLLBACK' and 'COMMIT' are output if the database specified is not exist +--echo # Test if the 'BEGIN', 'ROLLBACK' and 'COMMIT' are output if the database specified does not exist --exec $MYSQL_BINLOG --database=not_exist --short-form $MYSQLTEST_VARDIR/std_data/binlog_transaction.000001 +--echo # +--echo # Test if the 'SAVEPOINT', 'ROLLBACK TO' are output if the database specified exists +--exec $MYSQL_BINLOG --database=db1 --short-form $MYSQLTEST_VARDIR/std_data/binlog_savepoint.000001 +--echo # +--echo # Test if the 'SAVEPOINT', 'ROLLBACK TO' are output if the database specified does not exist +--exec $MYSQL_BINLOG --database=not_exist --short-form $MYSQLTEST_VARDIR/std_data/binlog_savepoint.000001 + --echo End of 5.0 tests --echo End of 5.1 tests + +# +# BUG#38468 Memory leak detected when using mysqlbinlog utility; +# +disable_query_log; +RESET MASTER; +CREATE TABLE t1 SELECT 1; +FLUSH LOGS; +DROP TABLE t1; +enable_query_log; + +# Write an empty file for comparison +write_file $MYSQLTEST_VARDIR/tmp/mysqlbinlog.warn.empty; +EOF + +# Before fix of BUG#38468, this would generate some warnings +--exec $MYSQL_BINLOG $MYSQLD_DATADIR/master-bin.000001 >/dev/null 2> $MYSQLTEST_VARDIR/tmp/mysqlbinlog.warn + +# Make sure the command above does not generate any error or warnings +diff_files $MYSQLTEST_VARDIR/tmp/mysqlbinlog.warn $MYSQLTEST_VARDIR/tmp/mysqlbinlog.warn.empty; + +# Cleanup for this part of test +remove_file $MYSQLTEST_VARDIR/tmp/mysqlbinlog.warn.empty; +remove_file $MYSQLTEST_VARDIR/tmp/mysqlbinlog.warn; diff --git a/mysql-test/t/mysqltest.test b/mysql-test/t/mysqltest.test index c86ebda3a24..b103140190f 100644 --- a/mysql-test/t/mysqltest.test +++ b/mysql-test/t/mysqltest.test @@ -613,6 +613,15 @@ echo ; --error 1 --exec echo "--exec " | $MYSQL_TEST 2>&1 +# Multi-line exec +exec $MYSQL + test -e "select 1"; +exec $MYSQL test -e "select + 2"; +let $query = select 3 + as X; +exec $MYSQL test -e "$query"; + # ---------------------------------------------------------------------------- # Test let command # ---------------------------------------------------------------------------- @@ -919,6 +928,28 @@ sleep 0.5; sleep 1; real_sleep 1; +# Parameter from variable, legal and illegal +let $sleep_var= 0.1; +sleep $sleep_var; +let $sleep_var= 1; +--real_sleep $sleep_var + +--write_file $MYSQL_TMP_DIR/sleep.inc +let $sleep_var= xyz; +--sleep $sleep_var +EOF +--error 1 +--exec $MYSQL_TEST < $MYSQL_TMP_DIR/sleep.inc 2>&1 +--remove_file $MYSQL_TMP_DIR/sleep.inc + +--write_file $MYSQL_TMP_DIR/sleep.inc +let $sleep_var= xyz; +real_sleep $sleep_var; +EOF +--error 1 +--exec $MYSQL_TEST < $MYSQL_TMP_DIR/sleep.inc 2>&1 +--remove_file $MYSQL_TMP_DIR/sleep.inc + # Missing parameter --error 1 --exec echo "sleep ;" | $MYSQL_TEST 2>&1 @@ -1014,6 +1045,37 @@ echo test3stop --delimiter ; echo test4; +# ---------------------------------------------------------------------------- +# Test that delimiter within if() works in in various combinations +# ---------------------------------------------------------------------------- + +if (0) +{ + delimiter ||; + echo false-inner|| + if (0) + { + delimiter *|| + echo false-innerer* + delimiter ||* + } + echo false-inner again|| +} +echo outer; +if (1) +{ + delimiter /; + echo true-inner/ + if (0) + { + delimiter %/ + echo true-innerer% + } + echo true-inner again/ +} +echo true-outer/ +delimiter ;/ + # ---------------------------------------------------------------------------- # Test if @@ -1293,6 +1355,17 @@ select "a" as col1, "c" as col2; --error 1 --exec echo "--replace_column 1 b c " | $MYSQL_TEST 2>&1 +let $long_rep= 1234567890123456789012345678901234567890; +let $long_rep= $long_rep,$long_rep; +let $long_rep= $long_rep,$long_rep; +let $long_rep= $long_rep,$long_rep; +let $long_rep= $long_rep,$long_rep; +let $long_rep= $long_rep,$long_rep; + +# This tests from strings > 1024 (here 1311) + +--replace_result $long_rep LONG_STRING +eval select "$long_rep" as x; # ---------------------------------------------------------------------------- # Test sync_with_master @@ -1614,6 +1687,57 @@ select * from t1; drop table t1; # ---------------------------------------------------------------------------- +# Tests of send +# ---------------------------------------------------------------------------- + +create table t1 ( f1 char(10)); +insert into t1 values ("Abcd"); + +# 1. Basic test + +send select * from t1; +reap; + +# 2. Test with error + +--send select * from t2; +--error ER_NO_SUCH_TABLE +--reap + +# 3. test send of next stmt + +--send +select * from t1; +--reap + +# 4. Non-query stmt betwen send and reap allowed + +--send select * from t1; +--sleep 0.05 +--echo Result coming up +--reap + +# 5. Test of send_eval + +--let $my_stmt= select * from t1; +--send_eval $my_stmt +--reap + +# 6. Test that mysqltest does not allow query stmt between send and reap +# Untestable directly as it causes mysqltest to fail + +--write_file $MYSQLTEST_VARDIR/tmp/mysqltest.in +--send select * from t1; +select 1; +--reap +EOF +--error 1 +--exec $MYSQL_TEST < $MYSQLTEST_VARDIR/tmp/mysqltest.in 2>&1 +remove_file $MYSQLTEST_VARDIR/tmp/mysqltest.in; + +drop table t1; + +# ---------------------------------------------------------------------------- # test for remove_file # ---------------------------------------------------------------------------- @@ -1624,6 +1748,19 @@ drop table t1; remove_file non_existing_file; # ---------------------------------------------------------------------------- +# test for remove_files_wildcard +# ---------------------------------------------------------------------------- + +--error 1 +--exec echo "remove_files_wildcard ;" | $MYSQL_TEST 2>&1 + +--error 1 +remove_files_wildcard non_existing_dir; + +--error 1 +remove_files_wildcard non_existing_dir non_existing_file; + +# ---------------------------------------------------------------------------- # test for write_file # ---------------------------------------------------------------------------- --error 1 @@ -1913,6 +2050,20 @@ perl; print "hello\n"; EOF +# Test perl within while, also with if being false first iteration +let $outer= 3; +let $ifval= 0; +while ($outer) { + if ($ifval) { + perl UNTIL; + my $val= 5; + print "val is $val\n"; +UNTIL + } + inc $ifval; + dec $outer; +} + # ---------------------------------------------------------------------------- # test for die # ---------------------------------------------------------------------------- @@ -2053,6 +2204,44 @@ INSERT INTO t1 SELECT f1 - 512 FROM t1; SELECT * FROM t1; --enable_result_log DROP TABLE t1; + +# ---------------------------------------------------------------------------- +# test for lowercase_result +# ---------------------------------------------------------------------------- + +# 1. Basic test +--lowercase_result +SELECT "500g BLÅBÆRSYLTETØY" AS "WILL BE lower cased"; + +# 2. test that it does not apply to next statement +SELECT "UPPER" AS "WILL NOT BE lower cased"; + +# 3. test that it does not affect non-SQL or the following statement +--lowercase_result +--echo UP +SELECT 0 as "UP AGAIN"; + +# 4. test that it works with eval and variables +let $lower_stmt=SELECT "ABCdef" AS "uvwXYZ"; +--lowercase_result +eval $lower_stmt; + +# 5. test that it works in combination with sort +sorted_result; +lowercase_result; +SELECT "Xyz" AS Name UNION SELECT "Abc" as Name ORDER BY Name DESC; + +# 6. Test combination with replace, and that lower casing is done first +--lowercase_result +--replace_result old new +SELECT 1 as "SOME OLD TEXT"; + +# 7. Test missing lower casing of "unknown" characters +--character_set utf8 +--lowercase_result +SELECT 0 as "WILL NOT lower case ÄËÐ"; +--character_set latin1 + # ---------------------------------------------------------------------------- # Some coverage tests # ---------------------------------------------------------------------------- @@ -2238,9 +2427,14 @@ rmdir $MYSQLTEST_VARDIR/tmp/testdir; cat_file $MYSQLTEST_VARDIR/tmp/testdir/file3.txt; -remove_file $MYSQLTEST_VARDIR/tmp/testdir/file1.txt; -remove_file $MYSQLTEST_VARDIR/tmp/testdir/file2.txt; -remove_file $MYSQLTEST_VARDIR/tmp/testdir/file3.txt; +list_files_write_file $MYSQLTEST_VARDIR/tmp/testdir/file11.txt $MYSQLTEST_VARDIR/tmp/testdir file?.txt; +remove_files_wildcard $MYSQLTEST_VARDIR/tmp/testdir file?.txt; +list_files_write_file $MYSQLTEST_VARDIR/tmp/testdir/dir-list.txt $MYSQLTEST_VARDIR/tmp/testdir file*.txt; +cat_file $MYSQLTEST_VARDIR/tmp/testdir/dir-list.txt; +remove_files_wildcard $MYSQLTEST_VARDIR/tmp/testdir file*.txt; +list_files $MYSQLTEST_VARDIR/tmp/testdir; +remove_files_wildcard $MYSQLTEST_VARDIR/tmp/testdir; +list_files $MYSQLTEST_VARDIR/tmp/testdir; rmdir $MYSQLTEST_VARDIR/tmp/testdir; # diff --git a/mysql-test/t/no_binlog.test b/mysql-test/t/no_binlog.test new file mode 100644 index 00000000000..fa9c87079de --- /dev/null +++ b/mysql-test/t/no_binlog.test @@ -0,0 +1,6 @@ +-- source include/not_embedded.inc + +# BUG#50780: 'show binary logs' debug assertion when binary logging is disabled + +-- error ER_NO_BINARY_LOGGING +SHOW BINARY LOGS; diff --git a/mysql-test/t/partition.test b/mysql-test/t/partition.test index af8cb1c168c..e6e000ec916 100644 --- a/mysql-test/t/partition.test +++ b/mysql-test/t/partition.test @@ -2085,4 +2085,17 @@ INSERT INTO t1 VALUES (6,8,10); SELECT 1 FROM t1 JOIN t1 AS t2 USING (a) FOR UPDATE; DROP TABLE t1; + +--echo # +--echo # BUG#51868 - crash with myisam_use_mmap and partitioned myisam tables +--echo # +SET GLOBAL myisam_use_mmap=1; +CREATE TABLE t1(a INT) PARTITION BY HASH(a) PARTITIONS 1; +INSERT INTO t1 VALUES(0); +FLUSH TABLE t1; +TRUNCATE TABLE t1; +INSERT INTO t1 VALUES(0); +DROP TABLE t1; +SET GLOBAL myisam_use_mmap=default; + --echo End of 5.1 tests diff --git a/mysql-test/t/partition_debug_sync.test b/mysql-test/t/partition_debug_sync.test new file mode 100644 index 00000000000..69bc86a8bb7 --- /dev/null +++ b/mysql-test/t/partition_debug_sync.test @@ -0,0 +1,81 @@ +#--disable_abort_on_error +# +# Test for the partition storage engine which require DEBUG_SYNC feature to +# Created by Mattias Jonsson +# +--source include/have_partition.inc +--source include/have_debug_sync.inc + +--disable_warnings +DROP TABLE IF EXISTS t1, t2; +SET DEBUG_SYNC= 'RESET'; +--enable_warnings + +--echo # +--echo # Bug#42438: Crash ha_partition::change_table_ptr +--echo # Test when remove partitioning is done while drop table is waiting +--echo # for the table. +connect(con1, localhost, root,,); +--echo # Con 1 +SET DEBUG_SYNC= 'RESET'; +CREATE TABLE t1 +(a INTEGER, + b INTEGER NOT NULL, + KEY (b)) +ENGINE = MYISAM +/*!50100 PARTITION BY RANGE (a) +(PARTITION p0 VALUES LESS THAN (2), + PARTITION p1 VALUES LESS THAN (20), + PARTITION p2 VALUES LESS THAN (100), + PARTITION p3 VALUES LESS THAN MAXVALUE ) */; +SET DEBUG_SYNC= 'alter_table_before_create_table_no_lock SIGNAL removing_partitioning WAIT_FOR waiting_for_alter'; +SET DEBUG_SYNC= 'alter_table_before_main_binlog SIGNAL partitioning_removed'; +--send ALTER TABLE t1 REMOVE PARTITIONING +connection default; +--echo # Con default +SET DEBUG_SYNC= 'now WAIT_FOR removing_partitioning'; +SET DEBUG_SYNC= 'waiting_for_table SIGNAL waiting_for_alter'; +SET DEBUG_SYNC= 'rm_table_part2_before_delete_table WAIT_FOR partitioning_removed'; +DROP TABLE IF EXISTS t1; +--echo # Con 1 +connection con1; +--reap +connection default; +SET DEBUG_SYNC= 'RESET'; +connection con1; +SET DEBUG_SYNC= 'RESET'; + +--echo # +--echo # Bug#42438: Crash ha_partition::change_table_ptr +--echo # Test when remove partitioning is failing due to drop table is already +--echo # in progress. +CREATE TABLE t2 +(a INTEGER, + b INTEGER NOT NULL, + KEY (b)) +ENGINE = MYISAM +/*!50100 PARTITION BY RANGE (a) +(PARTITION p0 VALUES LESS THAN (2), + PARTITION p1 VALUES LESS THAN (20), + PARTITION p2 VALUES LESS THAN (100), + PARTITION p3 VALUES LESS THAN MAXVALUE ) */; +SET DEBUG_SYNC= 'before_lock_tables_takes_lock SIGNAL removing_partitions WAIT_FOR waiting_for_alter'; +SET DEBUG_SYNC= 'alter_table_before_rename_result_table WAIT_FOR delete_done'; +--send ALTER TABLE t2 REMOVE PARTITIONING +connection default; +--echo # Con default +SET DEBUG_SYNC= 'now WAIT_FOR removing_partitions'; +SET DEBUG_SYNC= 'waiting_for_table SIGNAL waiting_for_alter'; +SET DEBUG_SYNC= 'rm_table_part2_before_binlog SIGNAL delete_done'; +DROP TABLE IF EXISTS t2; +--echo # Con 1 +connection con1; +--error ER_NO_SUCH_TABLE +--reap +SET DEBUG_SYNC= 'RESET'; +disconnect con1; +connection default; +--echo # Con default +SET DEBUG_SYNC= 'RESET'; + +--echo End of 5.1 tests diff --git a/mysql-test/t/partition_error.test b/mysql-test/t/partition_error.test index 1f011f36257..8da8f54b774 100644 --- a/mysql-test/t/partition_error.test +++ b/mysql-test/t/partition_error.test @@ -8,6 +8,30 @@ drop table if exists t1; --enable_warnings +--echo # +--echo # Bug#50392: insert_id is not reset for partitioned tables +--echo # auto_increment on duplicate entry +CREATE TABLE t1 (a INT AUTO_INCREMENT PRIMARY KEY); +SET INSERT_ID= 13; +INSERT INTO t1 VALUES (NULL); +SET INSERT_ID= 12; +--error ER_DUP_ENTRY +INSERT INTO t1 VALUES (NULL), (NULL), (NULL); +SHOW CREATE TABLE t1; +INSERT INTO t1 VALUES (NULL); +SELECT * FROM t1; +DROP TABLE t1; +CREATE TABLE t1 (a INT AUTO_INCREMENT PRIMARY KEY) PARTITION BY KEY(a); +SET INSERT_ID= 13; +INSERT INTO t1 VALUES (NULL); +SET INSERT_ID= 12; +--error ER_DUP_ENTRY +INSERT INTO t1 VALUES (NULL), (NULL), (NULL); +SHOW CREATE TABLE t1; +INSERT INTO t1 VALUES (NULL); +SELECT * FROM t1; +DROP TABLE t1; + # # Bug#38719: Partitioning returns a different error code for a # duplicate key error diff --git a/mysql-test/t/partition_innodb.test b/mysql-test/t/partition_innodb.test index 91ecfa15d60..0a32e6104b3 100644 --- a/mysql-test/t/partition_innodb.test +++ b/mysql-test/t/partition_innodb.test @@ -2,11 +2,70 @@ --source include/have_innodb.inc --disable_warnings -drop table if exists t1; +drop table if exists t1, t2; --enable_warnings let $MYSQLD_DATADIR= `SELECT @@datadir`; +--echo # +--echo # Bug#51830: Incorrect partition pruning on range partition (regression) +--echo # +CREATE TABLE t1 (a INT NOT NULL) +ENGINE = InnoDB +PARTITION BY RANGE(a) +(PARTITION p10 VALUES LESS THAN (10), + PARTITION p30 VALUES LESS THAN (30), + PARTITION p50 VALUES LESS THAN (50), + PARTITION p70 VALUES LESS THAN (70), + PARTITION p90 VALUES LESS THAN (90)); +INSERT INTO t1 VALUES (10),(30),(50); +INSERT INTO t1 VALUES (70); +INSERT INTO t1 VALUES (80); +INSERT INTO t1 VALUES (89); +--error ER_NO_PARTITION_FOR_GIVEN_VALUE +INSERT INTO t1 VALUES (90); +--error ER_NO_PARTITION_FOR_GIVEN_VALUE +INSERT INTO t1 VALUES (100); +--error ER_NO_PARTITION_FOR_GIVEN_VALUE +insert INTO t1 VALUES (110); +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > 90; +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= 90; +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = 90; +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = 89; +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= 89; +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > 89; +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = 100; +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= 100; +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > 100; +DROP TABLE t1; + +--echo # +--echo # Bug#50104: Partitioned table with just 1 partion works with fk +--echo # +CREATE TABLE t2 ( + id INT, + PRIMARY KEY (id) +) ENGINE=InnoDB ; + +CREATE TABLE t1 ( + id INT NOT NULL AUTO_INCREMENT, + parent_id INT DEFAULT NULL, + PRIMARY KEY (id), + KEY parent_id (parent_id) +) ENGINE=InnoDB; + +ALTER TABLE t1 PARTITION BY HASH (id) PARTITIONS 1; + +--error ER_FOREIGN_KEY_ON_PARTITIONED +ALTER TABLE t1 ADD CONSTRAINT test_ibfk_1 FOREIGN KEY (parent_id) REFERENCES t2 (id); + +ALTER TABLE t1 PARTITION BY HASH (id) PARTITIONS 2; + +--error ER_FOREIGN_KEY_ON_PARTITIONED +ALTER TABLE t1 ADD CONSTRAINT test_ibfk_1 FOREIGN KEY (parent_id) REFERENCES t2 (id); + +DROP TABLE t1, t2; + # # Bug#47029: Crash when reorganize partition with subpartition # diff --git a/mysql-test/t/partition_innodb_semi_consistent.test b/mysql-test/t/partition_innodb_semi_consistent.test index 5eda2f142c6..2711d79f194 100644 --- a/mysql-test/t/partition_innodb_semi_consistent.test +++ b/mysql-test/t/partition_innodb_semi_consistent.test @@ -187,7 +187,7 @@ SELECT * FROM t1; --echo # Switch to connection con2 connection con2; ---reap +reap; SELECT * FROM t1; connection default; diff --git a/mysql-test/t/partition_pruning.test b/mysql-test/t/partition_pruning.test index 315b8393d93..358832496e5 100644 --- a/mysql-test/t/partition_pruning.test +++ b/mysql-test/t/partition_pruning.test @@ -702,15 +702,29 @@ insert into t7 values (10),(30),(50); # leftmost intervals explain partitions select * from t7 where a < 5; +explain partitions select * from t7 where a < 9; +explain partitions select * from t7 where a <= 9; +explain partitions select * from t7 where a = 9; +explain partitions select * from t7 where a >= 9; +explain partitions select * from t7 where a > 9; explain partitions select * from t7 where a < 10; explain partitions select * from t7 where a <= 10; explain partitions select * from t7 where a = 10; +explain partitions select * from t7 where a >= 10; +explain partitions select * from t7 where a > 10; #rightmost intervals +explain partitions select * from t7 where a < 89; +explain partitions select * from t7 where a <= 89; +explain partitions select * from t7 where a = 89; +explain partitions select * from t7 where a > 89; +explain partitions select * from t7 where a >= 89; explain partitions select * from t7 where a < 90; +explain partitions select * from t7 where a <= 90; explain partitions select * from t7 where a = 90; explain partitions select * from t7 where a > 90; explain partitions select * from t7 where a >= 90; +explain partitions select * from t7 where a > 91; # misc intervals explain partitions select * from t7 where a > 11 and a < 29; @@ -728,15 +742,29 @@ insert into t7 values (10),(30),(50); # leftmost intervals explain partitions select * from t7 where a < 5; +explain partitions select * from t7 where a < 9; +explain partitions select * from t7 where a <= 9; +explain partitions select * from t7 where a = 9; +explain partitions select * from t7 where a >= 9; +explain partitions select * from t7 where a > 9; explain partitions select * from t7 where a < 10; explain partitions select * from t7 where a <= 10; explain partitions select * from t7 where a = 10; +explain partitions select * from t7 where a >= 10; +explain partitions select * from t7 where a > 10; #rightmost intervals +explain partitions select * from t7 where a < 89; +explain partitions select * from t7 where a <= 89; +explain partitions select * from t7 where a = 89; +explain partitions select * from t7 where a > 89; +explain partitions select * from t7 where a >= 89; explain partitions select * from t7 where a < 90; +explain partitions select * from t7 where a <= 90; explain partitions select * from t7 where a = 90; explain partitions select * from t7 where a > 90; explain partitions select * from t7 where a >= 90; +explain partitions select * from t7 where a > 91; # misc intervals explain partitions select * from t7 where a > 11 and a < 29; diff --git a/mysql-test/t/partition_range.test b/mysql-test/t/partition_range.test index c02d9049f2e..4d011ddc468 100644 --- a/mysql-test/t/partition_range.test +++ b/mysql-test/t/partition_range.test @@ -9,6 +9,24 @@ drop table if exists t1, t2; --enable_warnings +--echo # +--echo # Bug#48229: group by performance issue of partitioned table +--echo # +CREATE TABLE t1 ( + a INT, + b INT, + KEY a (a,b) +) +PARTITION BY HASH (a) PARTITIONS 1; + +# insert some rows (i.e. so that rows/blocks > 1) +INSERT INTO t1 VALUES (0, 580092), (3, 894076), (4, 805483), (4, 913540), (6, 611137), (8, 171602), (9, 599495), (9, 746305), (10, 272829), (10, 847519), (12, 258869), (12, 929028), (13, 288970), (15, 20971), (15, 105839), (16, 788272), (17, 76914), (18, 827274), (19, 802258), (20, 123677), (20, 587729), (22, 701449), (25, 31565), (25, 230782), (25, 442887), (25, 733139), (25, 851020); + +# Before the fix the 'Extra' column showed 'Using index for group-by' +EXPLAIN SELECT a, MAX(b) FROM t1 WHERE a IN (10, 100, 3) GROUP BY a; + +DROP TABLE t1; + # # BUG 33429: Succeeds in adding partition when maxvalue on last partition # diff --git a/mysql-test/t/plugin_not_embedded-master.opt b/mysql-test/t/plugin_not_embedded-master.opt new file mode 100644 index 00000000000..367d5233e0e --- /dev/null +++ b/mysql-test/t/plugin_not_embedded-master.opt @@ -0,0 +1 @@ +$EXAMPLE_PLUGIN_OPT diff --git a/mysql-test/t/plugin_not_embedded.test b/mysql-test/t/plugin_not_embedded.test new file mode 100644 index 00000000000..15aff548c29 --- /dev/null +++ b/mysql-test/t/plugin_not_embedded.test @@ -0,0 +1,20 @@ +--source include/not_embedded.inc +--source include/have_example_plugin.inc + +--echo # +--echo # Bug#51770: UNINSTALL PLUGIN requires no privileges +--echo # + +GRANT INSERT ON mysql.plugin TO bug51770@localhost; +connect(con1,localhost,bug51770,,); +--replace_regex /\.dll/.so/ +eval INSTALL PLUGIN example SONAME $HA_EXAMPLE_SO; +--error ER_TABLEACCESS_DENIED_ERROR +UNINSTALL PLUGIN example; +connection default; +GRANT DELETE ON mysql.plugin TO bug51770@localhost; +connection con1; +UNINSTALL PLUGIN example; +disconnect con1; +connection default; +DROP USER bug51770@localhost; diff --git a/mysql-test/t/query_cache_with_views.test b/mysql-test/t/query_cache_with_views.test index d4ebe45b7ac..97e37c53aa3 100644 --- a/mysql-test/t/query_cache_with_views.test +++ b/mysql-test/t/query_cache_with_views.test @@ -126,5 +126,22 @@ show status like "Qcache_inserts"; show status like "Qcache_hits"; drop table t1; +--echo # +--echo # Bug46615 Assertion in Query_cache::invalidate in INSERT in a VIEW of a MERGE table +--echo # +CREATE TABLE t1 (c1 INT, c2 INT); +CREATE TABLE t2 LIKE t1; +SET AUTOCOMMIT=OFF; +CREATE VIEW t1_view AS SELECT c1 FROM t1 NATURAL JOIN t2 ; +# Before the bug patch the below INSERT stmt used to +# crash when other fields than the ones listed in the +# view definition were used. +--error ER_BAD_FIELD_ERROR +INSERT INTO t1_view (c1, c2) SELECT c1, c2 FROM t1; +DROP TABLE t1; +DROP TABLE t2; +DROP VIEW t1_view; +SET AUTOCOMMIT=DEFAULT; + # Reset default environment. set GLOBAL query_cache_size=default; diff --git a/mysql-test/t/skip_name_resolve.test b/mysql-test/t/skip_name_resolve.test index df010d15fa0..0edbdbcd22c 100644 --- a/mysql-test/t/skip_name_resolve.test +++ b/mysql-test/t/skip_name_resolve.test @@ -29,3 +29,24 @@ disconnect con1; # Wait till all disconnects are completed --source include/wait_until_count_sessions.inc +--echo # +--echo # Bug #37168: Missing variable - skip_name_resolve +--echo # + +SHOW VARIABLES LIKE 'skip_name_resolve'; +SHOW GLOBAL VARIABLES LIKE 'skip_name_resolve'; +SHOW SESSION VARIABLES LIKE 'skip_name_resolve'; + +SELECT @@skip_name_resolve; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +SELECT @@LOCAL.skip_name_resolve; +SELECT @@GLOBAL.skip_name_resolve; + +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +SET @@skip_name_resolve=0; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +SET @@LOCAL.skip_name_resolve=0; +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +SET @@GLOBAL.skip_name_resolve=0; + +--echo End of 5.1 tests diff --git a/mysql-test/t/sp-bugs.test b/mysql-test/t/sp-bugs.test index 7b94e65a5e9..8aa0791e265 100644 --- a/mysql-test/t/sp-bugs.test +++ b/mysql-test/t/sp-bugs.test @@ -57,5 +57,48 @@ SELECT f2 (); DROP SCHEMA testdb; +USE test; + +--echo # +--echo # Bug#50423: Crash on second call of a procedure dropping a trigger +--echo # + +--disable_warnings +DROP TABLE IF EXISTS t1; +DROP TRIGGER IF EXISTS tr1; +DROP PROCEDURE IF EXISTS p1; +--enable_warnings + +CREATE TABLE t1 (f1 INTEGER); +CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW SET @aux = 1; +CREATE PROCEDURE p1 () DROP TRIGGER tr1; + +CALL p1 (); +--error ER_TRG_DOES_NOT_EXIST +CALL p1 (); + +DROP TABLE t1; +DROP PROCEDURE p1; + +--echo # +--echo # Bug#50423: Crash on second call of a procedure dropping a trigger +--echo # + +--disable_warnings +DROP TABLE IF EXISTS t1; +DROP TRIGGER IF EXISTS tr1; +DROP PROCEDURE IF EXISTS p1; +--enable_warnings + +CREATE TABLE t1 (f1 INTEGER); +CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW SET @aux = 1; +CREATE PROCEDURE p1 () DROP TRIGGER tr1; + +CALL p1 (); +--error ER_TRG_DOES_NOT_EXIST +CALL p1 (); + +DROP TABLE t1; +DROP PROCEDURE p1; --echo End of 5.1 tests diff --git a/mysql-test/t/sp_notembedded.test b/mysql-test/t/sp_notembedded.test index f8803e28e0a..e4b6a5deefe 100644 --- a/mysql-test/t/sp_notembedded.test +++ b/mysql-test/t/sp_notembedded.test @@ -367,6 +367,43 @@ let $wait_condition= --source include/wait_condition.inc DROP PROCEDURE p; +# +# Bug#47736 killing a select from a view when the view is processing a function, asserts +# +CREATE TABLE t1(a int); +INSERT INTO t1 VALUES (1); +CREATE FUNCTION f1 (inp TEXT) RETURNS INT NO SQL RETURN sleep(60); +CREATE VIEW v1 AS SELECT f1('a') FROM t1; + +--connect (con1, localhost, root,,) +--let $ID_1= `SELECT connection_id()` +--send SELECT * FROM v1; + +--connect (con2, localhost, root,,) +--let $ID_2= `SELECT connection_id()` +--send SELECT * FROM v1 + +--connection default +--disable_query_log +--eval KILL QUERY $ID_2 +--eval KILL QUERY $ID_1 +--enable_query_log + +--connection con1 +--error ER_QUERY_INTERRUPTED +--reap +--connection con2 +--error ER_QUERY_INTERRUPTED +--reap + +--connection default +DROP VIEW v1; +DROP TABLE t1; +DROP FUNCTION f1; +--disconnect con1 +--disconnect con2 + + --echo # ------------------------------------------------------------------ --echo # -- End of 5.1 tests --echo # ------------------------------------------------------------------ diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test index faaa42f8461..ffbd56c198d 100644 --- a/mysql-test/t/subselect.test +++ b/mysql-test/t/subselect.test @@ -3610,4 +3610,104 @@ SELECT 1 FROM t1 WHERE a <> SOME ); DROP TABLE t1; +--echo # +--echo # Bug #45989 take 2 : memory leak after explain encounters an +--echo # error in the query +--echo # + +CREATE TABLE t1(a LONGTEXT); +INSERT INTO t1 VALUES (repeat('a',@@global.max_allowed_packet)); +INSERT INTO t1 VALUES (repeat('b',@@global.max_allowed_packet)); + +--error ER_BAD_FIELD_ERROR +EXPLAIN EXTENDED SELECT DISTINCT 1 FROM t1, +(SELECT DISTINCTROW a AS away FROM t1 GROUP BY a WITH ROLLUP) AS d1 +WHERE t1.a = d1.a; + +DROP TABLE t1; + + --echo End of 5.1 tests. + +# +# Bug #47904 Incorrect results w/ table subquery, derived SQs, and LEFT JOIN on index +# + +--echo Set up test tables. +CREATE TABLE t1 ( + t1_id INT UNSIGNED, + + PRIMARY KEY(t1_id) +) Engine=MyISAM; + +INSERT INTO t1 (t1_id) VALUES (1), (2), (3), (4), (5); + +CREATE TABLE t2 SELECT * FROM t1; + +CREATE TABLE t3 ( + t3_id INT UNSIGNED AUTO_INCREMENT, + t1_id INT UNSIGNED, + amount DECIMAL(16,2), + + PRIMARY KEY(t3_id), + KEY(t1_id) +) Engine=MyISAM; + +INSERT INTO t3 (t1_id, t3_id, amount) + VALUES (1, 1, 100.00), (2, 2, 200.00), (4, 4, 400.00); + +--echo This is the 'inner query' running by itself. +--echo Produces correct results. +SELECT + t1.t1_id, + IFNULL((SELECT SUM(amount) FROM t3 WHERE t3.t1_id=t1.t1_id), 0) AS total_amount +FROM + t1 + LEFT JOIN t2 ON t2.t1_id=t1.t1_id +GROUP BY + t1.t1_id +; + +--echo SELECT * FROM (the same inner query) +--echo Produces correct results. +SELECT * FROM ( +SELECT + t1.t1_id, + IFNULL((SELECT SUM(amount) FROM t3 WHERE t3.t1_id=t1.t1_id), 0) AS total_amount +FROM + t1 + LEFT JOIN t2 ON t2.t1_id=t1.t1_id +GROUP BY + t1.t1_id +) AS t; + +--echo Now make t2.t1_id part of a key. +ALTER TABLE t2 ADD PRIMARY KEY(t1_id); + +--echo Same inner query by itself. +--echo Still correct results. +SELECT + t1.t1_id, + IFNULL((SELECT SUM(amount) FROM t3 WHERE t3.t1_id=t1.t1_id), 0) AS total_amount +FROM + t1 + LEFT JOIN t2 ON t2.t1_id=t1.t1_id +GROUP BY + t1.t1_id; + +--echo SELECT * FROM (the same inner query), now with indexes on the LEFT JOIN +SELECT * FROM ( +SELECT + t1.t1_id, + IFNULL((SELECT SUM(amount) FROM t3 WHERE t3.t1_id=t1.t1_id), 0) AS total_amount +FROM + t1 + LEFT JOIN t2 ON t2.t1_id=t1.t1_id +GROUP BY + t1.t1_id +) AS t; + + +DROP TABLE t3; +DROP TABLE t2; +DROP TABLE t1; diff --git a/mysql-test/t/symlink.test b/mysql-test/t/symlink.test index c805dd40fe8..073fcd28246 100644 --- a/mysql-test/t/symlink.test +++ b/mysql-test/t/symlink.test @@ -249,4 +249,26 @@ eval CREATE TABLE t1(a INT) DATA DIRECTORY='$MYSQLTEST_VARDIR/tmp' INDEX DIRECTO DROP TABLE t1; SET @@SQL_MODE=@OLD_SQL_MODE; +--echo # +--echo # BUG#40980 - Drop table can remove another MyISAM table's +--echo # data and index files +--echo # +--mkdir $MYSQL_TMP_DIR/mysql +--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR +eval CREATE TABLE user(a INT) DATA DIRECTORY='$MYSQL_TMP_DIR/mysql' + INDEX DIRECTORY='$MYSQL_TMP_DIR/mysql'; +FLUSH TABLE user; +--echo # Symlinking mysql database to tmpdir +--remove_file $MYSQL_TMP_DIR/mysql/user.MYD +--remove_file $MYSQL_TMP_DIR/mysql/user.MYI +--rmdir $MYSQL_TMP_DIR/mysql +--exec ln -s $MYSQLD_DATADIR/mysql $MYSQL_TMP_DIR/mysql +FLUSH TABLE mysql.user; +DROP TABLE user; +FLUSH TABLE mysql.user; +--disable_result_log +SELECT * FROM mysql.user; +--enable_result_log +--remove_file $MYSQL_TMP_DIR/mysql + --echo End of 5.1 tests diff --git a/mysql-test/t/trigger.test b/mysql-test/t/trigger.test index 57b5431fa1b..c1ff9b58e14 100644 --- a/mysql-test/t/trigger.test +++ b/mysql-test/t/trigger.test @@ -2334,4 +2334,47 @@ SELECT * FROM t2; DROP TABLE t1, t2; +--echo # +--echo # Bug#51650 crash with user variables and triggers +--echo # + +--disable_warnings +DROP TRIGGER IF EXISTS trg1; +DROP TABLE IF EXISTS t1, t2; +--enable_warnings + +CREATE TABLE t1 (b VARCHAR(50) NOT NULL); +CREATE TABLE t2 (a VARCHAR(10) NOT NULL DEFAULT ''); + +delimiter //; +CREATE TRIGGER trg1 AFTER INSERT ON t2 +FOR EACH ROW BEGIN + SELECT 1 FROM t1 c WHERE + (@bug51650 IS NULL OR @bug51650 != c.b) AND c.b = NEW.a LIMIT 1 INTO @foo; +END// +delimiter ;// + +SET @bug51650 = 1; +INSERT IGNORE INTO t2 VALUES(); +INSERT IGNORE INTO t1 SET b = '777'; +INSERT IGNORE INTO t2 SET a = '111'; +SET @bug51650 = 1; +INSERT IGNORE INTO t2 SET a = '777'; + +DROP TRIGGER trg1; +DROP TABLE t1, t2; + +# +# Bug #48525: trigger changes "Column 'id' cannot be null" behaviour +# +CREATE TABLE t1 (id INT NOT NULL); +CREATE TABLE t2 (id INT NOT NULL); +INSERT t1 VALUES (1),(2),(3); +UPDATE t1 SET id=NULL; +CREATE TRIGGER t1_bu BEFORE UPDATE ON t1 FOR EACH ROW + INSERT INTO t2 VALUES (3); +UPDATE t1 SET id=NULL; +DROP TRIGGER t1_bu; +DROP TABLE t1,t2; + --echo End of 5.1 tests. diff --git a/mysql-test/t/type_bit.test b/mysql-test/t/type_bit.test index 530389b3ab9..8bedf9357ef 100644 --- a/mysql-test/t/type_bit.test +++ b/mysql-test/t/type_bit.test @@ -425,4 +425,17 @@ select hex(a) from t1; show create table t1; drop table t1; +--echo # +--echo # Bug#50591 bit(31) causes Duplicate entry '1-NULL' for key 'group_key' +--echo # +CREATE TABLE t1(a INT, b BIT(7) NOT NULL); +INSERT INTO t1 VALUES (NULL, 0),(NULL, 0); +SELECT SUM(a) FROM t1 GROUP BY b, a; +DROP TABLE t1; + +CREATE TABLE t1(a INT, b BIT(7) NOT NULL, c BIT(8) NOT NULL); +INSERT INTO t1 VALUES (NULL, 0, 0),(NULL, 0, 0); +SELECT SUM(a) FROM t1 GROUP BY c, b, a; +DROP TABLE t1; + --echo End of 5.1 tests diff --git a/mysql-test/t/type_date.test b/mysql-test/t/type_date.test index aec60bc2dee..899f912a5a5 100644 --- a/mysql-test/t/type_date.test +++ b/mysql-test/t/type_date.test @@ -246,4 +246,24 @@ insert into t1 values ('0000-01-01'), ('0000-00-01'), ('0001-01-01'); select * from t1 where a between '0000-00-01' and '0000-00-02'; drop table t1; +--echo # +--echo # Bug#50918: Date columns treated differently in Views than in Base +--echo # Tables +--echo # +CREATE TABLE t1 ( the_date DATE, the_time TIME ); +INSERT INTO t1 VALUES ( '2010-01-01', '01:01:01' ); + +SELECT * FROM t1 t11 JOIN t1 t12 ON addtime( t11.the_date, t11.the_time ) = + addtime( t12.the_date, t12.the_time ); + +CREATE VIEW v1 AS SELECT * FROM t1; +SELECT * FROM t1 JOIN v1 ON addtime( t1.the_date, t1.the_time ) = + addtime( v1.the_date, v1.the_time ); + +SELECT * FROM t1 JOIN v1 ON addtime( t1.the_date, t1.the_time ) = + addtime( cast(v1.the_date AS DATETIME), v1.the_time ); + +DROP TABLE t1; +DROP VIEW v1; + --echo End of 5.1 tests diff --git a/mysql-test/t/type_year.test b/mysql-test/t/type_year.test index 16fd39a59d8..d8da4ccc82c 100644 --- a/mysql-test/t/type_year.test +++ b/mysql-test/t/type_year.test @@ -134,5 +134,21 @@ SELECT * FROM t4 WHERE yyyy > 123; DROP TABLE t2, t4; --echo # +--echo # Bug #49910: Behavioural change in SELECT/WHERE on YEAR(4) data type +--echo # + +CREATE TABLE t1 (y YEAR NOT NULL, s VARCHAR(4)); +INSERT INTO t1 (s) VALUES ('bad'); +INSERT INTO t1 (y, s) VALUES (0, 0), (2000, 2000), (2001, 2001); + +SELECT * FROM t1 ta, t1 tb WHERE ta.y = tb.y; +SELECT * FROM t1 WHERE t1.y = 0; +SELECT * FROM t1 WHERE t1.y = 2000; + +SELECT ta.y AS ta_y, ta.s, tb.y AS tb_y, tb.s FROM t1 ta, t1 tb HAVING ta_y = tb_y; + +DROP TABLE t1; + +--echo # --echo End of 5.1 tests diff --git a/mysql-test/t/udf.test b/mysql-test/t/udf.test index 642f77fd741..283bb1a833c 100644 --- a/mysql-test/t/udf.test +++ b/mysql-test/t/udf.test @@ -51,11 +51,14 @@ select lookup("localhost"); --error ER_CANT_INITIALIZE_UDF select reverse_lookup(); -# These two functions should return "localhost", but it's +# These two function calls should return "localhost", but it's # depending on configuration, so just call them and don't log the result --disable_result_log select reverse_lookup("127.0.0.1"); select reverse_lookup(127,0,0,1); + +# This function call may return different results depending on platform, +# so ignore results (see Bug#52060). select reverse_lookup("localhost"); --enable_result_log diff --git a/mysql-test/t/update.test b/mysql-test/t/update.test index 02e8763a630..f6708828a6b 100644 --- a/mysql-test/t/update.test +++ b/mysql-test/t/update.test @@ -467,3 +467,19 @@ UPDATE t2, t1 SET t2.f1 = 2, t1.f1 = 9; ALTER TABLE t2 COMMENT = 'DEF'; DROP TABLE t1, t2; + +--echo # +--echo # Bug#50545: Single table UPDATE IGNORE crashes on join view in +--echo # sql_safe_updates mode. +--echo # +CREATE TABLE t1 ( a INT, KEY( a ) ); +INSERT INTO t1 VALUES (0), (1); +CREATE VIEW v1 AS SELECT t11.a, t12.a AS b FROM t1 t11, t1 t12; +SET SESSION sql_safe_updates = 1; + +--error ER_UPDATE_WITHOUT_KEY_IN_SAFE_MODE +UPDATE IGNORE v1 SET a = 1; + +SET SESSION sql_safe_updates = DEFAULT; +DROP TABLE t1; +DROP VIEW v1; diff --git a/mysql-test/t/variables.test b/mysql-test/t/variables.test index 42bf7e5174d..94dba1e8ff6 100644 --- a/mysql-test/t/variables.test +++ b/mysql-test/t/variables.test @@ -1224,4 +1224,12 @@ SET @old_max_binlog_cache_size = @@GLOBAL.max_binlog_cache_size; SET GLOBAL max_binlog_cache_size = 5 * 1024 * 1024 * 1024; SELECT @@GLOBAL.max_binlog_cache_size; SET GLOBAL max_binlog_cache_size = @old_max_binlog_cache_size; + +--echo # +--echo # Bug #37168 : Missing variable - skip_name_resolve +--echo # + +SELECT @@skip_name_resolve; +SHOW VARIABLES LIKE 'skip_name_resolve'; + --echo End of 5.1 tests diff --git a/mysql-test/t/variables+c.test b/mysql-test/t/variables_community.test index 0092d34133d..0092d34133d 100644 --- a/mysql-test/t/variables+c.test +++ b/mysql-test/t/variables_community.test diff --git a/mysql-test/t/view.test b/mysql-test/t/view.test index 92edfdb4ad5..45e09a79da0 100644 --- a/mysql-test/t/view.test +++ b/mysql-test/t/view.test @@ -3869,6 +3869,52 @@ ALTER TABLE v1; DROP VIEW v1; DROP TABLE t1; +--echo # +--echo # Bug#48449: hang on show create view after upgrading when +--echo # view contains function of view +--echo # + +--disable_warnings +DROP VIEW IF EXISTS v1,v2; +DROP TABLE IF EXISTS t1,t2; +DROP FUNCTION IF EXISTS f1; +--enable_warnings + +CREATE TABLE t1 (a INT); +CREATE TABLE t2 (a INT); + +delimiter //; +CREATE FUNCTION f1() RETURNS INT +BEGIN + SELECT a FROM v2 INTO @a; + RETURN @a; +END// +delimiter ;// + +--echo # Trigger pre-locking when opening v2. +CREATE VIEW v1 AS SELECT f1() FROM t1; + +let $MYSQLD_DATADIR= `SELECT @@datadir`; +copy_file std_data/bug48449.frm $MYSQLD_DATADIR/test/v2.frm; + +SHOW CREATE VIEW v1; + +DROP VIEW v1,v2; +DROP TABLE t1,t2; +DROP FUNCTION f1; + + +# +# Bug#48294 assertion when creating a view based on some row() construct in select query +# +CREATE TABLE t1(f1 INT); +INSERT INTO t1 VALUES (); + +CREATE VIEW v1 AS SELECT 1 FROM t1 WHERE +ROW(1,1) >= ROW(1, (SELECT 1 FROM t1 WHERE f1 >= ANY ( SELECT '1' ))); + +DROP VIEW v1; +DROP TABLE t1; --echo # ----------------------------------------------------------------- --echo # -- End of 5.1 tests. diff --git a/mysql-test/t/view_alias.test b/mysql-test/t/view_alias.test new file mode 100644 index 00000000000..b155ba6c2a9 --- /dev/null +++ b/mysql-test/t/view_alias.test @@ -0,0 +1,92 @@ +--echo # +--echo # Bug#40277 SHOW CREATE VIEW returns invalid SQL +--echo # Bug#41999 SHOW CREATE VIEW returns invalid SQL if subquery is used in SELECT list +--echo # + +--echo # 65 characters exceed the maximum length of a column identifier. The system cannot derive the name from statement. +--echo # Constant with length = 65 . Expect to get the identifier 'Name_exp_1'. +let $after_select= '<--- 65 char including the arrows --->'; +--source include/view_alias.inc +--echo # Subquery with length = 65 . Expect to get the identifier 'Name_exp_1'. +--echo # Attention: Identifier for the column within the subquery will be not generated. +let $after_select= (SELECT '<--- 54 char including the arrows (+ 11 outside) -->'); +--source include/view_alias.inc +--echo # ----------------------------------------------------------------------------------------------------------------- +# +--echo # 64 characters are the maximum length of a column identifier. The system can derive the name from the statement. +let $after_select= '<--- 64 char including the arrows --->'; +--source include/view_alias.inc +let $after_select= (SELECT '<--- 53 char including the arrows (+ 11 outside) --->'); +--source include/view_alias.inc +--echo # ----------------------------------------------------------------------------------------------------------------- +# +--echo # Identifiers must not have trailing spaces. The system cannot derive the name from a constant with trailing space. +--echo # Generated identifiers have at their end the position within the select column list. +--echo # 'c2 ' -> 'Name_exp_1' , ' c4 ' -> 'Name_exp_2' +let $after_select= 'c1', 'c2 ', ' c3', ' c4 '; +--source include/view_alias.inc + +--echo # +--echo # Bug#40277 SHOW CREATE VIEW returns invalid SQL +--echo # + +--disable_warnings +DROP VIEW IF EXISTS v1; +DROP TABLE IF EXISTS t1,t2; +--enable_warnings + +--echo # Column name exceeds the maximum length. +CREATE VIEW v1 AS SELECT '0000000000 1111111111 2222222222 3333333333 4444444444 5555555555'; +let $query = `SELECT VIEW_DEFINITION FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 'v1'`; +DROP VIEW v1; +eval CREATE VIEW v1 AS $query; +DROP VIEW v1; + +--echo # Column names with leading trailing spaces. +CREATE VIEW v1 AS SELECT 'c1', 'c2 ', ' c3', ' c4 '; +let $query = `SELECT VIEW_DEFINITION FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 'v1'`; +DROP VIEW v1; +eval CREATE VIEW v1 AS $query; +DROP VIEW v1; + +--echo # Column name conflicts with a auto-generated one. +CREATE VIEW v1 AS SELECT 'c1', 'c2 ', ' c3', ' c4 ', 'Name_exp_2'; +let $query = `SELECT VIEW_DEFINITION FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 'v1'`; +DROP VIEW v1; +eval CREATE VIEW v1 AS $query; +DROP VIEW v1; + +--echo # Invalid conlumn name in subquery. +CREATE VIEW v1 AS SELECT (SELECT ' c1 '); +let $query = `SELECT VIEW_DEFINITION FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 'v1'`; +DROP VIEW v1; +eval CREATE VIEW v1 AS $query; +DROP VIEW v1; + +CREATE TABLE t1(a INT); +CREATE TABLE t2 LIKE t1; + +--echo # Test alias in subquery +CREATE VIEW v1 AS SELECT a FROM t1 WHERE EXISTS (SELECT 1 FROM t2 AS b WHERE b.a = 0); +let $query = `SELECT VIEW_DEFINITION FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 'v1'`; +DROP VIEW v1; +eval CREATE VIEW v1 AS $query; +DROP VIEW v1; + +--echo # Test column alias in subquery +CREATE VIEW v1 AS SELECT a FROM t1 WHERE EXISTS (SELECT a AS alias FROM t1 GROUP BY alias); +SHOW CREATE VIEW v1; +let $query = `SELECT VIEW_DEFINITION FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 'v1'`; +DROP VIEW v1; +eval CREATE VIEW v1 AS $query; +DROP VIEW v1; + +--echo # Alias as the expression column name. +CREATE VIEW v1 AS SELECT a FROM t1 WHERE EXISTS (SELECT ' a ' AS alias FROM t1 GROUP BY alias); +SHOW CREATE VIEW v1; +let $query = `SELECT VIEW_DEFINITION FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 'v1'`; +DROP VIEW v1; +eval CREATE VIEW v1 AS $query; +DROP VIEW v1; + +DROP TABLE t1, t2; diff --git a/mysql-test/t/view_grant.test b/mysql-test/t/view_grant.test index 175468db702..ba603bde7f8 100644 --- a/mysql-test/t/view_grant.test +++ b/mysql-test/t/view_grant.test @@ -1532,3 +1532,17 @@ SELECT a FROM v2; --disconnect mysqluser1 DROP USER mysqluser1; DROP DATABASE mysqltest1; +USE test; + +--echo # +--echo # Bug#47734: Assertion failed: ! is_set() when locking a view with non-existing definer +--echo # + +--disable_warnings +DROP VIEW IF EXISTS v1; +--enable_warnings + +CREATE DEFINER=`unknown`@`unknown` SQL SECURITY DEFINER VIEW v1 AS SELECT 1; +--error ER_NO_SUCH_USER +LOCK TABLES v1 READ; +DROP VIEW v1; diff --git a/mysql-test/t/xa.test b/mysql-test/t/xa.test index f84d822170f..9db8842f068 100644 --- a/mysql-test/t/xa.test +++ b/mysql-test/t/xa.test @@ -122,6 +122,22 @@ xa start 'a','c'; --connection default drop table t1; +--echo # +--echo # BUG#51342 - more xid crashing +--echo # +CREATE TABLE t1(a INT) ENGINE=InnoDB; +XA START 'x'; +SET SESSION autocommit=0; +INSERT INTO t1 VALUES(1); +--error ER_XAER_RMFAIL +SET SESSION autocommit=1; +SELECT @@autocommit; +INSERT INTO t1 VALUES(1); +XA END 'x'; +XA COMMIT 'x' ONE PHASE; +DROP TABLE t1; +SET SESSION autocommit=1; + --echo End of 5.0 tests # |