MDEV-7635: Update tests to adapt to the new default sql_modebb-10.2-mdev7635.savedbb-10.2-mdev7635.final

19 files changed, 136 insertions, 45 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/common-tests.inc b/mysql-test/include/common-tests.inc
index 204b6d4dd6b..e47ee0fcbd8 100644
--- a/mysql-test/include/common-tests.inc
+++ b/mysql-test/include/common-tests.inc
@@ -1773,6 +1773,7 @@ show keys from t2;
 
 drop table t4, t3, t2, t1;
 
+SET sql_mode = 'NO_ENGINE_SUBSTITUTION';
 
 CREATE TABLE t1 (
   cont_nr int(11) NOT NULL auto_increment,
@@ -1824,6 +1825,8 @@ CREATE TABLE t1 (
   KEY p_nr(p_nr,suffix)
 ) ENGINE=MyISAM;
 
+SET sql_mode = DEFAULT;
+
 INSERT INTO t1 VALUES (3359356,405,3359356,'Mustermann Musterfrau',52500,'2000-05-20','workflow','Auftrag erledigt','Originalvertrag eingegangen und geprüft','','privat',1485525,2122316,'+','','N',1909160,'MobilComSuper92000D2',NULL,NULL,'MS9ND2',3,24,'MobilCom Shop Koeln',52500,NULL,'auto',20010202105916,'Mobilfunk','PP','','','');
 INSERT INTO t1 VALUES (3359357,468,3359357,'Mustermann Musterfrau',7001,'2000-05-20','workflow','Auftrag erledigt','Originalvertrag eingegangen und geprüft','','privat',1503580,2139699,'+','','P',1909171,'MobilComSuper9D1T10SFreisprech(Akquise)',NULL,NULL,'MS9NS1',327,24,'MobilCom Intern',7003,NULL,'auto',20010202105916,'Mobilfunk','PP','','','');
 INSERT INTO t1 VALUES (3359358,407,3359358,'Mustermann Musterfrau',7001,'2000-05-20','workflow','Auftrag erledigt','Originalvertrag eingegangen und geprüft','','privat',1501358,2137473,'N','','N',1909159,'MobilComSuper92000D2',NULL,NULL,'MS9ND2',325,24,'MobilCom Intern',7003,NULL,'auto',20010202105916,'Mobilfunk','PP','','','');
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..7b2fa993164 100644
--- a/mysql-test/include/ctype_numconv.inc
+++ b/mysql-test/include/ctype_numconv.inc
@@ -739,7 +739,10 @@ show create table t1;
 drop table t1;
 
 select hex(concat(period_diff(200902, 200802)));
+set sql_mode = 'NO_ENGINE_SUBSTITUTION';
+
 create table t1 as select concat(period_add(200902, 200802)) as c1;
+set sql_mode = default;
 show create table t1;
 drop table t1;
 
@@ -1167,7 +1170,7 @@ show create table t2;
 drop table t1, t2;
 
 create table t1 (a timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);
-insert into t1 values (0);
+insert ignore into t1 values (0);
 insert into t1 values (20010203040506);
 insert into t1 values (19800203040506);
 insert into t1 values ('2001-02-03 04:05:06');
@@ -1479,7 +1482,7 @@ drop table t1;
 drop view v1;
 
 create table t1 (a timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);
-insert into t1 values (0);
+insert ignore into t1 values (0);
 insert into t1 values (20010203040506);
 insert into t1 values (19800203040506);
 insert into t1 values ('2001-02-03 04:05:06');
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..50e8eaead5a 100644
--- a/mysql-test/include/ctype_utf8mb4.inc
+++ b/mysql-test/include/ctype_utf8mb4.inc
@@ -123,6 +123,7 @@ set LC_TIME_NAMES='en_US';
 # Bug #2366  	Wrong utf8mb4 behaviour when data is truncated
 #
 set names koi8r;
+set sql_mode = 'NO_ENGINE_SUBSTITUTION';
 eval create table t1 (s1 char(1) character set utf8mb4) engine $engine;
 insert into t1 values (_koi8r'ÁÂ');
 select s1,hex(s1),char_length(s1),octet_length(s1) from t1;
@@ -163,10 +164,12 @@ insert into t1 select repeat('ÑÑ',66000);
 --sorted_result
 select length(s1),char_length(s1) from t1;
 drop table t1;
+set sql_mode = default;
 
 #
 # Bug #2368 Multibyte charsets do not check that incoming data is well-formed
 #
+set sql_mode = 'NO_ENGINE_SUBSTITUTION';
 eval create table t1 (s1 char(10) character set utf8mb4) engine $engine;
 insert into t1 values (0x41FF);
 select hex(s1) from t1;
@@ -184,6 +187,7 @@ insert into t1 values (0x41FF);
 select hex(s1) from t1;
 drop table t1;
 }
+set sql_mode = default;
 
 #
 # Bug 2699
@@ -850,6 +854,7 @@ eval create table t1 (x enum('A', 'B') default 0) character set utf8mb4 engine $
 #
 
 SET NAMES UTF8;
+SET sql_mode = 'NO_ENGINE_SUBSTITUTION';
 
 eval CREATE TABLE t1 (
   `id` int(20) NOT NULL auto_increment,
@@ -888,6 +893,7 @@ INSERT INTO t2 VALUES ('1234567890',2,'2005-05-24 13:53:25');
 SELECT content, t2.msisdn FROM t1, t2 WHERE t1.msisdn = '1234567890';
 
 DROP TABLE t1,t2;
+SET sql_mode = DEFAULT;
 
 #
 # Bug#11591: CHAR column with utf8mb4 does not work properly
@@ -895,7 +901,9 @@ DROP TABLE t1,t2;
 #
 eval create table t1 (a char(20) character set utf8mb4) engine $engine;
 insert into t1 values ('123456'),('андрей');
+SET sql_mode = 'NO_ENGINE_SUBSTITUTION';
 alter table t1 modify a char(2) character set utf8mb4;
+SET sql_mode = DEFAULT;
 select char_length(a), length(a), a from t1 order by a;
 drop table t1;
 
@@ -1276,6 +1284,7 @@ DROP TABLE t1;
 # Bug#20095 Changing length of VARCHAR field with UTF8
 # collation does not truncate values
 #
+SET sql_mode = 'NO_ENGINE_SUBSTITUTION';
 eval create table t1 (
   a varchar(26) not null
 ) default character set utf8mb4 ENGINE $engine;
@@ -1305,6 +1314,7 @@ insert into t1 values (repeat('a',4000));
 alter table t1 change a a varchar(3000) character set utf8mb4 not null;
 select length(a) from t1;
 drop table t1;
+SET sql_mode = DEFAULT;
 
 #
 #  Bug#10504: Character set does not support traditional mode
@@ -1552,7 +1562,9 @@ 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);
+--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD
 insert into t1 values (0xF08F8080);
+insert ignore into t1 values (0xF08F8080);
 --sorted_result
 select hex(utf8mb4) from t1;
 delete from t1;
@@ -1567,7 +1579,9 @@ delete from t1;
 --echo Testing [F4][80..8F][80..BF][80..BF]
 insert into t1 values (0xF4808080);
 insert into t1 values (0xF48F8080);
+--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD
 insert into t1 values (0xF4908080);
+insert ignore into t1 values (0xF4908080);
 --sorted_result
 select hex(utf8mb4) from t1;
 drop table t1;
@@ -1648,13 +1662,15 @@ 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
+--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD
 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 +1688,18 @@ 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)
+--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD
 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
+--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD
 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,7 +1720,9 @@ SELECT count(*) FROM t1, t2
 
 # Alter from 4-byte charset to 3-byte charset, error
 --disable_warnings
+set sql_mode = 'NO_ENGINE_SUBSTITUTION';
 ALTER TABLE t1 CONVERT TO CHARACTER SET utf8;
+set sql_mode = default;
 --enable_warnings
 SHOW CREATE TABLE t1;
 --sorted_result
diff --git a/mysql-test/include/dbt3_s001.inc b/mysql-test/include/dbt3_s001.inc
index 6a85e7052e9..e0eb3596d90 100644
--- a/mysql-test/include/dbt3_s001.inc
+++ b/mysql-test/include/dbt3_s001.inc
@@ -8989,9 +8989,11 @@ INSERT INTO supplier VALUES
 (8,'Supplier#000000008','9Sq4bBH2FQEmaFOocY45sRTxo6yuoG',17,'27-498-742-3860',7627.85,'carefully express escapades are slyly '),
 (9,'Supplier#000000009','1KhUgZegwM3ua7dsYmekYBsK',10,'20-403-398-8662',5302.37,'slyly regular decoys mold slyly ironic dugouts. requests are carefully-- carefully'),(10,'Supplier#000000010','Saygah3gYWMp72i PY',24,'34-852-489-8585',3891.91,'ironic deposits poach quickly furiously final accounts. carefull');
 
+SET sql_mode = 'NO_ENGINE_SUBSTITUTION';
 CREATE TABLE time_statistics (
   task_name varchar(40) DEFAULT NULL,
   s_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
   e_time timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
   int_time int(11) DEFAULT NULL
 );
+SET sql_mode = DEFAULT;
diff --git a/mysql-test/include/func_hybrid_type.inc b/mysql-test/include/func_hybrid_type.inc
index 854c7ebd34e..05139e249c9 100644
--- a/mysql-test/include/func_hybrid_type.inc
+++ b/mysql-test/include/func_hybrid_type.inc
@@ -39,6 +39,7 @@ FROM t1;
 --horizontal_results
 --disable_metadata
 --enable_ps_protocol
+SET sql_mode = 'NO_ENGINE_SUBSTITUTION';
 CREATE TABLE t2 AS
 SELECT
   a                                         AS ___________a,
@@ -60,8 +61,10 @@ SELECT
   LEAST(b, b)                               AS least____b_b,
   GREATEST(b, b)                            AS greatest_b_b
 FROM t1;
+SET sql_mode = DEFAULT;
 SHOW CREATE TABLE t2;
 DROP TABLE t2;
+SET sql_mode = 'NO_ENGINE_SUBSTITUTION';
 CREATE TABLE t2 AS
 SELECT
   CASE WHEN a IS NOT NULL THEN a ELSE b END AS case_____a_b,
@@ -77,5 +80,6 @@ SELECT
   GREATEST(a, b)                            AS greatest_a_b,
   GREATEST(b, a)                            AS greatest_b_a
 FROM t1;
+SET sql_mode = DEFAULT;
 SHOW CREATE TABLE t2;
 DROP TABLE t2;
diff --git a/mysql-test/include/function_defaults.inc b/mysql-test/include/function_defaults.inc
index 7c5d43b6e74..1d38d7c6843 100644
--- a/mysql-test/include/function_defaults.inc
+++ b/mysql-test/include/function_defaults.inc
@@ -1,4 +1,5 @@
 SET TIME_ZONE = "+00:00";
+SET sql_mode = 'NO_ENGINE_SUBSTITUTION';
 
 --echo #
 --echo # Test of errors for column data types that dont support function
@@ -1168,3 +1169,4 @@ INSERT INTO t1 VALUES ();
 SELECT * FROM t1;
 
 DROP TABLE t1;
+SET sql_mode = DEFAULT;
diff --git a/mysql-test/include/icp_tests.inc b/mysql-test/include/icp_tests.inc
index 88d6923d8f9..01ca9ead034 100644
--- a/mysql-test/include/icp_tests.inc
+++ b/mysql-test/include/icp_tests.inc
@@ -72,7 +72,7 @@ CREATE TABLE t1(
 );
 
 --echo
-INSERT INTO t1 VALUES('0000-00-00', '0000-00-00', '2008-01-04', '2008-01-05');
+INSERT IGNORE INTO t1 VALUES('0000-00-00', '0000-00-00', '2008-01-04', '2008-01-05');
 INSERT INTO t1 VALUES('2007-05-25', '2007-05-25', '2007-05-26', '2007-05-26');
 INSERT INTO t1 VALUES('2008-01-01', NULL        , '2008-01-02', '2008-01-03');
 INSERT INTO t1 VALUES('2008-01-17', NULL        , NULL        , '2009-01-29');
@@ -101,7 +101,7 @@ CREATE TABLE t1(
   UNIQUE INDEX(c2)
 );
 
-INSERT INTO t1 VALUES
+INSERT IGNORE INTO t1 VALUES
   ('0000-00-00 00:00:00','0000-00-00 00:00:00','2008-01-04','2008-01-05 00:00:00'),
   ('1971-01-01 00:00:01','1980-01-01 00:00:01','2009-01-01','2009-01-02 00:00:00'),
   ('1999-01-01 00:00:00','1999-01-01 00:00:00', NULL,        NULL),
@@ -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..fb53580568a 100644
--- a/mysql-test/include/mix1.inc
+++ b/mysql-test/include/mix1.inc
@@ -201,7 +201,7 @@ DROP TABLE t1;
 #
 # Bug #28272: EXPLAIN for SELECT from an empty InnoDB table
 #
-
+SET sql_mode = 'NO_ENGINE_SUBSTITUTION';
 CREATE TABLE t1 (
   a1 decimal(10,0) DEFAULT NULL,
   a2 blob,
@@ -212,7 +212,7 @@ CREATE TABLE t1 (
   a7 tinyblob,
   INDEX idx (a6,a7(239),a5)
 ) ENGINE=InnoDB;
-
+SET sql_mode = DEFAULT;
 EXPLAIN SELECT a4 FROM t1 WHERE
 a6=NULL AND
 a4='UNcT5pIde4I6c2SheTo4gt92OV1jgJCVkXmzyf325R1DwLURkbYHwhydANIZMbKTgdcR5xS';
@@ -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,7 +987,9 @@ DROP TABLE t1;
 #
 create table t1(a text) engine=innodb default charset=utf8; 
 insert into t1 values('aaa');
+SET sql_mode = 'NO_ENGINE_SUBSTITUTION';
 alter table t1 add index(a(1024));
+SET sql_mode = DEFAULT;
 show create table t1;
 drop table t1;
 
@@ -1558,7 +1560,9 @@ if ($test_foreign_keys)
 #             auto_increment keys
 #
 create table t1 (a int auto_increment primary key) engine=innodb;
+SET sql_mode = 'NO_ENGINE_SUBSTITUTION';
 alter table t1 order by a;
+SET sql_mode = DEFAULT;
 drop table t1;
 
 #
diff --git a/mysql-test/include/mix2.inc b/mysql-test/include/mix2.inc
index 24097514c21..83d5a5c54b6 100644
--- a/mysql-test/include/mix2.inc
+++ b/mysql-test/include/mix2.inc
@@ -722,6 +722,7 @@ drop table t1;
 # Test of multi-table-delete
 #
 
+SET sql_mode = 'NO_ENGINE_SUBSTITUTION';
 eval CREATE TABLE t1 (
   number bigint(20) NOT NULL default '0',
   cname char(15) NOT NULL default '',
@@ -735,12 +736,14 @@ eval CREATE TABLE t1 (
   assigned_scps int(11) default '0',
   status tinyint(4) default '0'
 ) ENGINE=$engine_type;
-INSERT INTO t1 VALUES (4077711111,'SeanWheeler',90,2,20020111112846,500,00000000000000,-1,2,3,1);
+SET sql_mode = DEFAULT;
+INSERT IGNORE INTO t1 VALUES (4077711111,'SeanWheeler',90,2,20020111112846,500,00000000000000,-1,2,3,1);
 INSERT INTO t1 VALUES (9197722223,'berry',90,3,20020111112809,500,20020102114532,501,4,10,0);
-INSERT INTO t1 VALUES (650,'San Francisco',0,0,20011227111336,342,00000000000000,-1,1,24,1);
+INSERT IGNORE INTO t1 VALUES (650,'San Francisco',0,0,20011227111336,342,00000000000000,-1,1,24,1);
 INSERT INTO t1 VALUES (302467,'Sue\'s Subshop',90,3,20020109113241,500,20020102115111,501,7,24,0);
 INSERT INTO t1 VALUES (6014911113,'SudzCarwash',520,1,20020102115234,500,20020102115259,501,33,32768,0);
 INSERT INTO t1 VALUES (333,'tubs',99,2,20020109113440,501,20020109113440,500,3,10,0);
+SET sql_mode = 'NO_ENGINE_SUBSTITUTION';
 eval CREATE TABLE t2 (
   number bigint(20) NOT NULL default '0',
   cname char(15) NOT NULL default '',
@@ -754,9 +757,10 @@ eval CREATE TABLE t2 (
   assigned_scps int(11) default '0',
   status tinyint(4) default '0'
 ) ENGINE=$engine_type;
-INSERT INTO t2 VALUES (4077711111,'SeanWheeler',0,2,20020111112853,500,00000000000000,-1,2,3,1);
+SET sql_mode = DEFAULT;
+INSERT IGNORE INTO t2 VALUES (4077711111,'SeanWheeler',0,2,20020111112853,500,00000000000000,-1,2,3,1);
 INSERT INTO t2 VALUES (9197722223,'berry',90,3,20020111112818,500,20020102114532,501,4,10,0);
-INSERT INTO t2 VALUES (650,'San Francisco',90,0,20020109113158,342,00000000000000,-1,1,24,1);
+INSERT IGNORE INTO t2 VALUES (650,'San Francisco',90,0,20020109113158,342,00000000000000,-1,1,24,1);
 INSERT INTO t2 VALUES (333,'tubs',99,2,20020109113453,501,20020109113453,500,3,10,0);
 select * from t1;
 select * from t2;
@@ -1470,10 +1474,14 @@ 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 sql_mode = 'NO_ENGINE_SUBSTITUTION';
 create table t1 (v varchar(65536));
+SET sql_mode = DEFAULT;
 show create table t1;
 drop table t1;
+SET sql_mode = 'NO_ENGINE_SUBSTITUTION';
 create table t1 (v varchar(65530) character set utf8);
+SET sql_mode = DEFAULT;
 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..fe74f625ff6 100644
--- a/mysql-test/include/ps_conv.inc
+++ b/mysql-test/include/ps_conv.inc
@@ -576,8 +576,10 @@ prepare my_delete from "delete from t9 where c21 = 'O' ";
 #
 # Use the maximum BIGINT from the manual
 set @arg00= 9223372036854775807 ;
+set sql_mode = '';
 execute my_insert using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
                         @arg00, @arg00, @arg00, @arg00, @arg00 ;
+set sql_mode = default;
 --vertical_results
 --replace_result e+0 e+
 execute my_select ;
@@ -585,8 +587,10 @@ execute my_select ;
 --replace_result e+0 e+
 execute my_delete ;
 set @arg00= '9223372036854775807' ;
+set sql_mode = '';
 execute my_insert using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
                         @arg00, @arg00, @arg00, @arg00, @arg00 ;
+set sql_mode = default;
 --vertical_results
 --replace_result e+0 e+
 execute my_select ;
@@ -596,8 +600,10 @@ execute my_delete ;
 # Use the minimum BIGINT from the manual
 #
 set @arg00= -9223372036854775808 ;
+set sql_mode = '';
 execute my_insert using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
                         @arg00, @arg00, @arg00, @arg00, @arg00 ;
+set sql_mode = default;
 --vertical_results
 --replace_result e+0 e+
 execute my_select ;
@@ -605,8 +611,10 @@ execute my_select ;
 --replace_result e+0 e+
 execute my_delete ;
 set @arg00= '-9223372036854775808' ;
+set sql_mode = '';
 execute my_insert using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
                         @arg00, @arg00, @arg00, @arg00, @arg00 ;
+set sql_mode = default;
 --vertical_results
 --replace_result e+0 e+
 execute my_select ;
@@ -618,8 +626,10 @@ execute my_delete ;
 # (FLOAT,REAL,DOUBLE) during insert
 #
 set @arg00= 1.11111111111111111111e+50 ;
+set sql_mode = '';
 execute my_insert using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
                         @arg00, @arg00, @arg00, @arg00, @arg00 ;
+set sql_mode = default;
 --vertical_results
 --replace_result e+0 e+
 execute my_select ;
@@ -630,8 +640,10 @@ execute my_delete ;
 #             because the string is treated as written integer and
 #             '.11111111111111111111e+50' is cut away.
 set @arg00= '1.11111111111111111111e+50' ;
+set sql_mode = '';
 execute my_insert using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
                         @arg00, @arg00, @arg00, @arg00, @arg00 ;
+set sql_mode = default;
 --vertical_results
 --replace_result e+0 e+
 execute my_select ;
@@ -639,8 +651,10 @@ execute my_select ;
 --replace_result e+0 e+
 execute my_delete ;
 set @arg00= -1.11111111111111111111e+50 ;
+set sql_mode = '';
 execute my_insert using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
                         @arg00, @arg00, @arg00, @arg00, @arg00 ;
+set sql_mode = default;
 --vertical_results
 --replace_result e+0 e+
 execute my_select ;
@@ -651,8 +665,10 @@ execute my_delete ;
 #             because the string is treated as written integer and
 #             '.11111111111111111111e+50' is cut away.
 set @arg00= '-1.11111111111111111111e+50' ;
+set sql_mode = '';
 execute my_insert using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00,
                         @arg00, @arg00, @arg00, @arg00, @arg00 ;
+set sql_mode = default;
 --vertical_results
 --replace_result e+0 e+
 execute my_select ;
@@ -672,6 +688,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
@@ -726,7 +743,6 @@ values
 execute stmt2 using @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, @arg00, 
                     @arg00, @arg00, @arg00, @arg00 ;
 
-
 ######## INSERT into .. string columns values(BIGINT(n),BIGINT) ########
 insert into t9 
   ( c1, c20, c21, c22, c23, c24, c25, c26, c27, c28, c29, c30 )
@@ -803,6 +819,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 +1001,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 +1097,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..9dc4d453f60 100644
--- a/mysql-test/include/ps_modify.inc
+++ b/mysql-test/include/ps_modify.inc
@@ -40,7 +40,6 @@
 select '------ delete tests ------' as test_sequence ;
 --enable_query_log
 --source include/ps_renew.inc
-
 ## delete without parameter
 prepare stmt1 from 'delete from t1 where a=2' ;
 execute stmt1;
@@ -108,7 +107,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..1d21a355f65 100644
--- a/mysql-test/include/type_hrtime.inc
+++ b/mysql-test/include/type_hrtime.inc
@@ -15,13 +15,15 @@ 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()
 select a DIV 1 from t1; # Field::val_int()
 select group_concat(distinct a) from t1; # Field::cmp()
+set sql_mode = 'NO_ENGINE_SUBSTITUTION';
 alter table t1 engine=innodb;
+set sql_mode = default;
 select * from t1 order by a;
 select * from t1 order by a+0;
 drop table t1;
@@ -50,7 +52,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;
@@ -77,8 +79,10 @@ show create table t3;
 drop table t1, t2, t3;
 
 # insert, alter with conversion
+set sql_mode = 'NO_ENGINE_SUBSTITUTION';
 --vertical_results
 eval create table t1 (f0_$type $type(0), f1_$type $type(1), f2_$type $type(2), f3_$type $type(3), f4_$type $type(4), f5_$type $type(5), f6_$type $type(6));
+set sql_mode = default;
 insert t1 values ( '2010-11-12 11:14:17.765432', '2010-11-12 11:14:17.765432', '2010-11-12 11:14:17.765432', '2010-11-12 11:14:17.765432', '2010-11-12 11:14:17.765432', '2010-11-12 11:14:17.765432', '2010-11-12 11:14:17.765432');
 select * from t1;
 eval select cast(f0_$type as time(4)) time4_f0_$type, cast(f1_$type as datetime(3)) datetime3_f1_$type, cast(f2_$type as date) date_f2_$type, cast(f4_$type as double) double_f3_$type, cast(f4_$type as decimal(40,5)) decimal5_f4_$type, cast(f5_$type as signed) bigint_f5_$type, cast(f6_$type as char(255)) varchar_f6_$type from t1;
@@ -87,7 +91,9 @@ insert t2 select * from t1;
 select * from t2;
 eval alter table t1 change f0_$type time4_f0_$type time(4), change f1_$type datetime3_f1_$type datetime(3), change f2_$type date_f2_$type date, change f3_$type double_f3_$type double, change f4_$type decimal5_f4_$type decimal(40,5), change f5_$type bigint_f5_$type bigint, change f6_$type varchar_f6_$type varchar(255);
 select * from t1;
+set sql_mode = 'NO_ENGINE_SUBSTITUTION';
 eval alter table t1 modify time4_f0_$type $type(0), modify datetime3_f1_$type $type(1), modify date_f2_$type $type(2), modify double_f3_$type $type(3), modify decimal5_f4_$type $type(4), modify bigint_f5_$type $type(5), modify varchar_f6_$type $type(6);
+set sql_mode = default;
 select * from t1;
 delete from t1;
 insert t1 select * from t2;
@@ -103,7 +109,9 @@ if ($type == timestamp)
 {
   let attr=NOT NULL DEFAULT '0000-00-00 00:00:00.000000';
 }
+set sql_mode = 'NO_ENGINE_SUBSTITUTION';
 eval create table t1 (a $type(6)$attr, b $type(6)$attr);
+set sql_mode = default;
 eval create procedure foo(x $type, y $type(4)) insert into t1 values (x, y);
 call foo('2010-02-03 4:5:6.789123', '2010-02-03 4:5:6.789123');
 select * from t1;
@@ -131,7 +139,9 @@ drop function xyz;
 create view v1 as select * from t1 group by a,b;
 select * from v1;
 show columns from v1;
+set sql_mode = 'NO_ENGINE_SUBSTITUTION';
 create table t2 select * from v1;
+set sql_mode = default;
 show create table t2;
 select * from t2;
 
diff --git a/mysql-test/include/type_temporal_zero_default.inc b/mysql-test/include/type_temporal_zero_default.inc
index 500d25ea18f..66b9b53937c 100644
--- a/mysql-test/include/type_temporal_zero_default.inc
+++ b/mysql-test/include/type_temporal_zero_default.inc
@@ -5,7 +5,9 @@
 # Testing direct INSERT
 
 SET sql_mode=DEFAULT;
+SET sql_mode = 'NO_ENGINE_SUBSTITUTION';
 eval CREATE TABLE t1 (a $type DEFAULT $defval);
+SET sql_mode = DEFAULT;
 SET sql_mode=TRADITIONAL;
 --error ER_TRUNCATED_WRONG_VALUE
 eval INSERT INTO t1 VALUES ($defval);
@@ -19,9 +21,13 @@ SET sql_mode=DEFAULT;
 
 # Testing INSERT .. SELECT
 
+SET sql_mode = 'NO_ENGINE_SUBSTITUTION';
 eval CREATE TABLE t1 (a $type NOT NULL DEFAULT $defval, b $type NOT NULL DEFAULT $defval);
+SET sql_mode = DEFAULT;
+SET sql_mode = 'NO_ENGINE_SUBSTITUTION';
 eval CREATE TABLE t2 (a $type NOT NULL DEFAULT $defval);
-eval INSERT INTO t2 VALUES ($defval);
+SET sql_mode = DEFAULT;
+eval INSERT IGNORE INTO t2 VALUES ($defval);
 SET sql_mode=TRADITIONAL;
 --error ER_INVALID_DEFAULT_VALUE_FOR_FIELD
 INSERT INTO t1 (a) SELECT a FROM t2;
@@ -31,8 +37,10 @@ SET sql_mode=DEFAULT;
 
 # Testing LOAD
 
+SET sql_mode = 'NO_ENGINE_SUBSTITUTION';
 --eval CREATE TABLE t1 (a $type DEFAULT $defval, b $type DEFAULT $defval)
 --eval INSERT INTO t1 VALUES (DEFAULT,DEFAULT);
+SET sql_mode = DEFAULT;
 --replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
 --eval SELECT a INTO OUTFILE '$MYSQLTEST_VARDIR/tmp/mdev-7824.txt' FROM t1
 DELETE FROM t1;
@@ -46,7 +54,9 @@ SET sql_mode=DEFAULT;
 
 # Testing ALTER when an old field default becomes invalid
 # Return an error, even if there is no STRICT_XXX_TABLES set
+SET sql_mode = 'NO_ENGINE_SUBSTITUTION';
 --eval CREATE TABLE t1 (a $type DEFAULT $defval);
+SET sql_mode = DEFAULT;
 SET sql_mode='NO_ZERO_DATE';
 --error ER_INVALID_DEFAULT
 ALTER TABLE t1 ADD b INT NOT NULL;
@@ -64,7 +74,7 @@ SET sql_mode=DEFAULT;
 
 SET sql_mode=DEFAULT;
 --eval CREATE TABLE t1 (a $type);
-INSERT INTO t1 VALUES (0);
+INSERT IGNORE INTO t1 VALUES (0);
 SET sql_mode='TRADITIONAL';
 --error ER_TRUNCATED_WRONG_VALUE
 CREATE TABLE t2 AS 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),