diff options
Diffstat (limited to 'mysql-test/suite/funcs_1')
33 files changed, 121 insertions, 79 deletions
diff --git a/mysql-test/suite/funcs_1/r/innodb_storedproc_02.result b/mysql-test/suite/funcs_1/r/innodb_storedproc_02.result index 8b96e54eefd..7cc0af43700 100644 --- a/mysql-test/suite/funcs_1/r/innodb_storedproc_02.result +++ b/mysql-test/suite/funcs_1/r/innodb_storedproc_02.result @@ -27,7 +27,7 @@ load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t4.txt' into table t6; USE db_storedproc; create table t7 (f1 char(20), f2 char(25), f3 date, f4 int) engine = <engine_to_be_tested>; -load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t7.txt' into table t7; +load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t7.txt' ignore into table t7; Warnings: Warning 1265 Data truncated for column 'f3' at row 1 Warning 1265 Data truncated for column 'f3' at row 2 @@ -41,7 +41,7 @@ Warning 1265 Data truncated for column 'f3' at row 9 Warning 1265 Data truncated for column 'f3' at row 10 create table t8 (f1 char(20), f2 char(25), f3 date, f4 int) engine = <engine_to_be_tested>; -load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t7.txt' into table t8; +load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t7.txt' ignore into table t8; Warnings: Warning 1265 Data truncated for column 'f3' at row 1 Warning 1265 Data truncated for column 'f3' at row 2 @@ -73,6 +73,7 @@ Ensure that the scope of each BEGIN/END compound statement within a stored procedure definition is properly applied -------------------------------------------------------------------------------- DROP PROCEDURE IF EXISTS sp1; +SET sql_mode = ''; CREATE PROCEDURE sp1( ) begin_label: BEGIN declare x char DEFAULT 'x'; @@ -109,6 +110,7 @@ set @v1 = x; set @v2 = y; SELECT '1.2', @v1, @v2; END begin_label// +SET sql_mode = DEFAULT; CALL sp1(); 1.1 x y 1.1 a a @@ -713,6 +715,7 @@ DROP PROCEDURE IF EXISTS sp3; DROP PROCEDURE IF EXISTS sp4; DROP TABLE IF EXISTS temp; CREATE TABLE temp( f1 CHAR, f2 CHAR); +SET sql_mode = '' // CREATE PROCEDURE sp0() BEGIN set @done=0; @@ -752,6 +755,7 @@ set @x=0; insert into temp values('xxx', 'yy'); set @x=1; END// +SET sql_mode = DEFAULT // INSERT INTO temp VALUES('0', NULL); CALL sp0(); Warnings: diff --git a/mysql-test/suite/funcs_1/r/innodb_storedproc_03.result b/mysql-test/suite/funcs_1/r/innodb_storedproc_03.result index fdbe03e17fc..5f3de814d9c 100644 --- a/mysql-test/suite/funcs_1/r/innodb_storedproc_03.result +++ b/mysql-test/suite/funcs_1/r/innodb_storedproc_03.result @@ -27,7 +27,7 @@ load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t4.txt' into table t6; USE db_storedproc; create table t7 (f1 char(20), f2 char(25), f3 date, f4 int) engine = <engine_to_be_tested>; -load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t7.txt' into table t7; +load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t7.txt' ignore into table t7; Warnings: Warning 1265 Data truncated for column 'f3' at row 1 Warning 1265 Data truncated for column 'f3' at row 2 @@ -41,7 +41,7 @@ Warning 1265 Data truncated for column 'f3' at row 9 Warning 1265 Data truncated for column 'f3' at row 10 create table t8 (f1 char(20), f2 char(25), f3 date, f4 int) engine = <engine_to_be_tested>; -load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t7.txt' into table t8; +load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t7.txt' ignore into table t8; Warnings: Warning 1265 Data truncated for column 'f3' at row 1 Warning 1265 Data truncated for column 'f3' at row 2 diff --git a/mysql-test/suite/funcs_1/r/innodb_storedproc_06.result b/mysql-test/suite/funcs_1/r/innodb_storedproc_06.result index 2cf079a75f8..6db6e374ca7 100644 --- a/mysql-test/suite/funcs_1/r/innodb_storedproc_06.result +++ b/mysql-test/suite/funcs_1/r/innodb_storedproc_06.result @@ -29,7 +29,7 @@ load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t4.txt' into table t6; USE db_storedproc; create table t7 (f1 char(20), f2 char(25), f3 date, f4 int) engine = <engine_to_be_tested>; -load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t7.txt' into table t7; +load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t7.txt' ignore into table t7; Warnings: Warning 1265 Data truncated for column 'f3' at row 1 Warning 1265 Data truncated for column 'f3' at row 2 @@ -43,7 +43,7 @@ Warning 1265 Data truncated for column 'f3' at row 9 Warning 1265 Data truncated for column 'f3' at row 10 create table t8 (f1 char(20), f2 char(25), f3 date, f4 int) engine = <engine_to_be_tested>; -load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t7.txt' into table t8; +load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t7.txt' ignore into table t8; Warnings: Warning 1265 Data truncated for column 'f3' at row 1 Warning 1265 Data truncated for column 'f3' at row 2 diff --git a/mysql-test/suite/funcs_1/r/innodb_storedproc_07.result b/mysql-test/suite/funcs_1/r/innodb_storedproc_07.result index a520bdfac36..cf0070144dc 100644 --- a/mysql-test/suite/funcs_1/r/innodb_storedproc_07.result +++ b/mysql-test/suite/funcs_1/r/innodb_storedproc_07.result @@ -27,7 +27,7 @@ load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t4.txt' into table t6; USE db_storedproc; create table t7 (f1 char(20), f2 char(25), f3 date, f4 int) engine = <engine_to_be_tested>; -load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t7.txt' into table t7; +load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t7.txt' ignore into table t7; Warnings: Warning 1265 Data truncated for column 'f3' at row 1 Warning 1265 Data truncated for column 'f3' at row 2 @@ -41,7 +41,7 @@ Warning 1265 Data truncated for column 'f3' at row 9 Warning 1265 Data truncated for column 'f3' at row 10 create table t8 (f1 char(20), f2 char(25), f3 date, f4 int) engine = <engine_to_be_tested>; -load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t7.txt' into table t8; +load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t7.txt' ignore into table t8; Warnings: Warning 1265 Data truncated for column 'f3' at row 1 Warning 1265 Data truncated for column 'f3' at row 2 diff --git a/mysql-test/suite/funcs_1/r/innodb_storedproc_08.result b/mysql-test/suite/funcs_1/r/innodb_storedproc_08.result index de63339ee95..4df88b53a56 100644 --- a/mysql-test/suite/funcs_1/r/innodb_storedproc_08.result +++ b/mysql-test/suite/funcs_1/r/innodb_storedproc_08.result @@ -28,7 +28,7 @@ load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t4.txt' into table t6; USE db_storedproc; create table t7 (f1 char(20), f2 char(25), f3 date, f4 int) engine = <engine_to_be_tested>; -load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t7.txt' into table t7; +load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t7.txt' ignore into table t7; Warnings: Warning 1265 Data truncated for column 'f3' at row 1 Warning 1265 Data truncated for column 'f3' at row 2 @@ -42,7 +42,7 @@ Warning 1265 Data truncated for column 'f3' at row 9 Warning 1265 Data truncated for column 'f3' at row 10 create table t8 (f1 char(20), f2 char(25), f3 date, f4 int) engine = <engine_to_be_tested>; -load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t7.txt' into table t8; +load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t7.txt' ignore into table t8; Warnings: Warning 1265 Data truncated for column 'f3' at row 1 Warning 1265 Data truncated for column 'f3' at row 2 diff --git a/mysql-test/suite/funcs_1/r/innodb_storedproc_10.result b/mysql-test/suite/funcs_1/r/innodb_storedproc_10.result index 0d846a019dd..405b7ab62b0 100644 --- a/mysql-test/suite/funcs_1/r/innodb_storedproc_10.result +++ b/mysql-test/suite/funcs_1/r/innodb_storedproc_10.result @@ -27,7 +27,7 @@ load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t4.txt' into table t6; USE db_storedproc; create table t7 (f1 char(20), f2 char(25), f3 date, f4 int) engine = <engine_to_be_tested>; -load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t7.txt' into table t7; +load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t7.txt' ignore into table t7; Warnings: Warning 1265 Data truncated for column 'f3' at row 1 Warning 1265 Data truncated for column 'f3' at row 2 @@ -41,7 +41,7 @@ Warning 1265 Data truncated for column 'f3' at row 9 Warning 1265 Data truncated for column 'f3' at row 10 create table t8 (f1 char(20), f2 char(25), f3 date, f4 int) engine = <engine_to_be_tested>; -load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t7.txt' into table t8; +load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t7.txt' ignore into table t8; Warnings: Warning 1265 Data truncated for column 'f3' at row 1 Warning 1265 Data truncated for column 'f3' at row 2 diff --git a/mysql-test/suite/funcs_1/r/innodb_trig_03e.result b/mysql-test/suite/funcs_1/r/innodb_trig_03e.result index 4e4716fd873..6d5ebc7a045 100644 --- a/mysql-test/suite/funcs_1/r/innodb_trig_03e.result +++ b/mysql-test/suite/funcs_1/r/innodb_trig_03e.result @@ -486,8 +486,8 @@ create trigger trg1_4 before UPDATE on t1 for each row set new.f1 = 'trig 1_4-yes'; show triggers; Trigger Event Table Statement Timing Created sql_mode Definer character_set_client collation_connection Database Collation -trg1_3 INSERT t1 set new.f1 = 'trig 1_3-yes' BEFORE # NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION test_yesprivs@localhost latin1 latin1_swedish_ci latin1_swedish_ci -trg1_4 UPDATE t1 set new.f1 = 'trig 1_4-yes' BEFORE # NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION test_yesprivs@localhost latin1 latin1_swedish_ci latin1_swedish_ci +trg1_3 INSERT t1 set new.f1 = 'trig 1_3-yes' BEFORE # STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION test_yesprivs@localhost latin1 latin1_swedish_ci latin1_swedish_ci +trg1_4 UPDATE t1 set new.f1 = 'trig 1_4-yes' BEFORE # STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION test_yesprivs@localhost latin1 latin1_swedish_ci latin1_swedish_ci connection no_privs; select current_user; current_user diff --git a/mysql-test/suite/funcs_1/r/innodb_trig_09.result b/mysql-test/suite/funcs_1/r/innodb_trig_09.result index e6a10592424..6664994543b 100644 --- a/mysql-test/suite/funcs_1/r/innodb_trig_09.result +++ b/mysql-test/suite/funcs_1/r/innodb_trig_09.result @@ -185,7 +185,7 @@ a NULL Test 3.5.9.4 7 999 995.240000000000000000000000000000 0 0 0 0 0 0 @tr_var_af_118 @tr_var_af_121 @tr_var_af_122 @tr_var_af_136 @tr_var_af_151 @tr_var_af_163 0 0 0 0 0 0 -Update tb3 Set f122='Test 3.5.9.4-trig', f136=NULL, f151=DEFAULT, f163=NULL +update ignore tb3 Set f122='Test 3.5.9.4-trig', f136=NULL, f151=DEFAULT, f163=NULL where f122='Test 3.5.9.4'; Warnings: Warning 1048 Column 'f136' cannot be null diff --git a/mysql-test/suite/funcs_1/r/innodb_trig_1011ext.result b/mysql-test/suite/funcs_1/r/innodb_trig_1011ext.result index 8e9a654d4ed..f4a6d75fbc0 100644 --- a/mysql-test/suite/funcs_1/r/innodb_trig_1011ext.result +++ b/mysql-test/suite/funcs_1/r/innodb_trig_1011ext.result @@ -163,6 +163,7 @@ Testcase 3.5.10.6: (implemented in trig_frkey.test) Testcase 3.5.10.extra: ---------------------- +set sql_mode = 'NO_ENGINE_SUBSTITUTION'; create table t1_sp (var136 tinyint, var151 decimal) engine = <engine_to_be_used>; create trigger trg before insert on t1_sp for each row set @counter=@counter+1; @@ -199,6 +200,7 @@ count(*) drop procedure trig_sp; drop trigger trg; drop table t1_sp; +set sql_mode = default; Testcase 3.5.11.1 (implemented in trig_perf.test) ------------------------------------------------- @@ -290,7 +292,7 @@ drop table t4; Testcase y.y.y.4: Recursive trigger/SP references ------------------------------------------------- -set @sql_mode='traditional'; +set sql_mode = 'NO_ENGINE_SUBSTITUTION'; create table t1_sp ( count integer, var136 tinyint, @@ -347,6 +349,7 @@ count(*) drop procedure trig_sp; drop trigger trg; drop table t1_sp; +set sql_mode = default; Testcase y.y.y.5: Rollback of nested trigger references ------------------------------------------------------- diff --git a/mysql-test/suite/funcs_1/r/is_cml_innodb.result b/mysql-test/suite/funcs_1/r/is_cml_innodb.result index d8cd7a93664..58718fc5fae 100644 --- a/mysql-test/suite/funcs_1/r/is_cml_innodb.result +++ b/mysql-test/suite/funcs_1/r/is_cml_innodb.result @@ -40,6 +40,9 @@ WHERE table_schema LIKE 'test%' AND CHARACTER_OCTET_LENGTH / CHARACTER_MAXIMUM_LENGTH = 1 ORDER BY CHARACTER_SET_NAME, COLLATION_NAME, COL_CML; COL_CML DATA_TYPE CHARACTER_SET_NAME COLLATION_NAME +Warnings: +Warning 1365 Division by 0 +Warning 1365 Division by 0 SELECT DISTINCT CHARACTER_OCTET_LENGTH / CHARACTER_MAXIMUM_LENGTH AS COL_CML, DATA_TYPE, @@ -56,6 +59,9 @@ COL_CML DATA_TYPE CHARACTER_SET_NAME COLLATION_NAME 2.0000 text ucs2 ucs2_general_ci 2.0000 varchar ucs2 ucs2_general_ci 2.0079 tinytext ucs2 ucs2_general_ci +Warnings: +Warning 1365 Division by 0 +Warning 1365 Division by 0 SELECT DISTINCT CHARACTER_OCTET_LENGTH / CHARACTER_MAXIMUM_LENGTH AS COL_CML, DATA_TYPE, @@ -68,6 +74,11 @@ ORDER BY CHARACTER_SET_NAME, COLLATION_NAME, COL_CML; COL_CML DATA_TYPE CHARACTER_SET_NAME COLLATION_NAME NULL char ucs2 ucs2_general_ci NULL varchar ucs2 ucs2_general_ci +Warnings: +Warning 1365 Division by 0 +Warning 1365 Division by 0 +Warning 1365 Division by 0 +Warning 1365 Division by 0 --> CHAR(0) is allowed (see manual), and here both CHARACHTER_* values --> are 0, which is intended behavior, and the result of 0 / 0 IS NULL SELECT CHARACTER_OCTET_LENGTH / CHARACTER_MAXIMUM_LENGTH AS COL_CML, @@ -94,4 +105,7 @@ NULL test t1 f5 varchar 0 0 ucs2 ucs2_general_ci varchar(0) 2.0079 test t1 f9 tinytext 127 255 ucs2 ucs2_general_ci tinytext 2.0000 test t1 f10 mediumtext 8388607 16777215 ucs2 ucs2_general_ci mediumtext 2.0000 test t1 f11 longtext 2147483647 4294967295 ucs2 ucs2_general_ci longtext +Warnings: +Warning 1365 Division by 0 +Warning 1365 Division by 0 DROP TABLE t1; diff --git a/mysql-test/suite/funcs_1/r/is_columns.result b/mysql-test/suite/funcs_1/r/is_columns.result index 89f29ff4b38..63c6e11f20a 100644 --- a/mysql-test/suite/funcs_1/r/is_columns.result +++ b/mysql-test/suite/funcs_1/r/is_columns.result @@ -430,8 +430,8 @@ TABLE_SCHEMA test TABLE_NAME t1_my_tablex COLUMN_NAME col2 ORDINAL_POSITION 2 -COLUMN_DEFAULT -IS_NULLABLE NO +COLUMN_DEFAULT NULL +IS_NULLABLE YES DATA_TYPE varchar CHARACTER_MAXIMUM_LENGTH 1 CHARACTER_OCTET_LENGTH 1 diff --git a/mysql-test/suite/funcs_1/r/is_columns_innodb.result b/mysql-test/suite/funcs_1/r/is_columns_innodb.result index a005e39b0b4..f946930bf30 100644 --- a/mysql-test/suite/funcs_1/r/is_columns_innodb.result +++ b/mysql-test/suite/funcs_1/r/is_columns_innodb.result @@ -359,29 +359,9 @@ drop TABLE if exists t7, t8; CREATE TABLE t7 (f1 char(20),f2 char(25),f3 date,f4 int) ENGINE = InnoDB; CREATE TABLE t8 (f1 char(20),f2 char(25),f3 date,f4 int) ENGINE = InnoDB; LOAD DATA INFILE '<MYSQLTEST_VARDIR>/std_data/funcs_1/t7.txt' INTO TABLE t7; -Warnings: -Warning 1265 Data truncated for column 'f3' at row 1 -Warning 1265 Data truncated for column 'f3' at row 2 -Warning 1265 Data truncated for column 'f3' at row 3 -Warning 1265 Data truncated for column 'f3' at row 4 -Warning 1265 Data truncated for column 'f3' at row 5 -Warning 1265 Data truncated for column 'f3' at row 6 -Warning 1265 Data truncated for column 'f3' at row 7 -Warning 1265 Data truncated for column 'f3' at row 8 -Warning 1265 Data truncated for column 'f3' at row 9 -Warning 1265 Data truncated for column 'f3' at row 10 +ERROR 22007: Incorrect date value: '' for column 'f3' at row 1 LOAD DATA INFILE '<MYSQLTEST_VARDIR>/std_data/funcs_1/t7.txt' INTO TABLE t8; -Warnings: -Warning 1265 Data truncated for column 'f3' at row 1 -Warning 1265 Data truncated for column 'f3' at row 2 -Warning 1265 Data truncated for column 'f3' at row 3 -Warning 1265 Data truncated for column 'f3' at row 4 -Warning 1265 Data truncated for column 'f3' at row 5 -Warning 1265 Data truncated for column 'f3' at row 6 -Warning 1265 Data truncated for column 'f3' at row 7 -Warning 1265 Data truncated for column 'f3' at row 8 -Warning 1265 Data truncated for column 'f3' at row 9 -Warning 1265 Data truncated for column 'f3' at row 10 +ERROR 22007: Incorrect date value: '' for column 'f3' at row 1 drop TABLE if exists t9; CREATE TABLE t9 (f1 int, f2 char(25), f3 int) ENGINE = InnoDB; LOAD DATA INFILE '<MYSQLTEST_VARDIR>/std_data/funcs_1/t9.txt' INTO TABLE t9; @@ -743,6 +723,12 @@ COL_CML DATA_TYPE CHARACTER_SET_NAME COLLATION_NAME 1.0000 text latin1 latin1_swedish_ci 1.0000 tinytext latin1 latin1_swedish_ci 1.0000 varchar latin1 latin1_swedish_ci +Warnings: +Warning 1365 Division by 0 +Warning 1365 Division by 0 +Warning 1365 Division by 0 +Warning 1365 Division by 0 +Warning 1365 Division by 0 SELECT DISTINCT CHARACTER_OCTET_LENGTH / CHARACTER_MAXIMUM_LENGTH AS COL_CML, DATA_TYPE, @@ -753,6 +739,12 @@ WHERE table_schema LIKE 'test%' AND CHARACTER_OCTET_LENGTH / CHARACTER_MAXIMUM_LENGTH <> 1 ORDER BY CHARACTER_SET_NAME, COLLATION_NAME, COL_CML; COL_CML DATA_TYPE CHARACTER_SET_NAME COLLATION_NAME +Warnings: +Warning 1365 Division by 0 +Warning 1365 Division by 0 +Warning 1365 Division by 0 +Warning 1365 Division by 0 +Warning 1365 Division by 0 SELECT DISTINCT CHARACTER_OCTET_LENGTH / CHARACTER_MAXIMUM_LENGTH AS COL_CML, DATA_TYPE, @@ -779,6 +771,17 @@ NULL year NULL NULL NULL char latin1 latin1_bin NULL char latin1 latin1_swedish_ci NULL varchar latin1 latin1_swedish_ci +Warnings: +Warning 1365 Division by 0 +Warning 1365 Division by 0 +Warning 1365 Division by 0 +Warning 1365 Division by 0 +Warning 1365 Division by 0 +Warning 1365 Division by 0 +Warning 1365 Division by 0 +Warning 1365 Division by 0 +Warning 1365 Division by 0 +Warning 1365 Division by 0 --> CHAR(0) is allowed (see manual), and here both CHARACHTER_* values --> are 0, which is intended behavior, and the result of 0 / 0 IS NULL SELECT CHARACTER_OCTET_LENGTH / CHARACTER_MAXIMUM_LENGTH AS COL_CML, @@ -1121,6 +1124,12 @@ NULL test4 t6 f3 date NULL NULL NULL NULL date NULL test4 t6 f4 int NULL NULL NULL NULL int(11) 1.0000 test4 t6 f5 char 25 25 latin1 latin1_swedish_ci char(25) NULL test4 t6 f6 int NULL NULL NULL NULL int(11) +Warnings: +Warning 1365 Division by 0 +Warning 1365 Division by 0 +Warning 1365 Division by 0 +Warning 1365 Division by 0 +Warning 1365 Division by 0 DROP DATABASE test1; DROP DATABASE test4; DROP TABLE test.t1; diff --git a/mysql-test/suite/funcs_1/r/memory_storedproc_02.result b/mysql-test/suite/funcs_1/r/memory_storedproc_02.result index 0ee25154c50..2d34240274a 100644 --- a/mysql-test/suite/funcs_1/r/memory_storedproc_02.result +++ b/mysql-test/suite/funcs_1/r/memory_storedproc_02.result @@ -28,7 +28,7 @@ load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t4.txt' into table t6; USE db_storedproc; create table t7 (f1 char(20), f2 char(25), f3 date, f4 int) engine = <engine_to_be_tested>; -load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t7.txt' into table t7; +load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t7.txt' ignore into table t7; Warnings: Warning 1265 Data truncated for column 'f3' at row 1 Warning 1265 Data truncated for column 'f3' at row 2 @@ -42,7 +42,7 @@ Warning 1265 Data truncated for column 'f3' at row 9 Warning 1265 Data truncated for column 'f3' at row 10 create table t8 (f1 char(20), f2 char(25), f3 date, f4 int) engine = <engine_to_be_tested>; -load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t7.txt' into table t8; +load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t7.txt' ignore into table t8; Warnings: Warning 1265 Data truncated for column 'f3' at row 1 Warning 1265 Data truncated for column 'f3' at row 2 @@ -74,6 +74,7 @@ Ensure that the scope of each BEGIN/END compound statement within a stored procedure definition is properly applied -------------------------------------------------------------------------------- DROP PROCEDURE IF EXISTS sp1; +SET sql_mode = ''; CREATE PROCEDURE sp1( ) begin_label: BEGIN declare x char DEFAULT 'x'; @@ -110,6 +111,7 @@ set @v1 = x; set @v2 = y; SELECT '1.2', @v1, @v2; END begin_label// +SET sql_mode = DEFAULT; CALL sp1(); 1.1 x y 1.1 a a @@ -714,6 +716,7 @@ DROP PROCEDURE IF EXISTS sp3; DROP PROCEDURE IF EXISTS sp4; DROP TABLE IF EXISTS temp; CREATE TABLE temp( f1 CHAR, f2 CHAR); +SET sql_mode = '' // CREATE PROCEDURE sp0() BEGIN set @done=0; @@ -753,6 +756,7 @@ set @x=0; insert into temp values('xxx', 'yy'); set @x=1; END// +SET sql_mode = DEFAULT // INSERT INTO temp VALUES('0', NULL); CALL sp0(); Warnings: diff --git a/mysql-test/suite/funcs_1/r/memory_storedproc_03.result b/mysql-test/suite/funcs_1/r/memory_storedproc_03.result index f5e40d190a6..6390cc240f9 100644 --- a/mysql-test/suite/funcs_1/r/memory_storedproc_03.result +++ b/mysql-test/suite/funcs_1/r/memory_storedproc_03.result @@ -28,7 +28,7 @@ load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t4.txt' into table t6; USE db_storedproc; create table t7 (f1 char(20), f2 char(25), f3 date, f4 int) engine = <engine_to_be_tested>; -load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t7.txt' into table t7; +load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t7.txt' ignore into table t7; Warnings: Warning 1265 Data truncated for column 'f3' at row 1 Warning 1265 Data truncated for column 'f3' at row 2 @@ -42,7 +42,7 @@ Warning 1265 Data truncated for column 'f3' at row 9 Warning 1265 Data truncated for column 'f3' at row 10 create table t8 (f1 char(20), f2 char(25), f3 date, f4 int) engine = <engine_to_be_tested>; -load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t7.txt' into table t8; +load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t7.txt' ignore into table t8; Warnings: Warning 1265 Data truncated for column 'f3' at row 1 Warning 1265 Data truncated for column 'f3' at row 2 diff --git a/mysql-test/suite/funcs_1/r/memory_storedproc_06.result b/mysql-test/suite/funcs_1/r/memory_storedproc_06.result index a0a48842ef8..ce1ee82e89a 100644 --- a/mysql-test/suite/funcs_1/r/memory_storedproc_06.result +++ b/mysql-test/suite/funcs_1/r/memory_storedproc_06.result @@ -29,7 +29,7 @@ load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t4.txt' into table t6; USE db_storedproc; create table t7 (f1 char(20), f2 char(25), f3 date, f4 int) engine = <engine_to_be_tested>; -load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t7.txt' into table t7; +load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t7.txt' ignore into table t7; Warnings: Warning 1265 Data truncated for column 'f3' at row 1 Warning 1265 Data truncated for column 'f3' at row 2 @@ -43,7 +43,7 @@ Warning 1265 Data truncated for column 'f3' at row 9 Warning 1265 Data truncated for column 'f3' at row 10 create table t8 (f1 char(20), f2 char(25), f3 date, f4 int) engine = <engine_to_be_tested>; -load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t7.txt' into table t8; +load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t7.txt' ignore into table t8; Warnings: Warning 1265 Data truncated for column 'f3' at row 1 Warning 1265 Data truncated for column 'f3' at row 2 diff --git a/mysql-test/suite/funcs_1/r/memory_storedproc_07.result b/mysql-test/suite/funcs_1/r/memory_storedproc_07.result index 2b8d41f963e..751986b837e 100644 --- a/mysql-test/suite/funcs_1/r/memory_storedproc_07.result +++ b/mysql-test/suite/funcs_1/r/memory_storedproc_07.result @@ -28,7 +28,7 @@ load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t4.txt' into table t6; USE db_storedproc; create table t7 (f1 char(20), f2 char(25), f3 date, f4 int) engine = <engine_to_be_tested>; -load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t7.txt' into table t7; +load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t7.txt' ignore into table t7; Warnings: Warning 1265 Data truncated for column 'f3' at row 1 Warning 1265 Data truncated for column 'f3' at row 2 @@ -42,7 +42,7 @@ Warning 1265 Data truncated for column 'f3' at row 9 Warning 1265 Data truncated for column 'f3' at row 10 create table t8 (f1 char(20), f2 char(25), f3 date, f4 int) engine = <engine_to_be_tested>; -load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t7.txt' into table t8; +load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t7.txt' ignore into table t8; Warnings: Warning 1265 Data truncated for column 'f3' at row 1 Warning 1265 Data truncated for column 'f3' at row 2 diff --git a/mysql-test/suite/funcs_1/r/memory_storedproc_08.result b/mysql-test/suite/funcs_1/r/memory_storedproc_08.result index 1188eedf6c6..1b8a571e481 100644 --- a/mysql-test/suite/funcs_1/r/memory_storedproc_08.result +++ b/mysql-test/suite/funcs_1/r/memory_storedproc_08.result @@ -28,7 +28,7 @@ load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t4.txt' into table t6; USE db_storedproc; create table t7 (f1 char(20), f2 char(25), f3 date, f4 int) engine = <engine_to_be_tested>; -load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t7.txt' into table t7; +load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t7.txt' ignore into table t7; Warnings: Warning 1265 Data truncated for column 'f3' at row 1 Warning 1265 Data truncated for column 'f3' at row 2 @@ -42,7 +42,7 @@ Warning 1265 Data truncated for column 'f3' at row 9 Warning 1265 Data truncated for column 'f3' at row 10 create table t8 (f1 char(20), f2 char(25), f3 date, f4 int) engine = <engine_to_be_tested>; -load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t7.txt' into table t8; +load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t7.txt' ignore into table t8; Warnings: Warning 1265 Data truncated for column 'f3' at row 1 Warning 1265 Data truncated for column 'f3' at row 2 diff --git a/mysql-test/suite/funcs_1/r/memory_storedproc_10.result b/mysql-test/suite/funcs_1/r/memory_storedproc_10.result index bf4fcefb9db..0f5fe7cf34c 100644 --- a/mysql-test/suite/funcs_1/r/memory_storedproc_10.result +++ b/mysql-test/suite/funcs_1/r/memory_storedproc_10.result @@ -28,7 +28,7 @@ load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t4.txt' into table t6; USE db_storedproc; create table t7 (f1 char(20), f2 char(25), f3 date, f4 int) engine = <engine_to_be_tested>; -load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t7.txt' into table t7; +load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t7.txt' ignore into table t7; Warnings: Warning 1265 Data truncated for column 'f3' at row 1 Warning 1265 Data truncated for column 'f3' at row 2 @@ -42,7 +42,7 @@ Warning 1265 Data truncated for column 'f3' at row 9 Warning 1265 Data truncated for column 'f3' at row 10 create table t8 (f1 char(20), f2 char(25), f3 date, f4 int) engine = <engine_to_be_tested>; -load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t7.txt' into table t8; +load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t7.txt' ignore into table t8; Warnings: Warning 1265 Data truncated for column 'f3' at row 1 Warning 1265 Data truncated for column 'f3' at row 2 diff --git a/mysql-test/suite/funcs_1/r/memory_trig_03e.result b/mysql-test/suite/funcs_1/r/memory_trig_03e.result index f9e8fc05088..77ae3c354fe 100644 --- a/mysql-test/suite/funcs_1/r/memory_trig_03e.result +++ b/mysql-test/suite/funcs_1/r/memory_trig_03e.result @@ -487,8 +487,8 @@ create trigger trg1_4 before UPDATE on t1 for each row set new.f1 = 'trig 1_4-yes'; show triggers; Trigger Event Table Statement Timing Created sql_mode Definer character_set_client collation_connection Database Collation -trg1_3 INSERT t1 set new.f1 = 'trig 1_3-yes' BEFORE # NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION test_yesprivs@localhost latin1 latin1_swedish_ci latin1_swedish_ci -trg1_4 UPDATE t1 set new.f1 = 'trig 1_4-yes' BEFORE # NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION test_yesprivs@localhost latin1 latin1_swedish_ci latin1_swedish_ci +trg1_3 INSERT t1 set new.f1 = 'trig 1_3-yes' BEFORE # STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION test_yesprivs@localhost latin1 latin1_swedish_ci latin1_swedish_ci +trg1_4 UPDATE t1 set new.f1 = 'trig 1_4-yes' BEFORE # STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION test_yesprivs@localhost latin1 latin1_swedish_ci latin1_swedish_ci connection no_privs; select current_user; current_user diff --git a/mysql-test/suite/funcs_1/r/memory_trig_09.result b/mysql-test/suite/funcs_1/r/memory_trig_09.result index 5394aa01bf6..7579418dc1f 100644 --- a/mysql-test/suite/funcs_1/r/memory_trig_09.result +++ b/mysql-test/suite/funcs_1/r/memory_trig_09.result @@ -186,7 +186,7 @@ a NULL Test 3.5.9.4 7 999 995.240000000000000000000000000000 0 0 0 0 0 0 @tr_var_af_118 @tr_var_af_121 @tr_var_af_122 @tr_var_af_136 @tr_var_af_151 @tr_var_af_163 0 0 0 0 0 0 -Update tb3 Set f122='Test 3.5.9.4-trig', f136=NULL, f151=DEFAULT, f163=NULL +update ignore tb3 Set f122='Test 3.5.9.4-trig', f136=NULL, f151=DEFAULT, f163=NULL where f122='Test 3.5.9.4'; Warnings: Warning 1048 Column 'f136' cannot be null diff --git a/mysql-test/suite/funcs_1/r/myisam_storedproc_02.result b/mysql-test/suite/funcs_1/r/myisam_storedproc_02.result index 0ee25154c50..2d34240274a 100644 --- a/mysql-test/suite/funcs_1/r/myisam_storedproc_02.result +++ b/mysql-test/suite/funcs_1/r/myisam_storedproc_02.result @@ -28,7 +28,7 @@ load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t4.txt' into table t6; USE db_storedproc; create table t7 (f1 char(20), f2 char(25), f3 date, f4 int) engine = <engine_to_be_tested>; -load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t7.txt' into table t7; +load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t7.txt' ignore into table t7; Warnings: Warning 1265 Data truncated for column 'f3' at row 1 Warning 1265 Data truncated for column 'f3' at row 2 @@ -42,7 +42,7 @@ Warning 1265 Data truncated for column 'f3' at row 9 Warning 1265 Data truncated for column 'f3' at row 10 create table t8 (f1 char(20), f2 char(25), f3 date, f4 int) engine = <engine_to_be_tested>; -load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t7.txt' into table t8; +load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t7.txt' ignore into table t8; Warnings: Warning 1265 Data truncated for column 'f3' at row 1 Warning 1265 Data truncated for column 'f3' at row 2 @@ -74,6 +74,7 @@ Ensure that the scope of each BEGIN/END compound statement within a stored procedure definition is properly applied -------------------------------------------------------------------------------- DROP PROCEDURE IF EXISTS sp1; +SET sql_mode = ''; CREATE PROCEDURE sp1( ) begin_label: BEGIN declare x char DEFAULT 'x'; @@ -110,6 +111,7 @@ set @v1 = x; set @v2 = y; SELECT '1.2', @v1, @v2; END begin_label// +SET sql_mode = DEFAULT; CALL sp1(); 1.1 x y 1.1 a a @@ -714,6 +716,7 @@ DROP PROCEDURE IF EXISTS sp3; DROP PROCEDURE IF EXISTS sp4; DROP TABLE IF EXISTS temp; CREATE TABLE temp( f1 CHAR, f2 CHAR); +SET sql_mode = '' // CREATE PROCEDURE sp0() BEGIN set @done=0; @@ -753,6 +756,7 @@ set @x=0; insert into temp values('xxx', 'yy'); set @x=1; END// +SET sql_mode = DEFAULT // INSERT INTO temp VALUES('0', NULL); CALL sp0(); Warnings: diff --git a/mysql-test/suite/funcs_1/r/myisam_storedproc_03.result b/mysql-test/suite/funcs_1/r/myisam_storedproc_03.result index f5e40d190a6..6390cc240f9 100644 --- a/mysql-test/suite/funcs_1/r/myisam_storedproc_03.result +++ b/mysql-test/suite/funcs_1/r/myisam_storedproc_03.result @@ -28,7 +28,7 @@ load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t4.txt' into table t6; USE db_storedproc; create table t7 (f1 char(20), f2 char(25), f3 date, f4 int) engine = <engine_to_be_tested>; -load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t7.txt' into table t7; +load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t7.txt' ignore into table t7; Warnings: Warning 1265 Data truncated for column 'f3' at row 1 Warning 1265 Data truncated for column 'f3' at row 2 @@ -42,7 +42,7 @@ Warning 1265 Data truncated for column 'f3' at row 9 Warning 1265 Data truncated for column 'f3' at row 10 create table t8 (f1 char(20), f2 char(25), f3 date, f4 int) engine = <engine_to_be_tested>; -load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t7.txt' into table t8; +load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t7.txt' ignore into table t8; Warnings: Warning 1265 Data truncated for column 'f3' at row 1 Warning 1265 Data truncated for column 'f3' at row 2 diff --git a/mysql-test/suite/funcs_1/r/myisam_storedproc_06.result b/mysql-test/suite/funcs_1/r/myisam_storedproc_06.result index a0a48842ef8..ce1ee82e89a 100644 --- a/mysql-test/suite/funcs_1/r/myisam_storedproc_06.result +++ b/mysql-test/suite/funcs_1/r/myisam_storedproc_06.result @@ -29,7 +29,7 @@ load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t4.txt' into table t6; USE db_storedproc; create table t7 (f1 char(20), f2 char(25), f3 date, f4 int) engine = <engine_to_be_tested>; -load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t7.txt' into table t7; +load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t7.txt' ignore into table t7; Warnings: Warning 1265 Data truncated for column 'f3' at row 1 Warning 1265 Data truncated for column 'f3' at row 2 @@ -43,7 +43,7 @@ Warning 1265 Data truncated for column 'f3' at row 9 Warning 1265 Data truncated for column 'f3' at row 10 create table t8 (f1 char(20), f2 char(25), f3 date, f4 int) engine = <engine_to_be_tested>; -load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t7.txt' into table t8; +load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t7.txt' ignore into table t8; Warnings: Warning 1265 Data truncated for column 'f3' at row 1 Warning 1265 Data truncated for column 'f3' at row 2 diff --git a/mysql-test/suite/funcs_1/r/myisam_storedproc_07.result b/mysql-test/suite/funcs_1/r/myisam_storedproc_07.result index 2b8d41f963e..751986b837e 100644 --- a/mysql-test/suite/funcs_1/r/myisam_storedproc_07.result +++ b/mysql-test/suite/funcs_1/r/myisam_storedproc_07.result @@ -28,7 +28,7 @@ load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t4.txt' into table t6; USE db_storedproc; create table t7 (f1 char(20), f2 char(25), f3 date, f4 int) engine = <engine_to_be_tested>; -load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t7.txt' into table t7; +load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t7.txt' ignore into table t7; Warnings: Warning 1265 Data truncated for column 'f3' at row 1 Warning 1265 Data truncated for column 'f3' at row 2 @@ -42,7 +42,7 @@ Warning 1265 Data truncated for column 'f3' at row 9 Warning 1265 Data truncated for column 'f3' at row 10 create table t8 (f1 char(20), f2 char(25), f3 date, f4 int) engine = <engine_to_be_tested>; -load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t7.txt' into table t8; +load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t7.txt' ignore into table t8; Warnings: Warning 1265 Data truncated for column 'f3' at row 1 Warning 1265 Data truncated for column 'f3' at row 2 diff --git a/mysql-test/suite/funcs_1/r/myisam_storedproc_08.result b/mysql-test/suite/funcs_1/r/myisam_storedproc_08.result index 1188eedf6c6..1b8a571e481 100644 --- a/mysql-test/suite/funcs_1/r/myisam_storedproc_08.result +++ b/mysql-test/suite/funcs_1/r/myisam_storedproc_08.result @@ -28,7 +28,7 @@ load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t4.txt' into table t6; USE db_storedproc; create table t7 (f1 char(20), f2 char(25), f3 date, f4 int) engine = <engine_to_be_tested>; -load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t7.txt' into table t7; +load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t7.txt' ignore into table t7; Warnings: Warning 1265 Data truncated for column 'f3' at row 1 Warning 1265 Data truncated for column 'f3' at row 2 @@ -42,7 +42,7 @@ Warning 1265 Data truncated for column 'f3' at row 9 Warning 1265 Data truncated for column 'f3' at row 10 create table t8 (f1 char(20), f2 char(25), f3 date, f4 int) engine = <engine_to_be_tested>; -load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t7.txt' into table t8; +load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t7.txt' ignore into table t8; Warnings: Warning 1265 Data truncated for column 'f3' at row 1 Warning 1265 Data truncated for column 'f3' at row 2 diff --git a/mysql-test/suite/funcs_1/r/myisam_storedproc_10.result b/mysql-test/suite/funcs_1/r/myisam_storedproc_10.result index bf4fcefb9db..0f5fe7cf34c 100644 --- a/mysql-test/suite/funcs_1/r/myisam_storedproc_10.result +++ b/mysql-test/suite/funcs_1/r/myisam_storedproc_10.result @@ -28,7 +28,7 @@ load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t4.txt' into table t6; USE db_storedproc; create table t7 (f1 char(20), f2 char(25), f3 date, f4 int) engine = <engine_to_be_tested>; -load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t7.txt' into table t7; +load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t7.txt' ignore into table t7; Warnings: Warning 1265 Data truncated for column 'f3' at row 1 Warning 1265 Data truncated for column 'f3' at row 2 @@ -42,7 +42,7 @@ Warning 1265 Data truncated for column 'f3' at row 9 Warning 1265 Data truncated for column 'f3' at row 10 create table t8 (f1 char(20), f2 char(25), f3 date, f4 int) engine = <engine_to_be_tested>; -load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t7.txt' into table t8; +load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t7.txt' ignore into table t8; Warnings: Warning 1265 Data truncated for column 'f3' at row 1 Warning 1265 Data truncated for column 'f3' at row 2 diff --git a/mysql-test/suite/funcs_1/r/myisam_trig_03e.result b/mysql-test/suite/funcs_1/r/myisam_trig_03e.result index d1ab5674cb4..ad3871fab29 100644 --- a/mysql-test/suite/funcs_1/r/myisam_trig_03e.result +++ b/mysql-test/suite/funcs_1/r/myisam_trig_03e.result @@ -487,8 +487,8 @@ create trigger trg1_4 before UPDATE on t1 for each row set new.f1 = 'trig 1_4-yes'; show triggers; Trigger Event Table Statement Timing Created sql_mode Definer character_set_client collation_connection Database Collation -trg1_3 INSERT t1 set new.f1 = 'trig 1_3-yes' BEFORE # NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION test_yesprivs@localhost latin1 latin1_swedish_ci latin1_swedish_ci -trg1_4 UPDATE t1 set new.f1 = 'trig 1_4-yes' BEFORE # NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION test_yesprivs@localhost latin1 latin1_swedish_ci latin1_swedish_ci +trg1_3 INSERT t1 set new.f1 = 'trig 1_3-yes' BEFORE # STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION test_yesprivs@localhost latin1 latin1_swedish_ci latin1_swedish_ci +trg1_4 UPDATE t1 set new.f1 = 'trig 1_4-yes' BEFORE # STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION test_yesprivs@localhost latin1 latin1_swedish_ci latin1_swedish_ci connection no_privs; select current_user; current_user diff --git a/mysql-test/suite/funcs_1/r/myisam_trig_09.result b/mysql-test/suite/funcs_1/r/myisam_trig_09.result index 5394aa01bf6..7579418dc1f 100644 --- a/mysql-test/suite/funcs_1/r/myisam_trig_09.result +++ b/mysql-test/suite/funcs_1/r/myisam_trig_09.result @@ -186,7 +186,7 @@ a NULL Test 3.5.9.4 7 999 995.240000000000000000000000000000 0 0 0 0 0 0 @tr_var_af_118 @tr_var_af_121 @tr_var_af_122 @tr_var_af_136 @tr_var_af_151 @tr_var_af_163 0 0 0 0 0 0 -Update tb3 Set f122='Test 3.5.9.4-trig', f136=NULL, f151=DEFAULT, f163=NULL +update ignore tb3 Set f122='Test 3.5.9.4-trig', f136=NULL, f151=DEFAULT, f163=NULL where f122='Test 3.5.9.4'; Warnings: Warning 1048 Column 'f136' cannot be null diff --git a/mysql-test/suite/funcs_1/r/storedproc.result b/mysql-test/suite/funcs_1/r/storedproc.result index 7a27703a1ec..2c86e6c3e73 100644 --- a/mysql-test/suite/funcs_1/r/storedproc.result +++ b/mysql-test/suite/funcs_1/r/storedproc.result @@ -28,7 +28,7 @@ load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t4.txt' into table t6; USE db_storedproc; create table t7 (f1 char(20), f2 char(25), f3 date, f4 int) engine = <engine_to_be_tested>; -load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t7.txt' into table t7; +load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t7.txt' ignore into table t7; Warnings: Warning 1265 Data truncated for column 'f3' at row 1 Warning 1265 Data truncated for column 'f3' at row 2 @@ -42,7 +42,7 @@ Warning 1265 Data truncated for column 'f3' at row 9 Warning 1265 Data truncated for column 'f3' at row 10 create table t8 (f1 char(20), f2 char(25), f3 date, f4 int) engine = <engine_to_be_tested>; -load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t7.txt' into table t8; +load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t7.txt' ignore into table t8; Warnings: Warning 1265 Data truncated for column 'f3' at row 1 Warning 1265 Data truncated for column 'f3' at row 2 diff --git a/mysql-test/suite/funcs_1/storedproc/load_sp_tb.inc b/mysql-test/suite/funcs_1/storedproc/load_sp_tb.inc index c4ae93aca6b..6f23a559ce3 100644 --- a/mysql-test/suite/funcs_1/storedproc/load_sp_tb.inc +++ b/mysql-test/suite/funcs_1/storedproc/load_sp_tb.inc @@ -70,7 +70,7 @@ create table t7 (f1 char(20), f2 char(25), f3 date, f4 int) engine = $engine_type; --replace_result $MYSQLTEST_VARDIR <MYSQLTEST_VARDIR> eval -load data infile '$MYSQLTEST_VARDIR/std_data/funcs_1/t7.txt' into table t7; +load data infile '$MYSQLTEST_VARDIR/std_data/funcs_1/t7.txt' ignore into table t7; --replace_result $engine_type <engine_to_be_tested> eval @@ -78,7 +78,7 @@ create table t8 (f1 char(20), f2 char(25), f3 date, f4 int) engine = $engine_type; --replace_result $MYSQLTEST_VARDIR <MYSQLTEST_VARDIR> eval -load data infile '$MYSQLTEST_VARDIR/std_data/funcs_1/t7.txt' into table t8; +load data infile '$MYSQLTEST_VARDIR/std_data/funcs_1/t7.txt' ignore into table t8; --replace_result $engine_type <engine_to_be_tested> eval diff --git a/mysql-test/suite/funcs_1/storedproc/storedproc_02.inc b/mysql-test/suite/funcs_1/storedproc/storedproc_02.inc index 0d9548830b4..e256ee98515 100644 --- a/mysql-test/suite/funcs_1/storedproc/storedproc_02.inc +++ b/mysql-test/suite/funcs_1/storedproc/storedproc_02.inc @@ -209,7 +209,7 @@ procedure definition is properly applied; --disable_warnings DROP PROCEDURE IF EXISTS sp1; --enable_warnings - +SET sql_mode = ''; delimiter //; CREATE PROCEDURE sp1( ) begin_label: BEGIN @@ -248,6 +248,7 @@ begin_label: BEGIN SELECT '1.2', @v1, @v2; END begin_label// delimiter ;// +SET sql_mode = DEFAULT; CALL sp1(); @@ -993,6 +994,7 @@ DROP TABLE IF EXISTS temp; CREATE TABLE temp( f1 CHAR, f2 CHAR); delimiter //; +SET sql_mode = '' // # 0 - without handler CREATE PROCEDURE sp0() BEGIN @@ -1041,6 +1043,7 @@ BEGIN insert into temp values('xxx', 'yy'); set @x=1; END// +SET sql_mode = DEFAULT // delimiter ;// INSERT INTO temp VALUES('0', NULL); diff --git a/mysql-test/suite/funcs_1/triggers/triggers_09.inc b/mysql-test/suite/funcs_1/triggers/triggers_09.inc index e9e6af92994..93762b363df 100644 --- a/mysql-test/suite/funcs_1/triggers/triggers_09.inc +++ b/mysql-test/suite/funcs_1/triggers/triggers_09.inc @@ -183,7 +183,7 @@ let $message= Testcase 3.5.9.4:; @tr_var_af_136, @tr_var_af_151, @tr_var_af_163; --enable_query_log - Update tb3 Set f122='Test 3.5.9.4-trig', f136=NULL, f151=DEFAULT, f163=NULL + update ignore tb3 Set f122='Test 3.5.9.4-trig', f136=NULL, f151=DEFAULT, f163=NULL where f122='Test 3.5.9.4'; select f118, f121, f122, f136, f151, f163 from tb3 diff --git a/mysql-test/suite/funcs_1/triggers/triggers_1011ext.inc b/mysql-test/suite/funcs_1/triggers/triggers_1011ext.inc index dd8b2a87cd8..88b04a0eefd 100644 --- a/mysql-test/suite/funcs_1/triggers/triggers_1011ext.inc +++ b/mysql-test/suite/funcs_1/triggers/triggers_1011ext.inc @@ -144,7 +144,7 @@ let $message= Testcase 3.5.10.6: (implemented in trig_frkey.test); # on the subject table from a stored procedure is indeed activated correctly let $message= Testcase 3.5.10.extra:; --source include/show_msg.inc - + set sql_mode = 'NO_ENGINE_SUBSTITUTION'; --replace_result $engine_type <engine_to_be_used> eval create table t1_sp (var136 tinyint, var151 decimal) engine = $engine_type; @@ -184,6 +184,7 @@ let $message= Testcase 3.5.10.extra:; drop trigger trg; drop table t1_sp; --enable_warnings + set sql_mode = default; ################################## ########## Section 3.5.11 ######## @@ -321,8 +322,7 @@ let $message= Testcase y.y.y.3: Circular trigger reference; # Testcase: create recursive trigger/storedprocedures conditions let $message= Testcase y.y.y.4: Recursive trigger/SP references; --source include/show_msg.inc - -set @sql_mode='traditional'; + set sql_mode = 'NO_ENGINE_SUBSTITUTION'; --replace_result $engine_type <engine_to_be_used> eval create table t1_sp ( count integer, @@ -375,6 +375,7 @@ set @sql_mode='traditional'; drop trigger trg; drop table t1_sp; --enable_warnings + set sql_mode = default; # Testcase: y.y.y.5: |