From db7561ecf8b703e285320f2e0fcec7daf72ca9d6 Mon Sep 17 00:00:00 2001 From: unknown Date: Fri, 1 Apr 2005 15:04:50 +0300 Subject: Invalid DEFAULT values for CREATE TABLE now generates errors. (Bug #5902) CAST() now produces warnings when casting a wrong INTEGER or CHAR values. This also applies to implicite string to number casts. (Bug #5912) ALTER TABLE now fails in STRICT mode if it generates warnings. Inserting a zero date in a DATE, DATETIME or TIMESTAMP column during TRADITIONAL mode now produces an error. (Bug #5933) mysql-test/r/bigint.result: New warning added mysql-test/r/cast.result: Added testing of wrong CAST's of strings to numbers and numbers to strings mysql-test/r/create.result: Added test for wrong default values (#5902) mysql-test/r/func_if.result: Changed tests to produce less warnings mysql-test/r/func_misc.result: New warning mysql-test/r/func_str.result: Added missing drop table Changed test to produce less warnings New warnings mysql-test/r/ndb_index_unique.result: Removed wrong default usage mysql-test/r/ps_1general.result: Changed tests to produce less warnings mysql-test/r/row.result: New warnings mysql-test/r/rpl_session_var.result: Changed tests to produce less warnings mysql-test/r/strict.result: New tests for CAST() and zero date handling mysql-test/r/subselect.result: Changed tests to produce less warnings mysql-test/r/type_ranges.result: Changed tests to produce less warnings mysql-test/t/cast.test: Added testing of wrong CAST's of strings to numbers and numbers to strings mysql-test/t/create.test: Added test for wrong default values (#5902) mysql-test/t/func_if.test: Changed tests to produce less warnings mysql-test/t/func_str.test: Added missing drop table Changed test to produce less warnings New warnings mysql-test/t/ndb_index_unique.test: Removed wrong default usage mysql-test/t/ps_1general.test: Changed tests to produce less warnings mysql-test/t/rpl_session_var.test: Changed tests to produce less warnings mysql-test/t/strict.test: New tests for CAST() and zero date handling mysql-test/t/subselect.test: Changed tests to produce less warnings mysql-test/t/type_ranges.test: Changed tests to produce less warnings sql/Makefile.am: Added new include file sql/field.cc: Added warnings for zero dates for DATE, DATETIME and TIMESTAMP Moved Field_blob::max_length() to a more appropriate position Changed type for 'level' in set_warning() to avoid casts sql/field.h: Changed type for 'level' in set_warning() to avoid casts sql/field_conv.cc: Copy date and datetime fields through string in 'traditional' mode to detect zero dates sql/item.cc: Removed compiler warnings Give warnings for wrong CAST of strings -> number sql/item.h: Moved Item_string::val_real() and ::val_int() to item.cc sql/item_row.cc: Better detection of null values (which doesn't produce warnings) sql/item_sum.cc: Better detection of null values (which doesn't produce warnings) sql/item_timefunc.cc: Give warnings for wrong CAST of number -> string sql/my_decimal.cc: Fixed typo in comment sql/mysql_priv.h: Removed prototype for static function Moved defines for error handling to sql_error.h (to be able to use these in field.h) sql/mysqld.cc: Simplify code sql/sql_class.h: Moved to sql_error.h sql/sql_load.cc: Removed wrong cast sql/sql_parse.cc: Fixed wrong printf() sql/sql_table.cc: Made mysql_prepare_table() static Changed references to pointers to make code more readable ALTER TABLE now aborts if one gets warnings in STRICT mode sql/time.cc: Fixed possible wrong call sql/unireg.cc: Removed one call to current_thd Give errors if one uses a wrong DEFAULT value --- mysql-test/r/bigint.result | 2 ++ mysql-test/r/cast.result | 68 ++++++++++++++++++++++++++++++++++++ mysql-test/r/create.result | 18 ++++++++-- mysql-test/r/func_if.result | 17 +++++---- mysql-test/r/func_misc.result | 2 ++ mysql-test/r/func_str.result | 10 +++--- mysql-test/r/ndb_index_unique.result | 8 ++--- mysql-test/r/ps_1general.result | 16 ++++----- mysql-test/r/row.result | 5 +++ mysql-test/r/rpl_session_var.result | 6 ++-- mysql-test/r/strict.result | 64 +++++++++++++++++++++++++++++++++ mysql-test/r/subselect.result | 12 ++++--- mysql-test/r/type_ranges.result | 6 ++-- 13 files changed, 198 insertions(+), 36 deletions(-) (limited to 'mysql-test/r') diff --git a/mysql-test/r/bigint.result b/mysql-test/r/bigint.result index 50e99e3ecd7..e372c307ba7 100644 --- a/mysql-test/r/bigint.result +++ b/mysql-test/r/bigint.result @@ -87,6 +87,8 @@ drop table t1; SELECT '0x8000000000000001'+0; '0x8000000000000001'+0 0 +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: '0x8000000000000001' create table t1 ( value64 bigint unsigned not null, value32 integer not null, diff --git a/mysql-test/r/cast.result b/mysql-test/r/cast.result index b015648534b..79d903737bf 100644 --- a/mysql-test/r/cast.result +++ b/mysql-test/r/cast.result @@ -7,6 +7,9 @@ CAST(CAST(1-2 AS UNSIGNED) AS SIGNED INTEGER) select CONVERT('-1',UNSIGNED); CONVERT('-1',UNSIGNED) 18446744073709551615 +select CAST('10 ' as unsigned integer); +CAST('10 ' as unsigned integer) +10 select cast(-5 as unsigned) | 1, cast(-5 as unsigned) & -1; cast(-5 as unsigned) | 1 cast(-5 as unsigned) & -1 18446744073709551611 18446744073709551611 @@ -42,6 +45,15 @@ cast("1:2:3" as TIME) select CONVERT("2004-01-22 21:45:33",DATE); CONVERT("2004-01-22 21:45:33",DATE) 2004-01-22 +select 10+'10'; +10+'10' +20 +select 10.0+'10'; +10.0+'10' +20 +select 10E+0+'10'; +10E+0+'10' +20 select CONVERT(DATE "2004-01-22 21:45:33" USING latin1); CONVERT(DATE "2004-01-22 21:45:33" USING latin1) 2004-01-22 21:45:33 @@ -51,12 +63,43 @@ CONVERT(DATE "2004-01-22 21:45:33",CHAR) select CONVERT(DATE "2004-01-22 21:45:33",CHAR(4)); CONVERT(DATE "2004-01-22 21:45:33",CHAR(4)) 2004 +Warnings: +Warning 1292 Truncated incorrect CHAR(4) value: '2004-01-22 21:45:33' select CONVERT(DATE "2004-01-22 21:45:33",BINARY(4)); CONVERT(DATE "2004-01-22 21:45:33",BINARY(4)) 2004 +Warnings: +Warning 1292 Truncated incorrect CHAR(4) value: '2004-01-22 21:45:33' select CAST(DATE "2004-01-22 21:45:33" AS BINARY(4)); CAST(DATE "2004-01-22 21:45:33" AS BINARY(4)) 2004 +Warnings: +Warning 1292 Truncated incorrect CHAR(4) value: '2004-01-22 21:45:33' +select cast('-10a' as signed integer); +cast('-10a' as signed integer) +-10 +Warnings: +Warning 1292 Truncated incorrect INTEGER value: '-10a' +select cast('a10' as unsigned integer); +cast('a10' as unsigned integer) +0 +Warnings: +Warning 1292 Truncated incorrect INTEGER value: 'a10' +select 10+'a'; +10+'a' +10 +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: 'a' +select 10.0+cast('a' as decimal); +10.0+cast('a' as decimal) +10.00 +Warnings: +Error 1366 Incorrect decimal value: '' for column '' at row -1 +select 10E+0+'a'; +10E+0+'a' +10 +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: 'a' set names binary; select cast(_latin1'test' as char character set latin2); cast(_latin1'test' as char character set latin2) @@ -79,12 +122,23 @@ cast(_latin1'a ' AS char(2)) as c4, cast(_latin1'a' AS char(2)) as c5; c1 c2 c3 c4 c5 ab a ab a a +Warnings: +Warning 1292 Truncated incorrect CHAR(2) value: 'abc' +Warning 1292 Truncated incorrect CHAR(2) value: 'a ' +select cast(1000 as CHAR(3)); +cast(1000 as CHAR(3)) +100 +Warnings: +Warning 1292 Truncated incorrect CHAR(3) value: '1000' create table t1 select cast(_latin1'ab' AS char) as c1, cast(_latin1'a ' AS char) as c2, cast(_latin1'abc' AS char(2)) as c3, cast(_latin1'a ' AS char(2)) as c4, cast(_latin1'a' AS char(2)) as c5; +Warnings: +Warning 1292 Truncated incorrect CHAR(2) value: 'abc' +Warning 1292 Truncated incorrect CHAR(2) value: 'a ' select * from t1; c1 c2 c3 c4 c5 ab a ab a a @@ -106,12 +160,18 @@ cast(_koi8r' cast(_koi8r'Æ' AS nchar(2)) as c5; c1 c2 c3 c4 c5 фг Ñ„ фг Ñ„ Ñ„ +Warnings: +Warning 1292 Truncated incorrect CHAR(4) value: 'фгх' +Warning 1292 Truncated incorrect CHAR(3) value: 'Ñ„ ' create table t1 select cast(_koi8r'ÆÇ' AS nchar) as c1, cast(_koi8r'Æ ' AS nchar) as c2, cast(_koi8r'ÆÇÈ' AS nchar(2)) as c3, cast(_koi8r'Æ ' AS nchar(2)) as c4, cast(_koi8r'Æ' AS nchar(2)) as c5; +Warnings: +Warning 1292 Truncated incorrect CHAR(4) value: 'фгх' +Warning 1292 Truncated incorrect CHAR(3) value: 'Ñ„ ' select * from t1; c1 c2 c3 c4 c5 фг Ñ„ фг Ñ„ Ñ„ @@ -167,6 +227,10 @@ a CAST(a AS CHAR(3)) aac aac aab aab aaa aaa +Warnings: +Warning 1292 Truncated incorrect CHAR(2) value: 'aaa' +Warning 1292 Truncated incorrect CHAR(2) value: 'aab' +Warning 1292 Truncated incorrect CHAR(2) value: 'aac' SELECT a, CAST(a AS UNSIGNED) FROM t1 ORDER BY CAST(a AS CHAR) ; a CAST(a AS UNSIGNED) aaa 3 @@ -177,6 +241,10 @@ a CAST(a AS CHAR(2)) aaa aa aab aa aac aa +Warnings: +Warning 1292 Truncated incorrect CHAR(2) value: 'aaa' +Warning 1292 Truncated incorrect CHAR(2) value: 'aab' +Warning 1292 Truncated incorrect CHAR(2) value: 'aac' DROP TABLE t1; select date_add(cast('2004-12-30 12:00:00' as date), interval 0 hour); date_add(cast('2004-12-30 12:00:00' as date), interval 0 hour) diff --git a/mysql-test/r/create.result b/mysql-test/r/create.result index 1c378f88e5c..a323669ac97 100644 --- a/mysql-test/r/create.result +++ b/mysql-test/r/create.result @@ -44,12 +44,24 @@ create table `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa ERROR 42000: Incorrect table name 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' create table a (`aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa` int); ERROR 42000: Identifier name 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' is too long -create table test (a datetime default now()); +create table t1 (a datetime default now()); ERROR 42000: Invalid default value for 'a' -create table test (a datetime on update now()); +create table t1 (a datetime on update now()); ERROR HY000: Invalid ON UPDATE clause for 'a' column -create table test (a int default 100 auto_increment); +create table t1 (a int default 100 auto_increment); ERROR 42000: Invalid default value for 'a' +create table t1 (a tinyint default 1000); +ERROR 42000: Invalid default value for 'a' +create table t1 (a varchar(5) default 'abcdef'); +ERROR 42000: Invalid default value for 'a' +create table t1 (a varchar(5) default 'abcde'); +insert into t1 values(); +select * from t1; +a +abcde +alter table t1 alter column a set default 'abcdef'; +ERROR 42000: Invalid default value for 'a' +drop table t1; create table 1ea10 (1a20 int,1e int); insert into 1ea10 values(1,1); select 1ea10.1a20,1e+ 1e+10 from 1ea10; diff --git a/mysql-test/r/func_if.result b/mysql-test/r/func_if.result index 0ee7412cbe9..4db31121756 100644 --- a/mysql-test/r/func_if.result +++ b/mysql-test/r/func_if.result @@ -44,21 +44,26 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 7 Using where; Using filesort Warnings: Note 1003 select if((`test`.`t1`.`u` = 1),`test`.`t1`.`st`,cast(`test`.`t1`.`st` as char charset binary)) AS `s` from `test`.`t1` where (`test`.`t1`.`st` like _latin1'%a%') order by if((`test`.`t1`.`u` = 1),`test`.`t1`.`st`,cast(`test`.`t1`.`st` as char charset binary)) -select nullif(u=0, 'test') from t1; -nullif(u=0, 'test') +select nullif(u, 1) from t1; +nullif(u, 1) NULL NULL NULL NULL NULL -1 -1 -explain extended select nullif(u=0, 'test') from t1; +0 +0 +explain extended select nullif(u, 1) from t1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 7 Warnings: -Note 1003 select nullif((`test`.`t1`.`u` = 0),_latin1'test') AS `nullif(u=0, 'test')` from `test`.`t1` +Note 1003 select nullif(`test`.`t1`.`u`,1) AS `nullif(u, 1)` from `test`.`t1` drop table t1; +select nullif(1,'test'); +nullif(1,'test') +1 +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: 'test' select NULLIF(NULL,NULL), NULLIF(NULL,1), NULLIF(NULL,1.0), NULLIF(NULL,"test"); NULLIF(NULL,NULL) NULLIF(NULL,1) NULLIF(NULL,1.0) NULLIF(NULL,"test") NULL NULL NULL NULL diff --git a/mysql-test/r/func_misc.result b/mysql-test/r/func_misc.result index 2d464c891bf..1d9b813e68a 100644 --- a/mysql-test/r/func_misc.result +++ b/mysql-test/r/func_misc.result @@ -25,6 +25,8 @@ length(uuid()) charset(uuid()) length(unhex(replace(uuid(),_utf8'-',_utf8''))) select length(format('nan', 2)) > 0; length(format('nan', 2)) > 0 1 +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: 'nan' select concat("$",format(2500,2)); concat("$",format(2500,2)) $2,500.00 diff --git a/mysql-test/r/func_str.result b/mysql-test/r/func_str.result index 1c2cfeca01e..bbb5a94606e 100644 --- a/mysql-test/r/func_str.result +++ b/mysql-test/r/func_str.result @@ -1,4 +1,4 @@ -drop table if exists t1; +drop table if exists t1,t2; set names latin1; select 'hello',"'hello'",'""hello""','''h''e''l''l''o''',"hel""lo",'hel\'lo'; hello 'hello' ""hello"" 'h'e'l'l'o' hel"lo hel'lo @@ -236,6 +236,8 @@ Warning 1301 Result of repeat() was larger than max_allowed_packet (1048576) - t select position("0" in "baaa" in (1)),position("0" in "1" in (1,2,3)),position("sql" in ("mysql")); position("0" in "baaa" in (1)) position("0" in "1" in (1,2,3)) position("sql" in ("mysql")) 1 0 3 +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: 'baaa' select position(("1" in (1,2,3)) in "01"); position(("1" in (1,2,3)) in "01") 2 @@ -393,9 +395,9 @@ select FIELD(_latin2'b','A','B'); ERROR HY000: Illegal mix of collations (latin2_general_ci,COERCIBLE), (latin1_swedish_ci,COERCIBLE), (latin1_swedish_ci,COERCIBLE) for operation 'field' select FIELD('b',_latin2'A','B'); ERROR HY000: Illegal mix of collations (latin1_swedish_ci,COERCIBLE), (latin2_general_ci,COERCIBLE), (latin1_swedish_ci,COERCIBLE) for operation 'field' -select FIELD('b',_latin2'A','B',1); -FIELD('b',_latin2'A','B',1) -1 +select FIELD('1',_latin2'3','2',1); +FIELD('1',_latin2'3','2',1) +3 select POSITION(_latin1'B' IN _latin1'abcd'); POSITION(_latin1'B' IN _latin1'abcd') 2 diff --git a/mysql-test/r/ndb_index_unique.result b/mysql-test/r/ndb_index_unique.result index 517d09ddf29..1401ae26ddb 100644 --- a/mysql-test/r/ndb_index_unique.result +++ b/mysql-test/r/ndb_index_unique.result @@ -248,8 +248,8 @@ INSERT INTO t3 VALUES (1,'V1',NULL); CREATE TABLE t4 ( uid bigint(20) unsigned NOT NULL default '0', gid bigint(20) unsigned NOT NULL, -rid bigint(20) unsigned NOT NULL default '-1', -cid bigint(20) unsigned NOT NULL default '-1', +rid bigint(20) unsigned NOT NULL, +cid bigint(20) unsigned NOT NULL, UNIQUE KEY m (uid,gid,rid,cid) ) engine=ndbcluster; INSERT INTO t4 VALUES (1,1,2,4); @@ -275,8 +275,8 @@ CREATE TABLE t7 ( mid bigint(20) unsigned NOT NULL PRIMARY KEY, uid bigint(20) unsigned NOT NULL default '0', gid bigint(20) unsigned NOT NULL, -rid bigint(20) unsigned NOT NULL default '-1', -cid bigint(20) unsigned NOT NULL default '-1', +rid bigint(20) unsigned NOT NULL, +cid bigint(20) unsigned NOT NULL, UNIQUE KEY m (uid,gid,rid,cid) ) engine=ndbcluster; INSERT INTO t7 VALUES(1, 1, 1, 1, 1); diff --git a/mysql-test/r/ps_1general.result b/mysql-test/r/ps_1general.result index 06acb72f49b..34f693ea33a 100644 --- a/mysql-test/r/ps_1general.result +++ b/mysql-test/r/ps_1general.result @@ -464,17 +464,17 @@ select 'a' || 'b' ; ab prepare stmt4 from ' SET sql_mode="" '; execute stmt4; -select 'a' || 'b' ; -'a' || 'b' -0 -prepare stmt5 from ' select ''a'' || ''b'' ' ; +select '2' || '3' ; +'2' || '3' +1 +prepare stmt5 from ' select ''2'' || ''3'' ' ; execute stmt5; -'a' || 'b' -0 +'2' || '3' +1 SET sql_mode=ansi; execute stmt5; -'a' || 'b' -0 +'2' || '3' +1 SET sql_mode=""; prepare stmt1 from ' flush local privileges ' ; ERROR HY000: This command is not supported in the prepared statement protocol yet diff --git a/mysql-test/r/row.result b/mysql-test/r/row.result index 40a31563604..691687762cb 100644 --- a/mysql-test/r/row.result +++ b/mysql-test/r/row.result @@ -16,12 +16,17 @@ row('a',1.5,3) IN (row(1,2,3), row('a',1.5,3), row('a','a','a')) 1 Warnings: Error 1366 Incorrect decimal value: '' for column '' at row -1 +Warning 1292 Truncated incorrect INTEGER value: 'a' select row('a',0,3) IN (row(3,2,3), row('a','0','3'), row(1,3,3)); row('a',0,3) IN (row(3,2,3), row('a','0','3'), row(1,3,3)) 1 select row('a',0,3) IN (row(3,2,3), row('a','a','3'), row(1,3,3)); +Warnings: +Warning 1292 Truncated incorrect INTEGER value: 'a' row('a',0,3) IN (row(3,2,3), row('a','a','3'), row(1,3,3)) 1 +Warnings: +Warning 1292 Truncated incorrect INTEGER value: 'a' select row('a',1.5,3) IN (row(3,NULL,3), row('a',1.5,3), row(1,3,3)); row('a',1.5,3) IN (row(3,NULL,3), row('a',1.5,3), row(1,3,3)) 1 diff --git a/mysql-test/r/rpl_session_var.result b/mysql-test/r/rpl_session_var.result index f1f79ffa597..b5b4b815ade 100644 --- a/mysql-test/r/rpl_session_var.result +++ b/mysql-test/r/rpl_session_var.result @@ -11,14 +11,14 @@ create table t1(a varchar(100),b int); set @@session.sql_mode=pipes_as_concat; insert into t1 values('My'||'SQL', 1); set @@session.sql_mode=default; -insert into t1 values('My'||'SQL', 2); +insert into t1 values('1'||'2', 2); select * from t1 where b<3 order by a; a b -0 2 +1 2 MySQL 1 select * from t1 where b<3 order by a; a b -0 2 +1 2 MySQL 1 set @@session.sql_mode=ignore_space; insert into t1 values(password ('MySQL'), 3); diff --git a/mysql-test/r/strict.result b/mysql-test/r/strict.result index 47dbb87b990..1f8ada55061 100644 --- a/mysql-test/r/strict.result +++ b/mysql-test/r/strict.result @@ -1089,4 +1089,68 @@ Error 1411 Incorrect datetime value: '2004.12.12 10:22:61' for function str_to_t Error 1411 Incorrect datetime value: '2004.12.12 10:22:61' for function str_to_time Error 1411 Incorrect datetime value: '2004.12.12 10:22:61' for function str_to_time drop table t1; +create table t1 (col1 char(3), col2 integer); +insert into t1 (col1) values (cast(1000 as char(3))); +ERROR 22007: Truncated incorrect CHAR(3) value: '1000' +insert into t1 (col1) values (cast(1000E+0 as char(3))); +ERROR 22007: Truncated incorrect CHAR(3) value: '1000' +insert into t1 (col1) values (cast(1000.0 as char(3))); +ERROR 22007: Truncated incorrect CHAR(3) value: '1000.0' +insert into t1 (col2) values (cast('abc' as signed integer)); +ERROR 22007: Truncated incorrect INTEGER value: 'abc' +insert into t1 (col2) values (10E+0 + 'a'); +ERROR 22007: Truncated incorrect DOUBLE value: 'a' +insert into t1 (col2) values (cast('10a' as unsigned integer)); +ERROR 22007: Truncated incorrect INTEGER value: '10a' +insert into t1 (col2) values (cast('10' as unsigned integer)); +insert into t1 (col2) values (cast('10' as signed integer)); +insert into t1 (col2) values (10E+0 + '0 '); +select * from t1; +col1 col2 +NULL 10 +NULL 10 +NULL 10 +drop table t1; +create table t1 (col1 date, col2 datetime, col3 timestamp); +insert into t1 values (0,0,0); +ERROR 22007: Incorrect date value: '0' for column 'col1' at row 1 +insert into t1 values (0.0,0.0,0.0); +ERROR 22007: Incorrect date value: '0' for column 'col1' at row 1 +set sql_mode='no_zero_date'; +insert into t1 values (0,0,0); +Warnings: +Warning 1264 Out of range value adjusted for column 'col1' at row 1 +Warning 1264 Out of range value adjusted for column 'col2' at row 1 +Warning 1265 Data truncated for column 'col3' at row 1 +insert into t1 values (0.0,0.0,0.0); +Warnings: +Warning 1264 Out of range value adjusted for column 'col1' at row 1 +Warning 1264 Out of range value adjusted for column 'col2' at row 1 +Warning 1265 Data truncated for column 'col3' at row 1 +drop table t1; +set sql_mode='traditional'; +create table t1 (col1 date); +insert ignore into t1 values ('0000-00-00'); +Warnings: +Warning 1265 Data truncated for column 'col1' at row 1 +insert into t1 select * from t1; +ERROR 22007: Incorrect date value: '0000-00-00' for column 'col1' at row 1 +insert ignore into t1 values ('0000-00-00'); +Warnings: +Warning 1265 Data truncated for column 'col1' at row 1 +insert into t1 select * from t1; +ERROR 22007: Incorrect date value: '0000-00-00' for column 'col1' at row 1 +alter table t1 modify col1 datetime; +ERROR 22007: Incorrect datetime value: '0000-00-00' for column 'col1' at row 1 +alter ignore table t1 modify col1 datetime; +Warnings: +Warning 1264 Out of range value adjusted for column 'col1' at row 1 +Warning 1264 Out of range value adjusted for column 'col1' at row 2 +insert into t1 select * from t1; +ERROR 22007: Incorrect datetime value: '0000-00-00 00:00:00' for column 'col1' at row 1 +select * from t1; +col1 +0000-00-00 00:00:00 +0000-00-00 00:00:00 +drop table t1; set sql_mode=@org_mode; diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result index 738c011012d..24d54c3ca33 100644 --- a/mysql-test/r/subselect.result +++ b/mysql-test/r/subselect.result @@ -117,15 +117,17 @@ SELECT (SELECT 1.5,2,'a') = ROW(1.5,2,'a'); SELECT (SELECT 1.5,2,'a') = ROW(1.5,2,'b'); (SELECT 1.5,2,'a') = ROW(1.5,2,'b') 0 -SELECT (SELECT 1.5,2,'a') = ROW('b',2,'b'); -(SELECT 1.5,2,'a') = ROW('b',2,'b') +SELECT (SELECT 1.5,2,'a') = ROW('1.5b',2,'b'); +(SELECT 1.5,2,'a') = ROW('1.5b',2,'b') 0 +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: '1.5b' SELECT (SELECT 'b',2,'a') = ROW(1.5,2,'a'); (SELECT 'b',2,'a') = ROW(1.5,2,'a') 0 -SELECT (SELECT 1.5,2,'a') = ROW(1.5,'c','a'); -(SELECT 1.5,2,'a') = ROW(1.5,'c','a') -0 +SELECT (SELECT 1.5,2,'a') = ROW(1.5,'2','a'); +(SELECT 1.5,2,'a') = ROW(1.5,'2','a') +1 SELECT (SELECT 1.5,'c','a') = ROW(1.5,2,'a'); (SELECT 1.5,'c','a') = ROW(1.5,2,'a') 0 diff --git a/mysql-test/r/type_ranges.result b/mysql-test/r/type_ranges.result index e553d9d86f2..12efb38179f 100644 --- a/mysql-test/r/type_ranges.result +++ b/mysql-test/r/type_ranges.result @@ -133,7 +133,7 @@ ALTER TABLE t1 add new_field char(10) default "new" not null, change blob_col new_blob_col varchar(20), change date_field date_field char(10), -alter column string set default "new default", +alter column string set default "newdefault", alter short drop default, DROP INDEX utiny, DROP INDEX ushort, @@ -211,7 +211,7 @@ update t2 set string="changed" where auto=16; show full columns from t1; Field Type Collation Null Key Default Extra Privileges Comment auto int(5) unsigned NULL NO MUL NULL auto_increment select,insert,update,references -string char(10) latin1_swedish_ci YES new defaul select,insert,update,references +string char(10) latin1_swedish_ci YES newdefault select,insert,update,references tiny tinyint(4) NULL NO MUL 0 select,insert,update,references short smallint(6) NULL NO MUL 0 select,insert,update,references medium mediumint(8) NULL NO MUL 0 select,insert,update,references @@ -237,7 +237,7 @@ new_field char(10) latin1_swedish_ci NO new select,insert,update,references show full columns from t2; Field Type Collation Null Key Default Extra Privileges Comment auto int(5) unsigned NULL NO 0 select,insert,update,references -string char(10) latin1_swedish_ci YES new defaul select,insert,update,references +string char(10) latin1_swedish_ci YES newdefault select,insert,update,references tiny tinyint(4) NULL NO 0 select,insert,update,references short smallint(6) NULL NO 0 select,insert,update,references medium mediumint(8) NULL NO 0 select,insert,update,references -- cgit v1.2.1