MDEV-7635: Update tests to adapt to the new default sql_mode

16 files changed, 87 insertions, 57 deletions

diff --git a/mysql-test/include/add_anonymous_users.inc b/mysql-test/include/add_anonymous_users.inc
index a59aa971d59..635a80e3dee 100644
--- a/mysql-test/include/add_anonymous_users.inc
+++ b/mysql-test/include/add_anonymous_users.inc
@@ -1,7 +1,7 @@
 # Allow anonymous users to connect 
 disable_warnings;
 disable_query_log;
-INSERT INTO mysql.user (host, user) VALUES ('localhost','');
+INSERT IGNORE INTO mysql.user (host, user) VALUES ('localhost','');
 FLUSH PRIVILEGES;
 enable_query_log;
 enable_warnings;
diff --git a/mysql-test/include/commit.inc b/mysql-test/include/commit.inc
index 830ffb52c02..a28d3e5f3d1 100644
--- a/mysql-test/include/commit.inc
+++ b/mysql-test/include/commit.inc
@@ -267,7 +267,7 @@ select * from t2;
 insert into t2 (a) values (1026);
 --replace_result $MYSQLTEST_VARDIR ..
 --error ER_DUP_ENTRY
-eval load data infile "../../std_data/words.dat" into table t1 (a) set a:=f2(26);
+eval load data infile "../../std_data/words.dat" ignore into table t1 (a) set a:=f2(26);
 
 select * from t2;
 rollback;
diff --git a/mysql-test/include/ctype_common.inc b/mysql-test/include/ctype_common.inc
index 4cb04599679..a39c25f2cc8 100644
--- a/mysql-test/include/ctype_common.inc
+++ b/mysql-test/include/ctype_common.inc
@@ -58,7 +58,7 @@ DROP TABLE t1;
 # Bug #32726: crash with cast in order by clause and cp932 charset
 #
 create table t1 (a set('a') not null);
-insert into t1 values (),();
+insert ignore into t1 values (),();
 select cast(a as char(1)) from t1;
 select a sounds like a from t1;
 select 1 from t1 order by cast(a as char(1));
diff --git a/mysql-test/include/ctype_numconv.inc b/mysql-test/include/ctype_numconv.inc
index 6bda2205c0e..07298b1d47b 100644
--- a/mysql-test/include/ctype_numconv.inc
+++ b/mysql-test/include/ctype_numconv.inc
@@ -739,6 +739,7 @@ show create table t1;
 drop table t1;
 
 select hex(concat(period_diff(200902, 200802)));
+SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR
 create table t1 as select concat(period_add(200902, 200802)) as c1;
 show create table t1;
 drop table t1;
diff --git a/mysql-test/include/ctype_unescape.inc b/mysql-test/include/ctype_unescape.inc
index 5d67bf8d189..171843ff66a 100644
--- a/mysql-test/include/ctype_unescape.inc
+++ b/mysql-test/include/ctype_unescape.inc
@@ -1,5 +1,7 @@
 --echo # Start of ctype_unescape.inc
 
+SET sql_mode = '';
+
 #
 # Testing how string literals with backslash and quote-quote are unescaped.
 # The tests assume that single quote (') is used as a delimiter.
@@ -323,5 +325,6 @@ DROP FUNCTION wellformedness;
 DROP FUNCTION mysql_real_escape_string_generated;
 DROP FUNCTION iswellformed;
 DROP TABLE allbytes;
+SET sql_mode = DEFAULT;
 
 --echo # End of ctype_backslash.inc
diff --git a/mysql-test/include/ctype_utf8mb4.inc b/mysql-test/include/ctype_utf8mb4.inc
index 71993a946b3..2d48f9373f6 100644
--- a/mysql-test/include/ctype_utf8mb4.inc
+++ b/mysql-test/include/ctype_utf8mb4.inc
@@ -124,7 +124,7 @@ set LC_TIME_NAMES='en_US';
 #
 set names koi8r;
 eval create table t1 (s1 char(1) character set utf8mb4) engine $engine;
-insert into t1 values (_koi8r'ÁÂ');
+insert ignore into t1 values (_koi8r'ÁÂ');
 select s1,hex(s1),char_length(s1),octet_length(s1) from t1;
 drop table t1;
 
@@ -136,11 +136,11 @@ if ($is_heap)
 {
 eval create table t1 (s1 varchar(255) character set utf8mb4) engine $engine;
 }
-insert into t1 select repeat('a',300);
-insert into t1 select repeat('Ñ',300);
-insert into t1 select repeat('aÑ',300);
-insert into t1 select repeat('Ña',300);
-insert into t1 select repeat('ÑÑ',300);
+insert ignore into t1 select repeat('a',300);
+insert ignore into t1 select repeat('Ñ',300);
+insert ignore into t1 select repeat('aÑ',300);
+insert ignore into t1 select repeat('Ña',300);
+insert ignore into t1 select repeat('ÑÑ',300);
 --sorted_result
 select hex(s1) from t1;
 --sorted_result
@@ -155,11 +155,11 @@ if ($is_heap)
 {
 eval create table t1 (s1 varchar(255) character set utf8mb4) engine $engine;
 }
-insert into t1 select repeat('a',66000);
-insert into t1 select repeat('Ñ',66000);
-insert into t1 select repeat('aÑ',66000);
-insert into t1 select repeat('Ña',66000);
-insert into t1 select repeat('ÑÑ',66000);
+insert ignore into t1 select repeat('a',66000);
+insert ignore into t1 select repeat('Ñ',66000);
+insert ignore into t1 select repeat('aÑ',66000);
+insert ignore into t1 select repeat('Ña',66000);
+insert ignore into t1 select repeat('ÑÑ',66000);
 --sorted_result
 select length(s1),char_length(s1) from t1;
 drop table t1;
@@ -168,19 +168,19 @@ drop table t1;
 # Bug #2368 Multibyte charsets do not check that incoming data is well-formed
 #
 eval create table t1 (s1 char(10) character set utf8mb4) engine $engine;
-insert into t1 values (0x41FF);
+insert ignore into t1 values (0x41FF);
 select hex(s1) from t1;
 drop table t1;
 
 eval create table t1 (s1 varchar(10) character set utf8mb4) engine $engine;
-insert into t1 values (0x41FF);
+insert ignore into t1 values (0x41FF);
 select hex(s1) from t1;
 drop table t1;
 
 if (!$is_heap)
 {
 eval create table t1 (s1 text character set utf8mb4) engine $engine;
-insert into t1 values (0x41FF);
+insert ignore into t1 values (0x41FF);
 select hex(s1) from t1;
 drop table t1;
 }
@@ -895,6 +895,7 @@ DROP TABLE t1,t2;
 #
 eval create table t1 (a char(20) character set utf8mb4) engine $engine;
 insert into t1 values ('123456'),('андрей');
+SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR
 alter table t1 modify a char(2) character set utf8mb4;
 select char_length(a), length(a), a from t1 order by a;
 drop table t1;
@@ -1282,15 +1283,19 @@ eval create table t1 (
 insert into t1 (a) values ('abcdefghijklmnopqrstuvwxyz');
 select * from t1;
 # varchar to varchar
+SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR
 alter table t1 change a a varchar(20) character set utf8mb4 not null;
 select * from t1;
 # varchar to char
+SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR
 alter table t1 change a a char(15) character set utf8mb4 not null;
 select * from t1;
 # char to char
+SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR
 alter table t1 change a a char(10) character set utf8mb4 not null;
 select * from t1;
 # char to varchar
+SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR
 alter table t1 change a a varchar(5) character set utf8mb4 not null;
 select * from t1;
 drop table t1;
@@ -1302,6 +1307,7 @@ eval create table t1 (
   a varchar(4000) not null
 ) default character set utf8mb4 engine $engine;
 insert into t1 values (repeat('a',4000));
+SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR
 alter table t1 change a a varchar(3000) character set utf8mb4 not null;
 select length(a) from t1;
 drop table t1;
@@ -1552,7 +1558,7 @@ eval create table t1 (utf8mb4 char(1) character set utf8mb4) engine $engine;
 --echo Testing [F0][90..BF][80..BF][80..BF]
 insert into t1 values (0xF0908080);
 insert into t1 values (0xF0BFBFBF);
-insert into t1 values (0xF08F8080);
+insert ignore into t1 values (0xF08F8080);
 --sorted_result
 select hex(utf8mb4) from t1;
 delete from t1;
@@ -1567,7 +1573,7 @@ delete from t1;
 --echo Testing [F4][80..8F][80..BF][80..BF]
 insert into t1 values (0xF4808080);
 insert into t1 values (0xF48F8080);
-insert into t1 values (0xF4908080);
+insert ignore into t1 values (0xF4908080);
 --sorted_result
 select hex(utf8mb4) from t1;
 drop table t1;
@@ -1648,13 +1654,13 @@ INSERT INTO t1 VALUES (119070, x'f09d849ef09d859ef09d859ef09d8480f09d859ff09d859
 #  Mix of 3-byte and 4-byte chars 
 INSERT INTO t1 VALUES (65131, x'efb9abf09d849ef09d859ef09d859ef09d8480f09d859fefb9abefb9abf09d85a0efb9ab');
 # All from musical chars, but 11 instead of 10 chars. truncated
-INSERT INTO t1 VALUES (119070, x'f09d849ef09d859ef09d859ef09d8480f09d859ff09d859ff09d859ff09d85a0f09d85a0f09d8480f09d85a0');
+INSERT IGNORE INTO t1 VALUES (119070, x'f09d849ef09d859ef09d859ef09d8480f09d859ff09d859ff09d859ff09d85a0f09d85a0f09d8480f09d85a0');
 
 --sorted_result
 SELECT u_decimal, hex(utf8mb4_encoding) FROM t1 ORDER BY utf8mb4_encoding COLLATE utf8mb4_general_ci, BINARY utf8mb4_encoding;
 
 # First invalid 4 byte value
-INSERT INTO t1 VALUES (1114111, x'f5808080');
+INSERT IGNORE INTO t1 VALUES (1114111, x'f5808080');
 
 --sorted_result
 SELECT character_maximum_length, character_octet_length FROM information_schema.columns WHERE
@@ -1672,14 +1678,14 @@ INSERT INTO t2 VALUES (42856, x'ea9da8');
 # SMALL COMMERCIAL AT
 INSERT INTO t2 VALUES (65131, x'efb9ab');
 # <Plane 16 Private Use, Last> (last 4 byte character)
-INSERT INTO t2 VALUES (1114111, x'f48fbfbf');
+INSERT IGNORE INTO t2 VALUES (1114111, x'f48fbfbf');
 
 --sorted_result
 SELECT character_maximum_length, character_octet_length FROM information_schema.columns WHERE
        table_name= 't2' AND column_name= 'utf8mb3_encoding';
 
 #  Update a 3-byte char col with a 4-byte char, error
-UPDATE t2 SET utf8mb3_encoding= x'f48fbfbd' where u_decimal= 42856;
+UPDATE IGNORE t2 SET utf8mb3_encoding= x'f48fbfbd' where u_decimal= 42856;
 
 #  Update to a 3-byte char casted to 4-byte, error?
 UPDATE t2 SET utf8mb3_encoding= _utf8mb4 x'ea9da8' where u_decimal= 42856;
@@ -1700,6 +1706,7 @@ SELECT count(*) FROM t1, t2
 
 # Alter from 4-byte charset to 3-byte charset, error
 --disable_warnings
+SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR
 ALTER TABLE t1 CONVERT TO CHARACTER SET utf8;
 --enable_warnings
 SHOW CREATE TABLE t1;
diff --git a/mysql-test/include/func_hybrid_type.inc b/mysql-test/include/func_hybrid_type.inc
index 854c7ebd34e..0eed936db38 100644
--- a/mysql-test/include/func_hybrid_type.inc
+++ b/mysql-test/include/func_hybrid_type.inc
@@ -62,6 +62,7 @@ SELECT
 FROM t1;
 SHOW CREATE TABLE t2;
 DROP TABLE t2;
+SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR
 CREATE TABLE t2 AS
 SELECT
   CASE WHEN a IS NOT NULL THEN a ELSE b END AS case_____a_b,
diff --git a/mysql-test/include/function_defaults.inc b/mysql-test/include/function_defaults.inc
index 7c5d43b6e74..23822aa24dc 100644
--- a/mysql-test/include/function_defaults.inc
+++ b/mysql-test/include/function_defaults.inc
@@ -835,10 +835,10 @@ SELECT 1, 2 INTO OUTFILE 't5.dat' FROM dual;
 --echo # Mon Aug 1 15:11:19 2011 UTC
 SET TIMESTAMP = 1312211479.918273;
 
-LOAD DATA INFILE 't3.dat' INTO TABLE t1;
+LOAD DATA INFILE 't3.dat' IGNORE INTO TABLE t1;
 --query_vertical SELECT * FROM t1
 
-LOAD DATA INFILE 't4.dat' INTO TABLE t2;
+LOAD DATA INFILE 't4.dat' IGNORE INTO TABLE t2;
 SELECT a FROM t2;
 SELECT b FROM t2;
 SELECT c FROM t2;
@@ -866,7 +866,7 @@ DELETE FROM t2;
 --echo # (read_fixed_length()) than under the LOAD ... INTO TABLE t1 command
 --echo # above. The code in this path is copy-pasted code from the path taken
 --echo # under the syntax used in the previous LOAD command.
-LOAD DATA INFILE 't3.dat' INTO TABLE t1
+LOAD DATA INFILE 't3.dat' IGNORE INTO TABLE t1
 FIELDS TERMINATED BY '' ENCLOSED BY '';
 
 SELECT b FROM t1;
@@ -954,7 +954,7 @@ CREATE TABLE t1 (
 --echo # There is no promotion
 SHOW CREATE TABLE t1;
 
-LOAD DATA INFILE "file1.dat" INTO table t1;
+LOAD DATA INFILE "file1.dat" IGNORE INTO table t1;
 
 --echo # It is strange that "like_b" gets NULL when "b" gets 0. But
 --echo # this is consistent with how "a" gets NULL when "b" gets 0,
@@ -971,7 +971,7 @@ modify f TIMESTAMP NULL default CURRENT_TIMESTAMP;
 --echo # There is no promotion
 SHOW CREATE TABLE t1;
 
-LOAD DATA INFILE "file1.dat" INTO table t1;
+LOAD DATA INFILE "file1.dat" IGNORE INTO table t1;
 
 --query_vertical SELECT * FROM t1
 delete from t1;
@@ -1007,7 +1007,7 @@ CREATE TABLE t1 (
 --echo # There is no promotion
 SHOW CREATE TABLE t1;
 
-LOAD DATA INFILE "file2.dat" INTO table t1;
+LOAD DATA INFILE "file2.dat" IGNORE INTO table t1;
 
 --query_vertical SELECT * FROM t1
 delete from t1;
@@ -1018,7 +1018,7 @@ modify f TIMESTAMP NULL default CURRENT_TIMESTAMP;
 --echo # There is no promotion
 SHOW CREATE TABLE t1;
 
-LOAD DATA INFILE "file2.dat" INTO table t1;
+LOAD DATA INFILE "file2.dat" IGNORE INTO table t1;
 
 --query_vertical SELECT * FROM t1
 delete from t1;
diff --git a/mysql-test/include/icp_tests.inc b/mysql-test/include/icp_tests.inc
index 88d6923d8f9..f29088887d0 100644
--- a/mysql-test/include/icp_tests.inc
+++ b/mysql-test/include/icp_tests.inc
@@ -509,7 +509,7 @@ CREATE TABLE t2 (
 );
 
 --disable_warnings
-INSERT INTO t2 VALUES ();
+INSERT IGNORE INTO t2 VALUES ();
 INSERT INTO t1 VALUES ();
 --enable_warnings
 
@@ -610,7 +610,7 @@ CREATE TABLE t1 (
   KEY idx4267 (col1000, col1003)
 );
 
-INSERT INTO t1 VALUES (),();
+INSERT IGNORE INTO t1 VALUES (),();
 SELECT col999 FROM t1 WHERE col1000 = "3" AND col1003 <=> sysdate();
 
 DROP TABLE t1;
@@ -888,7 +888,7 @@ DROP TABLE t1;
 # MDEV-308 lp:1008516 - Failing assertion: templ->mysql_col_len == len
 #
 create table t1 (a int,b char(5),primary key (a), key (b(1)));
-insert into t1 values ('a','b');
+insert ignore into t1 values ('a','b');
 select 1 from t1 where a and b >= 'aa';
 drop table t1;
 
diff --git a/mysql-test/include/loaddata_autocom.inc b/mysql-test/include/loaddata_autocom.inc
index 3bf88fefa6d..7b0c67d2ca8 100644
--- a/mysql-test/include/loaddata_autocom.inc
+++ b/mysql-test/include/loaddata_autocom.inc
@@ -11,13 +11,13 @@ let $load_file= $MYSQLTEST_VARDIR/std_data/loaddata2.dat;
 create table t1 (id int unsigned not null auto_increment primary key, a text, b text);
 start transaction;
 --replace_result $load_file LOAD_FILE
-eval load data infile '$load_file' into table t1 fields terminated by ',' enclosed by '''' (a, b);
+eval load data infile '$load_file' ignore into table t1 fields terminated by ',' enclosed by '''' (a, b);
 commit;
 select count(*) from t1;
 truncate table t1;
 start transaction;
 --replace_result $load_file LOAD_FILE
-eval load data infile '$load_file' into table t1 fields terminated by ',' enclosed by '''' (a, b);
+eval load data infile '$load_file' ignore into table t1 fields terminated by ',' enclosed by '''' (a, b);
 rollback;
 select count(*) from t1;
 
diff --git a/mysql-test/include/mix1.inc b/mysql-test/include/mix1.inc
index ea7bc3f5327..6a47b39e5bc 100644
--- a/mysql-test/include/mix1.inc
+++ b/mysql-test/include/mix1.inc
@@ -573,14 +573,14 @@ INSERT INTO t1(stat_id,acct_id) VALUES
 INSERT INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1;
 INSERT INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1;
 INSERT INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1;
-INSERT INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1;
-INSERT INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1;
-INSERT INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1;
-INSERT INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1;
-INSERT INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1;
-INSERT INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1;
-INSERT INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1;
-INSERT INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1;
+INSERT IGNORE INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1;
+INSERT IGNORE INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1;
+INSERT IGNORE INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1;
+INSERT IGNORE INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1;
+INSERT IGNORE INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1;
+INSERT IGNORE INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1;
+INSERT IGNORE INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1;
+INSERT IGNORE INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1;
 UPDATE t1 SET acct_id=785 
   WHERE MOD(stat_id,2)=0 AND MOD(id,stat_id)=MOD(acct_id,stat_id);
 OPTIMIZE TABLE t1;
@@ -987,6 +987,7 @@ DROP TABLE t1;
 #
 create table t1(a text) engine=innodb default charset=utf8; 
 insert into t1 values('aaa');
+set statement sql_mode = 'NO_ENGINE_SUBSTITUTION' for
 alter table t1 add index(a(1024));
 show create table t1;
 drop table t1;
@@ -1558,6 +1559,7 @@ if ($test_foreign_keys)
 #             auto_increment keys
 #
 create table t1 (a int auto_increment primary key) engine=innodb;
+set statement sql_mode = 'NO_ENGINE_SUBSTITUTION' for
 alter table t1 order by a;
 drop table t1;
 
diff --git a/mysql-test/include/mix2.inc b/mysql-test/include/mix2.inc
index 24097514c21..53c1ffd36a3 100644
--- a/mysql-test/include/mix2.inc
+++ b/mysql-test/include/mix2.inc
@@ -1470,9 +1470,11 @@ source include/varchar.inc;
 --replace_result \\ / $MYSQL_TEST_DIR . /var/mysqld.1/data/ / t1.frm t1
 create table t1 (v varchar(65530), key(v));
 drop table t1;
+SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR
 create table t1 (v varchar(65536));
 show create table t1;
 drop table t1;
+SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR
 create table t1 (v varchar(65530) character set utf8);
 show create table t1;
 drop table t1;
diff --git a/mysql-test/include/ps_conv.inc b/mysql-test/include/ps_conv.inc
index 2e42542d19a..d31f41ef39d 100644
--- a/mysql-test/include/ps_conv.inc
+++ b/mysql-test/include/ps_conv.inc
@@ -576,6 +576,7 @@ prepare my_delete from "delete from t9 where c21 = 'O' ";
 #
 # Use the maximum BIGINT from the manual
 set @arg00= 9223372036854775807 ;
+set statement sql_mode = '' for
 execute my_insert using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
                         @arg00, @arg00, @arg00, @arg00, @arg00 ;
 --vertical_results
@@ -585,6 +586,7 @@ execute my_select ;
 --replace_result e+0 e+
 execute my_delete ;
 set @arg00= '9223372036854775807' ;
+set statement sql_mode = '' for
 execute my_insert using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
                         @arg00, @arg00, @arg00, @arg00, @arg00 ;
 --vertical_results
@@ -596,6 +598,7 @@ execute my_delete ;
 # Use the minimum BIGINT from the manual
 #
 set @arg00= -9223372036854775808 ;
+set statement sql_mode = '' for
 execute my_insert using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
                         @arg00, @arg00, @arg00, @arg00, @arg00 ;
 --vertical_results
@@ -605,6 +608,7 @@ execute my_select ;
 --replace_result e+0 e+
 execute my_delete ;
 set @arg00= '-9223372036854775808' ;
+set statement sql_mode = '' for
 execute my_insert using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
                         @arg00, @arg00, @arg00, @arg00, @arg00 ;
 --vertical_results
@@ -618,6 +622,7 @@ execute my_delete ;
 # (FLOAT,REAL,DOUBLE) during insert
 #
 set @arg00= 1.11111111111111111111e+50 ;
+set statement sql_mode = '' for
 execute my_insert using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
                         @arg00, @arg00, @arg00, @arg00, @arg00 ;
 --vertical_results
@@ -630,6 +635,7 @@ execute my_delete ;
 #             because the string is treated as written integer and
 #             '.11111111111111111111e+50' is cut away.
 set @arg00= '1.11111111111111111111e+50' ;
+set statement sql_mode = '' for
 execute my_insert using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
                         @arg00, @arg00, @arg00, @arg00, @arg00 ;
 --vertical_results
@@ -639,6 +645,7 @@ execute my_select ;
 --replace_result e+0 e+
 execute my_delete ;
 set @arg00= -1.11111111111111111111e+50 ;
+set statement sql_mode = '' for
 execute my_insert using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
                         @arg00, @arg00, @arg00, @arg00, @arg00 ;
 --vertical_results
@@ -651,6 +658,7 @@ execute my_delete ;
 #             because the string is treated as written integer and
 #             '.11111111111111111111e+50' is cut away.
 set @arg00= '-1.11111111111111111111e+50' ;
+set statement sql_mode = '' for
 execute my_insert using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
                         @arg00, @arg00, @arg00, @arg00, @arg00 ;
 --vertical_results
@@ -672,6 +680,7 @@ select '-- insert into string columns --' as test_sequence ;
 --enable_query_log
 
 ######## INSERT into .. string columns values(CHAR(n),LONGTEXT) ########
+set sql_mode = '';
 insert into t9 
   ( c1, c20, c21, c22, c23, c24, c25, c26, c27, c28, c29, c30 )
 values
@@ -803,6 +812,7 @@ values
   ( 57, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )" ;
 execute stmt2 using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, 
                     @arg00, @arg00, @arg00, @arg00 ;
+set sql_mode = default;
 
 
 ######## INSERT into .. string columns values(LONGBLOB,NULL) ########
@@ -984,6 +994,7 @@ delete from t9 ;
 select '-- insert into date/time columns --' as test_sequence ;
 --enable_query_log
 ######## INSERT into .. date/time columns values(VARCHAR(19),LONGTEXT) ########
+set sql_mode = '';
 set @arg00= '1991-01-01 01:01:01' ;
 insert into t9
   ( c1, c13, c14, c15, c16, c17 )
@@ -1079,6 +1090,7 @@ prepare stmt2 from "insert into t9
 values
   ( 53, ?, ?, ?, ?, ? )" ;
 execute stmt2 using @arg00, @arg00, @arg00, @arg00, @arg00 ;
+set sql_mode = default;
 
 
 ######## INSERT into .. date/time columns values(LONGBLOB,NULL) ########
diff --git a/mysql-test/include/ps_modify.inc b/mysql-test/include/ps_modify.inc
index f66f888261d..d4cd4dbe346 100644
--- a/mysql-test/include/ps_modify.inc
+++ b/mysql-test/include/ps_modify.inc
@@ -108,7 +108,9 @@ execute stmt1 using @arg00, @arg01;
 select a,b from t1 where a=@arg00;
 set @arg00=NULL;
 set @arg01=2;
+set sql_mode = '';
 execute stmt1 using @arg00, @arg01;
+set sql_mode = default;
 select a,b from t1 order by a;
 set @arg00=0;
 execute stmt1 using @arg01, @arg00;
diff --git a/mysql-test/include/type_hrtime.inc b/mysql-test/include/type_hrtime.inc
index 071dc7b161d..55fd3d665fe 100644
--- a/mysql-test/include/type_hrtime.inc
+++ b/mysql-test/include/type_hrtime.inc
@@ -15,7 +15,7 @@ insert t1 values ('2010-12-11 00:20:03.1234');
 insert t1 values ('2010-12-11 15:47:11.1234');
 insert t1 values (20101211010203.45678);
 insert t1 values (20101211030405.789e0);
-insert t1 values (99991231235959e1);
+insert ignore t1 values (99991231235959e1);
 select * from t1;
 --replace_regex /121000/121094/ /457000/457031/ /789000/789062/
 select truncate(a, 6) from t1; # Field::val_real()
@@ -50,7 +50,7 @@ select a, a+interval 9876543 microsecond from t1;
 update t1 set a=a+interval 9876543 microsecond;
 select * from t1;
 select a, a + interval 2 year from t1;
-insert t1 select a + interval 2 year from t1;
+insert ignore t1 select a + interval 2 year from t1;
 select * from t1;
 delete from t1 where a < 20110101;
 select * from t1;
diff --git a/mysql-test/include/world.inc b/mysql-test/include/world.inc
index eae6556c422..1e81c5c1aa7 100644
--- a/mysql-test/include/world.inc
+++ b/mysql-test/include/world.inc
@@ -4,7 +4,7 @@
 
 # Table Country
 
-INSERT INTO Country VALUES
+INSERT IGNORE INTO Country VALUES
 ('AFG','Afghanistan',652090.00,22720000,1),
 ('NLD','Netherlands',41526.00,15864000,5),
 ('ANT','Netherlands Antilles',800.00,217000,33),
@@ -649,7 +649,7 @@ INSERT INTO City VALUES
 (398,'Franco da Rocha','BRA',108964),
 (399,'Teixeira de Freitas','BRA',108441),
 (400,'Varginha','BRA',108314);
-INSERT INTO City VALUES
+INSERT IGNORE INTO City VALUES
 (401,'Ribeirão Pires','BRA',108121),
 (402,'Sabará','BRA',107781),
 (403,'Catanduva','BRA',107761),
@@ -1855,7 +1855,7 @@ INSERT INTO City VALUES
 (1598,'Aomori','JPN',295969),
 (1599,'Hakodate','JPN',294788),
 (1600,'Akashi','JPN',292253);
-INSERT INTO City VALUES
+INSERT IGNORE INTO City VALUES
 (1601,'Yokkaichi','JPN',288173),
 (1602,'Fukushima','JPN',287525),
 (1603,'Morioka','JPN',287353),
@@ -2056,7 +2056,7 @@ INSERT INTO City VALUES
 (1798,'Subotica','YUG',100386),
 (1799,'Prizren','YUG',92303),
 (1800,'Phnom Penh','KHM',570155);
-INSERT INTO City VALUES
+INSERT IGNORE INTO City VALUES
 (1801,'Battambang','KHM',129800),
 (1802,'Siem Reap','KHM',105100),
 (1803,'Douala','CMR',1448300),
@@ -2659,7 +2659,7 @@ INSERT INTO City VALUES
 (2398,'Namwon','KOR',103544),
 (2399,'Tonghae','KOR',95472),
 (2400,'Mun-gyong','KOR',92239);
-INSERT INTO City VALUES
+INSERT IGNORE INTO City VALUES
 (2401,'Athenai','GRC',772072),
 (2402,'Thessaloniki','GRC',383967),
 (2403,'Pireus','GRC',182671),
@@ -3463,7 +3463,7 @@ INSERT INTO City VALUES
 (3198,'Dakar','SEN',785071),
 (3199,'Thiès','SEN',248000),
 (3200,'Kaolack','SEN',199000);
-INSERT INTO City VALUES
+INSERT IGNORE INTO City VALUES
 (3201,'Ziguinchor','SEN',192000),
 (3202,'Rufisque','SEN',150000),
 (3203,'Saint-Louis','SEN',132400),
@@ -3664,7 +3664,7 @@ INSERT INTO City VALUES
 (3398,'Çorlu','TUR',123300),
 (3399,'Isparta','TUR',121911),
 (3400,'Karabük','TUR',118285);
-INSERT INTO City VALUES
+INSERT IGNORE INTO City VALUES
 (3401,'Kilis','TUR',118245),
 (3402,'Alanya','TUR',117300),
 (3403,'Kiziltepe','TUR',112000),
@@ -3865,7 +3865,7 @@ INSERT INTO City VALUES
 (3598,'Izevsk','RUS',652800),
 (3599,'Krasnodar','RUS',639000),
 (3600,'Jaroslavl','RUS',616700);
-INSERT INTO City VALUES
+INSERT IGNORE INTO City VALUES
 (3601,'Habarovsk','RUS',609400),
 (3602,'Vladivostok','RUS',606200),
 (3603,'Irkutsk','RUS',593700),
@@ -4350,7 +4350,7 @@ INSERT INTO City VALUES
 
 # Table CountryLanguage
 
-INSERT INTO CountryLanguage VALUES 
+INSERT IGNORE INTO CountryLanguage VALUES 
 ('AFG','Pashto',52.4),
 ('NLD','Dutch',95.6),
 ('ANT','Papiamento',86.2),
@@ -4551,7 +4551,7 @@ INSERT INTO CountryLanguage VALUES
 ('TJK','Tadzhik',62.2),
 ('TWN','Min',66.7),
 ('TZA','Nyamwesi',21.1);
-INSERT INTO CountryLanguage VALUES 
+INSERT IGNORE INTO CountryLanguage VALUES 
 ('DNK','Danish',93.5),
 ('THA','Thai',52.6),
 ('TGO','Ewe',23.2),
@@ -4752,7 +4752,7 @@ INSERT INTO CountryLanguage VALUES
 ('WSM','Samoan',47.5),
 ('STP','French',0.7),
 ('SEN','Ful',21.7);
-INSERT INTO CountryLanguage VALUES 
+INSERT IGNORE INTO CountryLanguage VALUES 
 ('SYC','English',3.8),
 ('SLE','Temne',31.8),
 ('SGP','Malay',14.1),
@@ -4953,7 +4953,7 @@ INSERT INTO CountryLanguage VALUES
 ('AGO','Luimbe-nganguela',5.4),
 ('ABW','Dutch',5.3),
 ('AUS','Canton Chinese',1.1);
-INSERT INTO CountryLanguage VALUES 
+INSERT IGNORE INTO CountryLanguage VALUES 
 ('AZE','Armenian',2.0),
 ('BGD','Garo',0.1),
 ('BEL','Arabic',1.6),