diff options
49 files changed, 732 insertions, 271 deletions
diff --git a/configure.in b/configure.in index f2f59bbff19..c3c792e7339 100644 --- a/configure.in +++ b/configure.in @@ -351,11 +351,21 @@ then if echo $CXX | grep gcc > /dev/null 2>&1 then - if $CXX -v 2>&1 | grep 'version 3' > /dev/null 2>&1 - then - # Statically link the language support function's found in libsupc++.a - LIBS="$LIBS -lsupc++" - fi + GCC_VERSION=`gcc -v 2>&1 | grep version | sed -e 's/[[^0-9. ]]//g; s/^ *//g; s/ .*//g'` + echo "Using gcc version '$GCC_VERSION'" + case "$GCC_VERSION" in + 3.4.*|3.5.*) + # Statically link the language support function's found in libsupc++.a + LIBS="$LIBS -lsupc++" + echo "Using -libsupc++ for static linking with gcc" + ;; + *) + # Using -lsupc++ doesn't work in gcc 3.3 on SuSE 9.2 + # (causes link failures when linking things staticly) + CXXFLAGS="$CXXFLAGS -DUSE_MYSYS_NEW -DDEFINE_CXA_PURE_VIRTUAL" + echo "Using MYSYS_NEW for static linking with gcc" + ;; + esac fi fi 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'Æ ' AS nchar(2)) as c4, 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/func_time.result b/mysql-test/r/func_time.result index b30fddb8de0..4293ef5bd85 100644 --- a/mysql-test/r/func_time.result +++ b/mysql-test/r/func_time.result @@ -304,6 +304,8 @@ NULL select date_sub("0000-00-00 00:00:00",INTERVAL 1 SECOND); date_sub("0000-00-00 00:00:00",INTERVAL 1 SECOND) NULL +Warnings: +Warning 1292 Truncated incorrect datetime value: '0000-00-00 00:00:00' select date_add('1998-01-30',Interval 1 month); date_add('1998-01-30',Interval 1 month) 1998-02-28 @@ -424,6 +426,9 @@ insert into t1 values ("0000-00-00", "0000-00-00", "0000-00-00", "0000-00-00"); select dayofyear("0000-00-00"),dayofyear(d),dayofyear(dt),dayofyear(t),dayofyear(c) from t1; dayofyear("0000-00-00") dayofyear(d) dayofyear(dt) dayofyear(t) dayofyear(c) NULL NULL NULL NULL NULL +Warnings: +Warning 1292 Truncated incorrect datetime value: '0000-00-00' +Warning 1292 Truncated incorrect datetime value: '0000-00-00' select dayofmonth("0000-00-00"),dayofmonth(d),dayofmonth(dt),dayofmonth(t),dayofmonth(c) from t1; dayofmonth("0000-00-00") dayofmonth(d) dayofmonth(dt) dayofmonth(t) dayofmonth(c) 0 0 0 0 0 @@ -436,15 +441,24 @@ quarter("0000-00-00") quarter(d) quarter(dt) quarter(t) quarter(c) select week("0000-00-00"),week(d),week(dt),week(t),week(c) from t1; week("0000-00-00") week(d) week(dt) week(t) week(c) NULL NULL NULL NULL NULL +Warnings: +Warning 1292 Truncated incorrect datetime value: '0000-00-00' +Warning 1292 Truncated incorrect datetime value: '0000-00-00' select year("0000-00-00"),year(d),year(dt),year(t),year(c) from t1; year("0000-00-00") year(d) year(dt) year(t) year(c) 0 0 0 0 0 select yearweek("0000-00-00"),yearweek(d),yearweek(dt),yearweek(t),yearweek(c) from t1; yearweek("0000-00-00") yearweek(d) yearweek(dt) yearweek(t) yearweek(c) NULL NULL NULL NULL NULL +Warnings: +Warning 1292 Truncated incorrect datetime value: '0000-00-00' +Warning 1292 Truncated incorrect datetime value: '0000-00-00' select to_days("0000-00-00"),to_days(d),to_days(dt),to_days(t),to_days(c) from t1; to_days("0000-00-00") to_days(d) to_days(dt) to_days(t) to_days(c) NULL NULL NULL NULL NULL +Warnings: +Warning 1292 Truncated incorrect datetime value: '0000-00-00' +Warning 1292 Truncated incorrect datetime value: '0000-00-00' select extract(MONTH FROM "0000-00-00"),extract(MONTH FROM d),extract(MONTH FROM dt),extract(MONTH FROM t),extract(MONTH FROM c) from t1; extract(MONTH FROM "0000-00-00") extract(MONTH FROM d) extract(MONTH FROM dt) extract(MONTH FROM t) extract(MONTH FROM c) 0 0 0 0 0 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..0c75e80cb77 100644 --- a/mysql-test/r/row.result +++ b/mysql-test/r/row.result @@ -16,12 +16,15 @@ 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 -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 +Warning 1292 Truncated incorrect INTEGER value: 'a' select row('a',0,3) IN (row(3,2,3), row('a','a','3'), row(1,3,3)); 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',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',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..6f332b4e087 100644 --- a/mysql-test/r/strict.result +++ b/mysql-test/r/strict.result @@ -259,17 +259,23 @@ INSERT INTO t1 (col1) VALUES(CAST('2004-10-0' AS DATE)); ERROR 22007: Incorrect date value: '2004-10-00' for column 'col1' at row 1 INSERT INTO t1 (col1) VALUES(CAST('2004-0-10' AS DATE)); ERROR 22007: Incorrect date value: '2004-00-10' for column 'col1' at row 1 +INSERT INTO t1 (col1) VALUES(CAST('0000-00-00' AS DATE)); +ERROR 22007: Truncated incorrect datetime value: '0000-00-00' INSERT INTO t1 (col2) VALUES(CAST('0000-10-31 15:30' AS DATETIME)); INSERT INTO t1 (col2) VALUES(CAST('2004-10-0 15:30' AS DATETIME)); ERROR 22007: Incorrect datetime value: '2004-10-00 15:30:00' for column 'col2' at row 1 INSERT INTO t1 (col2) VALUES(CAST('2004-0-10 15:30' AS DATETIME)); ERROR 22007: Incorrect datetime value: '2004-00-10 15:30:00' for column 'col2' at row 1 +INSERT INTO t1 (col2) VALUES(CAST('0000-00-00' AS DATETIME)); +ERROR 22007: Truncated incorrect datetime value: '0000-00-00' INSERT INTO t1 (col3) VALUES(CAST('0000-10-31 15:30' AS DATETIME)); ERROR 22007: Incorrect datetime value: '0000-10-31 15:30:00' for column 'col3' at row 1 INSERT INTO t1 (col3) VALUES(CAST('2004-10-0 15:30' AS DATETIME)); ERROR 22007: Incorrect datetime value: '2004-10-00 15:30:00' for column 'col3' at row 1 INSERT INTO t1 (col3) VALUES(CAST('2004-0-10 15:30' AS DATETIME)); ERROR 22007: Incorrect datetime value: '2004-00-10 15:30:00' for column 'col3' at row 1 +INSERT INTO t1 (col3) VALUES(CAST('0000-00-00' AS DATETIME)); +ERROR 22007: Truncated incorrect datetime value: '0000-00-00' drop table t1; CREATE TABLE t1 (col1 date, col2 datetime, col3 timestamp); INSERT INTO t1 (col1) VALUES (CONVERT('2004-10-15',DATE)); @@ -280,17 +286,23 @@ INSERT INTO t1 (col1) VALUES(CONVERT('2004-10-0' , DATE)); ERROR 22007: Incorrect date value: '2004-10-00' for column 'col1' at row 1 INSERT INTO t1 (col1) VALUES(CONVERT('2004-0-10' , DATE)); ERROR 22007: Incorrect date value: '2004-00-10' for column 'col1' at row 1 +INSERT INTO t1 (col1) VALUES(CONVERT('0000-00-00',DATE)); +ERROR 22007: Truncated incorrect datetime value: '0000-00-00' INSERT INTO t1 (col2) VALUES(CONVERT('0000-10-31 15:30',DATETIME)); INSERT INTO t1 (col2) VALUES(CONVERT('2004-10-0 15:30',DATETIME)); ERROR 22007: Incorrect datetime value: '2004-10-00 15:30:00' for column 'col2' at row 1 INSERT INTO t1 (col2) VALUES(CONVERT('2004-0-10 15:30',DATETIME)); ERROR 22007: Incorrect datetime value: '2004-00-10 15:30:00' for column 'col2' at row 1 +INSERT INTO t1 (col2) VALUES(CONVERT('0000-00-00',DATETIME)); +ERROR 22007: Truncated incorrect datetime value: '0000-00-00' INSERT INTO t1 (col3) VALUES(CONVERT('0000-10-31 15:30',DATETIME)); ERROR 22007: Incorrect datetime value: '0000-10-31 15:30:00' for column 'col3' at row 1 INSERT INTO t1 (col3) VALUES(CONVERT('2004-10-0 15:30',DATETIME)); ERROR 22007: Incorrect datetime value: '2004-10-00 15:30:00' for column 'col3' at row 1 INSERT INTO t1 (col3) VALUES(CONVERT('2004-0-10 15:30',DATETIME)); ERROR 22007: Incorrect datetime value: '2004-00-10 15:30:00' for column 'col3' at row 1 +INSERT INTO t1 (col3) VALUES(CONVERT('0000-00-00',DATETIME)); +ERROR 22007: Truncated incorrect datetime value: '0000-00-00' drop table t1; CREATE TABLE t1(col1 TINYINT, col2 TINYINT UNSIGNED); INSERT INTO t1 VALUES(-128,0),(0,0),(127,255),('-128','0'),('0','0'),('127','255'),(-128.0,0.0),(0.0,0.0),(127.0,255.0); @@ -1089,4 +1101,76 @@ 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 +insert into t1 (col1) values (convert('0000-00-00',date)); +ERROR 22007: Truncated incorrect datetime value: '0000-00-00' +insert into t1 (col1) values (cast('0000-00-00' as date)); +ERROR 22007: Truncated incorrect datetime value: '0000-00-00' +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 ignore into t1 (col1) values (cast('0000-00-00' as date)); +Warnings: +Warning 1292 Truncated incorrect datetime value: '0000-00-00' +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 +NULL +drop table t1; set sql_mode=@org_mode; diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result index 036d1631592..0e8ba49d88d 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 diff --git a/mysql-test/r/union.result b/mysql-test/r/union.result index 8c8e12d8838..c5295048126 100644 --- a/mysql-test/r/union.result +++ b/mysql-test/r/union.result @@ -1235,3 +1235,20 @@ show columns from t2; Field Type Null Key Default Extra a varchar(3) YES NULL drop table t2, t1; +create table t1 (a varchar(5)); +create table t2 select * from t1 union select 'abcdefghijkl'; +show create table t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `a` varchar(12) default NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +select row_format from information_schema.TABLES where table_schema="test" and table_name="t2"; +row_format +Dynamic +alter table t2 ROW_FORMAT=fixed; +show create table t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `a` varchar(12) default NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 ROW_FORMAT=FIXED +drop table t1,t2; diff --git a/mysql-test/t/cast.test b/mysql-test/t/cast.test index 94d9590dd5f..e7dd49394ee 100644 --- a/mysql-test/t/cast.test +++ b/mysql-test/t/cast.test @@ -5,6 +5,7 @@ select CAST(1-2 AS UNSIGNED); select CAST(CAST(1-2 AS UNSIGNED) AS SIGNED INTEGER); select CONVERT('-1',UNSIGNED); +select CAST('10 ' as unsigned integer); select cast(-5 as unsigned) | 1, cast(-5 as unsigned) & -1; select cast(-5 as unsigned) -1, cast(-5 as unsigned) + 1; select ~5, cast(~5 as signed); @@ -16,11 +17,22 @@ select cast("A" as binary) = "a", cast(BINARY "a" as CHAR) = "A"; select cast("2001-1-1" as DATE), cast("2001-1-1" as DATETIME); select cast("1:2:3" as TIME); select CONVERT("2004-01-22 21:45:33",DATE); +select 10+'10'; +select 10.0+'10'; +select 10E+0+'10'; + +# The following cast creates warnings + select CONVERT(DATE "2004-01-22 21:45:33" USING latin1); select CONVERT(DATE "2004-01-22 21:45:33",CHAR); select CONVERT(DATE "2004-01-22 21:45:33",CHAR(4)); select CONVERT(DATE "2004-01-22 21:45:33",BINARY(4)); select CAST(DATE "2004-01-22 21:45:33" AS BINARY(4)); +select cast('-10a' as signed integer); +select cast('a10' as unsigned integer); +select 10+'a'; +select 10.0+cast('a' as decimal); +select 10E+0+'a'; # # Character set convertion @@ -41,6 +53,7 @@ select cast(_latin1'abc' AS char(2)) as c3, cast(_latin1'a ' AS char(2)) as c4, cast(_latin1'a' AS char(2)) as c5; +select cast(1000 as CHAR(3)); create table t1 select cast(_latin1'ab' AS char) as c1, diff --git a/mysql-test/t/create.test b/mysql-test/t/create.test index 26b467a398d..1e2cf391221 100644 --- a/mysql-test/t/create.test +++ b/mysql-test/t/create.test @@ -49,14 +49,25 @@ create table `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa create table a (`aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa` int); # -# Some wrong defaults, so these creates should fail too +# Some wrong defaults, so these creates should fail too (Bug #5902) # --error 1067 -create table test (a datetime default now()); +create table t1 (a datetime default now()); --error 1294 -create table test (a datetime on update now()); +create table t1 (a datetime on update now()); --error 1067 -create table test (a int default 100 auto_increment); +create table t1 (a int default 100 auto_increment); +--error 1067 +create table t1 (a tinyint default 1000); +--error 1067 +create table t1 (a varchar(5) default 'abcdef'); + +create table t1 (a varchar(5) default 'abcde'); +insert into t1 values(); +select * from t1; +--error 1067 +alter table t1 alter column a set default 'abcdef'; +drop table t1; # # test of dummy table names diff --git a/mysql-test/t/func_if.test b/mysql-test/t/func_if.test index 693773b18c8..a2ea26390ca 100644 --- a/mysql-test/t/func_if.test +++ b/mysql-test/t/func_if.test @@ -25,9 +25,10 @@ explain extended select if(u=1,st,binary st) s from t1 where st like "%a%" order # # NULLIF test # -select nullif(u=0, 'test') from t1; -explain extended select nullif(u=0, 'test') from t1; +select nullif(u, 1) from t1; +explain extended select nullif(u, 1) from t1; drop table t1; +select nullif(1,'test'); # # Bug 2629 diff --git a/mysql-test/t/func_str.test b/mysql-test/t/func_str.test index 8bf36431587..b3c875f7bf5 100644 --- a/mysql-test/t/func_str.test +++ b/mysql-test/t/func_str.test @@ -3,7 +3,7 @@ # Testing string functions --disable_warnings -drop table if exists t1; +drop table if exists t1,t2; --enable_warnings set names latin1; @@ -244,7 +244,7 @@ select FIELD('b','A' COLLATE latin1_bin,'B'); select FIELD(_latin2'b','A','B'); --error 1270 select FIELD('b',_latin2'A','B'); -select FIELD('b',_latin2'A','B',1); +select FIELD('1',_latin2'3','2',1); select POSITION(_latin1'B' IN _latin1'abcd'); select POSITION(_latin1'B' IN _latin1'abcd' COLLATE latin1_bin); @@ -523,4 +523,3 @@ SELECT t1.id, aes_decrypt(str, 'bar') FROM t1, t2 WHERE t1.id = t2.id ORDER BY t1.id; DROP TABLE t1, t2; - diff --git a/mysql-test/t/ndb_index_unique.test b/mysql-test/t/ndb_index_unique.test index 2fc7c496fea..6714f0233cf 100644 --- a/mysql-test/t/ndb_index_unique.test +++ b/mysql-test/t/ndb_index_unique.test @@ -182,8 +182,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); @@ -209,8 +209,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/t/ps_1general.test b/mysql-test/t/ps_1general.test index 22a4a8bf2c9..4800cb831f7 100644 --- a/mysql-test/t/ps_1general.test +++ b/mysql-test/t/ps_1general.test @@ -500,10 +500,10 @@ select 'a' || 'b' ; prepare stmt4 from ' SET sql_mode="" '; execute stmt4; # check if the sql_mode is not ansi -select 'a' || 'b' ; +select '2' || '3' ; # Will a switch of the sqlmode affect the execution of already prepared # statements ? -prepare stmt5 from ' select ''a'' || ''b'' ' ; +prepare stmt5 from ' select ''2'' || ''3'' ' ; execute stmt5; SET sql_mode=ansi; execute stmt5; diff --git a/mysql-test/t/row.test b/mysql-test/t/row.test index 58b90c9a356..afcaaef6811 100644 --- a/mysql-test/t/row.test +++ b/mysql-test/t/row.test @@ -9,9 +9,9 @@ select row(1,2,3) IN (row(3,NULL,3), row(1,2,3), row(1,3,3)); select row(10,2,3) IN (row(3,NULL,3), row(1,2,3), row(1,3,3)); --disable_ps_warnings select row('a',1.5,3) IN (row(1,2,3), row('a',1.5,3), row('a','a','a')); +select row('a',0,3) IN (row(3,2,3), row('a','a','3'), row(1,3,3)); --enable_ps_warnings select row('a',0,3) IN (row(3,2,3), row('a','0','3'), row(1,3,3)); -select row('a',0,3) IN (row(3,2,3), row('a','a','3'), row(1,3,3)); select row('a',1.5,3) IN (row(3,NULL,3), row('a',1.5,3), row(1,3,3)); select row('b',1.5,3) IN (row(3,NULL,3), row('a',1.5,3), row(1,3,3)); select row('b',1.5,3) IN (row('b',NULL,3), row('a',1.5,3), row(1,3,3)); diff --git a/mysql-test/t/rpl_session_var.test b/mysql-test/t/rpl_session_var.test index 2379df721b7..a6f4b496a23 100644 --- a/mysql-test/t/rpl_session_var.test +++ b/mysql-test/t/rpl_session_var.test @@ -7,7 +7,7 @@ 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; save_master_pos; connection slave; diff --git a/mysql-test/t/strict.test b/mysql-test/t/strict.test index 96ba9993c49..9d4befb9743 100644 --- a/mysql-test/t/strict.test +++ b/mysql-test/t/strict.test @@ -284,8 +284,8 @@ INSERT INTO t1 (col1) VALUES(CAST('2004-0-10' AS DATE)); # deactivated because of Bug#6145 # Bug#6145: Traditional: CONVERT and CAST should reject zero DATE values -#--error 1292 -#INSERT INTO t1 (col1) VALUES(CAST('0000-00-00' AS DATE)); +--error 1292 +INSERT INTO t1 (col1) VALUES(CAST('0000-00-00' AS DATE)); ## Test INSERT with CAST AS DATETIME into DATETIME # All test cases expected to fail should return @@ -308,10 +308,9 @@ INSERT INTO t1 (col2) VALUES(CAST('2004-0-10 15:30' AS DATETIME)); #--error 1292 #INSERT INTO t1 (col2) VALUES(CAST('2004-13-15 15:30' AS DATETIME)); -# deactivated because of Bug#6145 # Bug#6145: Traditional: CONVERT and CAST should reject zero DATE values -#--error 1292 -#INSERT INTO t1 (col2) VALUES(CAST('0000-00-00' AS DATETIME)); +--error 1292 +INSERT INTO t1 (col2) VALUES(CAST('0000-00-00' AS DATETIME)); ## Test INSERT with CAST AS DATETIME into TIMESTAMP # All test cases expected to fail should return @@ -338,10 +337,9 @@ INSERT INTO t1 (col3) VALUES(CAST('2004-0-10 15:30' AS DATETIME)); #--error 1292 #INSERT INTO t1 (col3) VALUES(CAST('2004-13-15 15:30' AS DATETIME)); -# deactivated because of Bug#6145 # Bug#6145: Traditional: CONVERT and CAST should reject zero DATE values -#--error 1292 -#INSERT INTO t1 (col3) VALUES(CAST('0000-00-00' AS DATETIME)); +--error 1292 +INSERT INTO t1 (col3) VALUES(CAST('0000-00-00' AS DATETIME)); drop table t1; @@ -376,10 +374,9 @@ INSERT INTO t1 (col1) VALUES(CONVERT('2004-0-10' , DATE)); #--error 1292 #INSERT INTO t1 (col1) VALUES(CONVERT('2004-13-15',DATE)); -# deactivated because of Bug#6145 # Bug#6145: Traditional: CONVERT and CAST should reject zero DATE values -#--error 1292 -#INSERT INTO t1 (col1) VALUES(CONVERT('0000-00-00',DATE)); +--error 1292 +INSERT INTO t1 (col1) VALUES(CONVERT('0000-00-00',DATE)); ## Test INSERT with CONVERT to DATETIME into DATETIME # All test cases expected to fail should return @@ -403,8 +400,8 @@ INSERT INTO t1 (col2) VALUES(CONVERT('2004-0-10 15:30',DATETIME)); #INSERT INTO t1 (col2) VALUES(CONVERT('2004-13-15 15:30',DATETIME)); # Bug#6145: Traditional: CONVERT and CAST should reject zero DATE values -#--error 1292 -#INSERT INTO t1 (col2) VALUES(CONVERT('0000-00-00',DATETIME)); +--error 1292 +INSERT INTO t1 (col2) VALUES(CONVERT('0000-00-00',DATETIME)); ## Test INSERT with CONVERT to DATETIME into DATETIME # All test cases expected to fail should return @@ -430,10 +427,9 @@ INSERT INTO t1 (col3) VALUES(CONVERT('2004-0-10 15:30',DATETIME)); #--error 1292 #INSERT INTO t1 (col3) VALUES(CONVERT('2004-13-15 15:30',DATETIME)); -# deactivated because of Bug#6145 # Bug#6145: Traditional: CONVERT and CAST should reject zero DATE values -#--error 1292 -#INSERT INTO t1 (col3) VALUES(CONVERT('0000-00-00',DATETIME)); +--error 1292 +INSERT INTO t1 (col3) VALUES(CONVERT('0000-00-00',DATETIME)); drop table t1; @@ -978,6 +974,64 @@ select count(*) from t1 where STR_TO_DATE('2004.12.12 10:22:61','%Y.%m.%d %T') I drop table t1; # +# Check insert with wrong CAST() (Bug #5912) +# + +create table t1 (col1 char(3), col2 integer); +--error 1292 +insert into t1 (col1) values (cast(1000 as char(3))); +--error 1292 +insert into t1 (col1) values (cast(1000E+0 as char(3))); +--error 1292 +insert into t1 (col1) values (cast(1000.0 as char(3))); +--error 1292 +insert into t1 (col2) values (cast('abc' as signed integer)); +--error 1292 +insert into t1 (col2) values (10E+0 + 'a'); +--error 1292 +insert into t1 (col2) values (cast('10a' as unsigned integer)); +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; +drop table t1; + +# +# Zero dates using numbers was not checked properly (Bug #5933 & #6145) +# + +create table t1 (col1 date, col2 datetime, col3 timestamp); +--error 1292 +insert into t1 values (0,0,0); +--error 1292 +insert into t1 values (0.0,0.0,0.0); +--error 1292 +insert into t1 (col1) values (convert('0000-00-00',date)); +--error 1292 +insert into t1 (col1) values (cast('0000-00-00' as date)); + +set sql_mode='no_zero_date'; +insert into t1 values (0,0,0); +insert into t1 values (0.0,0.0,0.0); +drop table t1; +set sql_mode='traditional'; +create table t1 (col1 date); +insert ignore into t1 values ('0000-00-00'); +--error 1292 +insert into t1 select * from t1; +insert ignore into t1 values ('0000-00-00'); +insert ignore into t1 (col1) values (cast('0000-00-00' as date)); +--error 1292 +insert into t1 select * from t1; +--error 1292 +alter table t1 modify col1 datetime; +alter ignore table t1 modify col1 datetime; +--error 1292 +insert into t1 select * from t1; +select * from t1; +drop table t1; + +# # Restore mode # set sql_mode=@org_mode; diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test index 55278ad40f8..3bb1da0eddf 100644 --- a/mysql-test/t/subselect.test +++ b/mysql-test/t/subselect.test @@ -46,9 +46,9 @@ SELECT ROW(1,2,3) > (SELECT 1,2,1); SELECT ROW(1,2,3) = (SELECT 1,2,NULL); SELECT (SELECT 1.5,2,'a') = ROW(1.5,2,'a'); SELECT (SELECT 1.5,2,'a') = ROW(1.5,2,'b'); -SELECT (SELECT 1.5,2,'a') = ROW('b',2,'b'); +SELECT (SELECT 1.5,2,'a') = ROW('1.5b',2,'b'); SELECT (SELECT 'b',2,'a') = ROW(1.5,2,'a'); -SELECT (SELECT 1.5,2,'a') = ROW(1.5,'c','a'); +SELECT (SELECT 1.5,2,'a') = ROW(1.5,'2','a'); SELECT (SELECT 1.5,'c','a') = ROW(1.5,2,'a'); -- error 1241 diff --git a/mysql-test/t/type_ranges.test b/mysql-test/t/type_ranges.test index 5f035921064..3bd3502766b 100644 --- a/mysql-test/t/type_ranges.test +++ b/mysql-test/t/type_ranges.test @@ -69,7 +69,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, diff --git a/mysql-test/t/union.test b/mysql-test/t/union.test index 95b5d4c6aaf..7d3f52bd7ae 100644 --- a/mysql-test/t/union.test +++ b/mysql-test/t/union.test @@ -760,3 +760,15 @@ create table t2 select a from t1 union select c from t1; create table t2 select a from t1 union select b from t1; show columns from t2; drop table t2, t1; + +# +# Test that union with VARCHAR produces dynamic row tables +# + +create table t1 (a varchar(5)); +create table t2 select * from t1 union select 'abcdefghijkl'; +show create table t2; +select row_format from information_schema.TABLES where table_schema="test" and table_name="t2"; +alter table t2 ROW_FORMAT=fixed; +show create table t2; +drop table t1,t2; diff --git a/sql/Makefile.am b/sql/Makefile.am index e0ff324b33c..b506d2a767b 100644 --- a/sql/Makefile.am +++ b/sql/Makefile.am @@ -49,7 +49,7 @@ noinst_HEADERS = item.h item_func.h item_sum.h item_cmpfunc.h \ mysql_priv.h item_geofunc.h sql_bitmap.h \ procedure.h sql_class.h sql_lex.h sql_list.h \ sql_manager.h sql_map.h sql_string.h unireg.h \ - field.h handler.h mysqld_suffix.h \ + sql_error.h field.h handler.h mysqld_suffix.h \ ha_myisammrg.h\ ha_heap.h ha_myisam.h ha_berkeley.h ha_innodb.h \ ha_ndbcluster.h opt_range.h protocol.h \ diff --git a/sql/field.cc b/sql/field.cc index f3666f862af..4ef80a99695 100644 --- a/sql/field.cc +++ b/sql/field.cc @@ -4512,6 +4512,13 @@ int Field_timestamp::store(longlong nr) set_datetime_warning(MYSQL_ERROR::WARN_LEVEL_WARN, WARN_DATA_TRUNCATED, nr, MYSQL_TIMESTAMP_DATETIME, 1); + if (!error && timestamp == 0 && + (table->in_use->variables.sql_mode & MODE_NO_ZERO_DATE)) + { + set_datetime_warning(MYSQL_ERROR::WARN_LEVEL_WARN, + WARN_DATA_TRUNCATED, + nr, MYSQL_TIMESTAMP_DATETIME, 1); + } #ifdef WORDS_BIGENDIAN if (table->s->db_low_byte_first) @@ -5137,6 +5144,12 @@ int Field_date::store(double nr) } else tmp=(long) rint(nr); + + /* + We don't need to check for zero dates here as this date type is only + used in .frm tables from very old MySQL versions + */ + #ifdef WORDS_BIGENDIAN if (table->s->db_low_byte_first) { @@ -5165,6 +5178,7 @@ int Field_date::store(longlong nr) } else tmp=(long) nr; + #ifdef WORDS_BIGENDIAN if (table->s->db_low_byte_first) { @@ -5277,6 +5291,7 @@ void Field_date::sql_type(String &res) const res.set_ascii("date", 4); } + /**************************************************************************** ** The new date type ** This is identical to the old date type, but stored on 3 bytes instead of 4 @@ -5309,17 +5324,17 @@ int Field_newdate::store(const char *from,uint len,CHARSET_INFO *cs) return error; } + int Field_newdate::store(double nr) { if (nr < 0.0 || nr > 99991231235959.0) { - (void) Field_newdate::store((longlong) -1); + int3store(ptr,(int32) 0); set_datetime_warning(MYSQL_ERROR::WARN_LEVEL_WARN, WARN_DATA_TRUNCATED, nr, MYSQL_TIMESTAMP_DATE); return 1; } - else - return Field_newdate::store((longlong) rint(nr)); + return Field_newdate::store((longlong) rint(nr)); } @@ -5339,6 +5354,8 @@ int Field_newdate::store(longlong nr) } else { + uint month, day; + tmp=(int32) nr; if (tmp) { @@ -5346,24 +5363,33 @@ int Field_newdate::store(longlong nr) tmp+= (uint32) 20000000L; else if (tmp < 999999L) tmp+= (uint32) 19000000L; + + month= (uint) ((tmp/100) % 100); + day= (uint) (tmp%100); + if (month > 12 || day > 31) + { + tmp=0L; // Don't allow date to change + set_datetime_warning(MYSQL_ERROR::WARN_LEVEL_WARN, + ER_WARN_DATA_OUT_OF_RANGE, nr, + MYSQL_TIMESTAMP_DATE, 1); + error= 1; + } + else + tmp= day + month*32 + (tmp/10000)*16*32; } - uint month= (uint) ((tmp/100) % 100); - uint day= (uint) (tmp%100); - if (month > 12 || day > 31) + else if (table->in_use->variables.sql_mode & MODE_NO_ZERO_DATE) { - tmp=0L; // Don't allow date to change set_datetime_warning(MYSQL_ERROR::WARN_LEVEL_WARN, - ER_WARN_DATA_OUT_OF_RANGE, nr, - MYSQL_TIMESTAMP_DATE, 1); + ER_WARN_DATA_OUT_OF_RANGE, + 0, MYSQL_TIMESTAMP_DATE); error= 1; } - else - tmp= day + month*32 + (tmp/10000)*16*32; } - int3store(ptr,(int32) tmp); + int3store(ptr, tmp); return error; } + int Field_newdate::store_time(TIME *ltime,timestamp_type type) { long tmp; @@ -5380,6 +5406,7 @@ int Field_newdate::store_time(TIME *ltime,timestamp_type type) return error; } + bool Field_newdate::send_binary(Protocol *protocol) { TIME tm; @@ -5387,11 +5414,13 @@ bool Field_newdate::send_binary(Protocol *protocol) return protocol->store_date(&tm); } + double Field_newdate::val_real(void) { return (double) Field_newdate::val_int(); } + longlong Field_newdate::val_int(void) { ulong j= uint3korr(ptr); @@ -5399,6 +5428,7 @@ longlong Field_newdate::val_int(void) return (longlong) j; } + String *Field_newdate::val_str(String *val_buffer, String *val_ptr __attribute__((unused))) { @@ -5426,6 +5456,7 @@ String *Field_newdate::val_str(String *val_buffer, return val_buffer; } + bool Field_newdate::get_date(TIME *ltime,uint fuzzydate) { uint32 tmp=(uint32) uint3korr(ptr); @@ -5438,11 +5469,13 @@ bool Field_newdate::get_date(TIME *ltime,uint fuzzydate) 1 : 0); } + bool Field_newdate::get_time(TIME *ltime) { return Field_newdate::get_date(ltime,0); } + int Field_newdate::cmp(const char *a_ptr, const char *b_ptr) { uint32 a,b; @@ -5451,6 +5484,7 @@ int Field_newdate::cmp(const char *a_ptr, const char *b_ptr) return (a < b) ? -1 : (a > b) ? 1 : 0; } + void Field_newdate::sort_string(char *to,uint length __attribute__((unused))) { to[0] = ptr[2]; @@ -5458,6 +5492,7 @@ void Field_newdate::sort_string(char *to,uint length __attribute__((unused))) to[2] = ptr[0]; } + void Field_newdate::sql_type(String &res) const { res.set_ascii("date", 4); @@ -5514,10 +5549,10 @@ int Field_datetime::store(double nr) set_datetime_warning(MYSQL_ERROR::WARN_LEVEL_WARN, ER_WARN_DATA_OUT_OF_RANGE, nr, MYSQL_TIMESTAMP_DATETIME); - nr=0.0; + nr= 0.0; error= 1; } - error |= Field_datetime::store((longlong) rint(nr)); + error|= Field_datetime::store((longlong) rint(nr)); return error; } @@ -5534,6 +5569,13 @@ int Field_datetime::store(longlong nr) set_datetime_warning(MYSQL_ERROR::WARN_LEVEL_WARN, WARN_DATA_TRUNCATED, initial_nr, MYSQL_TIMESTAMP_DATETIME, 1); + else if (nr == 0 && table->in_use->variables.sql_mode & MODE_NO_ZERO_DATE) + { + set_datetime_warning(MYSQL_ERROR::WARN_LEVEL_WARN, + ER_WARN_DATA_OUT_OF_RANGE, + initial_nr, MYSQL_TIMESTAMP_DATE); + error= 1; + } #ifdef WORDS_BIGENDIAN if (table->s->db_low_byte_first) @@ -8285,7 +8327,37 @@ create_field::create_field(Field *old_field,Field *orig_field) } -/* Warning handling */ +/* + maximum possible display length for blob + + SYNOPSIS + Field_blob::max_length() + + RETURN + length +*/ +uint32 Field_blob::max_length() +{ + switch (packlength) + { + case 1: + return 255; + case 2: + return 65535; + case 3: + return 16777215; + case 4: + return (uint32) 4294967295U; + default: + DBUG_ASSERT(0); // we should never go here + return 0; + } +} + + +/***************************************************************************** + Warning handling +*****************************************************************************/ /* Produce warning or note about data saved into field @@ -8301,18 +8373,20 @@ create_field::create_field(Field *old_field,Field *orig_field) if count_cuted_fields == FIELD_CHECK_IGNORE for current thread. RETURN VALUE - true - if count_cuted_fields == FIELD_CHECK_IGNORE - false - otherwise + 1 if count_cuted_fields == FIELD_CHECK_IGNORE + 0 otherwise */ + bool -Field::set_warning(uint level, uint code, int cuted_increment) +Field::set_warning(MYSQL_ERROR::enum_warning_level level, uint code, + int cuted_increment) { THD *thd= table->in_use; if (thd->count_cuted_fields) { thd->cuted_fields+= cuted_increment; - push_warning_printf(thd, (MYSQL_ERROR::enum_warning_level) level, - code, ER(code), field_name, thd->row_count); + push_warning_printf(thd, level, code, ER(code), field_name, + thd->row_count); return 0; } return 1; @@ -8336,8 +8410,9 @@ Field::set_warning(uint level, uint code, int cuted_increment) fields counter if count_cuted_fields == FIELD_CHECK_IGNORE for current thread. */ + void -Field::set_datetime_warning(const uint level, const uint code, +Field::set_datetime_warning(MYSQL_ERROR::enum_warning_level level, uint code, const char *str, uint str_length, timestamp_type ts_type, int cuted_increment) { @@ -8364,8 +8439,9 @@ Field::set_datetime_warning(const uint level, const uint code, fields counter if count_cuted_fields == FIELD_CHECK_IGNORE for current thread. */ + void -Field::set_datetime_warning(const uint level, const uint code, +Field::set_datetime_warning(MYSQL_ERROR::enum_warning_level level, uint code, longlong nr, timestamp_type ts_type, int cuted_increment) { @@ -8395,8 +8471,9 @@ Field::set_datetime_warning(const uint level, const uint code, fields counter if count_cuted_fields == FIELD_CHECK_IGNORE for current thread. */ + void -Field::set_datetime_warning(const uint level, const uint code, +Field::set_datetime_warning(MYSQL_ERROR::enum_warning_level level, uint code, double nr, timestamp_type ts_type) { if (table->in_use->really_abort_on_warning() || @@ -8409,30 +8486,3 @@ Field::set_datetime_warning(const uint level, const uint code, field_name); } } - -/* - maximum possible display length for blob - - SYNOPSIS - Field_blob::max_length() - - RETURN - length -*/ -uint32 Field_blob::max_length() -{ - switch (packlength) - { - case 1: - return 255; - case 2: - return 65535; - case 3: - return 16777215; - case 4: - return (uint32) 4294967295U; - default: - DBUG_ASSERT(0); // we should never go here - return 0; - } -} diff --git a/sql/field.h b/sql/field.h index ce6753400bb..365f2c323b9 100644 --- a/sql/field.h +++ b/sql/field.h @@ -280,17 +280,17 @@ public: virtual CHARSET_INFO *sort_charset(void) const { return charset(); } virtual bool has_charset(void) const { return FALSE; } virtual void set_charset(CHARSET_INFO *charset) { } - bool set_warning(unsigned int level, unsigned int code, + bool set_warning(MYSQL_ERROR::enum_warning_level, unsigned int code, int cuted_increment); bool check_int(const char *str, int length, const char *int_end, CHARSET_INFO *cs); - void set_datetime_warning(const uint level, const uint code, + void set_datetime_warning(MYSQL_ERROR::enum_warning_level, uint code, const char *str, uint str_len, timestamp_type ts_type, int cuted_increment); - void set_datetime_warning(const uint level, const uint code, + void set_datetime_warning(MYSQL_ERROR::enum_warning_level, uint code, longlong nr, timestamp_type ts_type, int cuted_increment); - void set_datetime_warning(const uint level, const uint code, + void set_datetime_warning(MYSQL_ERROR::enum_warning_level, const uint code, double nr, timestamp_type ts_type); inline bool check_overflow(int op_result) { diff --git a/sql/field_conv.cc b/sql/field_conv.cc index 57161a7063e..bbdd6619bf3 100644 --- a/sql/field_conv.cc +++ b/sql/field_conv.cc @@ -508,8 +508,16 @@ void (*Copy_field::get_copy_func(Field *to,Field *from))(Copy_field*) // Check if identical fields if (from->result_type() == STRING_RESULT) { + /* + If we are copying date or datetime's we have to check the dates + if we don't allow 'all' dates. +p */ if (to->real_type() != from->real_type() || - !compatible_db_low_byte_first) + !compatible_db_low_byte_first || + ((to->table->in_use->variables.sql_mode & + (MODE_NO_ZERO_IN_DATE | MODE_NO_ZERO_DATE | MODE_INVALID_DATES)) && + to->type() == FIELD_TYPE_DATE || + to->type() == FIELD_TYPE_DATETIME)) { if (from->real_type() == FIELD_TYPE_ENUM || from->real_type() == FIELD_TYPE_SET) @@ -590,7 +598,11 @@ void field_conv(Field *to,Field *from) (to->field_length == from->field_length && (((Field_num*)to)->dec == ((Field_num*)from)->dec))) && from->charset() == to->charset() && - to->table->s->db_low_byte_first == from->table->s->db_low_byte_first) + to->table->s->db_low_byte_first == from->table->s->db_low_byte_first && + (!(to->table->in_use->variables.sql_mode & + (MODE_NO_ZERO_IN_DATE | MODE_NO_ZERO_DATE | MODE_INVALID_DATES)) || + to->type() != FIELD_TYPE_DATE && + to->type() != FIELD_TYPE_DATETIME)) { // Identical fields memcpy(to->ptr,from->ptr,to->pack_length()); return; diff --git a/sql/item.cc b/sql/item.cc index 48faa3509f4..1572efb0f23 100644 --- a/sql/item.cc +++ b/sql/item.cc @@ -46,7 +46,7 @@ void Hybrid_type_traits::fix_length_and_dec(Item *item, Item *arg) const const Hybrid_type_traits *Hybrid_type_traits::instance() { - const static Hybrid_type_traits real_traits; + static const Hybrid_type_traits real_traits; return &real_traits; } @@ -70,7 +70,7 @@ Hybrid_type_traits::val_str(Hybrid_type *val, String *to, uint8 decimals) const const Hybrid_type_traits_decimal *Hybrid_type_traits_decimal::instance() { - const static Hybrid_type_traits_decimal decimal_traits; + static const Hybrid_type_traits_decimal decimal_traits; return &decimal_traits; } @@ -146,7 +146,7 @@ Hybrid_type_traits_decimal::val_str(Hybrid_type *val, String *to, const Hybrid_type_traits_integer *Hybrid_type_traits_integer::instance() { - const static Hybrid_type_traits_integer integer_traits; + static const Hybrid_type_traits_integer integer_traits; return &integer_traits; } @@ -1455,6 +1455,64 @@ void Item_string::print(String *str) } +inline bool check_if_only_end_space(CHARSET_INFO *cs, char *str, char *end) +{ + return str+ cs->cset->scan(cs, str, end, MY_SEQ_SPACES) == end; +} + + +double Item_string::val_real() +{ + DBUG_ASSERT(fixed == 1); + int error; + char *end, *org_end; + double tmp; + CHARSET_INFO *cs= str_value.charset(); + + org_end= (char*) str_value.ptr() + str_value.length(); + tmp= my_strntod(cs, (char*) str_value.ptr(), str_value.length(), &end, + &error); + if (error || (end != org_end && !check_if_only_end_space(cs, end, org_end))) + { + /* + We can use str_value.ptr() here as Item_string is gurantee to put an + end \0 here. + */ + push_warning_printf(current_thd, MYSQL_ERROR::WARN_LEVEL_WARN, + ER_TRUNCATED_WRONG_VALUE, + ER(ER_TRUNCATED_WRONG_VALUE), "DOUBLE", + str_value.ptr()); + } + return tmp; +} + + +longlong Item_string::val_int() +{ + DBUG_ASSERT(fixed == 1); + int err; + longlong tmp; + char *end= (char*) str_value.ptr()+ str_value.length(); + char *org_end= end; + CHARSET_INFO *cs= str_value.charset(); + + tmp= (*(cs->cset->my_strtoll10))(cs, str_value.ptr(), &end, &err); + /* + TODO: Give error if we wanted a signed integer and we got an unsigned + one + */ + if (err > 0 || + (end != org_end && !check_if_only_end_space(cs, end, org_end))) + { + push_warning_printf(current_thd, MYSQL_ERROR::WARN_LEVEL_WARN, + ER_TRUNCATED_WRONG_VALUE, + ER(ER_TRUNCATED_WRONG_VALUE), "INTEGER", + str_value.ptr()); + } + return tmp; +} + + my_decimal *Item_string::val_decimal(my_decimal *decimal_value) { /* following assert is redundant, because fixed=1 assigned in constructor */ @@ -4848,7 +4906,7 @@ bool Item_type_holder::join_types(THD *thd, Item *item) int intp2= max_length - min(decimals, NOT_FIXED_DEC - 1); /* can't be overflow because it work only for decimals (no strings) */ int dec_length= max(intp1, intp2) + decimals; - max_length= max(max_length, max(item_length, dec_length)); + max_length= max(max_length, (uint) max(item_length, dec_length)); /* we can't allow decimals to be NOT_FIXED_DEC, to prevent creation decimal with max precision (see Field_new_decimal constcuctor) @@ -4875,8 +4933,8 @@ bool Item_type_holder::join_types(THD *thd, Item *item) } maybe_null|= item->maybe_null; get_full_info(item); - DBUG_PRINT("info:", ("become type %d len %d, dec %d", - fld_type, max_length, decimals)); + DBUG_PRINT("info", ("become type: %d len: %u dec: %u", + (int) fld_type, max_length, (uint) decimals)); DBUG_RETURN(FALSE); } diff --git a/sql/item.h b/sql/item.h index 0a2be74c76c..9acd8ec27fc 100644 --- a/sql/item.h +++ b/sql/item.h @@ -1098,21 +1098,8 @@ public: fixed= 1; } enum Type type() const { return STRING_ITEM; } - double val_real() - { - DBUG_ASSERT(fixed == 1); - int err_not_used; - char *end_not_used; - return my_strntod(str_value.charset(), (char*) str_value.ptr(), - str_value.length(), &end_not_used, &err_not_used); - } - longlong val_int() - { - DBUG_ASSERT(fixed == 1); - int err; - return my_strntoll(str_value.charset(), str_value.ptr(), - str_value.length(), 10, (char**) 0, &err); - } + double val_real(); + longlong val_int(); String *val_str(String*) { DBUG_ASSERT(fixed == 1); diff --git a/sql/item_row.cc b/sql/item_row.cc index 00d849e55de..0c8baa332ca 100644 --- a/sql/item_row.cc +++ b/sql/item_row.cc @@ -73,8 +73,8 @@ bool Item_row::fix_fields(THD *thd, TABLE_LIST *tabl, Item **ref) with_null|= item->null_inside(); else { - item->val_int(); - with_null|= item->null_value; + if (item->is_null()) + with_null|= 1; } } maybe_null|= item->maybe_null; diff --git a/sql/item_sum.cc b/sql/item_sum.cc index 3dbc3833f9e..66b64128dab 100644 --- a/sql/item_sum.cc +++ b/sql/item_sum.cc @@ -2966,8 +2966,7 @@ bool Item_func_group_concat::setup(THD *thd) DBUG_RETURN(TRUE); if (item->const_item()) { - (void) item->val_int(); - if (item->null_value) + if (item->is_null()) { always_null= 1; DBUG_RETURN(FALSE); diff --git a/sql/item_timefunc.cc b/sql/item_timefunc.cc index c603d41fa2e..1ea20e9d7cc 100644 --- a/sql/item_timefunc.cc +++ b/sql/item_timefunc.cc @@ -2181,39 +2181,47 @@ String *Item_char_typecast::val_str(String *str) res->set_charset(cast_cs); /* - Cut the tail if cast with length - and the result is longer than cast length, e.g. - CAST('string' AS CHAR(1)) + Cut the tail if cast with length + and the result is longer than cast length, e.g. + CAST('string' AS CHAR(1)) */ if (cast_length >= 0 && (res->length() > (length= (uint32) res->charpos(cast_length)))) { // Safe even if const arg + char char_type[40]; + my_snprintf(char_type, sizeof(char_type), "CHAR(%lu)", length); + if (!res->alloced_length()) { // Don't change const str str_value= *res; // Not malloced string res= &str_value; } + push_warning_printf(current_thd, MYSQL_ERROR::WARN_LEVEL_WARN, + ER_TRUNCATED_WRONG_VALUE, + ER(ER_TRUNCATED_WRONG_VALUE), char_type, + res->c_ptr()); res->length((uint) length); } null_value= 0; return res; } + void Item_char_typecast::fix_length_and_dec() { uint32 char_length; - /* - We always force character set conversion if cast_cs - is a multi-byte character set. It garantees that the - result of CAST is a well-formed string. - For single-byte character sets we allow just to copy - from the argument. A single-byte character sets string - is always well-formed. + /* + We always force character set conversion if cast_cs is a + multi-byte character set. It garantees that the result of CAST is + a well-formed string. For single-byte character sets we allow + just to copy from the argument. A single-byte character sets + string is always well-formed. */ - charset_conversion= (cast_cs->mbmaxlen > 1) || - !my_charset_same(args[0]->collation.collation, cast_cs) && - args[0]->collation.collation != &my_charset_bin && - cast_cs != &my_charset_bin; + charset_conversion= ((cast_cs->mbmaxlen > 1) || + !my_charset_same(args[0]->collation.collation, + cast_cs) && + args[0]->collation.collation != &my_charset_bin && + cast_cs != &my_charset_bin); collation.set(cast_cs, DERIVATION_IMPLICIT); char_length= (cast_length >= 0) ? cast_length : args[0]->max_length/args[0]->collation.collation->mbmaxlen; diff --git a/sql/my_decimal.cc b/sql/my_decimal.cc index 19b6abd7243..b4bbef4a637 100644 --- a/sql/my_decimal.cc +++ b/sql/my_decimal.cc @@ -175,7 +175,7 @@ int str2my_decimal(uint mask, const char *from, uint length, err= string2decimal((char *)from, (decimal_t*) decimal_value, &end); if (end != from_end && !err) { - /* Give warining if there is something other than end space */ + /* Give warning if there is something other than end space */ for ( ; end < from_end; end++) { if (!my_isspace(&my_charset_latin1, *end)) diff --git a/sql/mysql_priv.h b/sql/mysql_priv.h index 2685da9780b..7290cfd8e0d 100644 --- a/sql/mysql_priv.h +++ b/sql/mysql_priv.h @@ -442,7 +442,6 @@ extern ulong server_id, concurrency; typedef my_bool (*qc_engine_callback)(THD *thd, char *table_key, uint key_length, ulonglong *engine_data); - #include "sql_string.h" #include "sql_list.h" #include "sql_map.h" @@ -450,6 +449,7 @@ typedef my_bool (*qc_engine_callback)(THD *thd, char *table_key, #include "handler.h" #include "parse_file.h" #include "table.h" +#include "sql_error.h" #include "field.h" /* Field definitions */ #include "protocol.h" #include "sql_udf.h" @@ -651,11 +651,6 @@ int prepare_create_field(create_field *sql_field, uint *blob_columns, int *timestamps, int *timestamps_with_niladic, uint table_flags); -int mysql_prepare_table(THD *thd, HA_CREATE_INFO *create_info, - List<create_field> &fields, - List<Key> &keys, uint &db_options, - handler *file, KEY *&key_info_buffer, - uint &key_count, int select_field_count); bool mysql_create_table(THD *thd,const char *db, const char *table_name, HA_CREATE_INFO *create_info, List<create_field> &fields, List<Key> &keys, @@ -830,14 +825,6 @@ void mysql_stmt_get_longdata(THD *thd, char *pos, ulong packet_length); void reset_stmt_for_execute(THD *thd, LEX *lex); void init_stmt_after_parse(THD*, LEX*); -/* sql_error.cc */ -MYSQL_ERROR *push_warning(THD *thd, MYSQL_ERROR::enum_warning_level level, uint code, - const char *msg); -void push_warning_printf(THD *thd, MYSQL_ERROR::enum_warning_level level, - uint code, const char *format, ...); -void mysql_reset_errors(THD *thd, bool force); -bool mysqld_show_warnings(THD *thd, ulong levels_to_show); - /* sql_handler.cc */ bool mysql_ha_open(THD *thd, TABLE_LIST *tables, bool reopen); bool mysql_ha_close(THD *thd, TABLE_LIST *tables); diff --git a/sql/mysqld.cc b/sql/mysqld.cc index 95de170b99d..e1303585114 100644 --- a/sql/mysqld.cc +++ b/sql/mysqld.cc @@ -1036,8 +1036,8 @@ void clean_up(bool print_message) (void) my_delete(pidfile_name,MYF(0)); // This may not always exist #endif finish_client_errs(); - const char **errmsgs= my_error_unregister(ER_ERROR_FIRST, ER_ERROR_LAST); - x_free((gptr) errmsgs); /* Free messages */ + my_free((gptr) my_error_unregister(ER_ERROR_FIRST, ER_ERROR_LAST), + MYF(MY_WME | MY_FAE | MY_ALLOW_ZERO_PTR)); DBUG_PRINT("quit", ("Error messages freed")); /* Tell main we are ready */ (void) pthread_mutex_lock(&LOCK_thread_count); diff --git a/sql/sp.cc b/sql/sp.cc index 84169ab8172..23d389cd299 100644 --- a/sql/sp.cc +++ b/sql/sp.cc @@ -64,8 +64,7 @@ db_find_routine_aux(THD *thd, int type, sp_name *name, enum thr_lock_type ltype, TABLE **tablep, bool *opened) { TABLE *table; - byte key[64+64+1]; // db, name, type - uint keylen; + byte key[NAME_LEN*2+4+1]; // db, name, optional key length type DBUG_ENTER("db_find_routine_aux"); DBUG_PRINT("enter", ("type: %d name: %*s", type, name->m_name.length, name->m_name.str)); @@ -78,20 +77,6 @@ db_find_routine_aux(THD *thd, int type, sp_name *name, if (!mysql_proc_table_exists && ltype == TL_READ) DBUG_RETURN(SP_OPEN_TABLE_FAILED); - // Put the key used to read the row together - keylen= name->m_db.length; - if (keylen > 64) - keylen= 64; - memcpy(key, name->m_db.str, keylen); - memset(key+keylen, (int)' ', 64-keylen); // Pad with space - keylen= name->m_name.length; - if (keylen > 64) - keylen= 64; - memcpy(key+64, name->m_name.str, keylen); - memset(key+64+keylen, (int)' ', 64-keylen); // Pad with space - key[128]= type; - keylen= sizeof(key); - if (thd->lex->proc_table) table= thd->lex->proc_table->table; else @@ -120,8 +105,22 @@ db_find_routine_aux(THD *thd, int type, sp_name *name, } mysql_proc_table_exists= 1; + /* + Create key to find row. We have to use field->store() to be able to + handle VARCHAR and CHAR fields. + Assumption here is that the three first fields in the table are + 'db', 'name' and 'type' and the first key is the primary key over the + same fields. + */ + table->field[0]->store(name->m_db.str, name->m_db.length, &my_charset_bin); + table->field[1]->store(name->m_name.str, name->m_name.length, + &my_charset_bin); + table->field[2]->store((longlong) type); + key_copy(key, table->record[0], table->key_info, + table->key_info->key_length); + if (table->file->index_read_idx(table->record[0], 0, - key, keylen, + key, table->key_info->key_length, HA_READ_KEY_EXACT)) { *tablep= NULL; diff --git a/sql/sql_class.h b/sql/sql_class.h index 39d5c37462b..e712af19a5c 100644 --- a/sql/sql_class.h +++ b/sql/sql_class.h @@ -482,27 +482,6 @@ public: }; -class MYSQL_ERROR: public Sql_alloc -{ -public: - enum enum_warning_level - { WARN_LEVEL_NOTE, WARN_LEVEL_WARN, WARN_LEVEL_ERROR, WARN_LEVEL_END}; - - uint code; - enum_warning_level level; - char *msg; - - MYSQL_ERROR(THD *thd, uint code_arg, enum_warning_level level_arg, - const char *msg_arg) - :code(code_arg), level(level_arg) - { - if (msg_arg) - set_msg(thd, msg_arg); - } - void set_msg(THD *thd, const char *msg_arg); -}; - - class delayed_insert; class select_result; diff --git a/sql/sql_error.h b/sql/sql_error.h new file mode 100644 index 00000000000..223b50be744 --- /dev/null +++ b/sql/sql_error.h @@ -0,0 +1,42 @@ +/* Copyright (C) 2000-2003 MySQL AB + + This program is free software; you can redistribute it and/or modify + it under the terms of the GNU General Public License as published by + the Free Software Foundation; either version 2 of the License, or + (at your option) any later version. + + This program is distributed in the hope that it will be useful, + but WITHOUT ANY WARRANTY; without even the implied warranty of + MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the + GNU General Public License for more details. + + You should have received a copy of the GNU General Public License + along with this program; if not, write to the Free Software + Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA */ + +class MYSQL_ERROR: public Sql_alloc +{ +public: + enum enum_warning_level + { WARN_LEVEL_NOTE, WARN_LEVEL_WARN, WARN_LEVEL_ERROR, WARN_LEVEL_END}; + + uint code; + enum_warning_level level; + char *msg; + + MYSQL_ERROR(THD *thd, uint code_arg, enum_warning_level level_arg, + const char *msg_arg) + :code(code_arg), level(level_arg) + { + if (msg_arg) + set_msg(thd, msg_arg); + } + void set_msg(THD *thd, const char *msg_arg); +}; + +MYSQL_ERROR *push_warning(THD *thd, MYSQL_ERROR::enum_warning_level level, + uint code, const char *msg); +void push_warning_printf(THD *thd, MYSQL_ERROR::enum_warning_level level, + uint code, const char *format, ...); +void mysql_reset_errors(THD *thd, bool force); +bool mysqld_show_warnings(THD *thd, ulong levels_to_show); diff --git a/sql/sql_load.cc b/sql/sql_load.cc index a0fed715405..c827bbace3e 100644 --- a/sql/sql_load.cc +++ b/sql/sql_load.cc @@ -671,7 +671,7 @@ read_sep_field(THD *thd, COPY_INFO &info, TABLE_LIST *table_list, if (field->type() == FIELD_TYPE_TIMESTAMP) ((Field_timestamp*) field)->set_time(); else if (field != table->next_number_field) - field->set_warning((uint) MYSQL_ERROR::WARN_LEVEL_WARN, + field->set_warning(MYSQL_ERROR::WARN_LEVEL_WARN, ER_WARN_NULL_TO_NOTNULL, 1); } } diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc index c0032742a11..1d7eba4ce43 100644 --- a/sql/sql_parse.cc +++ b/sql/sql_parse.cc @@ -1909,10 +1909,11 @@ bool dispatch_command(enum enum_server_command command, THD *thd, #endif ulong uptime = (ulong) (thd->start_time - start_time); sprintf((char*) buff, - "Uptime: %ld Threads: %d Questions: %lu Slow queries: %ld Opens: %ld Flush tables: %ld Open tables: %u Queries per second avg: %.3f", + "Uptime: %ld Threads: %d Questions: %lu Slow queries: %lu Opens: %ld Flush tables: %ld Open tables: %u Queries per second avg: %.3f", uptime, - (int) thread_count,thd->query_id,thd->status_var.long_query_count, - thd->status_var.opened_tables,refresh_version, cached_tables(), + (int) thread_count, (ulong) thd->query_id, + (ulong) thd->status_var.long_query_count, + thd->status_var.opened_tables, refresh_version, cached_tables(), uptime ? (float)thd->query_id/(float)uptime : 0); #ifdef SAFEMALLOC if (sf_malloc_cur_memory) // Using SAFEMALLOC diff --git a/sql/sql_table.cc b/sql/sql_table.cc index 4105d63330a..49ecd2591dd 100644 --- a/sql/sql_table.cc +++ b/sql/sql_table.cc @@ -633,11 +633,12 @@ int prepare_create_field(create_field *sql_field, -1 error */ -int mysql_prepare_table(THD *thd, HA_CREATE_INFO *create_info, - List<create_field> &fields, - List<Key> &keys, bool tmp_table, uint &db_options, - handler *file, KEY *&key_info_buffer, - uint *key_count, int select_field_count) +static int mysql_prepare_table(THD *thd, HA_CREATE_INFO *create_info, + List<create_field> *fields, + List<Key> *keys, bool tmp_table, + uint *db_options, + handler *file, KEY **key_info_buffer, + uint *key_count, int select_field_count) { const char *key_name; create_field *sql_field,*dup_field; @@ -649,11 +650,11 @@ int mysql_prepare_table(THD *thd, HA_CREATE_INFO *create_info, int timestamps= 0, timestamps_with_niladic= 0; int field_no,dup_no; int select_field_pos,auto_increment=0; - List_iterator<create_field> it(fields),it2(fields); + List_iterator<create_field> it(*fields),it2(*fields); uint total_uneven_bit_length= 0; DBUG_ENTER("mysql_prepare_table"); - select_field_pos=fields.elements - select_field_count; + select_field_pos= fields->elements - select_field_count; null_fields=blob_columns=0; create_info->varchar= 0; @@ -858,11 +859,11 @@ int mysql_prepare_table(THD *thd, HA_CREATE_INFO *create_info, if ((sql_field->flags & BLOB_FLAG) || sql_field->sql_type == MYSQL_TYPE_VARCHAR && create_info->row_type != ROW_TYPE_FIXED) - db_options|= HA_OPTION_PACK_RECORD; + (*db_options)|= HA_OPTION_PACK_RECORD; it2.rewind(); } /* If fixed row records, we need one bit to check for deleted rows */ - if (!(db_options & HA_OPTION_PACK_RECORD)) + if (!((*db_options) & HA_OPTION_PACK_RECORD)) null_fields++; pos= (null_fields + total_uneven_bit_length + 7) / 8; @@ -910,7 +911,7 @@ int mysql_prepare_table(THD *thd, HA_CREATE_INFO *create_info, /* Create keys */ - List_iterator<Key> key_iterator(keys), key_iterator2(keys); + List_iterator<Key> key_iterator(*keys), key_iterator2(*keys); uint key_parts=0, fk_key_count=0; bool primary_key=0,unique_key=0; Key *key, *key2; @@ -997,9 +998,9 @@ int mysql_prepare_table(THD *thd, HA_CREATE_INFO *create_info, DBUG_RETURN(-1); } - key_info_buffer=key_info=(KEY*) sql_calloc(sizeof(KEY)* *key_count); + (*key_info_buffer) = key_info= (KEY*) sql_calloc(sizeof(KEY)* *key_count); key_part_info=(KEY_PART_INFO*) sql_calloc(sizeof(KEY_PART_INFO)*key_parts); - if (!key_info_buffer || ! key_part_info) + if (!*key_info_buffer || ! key_part_info) DBUG_RETURN(-1); // Out of memory key_iterator.rewind(); @@ -1273,7 +1274,7 @@ int mysql_prepare_table(THD *thd, HA_CREATE_INFO *create_info, } key_part_info->length=(uint16) length; /* Use packed keys for long strings on the first column */ - if (!(db_options & HA_OPTION_NO_PACK_KEYS) && + if (!((*db_options) & HA_OPTION_NO_PACK_KEYS) && (length >= KEY_DEFAULT_PACK_LENGTH && (sql_field->sql_type == MYSQL_TYPE_STRING || sql_field->sql_type == MYSQL_TYPE_VARCHAR || @@ -1304,8 +1305,8 @@ int mysql_prepare_table(THD *thd, HA_CREATE_INFO *create_info, } else if (!(key_name = key->name)) key_name=make_unique_key_name(sql_field->field_name, - key_info_buffer,key_info); - if (check_if_keyname_exists(key_name,key_info_buffer,key_info)) + *key_info_buffer, key_info); + if (check_if_keyname_exists(key_name, *key_info_buffer, key_info)) { my_error(ER_DUP_KEYNAME, MYF(0), key_name); DBUG_RETURN(-1); @@ -1340,7 +1341,7 @@ int mysql_prepare_table(THD *thd, HA_CREATE_INFO *create_info, DBUG_RETURN(-1); } /* Sort keys in optimized order */ - qsort((gptr) key_info_buffer, *key_count, sizeof(KEY), + qsort((gptr) *key_info_buffer, *key_count, sizeof(KEY), (qsort_cmp) sort_keys); DBUG_RETURN(0); @@ -1406,7 +1407,7 @@ bool mysql_create_table(THD *thd,const char *db, const char *table_name, ha_get_storage_engine(new_db_type), table_name); } - db_options=create_info->table_options; + db_options= create_info->table_options; if (create_info->row_type == ROW_TYPE_DYNAMIC) db_options|=HA_OPTION_PACK_RECORD; alias= table_case_name(create_info, table_name); @@ -1445,9 +1446,9 @@ bool mysql_create_table(THD *thd,const char *db, const char *table_name, create_info->default_table_charset= db_info.default_table_charset; } - if (mysql_prepare_table(thd, create_info, fields, - keys, internal_tmp_table, db_options, file, - key_info_buffer, &key_count, + if (mysql_prepare_table(thd, create_info, &fields, + &keys, internal_tmp_table, &db_options, file, + &key_info_buffer, &key_count, select_field_count)) DBUG_RETURN(TRUE); @@ -2719,9 +2720,9 @@ int mysql_create_indexes(THD *thd, TABLE_LIST *table_list, List<Key> &keys) create_info.db_type=DB_TYPE_DEFAULT; create_info.default_table_charset= thd->variables.collation_database; db_options= 0; - if (mysql_prepare_table(thd, &create_info, fields, - keys, /*tmp_table*/ 0, db_options, table->file, - key_info_buffer, key_count, + if (mysql_prepare_table(thd, &create_info, &fields, + &keys, /*tmp_table*/ 0, &db_options, table->file, + &key_info_buffer, key_count, /*select_field_count*/ 0)) DBUG_RETURN(-1); @@ -2852,9 +2853,9 @@ int mysql_drop_indexes(THD *thd, TABLE_LIST *table_list, { db_options= 0; if (table->file->drop_index(table, key_numbers, key_count)|| - mysql_prepare_table(thd, &create_info, fields, - keys, /*tmp_table*/ 0, db_options, table->file, - key_info_buffer, key_count, + mysql_prepare_table(thd, &create_info, &fields, + &keys, /*tmp_table*/ 0, &db_options, table->file, + &key_info_buffer, key_count, /*select_field_count*/ 0)|| (snprintf(path, sizeof(path), "%s/%s/%s%s", mysql_data_home, table_list->db, (lower_case_table_names == 2)? @@ -3679,6 +3680,13 @@ copy_data_between_tables(TABLE *from,TABLE *to, if (to->file->external_lock(thd, F_WRLCK)) DBUG_RETURN(-1); + + /* We can abort alter table for any table type */ + thd->no_trans_update= 0; + thd->abort_on_warning= !ignore && test(thd->variables.sql_mode & + (MODE_STRICT_TRANS_TABLES | + MODE_STRICT_ALL_TABLES)); + from->file->info(HA_STATUS_VARIABLE); to->file->start_bulk_insert(from->file->records); @@ -3758,6 +3766,7 @@ copy_data_between_tables(TABLE *from,TABLE *to, else to->next_number_field->reset(); } + for (Copy_field *copy_ptr=copy ; copy_ptr != copy_end ; copy_ptr++) { copy_ptr->do_copy(copy_ptr); @@ -3802,6 +3811,7 @@ copy_data_between_tables(TABLE *from,TABLE *to, err: thd->variables.sql_mode= save_sql_mode; + thd->abort_on_warning= 0; free_io_cache(from); *copied= found_count; *deleted=delete_count; diff --git a/sql/time.cc b/sql/time.cc index f1d21915c23..a3ec2283860 100644 --- a/sql/time.cc +++ b/sql/time.cc @@ -186,6 +186,7 @@ ulong convert_month_to_period(ulong month) NOTE See description of str_to_datetime() for more information. */ + timestamp_type str_to_datetime_with_warn(const char *str, uint length, TIME *l_time, uint flags) @@ -199,7 +200,7 @@ str_to_datetime_with_warn(const char *str, uint length, TIME *l_time, (MODE_INVALID_DATES | MODE_NO_ZERO_DATE))), &was_cut); - if (was_cut) + if (was_cut || ts_type <= MYSQL_TIMESTAMP_ERROR) make_truncated_value_warning(current_thd, str, length, ts_type, NullS); return ts_type; } @@ -712,9 +713,9 @@ void make_truncated_value_warning(THD *thd, const char *str_val, else cs->cset->snprintf(cs, warn_buff, sizeof(warn_buff), ER(ER_TRUNCATED_WRONG_VALUE), - type_str, str.ptr()); - push_warning_printf(thd, MYSQL_ERROR::WARN_LEVEL_WARN, - ER_TRUNCATED_WRONG_VALUE, warn_buff); + type_str, str.c_ptr()); + push_warning(thd, MYSQL_ERROR::WARN_LEVEL_WARN, + ER_TRUNCATED_WRONG_VALUE, warn_buff); } diff --git a/sql/unireg.cc b/sql/unireg.cc index 3e85767dc86..57e2c1029f1 100644 --- a/sql/unireg.cc +++ b/sql/unireg.cc @@ -40,7 +40,7 @@ static bool pack_header(uchar *forminfo,enum db_type table_type, static uint get_interval_id(uint *int_count,List<create_field> &create_fields, create_field *last_field); static bool pack_fields(File file, List<create_field> &create_fields); -static bool make_empty_rec(int file, enum db_type table_type, +static bool make_empty_rec(THD *thd, int file, enum db_type table_type, uint table_options, List<create_field> &create_fields, uint reclength,uint null_fields); @@ -134,7 +134,7 @@ bool mysql_create_frm(THD *thd, my_string file_name, VOID(my_seek(file, (ulong) uint2korr(fileinfo+6)+ (ulong) key_buff_length, MY_SEEK_SET,MYF(0))); - if (make_empty_rec(file,create_info->db_type,create_info->table_options, + if (make_empty_rec(thd,file,create_info->db_type,create_info->table_options, create_fields,reclength,null_fields)) goto err; @@ -640,7 +640,7 @@ static bool pack_fields(File file,List<create_field> &create_fields) /* save an empty record on start of formfile */ -static bool make_empty_rec(File file,enum db_type table_type, +static bool make_empty_rec(THD *thd, File file,enum db_type table_type, uint table_options, List<create_field> &create_fields, uint reclength, uint null_fields) @@ -652,6 +652,7 @@ static bool make_empty_rec(File file,enum db_type table_type, TABLE table; create_field *field; handler *handler; + enum_check_fields old_count_cuted_fields= thd->count_cuted_fields; DBUG_ENTER("make_empty_rec"); /* We need a table to generate columns for default values */ @@ -666,7 +667,7 @@ static bool make_empty_rec(File file,enum db_type table_type, DBUG_RETURN(1); } - table.in_use= current_thd; + table.in_use= thd; table.s->db_low_byte_first= handler->low_byte_first(); table.s->blob_ptr_size= portable_sizeof_char_ptr; @@ -681,6 +682,7 @@ static bool make_empty_rec(File file,enum db_type table_type, null_pos=buff; List_iterator<create_field> it(create_fields); + thd->count_cuted_fields= CHECK_FIELD_WARN; // To find wrong default values while ((field=it++)) { Field *regfield=make_field((char*) buff+field->offset,field->length, @@ -709,7 +711,14 @@ static bool make_empty_rec(File file,enum db_type table_type, if (field->def && (regfield->real_type() != FIELD_TYPE_YEAR || field->def->val_int() != 0)) - (void) field->def->save_in_field(regfield, 1); + { + if (field->def->save_in_field(regfield, 1)) + { + my_error(ER_INVALID_DEFAULT, MYF(0), regfield->field_name); + error= 1; + goto err; + } + } else if (regfield->real_type() == FIELD_TYPE_ENUM && (field->flags & NOT_NULL_FLAG)) { @@ -728,7 +737,10 @@ static bool make_empty_rec(File file,enum db_type table_type, /* Fill not used startpos */ bfill((byte*) buff+null_length,firstpos-null_length,255); error=(int) my_write(file,(byte*) buff,(uint) reclength,MYF_RW); + +err: my_free((gptr) buff,MYF(MY_FAE)); delete handler; + thd->count_cuted_fields= old_count_cuted_fields; DBUG_RETURN(error); } /* make_empty_rec */ diff --git a/tests/mysql_client_test.c b/tests/mysql_client_test.c index 0f117b640c3..2dd5fda6f05 100644 --- a/tests/mysql_client_test.c +++ b/tests/mysql_client_test.c @@ -253,7 +253,12 @@ static void client_connect() mysql_autocommit(mysql, TRUE); if (!opt_silent) + { + fprintf(stdout, "\nConnected to MySQL server version: %s (%lu)\n", + mysql_get_server_info(mysql), + (ulong) mysql_get_server_version(mysql)); fprintf(stdout, "\n Creating a test database '%s' ...", current_db); + } strxmov(query, "CREATE DATABASE IF NOT EXISTS ", current_db, NullS); rc= mysql_query(mysql, query); @@ -12662,7 +12667,7 @@ static void test_view_sp_list_fields() int rc; MYSQL_RES *res; - myheader("test_view_insert_fields"); + myheader("test_view_sp_list_fields"); rc= mysql_query(mysql, "DROP FUNCTION IF EXISTS f1"); myquery(rc); |