diff options
Diffstat (limited to 'mysql-test/t')
39 files changed, 1164 insertions, 231 deletions
diff --git a/mysql-test/t/alter_table.test b/mysql-test/t/alter_table.test index 17549745203..5534aa0a234 100644 --- a/mysql-test/t/alter_table.test +++ b/mysql-test/t/alter_table.test @@ -1046,4 +1046,19 @@ 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/bug40113.test b/mysql-test/t/bug40113.test deleted file mode 100644 index 6d35d0b73d3..00000000000 --- a/mysql-test/t/bug40113.test +++ /dev/null @@ -1,46 +0,0 @@ ---source include/have_innodb.inc - ---echo # ---echo # Bug #40113: Embedded SELECT inside UPDATE or DELETE can timeout ---echo # without error ---echo # - -CREATE TABLE t1 (a int, b int, PRIMARY KEY (a,b)) ENGINE=InnoDB; - -INSERT INTO t1 (a,b) VALUES (1070109,99); - -CREATE TABLE t2 (b int, a int, PRIMARY KEY (b)) ENGINE=InnoDB; - -INSERT INTO t2 (b,a) VALUES (7,1070109); - -SELECT * FROM t1; - -BEGIN; - -SELECT b FROM t2 WHERE b=7 FOR UPDATE; - -CONNECT (addconroot, localhost, root,,); -CONNECTION addconroot; - -BEGIN; - ---error ER_LOCK_WAIT_TIMEOUT -SELECT b FROM t2 WHERE b=7 FOR UPDATE; - ---error ER_LOCK_WAIT_TIMEOUT -INSERT INTO t1 (a) VALUES ((SELECT a FROM t2 WHERE b=7)); - ---error ER_LOCK_WAIT_TIMEOUT -UPDATE t1 SET a='7000000' WHERE a=(SELECT a FROM t2 WHERE b=7); - ---error ER_LOCK_WAIT_TIMEOUT -DELETE FROM t1 WHERE a=(SELECT a FROM t2 WHERE b=7); - -SELECT * FROM t1; - -CONNECTION default; -DISCONNECT addconroot; - -DROP TABLE t2, t1; - ---echo End of 5.0 tests diff --git a/mysql-test/t/ctype_ldml.test b/mysql-test/t/ctype_ldml.test index bc04b93a935..0395de273de 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/disabled.def b/mysql-test/t/disabled.def index 6f0b1716d38..ad7617b9403 100644 --- a/mysql-test/t/disabled.def +++ b/mysql-test/t/disabled.def @@ -10,7 +10,8 @@ # ############################################################################## kill : Bug#37780 2008-12-03 HHunger need some changes to be robust enough for pushbuild. -innodb_bug39438 : Bug#42383 2009-01-28 lsoares "This fails in embedded and on windows. Note that this test is not run on windows and on embedded in PB for main trees currently" query_cache_28249 : Bug#43861 2009-03-25 main.query_cache_28249 fails sporadically partition_innodb_builtin : Bug#32430 2009-09-25 mattiasj Waiting for push of Innodb changes partition_innodb_plugin : Bug#32430 2009-09-25 mattiasj Waiting for push of Innodb changes +innodb-autoinc : Bug#48482 2009-11-02 svoj innodb-autoinc.test fails with results difference +rpl_killed_ddl : Bug#45520: rpl_killed_ddl fails sporadically in pb2 diff --git a/mysql-test/t/explain.test b/mysql-test/t/explain.test index 18f1145a25d..77b49a8b1a5 100644 --- a/mysql-test/t/explain.test +++ b/mysql-test/t/explain.test @@ -167,4 +167,24 @@ 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 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/innodb-autoinc.test b/mysql-test/t/innodb-autoinc.test index 61c42f45733..3f45bb9d003 100644 --- a/mysql-test/t/innodb-autoinc.test +++ b/mysql-test/t/innodb-autoinc.test @@ -482,6 +482,7 @@ DROP TABLE t2; # 44030: Error: (1500) Couldn't read the MAX(ID) autoinc value from # the index (PRIMARY) # This test requires a restart of the server +SET @@SESSION.AUTO_INCREMENT_INCREMENT=1, @@SESSION.AUTO_INCREMENT_OFFSET=1; CREATE TABLE t1 (c1 INT PRIMARY KEY AUTO_INCREMENT) ENGINE=InnoDB; INSERT INTO t1 VALUES (null); INSERT INTO t1 VALUES (null); @@ -492,9 +493,130 @@ SELECT * FROM t1; # The MySQL and InnoDB data dictionaries should now be out of sync. # The select should print message to the error log SELECT * FROM t1; +# MySQL have made a change (http://lists.mysql.com/commits/75268) that no +# longer results in the two data dictionaries being out of sync. If they +# revert their changes then this check for ER_AUTOINC_READ_FAILED will need +# to be enabled. -- error ER_AUTOINC_READ_FAILED,1467 INSERT INTO t1 VALUES(null); ALTER TABLE t1 AUTO_INCREMENT = 3; +SHOW CREATE TABLE t1; INSERT INTO t1 VALUES(null); SELECT * FROM t1; DROP TABLE t1; + +# If the user has specified negative values for an AUTOINC column then +# InnoDB should ignore those values when setting the table's max value. +SET @@SESSION.AUTO_INCREMENT_INCREMENT=1, @@SESSION.AUTO_INCREMENT_OFFSET=1; +SHOW VARIABLES LIKE "%auto_inc%"; +# TINYINT +CREATE TABLE t1 (c1 TINYINT PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB; +INSERT INTO t1 VALUES (1, NULL); +INSERT INTO t1 VALUES (-1, 'innodb'); +INSERT INTO t1 VALUES (-127, 'innodb'); +INSERT INTO t1 VALUES (NULL, NULL); +SHOW CREATE TABLE t1; +SELECT * FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 (c1 TINYINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB; +INSERT INTO t1 VALUES (1, NULL); +INSERT INTO t1 VALUES (-1, 'innodb'); +INSERT INTO t1 VALUES (-127, 'innodb'); +INSERT INTO t1 VALUES (NULL, NULL); +SHOW CREATE TABLE t1; +SELECT * FROM t1; +DROP TABLE t1; +# +# SMALLINT +# +CREATE TABLE t1 (c1 SMALLINT PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB; +INSERT INTO t1 VALUES (1, NULL); +INSERT INTO t1 VALUES (-1, 'innodb'); +INSERT INTO t1 VALUES (-32767, 'innodb'); +INSERT INTO t1 VALUES (NULL, NULL); +SHOW CREATE TABLE t1; +SELECT * FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 (c1 SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB; +INSERT INTO t1 VALUES (1, NULL); +INSERT INTO t1 VALUES (-1, 'innodb'); +INSERT INTO t1 VALUES (-32757, 'innodb'); +INSERT INTO t1 VALUES (NULL, NULL); +SHOW CREATE TABLE t1; +SELECT * FROM t1; +DROP TABLE t1; +# +# MEDIUMINT +# +CREATE TABLE t1 (c1 MEDIUMINT PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB; +INSERT INTO t1 VALUES (1, NULL); +INSERT INTO t1 VALUES (-1, 'innodb'); +INSERT INTO t1 VALUES (-8388607, 'innodb'); +INSERT INTO t1 VALUES (NULL, NULL); +SHOW CREATE TABLE t1; +SELECT * FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 (c1 MEDIUMINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB; +INSERT INTO t1 VALUES (1, NULL); +INSERT INTO t1 VALUES (-1, 'innodb'); +INSERT INTO t1 VALUES (-8388607, 'innodb'); +INSERT INTO t1 VALUES (NULL, NULL); +SHOW CREATE TABLE t1; +SELECT * FROM t1; +DROP TABLE t1; +# +# INT +# +CREATE TABLE t1 (c1 INT PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB; +INSERT INTO t1 VALUES (1, NULL); +INSERT INTO t1 VALUES (-1, 'innodb'); +INSERT INTO t1 VALUES (-2147483647, 'innodb'); +INSERT INTO t1 VALUES (NULL, NULL); +SHOW CREATE TABLE t1; +SELECT * FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 (c1 INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB; +INSERT INTO t1 VALUES (1, NULL); +INSERT INTO t1 VALUES (-1, 'innodb'); +INSERT INTO t1 VALUES (-2147483647, 'innodb'); +INSERT INTO t1 VALUES (NULL, NULL); +SHOW CREATE TABLE t1; +SELECT * FROM t1; +DROP TABLE t1; +# +# BIGINT +# +CREATE TABLE t1 (c1 BIGINT PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB; +INSERT INTO t1 VALUES (1, NULL); +INSERT INTO t1 VALUES (-1, 'innodb'); +INSERT INTO t1 VALUES (-9223372036854775807, 'innodb'); +INSERT INTO t1 VALUES (NULL, NULL); +SHOW CREATE TABLE t1; +SELECT * FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 (c1 BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB; +INSERT INTO t1 VALUES (1, NULL); +INSERT INTO t1 VALUES (-1, 'innodb'); +INSERT INTO t1 VALUES (-9223372036854775807, 'innodb'); +INSERT INTO t1 VALUES (NULL, NULL); +SHOW CREATE TABLE t1; +SELECT * FROM t1; +DROP TABLE t1; +# +# End negative number check + +## +# 47125: auto_increment start value is ignored if an index is created +# and engine=innodb +# +CREATE TABLE T1 (c1 INT AUTO_INCREMENT, c2 INT, PRIMARY KEY(c1)) AUTO_INCREMENT=10 ENGINE=InnoDB; +CREATE INDEX i1 on T1(c2); +SHOW CREATE TABLE T1; +INSERT INTO T1 (c2) values (0); +SELECT * FROM T1; +DROP TABLE T1; diff --git a/mysql-test/t/innodb_bug34300.test b/mysql-test/t/innodb_bug34300.test index ecec381da14..432ddd03547 100644 --- a/mysql-test/t/innodb_bug34300.test +++ b/mysql-test/t/innodb_bug34300.test @@ -9,7 +9,6 @@ -- disable_result_log # set packet size and reconnect -let $max_packet=`select @@global.max_allowed_packet`; SET @@global.max_allowed_packet=16777216; --connect (newconn, localhost, root,,) diff --git a/mysql-test/t/innodb_bug39438.test b/mysql-test/t/innodb_bug39438.test index 4dc3d957c39..2a51e5fcbb8 100644 --- a/mysql-test/t/innodb_bug39438.test +++ b/mysql-test/t/innodb_bug39438.test @@ -9,6 +9,10 @@ -- source include/have_innodb.inc +--disable_query_log +call mtr.add_suppression("InnoDB: Error: table 'test/bug39438'"); +--enable_query_log + SET storage_engine=InnoDB; # we care only that the following SQL commands do not crash the server diff --git a/mysql-test/t/innodb_bug44369.test b/mysql-test/t/innodb_bug44369.test index 495059eb5e6..238dc3d8fb1 100644 --- a/mysql-test/t/innodb_bug44369.test +++ b/mysql-test/t/innodb_bug44369.test @@ -13,9 +13,9 @@ create table bug44369 (DB_ROW_ID int) engine=innodb; --error ER_CANT_CREATE_TABLE create table bug44369 (db_row_id int) engine=innodb; -show errors; +show warnings; --error ER_CANT_CREATE_TABLE create table bug44369 (db_TRX_Id int) engine=innodb; -show errors; +show warnings; diff --git a/mysql-test/t/innodb_bug46000.test b/mysql-test/t/innodb_bug46000.test index 80c18c58ef0..eb10d866e54 100644 --- a/mysql-test/t/innodb_bug46000.test +++ b/mysql-test/t/innodb_bug46000.test @@ -14,7 +14,7 @@ create table bug46000(`id` int,key `GEN_CLUST_INDEX`(`id`))engine=innodb; --error ER_CANT_CREATE_TABLE create table bug46000(`id` int, key `GEN_clust_INDEX`(`id`))engine=innodb; -show errors; +show warnings; create table bug46000(id int) engine=innodb; @@ -24,7 +24,7 @@ create table bug46000(id int) engine=innodb; create index GEN_CLUST_INDEX on bug46000(id); --replace_regex /'[^']*test.#sql-[0-9a-f_]*'/'#sql-temporary'/ -show errors; +show warnings; # This 'create index' operation should succeed, no # temp table left from last failed create index diff --git a/mysql-test/t/innodb_bug47777.test b/mysql-test/t/innodb_bug47777.test new file mode 100644 index 00000000000..8f2985b2cf0 --- /dev/null +++ b/mysql-test/t/innodb_bug47777.test @@ -0,0 +1,24 @@ +# This is the test for bug 47777. GEOMETRY +# data is treated as BLOB data in innodb. +# Consequently, its key value generation/storing +# should follow the process for the BLOB +# datatype as well. + +--source include/have_innodb.inc + +create table bug47777(c2 linestring not null, primary key (c2(1))) engine=innodb; + +insert into bug47777 values (geomfromtext('linestring(1 2,3 4,5 6,7 8,9 10)')); + +# Verify correct row get inserted. +select count(*) from bug47777 where c2 =geomfromtext('linestring(1 2,3 4,5 6,7 8,9 10)'); + +# Update table bug47777 should be successful. +update bug47777 set c2=GeomFromText('POINT(1 1)'); + +# Verify the row get updated successfully. The original +# c2 value should be changed to GeomFromText('POINT(1 1)'). +select count(*) from bug47777 where c2 =geomfromtext('linestring(1 2,3 4,5 6,7 8,9 10)'); +select count(*) from bug47777 where c2 = GeomFromText('POINT(1 1)'); + +drop table bug47777; 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/innodb_lock_wait_timeout_1.test b/mysql-test/t/innodb_lock_wait_timeout_1.test new file mode 100644 index 00000000000..e42e9f3e37c --- /dev/null +++ b/mysql-test/t/innodb_lock_wait_timeout_1.test @@ -0,0 +1,230 @@ +--source include/have_innodb.inc + +--echo # +--echo # Bug #40113: Embedded SELECT inside UPDATE or DELETE can timeout +--echo # without error +--echo # + +CREATE TABLE t1 (a int, b int, PRIMARY KEY (a,b)) ENGINE=InnoDB; + +INSERT INTO t1 (a,b) VALUES (1070109,99); + +CREATE TABLE t2 (b int, a int, PRIMARY KEY (b)) ENGINE=InnoDB; + +INSERT INTO t2 (b,a) VALUES (7,1070109); + +SELECT * FROM t1; + +BEGIN; + +SELECT b FROM t2 WHERE b=7 FOR UPDATE; + +CONNECT (addconroot, localhost, root,,); +CONNECTION addconroot; + +BEGIN; + +--error ER_LOCK_WAIT_TIMEOUT +SELECT b FROM t2 WHERE b=7 FOR UPDATE; + +--error ER_LOCK_WAIT_TIMEOUT +INSERT INTO t1 (a) VALUES ((SELECT a FROM t2 WHERE b=7)); + +--error ER_LOCK_WAIT_TIMEOUT +UPDATE t1 SET a='7000000' WHERE a=(SELECT a FROM t2 WHERE b=7); + +--error ER_LOCK_WAIT_TIMEOUT +DELETE FROM t1 WHERE a=(SELECT a FROM t2 WHERE b=7); + +SELECT * FROM t1; + +CONNECTION default; +DISCONNECT addconroot; + +DROP TABLE t2, t1; + +--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 # Bug#41756 Strange error messages about locks from InnoDB +--echo # +--disable_warnings +drop table if exists t1; +--enable_warnings +--echo # In the default transaction isolation mode, and/or with +--echo # innodb_locks_unsafe_for_binlog=OFF, handler::unlock_row() +--echo # in InnoDB does nothing. +--echo # Thus in order to reproduce the condition that led to the +--echo # warning, one needs to relax isolation by either +--echo # setting a weaker tx_isolation value, or by turning on +--echo # the unsafe replication switch. +--echo # For testing purposes, choose to tweak the isolation level, +--echo # since it's settable at runtime, unlike +--echo # innodb_locks_unsafe_for_binlog, which is +--echo # only a command-line switch. +--echo # +set @@session.tx_isolation="read-committed"; + +--echo # Prepare data. We need a table with a unique index, +--echo # for join_read_key to be used. The other column +--echo # allows to control what passes WHERE clause filter. +create table t1 (a int primary key, b int) engine=innodb; +--echo # Let's make sure t1 has sufficient amount of rows +--echo # to exclude JT_ALL access method when reading it, +--echo # i.e. make sure that JT_EQ_REF(a) is always preferred. +insert into t1 values (1,1), (2,null), (3,1), (4,1), + (5,1), (6,1), (7,1), (8,1), (9,1), (10,1), + (11,1), (12,1), (13,1), (14,1), (15,1), + (16,1), (17,1), (18,1), (19,1), (20,1); +--echo # +--echo # Demonstrate that for the SELECT statement +--echo # used later in the test JT_EQ_REF access method is used. +--echo # +--vertical_results +explain +select 1 from t1 natural join (select 2 as a, 1 as b union all + select 2 as a, 2 as b) as t2 for update; +--horizontal_results +--echo # +--echo # Demonstrate that the reported SELECT statement +--echo # no longer produces warnings. +--echo # +select 1 from t1 natural join (select 2 as a, 1 as b union all + select 2 as a, 2 as b) as t2 for update; +commit; +--echo # +--echo # Demonstrate that due to lack of inter-sweep "reset" function, +--echo # we keep some non-matching records locked, even though we know +--echo # we could unlock them. +--echo # To do that, show that if there is only one distinct value +--echo # for a in t2 (a=2), we will keep record (2,null) in t1 locked. +--echo # But if we add another value for "a" to t2, say 6, +--echo # join_read_key cache will be pruned at least once, +--echo # and thus record (2, null) in t1 will get unlocked. +--echo # +begin; +select 1 from t1 natural join (select 2 as a, 1 as b union all + select 2 as a, 2 as b) as t2 for update; +connect (con1,localhost,root,,); +--echo # +--echo # Switching to connection con1 +connection con1; +--echo # We should be able to delete all records from t1 except (2, null), +--echo # since they were not locked. +begin; +--echo # Delete in series of 3 records so that full scan +--echo # is not used and we're not blocked on record (2,null) +delete from t1 where a in (1,3,4); +delete from t1 where a in (5,6,7); +delete from t1 where a in (8,9,10); +delete from t1 where a in (11,12,13); +delete from t1 where a in (14,15,16); +delete from t1 where a in (17,18); +delete from t1 where a in (19,20); +--echo # +--echo # Record (2, null) is locked. This is actually unnecessary, +--echo # because the previous select returned no rows. +--echo # Just demonstrate the effect. +--echo # +--error ER_LOCK_WAIT_TIMEOUT +delete from t1; +rollback; +--echo # +--echo # Switching to connection default +connection default; +--echo # +--echo # Show that the original contents of t1 is intact: +select * from t1; +commit; +--echo # +--echo # Have a one more record in t2 to show that +--echo # if join_read_key cache is purned, the current +--echo # row under the cursor is unlocked (provided, this row didn't +--echo # match the partial WHERE clause, of course). +--echo # Sic: the result of this test dependent on the order of retrieval +--echo # of records --echo # from the derived table, if ! +--echo # We use DELETE to disable the JOIN CACHE. This DELETE modifies no +--echo # records. It also should leave no InnoDB row locks. +--echo # +begin; +delete t1.* from t1 natural join (select 2 as a, 2 as b union all + select 0 as a, 0 as b) as t2; +--echo # Demonstrate that nothing was deleted form t1 +select * from t1; +--echo # +--echo # Switching to connection con1 +connection con1; +begin; +--echo # Since there is another distinct record in the derived table +--echo # the previous matching record in t1 -- (2,null) -- was unlocked. +delete from t1; +--echo # We will need the contents of the table again. +rollback; +select * from t1; +commit; +--echo # +--echo # Switching to connection default +connection default; +rollback; +begin; +--echo # +--echo # Before this patch, we could wrongly unlock a record +--echo # that was cached and later used in a join. Demonstrate that +--echo # this is no longer the case. +--echo # Sic: this test is also order-dependent (i.e. the +--echo # the bug would show up only if the first record in the union +--echo # is retreived and processed first. +--echo # +--echo # Verify that JT_EQ_REF is used. +--vertical_results +explain +select 1 from t1 natural join (select 3 as a, 2 as b union all + select 3 as a, 1 as b) as t2 for update; +--horizontal_results +--echo # Lock the record. +select 1 from t1 natural join (select 3 as a, 2 as b union all + select 3 as a, 1 as b) as t2 for update; +--echo # Switching to connection con1 +connection con1; +--echo # +--echo # We should not be able to delete record (3,1) from t1, +--echo # (previously it was possible). +--echo # +--error ER_LOCK_WAIT_TIMEOUT +delete from t1 where a=3; +--echo # Switching to connection default +connection default; +commit; + +disconnect con1; +set @@session.tx_isolation=default; +drop table t1; + +--echo # +--echo # End of 5.1 tests +--echo # diff --git a/mysql-test/t/innodb_mysql.test b/mysql-test/t/innodb_mysql.test index c643465b2f3..7055879ce1a 100644 --- a/mysql-test/t/innodb_mysql.test +++ b/mysql-test/t/innodb_mysql.test @@ -461,4 +461,33 @@ EXPLAIN SELECT * FROM t1 FORCE INDEX(PRIMARY) WHERE b=1 AND c=1 ORDER BY a; DROP TABLE t1; +--echo # +--echo # Bug #47963: Wrong results when index is used +--echo # +CREATE TABLE t1( + a VARCHAR(5) NOT NULL, + b VARCHAR(5) NOT NULL, + c DATETIME NOT NULL, + KEY (c) +) ENGINE=InnoDB; +INSERT INTO t1 VALUES('TEST', 'TEST', '2009-10-09 00:00:00'); +SELECT * FROM t1 WHERE a = 'TEST' AND + c >= '2009-10-09 00:00:00' AND c <= '2009-10-09 00:00:00'; +SELECT * FROM t1 WHERE a = 'TEST' AND + c >= '2009-10-09 00:00:00.0' AND c <= '2009-10-09 00:00:00.0'; +SELECT * FROM t1 WHERE a = 'TEST' AND + c >= '2009-10-09 00:00:00.0' AND c <= '2009-10-09 00:00:00'; +SELECT * FROM t1 WHERE a = 'TEST' AND + c >= '2009-10-09 00:00:00' AND c <= '2009-10-09 00:00:00.0'; +SELECT * FROM t1 WHERE a = 'TEST' AND + c >= '2009-10-09 00:00:00.000' AND c <= '2009-10-09 00:00:00.000'; +SELECT * FROM t1 WHERE a = 'TEST' AND + c >= '2009-10-09 00:00:00.00' AND c <= '2009-10-09 00:00:00.001'; +SELECT * FROM t1 WHERE a = 'TEST' AND + c >= '2009-10-09 00:00:00.001' AND c <= '2009-10-09 00:00:00.00'; +EXPLAIN SELECT * FROM t1 WHERE a = 'TEST' AND + c >= '2009-10-09 00:00:00.001' AND c <= '2009-10-09 00:00:00.00'; +DROP TABLE t1; + + --echo End of 5.1 tests 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/locale.test b/mysql-test/t/locale.test new file mode 100644 index 00000000000..7ceb49fd1f4 --- /dev/null +++ b/mysql-test/t/locale.test @@ -0,0 +1,18 @@ +--disable_warnings +DROP TABLE IF EXISTS t1; +--enable_warnings + +--echo Start of 5.4 tests +--echo # +--echo # Bug#43207 wrong LC_TIME names for romanian locale +--echo # +SET NAMES utf8; +SET lc_time_names=ro_RO; +SELECT DATE_FORMAT('2001-01-01', '%w %a %W'); +SELECT DATE_FORMAT('2001-01-02', '%w %a %W'); +SELECT DATE_FORMAT('2001-01-03', '%w %a %W'); +SELECT DATE_FORMAT('2001-01-04', '%w %a %W'); +SELECT DATE_FORMAT('2001-01-05', '%w %a %W'); +SELECT DATE_FORMAT('2001-01-06', '%w %a %W'); +SELECT DATE_FORMAT('2001-01-07', '%w %a %W'); +--echo End of 5.4 tests 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 dc119b6a77e..3a845471cd0 100644 --- a/mysql-test/t/range.test +++ b/mysql-test/t/range.test @@ -1171,3 +1171,93 @@ a < 5 OR a < 10; DROP TABLE t1, t2, t3; + +--echo # +--echo # Bug #47123: Endless 100% CPU loop with STRAIGHT_JOIN +--echo # + +CREATE TABLE t1(a INT, KEY(a)); +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 63b92451a65..f61088086b9 100644 --- a/mysql-test/t/sp-error.test +++ b/mysql-test/t/sp-error.test @@ -2431,3 +2431,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/view_grant.test b/mysql-test/t/view_grant.test index 2ad488b7529..175468db702 100644 --- a/mysql-test/t/view_grant.test +++ b/mysql-test/t/view_grant.test @@ -1506,3 +1506,29 @@ DROP VIEW v1; # Wait till we reached the initial number of concurrent sessions --source include/wait_until_count_sessions.inc +--echo # +--echo # Bug #46019: ERROR 1356 When selecting from within another +--echo # view that has Group By +--echo # +CREATE DATABASE mysqltest1; +USE mysqltest1; + +CREATE TABLE t1 (a INT); + +CREATE SQL SECURITY INVOKER VIEW v1 AS SELECT a FROM t1 GROUP BY a; +CREATE SQL SECURITY INVOKER VIEW v2 AS SELECT a FROM v1; + +CREATE USER mysqluser1; + +GRANT SELECT ON TABLE t1 TO mysqluser1; +GRANT SELECT, SHOW VIEW ON TABLE v1 TO mysqluser1; +GRANT SELECT, SHOW VIEW ON TABLE v2 TO mysqluser1; + +--connect (mysqluser1, localhost, mysqluser1,,mysqltest1) +SELECT a FROM v1; +SELECT a FROM v2; + +--connection default +--disconnect mysqluser1 +DROP USER mysqluser1; +DROP DATABASE mysqltest1; 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 |