diff options
Diffstat (limited to 'mysql-test/t')
28 files changed, 724 insertions, 180 deletions
diff --git a/mysql-test/t/alter_table.test b/mysql-test/t/alter_table.test index 9ae206f7783..bf599818ad6 100644 --- a/mysql-test/t/alter_table.test +++ b/mysql-test/t/alter_table.test @@ -1046,6 +1046,21 @@ ALTER TABLE t1 MODIFY COLUMN a SET('a1','a2','a0','xx','a5','a6','a7','a8','a9', --disable_info DROP TABLE t1; +# +# Bug#43508: Renaming timestamp or date column triggers table copy +# + +CREATE TABLE t1 (f1 TIMESTAMP NULL DEFAULT NULL, + f2 INT(11) DEFAULT NULL) ENGINE=MYISAM DEFAULT CHARSET=utf8; + +INSERT INTO t1 VALUES (NULL, NULL), ("2009-10-09 11:46:19", 2); + +--echo this should affect no rows as there is no real change +--enable_info +ALTER TABLE t1 CHANGE COLUMN f1 f1_no_real_change TIMESTAMP NULL DEFAULT NULL; +--disable_info +DROP TABLE t1; + --echo End of 5.1 tests # diff --git a/mysql-test/t/analyse.test b/mysql-test/t/analyse.test index d8466df14bf..05f739bfd69 100644 --- a/mysql-test/t/analyse.test +++ b/mysql-test/t/analyse.test @@ -10,37 +10,14 @@ insert into t1 values (1,2,"","Y","2002-03-03"), (3,4,"","N","2002-03-04"), (5,6 select count(*) from t1 procedure analyse(); select * from t1 procedure analyse(); select * from t1 procedure analyse(2); +--error ER_WRONG_USAGE create table t2 select * from t1 procedure analyse(); -select * from t2; -drop table t1,t2; +drop table t1; --error ER_WRONG_USAGE EXPLAIN SELECT 1 FROM (SELECT 1) a PROCEDURE ANALYSE(); # -# Test with impossible where -# -create table t1 (a int not null); -create table t2 select * from t1 where 0=1 procedure analyse(); -show create table t2; -select * from t1 where 0=1 procedure analyse(); -insert into t1 values(1); -drop table t2; -create table t2 select * from t1 where 0=1 procedure analyse(); -show create table t2; -select * from t2; -insert into t2 select * from t1 procedure analyse(); -select * from t2; -insert into t1 values(2); -drop table t2; -create table t2 select * from t1 where 0=1 procedure analyse(); -show create table t2; -select * from t2; -insert into t2 select * from t1 procedure analyse(); -select * from t2; -drop table t1,t2; - -# # Bug#2813 - analyse does not quote string values in enums from string # @@ -113,3 +90,46 @@ SELECT * FROM (SELECT * FROM t1) d PROCEDURE ANALYSE(); DROP TABLE t1; --echo End of 4.1 tests + +--echo # +--echo # Bug #48293: crash with procedure analyse, view with > 10 columns, +--echo # having clause... +--echo # + +CREATE TABLE t1(a INT, b INT, c INT, d INT, e INT, + f INT, g INT, h INT, i INT, j INT,k INT); +INSERT INTO t1 VALUES (),(); + +CREATE ALGORITHM=TEMPTABLE VIEW v1 AS SELECT * FROM t1; +--echo #should have a derived table +EXPLAIN SELECT * FROM v1; +--echo #should not crash +--error ER_WRONG_USAGE +SELECT * FROM v1 PROCEDURE analyse(); +--echo #should not crash +--error ER_WRONG_USAGE +SELECT * FROM t1 a, v1, t1 b PROCEDURE analyse(); +--echo #should not crash +--error ER_WRONG_USAGE +SELECT * FROM (SELECT * FROM t1 having a > 1) x PROCEDURE analyse(); +--echo #should not crash +--error ER_WRONG_USAGE +SELECT * FROM t1 a, (SELECT * FROM t1 having a > 1) x, t1 b PROCEDURE analyse(); +--echo #should not crash +--error ER_ORDER_WITH_PROC +SELECT 1 FROM t1 group by a having a > 1 order by 1 PROCEDURE analyse(); + +DROP VIEW v1; +DROP TABLE t1; + +CREATE TABLE t1(a INT); +INSERT INTO t1 VALUES (1),(2); + +--echo # should not crash +--error ER_WRONG_USAGE +CREATE TABLE t2 SELECT 1 FROM t1, t1 t3 GROUP BY t3.a PROCEDURE ANALYSE(); + +DROP TABLE t1; + + +--echo End of 5.0 tests diff --git a/mysql-test/t/ctype_ldml.test b/mysql-test/t/ctype_ldml.test index d0a5e5e4896..0fcfd603aad 100644 --- a/mysql-test/t/ctype_ldml.test +++ b/mysql-test/t/ctype_ldml.test @@ -38,6 +38,14 @@ SELECT * FROM t1 WHERE col1=col2 ORDER BY col1; DROP TABLE t1; --echo # +--echo # Bug#45645 Mysql server close all connection and restart using lower function +--echo # +CREATE TABLE t1 (a VARCHAR(10)) CHARACTER SET utf8 COLLATE utf8_test_ci; +INSERT INTO t1 (a) VALUES ('hello!'); +SELECT * FROM t1 WHERE LOWER(a)=LOWER('N'); +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/explain.test b/mysql-test/t/explain.test index 162a310c0a6..7e2a9bdc811 100644 --- a/mysql-test/t/explain.test +++ b/mysql-test/t/explain.test @@ -167,6 +167,25 @@ flush tables; SELECT OUTR.dt FROM t1 AS OUTR WHERE OUTR.dt IN ( SELECT INNR.dt FROM t2 AS INNR WHERE OUTR.t < '2005-11-13 7:41:31' ); drop tables t1, t2; +--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 # --echo # Bug#30302: Tables that were optimized away are printed in the @@ -179,4 +198,5 @@ insert into t2 values(1),(2); explain extended select * from t1 where f1=1; explain extended select * from t1 join t2 on f1=f2 where f1=1; drop table t1,t2; + --echo End of 5.1 tests. diff --git a/mysql-test/t/flush_read_lock_kill.test b/mysql-test/t/flush_read_lock_kill.test index aeb09d52460..2d359383949 100644 --- a/mysql-test/t/flush_read_lock_kill.test +++ b/mysql-test/t/flush_read_lock_kill.test @@ -57,7 +57,7 @@ connection con1; # debug build running without our --debug=make_global..., will be # error 0 (no error). The only important thing to test is that on # debug builds with our --debug=make_global... we don't hang forever. ---error 0,1053,2013 +--error 0,1317,2013 reap; connection con2; diff --git a/mysql-test/t/gis-rtree.test b/mysql-test/t/gis-rtree.test index 19bbcf19cca..c325b3bd223 100644 --- a/mysql-test/t/gis-rtree.test +++ b/mysql-test/t/gis-rtree.test @@ -881,4 +881,25 @@ SELECT COUNT(*) FROM t1 IGNORE INDEX (b) WHERE DROP TABLE t1; + +--echo # +--echo # Bug #48258: Assertion failed when using a spatial index +--echo # +CREATE TABLE t1(a LINESTRING NOT NULL, SPATIAL KEY(a)); +INSERT INTO t1 VALUES + (GEOMFROMTEXT('LINESTRING(-1 -1, 1 -1, -1 -1, -1 1, 1 1)')), + (GEOMFROMTEXT('LINESTRING(-1 -1, 1 -1, -1 -1, -1 1, 1 1)')); +EXPLAIN SELECT 1 FROM t1 WHERE a = GEOMFROMTEXT('LINESTRING(-1 -1, 1 -1, -1 -1, -1 1, 1 1)'); +SELECT 1 FROM t1 WHERE a = GEOMFROMTEXT('LINESTRING(-1 -1, 1 -1, -1 -1, -1 1, 1 1)'); +EXPLAIN SELECT 1 FROM t1 WHERE a < GEOMFROMTEXT('LINESTRING(-1 -1, 1 -1, -1 -1, -1 1, 1 1)'); +SELECT 1 FROM t1 WHERE a < GEOMFROMTEXT('LINESTRING(-1 -1, 1 -1, -1 -1, -1 1, 1 1)'); +EXPLAIN SELECT 1 FROM t1 WHERE a <= GEOMFROMTEXT('LINESTRING(-1 -1, 1 -1, -1 -1, -1 1, 1 1)'); +SELECT 1 FROM t1 WHERE a <= GEOMFROMTEXT('LINESTRING(-1 -1, 1 -1, -1 -1, -1 1, 1 1)'); +EXPLAIN SELECT 1 FROM t1 WHERE a > GEOMFROMTEXT('LINESTRING(-1 -1, 1 -1, -1 -1, -1 1, 1 1)'); +SELECT 1 FROM t1 WHERE a > GEOMFROMTEXT('LINESTRING(-1 -1, 1 -1, -1 -1, -1 1, 1 1)'); +EXPLAIN SELECT 1 FROM t1 WHERE a >= GEOMFROMTEXT('LINESTRING(-1 -1, 1 -1, -1 -1, -1 1, 1 1)'); +SELECT 1 FROM t1 WHERE a >= GEOMFROMTEXT('LINESTRING(-1 -1, 1 -1, -1 -1, -1 1, 1 1)'); +DROP TABLE t1; + + --echo End of 5.0 tests. diff --git a/mysql-test/t/gis.test b/mysql-test/t/gis.test index 4a60e777cc7..2d10c3bf1e1 100644 --- a/mysql-test/t/gis.test +++ b/mysql-test/t/gis.test @@ -655,6 +655,22 @@ insert into t1 values (),(),(); select min(`col002`) from t1 union select `col002` from t1; drop table t1; +--echo # +--echo # Bug #47780: crash when comparing GIS items from subquery +--echo # + +CREATE TABLE t1(a INT, b MULTIPOLYGON); +INSERT INTO t1 VALUES + (0, + GEOMFROMTEXT( + 'multipolygon(((1 2,3 4,5 6,7 8,9 8),(7 6,5 4,3 2,1 2,3 4)))')); + +--echo # must not crash +SELECT 1 FROM t1 WHERE a <> (SELECT GEOMETRYCOLLECTIONFROMWKB(b) FROM t1); + +DROP TABLE t1; + + --echo End of 5.0 tests diff --git a/mysql-test/t/grant3.test b/mysql-test/t/grant3.test index 9a635048774..437fc9a278f 100644 --- a/mysql-test/t/grant3.test +++ b/mysql-test/t/grant3.test @@ -163,6 +163,41 @@ connection default; DROP USER 'mysqltest1'@'%'; DROP DATABASE mysqltest_1; +--echo # +--echo # Bug#41597 - After rename of user, there are additional grants +--echo # when grants are reapplied. +--echo # + +CREATE DATABASE temp; +CREATE TABLE temp.t1(a INT, b VARCHAR(10)); +INSERT INTO temp.t1 VALUES(1, 'name1'); +INSERT INTO temp.t1 VALUES(2, 'name2'); +INSERT INTO temp.t1 VALUES(3, 'name3'); + + +CREATE USER 'user1'@'%'; +RENAME USER 'user1'@'%' TO 'user2'@'%'; +--echo # Show privileges after rename and BEFORE grant +SHOW GRANTS FOR 'user2'@'%'; +GRANT SELECT (a), INSERT (b) ON `temp`.`t1` TO 'user2'@'%'; +--echo # Show privileges after rename and grant +SHOW GRANTS FOR 'user2'@'%'; + +--echo # Connect as the renamed user +connect (conn1, localhost, user2,,); +connection conn1; +SHOW GRANTS; +SELECT a FROM temp.t1; +--echo # Check for additional privileges by accessing a +--echo # non privileged column. We shouldn't be able to +--echo # access this column. +--error ER_COLUMNACCESS_DENIED_ERROR +SELECT b FROM temp.t1; +disconnect conn1; + +connection default; +DROP USER 'user2'@'%'; +DROP DATABASE temp; --echo End of 5.0 tests diff --git a/mysql-test/t/grant_lowercase_fs.test b/mysql-test/t/grant_lowercase_fs.test new file mode 100644 index 00000000000..f57f950ec8c --- /dev/null +++ b/mysql-test/t/grant_lowercase_fs.test @@ -0,0 +1,30 @@ +-- source include/have_case_insensitive_fs.inc +-- source include/not_embedded.inc + + +# +# Bug#41049 does syntax "grant" case insensitive? +# +create database db1; +GRANT CREATE ON db1.* to user_1@localhost; +GRANT SELECT ON db1.* to USER_1@localhost; + +connect (con1,localhost,user_1,,db1); +CREATE TABLE t1(f1 int); +--error 1142 +SELECT * FROM t1; +connect (con2,localhost,USER_1,,db1); +SELECT * FROM t1; +--error 1142 +CREATE TABLE t2(f1 int); + +connection default; +disconnect con1; +disconnect con2; + +REVOKE ALL PRIVILEGES, GRANT OPTION FROM user_1@localhost; +REVOKE ALL PRIVILEGES, GRANT OPTION FROM USER_1@localhost; +DROP USER user_1@localhost; +DROP USER USER_1@localhost; +DROP DATABASE db1; +use test; diff --git a/mysql-test/t/bug40113-master.opt b/mysql-test/t/innodb_lock_wait_timeout_1-master.opt index 462f8fbe828..462f8fbe828 100644 --- a/mysql-test/t/bug40113-master.opt +++ b/mysql-test/t/innodb_lock_wait_timeout_1-master.opt diff --git a/mysql-test/t/bug40113.test b/mysql-test/t/innodb_lock_wait_timeout_1.test index 6d35d0b73d3..5f33e7c8d49 100644 --- a/mysql-test/t/bug40113.test +++ b/mysql-test/t/innodb_lock_wait_timeout_1.test @@ -43,4 +43,33 @@ DISCONNECT addconroot; DROP TABLE t2, t1; ---echo End of 5.0 tests +--echo # End of 5.0 tests + +--echo # +--echo # Bug#46539 Various crashes on INSERT IGNORE SELECT + SELECT +--echo # FOR UPDATE +--echo # +--disable_warnings +drop table if exists t1; +--enable_warnings +create table t1 (a int primary key auto_increment, + b int, index(b)) engine=innodb; +insert into t1 (b) values (1), (2), (3), (4), (5), (6), (7), (8), (9), (10); +set autocommit=0; +begin; +select * from t1 where b=5 for update; +connect (con1, localhost, root,,); +connection con1; +--error ER_LOCK_WAIT_TIMEOUT +insert ignore into t1 (b) select a as b from t1; +connection default; +--echo # Cleanup +--echo # +disconnect con1; +commit; +set autocommit=default; +drop table t1; + +--echo # +--echo # End of 5.1 tests +--echo # diff --git a/mysql-test/t/join.test b/mysql-test/t/join.test index 1cd05c8cb65..dbf36dedec8 100644 --- a/mysql-test/t/join.test +++ b/mysql-test/t/join.test @@ -730,6 +730,60 @@ SELECT * FROM t1 JOIN t2 ON a=c ORDER BY a; DROP TABLE IF EXISTS t1,t2; + +--echo # +--echo # Bug #42116: Mysql crash on specific query +--echo # +CREATE TABLE t1 (a INT); +CREATE TABLE t2 (a INT); +CREATE TABLE t3 (a INT, INDEX (a)); +CREATE TABLE t4 (a INT); +CREATE TABLE t5 (a INT); +CREATE TABLE t6 (a INT); + +INSERT INTO t1 VALUES (1), (1), (1); + +INSERT INTO t2 VALUES +(2), (2), (2), (2), (2), (2), (2), (2), (2), (2); + +INSERT INTO t3 VALUES +(3), (3), (3), (3), (3), (3), (3), (3), (3), (3); + +EXPLAIN +SELECT * +FROM + t1 JOIN t2 ON t1.a = t2.a + LEFT JOIN + ( + ( + t3 LEFT JOIN t4 ON t3.a = t4.a + ) + LEFT JOIN + ( + t5 LEFT JOIN t6 ON t5.a = t6.a + ) + ON t4.a = t5.a + ) + ON t1.a = t3.a; + +SELECT * +FROM + t1 JOIN t2 ON t1.a = t2.a + LEFT JOIN + ( + ( + t3 LEFT JOIN t4 ON t3.a = t4.a + ) + LEFT JOIN + ( + t5 LEFT JOIN t6 ON t5.a = t6.a + ) + ON t4.a = t5.a + ) + ON t1.a = t3.a; + +DROP TABLE t1,t2,t3,t4,t5,t6; + --echo End of 5.0 tests. diff --git a/mysql-test/t/kill.test b/mysql-test/t/kill.test index 8ef668f542b..02b033df2e5 100644 --- a/mysql-test/t/kill.test +++ b/mysql-test/t/kill.test @@ -97,7 +97,7 @@ select ((@id := kill_id) - kill_id) from t3; kill @id; connection conn1; --- error 1053,2013 +-- error 1317,2013 reap; connection default; diff --git a/mysql-test/t/lowercase_fs_off.test b/mysql-test/t/lowercase_fs_off.test index 878564c32ab..86d1e084c29 100644 --- a/mysql-test/t/lowercase_fs_off.test +++ b/mysql-test/t/lowercase_fs_off.test @@ -29,3 +29,65 @@ disconnect master; connection default; # End of 4.1 tests + +# +# Bug#41049 does syntax "grant" case insensitive? +# +CREATE DATABASE d1; +USE d1; +CREATE TABLE T1(f1 INT); +CREATE TABLE t1(f1 INT); +GRANT SELECT ON T1 to user_1@localhost; + +connect (con1,localhost,user_1,,d1); +--error ER_TABLEACCESS_DENIED_ERROR +select * from t1; +select * from T1; +connection default; +GRANT SELECT ON t1 to user_1@localhost; +connection con1; +select * from information_schema.table_privileges; +connection default; +disconnect con1; + +REVOKE ALL PRIVILEGES, GRANT OPTION FROM user_1@localhost; +DROP USER user_1@localhost; +DROP DATABASE d1; +USE test; + +CREATE DATABASE db1; +USE db1; +CREATE PROCEDURE p1() BEGIN END; +CREATE FUNCTION f1(i INT) RETURNS INT RETURN i+1; + +GRANT USAGE ON db1.* to user_1@localhost; +GRANT EXECUTE ON PROCEDURE db1.P1 to user_1@localhost; +GRANT EXECUTE ON FUNCTION db1.f1 to user_1@localhost; +GRANT UPDATE ON db1.* to USER_1@localhost; + +connect (con1,localhost,user_1,,db1); +call p1(); +call P1(); +select f1(1); +connect (con2,localhost,USER_1,,db1); +--error ER_PROCACCESS_DENIED_ERROR +call p1(); +--error ER_PROCACCESS_DENIED_ERROR +call P1(); +--error ER_PROCACCESS_DENIED_ERROR +select f1(1); + +connection default; +disconnect con1; +disconnect con2; + +REVOKE ALL PRIVILEGES, GRANT OPTION FROM user_1@localhost; +REVOKE ALL PRIVILEGES, GRANT OPTION FROM USER_1@localhost; +DROP FUNCTION f1; +DROP PROCEDURE p1; +DROP USER user_1@localhost; +DROP USER USER_1@localhost; +DROP DATABASE db1; +use test; + +# End of 5.0 tests diff --git a/mysql-test/t/myisam.test b/mysql-test/t/myisam.test index e4ea939f348..56fe103adc9 100644 --- a/mysql-test/t/myisam.test +++ b/mysql-test/t/myisam.test @@ -1539,14 +1539,14 @@ INSERT INTO t1 SELECT a+5120,b FROM t1; SET myisam_sort_buffer_size=4; REPAIR TABLE t1; -# !!! Disabled until additional fix for BUG#47073 is pushed. -#SET myisam_repair_threads=2; +SET myisam_repair_threads=2; # May report different values depending on threads activity. -#--replace_regex /changed from [0-9]+/changed from #/ -#REPAIR TABLE t1; -#SET myisam_repair_threads=@@global.myisam_repair_threads; - +--disable_result_log +REPAIR TABLE t1; +--enable_result_log +SET myisam_repair_threads=@@global.myisam_repair_threads; SET myisam_sort_buffer_size=@@global.myisam_sort_buffer_size; +CHECK TABLE t1; DROP TABLE t1; --echo End of 5.1 tests diff --git a/mysql-test/t/myisam_crash_before_flush_keys.test b/mysql-test/t/myisam_crash_before_flush_keys.test index d6559f7760d..1860ddd27e3 100644 --- a/mysql-test/t/myisam_crash_before_flush_keys.test +++ b/mysql-test/t/myisam_crash_before_flush_keys.test @@ -26,12 +26,6 @@ SET SESSION debug="d,crash_before_flush_keys"; --error 2013 FLUSH TABLE t1; ---echo # Run MYISAMCHK tool to check the table t1 and repair ---replace_result $MYISAMCHK MYISAMCHK $MYSQLD_DATADIR MYSQLD_DATADIR ---error 255 ---exec $MYISAMCHK -cs $MYSQLD_DATADIR/test/t1 2>&1 ---exec $MYISAMCHK -rs $MYSQLD_DATADIR/test/t1 - --echo # Write file to make mysql-test-run.pl start the server --exec echo "restart" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect @@ -42,8 +36,6 @@ FLUSH TABLE t1; --echo # it to be back online again --source include/wait_until_connected_again.inc -SHOW CREATE TABLE t1; - -SELECT * FROM t1 FORCE INDEX (PRIMARY); - +# Must report that the table wasn't closed properly +CHECK TABLE t1; DROP TABLE t1; diff --git a/mysql-test/t/named_pipe.test b/mysql-test/t/named_pipe.test index e3dfd24bb52..e88fd8e1ef8 100644 --- a/mysql-test/t/named_pipe.test +++ b/mysql-test/t/named_pipe.test @@ -9,6 +9,11 @@ if (`SELECT '$nmp' != 'ON'`){ skip No named pipe support; } +# Connect using named pipe for testing +connect(pipe_con,localhost,root,,,,,PIPE); + # Source select test case -- source include/common-tests.inc +connection default; +disconnect pipe_con; diff --git a/mysql-test/t/olap.test b/mysql-test/t/olap.test index d1e40024733..8f672af40a3 100644 --- a/mysql-test/t/olap.test +++ b/mysql-test/t/olap.test @@ -375,4 +375,19 @@ INSERT INTO t1 VALUES(0); SELECT 1 FROM t1 GROUP BY (DATE(NULL)) WITH ROLLUP; DROP TABLE t1; +--echo # +--echo # Bug #48131: crash group by with rollup, distinct, +--echo # filesort, with temporary tables +--echo # + +CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY); +INSERT INTO t1 VALUES (1), (2); +CREATE TABLE t2 (b INT); +INSERT INTO t2 VALUES (100); + +SELECT a, b FROM t1, t2 GROUP BY a, b WITH ROLLUP; +SELECT DISTINCT b FROM t1, t2 GROUP BY a, b WITH ROLLUP; + +DROP TABLE t1, t2; + --echo End of 5.0 tests diff --git a/mysql-test/t/query_cache.test b/mysql-test/t/query_cache.test index 7841e7274dd..0edc9cca385 100644 --- a/mysql-test/t/query_cache.test +++ b/mysql-test/t/query_cache.test @@ -1294,7 +1294,7 @@ SET GLOBAL query_cache_size= default; # Bug #31157: Crash when select+order by the avg of some field within the # group by # - +SET GLOBAL query_cache_size=1024*1024*512; CREATE TABLE t1 (a ENUM('rainbow')); INSERT INTO t1 VALUES (),(),(),(),(); SELECT 1 FROM t1 GROUP BY (SELECT 1 FROM t1 ORDER BY AVG(LAST_INSERT_ID())); @@ -1305,6 +1305,7 @@ INSERT INTO t1 SET a = 'aaaa'; SELECT 1 FROM t1 GROUP BY (SELECT LAST_INSERT_ID() FROM t1 ORDER BY MIN(a) ASC LIMIT 1); DROP TABLE t1; +SET GLOBAL query_cache_size= default; --echo End of 5.1 tests diff --git a/mysql-test/t/range.test b/mysql-test/t/range.test index f0fa99f3d95..3a845471cd0 100644 --- a/mysql-test/t/range.test +++ b/mysql-test/t/range.test @@ -1181,4 +1181,83 @@ INSERT INTO t1 VALUES (1), (NULL); SELECT * FROM t1 WHERE a <> NULL and (a <> NULL or a <= NULL); DROP TABLE t1; +--echo # +--echo # Bug#47925: regression of range optimizer and date comparison in 5.1.39! +--echo # +CREATE TABLE t1 ( a DATE, KEY ( a ) ); +CREATE TABLE t2 ( a DATETIME, KEY ( a ) ); + +--echo # Make optimizer choose range scan +INSERT INTO t1 VALUES ('2009-09-22'), ('2009-09-22'), ('2009-09-22'); +INSERT INTO t1 VALUES ('2009-09-23'), ('2009-09-23'), ('2009-09-23'); + +INSERT INTO t2 VALUES ('2009-09-22 12:00:00'), ('2009-09-22 12:00:00'), + ('2009-09-22 12:00:00'); +INSERT INTO t2 VALUES ('2009-09-23 12:00:00'), ('2009-09-23 12:00:00'), + ('2009-09-23 12:00:00'); + +--echo # DATE vs DATE +--replace_column 1 X 2 X 3 X 7 X 8 X 9 X 10 X +EXPLAIN +SELECT * FROM t1 WHERE a >= '2009/09/23'; +SELECT * FROM t1 WHERE a >= '2009/09/23'; +SELECT * FROM t1 WHERE a >= '20090923'; +SELECT * FROM t1 WHERE a >= 20090923; +SELECT * FROM t1 WHERE a >= '2009-9-23'; +SELECT * FROM t1 WHERE a >= '2009.09.23'; +SELECT * FROM t1 WHERE a >= '2009:09:23'; + +--echo # DATE vs DATETIME +--replace_column 1 X 2 X 3 X 7 X 8 X 9 X 10 X +EXPLAIN +SELECT * FROM t2 WHERE a >= '2009/09/23'; +SELECT * FROM t2 WHERE a >= '2009/09/23'; +SELECT * FROM t2 WHERE a >= '2009/09/23'; +SELECT * FROM t2 WHERE a >= '20090923'; +SELECT * FROM t2 WHERE a >= 20090923; +SELECT * FROM t2 WHERE a >= '2009-9-23'; +SELECT * FROM t2 WHERE a >= '2009.09.23'; +SELECT * FROM t2 WHERE a >= '2009:09:23'; + +--echo # DATETIME vs DATETIME +--replace_column 1 X 2 X 3 X 7 X 8 X 9 X 10 X +EXPLAIN +SELECT * FROM t2 WHERE a >= '2009/09/23 12:00:00'; +SELECT * FROM t2 WHERE a >= '2009/09/23 12:00:00'; +SELECT * FROM t2 WHERE a >= '20090923120000'; +SELECT * FROM t2 WHERE a >= 20090923120000; +SELECT * FROM t2 WHERE a >= '2009-9-23 12:00:00'; +SELECT * FROM t2 WHERE a >= '2009.09.23 12:00:00'; +SELECT * FROM t2 WHERE a >= '2009:09:23 12:00:00'; + +--echo # DATETIME vs DATE +--replace_column 1 X 2 X 3 X 7 X 8 X 9 X 10 X +EXPLAIN +SELECT * FROM t1 WHERE a >= '2009/09/23 00:00:00'; +SELECT * FROM t1 WHERE a >= '2009/09/23 00:00:00'; +SELECT * FROM t1 WHERE a >= '2009/09/23 00:00:00'; +SELECT * FROM t1 WHERE a >= '20090923000000'; +SELECT * FROM t1 WHERE a >= 20090923000000; +SELECT * FROM t1 WHERE a >= '2009-9-23 00:00:00'; +SELECT * FROM t1 WHERE a >= '2009.09.23 00:00:00'; +SELECT * FROM t1 WHERE a >= '2009:09:23 00:00:00'; + +--echo # Test of the new get_date_from_str implementation +--echo # Behavior differs slightly between the trunk and mysql-pe. +--echo # The former may give errors for the truncated values, while the latter +--echo # gives warnings. The purpose of this test is not to interfere, and only +--echo # preserve existing behavior. +SELECT str_to_date('2007-10-00', '%Y-%m-%d') >= '' AND + str_to_date('2007-10-00', '%Y-%m-%d') <= '2007/10/20'; + +SELECT str_to_date('2007-20-00', '%Y-%m-%d') >= '2007/10/20' AND + str_to_date('2007-20-00', '%Y-%m-%d') <= ''; + +SELECT str_to_date('2007-10-00', '%Y-%m-%d') BETWEEN '' AND '2007/10/20'; +SELECT str_to_date('2007-20-00', '%Y-%m-%d') BETWEEN '2007/10/20' AND ''; + +SELECT str_to_date('', '%Y-%m-%d'); + +DROP TABLE t1, t2; + --echo End of 5.1 tests diff --git a/mysql-test/t/select.test b/mysql-test/t/select.test index 7d3785ecccc..51f0cd73374 100644 --- a/mysql-test/t/select.test +++ b/mysql-test/t/select.test @@ -3739,7 +3739,40 @@ EXPLAIN EXTENDED SELECT a, b FROM t1 WHERE a > 1 AND a = b LIMIT 2; EXPLAIN EXTENDED SELECT a, b FROM t1 WHERE a > 1 AND b = a LIMIT 2; DROP TABLE t1; + + +--echo # +--echo # Bug#47019: Assertion failed: 0, file .\rt_mbr.c, line 138 when +--echo # forcing a spatial index +--echo # +CREATE TABLE t1(a LINESTRING NOT NULL, SPATIAL KEY(a)); +INSERT INTO t1 VALUES + (GEOMFROMTEXT('LINESTRING(-1 -1, 1 -1, -1 -1, -1 1, 1 1)')), + (GEOMFROMTEXT('LINESTRING(-1 -1, 1 -1, -1 -1, -1 1, 1 1)')); +EXPLAIN SELECT 1 FROM t1 NATURAL LEFT JOIN t1 AS t2; +SELECT 1 FROM t1 NATURAL LEFT JOIN t1 AS t2; +EXPLAIN SELECT 1 FROM t1 NATURAL LEFT JOIN t1 AS t2 FORCE INDEX(a); +SELECT 1 FROM t1 NATURAL LEFT JOIN t1 AS t2 FORCE INDEX(a); +DROP TABLE t1; + + +--echo # +--echo # Bug #48291 : crash with row() operator,select into @var, and +--echo # subquery returning multiple rows +--echo # + +CREATE TABLE t1(a INT); +INSERT INTO t1 VALUES (2),(3); + +--echo # Should not crash +--error ER_SUBQUERY_NO_1_ROW +SELECT 1 FROM t1 WHERE a <> 1 AND NOT +ROW(a,a) <=> ROW((SELECT 1 FROM t1 WHERE 1=2),(SELECT 1 FROM t1)) +INTO @var0; + +DROP TABLE t1; + --echo End of 5.0 tests # diff --git a/mysql-test/t/sp-bugs.test b/mysql-test/t/sp-bugs.test new file mode 100644 index 00000000000..7b94e65a5e9 --- /dev/null +++ b/mysql-test/t/sp-bugs.test @@ -0,0 +1,61 @@ +# Test file for stored procedure bugfixes + +--echo # +--echo # Bug #47412: Valgrind warnings / user can read uninitalized memory +--echo # using SP variables +--echo # + +CREATE SCHEMA testdb; +USE testdb; +DELIMITER |; +CREATE FUNCTION f2 () RETURNS INTEGER +BEGIN + DECLARE CONTINUE HANDLER FOR SQLSTATE '42000' SET @aux = 1; + RETURN f_not_exists () ; +END| +CREATE PROCEDURE p3 ( arg1 VARCHAR(32) ) +BEGIN + CALL p_not_exists ( ); +END| +DELIMITER ;| +--echo # should not return valgrind warnings +--error ER_SP_DOES_NOT_EXIST +CALL p3 ( f2 () ); + +DROP SCHEMA testdb; + +CREATE SCHEMA testdb; +USE testdb; +DELIMITER |; +CREATE FUNCTION f2 () RETURNS INTEGER +BEGIN + DECLARE CONTINUE HANDLER FOR SQLSTATE '42000' SET @aux = 1; + RETURN f_not_exists () ; +END| +CREATE PROCEDURE p3 ( arg2 INTEGER ) +BEGIN + CALL p_not_exists ( ); +END| +DELIMITER ;| +--echo # should not return valgrind warnings +--error ER_SP_DOES_NOT_EXIST +CALL p3 ( f2 () ); + +DROP SCHEMA testdb; + +CREATE SCHEMA testdb; +USE testdb; +DELIMITER |; +CREATE FUNCTION f2 () RETURNS INTEGER +BEGIN + DECLARE CONTINUE HANDLER FOR SQLSTATE '42000' SET @aux = 1; + RETURN f_not_exists () ; +END| +DELIMITER ;| +--echo # should not return valgrind warnings +SELECT f2 (); + +DROP SCHEMA testdb; + + +--echo End of 5.1 tests diff --git a/mysql-test/t/sp-error.test b/mysql-test/t/sp-error.test index 66b960c938f..18a4a117939 100644 --- a/mysql-test/t/sp-error.test +++ b/mysql-test/t/sp-error.test @@ -2448,3 +2448,27 @@ SELECT AVG (a) FROM t1 WHERE b = 999999; --error ER_SP_DOES_NOT_EXIST SELECT non_existent (a) FROM t1 WHERE b = 999999; DROP TABLE t1; + +--echo # +--echo # Bug #47788: Crash in TABLE_LIST::hide_view_error on UPDATE + VIEW + +--echo # SP + MERGE + ALTER +--echo # + +CREATE TABLE t1 (pk INT, b INT, KEY (b)); +CREATE ALGORITHM = TEMPTABLE VIEW v1 AS SELECT * FROM t1; + +CREATE PROCEDURE p1 (a int) UPDATE IGNORE v1 SET b = a; + +--error ER_NON_UPDATABLE_TABLE +CALL p1(5); + +ALTER TABLE t1 CHANGE COLUMN b b2 INT; + +--error ER_VIEW_INVALID +CALL p1(7); + +DROP PROCEDURE p1; +DROP VIEW v1; +DROP TABLE t1; + +--echo End of 5.1 tests diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test index 544017ebb97..7f2dba00ba8 100644 --- a/mysql-test/t/subselect.test +++ b/mysql-test/t/subselect.test @@ -30,7 +30,7 @@ SELECT 1 IN (SELECT 1); SELECT 1 FROM (SELECT 1 as a) b WHERE 1 IN (SELECT (SELECT a)); -- error ER_WRONG_USAGE select (SELECT 1 FROM (SELECT 1) a PROCEDURE ANALYSE(1)); --- error ER_WRONG_USAGE +-- error ER_WRONG_PARAMETERS_TO_PROCEDURE SELECT 1 FROM (SELECT 1) a PROCEDURE ANALYSE((SELECT 1)); -- error ER_BAD_FIELD_ERROR SELECT (SELECT 1) as a FROM (SELECT 1) b WHERE (SELECT a) IS NULL; @@ -3544,4 +3544,19 @@ where v in(select v where t1.g=t2.g) is unknown; drop table t1, t2; +# +# Bug #31157: Crash when select+order by the avg of some field within the +# group by +# +CREATE TABLE t1 (a ENUM('rainbow')); +INSERT INTO t1 VALUES (),(),(),(),(); +SELECT 1 FROM t1 GROUP BY (SELECT 1 FROM t1 ORDER BY AVG(LAST_INSERT_ID())); +DROP TABLE t1; +CREATE TABLE t1 (a LONGBLOB); +INSERT INTO t1 SET a = 'aaaa'; +INSERT INTO t1 SET a = 'aaaa'; +SELECT 1 FROM t1 GROUP BY + (SELECT LAST_INSERT_ID() FROM t1 ORDER BY MIN(a) ASC LIMIT 1); +DROP TABLE t1; + --echo End of 5.1 tests. diff --git a/mysql-test/t/subselect3.test b/mysql-test/t/subselect3.test index 7a2a9f328ef..fab0a462157 100644 --- a/mysql-test/t/subselect3.test +++ b/mysql-test/t/subselect3.test @@ -728,3 +728,69 @@ where from t4, t5 limit 2)); drop table t0, t1, t2, t3, t4, t5; + +--echo # +--echo # BUG#48177 - SELECTs with NOT IN subqueries containing NULL +--echo # values return too many records +--echo # + +CREATE TABLE t1 ( + i1 int DEFAULT NULL, + i2 int DEFAULT NULL +) ; + +INSERT INTO t1 VALUES (1, NULL); +INSERT INTO t1 VALUES (2, 3); +INSERT INTO t1 VALUES (4, NULL); +INSERT INTO t1 VALUES (4, 0); +INSERT INTO t1 VALUES (NULL, NULL); + +CREATE TABLE t2 ( + i1 int DEFAULT NULL, + i2 int DEFAULT NULL +) ; + +INSERT INTO t2 VALUES (4, NULL); +INSERT INTO t2 VALUES (5, 0); + +--echo +--echo Data in t1 +SELECT i1, i2 FROM t1; + +--echo +--echo Data in subquery (should be filtered out) +SELECT i1, i2 FROM t2 ORDER BY i1; + +FLUSH STATUS; + +--echo +SELECT i1, i2 +FROM t1 +WHERE (i1, i2) + NOT IN (SELECT i1, i2 FROM t2); + +--echo +--echo # Check that the subquery only has to be evaluated once +--echo # for all-NULL values even though there are two (NULL,NULL) records +--echo # Baseline: +SHOW STATUS LIKE '%Handler_read_rnd_next'; + +--echo +INSERT INTO t1 VALUES (NULL, NULL); +FLUSH STATUS; + +--echo +SELECT i1, i2 +FROM t1 +WHERE (i1, i2) + NOT IN (SELECT i1, i2 FROM t2); + +--echo +--echo # Handler_read_rnd_next should be one more than baseline +--echo # (read record from t1, but do not read from t2) +SHOW STATUS LIKE '%Handler_read_rnd_next'; + + +DROP TABLE t1,t2; + +--echo End of 5.1 tests diff --git a/mysql-test/t/type_newdecimal.test b/mysql-test/t/type_newdecimal.test index 65bafaae77e..cd3c3f81510 100644 --- a/mysql-test/t/type_newdecimal.test +++ b/mysql-test/t/type_newdecimal.test @@ -1286,137 +1286,3 @@ CREATE TABLE t1 SELECT 1 % .1234567891234567891234567891234567891234567891234567 DESCRIBE t1; SELECT my_col FROM t1; DROP TABLE t1; - ---echo # ---echo # Bug#45261: Crash, stored procedure + decimal ---echo # - ---disable_warnings -DROP TABLE IF EXISTS t1; ---enable_warnings - -CREATE TABLE t1 SELECT - /* 81 */ 100000000000000000000000000000000000000000000000000000000000000000000000000000001 - AS c1; -DESC t1; -SELECT * FROM t1; -DROP TABLE t1; - -CREATE TABLE t1 SELECT - /* 81 */ 100000000000000000000000000000000000000000000000000000000000000000000000000000001. - AS c1; -DESC t1; -SELECT * FROM t1; -DROP TABLE t1; - -CREATE TABLE t1 SELECT - /* 81 */ 100000000000000000000000000000000000000000000000000000000000000000000000000000001.1 /* 1 */ - AS c1; -DESC t1; -SELECT * FROM t1; -DROP TABLE t1; - -CREATE TABLE t1 SELECT - /* 82 */ 1000000000000000000000000000000000000000000000000000000000000000000000000000000001 - AS c1; -DESC t1; -SELECT * FROM t1; -DROP TABLE t1; - -CREATE TABLE t1 SELECT - /* 40 */ 1000000000000000000000000000000000000001.1000000000000000000000000000000000000001 /* 40 */ - AS c1; -DESC t1; -SELECT * FROM t1; -DROP TABLE t1; - -CREATE TABLE t1 SELECT - /* 1 */ 1.10000000000000000000000000000000000000000000000000000000000000000000000000000001 /* 80 */ - AS c1; -DESC t1; -SELECT * FROM t1; -DROP TABLE t1; - -CREATE TABLE t1 SELECT - /* 1 */ 1.100000000000000000000000000000000000000000000000000000000000000000000000000000001 /* 81 */ - AS c1; -DESC t1; -SELECT * FROM t1; -DROP TABLE t1; - -CREATE TABLE t1 SELECT - .100000000000000000000000000000000000000000000000000000000000000000000000000000001 /* 81 */ - AS c1; -DESC t1; -SELECT * FROM t1; -DROP TABLE t1; - -CREATE TABLE t1 SELECT - /* 45 */ 123456789012345678901234567890123456789012345.123456789012345678901234567890123456789012345 /* 45 */ - AS c1; -DESC t1; -SELECT * FROM t1; -DROP TABLE t1; - -CREATE TABLE t1 SELECT - /* 65 */ 12345678901234567890123456789012345678901234567890123456789012345.1 /* 1 */ - AS c1; -DESC t1; -SELECT * FROM t1; -DROP TABLE t1; - -CREATE TABLE t1 SELECT - /* 66 */ 123456789012345678901234567890123456789012345678901234567890123456.1 /* 1 */ - AS c1; -DESC t1; -SELECT * FROM t1; -DROP TABLE t1; - -CREATE TABLE t1 SELECT - .123456789012345678901234567890123456789012345678901234567890123456 /* 66 */ - AS c1; -DESC t1; -SELECT * FROM t1; -DROP TABLE t1; - -CREATE TABLE t1 AS SELECT 123.1234567890123456789012345678901 /* 31 */ AS c1; -DESC t1; -SELECT * FROM t1; -DROP TABLE t1; - -CREATE TABLE t1 SELECT 1.1 + CAST(1 AS DECIMAL(65,30)) AS c1; -DESC t1; -SELECT * FROM t1; -DROP TABLE t1; - ---echo # ---echo # Test that the integer and decimal parts are properly calculated. ---echo # - -CREATE TABLE t1 (a DECIMAL(30,30)); -INSERT INTO t1 VALUES (0.1),(0.2),(0.3); -CREATE TABLE t2 SELECT MIN(a + 0.0000000000000000000000000000001) AS c1 FROM t1; -DESC t2; -DROP TABLE t1,t2; - -CREATE TABLE t1 (a DECIMAL(30,30)); -INSERT INTO t1 VALUES (0.1),(0.2),(0.3); -CREATE TABLE t2 SELECT IFNULL(a + 0.0000000000000000000000000000001, NULL) AS c1 FROM t1; -DESC t2; -DROP TABLE t1,t2; - -CREATE TABLE t1 (a DECIMAL(30,30)); -INSERT INTO t1 VALUES (0.1),(0.2),(0.3); -CREATE TABLE t2 SELECT CASE a WHEN 0.1 THEN 0.0000000000000000000000000000000000000000000000000000000000000000001 END AS c1 FROM t1; -DESC t2; -DROP TABLE t1,t2; - ---echo # ---echo # Test that variables get maximum precision. ---echo # - -SET @decimal= 1.1; -CREATE TABLE t1 SELECT @decimal AS c1; -DESC t1; -SELECT * FROM t1; -DROP TABLE t1; diff --git a/mysql-test/t/update.test b/mysql-test/t/update.test index 7d56df259ba..02e8763a630 100644 --- a/mysql-test/t/update.test +++ b/mysql-test/t/update.test @@ -452,3 +452,18 @@ DROP TABLE t1; DROP FUNCTION f1; --echo End of 5.0 tests + +--echo # +--echo # Bug #47919 assert in open_table during ALTER temporary table +--echo # + +CREATE TABLE t1 (f1 INTEGER AUTO_INCREMENT, PRIMARY KEY (f1)); +CREATE TEMPORARY TABLE t2 LIKE t1; +INSERT INTO t1 VALUES (1); +INSERT INTO t2 VALUES (1); + +ALTER TABLE t2 COMMENT = 'ABC'; +UPDATE t2, t1 SET t2.f1 = 2, t1.f1 = 9; +ALTER TABLE t2 COMMENT = 'DEF'; + +DROP TABLE t1, t2; diff --git a/mysql-test/t/xa.test b/mysql-test/t/xa.test index 7b1c6a268d5..f84d822170f 100644 --- a/mysql-test/t/xa.test +++ b/mysql-test/t/xa.test @@ -149,6 +149,68 @@ xa end 'a'; xa prepare 'a'; xa commit 'a'; +# +# BUG#43171 - Assertion failed: thd->transaction.xid_state.xid.is_null() +# +CREATE TABLE t1(a INT, KEY(a)) ENGINE=InnoDB; +INSERT INTO t1 VALUES(1),(2); +connect(con1,localhost,root,,); + +# Part 1: Prepare to test XA START after regular transaction deadlock +BEGIN; +UPDATE t1 SET a=3 WHERE a=1; + +connection default; +BEGIN; +UPDATE t1 SET a=4 WHERE a=2; + +connection con1; +let $conn_id= `SELECT CONNECTION_ID()`; +SEND UPDATE t1 SET a=5 WHERE a=2; + +connection default; +let $wait_timeout= 2; +let $wait_condition= SELECT 1 FROM INFORMATION_SCHEMA.PROCESSLIST +WHERE ID=$conn_id AND STATE='Searching rows for update'; +--source include/wait_condition.inc + +--error ER_LOCK_DEADLOCK +UPDATE t1 SET a=5 WHERE a=1; +ROLLBACK; + +# Part 2: Prepare to test XA START after XA transaction deadlock +connection con1; +REAP; +ROLLBACK; +BEGIN; +UPDATE t1 SET a=3 WHERE a=1; + +connection default; +XA START 'xid1'; +UPDATE t1 SET a=4 WHERE a=2; + +connection con1; +SEND UPDATE t1 SET a=5 WHERE a=2; + +connection default; +let $wait_timeout= 2; +let $wait_condition= SELECT 1 FROM INFORMATION_SCHEMA.PROCESSLIST +WHERE ID=$conn_id AND STATE='Searching rows for update'; +--source include/wait_condition.inc + +--error ER_LOCK_DEADLOCK +UPDATE t1 SET a=5 WHERE a=1; +--error ER_XA_RBDEADLOCK +XA END 'xid1'; +XA ROLLBACK 'xid1'; + +XA START 'xid1'; +XA END 'xid1'; +XA ROLLBACK 'xid1'; + +disconnect con1; +DROP TABLE t1; + # Wait till all disconnects are completed --source include/wait_until_count_sessions.inc |