diff options
Diffstat (limited to 'mysql-test')
59 files changed, 1785 insertions, 175 deletions
diff --git a/mysql-test/collections/default.experimental b/mysql-test/collections/default.experimental index f84337660ea..91dada3ee05 100644 --- a/mysql-test/collections/default.experimental +++ b/mysql-test/collections/default.experimental @@ -12,9 +12,10 @@ funcs_1.ndb* # joro : NDB tests marked as experiment funcs_2.ndb_charset # joro : NDB tests marked as experimental as agreed with bochklin +innodb_plugin.* @solaris # Bug#56063 InnoDB Plugin mysql-tests fail on Solaris + main.ctype_gbk_binlog @solaris # Bug#46010: main.ctype_gbk_binlog fails sporadically : Table 't2' already exists main.func_str @solaris # joro: Bug#40928 -main.plugin_load @solaris # Bug#42144 main.sp @solaris # joro : Bug#54138 main.outfile_loaddata @solaris # joro : Bug #46895 @@ -44,3 +45,6 @@ parts.partition_syntax_ndb # joro : NDB tests marked as experiment parts.partition_value_ndb # joro : NDB tests marked as experimental as agreed with bochklin main.mysqlhotcopy_myisam # horst: due to bug#54129 main.mysqlhotcopy_archive # horst: due to bug#54129 +main.gis-rtree # svoj: due to BUG#38965 +main.type_float # svoj: due to BUG#38965 +main.type_newdecimal # svoj: due to BUG#38965 diff --git a/mysql-test/extra/rpl_tests/rpl_loaddata.test b/mysql-test/extra/rpl_tests/rpl_loaddata.test index 84419ec9cc5..c4fa9f16dc7 100644 --- a/mysql-test/extra/rpl_tests/rpl_loaddata.test +++ b/mysql-test/extra/rpl_tests/rpl_loaddata.test @@ -1,5 +1,5 @@ # Requires statement logging --- source include/have_binlog_format_mixed_or_statement.inc +-- source include/have_binlog_format_statement.inc # See if replication of a "LOAD DATA in an autoincrement column" # Honours autoincrement values diff --git a/mysql-test/extra/rpl_tests/rpl_stm_create_if_not_exists.test b/mysql-test/extra/rpl_tests/rpl_stm_create_if_not_exists.test new file mode 100644 index 00000000000..65d2483dc4b --- /dev/null +++ b/mysql-test/extra/rpl_tests/rpl_stm_create_if_not_exists.test @@ -0,0 +1,235 @@ +--echo +--echo +connection master; + +if ($is_temporary) +{ + --let $_temporary=TEMPORARY +} + +CREATE TABLE t2(c1 INT, c2 char(10)); +INSERT INTO t2 VALUES(1, 'abc'), (2, 'abc'); + +--echo +--echo # The original query should be binlogged if the table does not exist. +--echo # ------------------------------------------------------------------ +--echo +let binlog_start= query_get_value(SHOW MASTER STATUS, Position, 1); +eval CREATE $_temporary TABLE IF NOT EXISTS t1 (c1 INT , c2 INT, c3 char(10), c4 INT KEY) + SELECT 'abc' AS c3, 1 AS c4; +source include/show_binlog_events.inc; + +if (!$is_temporary) +{ + let $diff_table= test.t1; + source include/rpl_diff_tables.inc; +} + +--echo +--echo # The statement should be binlogged as two events. one is +--echo # 'CREATE $_temporary TABLE IF NOT EXISTS ..', another one is +--echo # 'INSERT ... SELECT'. +--echo # ------------------------------------------------------------------ +--echo +let binlog_start= query_get_value(SHOW MASTER STATUS, Position, 1); +eval CREATE $_temporary TABLE IF NOT EXISTS t1 + SELECT 'abc', 2; +source include/show_binlog_events.inc; + +if (!$is_temporary) +{ + let $diff_table= test.t1; + source include/rpl_diff_tables.inc; +} + +--echo +--echo # Verify if it can be binlogged with right database name when the table +--echo # is not in the default database +--echo +--disable_warnings +DROP DATABASE IF EXISTS db1; +--enable_warnings +CREATE DATABASE db1; +USE db1; + +let binlog_start= query_get_value(SHOW MASTER STATUS, Position, 1); +eval CREATE $_temporary TABLE IF NOT EXISTS test.t1 + SELECT 'abc', 20; +source include/show_binlog_events.inc; + +if (!$is_temporary) +{ + let $diff_table= test.t1; + source include/rpl_diff_tables.inc; +} +USE test; +DROP DATABASE db1; + +--echo +--echo # It should be binlogged as 'REPLACE ... SELECT' +--echo # if the original statement has option REPLACE +--echo +let binlog_start= query_get_value(SHOW MASTER STATUS, Position, 1); +eval CREATE $_temporary TABLE IF NOT EXISTS t1 + REPLACE SELECT '123', 2; +source include/show_binlog_events.inc; + +if (!$is_temporary) +{ + let $diff_table= test.t1; + source include/rpl_diff_tables.inc; +} + +--echo +--echo # It should be binlogged as 'INSERT IGNORE... SELECT' +--echo # if the original statement has option IGNORE +--echo +let binlog_start= query_get_value(SHOW MASTER STATUS, Position, 1); +eval CREATE $_temporary TABLE IF NOT EXISTS t1 + IGNORE SELECT '123', 2; +source include/show_binlog_events.inc; + +if (!$is_temporary) +{ + let $diff_table= test.t1; + source include/rpl_diff_tables.inc; +} + +--echo +--echo # Nothing should be binlogged if error happens and no any row is inserted +--echo +let binlog_start= query_get_value(SHOW MASTER STATUS, Position, 1); +--error ER_DUP_ENTRY +eval CREATE $_temporary TABLE IF NOT EXISTS t1 + SELECT '123', 2; +source include/show_binlog_events.inc; +if (!$is_temporary) +{ + let $diff_table= test.t1; + source include/rpl_diff_tables.inc; +} + +--echo +--echo # Verify it can binlog well when there are some braces('(') +--echo +let binlog_start= query_get_value(SHOW MASTER STATUS, Position, 1); +eval CREATE $_temporary TABLE IF NOT EXISTS t1 + (SELECT '123', 3) UNION (SELECT '123', 4); +eval CREATE $_temporary TABLE IF NOT EXISTS t1 + REPLACE (SELECT 'abc', 3) UNION (SELECT 'abc', 4); +eval CREATE $_temporary TABLE IF NOT EXISTS t1 + IGNORE (SELECT '123', 3) UNION (SELECT '123', 4); +source include/show_binlog_events.inc; + +if (!$is_temporary) +{ + let $diff_table= test.t1; + source include/rpl_diff_tables.inc; +} + +if (!$is_temporary) +{ + --echo + --echo # Throw a warning that table already exists and don't insert anything + --echo + CREATE VIEW t3 AS SELECT * FROM t2; + let binlog_start= query_get_value(SHOW MASTER STATUS, Position, 1); + + CREATE TABLE IF NOT EXISTS t3 + SELECT '123', 2; + source include/show_binlog_events.inc; + DROP VIEW t3; +} + +--echo +--echo # The statement can be binlogged correctly when it is in a SP/EVENT/TRIGGER +--echo + +--disable_warnings +DROP PROCEDURE IF EXISTS p1; +--enable_warnings +eval CREATE PROCEDURE p1(IN a INT) + CREATE $_temporary TABLE IF NOT EXISTS t1 SELECT '123', a; + +let binlog_start= query_get_value(SHOW MASTER STATUS, Position, 1); +call p1(500); +call p1(600); +source include/show_binlog_events.inc; +if (!$is_temporary) +{ + let $diff_table= test.t1; + source include/rpl_diff_tables.inc; +} +DROP PROCEDURE p1; + +--echo +--echo # The statement can be binlogged correctly when it is in a prepared statement +--echo +eval PREPARE stm FROM "CREATE $_temporary TABLE IF NOT EXISTS t1 SELECT '123', ?"; + +let binlog_start= query_get_value(SHOW MASTER STATUS, Position, 1); +SET @a= 700; +EXECUTE stm USING @a; +SET @a= 800; +EXECUTE stm USING @a; +source include/show_binlog_events.inc; +if (!$is_temporary) +{ + let $diff_table= test.t1; + source include/rpl_diff_tables.inc; +} + +--echo +--echo # The statement can be binlogged correctly when it is in a conditional comment +--echo +let binlog_start= query_get_value(SHOW MASTER STATUS, Position, 1); + +--echo # The whole statement in a conditional comment +eval /*!CREATE $_temporary TABLE IF NOT EXISTS t1 + SELECT 'abc', 900*/; +source include/show_binlog_events.inc; +let binlog_start= query_get_value(SHOW MASTER STATUS, Position, 1); + +--echo +--echo # There is an long comment before SELECT +eval /*!CREATE $_temporary /*blabla*/ TABLE IF NOT EXISTS t1 + SELECT 'abc', 901*/; +source include/show_binlog_events.inc; +let binlog_start= query_get_value(SHOW MASTER STATUS, Position, 1); + +--echo +--echo # Conditional comment starts just from SELECT +eval CREATE $_temporary TABLE IF NOT EXISTS t1 + /*!SELECT 'abc',*/ 902; +source include/show_binlog_events.inc; +let binlog_start= query_get_value(SHOW MASTER STATUS, Position, 1); + +--echo +--echo # Only SELECT keyword is in the conditional comment +eval CREATE $_temporary TABLE IF NOT EXISTS t1 + /*!SELECT*/ /*!'abc',*/ 904; +source include/show_binlog_events.inc; +let binlog_start= query_get_value(SHOW MASTER STATUS, Position, 1); + +--echo +--echo # Conditional comment is after SELECT keyword +eval CREATE $_temporary TABLE IF NOT EXISTS t1 + SELECT /*!'abc',*/ 903; +source include/show_binlog_events.inc; +let binlog_start= query_get_value(SHOW MASTER STATUS, Position, 1); + +--echo +--echo # Conditional comment ends just before SELECT keyword +eval /*!CREATE $_temporary TABLE IF NOT EXISTS t1 + */SELECT 'abc', 905; +source include/show_binlog_events.inc; + +if (!$is_temporary) +{ + let $diff_table= test.t1; + source include/rpl_diff_tables.inc; +} + +DROP TABLE t2; +eval DROP $_temporary TABLE t1; + diff --git a/mysql-test/include/not_blackhole.inc b/mysql-test/include/not_blackhole.inc new file mode 100644 index 00000000000..078927ec4ca --- /dev/null +++ b/mysql-test/include/not_blackhole.inc @@ -0,0 +1,5 @@ +if (`SELECT count(*) FROM information_schema.engines WHERE + (support = 'YES' OR support = 'DEFAULT') AND + engine = 'blackhole'`){ + skip Blackhole engine enabled; +} diff --git a/mysql-test/include/rpl_diff_tables.inc b/mysql-test/include/rpl_diff_tables.inc index c3a45578a79..7fc68422c40 100644 --- a/mysql-test/include/rpl_diff_tables.inc +++ b/mysql-test/include/rpl_diff_tables.inc @@ -33,3 +33,4 @@ while (`SELECT "XX$_servers" <> "XX"`) --source include/diff_tables.inc connection $_slave; } +connection $_master; diff --git a/mysql-test/r/delete.result b/mysql-test/r/delete.result index 36025cbfb35..7d6fc30be67 100644 --- a/mysql-test/r/delete.result +++ b/mysql-test/r/delete.result @@ -358,4 +358,13 @@ INDEX(a), INDEX(b), INDEX(c)); INSERT INTO t1 VALUES (1,2,3), (4,5,6), (7,8,9); DELETE FROM t1 WHERE a = 10 OR b = 20 ORDER BY c LIMIT 1; DROP TABLE t1; +# +# Bug #53034: Multiple-table DELETE statements not accepting +# "Access compatibility" syntax +# +CREATE TABLE t1 (id INT); +CREATE TABLE t2 LIKE t1; +CREATE TABLE t3 LIKE t1; +DELETE FROM t1.*, test.t2.*, a.* USING t1, t2, t3 AS a; +DROP TABLE t1, t2, t3; End of 5.1 tests diff --git a/mysql-test/r/func_group.result b/mysql-test/r/func_group.result index b36f561578b..606f879b47f 100644 --- a/mysql-test/r/func_group.result +++ b/mysql-test/r/func_group.result @@ -1713,4 +1713,15 @@ f1 f2 f3 f4 f1 = f2 NULL NULL NULL NULL NULL drop table t1; # +# Bug #54465: assert: field_types == 0 || field_types[field_pos] == +# MYSQL_TYPE_LONGLONG +# +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1), (2); +SELECT MAX((SELECT 1 FROM t1 ORDER BY @var LIMIT 1)) m FROM t1 t2, t1 +ORDER BY t1.a; +m +1 +DROP TABLE t1; +# End of 5.1 tests diff --git a/mysql-test/r/func_sapdb.result b/mysql-test/r/func_sapdb.result index bbc5390895b..87b88692a34 100644 --- a/mysql-test/r/func_sapdb.result +++ b/mysql-test/r/func_sapdb.result @@ -194,7 +194,7 @@ date("1997-12-31 23:59:59.000001") as f8, time("1997-12-31 23:59:59.000001") as f9; describe t1; Field Type Null Key Default Extra -f1 date NO 0000-00-00 +f1 date YES NULL f2 datetime YES NULL f3 time YES NULL f4 time YES NULL diff --git a/mysql-test/r/func_time.result b/mysql-test/r/func_time.result index 93317020c5b..4ff4cfa586b 100644 --- a/mysql-test/r/func_time.result +++ b/mysql-test/r/func_time.result @@ -1335,4 +1335,12 @@ date_sub("0069-01-01 00:00:01",INTERVAL 2 SECOND) select date_sub("0169-01-01 00:00:01",INTERVAL 2 SECOND); date_sub("0169-01-01 00:00:01",INTERVAL 2 SECOND) 0168-12-31 23:59:59 +CREATE TABLE t1(a DOUBLE NOT NULL); +INSERT INTO t1 VALUES (0),(9.216e-096); +# should not crash +SELECT 1 FROM t1 ORDER BY @x:=makedate(a,a); +1 +1 +1 +DROP TABLE t1; End of 5.1 tests diff --git a/mysql-test/r/gis.result b/mysql-test/r/gis.result index 3e28227d542..3b18ee61336 100644 --- a/mysql-test/r/gis.result +++ b/mysql-test/r/gis.result @@ -707,10 +707,7 @@ numgeometries(b) IS NULL, numinteriorrings(b) IS NULL, numpoints(b) IS NULL, area(b) IS NULL, glength(b) IS NULL, srid(b) IS NULL, x(b) IS NULL, y(b) IS NULL from t1; -geometryfromtext(b) IS NULL geometryfromwkb(b) IS NULL astext(b) IS NULL aswkb(b) IS NULL geometrytype(b) IS NULL centroid(b) IS NULL envelope(b) IS NULL startpoint(b) IS NULL endpoint(b) IS NULL exteriorring(b) IS NULL pointn(b, 1) IS NULL geometryn(b, 1) IS NULL interiorringn(b, 1) IS NULL multipoint(b) IS NULL isempty(b) IS NULL issimple(b) IS NULL isclosed(b) IS NULL dimension(b) IS NULL numgeometries(b) IS NULL numinteriorrings(b) IS NULL numpoints(b) IS NULL area(b) IS NULL glength(b) IS NULL srid(b) IS NULL x(b) IS NULL y(b) IS NULL -1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 -1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 -1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 +ERROR 22007: Illegal non geometric '`test`.`t1`.`b`' value found during parsing select within(b, b) IS NULL, contains(b, b) IS NULL, overlaps(b, b) IS NULL, equals(b, b) IS NULL, disjoint(b, b) IS NULL, touches(b, b) IS NULL, @@ -725,10 +722,7 @@ point(b, b) IS NULL, linestring(b) IS NULL, polygon(b) IS NULL, multipoint(b) IS multilinestring(b) IS NULL, multipolygon(b) IS NULL, geometrycollection(b) IS NULL from t1; -point(b, b) IS NULL linestring(b) IS NULL polygon(b) IS NULL multipoint(b) IS NULL multilinestring(b) IS NULL multipolygon(b) IS NULL geometrycollection(b) IS NULL -0 1 1 1 1 1 1 -1 1 1 1 1 1 1 -0 1 1 1 1 1 1 +ERROR 22007: Illegal non geometric '`test`.`t1`.`b`' value found during parsing drop table t1; CREATE TABLE t1(a POINT) ENGINE=MyISAM; INSERT INTO t1 VALUES (NULL); @@ -1010,51 +1004,14 @@ f5 datetime YES NULL drop view v1; drop table t1; SELECT MultiPoint(12345,''); -MultiPoint(12345,'') -NULL -SELECT MultiPoint(123451,''); -MultiPoint(123451,'') -NULL -SELECT MultiPoint(1234512,''); -MultiPoint(1234512,'') -NULL -SELECT MultiPoint(12345123,''); -MultiPoint(12345123,'') -NULL -SELECT MultiLineString(12345,''); -MultiLineString(12345,'') -NULL -SELECT MultiLineString(123451,''); -MultiLineString(123451,'') -NULL -SELECT MultiLineString(1234512,''); -MultiLineString(1234512,'') -NULL -SELECT MultiLineString(12345123,''); -MultiLineString(12345123,'') -NULL -SELECT LineString(12345,''); -LineString(12345,'') -NULL -SELECT LineString(123451,''); -LineString(123451,'') -NULL -SELECT LineString(1234512,''); -LineString(1234512,'') -NULL -SELECT LineString(12345123,''); -LineString(12345123,'') -NULL -SELECT Polygon(12345,''); -Polygon(12345,'') -NULL -SELECT Polygon(123451,''); -Polygon(123451,'') -NULL -SELECT Polygon(1234512,''); -Polygon(1234512,'') -NULL -SELECT Polygon(12345123,''); -Polygon(12345123,'') -NULL +ERROR 22007: Illegal non geometric '12345' value found during parsing +SELECT 1 FROM (SELECT GREATEST(1,GEOMETRYCOLLECTION('00000','00000')) b FROM DUAL) AS d WHERE (LINESTRING(d.b)); +ERROR 22007: Illegal non geometric ''00000'' value found during parsing +# +# BUG#51875: crash when loading data into geometry function polyfromwkb +# +SET @a=0x00000000030000000100000000000000000000000000144000000000000014400000000000001840000000000000184000000000000014400000000000001440; +SET @a=POLYFROMWKB(@a); +SET @a=0x00000000030000000000000000000000000000000000144000000000000014400000000000001840000000000000184000000000000014400000000000001440; +SET @a=POLYFROMWKB(@a); End of 5.1 tests diff --git a/mysql-test/r/insert.result b/mysql-test/r/insert.result index 3f91039d592..589fb8b0a48 100644 --- a/mysql-test/r/insert.result +++ b/mysql-test/r/insert.result @@ -639,3 +639,18 @@ CREATE TABLE t2(f1 CHAR(1)); INSERT INTO t2 SELECT f1 FROM t1; DROP TABLE t1, t2; End of 5.0 tests. +# +# Bug#54106 assert in Protocol::end_statement, +# INSERT IGNORE ... SELECT ... UNION SELECT ... +# +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 (a INT); +INSERT INTO t1 (a, a) VALUES (1, 1); +ERROR 42000: Column 'a' specified twice +INSERT IGNORE t1 (a, a) VALUES (1, 1); +ERROR 42000: Column 'a' specified twice +INSERT IGNORE t1 (a, a) SELECT 1,1; +ERROR 42000: Column 'a' specified twice +INSERT IGNORE t1 (a, a) SELECT 1,1 UNION SELECT 2,2; +ERROR 42000: Column 'a' specified twice +DROP TABLE t1; diff --git a/mysql-test/r/join.result b/mysql-test/r/join.result index c60c6bfb3c8..96504df4b1e 100644 --- a/mysql-test/r/join.result +++ b/mysql-test/r/join.result @@ -1184,4 +1184,40 @@ NULL NULL 1 DROP TABLE t1, t2, mm1; +# +# Bug #54468: crash after item's print() function when ordering/grouping +# by subquery +# +CREATE TABLE t1(a INT, b INT); +INSERT INTO t1 VALUES (), (); +SELECT 1 FROM t1 +GROUP BY +GREATEST(t1.a, +(SELECT 1 FROM +(SELECT t1.b FROM t1,t1 t2 +ORDER BY t1.a, t1.a LIMIT 1) AS d) +); +1 +1 +DROP TABLE t1; +# +# Bug #53544: Server hangs during JOIN query in stored procedure called +# twice in a row +# +CREATE TABLE t1(c INT); +INSERT INTO t1 VALUES (1), (2); +PREPARE stmt FROM "SELECT t2.c AS f1 FROM t1 LEFT JOIN + t1 t2 ON t1.c=t2.c RIGHT JOIN + t1 t3 ON t1.c=t3.c + GROUP BY f1;"; +EXECUTE stmt; +f1 +1 +2 +EXECUTE stmt; +f1 +1 +2 +DEALLOCATE PREPARE stmt; +DROP TABLE t1; End of 5.1 tests diff --git a/mysql-test/r/partition_not_blackhole.result b/mysql-test/r/partition_not_blackhole.result new file mode 100644 index 00000000000..dc0339f8c48 --- /dev/null +++ b/mysql-test/r/partition_not_blackhole.result @@ -0,0 +1,16 @@ +DROP TABLE IF EXISTS t1; +# +# Bug#46086: crash when dropping a partitioned table and +# the original engine is disabled +# Copy a .frm and .par file which was created with: +# create table `t1` (`id` int primary key) engine=blackhole +# partition by key () partitions 1; +SHOW TABLES; +Tables_in_test +t1 +SHOW CREATE TABLE t1; +ERROR HY000: Incorrect information in file: './test/t1.frm' +DROP TABLE t1; +ERROR 42S02: Unknown table 't1' +t1.frm +t1.par diff --git a/mysql-test/r/partition_range.result b/mysql-test/r/partition_range.result index 731f2478cc9..7a3ff4861cc 100644 --- a/mysql-test/r/partition_range.result +++ b/mysql-test/r/partition_range.result @@ -808,3 +808,47 @@ select sum(count) from t2 ch where ch.defid in (50,52) and ch.day between 200703 sum(count) 579 drop table t1, t2; +# +# Bug#50939: Loose Index Scan unduly relies on engine to remember range +# endpoints +# +CREATE TABLE t1 ( +a INT, +b INT, +KEY ( a, b ) +) PARTITION BY HASH (a) PARTITIONS 1; +CREATE TABLE t2 ( +a INT, +b INT, +KEY ( a, b ) +); +INSERT INTO t1 VALUES (1, 1), (2, 2), (3, 3), (4, 4), (5, 5); +INSERT INTO t1 SELECT a + 5, b + 5 FROM t1; +INSERT INTO t1 SELECT a + 10, b + 10 FROM t1; +INSERT INTO t1 SELECT a + 20, b + 20 FROM t1; +INSERT INTO t1 SELECT a + 40, b + 40 FROM t1; +INSERT INTO t2 SELECT * FROM t1; +# plans should be identical +EXPLAIN SELECT a, MAX(b) FROM t1 WHERE a IN (10,100) GROUP BY a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range a a 5 NULL 1 Using where; Using index for group-by +EXPLAIN SELECT a, MAX(b) FROM t2 WHERE a IN (10,100) GROUP BY a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 range a a 5 NULL 2 Using where; Using index for group-by +FLUSH status; +SELECT a, MAX(b) FROM t1 WHERE a IN (10, 100) GROUP BY a; +a MAX(b) +10 10 +# Should be no more than 4 reads. +SHOW status LIKE 'handler_read_key'; +Variable_name Value +Handler_read_key 4 +FLUSH status; +SELECT a, MAX(b) FROM t2 WHERE a IN (10, 100) GROUP BY a; +a MAX(b) +10 10 +# Should be no more than 4 reads. +SHOW status LIKE 'handler_read_key'; +Variable_name Value +Handler_read_key 4 +DROP TABLE t1, t2; diff --git a/mysql-test/r/range.result b/mysql-test/r/range.result index ad8aec7383a..d989896514c 100644 --- a/mysql-test/r/range.result +++ b/mysql-test/r/range.result @@ -1654,47 +1654,16 @@ a b 1 1 DROP TABLE t1; # -# Bug#50939: Loose Index Scan unduly relies on engine to remember range -# endpoints +# Bug #54802: 'NOT BETWEEN' evaluation is incorrect # -CREATE TABLE t1 ( -a INT, -b INT, -KEY ( a, b ) -) PARTITION BY HASH (a) PARTITIONS 1; -CREATE TABLE t2 ( -a INT, -b INT, -KEY ( a, b ) -); -INSERT INTO t1 VALUES (1, 1), (2, 2), (3, 3), (4, 4), (5, 5); -INSERT INTO t1 SELECT a + 5, b + 5 FROM t1; -INSERT INTO t1 SELECT a + 10, b + 10 FROM t1; -INSERT INTO t1 SELECT a + 20, b + 20 FROM t1; -INSERT INTO t1 SELECT a + 40, b + 40 FROM t1; -INSERT INTO t2 SELECT * FROM t1; -# plans should be identical -EXPLAIN SELECT a, MAX(b) FROM t1 WHERE a IN (10,100) GROUP BY a; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range a a 5 NULL 1 Using where; Using index for group-by -EXPLAIN SELECT a, MAX(b) FROM t2 WHERE a IN (10,100) GROUP BY a; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 range a a 5 NULL 2 Using where; Using index for group-by -FLUSH status; -SELECT a, MAX(b) FROM t1 WHERE a IN (10, 100) GROUP BY a; -a MAX(b) -10 10 -# Should be no more than 4 reads. -SHOW status LIKE 'handler_read_key'; -Variable_name Value -Handler_read_key 4 -FLUSH status; -SELECT a, MAX(b) FROM t2 WHERE a IN (10, 100) GROUP BY a; -a MAX(b) -10 10 -# Should be no more than 4 reads. -SHOW status LIKE 'handler_read_key'; -Variable_name Value -Handler_read_key 4 -DROP TABLE t1, t2; +CREATE TABLE t1 (c_key INT, c_notkey INT, KEY(c_key)); +INSERT INTO t1 VALUES (1, 1), (2, 2), (3, 3); +EXPLAIN SELECT * FROM t1 WHERE 2 NOT BETWEEN c_notkey AND c_key; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL c_key NULL NULL NULL 3 Using where +SELECT * FROM t1 WHERE 2 NOT BETWEEN c_notkey AND c_key; +c_key c_notkey +1 1 +3 3 +DROP TABLE t1; End of 5.1 tests diff --git a/mysql-test/r/subselect4.result b/mysql-test/r/subselect4.result index 482e0045840..fad65d3f000 100644 --- a/mysql-test/r/subselect4.result +++ b/mysql-test/r/subselect4.result @@ -59,3 +59,24 @@ FROM t3 WHERE 1 = 0 GROUP BY 1; (SELECT 1 FROM t1,t2 WHERE t2.b > t3.b) DROP TABLE t1,t2,t3; End of 5.0 tests. +# +# Bug#54568: create view cause Assertion failed: 0, +# file .\item_subselect.cc, line 836 +# +EXPLAIN SELECT 1 LIKE ( 1 IN ( SELECT 1 ) ); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used +Warnings: +Note 1249 Select 2 was reduced during optimization +DESCRIBE SELECT 1 LIKE ( 1 IN ( SELECT 1 ) ); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used +Warnings: +Note 1249 Select 2 was reduced during optimization +# None of the below should crash +CREATE VIEW v1 AS SELECT 1 LIKE ( 1 IN ( SELECT 1 ) ); +CREATE VIEW v2 AS SELECT 1 LIKE '%' ESCAPE ( 1 IN ( SELECT 1 ) ); +DROP VIEW v1, v2; +# +# End of 5.1 tests. +# diff --git a/mysql-test/r/timezone2.result b/mysql-test/r/timezone2.result index 2948bb8ecec..e2e337628ce 100644 --- a/mysql-test/r/timezone2.result +++ b/mysql-test/r/timezone2.result @@ -296,4 +296,16 @@ CONVERT_TZ(NOW(), 'UTC', 'Europe/Moscow') IS NULL UPDATE t1 SET t = CONVERT_TZ(t, 'UTC', 'Europe/Moscow'); UNLOCK TABLES; DROP TABLE t1; +# +# Bug #55424: convert_tz crashes when fed invalid data +# +CREATE TABLE t1 (a SET('x') NOT NULL); +INSERT INTO t1 VALUES (''); +SELECT CONVERT_TZ(1, a, 1) FROM t1; +CONVERT_TZ(1, a, 1) +NULL +SELECT CONVERT_TZ(1, 1, a) FROM t1; +CONVERT_TZ(1, 1, a) +NULL +DROP TABLE t1; End of 5.1 tests diff --git a/mysql-test/r/trigger.result b/mysql-test/r/trigger.result index e3c0b0e1dd9..16b165cdc11 100644 --- a/mysql-test/r/trigger.result +++ b/mysql-test/r/trigger.result @@ -1824,11 +1824,8 @@ Note 1050 Table 'v1' already exists set @id=last_insert_id(); select * from t1; id operation -1 CREATE TABLE ... SELECT, inserting a new key select * from t1_op_log; operation -Before INSERT, new=CREATE TABLE ... SELECT, inserting a new key -After INSERT, new=CREATE TABLE ... SELECT, inserting a new key truncate t1_op_log; create table if not exists v1 replace select @id, "CREATE TABLE ... REPLACE SELECT, deleting a duplicate key"; @@ -1836,13 +1833,8 @@ Warnings: Note 1050 Table 'v1' already exists select * from t1; id operation -1 CREATE TABLE ... REPLACE SELECT, deleting a duplicate key select * from t1_op_log; operation -Before INSERT, new=CREATE TABLE ... REPLACE SELECT, deleting a duplicate key -Before DELETE, old=CREATE TABLE ... SELECT, inserting a new key -After DELETE, old=CREATE TABLE ... SELECT, inserting a new key -After INSERT, new=CREATE TABLE ... REPLACE SELECT, deleting a duplicate key truncate t1; truncate t1_op_log; insert into v1 (id, operation) diff --git a/mysql-test/r/user_var.result b/mysql-test/r/user_var.result index be10bd9954b..56266a46e20 100644 --- a/mysql-test/r/user_var.result +++ b/mysql-test/r/user_var.result @@ -430,4 +430,21 @@ CREATE TRIGGER t_after_insert AFTER INSERT ON t1 FOR EACH ROW SET @bug42188 = 10 INSERT INTO t1 VALUES (1); INSERT INTO t1 VALUES (1); DROP TABLE t1; +CREATE TABLE t1(a INT); +INSERT INTO t1 VALUES (0),(0); +# BUG#55615 : should not crash +SELECT (@a:=(SELECT @a:=1 FROM t1 LIMIT 1)) AND COUNT(1) FROM t1 GROUP BY @a; +(@a:=(SELECT @a:=1 FROM t1 LIMIT 1)) AND COUNT(1) +1 +1 +# BUG#55564 : should not crash +SELECT IF( +@v:=LEAST((SELECT 1 FROM t1 t2 LEFT JOIN t1 ON (@v) GROUP BY t1.a), a), +count(*), 1) +FROM t1 GROUP BY a LIMIT 1; +IF( +@v:=LEAST((SELECT 1 FROM t1 t2 LEFT JOIN t1 ON (@v) GROUP BY t1.a), a), +count(*), 1) +1 +DROP TABLE t1; End of 5.1 tests diff --git a/mysql-test/std_data/parts/t1_blackhole.frm b/mysql-test/std_data/parts/t1_blackhole.frm Binary files differnew file mode 100644 index 00000000000..be77b7a041a --- /dev/null +++ b/mysql-test/std_data/parts/t1_blackhole.frm diff --git a/mysql-test/std_data/parts/t1_blackhole.par b/mysql-test/std_data/parts/t1_blackhole.par Binary files differnew file mode 100644 index 00000000000..6528edf190c --- /dev/null +++ b/mysql-test/std_data/parts/t1_blackhole.par diff --git a/mysql-test/suite/binlog/r/binlog_mixed_load_data.result b/mysql-test/suite/binlog/r/binlog_mixed_load_data.result new file mode 100644 index 00000000000..840257f11ff --- /dev/null +++ b/mysql-test/suite/binlog/r/binlog_mixed_load_data.result @@ -0,0 +1,10 @@ +RESET MASTER; +CREATE TABLE t1 (word CHAR(20) NOT NULL) ENGINE=MYISAM; +LOAD DATA INFILE '../../std_data/words.dat' INTO TABLE t1; +show binlog events from <binlog_start>; +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Table_map # # table_id: # (test.t1) +master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Query # # COMMIT +DROP TABLE t1; diff --git a/mysql-test/suite/binlog/t/binlog_killed_simulate.test b/mysql-test/suite/binlog/t/binlog_killed_simulate.test index ec61271ae88..b87d47559fe 100644 --- a/mysql-test/suite/binlog/t/binlog_killed_simulate.test +++ b/mysql-test/suite/binlog/t/binlog_killed_simulate.test @@ -1,5 +1,5 @@ -- source include/have_debug.inc --- source include/have_binlog_format_mixed_or_statement.inc +-- source include/have_binlog_format_statement.inc # # bug#27571 asynchronous setting mysql_$query()'s local error and # Query_log_event::error_code diff --git a/mysql-test/suite/binlog/t/binlog_mixed_load_data.test b/mysql-test/suite/binlog/t/binlog_mixed_load_data.test new file mode 100644 index 00000000000..7e7cb973c1b --- /dev/null +++ b/mysql-test/suite/binlog/t/binlog_mixed_load_data.test @@ -0,0 +1,15 @@ +# +# Bug #34283 mysqlbinlog leaves tmpfile after termination +# if binlog contains load data infile, so in mixed mode we +# go to row-based for avoiding the problem. +# + +--source include/have_binlog_format_mixed.inc +--source include/have_log_bin.inc + +RESET MASTER; +CREATE TABLE t1 (word CHAR(20) NOT NULL) ENGINE=MYISAM; +let $binlog_start= query_get_value(SHOW MASTER STATUS, Position, 1); +LOAD DATA INFILE '../../std_data/words.dat' INTO TABLE t1; +--source include/show_binlog_events.inc +DROP TABLE t1; diff --git a/mysql-test/suite/binlog/t/binlog_stm_blackhole.test b/mysql-test/suite/binlog/t/binlog_stm_blackhole.test index 02ba2be095b..6047d8ca2fc 100644 --- a/mysql-test/suite/binlog/t/binlog_stm_blackhole.test +++ b/mysql-test/suite/binlog/t/binlog_stm_blackhole.test @@ -2,5 +2,5 @@ # For both statement and row based bin logs 9/19/2005 [jbm] -- source include/not_embedded.inc --- source include/have_binlog_format_mixed_or_statement.inc +-- source include/have_binlog_format_statement.inc -- source extra/binlog_tests/blackhole.test diff --git a/mysql-test/suite/innodb/r/innodb-autoinc.result b/mysql-test/suite/innodb/r/innodb-autoinc.result index a36b3a1a865..350c7ebd541 100644 --- a/mysql-test/suite/innodb/r/innodb-autoinc.result +++ b/mysql-test/suite/innodb/r/innodb-autoinc.result @@ -1244,3 +1244,16 @@ t1 CREATE TABLE `t1` ( PRIMARY KEY (`c1`) ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1 DROP TABLE t1; +DROP TABLE IF EXISTS t1; +Warnings: +Note 1051 Unknown table 't1' +CREATE TABLE t1(c1 BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY) ENGINE=InnoDB; +INSERT INTO t1 VALUES (NULL); +INSERT INTO t1 VALUES (18446744073709551615); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` bigint(20) unsigned NOT NULL AUTO_INCREMENT, + PRIMARY KEY (`c1`) +) ENGINE=InnoDB AUTO_INCREMENT=18446744073709551615 DEFAULT CHARSET=latin1 +DROP TABLE t1; diff --git a/mysql-test/suite/innodb/r/innodb_mysql.result b/mysql-test/suite/innodb/r/innodb_mysql.result index 8765f58b120..66fc73888ed 100644 --- a/mysql-test/suite/innodb/r/innodb_mysql.result +++ b/mysql-test/suite/innodb/r/innodb_mysql.result @@ -2507,4 +2507,96 @@ LOCK TABLES t1 READ; ALTER TABLE t1 COMMENT 'test'; UNLOCK TABLES; DROP TABLE t1; +# +# Bug#55826: create table .. select crashes with when KILL_BAD_DATA +# is returned +# +CREATE TABLE t1(a INT) ENGINE=innodb; +INSERT INTO t1 VALUES (0); +SET SQL_MODE='STRICT_ALL_TABLES'; +CREATE TABLE t2 +SELECT LEAST((SELECT '' FROM t1),NOW()) FROM `t1`; +ERROR 22007: Incorrect datetime value: '' for column 'NOW()' at row 1 +DROP TABLE t1; +SET SQL_MODE=DEFAULT; +# +# Bug#55580: segfault in read_view_sees_trx_id +# +CREATE TABLE t1 (a INT) ENGINE=Innodb; +CREATE TABLE t2 (a INT) ENGINE=Innodb; +INSERT INTO t1 VALUES (1),(2); +INSERT INTO t2 VALUES (1),(2); +START TRANSACTION; +SELECT * FROM t2 LOCK IN SHARE MODE; +a +1 +2 +START TRANSACTION; +SELECT * FROM t1 LOCK IN SHARE MODE; +a +1 +2 +SELECT * FROM t1 FOR UPDATE; +# should not crash +SELECT * FROM t1 GROUP BY (SELECT a FROM t2 LIMIT 1 FOR UPDATE) + t1.a; +ERROR 40001: Deadlock found when trying to get lock; try restarting transaction +DROP TABLE t1,t2; +# +# Bug#55656: mysqldump can be slower after bug #39653 fix +# +CREATE TABLE t1 (a INT , b INT, c INT, d INT, +KEY (b), PRIMARY KEY (a,b)) ENGINE=INNODB; +INSERT INTO t1 VALUES (1,1,1,1), (2,2,2,2), (3,3,3,3); +EXPLAIN SELECT COUNT(*) FROM t1; +id 1 +select_type SIMPLE +table t1 +type index +possible_keys NULL +key b +key_len 4 +ref NULL +rows 3 +Extra Using index +DROP INDEX b ON t1; +CREATE INDEX b ON t1(a,b); +EXPLAIN SELECT COUNT(*) FROM t1; +id 1 +select_type SIMPLE +table t1 +type index +possible_keys NULL +key b +key_len 8 +ref NULL +rows 3 +Extra Using index +DROP INDEX b ON t1; +CREATE INDEX b ON t1(a,b,c); +EXPLAIN SELECT COUNT(*) FROM t1; +id 1 +select_type SIMPLE +table t1 +type index +possible_keys NULL +key b +key_len 13 +ref NULL +rows 3 +Extra Using index +DROP INDEX b ON t1; +CREATE INDEX b ON t1(a,b,c,d); +EXPLAIN SELECT COUNT(*) FROM t1; +id 1 +select_type SIMPLE +table t1 +type index +possible_keys NULL +key PRIMARY +key_len 8 +ref NULL +rows 3 +Extra Using index +DROP TABLE t1; +# End of 5.1 tests diff --git a/mysql-test/suite/innodb/t/innodb-autoinc.test b/mysql-test/suite/innodb/t/innodb-autoinc.test index ef0359b78b0..10602499222 100644 --- a/mysql-test/suite/innodb/t/innodb-autoinc.test +++ b/mysql-test/suite/innodb/t/innodb-autoinc.test @@ -662,3 +662,15 @@ INSERT INTO t1 VALUES (1), (2), (-685113344), (NULL); SELECT * FROM t1; SHOW CREATE TABLE t1; DROP TABLE t1; + +## +# 55277: Failing assertion: auto_inc > 0 +# +DROP TABLE IF EXISTS t1; +CREATE TABLE t1(c1 BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY) ENGINE=InnoDB; +INSERT INTO t1 VALUES (NULL); +INSERT INTO t1 VALUES (18446744073709551615); +# Restart the server +-- source include/restart_mysqld.inc +SHOW CREATE TABLE t1; +DROP TABLE t1; diff --git a/mysql-test/suite/innodb/t/innodb_mysql.test b/mysql-test/suite/innodb/t/innodb_mysql.test index 008ab273497..e892eec7bc2 100644 --- a/mysql-test/suite/innodb/t/innodb_mysql.test +++ b/mysql-test/suite/innodb/t/innodb_mysql.test @@ -747,4 +747,83 @@ UNLOCK TABLES; DROP TABLE t1; +--echo # +--echo # Bug#55826: create table .. select crashes with when KILL_BAD_DATA +--echo # is returned +--echo # +CREATE TABLE t1(a INT) ENGINE=innodb; +INSERT INTO t1 VALUES (0); +SET SQL_MODE='STRICT_ALL_TABLES'; +--error ER_TRUNCATED_WRONG_VALUE +CREATE TABLE t2 + SELECT LEAST((SELECT '' FROM t1),NOW()) FROM `t1`; +DROP TABLE t1; +SET SQL_MODE=DEFAULT; + + + +--echo # +--echo # Bug#55580: segfault in read_view_sees_trx_id +--echo # +CREATE TABLE t1 (a INT) ENGINE=Innodb; +CREATE TABLE t2 (a INT) ENGINE=Innodb; +INSERT INTO t1 VALUES (1),(2); +INSERT INTO t2 VALUES (1),(2); + +connect (con1,localhost,root,,test); +connect (con2,localhost,root,,test); + +connection con1; +START TRANSACTION; +SELECT * FROM t2 LOCK IN SHARE MODE; + +connection con2; +START TRANSACTION; +SELECT * FROM t1 LOCK IN SHARE MODE; + +connection con1; +let $conn_id= `SELECT CONNECTION_ID()`; +--send SELECT * FROM t1 FOR UPDATE + +connection con2; +let $wait_timeout= 2; +let $wait_condition= SELECT 1 FROM INFORMATION_SCHEMA.PROCESSLIST +WHERE ID=$conn_id AND STATE='Sending data'; +--source include/wait_condition.inc +--echo # should not crash +--error ER_LOCK_DEADLOCK +SELECT * FROM t1 GROUP BY (SELECT a FROM t2 LIMIT 1 FOR UPDATE) + t1.a; + +connection default; +disconnect con1; +disconnect con2; + +DROP TABLE t1,t2; + +--echo # +--echo # Bug#55656: mysqldump can be slower after bug #39653 fix +--echo # + +CREATE TABLE t1 (a INT , b INT, c INT, d INT, + KEY (b), PRIMARY KEY (a,b)) ENGINE=INNODB; +INSERT INTO t1 VALUES (1,1,1,1), (2,2,2,2), (3,3,3,3); +--query_vertical EXPLAIN SELECT COUNT(*) FROM t1 + +DROP INDEX b ON t1; +CREATE INDEX b ON t1(a,b); +--query_vertical EXPLAIN SELECT COUNT(*) FROM t1 + +DROP INDEX b ON t1; +CREATE INDEX b ON t1(a,b,c); +--query_vertical EXPLAIN SELECT COUNT(*) FROM t1 + +DROP INDEX b ON t1; +CREATE INDEX b ON t1(a,b,c,d); +--query_vertical EXPLAIN SELECT COUNT(*) FROM t1 + +DROP TABLE t1; + +--echo # + + --echo End of 5.1 tests diff --git a/mysql-test/suite/innodb_plugin/r/innodb-autoinc.result b/mysql-test/suite/innodb_plugin/r/innodb-autoinc.result index a36b3a1a865..350c7ebd541 100644 --- a/mysql-test/suite/innodb_plugin/r/innodb-autoinc.result +++ b/mysql-test/suite/innodb_plugin/r/innodb-autoinc.result @@ -1244,3 +1244,16 @@ t1 CREATE TABLE `t1` ( PRIMARY KEY (`c1`) ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1 DROP TABLE t1; +DROP TABLE IF EXISTS t1; +Warnings: +Note 1051 Unknown table 't1' +CREATE TABLE t1(c1 BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY) ENGINE=InnoDB; +INSERT INTO t1 VALUES (NULL); +INSERT INTO t1 VALUES (18446744073709551615); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` bigint(20) unsigned NOT NULL AUTO_INCREMENT, + PRIMARY KEY (`c1`) +) ENGINE=InnoDB AUTO_INCREMENT=18446744073709551615 DEFAULT CHARSET=latin1 +DROP TABLE t1; diff --git a/mysql-test/suite/innodb_plugin/t/innodb-autoinc-44030.test b/mysql-test/suite/innodb_plugin/t/innodb-autoinc-44030.test index 5e4cf9dcb4c..99cdac72e2e 100644 --- a/mysql-test/suite/innodb_plugin/t/innodb-autoinc-44030.test +++ b/mysql-test/suite/innodb_plugin/t/innodb-autoinc-44030.test @@ -1,8 +1,6 @@ -- source include/have_innodb_plugin.inc # embedded server ignores 'delayed', so skip this -- source include/not_embedded.inc -# remove the next line after bug #55503 is fixed --- source include/not_valgrind.inc let $innodb_file_format_check_orig=`select @@innodb_file_format_check`; diff --git a/mysql-test/suite/innodb_plugin/t/innodb-autoinc.test b/mysql-test/suite/innodb_plugin/t/innodb-autoinc.test index 49394a019d0..997c503d2d3 100644 --- a/mysql-test/suite/innodb_plugin/t/innodb-autoinc.test +++ b/mysql-test/suite/innodb_plugin/t/innodb-autoinc.test @@ -1,8 +1,6 @@ -- source include/have_innodb_plugin.inc # embedded server ignores 'delayed', so skip this -- source include/not_embedded.inc -# remove the next line after bug #55503 is fixed --- source include/not_valgrind.inc let $innodb_file_format_check_orig=`select @@innodb_file_format_check`; @@ -667,6 +665,18 @@ SELECT * FROM t1; SHOW CREATE TABLE t1; DROP TABLE t1; +## +# 55277: Failing assertion: auto_inc > 0 +# +DROP TABLE IF EXISTS t1; +CREATE TABLE t1(c1 BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY) ENGINE=InnoDB; +INSERT INTO t1 VALUES (NULL); +INSERT INTO t1 VALUES (18446744073709551615); +# Restart the server +-- source include/restart_mysqld.inc +SHOW CREATE TABLE t1; +DROP TABLE t1; + # # restore environment to the state it was before this test execution # diff --git a/mysql-test/suite/rpl/r/rpl_create_if_not_exists.result b/mysql-test/suite/rpl/r/rpl_create_if_not_exists.result index fc53aca5136..e3cddf4c606 100644 --- a/mysql-test/suite/rpl/r/rpl_create_if_not_exists.result +++ b/mysql-test/suite/rpl/r/rpl_create_if_not_exists.result @@ -65,3 +65,12 @@ c1 DROP TABLE t1; DROP TABLE t2; DROP TABLE t3; + +# Bug#55616 Killing thread or query during CREATE IF NOT EXISTS makes +# slave SQL thread abort + +CREATE TABLE t1 ( i INT ); +CREATE TABLE IF NOT EXISTS t1 +AS SELECT SLEEP(3); +KILL QUERY master1; +DROP TABLE t1; diff --git a/mysql-test/suite/rpl/r/rpl_create_tmp_table_if_not_exists.result b/mysql-test/suite/rpl/r/rpl_create_tmp_table_if_not_exists.result index 8d0b61cc6d8..de44a1e5d2a 100644 --- a/mysql-test/suite/rpl/r/rpl_create_tmp_table_if_not_exists.result +++ b/mysql-test/suite/rpl/r/rpl_create_tmp_table_if_not_exists.result @@ -19,4 +19,9 @@ master-bin.000001 # Query # # use `test`; CREATE TEMPORARY TABLE IF NOT EXISTS t master-bin.000001 # Query # # use `test`; CREATE TEMPORARY TABLE IF NOT EXISTS tmp1 LIKE tmp master-bin.000001 # Query # # use `test`; CREATE TEMPORARY TABLE IF NOT EXISTS tmp1 LIKE tmp master-bin.000001 # Query # # use `test`; CREATE TEMPORARY TABLE IF NOT EXISTS tmp2 SELECT * FROM tmp -master-bin.000001 # Query # # use `test`; CREATE TEMPORARY TABLE IF NOT EXISTS tmp2 SELECT * FROM tmp +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Query # # use `test`; CREATE TEMPORARY TABLE IF NOT EXISTS `tmp2` ( + `c1` int(11) DEFAULT NULL +) +master-bin.000001 # Query # # use `test`; INSERT INTO `test`.`tmp2` (`c1`) SELECT * FROM tmp +master-bin.000001 # Query # # COMMIT diff --git a/mysql-test/suite/rpl/r/rpl_innodb_mixed_dml.result b/mysql-test/suite/rpl/r/rpl_innodb_mixed_dml.result index 35f4cd3ecbb..7fc940f25e0 100644 --- a/mysql-test/suite/rpl/r/rpl_innodb_mixed_dml.result +++ b/mysql-test/suite/rpl/r/rpl_innodb_mixed_dml.result @@ -883,8 +883,8 @@ master-bin.000001 # Query # # BEGIN master-bin.000001 # Query # # use `test_rpl`; DELETE FROM t2 master-bin.000001 # Xid # # COMMIT /* XID */ master-bin.000001 # Query # # BEGIN -master-bin.000001 # Begin_load_query # # ;file_id=#;block_len=# -master-bin.000001 # Execute_load_query # # use `test_rpl`; LOAD DATA INFILE 'MYSQLTEST_VARDIR/std_data/rpl_mixed.dat' INTO TABLE `t1` FIELDS TERMINATED BY '|' ENCLOSED BY '' ESCAPED BY '\\' LINES TERMINATED BY '\n' (`a`, `b`) ;file_id=# +master-bin.000001 # Table_map # # table_id: # (test_rpl.t1) +master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F master-bin.000001 # Xid # # COMMIT /* XID */ master-bin.000001 # Query # # BEGIN master-bin.000001 # Query # # use `test_rpl`; DELETE FROM t1 diff --git a/mysql-test/suite/rpl/r/rpl_row_create_table.result b/mysql-test/suite/rpl/r/rpl_row_create_table.result index e480ff3dfe9..c6aa05f9e90 100644 --- a/mysql-test/suite/rpl/r/rpl_row_create_table.result +++ b/mysql-test/suite/rpl/r/rpl_row_create_table.result @@ -467,4 +467,10 @@ DROP VIEW IF EXISTS bug48506_t1, bug48506_t2, bug48506_t3; DROP TEMPORARY TABLES t7; DROP TABLES t4, t5; DROP TABLES IF EXISTS bug48506_t4; +CREATE TABLE t1 SELECT 1; +CREATE TABLE IF NOT EXISTS t1 SELECT 1; +Warnings: +Note 1050 Table 't1' already exists +Comparing tables master:test.t1 and slave:test.t1 +DROP TABLE t1; end of the tests diff --git a/mysql-test/suite/rpl/r/rpl_stm_create_if_not_exists.result b/mysql-test/suite/rpl/r/rpl_stm_create_if_not_exists.result new file mode 100644 index 00000000000..9ae1ef315b6 --- /dev/null +++ b/mysql-test/suite/rpl/r/rpl_stm_create_if_not_exists.result @@ -0,0 +1,704 @@ +# WL#5370 Keep forward-compatibility when changing 'CREATE TABLE IF NOT +# EXISTS ... SELECT' behaviour +# +stop slave; +drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; +reset master; +reset slave; +drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; +start slave; + + +CREATE TABLE t2(c1 INT, c2 char(10)); +INSERT INTO t2 VALUES(1, 'abc'), (2, 'abc'); + +# The original query should be binlogged if the table does not exist. +# ------------------------------------------------------------------ + +CREATE TABLE IF NOT EXISTS t1 (c1 INT , c2 INT, c3 char(10), c4 INT KEY) +SELECT 'abc' AS c3, 1 AS c4; +show binlog events from <binlog_start>; +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Query # # use `test`; CREATE TABLE IF NOT EXISTS t1 (c1 INT , c2 INT, c3 char(10), c4 INT KEY) +SELECT 'abc' AS c3, 1 AS c4 +Comparing tables master:test.t1 and slave:test.t1 + +# The statement should be binlogged as two events. one is +# 'CREATE TABLE IF NOT EXISTS ..', another one is +# 'INSERT ... SELECT'. +# ------------------------------------------------------------------ + +CREATE TABLE IF NOT EXISTS t1 +SELECT 'abc', 2; +Warnings: +Note 1050 Table 't1' already exists +show binlog events from <binlog_start>; +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Query # # use `test`; CREATE TABLE IF NOT EXISTS `t1` ( + `c1` int(11) DEFAULT NULL, + `c2` int(11) DEFAULT NULL, + `c3` char(10) DEFAULT NULL, + `c4` int(11) NOT NULL, + PRIMARY KEY (`c4`) +) +master-bin.000001 # Query # # use `test`; INSERT INTO `test`.`t1` (`c3`,`c4`) SELECT 'abc', 2 +master-bin.000001 # Query # # COMMIT +Comparing tables master:test.t1 and slave:test.t1 + +# Verify if it can be binlogged with right database name when the table +# is not in the default database + +DROP DATABASE IF EXISTS db1; +CREATE DATABASE db1; +USE db1; +CREATE TABLE IF NOT EXISTS test.t1 +SELECT 'abc', 20; +Warnings: +Note 1050 Table 't1' already exists +show binlog events from <binlog_start>; +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Query # # use `db1`; CREATE TABLE IF NOT EXISTS `test`.`t1` ( + `c1` int(11) DEFAULT NULL, + `c2` int(11) DEFAULT NULL, + `c3` char(10) DEFAULT NULL, + `c4` int(11) NOT NULL, + PRIMARY KEY (`c4`) +) +master-bin.000001 # Query # # use `db1`; INSERT INTO `test`.`t1` (`c3`,`c4`) SELECT 'abc', 20 +master-bin.000001 # Query # # COMMIT +Comparing tables master:test.t1 and slave:test.t1 +USE test; +DROP DATABASE db1; + +# It should be binlogged as 'REPLACE ... SELECT' +# if the original statement has option REPLACE + +CREATE TABLE IF NOT EXISTS t1 +REPLACE SELECT '123', 2; +Warnings: +Note 1050 Table 't1' already exists +show binlog events from <binlog_start>; +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Query # # use `test`; CREATE TABLE IF NOT EXISTS `t1` ( + `c1` int(11) DEFAULT NULL, + `c2` int(11) DEFAULT NULL, + `c3` char(10) DEFAULT NULL, + `c4` int(11) NOT NULL, + PRIMARY KEY (`c4`) +) +master-bin.000001 # Query # # use `test`; REPLACE INTO `test`.`t1` (`c3`,`c4`) SELECT '123', 2 +master-bin.000001 # Query # # COMMIT +Comparing tables master:test.t1 and slave:test.t1 + +# It should be binlogged as 'INSERT IGNORE... SELECT' +# if the original statement has option IGNORE + +CREATE TABLE IF NOT EXISTS t1 +IGNORE SELECT '123', 2; +Warnings: +Note 1050 Table 't1' already exists +show binlog events from <binlog_start>; +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Query # # use `test`; CREATE TABLE IF NOT EXISTS `t1` ( + `c1` int(11) DEFAULT NULL, + `c2` int(11) DEFAULT NULL, + `c3` char(10) DEFAULT NULL, + `c4` int(11) NOT NULL, + PRIMARY KEY (`c4`) +) +master-bin.000001 # Query # # use `test`; INSERT IGNORE INTO `test`.`t1` (`c3`,`c4`) SELECT '123', 2 +master-bin.000001 # Query # # COMMIT +Comparing tables master:test.t1 and slave:test.t1 + +# Nothing should be binlogged if error happens and no any row is inserted + +CREATE TABLE IF NOT EXISTS t1 +SELECT '123', 2; +ERROR 23000: Duplicate entry '2' for key 'PRIMARY' +show binlog events from <binlog_start>; +Log_name Pos Event_type Server_id End_log_pos Info +Comparing tables master:test.t1 and slave:test.t1 + +# Verify it can binlog well when there are some braces('(') + +CREATE TABLE IF NOT EXISTS t1 +(SELECT '123', 3) UNION (SELECT '123', 4); +Warnings: +Note 1050 Table 't1' already exists +CREATE TABLE IF NOT EXISTS t1 +REPLACE (SELECT 'abc', 3) UNION (SELECT 'abc', 4); +Warnings: +Note 1050 Table 't1' already exists +CREATE TABLE IF NOT EXISTS t1 +IGNORE (SELECT '123', 3) UNION (SELECT '123', 4); +Warnings: +Note 1050 Table 't1' already exists +show binlog events from <binlog_start>; +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Query # # use `test`; CREATE TABLE IF NOT EXISTS `t1` ( + `c1` int(11) DEFAULT NULL, + `c2` int(11) DEFAULT NULL, + `c3` char(10) DEFAULT NULL, + `c4` int(11) NOT NULL, + PRIMARY KEY (`c4`) +) +master-bin.000001 # Query # # use `test`; INSERT INTO `test`.`t1` (`c3`,`c4`) (SELECT '123', 3) UNION (SELECT '123', 4) +master-bin.000001 # Query # # COMMIT +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Query # # use `test`; CREATE TABLE IF NOT EXISTS `t1` ( + `c1` int(11) DEFAULT NULL, + `c2` int(11) DEFAULT NULL, + `c3` char(10) DEFAULT NULL, + `c4` int(11) NOT NULL, + PRIMARY KEY (`c4`) +) +master-bin.000001 # Query # # use `test`; REPLACE INTO `test`.`t1` (`c3`,`c4`) (SELECT 'abc', 3) UNION (SELECT 'abc', 4) +master-bin.000001 # Query # # COMMIT +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Query # # use `test`; CREATE TABLE IF NOT EXISTS `t1` ( + `c1` int(11) DEFAULT NULL, + `c2` int(11) DEFAULT NULL, + `c3` char(10) DEFAULT NULL, + `c4` int(11) NOT NULL, + PRIMARY KEY (`c4`) +) +master-bin.000001 # Query # # use `test`; INSERT IGNORE INTO `test`.`t1` (`c3`,`c4`) (SELECT '123', 3) UNION (SELECT '123', 4) +master-bin.000001 # Query # # COMMIT +Comparing tables master:test.t1 and slave:test.t1 + +# Throw a warning that table already exists and don't insert anything + +CREATE VIEW t3 AS SELECT * FROM t2; +CREATE TABLE IF NOT EXISTS t3 +SELECT '123', 2; +Warnings: +Note 1050 Table 't3' already exists +show binlog events from <binlog_start>; +Log_name Pos Event_type Server_id End_log_pos Info +DROP VIEW t3; + +# The statement can be binlogged correctly when it is in a SP/EVENT/TRIGGER + +DROP PROCEDURE IF EXISTS p1; +CREATE PROCEDURE p1(IN a INT) +CREATE TABLE IF NOT EXISTS t1 SELECT '123', a; +call p1(500); +Warnings: +Note 1050 Table 't1' already exists +call p1(600); +Warnings: +Note 1050 Table 't1' already exists +show binlog events from <binlog_start>; +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Query # # use `test`; CREATE TABLE IF NOT EXISTS `t1` ( + `c1` int(11) DEFAULT NULL, + `c2` int(11) DEFAULT NULL, + `c3` char(10) DEFAULT NULL, + `c4` int(11) NOT NULL, + PRIMARY KEY (`c4`) +) +master-bin.000001 # Query # # use `test`; INSERT INTO `test`.`t1` (`c3`,`c4`) SELECT '123', NAME_CONST('a',500) +master-bin.000001 # Query # # COMMIT +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Query # # use `test`; CREATE TABLE IF NOT EXISTS `t1` ( + `c1` int(11) DEFAULT NULL, + `c2` int(11) DEFAULT NULL, + `c3` char(10) DEFAULT NULL, + `c4` int(11) NOT NULL, + PRIMARY KEY (`c4`) +) +master-bin.000001 # Query # # use `test`; INSERT INTO `test`.`t1` (`c3`,`c4`) SELECT '123', NAME_CONST('a',600) +master-bin.000001 # Query # # COMMIT +Comparing tables master:test.t1 and slave:test.t1 +DROP PROCEDURE p1; + +# The statement can be binlogged correctly when it is in a prepared statement + +PREPARE stm FROM "CREATE TABLE IF NOT EXISTS t1 SELECT '123', ?"; +SET @a= 700; +EXECUTE stm USING @a; +Warnings: +Note 1050 Table 't1' already exists +SET @a= 800; +EXECUTE stm USING @a; +Warnings: +Note 1050 Table 't1' already exists +show binlog events from <binlog_start>; +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Query # # use `test`; CREATE TABLE IF NOT EXISTS `t1` ( + `c1` int(11) DEFAULT NULL, + `c2` int(11) DEFAULT NULL, + `c3` char(10) DEFAULT NULL, + `c4` int(11) NOT NULL, + PRIMARY KEY (`c4`) +) +master-bin.000001 # Query # # use `test`; INSERT INTO `test`.`t1` (`c3`,`c4`) SELECT '123', 700 +master-bin.000001 # Query # # COMMIT +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Query # # use `test`; CREATE TABLE IF NOT EXISTS `t1` ( + `c1` int(11) DEFAULT NULL, + `c2` int(11) DEFAULT NULL, + `c3` char(10) DEFAULT NULL, + `c4` int(11) NOT NULL, + PRIMARY KEY (`c4`) +) +master-bin.000001 # Query # # use `test`; INSERT INTO `test`.`t1` (`c3`,`c4`) SELECT '123', 800 +master-bin.000001 # Query # # COMMIT +Comparing tables master:test.t1 and slave:test.t1 + +# The statement can be binlogged correctly when it is in a conditional comment + +# The whole statement in a conditional comment +/*!CREATE TABLE IF NOT EXISTS t1 +SELECT 'abc', 900*/; +Warnings: +Note 1050 Table 't1' already exists +show binlog events from <binlog_start>; +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Query # # use `test`; CREATE TABLE IF NOT EXISTS `t1` ( + `c1` int(11) DEFAULT NULL, + `c2` int(11) DEFAULT NULL, + `c3` char(10) DEFAULT NULL, + `c4` int(11) NOT NULL, + PRIMARY KEY (`c4`) +) +master-bin.000001 # Query # # use `test`; /*! INSERT INTO `test`.`t1` (`c3`,`c4`) SELECT 'abc', 900*/ +master-bin.000001 # Query # # COMMIT + +# There is an long comment before SELECT +/*!CREATE /*blabla*/ TABLE IF NOT EXISTS t1 +SELECT 'abc', 901*/; +Warnings: +Note 1050 Table 't1' already exists +show binlog events from <binlog_start>; +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Query # # use `test`; CREATE TABLE IF NOT EXISTS `t1` ( + `c1` int(11) DEFAULT NULL, + `c2` int(11) DEFAULT NULL, + `c3` char(10) DEFAULT NULL, + `c4` int(11) NOT NULL, + PRIMARY KEY (`c4`) +) +master-bin.000001 # Query # # use `test`; /*! INSERT INTO `test`.`t1` (`c3`,`c4`) SELECT 'abc', 901*/ +master-bin.000001 # Query # # COMMIT + +# Conditional comment starts just from SELECT +CREATE TABLE IF NOT EXISTS t1 +/*!SELECT 'abc',*/ 902; +Warnings: +Note 1050 Table 't1' already exists +show binlog events from <binlog_start>; +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Query # # use `test`; CREATE TABLE IF NOT EXISTS `t1` ( + `c1` int(11) DEFAULT NULL, + `c2` int(11) DEFAULT NULL, + `c3` char(10) DEFAULT NULL, + `c4` int(11) NOT NULL, + PRIMARY KEY (`c4`) +) +master-bin.000001 # Query # # use `test`; /*! INSERT INTO `test`.`t1` (`c3`,`c4`) SELECT 'abc',*/ 902 +master-bin.000001 # Query # # COMMIT + +# Only SELECT keyword is in the conditional comment +CREATE TABLE IF NOT EXISTS t1 +/*!SELECT*/ /*!'abc',*/ 904; +Warnings: +Note 1050 Table 't1' already exists +show binlog events from <binlog_start>; +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Query # # use `test`; CREATE TABLE IF NOT EXISTS `t1` ( + `c1` int(11) DEFAULT NULL, + `c2` int(11) DEFAULT NULL, + `c3` char(10) DEFAULT NULL, + `c4` int(11) NOT NULL, + PRIMARY KEY (`c4`) +) +master-bin.000001 # Query # # use `test`; /*! INSERT INTO `test`.`t1` (`c3`,`c4`) SELECT*/ /*!'abc',*/ 904 +master-bin.000001 # Query # # COMMIT + +# Conditional comment is after SELECT keyword +CREATE TABLE IF NOT EXISTS t1 +SELECT /*!'abc',*/ 903; +Warnings: +Note 1050 Table 't1' already exists +show binlog events from <binlog_start>; +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Query # # use `test`; CREATE TABLE IF NOT EXISTS `t1` ( + `c1` int(11) DEFAULT NULL, + `c2` int(11) DEFAULT NULL, + `c3` char(10) DEFAULT NULL, + `c4` int(11) NOT NULL, + PRIMARY KEY (`c4`) +) +master-bin.000001 # Query # # use `test`; INSERT INTO `test`.`t1` (`c3`,`c4`) SELECT /*!'abc',*/ 903 +master-bin.000001 # Query # # COMMIT + +# Conditional comment ends just before SELECT keyword +/*!CREATE TABLE IF NOT EXISTS t1 +*/SELECT 'abc', 905; +Warnings: +Note 1050 Table 't1' already exists +show binlog events from <binlog_start>; +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Query # # use `test`; CREATE TABLE IF NOT EXISTS `t1` ( + `c1` int(11) DEFAULT NULL, + `c2` int(11) DEFAULT NULL, + `c3` char(10) DEFAULT NULL, + `c4` int(11) NOT NULL, + PRIMARY KEY (`c4`) +) +master-bin.000001 # Query # # use `test`; INSERT INTO `test`.`t1` (`c3`,`c4`) SELECT 'abc', 905 +master-bin.000001 # Query # # COMMIT +Comparing tables master:test.t1 and slave:test.t1 +DROP TABLE t2; +DROP TABLE t1; + + +CREATE TABLE t2(c1 INT, c2 char(10)); +INSERT INTO t2 VALUES(1, 'abc'), (2, 'abc'); + +# The original query should be binlogged if the table does not exist. +# ------------------------------------------------------------------ + +CREATE TEMPORARY TABLE IF NOT EXISTS t1 (c1 INT , c2 INT, c3 char(10), c4 INT KEY) +SELECT 'abc' AS c3, 1 AS c4; +show binlog events from <binlog_start>; +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Query # # use `test`; CREATE TEMPORARY TABLE IF NOT EXISTS t1 (c1 INT , c2 INT, c3 char(10), c4 INT KEY) +SELECT 'abc' AS c3, 1 AS c4 + +# The statement should be binlogged as two events. one is +# 'CREATE TEMPORARY TABLE IF NOT EXISTS ..', another one is +# 'INSERT ... SELECT'. +# ------------------------------------------------------------------ + +CREATE TEMPORARY TABLE IF NOT EXISTS t1 +SELECT 'abc', 2; +Warnings: +Note 1050 Table 't1' already exists +show binlog events from <binlog_start>; +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Query # # use `test`; CREATE TEMPORARY TABLE IF NOT EXISTS `t1` ( + `c1` int(11) DEFAULT NULL, + `c2` int(11) DEFAULT NULL, + `c3` char(10) DEFAULT NULL, + `c4` int(11) NOT NULL, + PRIMARY KEY (`c4`) +) +master-bin.000001 # Query # # use `test`; INSERT INTO `test`.`t1` (`c3`,`c4`) SELECT 'abc', 2 +master-bin.000001 # Query # # COMMIT + +# Verify if it can be binlogged with right database name when the table +# is not in the default database + +DROP DATABASE IF EXISTS db1; +CREATE DATABASE db1; +USE db1; +CREATE TEMPORARY TABLE IF NOT EXISTS test.t1 +SELECT 'abc', 20; +Warnings: +Note 1050 Table 't1' already exists +show binlog events from <binlog_start>; +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Query # # use `db1`; CREATE TEMPORARY TABLE IF NOT EXISTS `test`.`t1` ( + `c1` int(11) DEFAULT NULL, + `c2` int(11) DEFAULT NULL, + `c3` char(10) DEFAULT NULL, + `c4` int(11) NOT NULL, + PRIMARY KEY (`c4`) +) +master-bin.000001 # Query # # use `db1`; INSERT INTO `test`.`t1` (`c3`,`c4`) SELECT 'abc', 20 +master-bin.000001 # Query # # COMMIT +USE test; +DROP DATABASE db1; + +# It should be binlogged as 'REPLACE ... SELECT' +# if the original statement has option REPLACE + +CREATE TEMPORARY TABLE IF NOT EXISTS t1 +REPLACE SELECT '123', 2; +Warnings: +Note 1050 Table 't1' already exists +show binlog events from <binlog_start>; +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Query # # use `test`; CREATE TEMPORARY TABLE IF NOT EXISTS `t1` ( + `c1` int(11) DEFAULT NULL, + `c2` int(11) DEFAULT NULL, + `c3` char(10) DEFAULT NULL, + `c4` int(11) NOT NULL, + PRIMARY KEY (`c4`) +) +master-bin.000001 # Query # # use `test`; REPLACE INTO `test`.`t1` (`c3`,`c4`) SELECT '123', 2 +master-bin.000001 # Query # # COMMIT + +# It should be binlogged as 'INSERT IGNORE... SELECT' +# if the original statement has option IGNORE + +CREATE TEMPORARY TABLE IF NOT EXISTS t1 +IGNORE SELECT '123', 2; +Warnings: +Note 1050 Table 't1' already exists +show binlog events from <binlog_start>; +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Query # # use `test`; CREATE TEMPORARY TABLE IF NOT EXISTS `t1` ( + `c1` int(11) DEFAULT NULL, + `c2` int(11) DEFAULT NULL, + `c3` char(10) DEFAULT NULL, + `c4` int(11) NOT NULL, + PRIMARY KEY (`c4`) +) +master-bin.000001 # Query # # use `test`; INSERT IGNORE INTO `test`.`t1` (`c3`,`c4`) SELECT '123', 2 +master-bin.000001 # Query # # COMMIT + +# Nothing should be binlogged if error happens and no any row is inserted + +CREATE TEMPORARY TABLE IF NOT EXISTS t1 +SELECT '123', 2; +ERROR 23000: Duplicate entry '2' for key 'PRIMARY' +show binlog events from <binlog_start>; +Log_name Pos Event_type Server_id End_log_pos Info + +# Verify it can binlog well when there are some braces('(') + +CREATE TEMPORARY TABLE IF NOT EXISTS t1 +(SELECT '123', 3) UNION (SELECT '123', 4); +Warnings: +Note 1050 Table 't1' already exists +CREATE TEMPORARY TABLE IF NOT EXISTS t1 +REPLACE (SELECT 'abc', 3) UNION (SELECT 'abc', 4); +Warnings: +Note 1050 Table 't1' already exists +CREATE TEMPORARY TABLE IF NOT EXISTS t1 +IGNORE (SELECT '123', 3) UNION (SELECT '123', 4); +Warnings: +Note 1050 Table 't1' already exists +show binlog events from <binlog_start>; +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Query # # use `test`; CREATE TEMPORARY TABLE IF NOT EXISTS `t1` ( + `c1` int(11) DEFAULT NULL, + `c2` int(11) DEFAULT NULL, + `c3` char(10) DEFAULT NULL, + `c4` int(11) NOT NULL, + PRIMARY KEY (`c4`) +) +master-bin.000001 # Query # # use `test`; INSERT INTO `test`.`t1` (`c3`,`c4`) (SELECT '123', 3) UNION (SELECT '123', 4) +master-bin.000001 # Query # # COMMIT +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Query # # use `test`; CREATE TEMPORARY TABLE IF NOT EXISTS `t1` ( + `c1` int(11) DEFAULT NULL, + `c2` int(11) DEFAULT NULL, + `c3` char(10) DEFAULT NULL, + `c4` int(11) NOT NULL, + PRIMARY KEY (`c4`) +) +master-bin.000001 # Query # # use `test`; REPLACE INTO `test`.`t1` (`c3`,`c4`) (SELECT 'abc', 3) UNION (SELECT 'abc', 4) +master-bin.000001 # Query # # COMMIT +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Query # # use `test`; CREATE TEMPORARY TABLE IF NOT EXISTS `t1` ( + `c1` int(11) DEFAULT NULL, + `c2` int(11) DEFAULT NULL, + `c3` char(10) DEFAULT NULL, + `c4` int(11) NOT NULL, + PRIMARY KEY (`c4`) +) +master-bin.000001 # Query # # use `test`; INSERT IGNORE INTO `test`.`t1` (`c3`,`c4`) (SELECT '123', 3) UNION (SELECT '123', 4) +master-bin.000001 # Query # # COMMIT + +# The statement can be binlogged correctly when it is in a SP/EVENT/TRIGGER + +DROP PROCEDURE IF EXISTS p1; +CREATE PROCEDURE p1(IN a INT) +CREATE TEMPORARY TABLE IF NOT EXISTS t1 SELECT '123', a; +call p1(500); +Warnings: +Note 1050 Table 't1' already exists +call p1(600); +Warnings: +Note 1050 Table 't1' already exists +show binlog events from <binlog_start>; +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Query # # use `test`; CREATE TEMPORARY TABLE IF NOT EXISTS `t1` ( + `c1` int(11) DEFAULT NULL, + `c2` int(11) DEFAULT NULL, + `c3` char(10) DEFAULT NULL, + `c4` int(11) NOT NULL, + PRIMARY KEY (`c4`) +) +master-bin.000001 # Query # # use `test`; INSERT INTO `test`.`t1` (`c3`,`c4`) SELECT '123', NAME_CONST('a',500) +master-bin.000001 # Query # # COMMIT +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Query # # use `test`; CREATE TEMPORARY TABLE IF NOT EXISTS `t1` ( + `c1` int(11) DEFAULT NULL, + `c2` int(11) DEFAULT NULL, + `c3` char(10) DEFAULT NULL, + `c4` int(11) NOT NULL, + PRIMARY KEY (`c4`) +) +master-bin.000001 # Query # # use `test`; INSERT INTO `test`.`t1` (`c3`,`c4`) SELECT '123', NAME_CONST('a',600) +master-bin.000001 # Query # # COMMIT +DROP PROCEDURE p1; + +# The statement can be binlogged correctly when it is in a prepared statement + +PREPARE stm FROM "CREATE TEMPORARY TABLE IF NOT EXISTS t1 SELECT '123', ?"; +SET @a= 700; +EXECUTE stm USING @a; +Warnings: +Note 1050 Table 't1' already exists +SET @a= 800; +EXECUTE stm USING @a; +Warnings: +Note 1050 Table 't1' already exists +show binlog events from <binlog_start>; +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Query # # use `test`; CREATE TEMPORARY TABLE IF NOT EXISTS `t1` ( + `c1` int(11) DEFAULT NULL, + `c2` int(11) DEFAULT NULL, + `c3` char(10) DEFAULT NULL, + `c4` int(11) NOT NULL, + PRIMARY KEY (`c4`) +) +master-bin.000001 # Query # # use `test`; INSERT INTO `test`.`t1` (`c3`,`c4`) SELECT '123', 700 +master-bin.000001 # Query # # COMMIT +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Query # # use `test`; CREATE TEMPORARY TABLE IF NOT EXISTS `t1` ( + `c1` int(11) DEFAULT NULL, + `c2` int(11) DEFAULT NULL, + `c3` char(10) DEFAULT NULL, + `c4` int(11) NOT NULL, + PRIMARY KEY (`c4`) +) +master-bin.000001 # Query # # use `test`; INSERT INTO `test`.`t1` (`c3`,`c4`) SELECT '123', 800 +master-bin.000001 # Query # # COMMIT + +# The statement can be binlogged correctly when it is in a conditional comment + +# The whole statement in a conditional comment +/*!CREATE TEMPORARY TABLE IF NOT EXISTS t1 +SELECT 'abc', 900*/; +Warnings: +Note 1050 Table 't1' already exists +show binlog events from <binlog_start>; +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Query # # use `test`; CREATE TEMPORARY TABLE IF NOT EXISTS `t1` ( + `c1` int(11) DEFAULT NULL, + `c2` int(11) DEFAULT NULL, + `c3` char(10) DEFAULT NULL, + `c4` int(11) NOT NULL, + PRIMARY KEY (`c4`) +) +master-bin.000001 # Query # # use `test`; /*! INSERT INTO `test`.`t1` (`c3`,`c4`) SELECT 'abc', 900*/ +master-bin.000001 # Query # # COMMIT + +# There is an long comment before SELECT +/*!CREATE TEMPORARY /*blabla*/ TABLE IF NOT EXISTS t1 +SELECT 'abc', 901*/; +Warnings: +Note 1050 Table 't1' already exists +show binlog events from <binlog_start>; +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Query # # use `test`; CREATE TEMPORARY TABLE IF NOT EXISTS `t1` ( + `c1` int(11) DEFAULT NULL, + `c2` int(11) DEFAULT NULL, + `c3` char(10) DEFAULT NULL, + `c4` int(11) NOT NULL, + PRIMARY KEY (`c4`) +) +master-bin.000001 # Query # # use `test`; /*! INSERT INTO `test`.`t1` (`c3`,`c4`) SELECT 'abc', 901*/ +master-bin.000001 # Query # # COMMIT + +# Conditional comment starts just from SELECT +CREATE TEMPORARY TABLE IF NOT EXISTS t1 +/*!SELECT 'abc',*/ 902; +Warnings: +Note 1050 Table 't1' already exists +show binlog events from <binlog_start>; +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Query # # use `test`; CREATE TEMPORARY TABLE IF NOT EXISTS `t1` ( + `c1` int(11) DEFAULT NULL, + `c2` int(11) DEFAULT NULL, + `c3` char(10) DEFAULT NULL, + `c4` int(11) NOT NULL, + PRIMARY KEY (`c4`) +) +master-bin.000001 # Query # # use `test`; /*! INSERT INTO `test`.`t1` (`c3`,`c4`) SELECT 'abc',*/ 902 +master-bin.000001 # Query # # COMMIT + +# Only SELECT keyword is in the conditional comment +CREATE TEMPORARY TABLE IF NOT EXISTS t1 +/*!SELECT*/ /*!'abc',*/ 904; +Warnings: +Note 1050 Table 't1' already exists +show binlog events from <binlog_start>; +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Query # # use `test`; CREATE TEMPORARY TABLE IF NOT EXISTS `t1` ( + `c1` int(11) DEFAULT NULL, + `c2` int(11) DEFAULT NULL, + `c3` char(10) DEFAULT NULL, + `c4` int(11) NOT NULL, + PRIMARY KEY (`c4`) +) +master-bin.000001 # Query # # use `test`; /*! INSERT INTO `test`.`t1` (`c3`,`c4`) SELECT*/ /*!'abc',*/ 904 +master-bin.000001 # Query # # COMMIT + +# Conditional comment is after SELECT keyword +CREATE TEMPORARY TABLE IF NOT EXISTS t1 +SELECT /*!'abc',*/ 903; +Warnings: +Note 1050 Table 't1' already exists +show binlog events from <binlog_start>; +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Query # # use `test`; CREATE TEMPORARY TABLE IF NOT EXISTS `t1` ( + `c1` int(11) DEFAULT NULL, + `c2` int(11) DEFAULT NULL, + `c3` char(10) DEFAULT NULL, + `c4` int(11) NOT NULL, + PRIMARY KEY (`c4`) +) +master-bin.000001 # Query # # use `test`; INSERT INTO `test`.`t1` (`c3`,`c4`) SELECT /*!'abc',*/ 903 +master-bin.000001 # Query # # COMMIT + +# Conditional comment ends just before SELECT keyword +/*!CREATE TEMPORARY TABLE IF NOT EXISTS t1 +*/SELECT 'abc', 905; +Warnings: +Note 1050 Table 't1' already exists +show binlog events from <binlog_start>; +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Query # # use `test`; CREATE TEMPORARY TABLE IF NOT EXISTS `t1` ( + `c1` int(11) DEFAULT NULL, + `c2` int(11) DEFAULT NULL, + `c3` char(10) DEFAULT NULL, + `c4` int(11) NOT NULL, + PRIMARY KEY (`c4`) +) +master-bin.000001 # Query # # use `test`; INSERT INTO `test`.`t1` (`c3`,`c4`) SELECT 'abc', 905 +master-bin.000001 # Query # # COMMIT +DROP TABLE t2; +DROP TEMPORARY TABLE t1; diff --git a/mysql-test/suite/rpl/t/disabled.def b/mysql-test/suite/rpl/t/disabled.def index ef77c54e067..5b150288935 100644 --- a/mysql-test/suite/rpl/t/disabled.def +++ b/mysql-test/suite/rpl/t/disabled.def @@ -11,3 +11,4 @@ ############################################################################## rpl_row_create_table : Bug#51574 Feb 27 2010 andrei failed different way than earlier with bug#45576 +rpl_log_pos : BUG#55675 Sep 10 2010 27 2010 alfranio rpl.rpl_log_pos fails sporadically with error binlog truncated in the middle diff --git a/mysql-test/suite/rpl/t/rpl_create_if_not_exists.test b/mysql-test/suite/rpl/t/rpl_create_if_not_exists.test index 114f71af873..cf26e58c3ec 100644 --- a/mysql-test/suite/rpl/t/rpl_create_if_not_exists.test +++ b/mysql-test/suite/rpl/t/rpl_create_if_not_exists.test @@ -119,5 +119,32 @@ SELECT * FROM t2; DROP TABLE t1; DROP TABLE t2; DROP TABLE t3; +sync_slave_with_master; + +--echo +--echo # Bug#55616 Killing thread or query during CREATE IF NOT EXISTS makes +--echo # slave SQL thread abort +--echo + +--connection master1 +let $con_id = `SELECT CONNECTION_ID()`; + +CREATE TABLE t1 ( i INT ); +send CREATE TABLE IF NOT EXISTS t1 + AS SELECT SLEEP(3); + +connection master; +let $wait_timeout = 3; +let $show_statement = SHOW PROCESSLIST; +let $field = State; +let $condition = = 'User sleep'; +source include/wait_show_condition.inc; + +--replace_result $con_id master1 +eval KILL QUERY $con_id; +sync_slave_with_master; + +connection master; +DROP TABLE t1; source include/master-slave-end.inc; diff --git a/mysql-test/suite/rpl/t/rpl_loaddata_fatal.test b/mysql-test/suite/rpl/t/rpl_loaddata_fatal.test index e80fc160d8f..b8975308a86 100644 --- a/mysql-test/suite/rpl/t/rpl_loaddata_fatal.test +++ b/mysql-test/suite/rpl/t/rpl_loaddata_fatal.test @@ -1,4 +1,4 @@ -source include/have_binlog_format_mixed_or_statement.inc; +source include/have_binlog_format_statement.inc; source include/have_debug.inc; source include/master-slave.inc; diff --git a/mysql-test/suite/rpl/t/rpl_loaddata_map.test b/mysql-test/suite/rpl/t/rpl_loaddata_map.test index ddee9e7e989..1db7c4a893b 100644 --- a/mysql-test/suite/rpl/t/rpl_loaddata_map.test +++ b/mysql-test/suite/rpl/t/rpl_loaddata_map.test @@ -16,7 +16,7 @@ # BUG#33413 show binlog events fails if binlog has event size of close # to max_allowed_packet -source include/have_binlog_format_mixed_or_statement.inc; +source include/have_binlog_format_statement.inc; source include/master-slave.inc; diff --git a/mysql-test/suite/rpl/t/rpl_row_create_table.test b/mysql-test/suite/rpl/t/rpl_row_create_table.test index a72ca75e975..148032f2987 100644 --- a/mysql-test/suite/rpl/t/rpl_row_create_table.test +++ b/mysql-test/suite/rpl/t/rpl_row_create_table.test @@ -299,5 +299,18 @@ DROP VIEW IF EXISTS bug48506_t1, bug48506_t2, bug48506_t3; DROP TEMPORARY TABLES t7; DROP TABLES t4, t5; DROP TABLES IF EXISTS bug48506_t4; +sync_slave_with_master; + +# +# Bug#55598 RBR: CREATE TABLE IF NOT EXISTS and INSERT written to binary log +# twice +# +connection master; +CREATE TABLE t1 SELECT 1; +CREATE TABLE IF NOT EXISTS t1 SELECT 1; +let $diff_table=test.t1; +source include/rpl_diff_tables.inc; +DROP TABLE t1; + source include/master-slave-end.inc; --echo end of the tests diff --git a/mysql-test/suite/rpl/t/rpl_slave_load_remove_tmpfile.test b/mysql-test/suite/rpl/t/rpl_slave_load_remove_tmpfile.test index 22309c33724..b7342fd1c40 100644 --- a/mysql-test/suite/rpl/t/rpl_slave_load_remove_tmpfile.test +++ b/mysql-test/suite/rpl/t/rpl_slave_load_remove_tmpfile.test @@ -8,7 +8,7 @@ # 2 - Catches error. ########################################################################## ---source include/have_binlog_format_mixed_or_statement.inc +--source include/have_binlog_format_statement.inc --source include/have_innodb.inc --source include/have_debug.inc --source include/master-slave.inc diff --git a/mysql-test/suite/rpl/t/rpl_stm_create_if_not_exists.test b/mysql-test/suite/rpl/t/rpl_stm_create_if_not_exists.test new file mode 100644 index 00000000000..69866cad267 --- /dev/null +++ b/mysql-test/suite/rpl/t/rpl_stm_create_if_not_exists.test @@ -0,0 +1,14 @@ +# +--echo # WL#5370 Keep forward-compatibility when changing 'CREATE TABLE IF NOT +--echo # EXISTS ... SELECT' behaviour +--echo # + +source include/master-slave.inc; +source include/have_binlog_format_statement.inc; + +source extra/rpl_tests/rpl_stm_create_if_not_exists.test; + +let $is_temporary=1; +source extra/rpl_tests/rpl_stm_create_if_not_exists.test; + +source include/master-slave-end.inc; diff --git a/mysql-test/suite/rpl/t/rpl_stm_log.test b/mysql-test/suite/rpl/t/rpl_stm_log.test index 2af9d7f85bc..7bc17fbaada 100644 --- a/mysql-test/suite/rpl/t/rpl_stm_log.test +++ b/mysql-test/suite/rpl/t/rpl_stm_log.test @@ -1,5 +1,5 @@ # Requires statement logging --- source include/have_binlog_format_mixed_or_statement.inc +-- source include/have_binlog_format_statement.inc -- source include/master-slave.inc let $engine_type=MyISAM; -- source extra/rpl_tests/rpl_log.test diff --git a/mysql-test/t/delete.test b/mysql-test/t/delete.test index 5a0e86568f3..ea5c87babbb 100644 --- a/mysql-test/t/delete.test +++ b/mysql-test/t/delete.test @@ -387,4 +387,17 @@ DELETE FROM t1 WHERE a = 10 OR b = 20 ORDER BY c LIMIT 1; DROP TABLE t1; +--echo # +--echo # Bug #53034: Multiple-table DELETE statements not accepting +--echo # "Access compatibility" syntax +--echo # + +CREATE TABLE t1 (id INT); +CREATE TABLE t2 LIKE t1; +CREATE TABLE t3 LIKE t1; + +DELETE FROM t1.*, test.t2.*, a.* USING t1, t2, t3 AS a; + +DROP TABLE t1, t2, t3; + --echo End of 5.1 tests diff --git a/mysql-test/t/func_group.test b/mysql-test/t/func_group.test index 6dbc8a05789..72a78f612a2 100644 --- a/mysql-test/t/func_group.test +++ b/mysql-test/t/func_group.test @@ -1082,6 +1082,20 @@ select a.f1 as a, b.f4 as b, a.f1 > b.f4 as gt, from t1 a, t1 b; select *, f1 = f2 from t1; drop table t1; + +--echo # +--echo # Bug #54465: assert: field_types == 0 || field_types[field_pos] == +--echo # MYSQL_TYPE_LONGLONG +--echo # + +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1), (2); + +SELECT MAX((SELECT 1 FROM t1 ORDER BY @var LIMIT 1)) m FROM t1 t2, t1 + ORDER BY t1.a; + +DROP TABLE t1; + --echo # --echo End of 5.1 tests diff --git a/mysql-test/t/func_time.test b/mysql-test/t/func_time.test index b9da946a55f..e764906c374 100644 --- a/mysql-test/t/func_time.test +++ b/mysql-test/t/func_time.test @@ -838,4 +838,15 @@ select date_sub("0069-01-01 00:00:01",INTERVAL 2 SECOND); select date_sub("0169-01-01 00:00:01",INTERVAL 2 SECOND); +# +# Bug #55565: debug assertion when ordering by expressions with user +# variable assignments +# + +CREATE TABLE t1(a DOUBLE NOT NULL); +INSERT INTO t1 VALUES (0),(9.216e-096); +--echo # should not crash +SELECT 1 FROM t1 ORDER BY @x:=makedate(a,a); +DROP TABLE t1; + --echo End of 5.1 tests diff --git a/mysql-test/t/gis.test b/mysql-test/t/gis.test index bc0695aaa93..fd0a18ab4dd 100644 --- a/mysql-test/t/gis.test +++ b/mysql-test/t/gis.test @@ -401,6 +401,7 @@ create table t1 (a int, b blob); insert into t1 values (1, ''), (2, NULL), (3, '1'); select * from t1; +--error ER_ILLEGAL_VALUE_FOR_TYPE select geometryfromtext(b) IS NULL, geometryfromwkb(b) IS NULL, astext(b) IS NULL, aswkb(b) IS NULL, geometrytype(b) IS NULL, centroid(b) IS NULL, @@ -419,6 +420,7 @@ select intersects(b, b) IS NULL, crosses(b, b) IS NULL from t1; +--error ER_ILLEGAL_VALUE_FOR_TYPE select point(b, b) IS NULL, linestring(b) IS NULL, polygon(b) IS NULL, multipoint(b) IS NULL, multilinestring(b) IS NULL, multipolygon(b) IS NULL, @@ -702,24 +704,44 @@ drop table t1; # Bug#44684: valgrind reports invalid reads in # Item_func_spatial_collection::val_str # +--error ER_ILLEGAL_VALUE_FOR_TYPE SELECT MultiPoint(12345,''); -SELECT MultiPoint(123451,''); -SELECT MultiPoint(1234512,''); -SELECT MultiPoint(12345123,''); - -SELECT MultiLineString(12345,''); -SELECT MultiLineString(123451,''); -SELECT MultiLineString(1234512,''); -SELECT MultiLineString(12345123,''); - -SELECT LineString(12345,''); -SELECT LineString(123451,''); -SELECT LineString(1234512,''); -SELECT LineString(12345123,''); - -SELECT Polygon(12345,''); -SELECT Polygon(123451,''); -SELECT Polygon(1234512,''); -SELECT Polygon(12345123,''); +#SELECT MultiPoint(123451,''); +#SELECT MultiPoint(1234512,''); +#SELECT MultiPoint(12345123,''); + +--error ER_ILLEGAL_VALUE_FOR_TYPE +#SELECT MultiLineString(12345,''); +#SELECT MultiLineString(123451,''); +#SELECT MultiLineString(1234512,''); +#SELECT MultiLineString(12345123,''); + +--error ER_ILLEGAL_VALUE_FOR_TYPE +#SELECT LineString(12345,''); +#SELECT LineString(123451,''); +#SELECT LineString(1234512,''); +#SELECT LineString(12345123,''); + +--error ER_ILLEGAL_VALUE_FOR_TYPE +#SELECT Polygon(12345,''); +#SELECT Polygon(123451,''); +#SELECT Polygon(1234512,''); +#SELECT Polygon(12345123,''); + +# +# Bug55531 crash with conversions of geometry types / strings +# +--error ER_ILLEGAL_VALUE_FOR_TYPE +SELECT 1 FROM (SELECT GREATEST(1,GEOMETRYCOLLECTION('00000','00000')) b FROM DUAL) AS d WHERE (LINESTRING(d.b)); + + +--echo # +--echo # BUG#51875: crash when loading data into geometry function polyfromwkb +--echo # +SET @a=0x00000000030000000100000000000000000000000000144000000000000014400000000000001840000000000000184000000000000014400000000000001440; +SET @a=POLYFROMWKB(@a); +SET @a=0x00000000030000000000000000000000000000000000144000000000000014400000000000001840000000000000184000000000000014400000000000001440; +SET @a=POLYFROMWKB(@a); + --echo End of 5.1 tests diff --git a/mysql-test/t/insert.test b/mysql-test/t/insert.test index 8f9ed6c7d06..152f43a671d 100644 --- a/mysql-test/t/insert.test +++ b/mysql-test/t/insert.test @@ -499,3 +499,28 @@ DROP TABLE t1, t2; --echo End of 5.0 tests. + +--echo # +--echo # Bug#54106 assert in Protocol::end_statement, +--echo # INSERT IGNORE ... SELECT ... UNION SELECT ... +--echo # + +--disable_warnings +DROP TABLE IF EXISTS t1; +--enable_warnings + +CREATE TABLE t1 (a INT); + +--error ER_FIELD_SPECIFIED_TWICE +INSERT INTO t1 (a, a) VALUES (1, 1); +# Verify that ER_FIELD_SPECIFIED_TWICE is not ignorable +--error ER_FIELD_SPECIFIED_TWICE +INSERT IGNORE t1 (a, a) VALUES (1, 1); + +--error ER_FIELD_SPECIFIED_TWICE +INSERT IGNORE t1 (a, a) SELECT 1,1; +# Used to cause an assert +--error ER_FIELD_SPECIFIED_TWICE +INSERT IGNORE t1 (a, a) SELECT 1,1 UNION SELECT 2,2; + +DROP TABLE t1; diff --git a/mysql-test/t/join.test b/mysql-test/t/join.test index 43b373c9703..05d630edfb2 100644 --- a/mysql-test/t/join.test +++ b/mysql-test/t/join.test @@ -851,4 +851,74 @@ ENGINE=MERGE UNION=(t1,t2); SELECT t1.a FROM mm1,t1; DROP TABLE t1, t2, mm1; +#--echo # +#--echo # Bug #55568: user variable assignments crash server when used within +#--echo # query +#--echo # +# +# +# This test case is invalidated because of fix of bug 55531 +# The reason is that {1} is not a valid geometric collection. +# +#CREATE TABLE t1 (a INT); + +#INSERT INTO t1 VALUES (0), (1); + +#let $i=2; +#while ($i) +#{ +# SELECT MULTIPOINT( +# 1, +# ( +# SELECT MULTIPOINT( +# MULTIPOINT( +# 1, +# (SELECT COUNT(*) FROM (SELECT 1 FROM t1 GROUP BY a,a) d) +# ) +# ) FROM t1 +# ) +# ) != COUNT(*) q FROM t1 GROUP BY a; +# dec $i; +#} +# +#DROP TABLE t1; + +--echo # +--echo # Bug #54468: crash after item's print() function when ordering/grouping +--echo # by subquery +--echo # + +CREATE TABLE t1(a INT, b INT); +INSERT INTO t1 VALUES (), (); + +SELECT 1 FROM t1 +GROUP BY +GREATEST(t1.a, + (SELECT 1 FROM + (SELECT t1.b FROM t1,t1 t2 + ORDER BY t1.a, t1.a LIMIT 1) AS d) + ); + +DROP TABLE t1; + +--echo # +--echo # Bug #53544: Server hangs during JOIN query in stored procedure called +--echo # twice in a row +--echo # + +CREATE TABLE t1(c INT); + +INSERT INTO t1 VALUES (1), (2); + +PREPARE stmt FROM "SELECT t2.c AS f1 FROM t1 LEFT JOIN + t1 t2 ON t1.c=t2.c RIGHT JOIN + t1 t3 ON t1.c=t3.c + GROUP BY f1;"; + +EXECUTE stmt; +EXECUTE stmt; + +DEALLOCATE PREPARE stmt; +DROP TABLE t1; + --echo End of 5.1 tests diff --git a/mysql-test/t/mysqlbinlog.test b/mysql-test/t/mysqlbinlog.test index 7c9fbf031bb..783708cc3b2 100644 --- a/mysql-test/t/mysqlbinlog.test +++ b/mysql-test/t/mysqlbinlog.test @@ -1,6 +1,6 @@ # We are using .opt file since we need small binlog size # TODO: Need to look at making a row based version once the new row based client is completed. [jbm] --- source include/have_binlog_format_mixed_or_statement.inc +-- source include/have_binlog_format_statement.inc -- source include/have_log_bin.inc diff --git a/mysql-test/t/partition_not_blackhole-master.opt b/mysql-test/t/partition_not_blackhole-master.opt new file mode 100644 index 00000000000..1e47be930bc --- /dev/null +++ b/mysql-test/t/partition_not_blackhole-master.opt @@ -0,0 +1 @@ +--loose-skip-blackhole diff --git a/mysql-test/t/partition_not_blackhole.test b/mysql-test/t/partition_not_blackhole.test new file mode 100644 index 00000000000..7352aeaa230 --- /dev/null +++ b/mysql-test/t/partition_not_blackhole.test @@ -0,0 +1,26 @@ +--source include/have_partition.inc +--source include/not_blackhole.inc + +--disable_warnings +DROP TABLE IF EXISTS t1; +--enable_warnings + +let $MYSQLD_DATADIR= `SELECT @@datadir`; + +--echo # +--echo # Bug#46086: crash when dropping a partitioned table and +--echo # the original engine is disabled +--echo # Copy a .frm and .par file which was created with: +--echo # create table `t1` (`id` int primary key) engine=blackhole +--echo # partition by key () partitions 1; +--copy_file std_data/parts/t1_blackhole.frm $MYSQLD_DATADIR/test/t1.frm +--copy_file std_data/parts/t1_blackhole.par $MYSQLD_DATADIR/test/t1.par +SHOW TABLES; +--replace_result $MYSQLD_DATADIR ./ +--error ER_NOT_FORM_FILE +SHOW CREATE TABLE t1; +--error ER_BAD_TABLE_ERROR +DROP TABLE t1; +--list_files $MYSQLD_DATADIR/test t1* +--remove_file $MYSQLD_DATADIR/test/t1.frm +--remove_file $MYSQLD_DATADIR/test/t1.par diff --git a/mysql-test/t/partition_range.test b/mysql-test/t/partition_range.test index 4d011ddc468..f386fac4998 100644 --- a/mysql-test/t/partition_range.test +++ b/mysql-test/t/partition_range.test @@ -846,3 +846,44 @@ insert into t2 values(52, 20070322, 456, 'filler') ; select sum(count) from t2 ch where ch.defid in (50,52) and ch.day between 20070320 and 20070401 group by defid; drop table t1, t2; + +--echo # +--echo # Bug#50939: Loose Index Scan unduly relies on engine to remember range +--echo # endpoints +--echo # +CREATE TABLE t1 ( + a INT, + b INT, + KEY ( a, b ) +) PARTITION BY HASH (a) PARTITIONS 1; + +CREATE TABLE t2 ( + a INT, + b INT, + KEY ( a, b ) +); + +INSERT INTO t1 VALUES (1, 1), (2, 2), (3, 3), (4, 4), (5, 5); + +INSERT INTO t1 SELECT a + 5, b + 5 FROM t1; +INSERT INTO t1 SELECT a + 10, b + 10 FROM t1; +INSERT INTO t1 SELECT a + 20, b + 20 FROM t1; +INSERT INTO t1 SELECT a + 40, b + 40 FROM t1; + +INSERT INTO t2 SELECT * FROM t1; + +--echo # plans should be identical +EXPLAIN SELECT a, MAX(b) FROM t1 WHERE a IN (10,100) GROUP BY a; +EXPLAIN SELECT a, MAX(b) FROM t2 WHERE a IN (10,100) GROUP BY a; + +FLUSH status; +SELECT a, MAX(b) FROM t1 WHERE a IN (10, 100) GROUP BY a; +--echo # Should be no more than 4 reads. +SHOW status LIKE 'handler_read_key'; + +FLUSH status; +SELECT a, MAX(b) FROM t2 WHERE a IN (10, 100) GROUP BY a; +--echo # Should be no more than 4 reads. +SHOW status LIKE 'handler_read_key'; + +DROP TABLE t1, t2; diff --git a/mysql-test/t/range.test b/mysql-test/t/range.test index 2d2a6f75d73..0ad3d3e8504 100644 --- a/mysql-test/t/range.test +++ b/mysql-test/t/range.test @@ -1314,44 +1314,15 @@ SELECT * FROM t1 FORCE INDEX (PRIMARY) DROP TABLE t1; --echo # ---echo # Bug#50939: Loose Index Scan unduly relies on engine to remember range ---echo # endpoints +--echo # Bug #54802: 'NOT BETWEEN' evaluation is incorrect --echo # -CREATE TABLE t1 ( - a INT, - b INT, - KEY ( a, b ) -) PARTITION BY HASH (a) PARTITIONS 1; - -CREATE TABLE t2 ( - a INT, - b INT, - KEY ( a, b ) -); - -INSERT INTO t1 VALUES (1, 1), (2, 2), (3, 3), (4, 4), (5, 5); -INSERT INTO t1 SELECT a + 5, b + 5 FROM t1; -INSERT INTO t1 SELECT a + 10, b + 10 FROM t1; -INSERT INTO t1 SELECT a + 20, b + 20 FROM t1; -INSERT INTO t1 SELECT a + 40, b + 40 FROM t1; +CREATE TABLE t1 (c_key INT, c_notkey INT, KEY(c_key)); +INSERT INTO t1 VALUES (1, 1), (2, 2), (3, 3); -INSERT INTO t2 SELECT * FROM t1; +EXPLAIN SELECT * FROM t1 WHERE 2 NOT BETWEEN c_notkey AND c_key; +SELECT * FROM t1 WHERE 2 NOT BETWEEN c_notkey AND c_key; ---echo # plans should be identical -EXPLAIN SELECT a, MAX(b) FROM t1 WHERE a IN (10,100) GROUP BY a; -EXPLAIN SELECT a, MAX(b) FROM t2 WHERE a IN (10,100) GROUP BY a; - -FLUSH status; -SELECT a, MAX(b) FROM t1 WHERE a IN (10, 100) GROUP BY a; ---echo # Should be no more than 4 reads. -SHOW status LIKE 'handler_read_key'; - -FLUSH status; -SELECT a, MAX(b) FROM t2 WHERE a IN (10, 100) GROUP BY a; ---echo # Should be no more than 4 reads. -SHOW status LIKE 'handler_read_key'; - -DROP TABLE t1, t2; +DROP TABLE t1; --echo End of 5.1 tests diff --git a/mysql-test/t/subselect4.test b/mysql-test/t/subselect4.test index 440eca22828..2c6efdbaac2 100644 --- a/mysql-test/t/subselect4.test +++ b/mysql-test/t/subselect4.test @@ -62,3 +62,19 @@ FROM t3 WHERE 1 = 0 GROUP BY 1; DROP TABLE t1,t2,t3; --echo End of 5.0 tests. + +--echo # +--echo # Bug#54568: create view cause Assertion failed: 0, +--echo # file .\item_subselect.cc, line 836 +--echo # +EXPLAIN SELECT 1 LIKE ( 1 IN ( SELECT 1 ) ); +DESCRIBE SELECT 1 LIKE ( 1 IN ( SELECT 1 ) ); +--echo # None of the below should crash +CREATE VIEW v1 AS SELECT 1 LIKE ( 1 IN ( SELECT 1 ) ); +CREATE VIEW v2 AS SELECT 1 LIKE '%' ESCAPE ( 1 IN ( SELECT 1 ) ); +DROP VIEW v1, v2; + + +--echo # +--echo # End of 5.1 tests. +--echo # diff --git a/mysql-test/t/timezone2.test b/mysql-test/t/timezone2.test index 15ddceb8d68..c4445da107c 100644 --- a/mysql-test/t/timezone2.test +++ b/mysql-test/t/timezone2.test @@ -273,5 +273,14 @@ UNLOCK TABLES; DROP TABLE t1; +--echo # +--echo # Bug #55424: convert_tz crashes when fed invalid data +--echo # + +CREATE TABLE t1 (a SET('x') NOT NULL); +INSERT INTO t1 VALUES (''); +SELECT CONVERT_TZ(1, a, 1) FROM t1; +SELECT CONVERT_TZ(1, 1, a) FROM t1; +DROP TABLE t1; --echo End of 5.1 tests diff --git a/mysql-test/t/user_var.test b/mysql-test/t/user_var.test index b6b58e7bd46..4f27866de23 100644 --- a/mysql-test/t/user_var.test +++ b/mysql-test/t/user_var.test @@ -328,4 +328,22 @@ INSERT INTO t1 VALUES (1); INSERT INTO t1 VALUES (1); DROP TABLE t1; +# +# Bug #55615: debug assertion after using variable in assignment and +# referred to +# Bug #55564: crash with user variables, assignments, joins... +# + +CREATE TABLE t1(a INT); +INSERT INTO t1 VALUES (0),(0); +--echo # BUG#55615 : should not crash +SELECT (@a:=(SELECT @a:=1 FROM t1 LIMIT 1)) AND COUNT(1) FROM t1 GROUP BY @a; +--echo # BUG#55564 : should not crash +SELECT IF( + @v:=LEAST((SELECT 1 FROM t1 t2 LEFT JOIN t1 ON (@v) GROUP BY t1.a), a), + count(*), 1) +FROM t1 GROUP BY a LIMIT 1; + +DROP TABLE t1; + --echo End of 5.1 tests |