diff options
Diffstat (limited to 'mysql-test/r')
37 files changed, 1461 insertions, 356 deletions
diff --git a/mysql-test/r/alter_table.result b/mysql-test/r/alter_table.result index db7173d0b47..06f4e7fbe8a 100644 --- a/mysql-test/r/alter_table.result +++ b/mysql-test/r/alter_table.result @@ -1330,4 +1330,12 @@ ALTER TABLE t1 MODIFY COLUMN a SET('a1','a2','a0','xx','a5','a6','a7','a8','a9', affected rows: 2 info: Records: 2 Duplicates: 0 Warnings: 0 DROP TABLE t1; +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); +this should affect no rows as there is no real change +ALTER TABLE t1 CHANGE COLUMN f1 f1_no_real_change TIMESTAMP NULL DEFAULT NULL; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 +DROP TABLE t1; End of 5.1 tests diff --git a/mysql-test/r/analyse.result b/mysql-test/r/analyse.result index 6eaa8731dc6..1820782d2f8 100644 --- a/mysql-test/r/analyse.result +++ b/mysql-test/r/analyse.result @@ -19,81 +19,10 @@ test.t1.empty_string 0 0 4 0 0.0000 NULL CHAR(0) NOT NULL test.t1.bool N Y 1 1 0 0 1.0000 NULL ENUM('N','Y') NOT NULL test.t1.d 2002-03-03 2002-03-05 10 10 0 0 10.0000 NULL ENUM('2002-03-03','2002-03-04','2002-03-05') NOT NULL create table t2 select * from t1 procedure analyse(); -select * from t2; -Field_name Min_value Max_value Min_length Max_length Empties_or_zeros Nulls Avg_value_or_avg_length Std Optimal_fieldtype -test.t1.i 1 7 1 1 0 0 4.0000 2.2361 ENUM('1','3','5','7') NOT NULL -test.t1.j 2 8 1 1 0 0 5.0000 2.2361 ENUM('2','4','6','8') NOT NULL -test.t1.empty_string 0 0 4 0 0.0000 NULL CHAR(0) NOT NULL -test.t1.bool N Y 1 1 0 0 1.0000 NULL ENUM('N','Y') NOT NULL -test.t1.d 2002-03-03 2002-03-05 10 10 0 0 10.0000 NULL ENUM('2002-03-03','2002-03-04','2002-03-05') NOT NULL -drop table t1,t2; +ERROR HY000: Incorrect usage of PROCEDURE and non-SELECT +drop table t1; EXPLAIN SELECT 1 FROM (SELECT 1) a PROCEDURE ANALYSE(); ERROR HY000: Incorrect usage of PROCEDURE and subquery -create table t1 (a int not null); -create table t2 select * from t1 where 0=1 procedure analyse(); -show create table t2; -Table Create Table -t2 CREATE TABLE `t2` ( - `Field_name` varbinary(255) NOT NULL DEFAULT '', - `Min_value` varbinary(255) DEFAULT NULL, - `Max_value` varbinary(255) DEFAULT NULL, - `Min_length` bigint(11) NOT NULL DEFAULT '0', - `Max_length` bigint(11) NOT NULL DEFAULT '0', - `Empties_or_zeros` bigint(11) NOT NULL DEFAULT '0', - `Nulls` bigint(11) NOT NULL DEFAULT '0', - `Avg_value_or_avg_length` varbinary(255) NOT NULL DEFAULT '', - `Std` varbinary(255) DEFAULT NULL, - `Optimal_fieldtype` varbinary(64) NOT NULL DEFAULT '' -) ENGINE=MyISAM DEFAULT CHARSET=latin1 -select * from t1 where 0=1 procedure analyse(); -Field_name Min_value Max_value Min_length Max_length Empties_or_zeros Nulls Avg_value_or_avg_length Std Optimal_fieldtype -insert into t1 values(1); -drop table t2; -create table t2 select * from t1 where 0=1 procedure analyse(); -show create table t2; -Table Create Table -t2 CREATE TABLE `t2` ( - `Field_name` varbinary(255) NOT NULL DEFAULT '', - `Min_value` varbinary(255) DEFAULT NULL, - `Max_value` varbinary(255) DEFAULT NULL, - `Min_length` bigint(11) NOT NULL DEFAULT '0', - `Max_length` bigint(11) NOT NULL DEFAULT '0', - `Empties_or_zeros` bigint(11) NOT NULL DEFAULT '0', - `Nulls` bigint(11) NOT NULL DEFAULT '0', - `Avg_value_or_avg_length` varbinary(255) NOT NULL DEFAULT '', - `Std` varbinary(255) DEFAULT NULL, - `Optimal_fieldtype` varbinary(64) NOT NULL DEFAULT '' -) ENGINE=MyISAM DEFAULT CHARSET=latin1 -select * from t2; -Field_name Min_value Max_value Min_length Max_length Empties_or_zeros Nulls Avg_value_or_avg_length Std Optimal_fieldtype -insert into t2 select * from t1 procedure analyse(); -select * from t2; -Field_name Min_value Max_value Min_length Max_length Empties_or_zeros Nulls Avg_value_or_avg_length Std Optimal_fieldtype -test.t1.a 1 1 1 1 0 0 1.0000 0.0000 ENUM('1') NOT NULL -insert into t1 values(2); -drop table t2; -create table t2 select * from t1 where 0=1 procedure analyse(); -show create table t2; -Table Create Table -t2 CREATE TABLE `t2` ( - `Field_name` varbinary(255) NOT NULL DEFAULT '', - `Min_value` varbinary(255) DEFAULT NULL, - `Max_value` varbinary(255) DEFAULT NULL, - `Min_length` bigint(11) NOT NULL DEFAULT '0', - `Max_length` bigint(11) NOT NULL DEFAULT '0', - `Empties_or_zeros` bigint(11) NOT NULL DEFAULT '0', - `Nulls` bigint(11) NOT NULL DEFAULT '0', - `Avg_value_or_avg_length` varbinary(255) NOT NULL DEFAULT '', - `Std` varbinary(255) DEFAULT NULL, - `Optimal_fieldtype` varbinary(64) NOT NULL DEFAULT '' -) ENGINE=MyISAM DEFAULT CHARSET=latin1 -select * from t2; -Field_name Min_value Max_value Min_length Max_length Empties_or_zeros Nulls Avg_value_or_avg_length Std Optimal_fieldtype -insert into t2 select * from t1 procedure analyse(); -select * from t2; -Field_name Min_value Max_value Min_length Max_length Empties_or_zeros Nulls Avg_value_or_avg_length Std Optimal_fieldtype -test.t1.a 1 2 1 1 0 0 1.5000 0.5000 ENUM('1','2') NOT NULL -drop table t1,t2; create table t1 (v varchar(128)); insert into t1 values ('abc'),('abc\'def\\hij\"klm\0opq'),('\''),('\"'),('\\'),('a\0'),('b\''),('c\"'),('d\\'),('\'b'),('\"c'),('\\d'),('a\0\0\0b'),('a\'\'\'\'b'),('a\"\"\"\"b'),('a\\\\\\\\b'),('\'\0\\\"'),('\'\''),('\"\"'),('\\\\'),('The\ZEnd'); select * from t1 procedure analyse(); @@ -157,3 +86,40 @@ SELECT * FROM (SELECT * FROM t1) d PROCEDURE ANALYSE(); ERROR HY000: Incorrect usage of PROCEDURE and subquery DROP TABLE t1; End of 4.1 tests +# +# Bug #48293: crash with procedure analyse, view with > 10 columns, +# having clause... +# +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; +#should have a derived table +EXPLAIN SELECT * FROM v1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 +2 DERIVED t1 ALL NULL NULL NULL NULL 2 +#should not crash +SELECT * FROM v1 PROCEDURE analyse(); +ERROR HY000: Incorrect usage of PROCEDURE and view +#should not crash +SELECT * FROM t1 a, v1, t1 b PROCEDURE analyse(); +ERROR HY000: Incorrect usage of PROCEDURE and view +#should not crash +SELECT * FROM (SELECT * FROM t1 having a > 1) x PROCEDURE analyse(); +ERROR HY000: Incorrect usage of PROCEDURE and subquery +#should not crash +SELECT * FROM t1 a, (SELECT * FROM t1 having a > 1) x, t1 b PROCEDURE analyse(); +ERROR HY000: Incorrect usage of PROCEDURE and subquery +#should not crash +SELECT 1 FROM t1 group by a having a > 1 order by 1 PROCEDURE analyse(); +ERROR HY000: Can't use ORDER clause with this procedure +DROP VIEW v1; +DROP TABLE t1; +CREATE TABLE t1(a INT); +INSERT INTO t1 VALUES (1),(2); +# should not crash +CREATE TABLE t2 SELECT 1 FROM t1, t1 t3 GROUP BY t3.a PROCEDURE ANALYSE(); +ERROR HY000: Incorrect usage of PROCEDURE and non-SELECT +DROP TABLE t1; +End of 5.0 tests diff --git a/mysql-test/r/bug40113.result b/mysql-test/r/bug40113.result deleted file mode 100644 index 289037a3f35..00000000000 --- a/mysql-test/r/bug40113.result +++ /dev/null @@ -1,29 +0,0 @@ -# -# Bug #40113: Embedded SELECT inside UPDATE or DELETE can timeout -# without error -# -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; -a b -1070109 99 -BEGIN; -SELECT b FROM t2 WHERE b=7 FOR UPDATE; -b -7 -BEGIN; -SELECT b FROM t2 WHERE b=7 FOR UPDATE; -ERROR HY000: Lock wait timeout exceeded; try restarting transaction -INSERT INTO t1 (a) VALUES ((SELECT a FROM t2 WHERE b=7)); -ERROR HY000: Lock wait timeout exceeded; try restarting transaction -UPDATE t1 SET a='7000000' WHERE a=(SELECT a FROM t2 WHERE b=7); -ERROR HY000: Lock wait timeout exceeded; try restarting transaction -DELETE FROM t1 WHERE a=(SELECT a FROM t2 WHERE b=7); -ERROR HY000: Lock wait timeout exceeded; try restarting transaction -SELECT * FROM t1; -a b -1070109 99 -DROP TABLE t2, t1; -End of 5.0 tests diff --git a/mysql-test/r/case_insensitive_fs.require b/mysql-test/r/case_insensitive_fs.require new file mode 100644 index 00000000000..062ac610ddd --- /dev/null +++ b/mysql-test/r/case_insensitive_fs.require @@ -0,0 +1,2 @@ +Variable_name Value +lower_case_file_system ON diff --git a/mysql-test/r/ctype_ldml.result b/mysql-test/r/ctype_ldml.result index fe870f82743..d5585dcfad9 100644 --- a/mysql-test/r/ctype_ldml.result +++ b/mysql-test/r/ctype_ldml.result @@ -41,6 +41,14 @@ efgh efgh ijkl ijkl DROP TABLE t1; # +# Bug#45645 Mysql server close all connection and restart using lower function +# +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'); +a +DROP TABLE t1; +# # Bug#43827 Server closes connections and restarts # CREATE TABLE t1 (c1 VARCHAR(10) CHARACTER SET utf8 COLLATE utf8_test_ci); diff --git a/mysql-test/r/explain.result b/mysql-test/r/explain.result index 96fcbc33d3f..5a1bf1a1290 100644 --- a/mysql-test/r/explain.result +++ b/mysql-test/r/explain.result @@ -194,4 +194,20 @@ dt 2001-01-01 01:01:01 2001-01-01 01:01:01 drop tables t1, t2; +# +# Bug#48295: +# explain extended crash with subquery and ONLY_FULL_GROUP_BY sql_mode +# +CREATE TABLE t1 (f1 INT); +SELECT @@session.sql_mode INTO @old_sql_mode; +SET SESSION sql_mode='ONLY_FULL_GROUP_BY'; +EXPLAIN EXTENDED SELECT 1 FROM t1 +WHERE f1 > ALL( SELECT t.f1 FROM t1,t1 AS t ); +ERROR 42000: Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause +SHOW WARNINGS; +Level Code Message +Error 1140 Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause +Note 1003 select 1 AS `1` from `test`.`t1` where <not>(<exists>(...)) +SET SESSION sql_mode=@old_sql_mode; +DROP TABLE t1; End of 5.1 tests. diff --git a/mysql-test/r/gis-rtree.result b/mysql-test/r/gis-rtree.result index b030139e40e..68c4a6a13e5 100644 --- a/mysql-test/r/gis-rtree.result +++ b/mysql-test/r/gis-rtree.result @@ -1487,4 +1487,43 @@ MBRINTERSECTS(b, GEOMFROMTEXT('LINESTRING(1 1,1102219 2)') ); COUNT(*) 2 DROP TABLE t1; +# +# Bug #48258: Assertion failed when using a spatial index +# +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)'); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL a NULL NULL NULL 2 Using where +SELECT 1 FROM t1 WHERE a = GEOMFROMTEXT('LINESTRING(-1 -1, 1 -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)'); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL a NULL NULL NULL 2 Using where +SELECT 1 FROM t1 WHERE a < GEOMFROMTEXT('LINESTRING(-1 -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)'); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL a NULL NULL NULL 2 Using where +SELECT 1 FROM t1 WHERE a <= GEOMFROMTEXT('LINESTRING(-1 -1, 1 -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)'); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL a NULL NULL NULL 2 Using where +SELECT 1 FROM t1 WHERE a > GEOMFROMTEXT('LINESTRING(-1 -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)'); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL a NULL NULL NULL 2 Using where +SELECT 1 FROM t1 WHERE a >= GEOMFROMTEXT('LINESTRING(-1 -1, 1 -1, -1 -1, -1 1, 1 1)'); +1 +1 +1 +DROP TABLE t1; End of 5.0 tests. diff --git a/mysql-test/r/gis.result b/mysql-test/r/gis.result index a3708d06a1c..b40ff6be160 100644 --- a/mysql-test/r/gis.result +++ b/mysql-test/r/gis.result @@ -972,6 +972,18 @@ select min(`col002`) from t1 union select `col002` from t1; min(`col002`) NULL drop table t1; +# +# Bug #47780: crash when comparing GIS items from subquery +# +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)))')); +# must not crash +SELECT 1 FROM t1 WHERE a <> (SELECT GEOMETRYCOLLECTIONFROMWKB(b) FROM t1); +1 +DROP TABLE t1; End of 5.0 tests create table t1 (f1 tinyint(1), f2 char(1), f3 varchar(1), f4 geometry, f5 datetime); create view v1 as select * from t1; diff --git a/mysql-test/r/grant.result b/mysql-test/r/grant.result index a677d71b266..92beccd2a9e 100644 --- a/mysql-test/r/grant.result +++ b/mysql-test/r/grant.result @@ -1007,8 +1007,8 @@ DROP TABLE mysqltest1.t2; SHOW GRANTS; Grants for mysqltest_1@localhost GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost' -GRANT SELECT, INSERT, CREATE, DROP, ALTER ON `mysqltest1`.`t2` TO 'mysqltest_1'@'localhost' GRANT SELECT, INSERT, CREATE, DROP, ALTER ON `mysqltest1`.`t1` TO 'mysqltest_1'@'localhost' +GRANT SELECT, INSERT, CREATE, DROP, ALTER ON `mysqltest1`.`t2` TO 'mysqltest_1'@'localhost' RENAME TABLE t1 TO t2; RENAME TABLE t2 TO t1; ALTER TABLE t1 RENAME TO t2; @@ -1018,8 +1018,8 @@ REVOKE DROP, INSERT ON mysqltest1.t2 FROM mysqltest_1@localhost; SHOW GRANTS; Grants for mysqltest_1@localhost GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost' -GRANT SELECT, CREATE, ALTER ON `mysqltest1`.`t2` TO 'mysqltest_1'@'localhost' GRANT SELECT, CREATE, ALTER ON `mysqltest1`.`t1` TO 'mysqltest_1'@'localhost' +GRANT SELECT, CREATE, ALTER ON `mysqltest1`.`t2` TO 'mysqltest_1'@'localhost' RENAME TABLE t1 TO t2; ERROR 42000: DROP command denied to user 'mysqltest_1'@'localhost' for table 't1' ALTER TABLE t1 RENAME TO t2; diff --git a/mysql-test/r/grant3.result b/mysql-test/r/grant3.result index f38848111ad..59c64ee84ae 100644 --- a/mysql-test/r/grant3.result +++ b/mysql-test/r/grant3.result @@ -154,4 +154,42 @@ SELECT * FROM mysqltest_1.t1; a DROP USER 'mysqltest1'@'%'; DROP DATABASE mysqltest_1; +# +# Bug#41597 - After rename of user, there are additional grants +# when grants are reapplied. +# +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'@'%'; +# Show privileges after rename and BEFORE grant +SHOW GRANTS FOR 'user2'@'%'; +Grants for user2@% +GRANT USAGE ON *.* TO 'user2'@'%' +GRANT SELECT (a), INSERT (b) ON `temp`.`t1` TO 'user2'@'%'; +# Show privileges after rename and grant +SHOW GRANTS FOR 'user2'@'%'; +Grants for user2@% +GRANT USAGE ON *.* TO 'user2'@'%' +GRANT SELECT (a), INSERT (b) ON `temp`.`t1` TO 'user2'@'%' +# Connect as the renamed user +SHOW GRANTS; +Grants for user2@% +GRANT USAGE ON *.* TO 'user2'@'%' +GRANT SELECT (a), INSERT (b) ON `temp`.`t1` TO 'user2'@'%' +SELECT a FROM temp.t1; +a +1 +2 +3 +# Check for additional privileges by accessing a +# non privileged column. We shouldn't be able to +# access this column. +SELECT b FROM temp.t1; +ERROR 42000: SELECT command denied to user 'user2'@'localhost' for column 'b' in table 't1' +DROP USER 'user2'@'%'; +DROP DATABASE temp; End of 5.0 tests diff --git a/mysql-test/r/grant_lowercase_fs.result b/mysql-test/r/grant_lowercase_fs.result new file mode 100644 index 00000000000..5a3087ed5cd --- /dev/null +++ b/mysql-test/r/grant_lowercase_fs.result @@ -0,0 +1,16 @@ +create database db1; +GRANT CREATE ON db1.* to user_1@localhost; +GRANT SELECT ON db1.* to USER_1@localhost; +CREATE TABLE t1(f1 int); +SELECT * FROM t1; +ERROR 42000: SELECT command denied to user 'user_1'@'localhost' for table 't1' +SELECT * FROM t1; +f1 +CREATE TABLE t2(f1 int); +ERROR 42000: CREATE command denied to user 'USER_1'@'localhost' for table 't2' +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/r/innodb-autoinc.result b/mysql-test/r/innodb-autoinc.result index d2e8eb19e0c..a40a13dbe9f 100644 --- a/mysql-test/r/innodb-autoinc.result +++ b/mysql-test/r/innodb-autoinc.result @@ -867,6 +867,7 @@ INSERT INTO t2 SELECT NULL FROM t1; Got one of the listed errors DROP TABLE t1; DROP TABLE t2; +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); @@ -882,6 +883,12 @@ d1 INSERT INTO t1 VALUES(null); Got one of the listed errors ALTER TABLE t1 AUTO_INCREMENT = 3; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `d1` int(11) NOT NULL AUTO_INCREMENT, + PRIMARY KEY (`d1`) +) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1 INSERT INTO t1 VALUES(null); SELECT * FROM t1; d1 @@ -889,3 +896,233 @@ d1 3 4 DROP TABLE t1; +SET @@SESSION.AUTO_INCREMENT_INCREMENT=1, @@SESSION.AUTO_INCREMENT_OFFSET=1; +SHOW VARIABLES LIKE "%auto_inc%"; +Variable_name Value +auto_increment_increment 1 +auto_increment_offset 1 +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; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` tinyint(4) NOT NULL AUTO_INCREMENT, + `c2` varchar(10) DEFAULT NULL, + PRIMARY KEY (`c1`) +) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1 +SELECT * FROM t1; +c1 c2 +-127 innodb +-1 innodb +1 NULL +2 NULL +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'); +Warnings: +Warning 1264 Out of range value for column 'c1' at row 1 +INSERT INTO t1 VALUES (-127, 'innodb'); +Warnings: +Warning 1264 Out of range value for column 'c1' at row 1 +INSERT INTO t1 VALUES (NULL, NULL); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` tinyint(3) unsigned NOT NULL AUTO_INCREMENT, + `c2` varchar(10) DEFAULT NULL, + PRIMARY KEY (`c1`) +) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1 +SELECT * FROM t1; +c1 c2 +1 NULL +2 innodb +3 innodb +4 NULL +DROP TABLE t1; +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; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` smallint(6) NOT NULL AUTO_INCREMENT, + `c2` varchar(10) DEFAULT NULL, + PRIMARY KEY (`c1`) +) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1 +SELECT * FROM t1; +c1 c2 +-32767 innodb +-1 innodb +1 NULL +2 NULL +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'); +Warnings: +Warning 1264 Out of range value for column 'c1' at row 1 +INSERT INTO t1 VALUES (-32757, 'innodb'); +Warnings: +Warning 1264 Out of range value for column 'c1' at row 1 +INSERT INTO t1 VALUES (NULL, NULL); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` smallint(5) unsigned NOT NULL AUTO_INCREMENT, + `c2` varchar(10) DEFAULT NULL, + PRIMARY KEY (`c1`) +) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1 +SELECT * FROM t1; +c1 c2 +1 NULL +2 innodb +3 innodb +4 NULL +DROP TABLE t1; +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; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` mediumint(9) NOT NULL AUTO_INCREMENT, + `c2` varchar(10) DEFAULT NULL, + PRIMARY KEY (`c1`) +) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1 +SELECT * FROM t1; +c1 c2 +-8388607 innodb +-1 innodb +1 NULL +2 NULL +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'); +Warnings: +Warning 1264 Out of range value for column 'c1' at row 1 +INSERT INTO t1 VALUES (-8388607, 'innodb'); +Warnings: +Warning 1264 Out of range value for column 'c1' at row 1 +INSERT INTO t1 VALUES (NULL, NULL); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` mediumint(8) unsigned NOT NULL AUTO_INCREMENT, + `c2` varchar(10) DEFAULT NULL, + PRIMARY KEY (`c1`) +) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1 +SELECT * FROM t1; +c1 c2 +1 NULL +2 innodb +3 innodb +4 NULL +DROP TABLE t1; +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; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` int(11) NOT NULL AUTO_INCREMENT, + `c2` varchar(10) DEFAULT NULL, + PRIMARY KEY (`c1`) +) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1 +SELECT * FROM t1; +c1 c2 +-2147483647 innodb +-1 innodb +1 NULL +2 NULL +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'); +Warnings: +Warning 1264 Out of range value for column 'c1' at row 1 +INSERT INTO t1 VALUES (-2147483647, 'innodb'); +Warnings: +Warning 1264 Out of range value for column 'c1' at row 1 +INSERT INTO t1 VALUES (NULL, NULL); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` int(10) unsigned NOT NULL AUTO_INCREMENT, + `c2` varchar(10) DEFAULT NULL, + PRIMARY KEY (`c1`) +) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1 +SELECT * FROM t1; +c1 c2 +1 NULL +2 innodb +3 innodb +4 NULL +DROP TABLE t1; +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; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` bigint(20) NOT NULL AUTO_INCREMENT, + `c2` varchar(10) DEFAULT NULL, + PRIMARY KEY (`c1`) +) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1 +SELECT * FROM t1; +c1 c2 +-9223372036854775807 innodb +-1 innodb +1 NULL +2 NULL +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'); +Warnings: +Warning 1264 Out of range value for column 'c1' at row 1 +INSERT INTO t1 VALUES (-9223372036854775807, 'innodb'); +Warnings: +Warning 1264 Out of range value for column 'c1' at row 1 +INSERT INTO t1 VALUES (NULL, NULL); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` bigint(20) unsigned NOT NULL AUTO_INCREMENT, + `c2` varchar(10) DEFAULT NULL, + PRIMARY KEY (`c1`) +) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1 +SELECT * FROM t1; +c1 c2 +1 NULL +2 innodb +3 innodb +4 NULL +DROP TABLE t1; +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; +Table Create Table +T1 CREATE TABLE `T1` ( + `c1` int(11) NOT NULL AUTO_INCREMENT, + `c2` int(11) DEFAULT NULL, + PRIMARY KEY (`c1`), + KEY `i1` (`c2`) +) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=latin1 +INSERT INTO T1 (c2) values (0); +SELECT * FROM T1; +c1 c2 +10 0 +DROP TABLE T1; diff --git a/mysql-test/r/innodb_bug44369.result b/mysql-test/r/innodb_bug44369.result index e4b84ecac19..9cf79aeffab 100644 --- a/mysql-test/r/innodb_bug44369.result +++ b/mysql-test/r/innodb_bug44369.result @@ -2,13 +2,13 @@ create table bug44369 (DB_ROW_ID int) engine=innodb; ERROR HY000: Can't create table 'test.bug44369' (errno: -1) create table bug44369 (db_row_id int) engine=innodb; ERROR HY000: Can't create table 'test.bug44369' (errno: -1) -show errors; +show warnings; Level Code Message -Error 1005 Error creating table 'test/bug44369' with column name 'db_row_id'. 'db_row_id' is a reserved name. Please try to re-create the table with a different column name. +Warning 1005 Error creating table 'test/bug44369' with column name 'db_row_id'. 'db_row_id' is a reserved name. Please try to re-create the table with a different column name. Error 1005 Can't create table 'test.bug44369' (errno: -1) create table bug44369 (db_TRX_Id int) engine=innodb; ERROR HY000: Can't create table 'test.bug44369' (errno: -1) -show errors; +show warnings; Level Code Message -Error 1005 Error creating table 'test/bug44369' with column name 'db_TRX_Id'. 'db_TRX_Id' is a reserved name. Please try to re-create the table with a different column name. +Warning 1005 Error creating table 'test/bug44369' with column name 'db_TRX_Id'. 'db_TRX_Id' is a reserved name. Please try to re-create the table with a different column name. Error 1005 Can't create table 'test.bug44369' (errno: -1) diff --git a/mysql-test/r/innodb_bug46000.result b/mysql-test/r/innodb_bug46000.result index ccff888a48d..ccf7cc56859 100644 --- a/mysql-test/r/innodb_bug46000.result +++ b/mysql-test/r/innodb_bug46000.result @@ -2,16 +2,16 @@ create table bug46000(`id` int,key `GEN_CLUST_INDEX`(`id`))engine=innodb; ERROR HY000: Can't create table 'test.bug46000' (errno: -1) create table bug46000(`id` int, key `GEN_clust_INDEX`(`id`))engine=innodb; ERROR HY000: Can't create table 'test.bug46000' (errno: -1) -show errors; +show warnings; Level Code Message -Error 1005 Cannot Create Index with name 'GEN_CLUST_INDEX'. The name is reserved for the system default primary index. +Warning 1005 Cannot Create Index with name 'GEN_CLUST_INDEX'. The name is reserved for the system default primary index. Error 1005 Can't create table 'test.bug46000' (errno: -1) create table bug46000(id int) engine=innodb; create index GEN_CLUST_INDEX on bug46000(id); ERROR HY000: Can't create table '#sql-temporary' (errno: -1) -show errors; +show warnings; Level Code Message -Error 1005 Cannot Create Index with name 'GEN_CLUST_INDEX'. The name is reserved for the system default primary index. +Warning 1005 Cannot Create Index with name 'GEN_CLUST_INDEX'. The name is reserved for the system default primary index. Error 1005 Can't create table '#sql-temporary' (errno: -1) create index idx on bug46000(id); drop table bug46000; diff --git a/mysql-test/r/innodb_bug47777.result b/mysql-test/r/innodb_bug47777.result new file mode 100644 index 00000000000..fbba47edcfc --- /dev/null +++ b/mysql-test/r/innodb_bug47777.result @@ -0,0 +1,13 @@ +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)')); +select count(*) from bug47777 where c2 =geomfromtext('linestring(1 2,3 4,5 6,7 8,9 10)'); +count(*) +1 +update bug47777 set c2=GeomFromText('POINT(1 1)'); +select count(*) from bug47777 where c2 =geomfromtext('linestring(1 2,3 4,5 6,7 8,9 10)'); +count(*) +0 +select count(*) from bug47777 where c2 = GeomFromText('POINT(1 1)'); +count(*) +1 +drop table bug47777; diff --git a/mysql-test/r/innodb_lock_wait_timeout_1.result b/mysql-test/r/innodb_lock_wait_timeout_1.result new file mode 100644 index 00000000000..a635b0d527a --- /dev/null +++ b/mysql-test/r/innodb_lock_wait_timeout_1.result @@ -0,0 +1,357 @@ +# +# Bug #40113: Embedded SELECT inside UPDATE or DELETE can timeout +# without error +# +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; +a b +1070109 99 +BEGIN; +SELECT b FROM t2 WHERE b=7 FOR UPDATE; +b +7 +BEGIN; +SELECT b FROM t2 WHERE b=7 FOR UPDATE; +ERROR HY000: Lock wait timeout exceeded; try restarting transaction +INSERT INTO t1 (a) VALUES ((SELECT a FROM t2 WHERE b=7)); +ERROR HY000: Lock wait timeout exceeded; try restarting transaction +UPDATE t1 SET a='7000000' WHERE a=(SELECT a FROM t2 WHERE b=7); +ERROR HY000: Lock wait timeout exceeded; try restarting transaction +DELETE FROM t1 WHERE a=(SELECT a FROM t2 WHERE b=7); +ERROR HY000: Lock wait timeout exceeded; try restarting transaction +SELECT * FROM t1; +a b +1070109 99 +DROP TABLE t2, t1; +# End of 5.0 tests +# +# Bug#46539 Various crashes on INSERT IGNORE SELECT + SELECT +# FOR UPDATE +# +drop table if exists t1; +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; +a b +5 5 +insert ignore into t1 (b) select a as b from t1; +ERROR HY000: Lock wait timeout exceeded; try restarting transaction +# Cleanup +# +commit; +set autocommit=default; +drop table t1; +# +# Bug#41756 Strange error messages about locks from InnoDB +# +drop table if exists t1; +# In the default transaction isolation mode, and/or with +# innodb_locks_unsafe_for_binlog=OFF, handler::unlock_row() +# in InnoDB does nothing. +# Thus in order to reproduce the condition that led to the +# warning, one needs to relax isolation by either +# setting a weaker tx_isolation value, or by turning on +# the unsafe replication switch. +# For testing purposes, choose to tweak the isolation level, +# since it's settable at runtime, unlike +# innodb_locks_unsafe_for_binlog, which is +# only a command-line switch. +# +set @@session.tx_isolation="read-committed"; +# Prepare data. We need a table with a unique index, +# for join_read_key to be used. The other column +# allows to control what passes WHERE clause filter. +create table t1 (a int primary key, b int) engine=innodb; +# Let's make sure t1 has sufficient amount of rows +# to exclude JT_ALL access method when reading it, +# 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); +# +# Demonstrate that for the SELECT statement +# used later in the test JT_EQ_REF access method is used. +# +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; +id 1 +select_type PRIMARY +table <derived2> +type ALL +possible_keys NULL +key NULL +key_len NULL +ref NULL +rows 2 +Extra +id 1 +select_type PRIMARY +table t1 +type eq_ref +possible_keys PRIMARY +key PRIMARY +key_len 4 +ref t2.a +rows 1 +Extra Using where +id 2 +select_type DERIVED +table NULL +type NULL +possible_keys NULL +key NULL +key_len NULL +ref NULL +rows NULL +Extra No tables used +id 3 +select_type UNION +table NULL +type NULL +possible_keys NULL +key NULL +key_len NULL +ref NULL +rows NULL +Extra No tables used +id NULL +select_type UNION RESULT +table <union2,3> +type ALL +possible_keys NULL +key NULL +key_len NULL +ref NULL +rows NULL +Extra +# +# Demonstrate that the reported SELECT statement +# no longer produces warnings. +# +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; +1 +commit; +# +# Demonstrate that due to lack of inter-sweep "reset" function, +# we keep some non-matching records locked, even though we know +# we could unlock them. +# To do that, show that if there is only one distinct value +# for a in t2 (a=2), we will keep record (2,null) in t1 locked. +# But if we add another value for "a" to t2, say 6, +# join_read_key cache will be pruned at least once, +# and thus record (2, null) in t1 will get unlocked. +# +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; +1 +# +# Switching to connection con1 +# We should be able to delete all records from t1 except (2, null), +# since they were not locked. +begin; +# Delete in series of 3 records so that full scan +# 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); +# +# Record (2, null) is locked. This is actually unnecessary, +# because the previous select returned no rows. +# Just demonstrate the effect. +# +delete from t1; +ERROR HY000: Lock wait timeout exceeded; try restarting transaction +rollback; +# +# Switching to connection default +# +# Show that the original contents of t1 is intact: +select * from t1; +a b +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 +commit; +# +# Have a one more record in t2 to show that +# if join_read_key cache is purned, the current +# row under the cursor is unlocked (provided, this row didn't +# match the partial WHERE clause, of course). +# Sic: the result of this test dependent on the order of retrieval +# of records --echo # from the derived table, if ! +# We use DELETE to disable the JOIN CACHE. This DELETE modifies no +# records. It also should leave no InnoDB row locks. +# +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; +# Demonstrate that nothing was deleted form t1 +select * from t1; +a b +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 +# +# Switching to connection con1 +begin; +# Since there is another distinct record in the derived table +# the previous matching record in t1 -- (2,null) -- was unlocked. +delete from t1; +# We will need the contents of the table again. +rollback; +select * from t1; +a b +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 +commit; +# +# Switching to connection default +rollback; +begin; +# +# Before this patch, we could wrongly unlock a record +# that was cached and later used in a join. Demonstrate that +# this is no longer the case. +# Sic: this test is also order-dependent (i.e. the +# the bug would show up only if the first record in the union +# is retreived and processed first. +# +# Verify that JT_EQ_REF is used. +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; +id 1 +select_type PRIMARY +table <derived2> +type ALL +possible_keys NULL +key NULL +key_len NULL +ref NULL +rows 2 +Extra +id 1 +select_type PRIMARY +table t1 +type eq_ref +possible_keys PRIMARY +key PRIMARY +key_len 4 +ref t2.a +rows 1 +Extra Using where +id 2 +select_type DERIVED +table NULL +type NULL +possible_keys NULL +key NULL +key_len NULL +ref NULL +rows NULL +Extra No tables used +id 3 +select_type UNION +table NULL +type NULL +possible_keys NULL +key NULL +key_len NULL +ref NULL +rows NULL +Extra No tables used +id NULL +select_type UNION RESULT +table <union2,3> +type ALL +possible_keys NULL +key NULL +key_len NULL +ref NULL +rows NULL +Extra +# 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; +1 +1 +# Switching to connection con1 +# +# We should not be able to delete record (3,1) from t1, +# (previously it was possible). +# +delete from t1 where a=3; +ERROR HY000: Lock wait timeout exceeded; try restarting transaction +# Switching to connection default +commit; +set @@session.tx_isolation=default; +drop table t1; +# +# End of 5.1 tests +# diff --git a/mysql-test/r/innodb_mysql.result b/mysql-test/r/innodb_mysql.result index b112bde4f27..c882d2af1ed 100644 --- a/mysql-test/r/innodb_mysql.result +++ b/mysql-test/r/innodb_mysql.result @@ -2209,4 +2209,46 @@ EXPLAIN SELECT * FROM t1 FORCE INDEX(PRIMARY) WHERE b=1 AND c=1 ORDER BY a; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index NULL PRIMARY 4 NULL 128 Using where DROP TABLE t1; +# +# Bug #47963: Wrong results when index is used +# +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'; +a b c +TEST TEST 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'; +a b c +TEST TEST 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'; +a b c +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.0'; +a b c +TEST TEST 2009-10-09 00:00:00 +SELECT * FROM t1 WHERE a = 'TEST' AND +c >= '2009-10-09 00:00:00.000' AND c <= '2009-10-09 00:00:00.000'; +a b c +TEST TEST 2009-10-09 00:00:00 +SELECT * FROM t1 WHERE a = 'TEST' AND +c >= '2009-10-09 00:00:00.00' AND c <= '2009-10-09 00:00:00.001'; +a b c +TEST TEST 2009-10-09 00:00:00 +SELECT * FROM t1 WHERE a = 'TEST' AND +c >= '2009-10-09 00:00:00.001' AND c <= '2009-10-09 00:00:00.00'; +a b c +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'; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +DROP TABLE t1; End of 5.1 tests diff --git a/mysql-test/r/join.result b/mysql-test/r/join.result index 77f73532474..baabf48cb2f 100644 --- a/mysql-test/r/join.result +++ b/mysql-test/r/join.result @@ -1063,6 +1063,60 @@ a b c d 127 NULL 127 NULL 128 NULL 128 NULL DROP TABLE IF EXISTS t1,t2; +# +# Bug #42116: Mysql crash on specific query +# +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; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 +1 SIMPLE t3 ref a a 5 test.t1.a 2 Using index +1 SIMPLE t4 ALL NULL NULL NULL NULL 0 +1 SIMPLE t5 ALL NULL NULL NULL NULL 0 +1 SIMPLE t6 ALL NULL NULL NULL NULL 0 +1 SIMPLE t2 ALL NULL NULL NULL NULL 10 Using where; Using join buffer +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; +a a a a a a +DROP TABLE t1,t2,t3,t4,t5,t6; End of 5.0 tests. CREATE TABLE t1 (f1 int); CREATE TABLE t2 (f1 int); diff --git a/mysql-test/r/locale.result b/mysql-test/r/locale.result new file mode 100644 index 00000000000..89883b070d2 --- /dev/null +++ b/mysql-test/r/locale.result @@ -0,0 +1,29 @@ +DROP TABLE IF EXISTS t1; +Start of 5.4 tests +# +# Bug#43207 wrong LC_TIME names for romanian locale +# +SET NAMES utf8; +SET lc_time_names=ro_RO; +SELECT DATE_FORMAT('2001-01-01', '%w %a %W'); +DATE_FORMAT('2001-01-01', '%w %a %W') +1 Lu Luni +SELECT DATE_FORMAT('2001-01-02', '%w %a %W'); +DATE_FORMAT('2001-01-02', '%w %a %W') +2 Ma Marţi +SELECT DATE_FORMAT('2001-01-03', '%w %a %W'); +DATE_FORMAT('2001-01-03', '%w %a %W') +3 Mi Miercuri +SELECT DATE_FORMAT('2001-01-04', '%w %a %W'); +DATE_FORMAT('2001-01-04', '%w %a %W') +4 Jo Joi +SELECT DATE_FORMAT('2001-01-05', '%w %a %W'); +DATE_FORMAT('2001-01-05', '%w %a %W') +5 Vi Vineri +SELECT DATE_FORMAT('2001-01-06', '%w %a %W'); +DATE_FORMAT('2001-01-06', '%w %a %W') +6 Sâ Sâmbătă +SELECT DATE_FORMAT('2001-01-07', '%w %a %W'); +DATE_FORMAT('2001-01-07', '%w %a %W') +0 Du Duminică +End of 5.4 tests diff --git a/mysql-test/r/lowercase_fs_off.result b/mysql-test/r/lowercase_fs_off.result index ecb21261987..4a59801692d 100644 --- a/mysql-test/r/lowercase_fs_off.result +++ b/mysql-test/r/lowercase_fs_off.result @@ -10,3 +10,48 @@ create database D1; ERROR 42000: Access denied for user 'sample'@'localhost' to database 'D1' drop user 'sample'@'localhost'; drop database if exists d1; +CREATE DATABASE d1; +USE d1; +CREATE TABLE T1(f1 INT); +CREATE TABLE t1(f1 INT); +GRANT SELECT ON T1 to user_1@localhost; +select * from t1; +ERROR 42000: SELECT command denied to user 'user_1'@'localhost' for table 't1' +select * from T1; +f1 +GRANT SELECT ON t1 to user_1@localhost; +select * from information_schema.table_privileges; +GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE_NAME PRIVILEGE_TYPE IS_GRANTABLE +'user_1'@'localhost' NULL d1 T1 SELECT NO +'user_1'@'localhost' NULL d1 t1 SELECT NO +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; +call p1(); +call P1(); +select f1(1); +f1(1) +2 +call p1(); +ERROR 42000: execute command denied to user 'USER_1'@'localhost' for routine 'db1.p1' +call P1(); +ERROR 42000: execute command denied to user 'USER_1'@'localhost' for routine 'db1.p1' +select f1(1); +ERROR 42000: execute command denied to user 'USER_1'@'localhost' for routine 'db1.f1' +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; diff --git a/mysql-test/r/myisam.result b/mysql-test/r/myisam.result index f5050ccf1c0..95fdc4fb93d 100644 --- a/mysql-test/r/myisam.result +++ b/mysql-test/r/myisam.result @@ -2290,6 +2290,12 @@ Table Op Msg_type Msg_text test.t1 repair error myisam_sort_buffer_size is too small test.t1 repair warning Number of rows changed from 0 to 7168 test.t1 repair status OK +SET myisam_repair_threads=2; +REPAIR TABLE t1; +SET myisam_repair_threads=@@global.myisam_repair_threads; SET myisam_sort_buffer_size=@@global.myisam_sort_buffer_size; +CHECK TABLE t1; +Table Op Msg_type Msg_text +test.t1 check status OK DROP TABLE t1; End of 5.1 tests diff --git a/mysql-test/r/myisam_crash_before_flush_keys.result b/mysql-test/r/myisam_crash_before_flush_keys.result index 372f2e41590..d3545ea47d0 100644 --- a/mysql-test/r/myisam_crash_before_flush_keys.result +++ b/mysql-test/r/myisam_crash_before_flush_keys.result @@ -15,31 +15,13 @@ SET SESSION debug="d,crash_before_flush_keys"; # Run the crashing query FLUSH TABLE t1; ERROR HY000: Lost connection to MySQL server during query -# Run MYISAMCHK tool to check the table t1 and repair -myisamchk: MyISAM file MYSQLD_DATADIR/test/t1 -myisamchk: warning: 1 client is using or hasn't closed the table properly -myisamchk: error: Size of indexfile is: 1024 Should be: 3072 -MYISAMCHK: Unknown error 126 -myisamchk: error: Can't read indexpage from filepos: 1024 -MyISAM-table 'MYSQLD_DATADIR/test/t1' is corrupted -Fix it using switch "-r" or "-o" # Write file to make mysql-test-run.pl start the server # Turn on reconnect # Call script that will poll the server waiting for # it to be back online again -SHOW CREATE TABLE t1; -Table Create Table -t1 CREATE TABLE `t1` ( - `a` int(11) NOT NULL DEFAULT '0', - `b` int(11) NOT NULL DEFAULT '0', - PRIMARY KEY (`a`,`b`), - KEY `b` (`b`) -) ENGINE=MyISAM DEFAULT CHARSET=latin1 DELAY_KEY_WRITE=1 -SELECT * FROM t1 FORCE INDEX (PRIMARY); -a b -1 2 -2 3 -3 4 -4 5 -5 6 +CHECK TABLE t1; +Table Op Msg_type Msg_text +test.t1 check warning 1 client is using or hasn't closed the table properly +test.t1 check error Size of indexfile is: 1024 Should be: 3072 +test.t1 check error Corrupt DROP TABLE t1; diff --git a/mysql-test/r/olap.result b/mysql-test/r/olap.result index 4540c9d5218..a7516d97888 100644 --- a/mysql-test/r/olap.result +++ b/mysql-test/r/olap.result @@ -733,4 +733,24 @@ SELECT 1 FROM t1 GROUP BY (DATE(NULL)) WITH ROLLUP; 1 1 DROP TABLE t1; +# +# Bug #48131: crash group by with rollup, distinct, +# filesort, with temporary tables +# +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; +a b +1 100 +1 NULL +2 100 +2 NULL +NULL NULL +SELECT DISTINCT b FROM t1, t2 GROUP BY a, b WITH ROLLUP; +b +100 +NULL +DROP TABLE t1, t2; End of 5.0 tests diff --git a/mysql-test/r/partition_pruning.result b/mysql-test/r/partition_pruning.result index 769d499fc0a..3128c57b2cf 100644 --- a/mysql-test/r/partition_pruning.result +++ b/mysql-test/r/partition_pruning.result @@ -1272,10 +1272,9 @@ INSERT INTO t1 VALUES (1, '2009-01-01'), (2, NULL); # test with an invalid date, which lead to item->null_value is set. EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < CAST('2009-04-99' AS DATETIME); id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 p20090401 ALL NULL NULL NULL NULL 2 Using where +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables Warnings: Warning 1292 Incorrect datetime value: '2009-04-99' -Warning 1292 Incorrect datetime value: '2009-04-99' DROP TABLE t1; CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT, diff --git a/mysql-test/r/ps_grant.result b/mysql-test/r/ps_grant.result index 8b16123ccea..672db74d9c0 100644 --- a/mysql-test/r/ps_grant.result +++ b/mysql-test/r/ps_grant.result @@ -32,19 +32,19 @@ identified by 'looser' ; show grants for second_user@localhost ; Grants for second_user@localhost GRANT USAGE ON *.* TO 'second_user'@'localhost' IDENTIFIED BY PASSWORD '*13843FE600B19A81E32AF50D4A6FED25875FF1F3' -GRANT SELECT ON `mysqltest`.`t1` TO 'second_user'@'localhost' GRANT SELECT ON `mysqltest`.`t9` TO 'second_user'@'localhost' +GRANT SELECT ON `mysqltest`.`t1` TO 'second_user'@'localhost' drop table mysqltest.t9 ; show grants for second_user@localhost ; Grants for second_user@localhost GRANT USAGE ON *.* TO 'second_user'@'localhost' IDENTIFIED BY PASSWORD '*13843FE600B19A81E32AF50D4A6FED25875FF1F3' -GRANT SELECT ON `mysqltest`.`t1` TO 'second_user'@'localhost' GRANT SELECT ON `mysqltest`.`t9` TO 'second_user'@'localhost' +GRANT SELECT ON `mysqltest`.`t1` TO 'second_user'@'localhost' show grants for second_user@localhost ; Grants for second_user@localhost GRANT USAGE ON *.* TO 'second_user'@'localhost' IDENTIFIED BY PASSWORD '*13843FE600B19A81E32AF50D4A6FED25875FF1F3' -GRANT SELECT ON `mysqltest`.`t1` TO 'second_user'@'localhost' GRANT SELECT ON `mysqltest`.`t9` TO 'second_user'@'localhost' +GRANT SELECT ON `mysqltest`.`t1` TO 'second_user'@'localhost' prepare s_t1 from 'select a as my_col from t1' ; execute s_t1 ; my_col diff --git a/mysql-test/r/query_cache.result b/mysql-test/r/query_cache.result index 6cabc24d0eb..2817bec2198 100644 --- a/mysql-test/r/query_cache.result +++ b/mysql-test/r/query_cache.result @@ -1708,6 +1708,7 @@ Qcache_hits 2 DROP TABLE t1; SET GLOBAL query_cache_size= default; End of 5.0 tests +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())); @@ -1722,4 +1723,5 @@ SELECT 1 FROM t1 GROUP BY 1 1 DROP TABLE t1; +SET GLOBAL query_cache_size= default; End of 5.1 tests diff --git a/mysql-test/r/range.result b/mysql-test/r/range.result index c98a7696ea6..2306f8b501e 100644 --- a/mysql-test/r/range.result +++ b/mysql-test/r/range.result @@ -1398,3 +1398,209 @@ a < 10; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t3 range a a 5 NULL 8 Using where; Using index DROP TABLE t1, t2, t3; +# +# Bug #47123: Endless 100% CPU loop with STRAIGHT_JOIN +# +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); +a +DROP TABLE t1; +# +# Bug#47925: regression of range optimizer and date comparison in 5.1.39! +# +CREATE TABLE t1 ( a DATE, KEY ( a ) ); +CREATE TABLE t2 ( a DATETIME, KEY ( a ) ); +# 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'); +# DATE vs DATE +EXPLAIN +SELECT * FROM t1 WHERE a >= '2009/09/23'; +id select_type table type possible_keys key key_len ref rows Extra +X X X range a a X X X X +SELECT * FROM t1 WHERE a >= '2009/09/23'; +a +2009-09-23 +2009-09-23 +2009-09-23 +SELECT * FROM t1 WHERE a >= '20090923'; +a +2009-09-23 +2009-09-23 +2009-09-23 +SELECT * FROM t1 WHERE a >= 20090923; +a +2009-09-23 +2009-09-23 +2009-09-23 +SELECT * FROM t1 WHERE a >= '2009-9-23'; +a +2009-09-23 +2009-09-23 +2009-09-23 +SELECT * FROM t1 WHERE a >= '2009.09.23'; +a +2009-09-23 +2009-09-23 +2009-09-23 +SELECT * FROM t1 WHERE a >= '2009:09:23'; +a +2009-09-23 +2009-09-23 +2009-09-23 +# DATE vs DATETIME +EXPLAIN +SELECT * FROM t2 WHERE a >= '2009/09/23'; +id select_type table type possible_keys key key_len ref rows Extra +X X X range a a X X X X +SELECT * FROM t2 WHERE a >= '2009/09/23'; +a +2009-09-23 12:00:00 +2009-09-23 12:00:00 +2009-09-23 12:00:00 +SELECT * FROM t2 WHERE a >= '2009/09/23'; +a +2009-09-23 12:00:00 +2009-09-23 12:00:00 +2009-09-23 12:00:00 +SELECT * FROM t2 WHERE a >= '20090923'; +a +2009-09-23 12:00:00 +2009-09-23 12:00:00 +2009-09-23 12:00:00 +SELECT * FROM t2 WHERE a >= 20090923; +a +2009-09-23 12:00:00 +2009-09-23 12:00:00 +2009-09-23 12:00:00 +SELECT * FROM t2 WHERE a >= '2009-9-23'; +a +2009-09-23 12:00:00 +2009-09-23 12:00:00 +2009-09-23 12:00:00 +SELECT * FROM t2 WHERE a >= '2009.09.23'; +a +2009-09-23 12:00:00 +2009-09-23 12:00:00 +2009-09-23 12:00:00 +SELECT * FROM t2 WHERE a >= '2009:09:23'; +a +2009-09-23 12:00:00 +2009-09-23 12:00:00 +2009-09-23 12:00:00 +# DATETIME vs DATETIME +EXPLAIN +SELECT * FROM t2 WHERE a >= '2009/09/23 12:00:00'; +id select_type table type possible_keys key key_len ref rows Extra +X X X range a a X X X X +SELECT * FROM t2 WHERE a >= '2009/09/23 12:00:00'; +a +2009-09-23 12:00:00 +2009-09-23 12:00:00 +2009-09-23 12:00:00 +SELECT * FROM t2 WHERE a >= '20090923120000'; +a +2009-09-23 12:00:00 +2009-09-23 12:00:00 +2009-09-23 12:00:00 +SELECT * FROM t2 WHERE a >= 20090923120000; +a +2009-09-23 12:00:00 +2009-09-23 12:00:00 +2009-09-23 12:00:00 +SELECT * FROM t2 WHERE a >= '2009-9-23 12:00:00'; +a +2009-09-23 12:00:00 +2009-09-23 12:00:00 +2009-09-23 12:00:00 +SELECT * FROM t2 WHERE a >= '2009.09.23 12:00:00'; +a +2009-09-23 12:00:00 +2009-09-23 12:00:00 +2009-09-23 12:00:00 +SELECT * FROM t2 WHERE a >= '2009:09:23 12:00:00'; +a +2009-09-23 12:00:00 +2009-09-23 12:00:00 +2009-09-23 12:00:00 +# DATETIME vs DATE +EXPLAIN +SELECT * FROM t1 WHERE a >= '2009/09/23 00:00:00'; +id select_type table type possible_keys key key_len ref rows Extra +X X X range a a X X X X +SELECT * FROM t1 WHERE a >= '2009/09/23 00:00:00'; +a +2009-09-23 +2009-09-23 +2009-09-23 +SELECT * FROM t1 WHERE a >= '2009/09/23 00:00:00'; +a +2009-09-23 +2009-09-23 +2009-09-23 +SELECT * FROM t1 WHERE a >= '20090923000000'; +a +2009-09-23 +2009-09-23 +2009-09-23 +SELECT * FROM t1 WHERE a >= 20090923000000; +a +2009-09-23 +2009-09-23 +2009-09-23 +SELECT * FROM t1 WHERE a >= '2009-9-23 00:00:00'; +a +2009-09-23 +2009-09-23 +2009-09-23 +SELECT * FROM t1 WHERE a >= '2009.09.23 00:00:00'; +a +2009-09-23 +2009-09-23 +2009-09-23 +SELECT * FROM t1 WHERE a >= '2009:09:23 00:00:00'; +a +2009-09-23 +2009-09-23 +2009-09-23 +# Test of the new get_date_from_str implementation +# Behavior differs slightly between the trunk and mysql-pe. +# The former may give errors for the truncated values, while the latter +# gives warnings. The purpose of this test is not to interfere, and only +# 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'; +str_to_date('2007-10-00', '%Y-%m-%d') >= '' AND +str_to_date('2007-10-00', '%Y-%m-%d') <= '2007/10/20' +1 +Warnings: +Warning 1292 Truncated incorrect date value: '' +SELECT str_to_date('2007-20-00', '%Y-%m-%d') >= '2007/10/20' AND +str_to_date('2007-20-00', '%Y-%m-%d') <= ''; +str_to_date('2007-20-00', '%Y-%m-%d') >= '2007/10/20' AND +str_to_date('2007-20-00', '%Y-%m-%d') <= '' +NULL +Warnings: +Warning 1292 Truncated incorrect date value: '' +Error 1411 Incorrect datetime value: '2007-20-00' for function str_to_date +Error 1411 Incorrect datetime value: '2007-20-00' for function str_to_date +SELECT str_to_date('2007-10-00', '%Y-%m-%d') BETWEEN '' AND '2007/10/20'; +str_to_date('2007-10-00', '%Y-%m-%d') BETWEEN '' AND '2007/10/20' +1 +Warnings: +Warning 1292 Truncated incorrect datetime value: '' +SELECT str_to_date('2007-20-00', '%Y-%m-%d') BETWEEN '2007/10/20' AND ''; +str_to_date('2007-20-00', '%Y-%m-%d') BETWEEN '2007/10/20' AND '' +NULL +Warnings: +Error 1411 Incorrect datetime value: '2007-20-00' for function str_to_date +SELECT str_to_date('', '%Y-%m-%d'); +str_to_date('', '%Y-%m-%d') +0000-00-00 +DROP TABLE t1, t2; +End of 5.1 tests diff --git a/mysql-test/r/select.result b/mysql-test/r/select.result index 50b5c3c13fb..1b615233a14 100644 --- a/mysql-test/r/select.result +++ b/mysql-test/r/select.result @@ -4386,6 +4386,47 @@ id select_type table type possible_keys key key_len ref rows filtered Extra Warnings: Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where ((`test`.`t1`.`a` = `test`.`t1`.`b`) and (`test`.`t1`.`a` > 1)) limit 2 DROP TABLE t1; +# +# Bug#47019: Assertion failed: 0, file .\rt_mbr.c, line 138 when +# forcing a spatial index +# +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; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 +1 SIMPLE t2 ALL a NULL NULL NULL 2 +SELECT 1 FROM t1 NATURAL LEFT JOIN t1 AS t2; +1 +1 +1 +1 +1 +EXPLAIN SELECT 1 FROM t1 NATURAL LEFT JOIN t1 AS t2 FORCE INDEX(a); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 +1 SIMPLE t2 ALL a NULL NULL NULL 2 +SELECT 1 FROM t1 NATURAL LEFT JOIN t1 AS t2 FORCE INDEX(a); +1 +1 +1 +1 +1 +DROP TABLE t1; +# +# Bug #48291 : crash with row() operator,select into @var, and +# subquery returning multiple rows +# +CREATE TABLE t1(a INT); +INSERT INTO t1 VALUES (2),(3); +# Should not crash +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; +ERROR 21000: Subquery returns more than 1 row +DROP TABLE t1; End of 5.0 tests create table t1(a INT, KEY (a)); INSERT INTO t1 VALUES (1),(2),(3),(4),(5); diff --git a/mysql-test/r/sp-bugs.result b/mysql-test/r/sp-bugs.result new file mode 100644 index 00000000000..14c5311bbe5 --- /dev/null +++ b/mysql-test/r/sp-bugs.result @@ -0,0 +1,47 @@ +# +# Bug #47412: Valgrind warnings / user can read uninitalized memory +# using SP variables +# +CREATE SCHEMA testdb; +USE testdb; +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| +# should not return valgrind warnings +CALL p3 ( f2 () ); +ERROR 42000: PROCEDURE testdb.p_not_exists does not exist +DROP SCHEMA testdb; +CREATE SCHEMA testdb; +USE testdb; +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| +# should not return valgrind warnings +CALL p3 ( f2 () ); +ERROR 42000: PROCEDURE testdb.p_not_exists does not exist +DROP SCHEMA testdb; +CREATE SCHEMA testdb; +USE testdb; +CREATE FUNCTION f2 () RETURNS INTEGER +BEGIN +DECLARE CONTINUE HANDLER FOR SQLSTATE '42000' SET @aux = 1; +RETURN f_not_exists () ; +END| +# should not return valgrind warnings +SELECT f2 (); +f2 () +NULL +DROP SCHEMA testdb; +End of 5.1 tests diff --git a/mysql-test/r/sp-error.result b/mysql-test/r/sp-error.result index 8a8cbd751a0..ef51f5e37fb 100644 --- a/mysql-test/r/sp-error.result +++ b/mysql-test/r/sp-error.result @@ -1652,3 +1652,19 @@ NULL SELECT non_existent (a) FROM t1 WHERE b = 999999; ERROR 42000: FUNCTION test.non_existent does not exist DROP TABLE t1; +# +# Bug #47788: Crash in TABLE_LIST::hide_view_error on UPDATE + VIEW + +# SP + MERGE + ALTER +# +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; +CALL p1(5); +ERROR HY000: The target table v1 of the UPDATE is not updatable +ALTER TABLE t1 CHANGE COLUMN b b2 INT; +CALL p1(7); +ERROR HY000: View 'test.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them +DROP PROCEDURE p1; +DROP VIEW v1; +DROP TABLE t1; +End of 5.1 tests diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result index c60ac9790c5..665473df3ef 100644 --- a/mysql-test/r/subselect.result +++ b/mysql-test/r/subselect.result @@ -75,7 +75,7 @@ SELECT 1 FROM (SELECT 1 as a) b WHERE 1 IN (SELECT (SELECT a)); select (SELECT 1 FROM (SELECT 1) a PROCEDURE ANALYSE(1)); ERROR HY000: Incorrect usage of PROCEDURE and subquery SELECT 1 FROM (SELECT 1) a PROCEDURE ANALYSE((SELECT 1)); -ERROR HY000: Incorrect usage of PROCEDURE and subquery +ERROR HY000: Incorrect parameters to procedure 'ANALYSE' SELECT (SELECT 1) as a FROM (SELECT 1) b WHERE (SELECT a) IS NULL; ERROR 42S22: Unknown column 'a' in 'field list' SELECT (SELECT 1) as a FROM (SELECT 1) b WHERE (SELECT a) IS NOT NULL; @@ -4403,8 +4403,7 @@ FROM t1 WHERE a = 230; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -2 DEPENDENT SUBQUERY st1 index NULL a 5 NULL 2 Using index -2 DEPENDENT SUBQUERY st2 index b b 5 NULL 2 Using where; Using index; Using join buffer +2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables SELECT MAX(b), (SELECT COUNT(*) FROM st1,st2 WHERE st2.b <= t1.b) FROM t1 WHERE a = 230; @@ -4564,4 +4563,18 @@ id g v s 51 50 NULL l 61 60 NULL l drop table t1, t2; +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())); +1 +1 +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); +1 +1 +DROP TABLE t1; End of 5.1 tests. diff --git a/mysql-test/r/subselect3.result b/mysql-test/r/subselect3.result index f055b40116a..d5fb1a7bbde 100644 --- a/mysql-test/r/subselect3.result +++ b/mysql-test/r/subselect3.result @@ -895,3 +895,72 @@ t1.a < (select t4.a+10 from t4, t5 limit 2)); ERROR 21000: Subquery returns more than 1 row drop table t0, t1, t2, t3, t4, t5; +# +# BUG#48177 - SELECTs with NOT IN subqueries containing NULL +# values return too many records +# +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); + +Data in t1 +SELECT i1, i2 FROM t1; +i1 i2 +1 NULL +2 3 +4 NULL +4 0 +NULL NULL + +Data in subquery (should be filtered out) +SELECT i1, i2 FROM t2 ORDER BY i1; +i1 i2 +4 NULL +5 0 +FLUSH STATUS; + +SELECT i1, i2 +FROM t1 +WHERE (i1, i2) +NOT IN (SELECT i1, i2 FROM t2); +i1 i2 +1 NULL +2 3 + +# Check that the subquery only has to be evaluated once +# for all-NULL values even though there are two (NULL,NULL) records +# Baseline: +SHOW STATUS LIKE '%Handler_read_rnd_next'; +Variable_name Value +Handler_read_rnd_next 17 + +INSERT INTO t1 VALUES (NULL, NULL); +FLUSH STATUS; + +SELECT i1, i2 +FROM t1 +WHERE (i1, i2) +NOT IN (SELECT i1, i2 FROM t2); +i1 i2 +1 NULL +2 3 + +# Handler_read_rnd_next should be one more than baseline +# (read record from t1, but do not read from t2) +SHOW STATUS LIKE '%Handler_read_rnd_next'; +Variable_name Value +Handler_read_rnd_next 18 +DROP TABLE t1,t2; +End of 5.1 tests diff --git a/mysql-test/r/system_mysql_db.result b/mysql-test/r/system_mysql_db.result index e252331cd1a..17fd95ab1c8 100644 --- a/mysql-test/r/system_mysql_db.result +++ b/mysql-test/r/system_mysql_db.result @@ -161,7 +161,7 @@ procs_priv CREATE TABLE `procs_priv` ( `Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '', `Db` char(64) COLLATE utf8_bin NOT NULL DEFAULT '', `User` char(16) COLLATE utf8_bin NOT NULL DEFAULT '', - `Routine_name` char(64) COLLATE utf8_bin NOT NULL DEFAULT '', + `Routine_name` char(64) CHARACTER SET utf8 NOT NULL DEFAULT '', `Routine_type` enum('FUNCTION','PROCEDURE') COLLATE utf8_bin NOT NULL, `Grantor` char(77) COLLATE utf8_bin NOT NULL DEFAULT '', `Proc_priv` set('Execute','Alter Routine','Grant') CHARACTER SET utf8 NOT NULL DEFAULT '', diff --git a/mysql-test/r/type_newdecimal.result b/mysql-test/r/type_newdecimal.result index c3d1e400b23..748aadee4fb 100644 --- a/mysql-test/r/type_newdecimal.result +++ b/mysql-test/r/type_newdecimal.result @@ -1495,9 +1495,9 @@ CREATE TABLE t1 (a int DEFAULT NULL, b int DEFAULT NULL); INSERT INTO t1 VALUES (3,30), (1,10), (2,10); SELECT a+CAST(1 AS decimal(65,30)) AS aa, SUM(b) FROM t1 GROUP BY aa; aa SUM(b) -2.00000000000000000000000000000 10 -3.00000000000000000000000000000 10 -4.00000000000000000000000000000 30 +2.000000000000000000000000000000 10 +3.000000000000000000000000000000 10 +4.000000000000000000000000000000 30 SELECT a+CAST(1 AS decimal(65,31)) AS aa, SUM(b) FROM t1 GROUP BY aa; ERROR 42000: Too big scale 31 specified for column '1'. Maximum is 30. DROP TABLE t1; @@ -1521,13 +1521,13 @@ f1 DROP TABLE t1; CREATE TABLE t1 SELECT 123451234512345123451234512345123451234512345.678906789067890678906789067890678906789067890 AS f1; Warnings: -Note 1265 Data truncated for column 'f1' at row 1 +Warning 1264 Out of range value for column 'f1' at row 1 DESC t1; Field Type Null Key Default Extra -f1 decimal(65,20) NO 0.00000000000000000000 +f1 decimal(65,30) NO 0.000000000000000000000000000000 SELECT f1 FROM t1; f1 -123451234512345123451234512345123451234512345.67890678906789067891 +99999999999999999999999999999999999.999999999999999999999999999999 DROP TABLE t1; select (1.20396873 * 0.89550000 * 0.68000000 * 1.08721696 * 0.99500000 * 1.01500000 * 1.01500000 * 0.99500000); @@ -1595,7 +1595,7 @@ Warnings: Note 1265 Data truncated for column 'my_col' at row 1 DESCRIBE t1; Field Type Null Key Default Extra -my_col decimal(32,30) NO 0.000000000000000000000000000000 +my_col decimal(65,30) NO 0.000000000000000000000000000000 SELECT my_col FROM t1; my_col 1.123456789123456789123456789123 @@ -1625,212 +1625,8 @@ Warnings: Note 1265 Data truncated for column 'my_col' at row 1 DESCRIBE t1; Field Type Null Key Default Extra -my_col decimal(30,30) YES NULL +my_col decimal(65,30) YES NULL SELECT my_col FROM t1; my_col 0.012345687012345687012345687012 DROP TABLE t1; -# -# Bug#45261: Crash, stored procedure + decimal -# -DROP TABLE IF EXISTS t1; -CREATE TABLE t1 SELECT -/* 81 */ 100000000000000000000000000000000000000000000000000000000000000000000000000000001 -AS c1; -Warnings: -Warning 1264 Out of range value for column 'c1' at row 1 -DESC t1; -Field Type Null Key Default Extra -c1 decimal(65,0) NO 0 -SELECT * FROM t1; -c1 -99999999999999999999999999999999999999999999999999999999999999999 -DROP TABLE t1; -CREATE TABLE t1 SELECT -/* 81 */ 100000000000000000000000000000000000000000000000000000000000000000000000000000001. -AS c1; -Warnings: -Warning 1264 Out of range value for column 'c1' at row 1 -DESC t1; -Field Type Null Key Default Extra -c1 decimal(65,0) NO 0 -SELECT * FROM t1; -c1 -99999999999999999999999999999999999999999999999999999999999999999 -DROP TABLE t1; -CREATE TABLE t1 SELECT -/* 81 */ 100000000000000000000000000000000000000000000000000000000000000000000000000000001.1 /* 1 */ -AS c1; -Warnings: -Warning 1264 Out of range value for column 'c1' at row 1 -DESC t1; -Field Type Null Key Default Extra -c1 decimal(65,0) NO 0 -SELECT * FROM t1; -c1 -99999999999999999999999999999999999999999999999999999999999999999 -DROP TABLE t1; -CREATE TABLE t1 SELECT -/* 82 */ 1000000000000000000000000000000000000000000000000000000000000000000000000000000001 -AS c1; -Warnings: -Error 1292 Truncated incorrect DECIMAL value: '' -DESC t1; -Field Type Null Key Default Extra -c1 decimal(65,0) NO 0 -SELECT * FROM t1; -c1 -99999999999999999999999999999999999999999999999999999999999999999 -DROP TABLE t1; -CREATE TABLE t1 SELECT -/* 40 */ 1000000000000000000000000000000000000001.1000000000000000000000000000000000000001 /* 40 */ -AS c1; -DESC t1; -Field Type Null Key Default Extra -c1 decimal(65,25) NO 0.0000000000000000000000000 -SELECT * FROM t1; -c1 -1000000000000000000000000000000000000001.1000000000000000000000000 -DROP TABLE t1; -CREATE TABLE t1 SELECT -/* 1 */ 1.10000000000000000000000000000000000000000000000000000000000000000000000000000001 /* 80 */ -AS c1; -DESC t1; -Field Type Null Key Default Extra -c1 decimal(31,30) NO 0.000000000000000000000000000000 -SELECT * FROM t1; -c1 -1.100000000000000000000000000000 -DROP TABLE t1; -CREATE TABLE t1 SELECT -/* 1 */ 1.100000000000000000000000000000000000000000000000000000000000000000000000000000001 /* 81 */ -AS c1; -DESC t1; -Field Type Null Key Default Extra -c1 decimal(31,30) NO 0.000000000000000000000000000000 -SELECT * FROM t1; -c1 -1.100000000000000000000000000000 -DROP TABLE t1; -CREATE TABLE t1 SELECT -.100000000000000000000000000000000000000000000000000000000000000000000000000000001 /* 81 */ -AS c1; -Warnings: -Note 1265 Data truncated for column 'c1' at row 1 -DESC t1; -Field Type Null Key Default Extra -c1 decimal(30,30) NO 0.000000000000000000000000000000 -SELECT * FROM t1; -c1 -0.100000000000000000000000000000 -DROP TABLE t1; -CREATE TABLE t1 SELECT -/* 45 */ 123456789012345678901234567890123456789012345.123456789012345678901234567890123456789012345 /* 45 */ -AS c1; -Warnings: -Note 1265 Data truncated for column 'c1' at row 1 -DESC t1; -Field Type Null Key Default Extra -c1 decimal(65,20) NO 0.00000000000000000000 -SELECT * FROM t1; -c1 -123456789012345678901234567890123456789012345.12345678901234567890 -DROP TABLE t1; -CREATE TABLE t1 SELECT -/* 65 */ 12345678901234567890123456789012345678901234567890123456789012345.1 /* 1 */ -AS c1; -Warnings: -Note 1265 Data truncated for column 'c1' at row 1 -DESC t1; -Field Type Null Key Default Extra -c1 decimal(65,0) NO 0 -SELECT * FROM t1; -c1 -12345678901234567890123456789012345678901234567890123456789012345 -DROP TABLE t1; -CREATE TABLE t1 SELECT -/* 66 */ 123456789012345678901234567890123456789012345678901234567890123456.1 /* 1 */ -AS c1; -Warnings: -Warning 1264 Out of range value for column 'c1' at row 1 -DESC t1; -Field Type Null Key Default Extra -c1 decimal(65,0) NO 0 -SELECT * FROM t1; -c1 -99999999999999999999999999999999999999999999999999999999999999999 -DROP TABLE t1; -CREATE TABLE t1 SELECT -.123456789012345678901234567890123456789012345678901234567890123456 /* 66 */ -AS c1; -Warnings: -Note 1265 Data truncated for column 'c1' at row 1 -DESC t1; -Field Type Null Key Default Extra -c1 decimal(30,30) NO 0.000000000000000000000000000000 -SELECT * FROM t1; -c1 -0.123456789012345678901234567890 -DROP TABLE t1; -CREATE TABLE t1 AS SELECT 123.1234567890123456789012345678901 /* 31 */ AS c1; -Warnings: -Note 1265 Data truncated for column 'c1' at row 1 -DESC t1; -Field Type Null Key Default Extra -c1 decimal(33,30) NO 0.000000000000000000000000000000 -SELECT * FROM t1; -c1 -123.123456789012345678901234567890 -DROP TABLE t1; -CREATE TABLE t1 SELECT 1.1 + CAST(1 AS DECIMAL(65,30)) AS c1; -DESC t1; -Field Type Null Key Default Extra -c1 decimal(65,29) NO 0.00000000000000000000000000000 -SELECT * FROM t1; -c1 -2.10000000000000000000000000000 -DROP TABLE t1; -# -# Test that the integer and decimal parts are properly calculated. -# -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; -Warnings: -Note 1265 Data truncated for column 'c1' at row 3 -DESC t2; -Field Type Null Key Default Extra -c1 decimal(32,30) YES NULL -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; -Warnings: -Note 1265 Data truncated for column 'c1' at row 1 -Note 1265 Data truncated for column 'c1' at row 2 -Note 1265 Data truncated for column 'c1' at row 3 -DESC t2; -Field Type Null Key Default Extra -c1 decimal(32,30) YES NULL -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; -Warnings: -Note 1265 Data truncated for column 'c1' at row 1 -DESC t2; -Field Type Null Key Default Extra -c1 decimal(31,30) YES NULL -DROP TABLE t1,t2; -# -# Test that variables get maximum precision. -# -SET @decimal= 1.1; -CREATE TABLE t1 SELECT @decimal AS c1; -DESC t1; -Field Type Null Key Default Extra -c1 decimal(65,30) YES NULL -SELECT * FROM t1; -c1 -1.100000000000000000000000000000 -DROP TABLE t1; diff --git a/mysql-test/r/update.result b/mysql-test/r/update.result index 7a51649fac5..d859579e835 100644 --- a/mysql-test/r/update.result +++ b/mysql-test/r/update.result @@ -503,3 +503,14 @@ ERROR HY000: Recursive stored functions and triggers are not allowed. DROP TABLE t1; DROP FUNCTION f1; End of 5.0 tests +# +# Bug #47919 assert in open_table during ALTER temporary table +# +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/r/view_grant.result b/mysql-test/r/view_grant.result index a9d4164cda4..83cacc315d8 100644 --- a/mysql-test/r/view_grant.result +++ b/mysql-test/r/view_grant.result @@ -1218,3 +1218,22 @@ Warnings: Note 1449 The user specified as a definer ('no_such_user'@'no_such_host') does not exist DROP TABLE t1; DROP VIEW v1; +# +# Bug #46019: ERROR 1356 When selecting from within another +# view that has Group By +# +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; +SELECT a FROM v1; +a +SELECT a FROM v2; +a +DROP USER mysqluser1; +DROP DATABASE mysqltest1; diff --git a/mysql-test/r/xa.result b/mysql-test/r/xa.result index a597806d897..d23c8e672b0 100644 --- a/mysql-test/r/xa.result +++ b/mysql-test/r/xa.result @@ -89,3 +89,28 @@ xa start 'a'; xa end 'a'; xa prepare 'a'; xa commit 'a'; +CREATE TABLE t1(a INT, KEY(a)) ENGINE=InnoDB; +INSERT INTO t1 VALUES(1),(2); +BEGIN; +UPDATE t1 SET a=3 WHERE a=1; +BEGIN; +UPDATE t1 SET a=4 WHERE a=2; +UPDATE t1 SET a=5 WHERE a=2; +UPDATE t1 SET a=5 WHERE a=1; +ERROR 40001: Deadlock found when trying to get lock; try restarting transaction +ROLLBACK; +ROLLBACK; +BEGIN; +UPDATE t1 SET a=3 WHERE a=1; +XA START 'xid1'; +UPDATE t1 SET a=4 WHERE a=2; +UPDATE t1 SET a=5 WHERE a=2; +UPDATE t1 SET a=5 WHERE a=1; +ERROR 40001: Deadlock found when trying to get lock; try restarting transaction +XA END 'xid1'; +ERROR XA102: XA_RBDEADLOCK: Transaction branch was rolled back: deadlock was detected +XA ROLLBACK 'xid1'; +XA START 'xid1'; +XA END 'xid1'; +XA ROLLBACK 'xid1'; +DROP TABLE t1; |