diff options
Diffstat (limited to 'mysql-test')
| -rw-r--r-- | mysql-test/r/date_formats.result | 39 | ||||
| -rw-r--r-- | mysql-test/r/ps_1general.result | 2 | ||||
| -rw-r--r-- | mysql-test/r/strict.result | 60 | ||||
| -rw-r--r-- | mysql-test/r/type_decimal.result | 55 | ||||
| -rw-r--r-- | mysql-test/t/date_formats.test | 1 | ||||
| -rw-r--r-- | mysql-test/t/strict.test | 93 | ||||
| -rw-r--r-- | mysql-test/t/type_decimal.test | 41 |
7 files changed, 253 insertions, 38 deletions
diff --git a/mysql-test/r/date_formats.result b/mysql-test/r/date_formats.result index 2db014c4a52..8217a0e7ba6 100644 --- a/mysql-test/r/date_formats.result +++ b/mysql-test/r/date_formats.result @@ -79,6 +79,11 @@ concat('%d-%m-%Y',' ','%H:%i:%s.%f')); str_to_date(concat('15-01-2001',' 2:59:58.999'), concat('%d-%m-%Y',' ','%H:%i:%s.%f')) 2001-01-15 02:59:58.999000 +select STR_TO_DATE('2004.12.12 22.30.61','%Y.%m.%d %T'); +STR_TO_DATE('2004.12.12 22.30.61','%Y.%m.%d %T') +NULL +Warnings: +Error 1411 Incorrect time value: '22.30.61' for function str_to_time create table t1 (date char(30), format char(30) not null); insert into t1 values ('2003-01-02 10:11:12', '%Y-%m-%d %H:%i:%S'), @@ -335,6 +340,22 @@ Tuesday 52 2001 %W %V %Y NULL Tuesday 52 2001 %W %u %x NULL 7 53 1998 %w %u %Y NULL NULL %m.%d.%Y NULL +Warnings: +Error 1411 Incorrect datetime value: '2003-01-02 10:11:12 PM' for function str_to_time +Error 1411 Incorrect datetime value: '2003-01-02 10:11:12.123456' for function str_to_time +Error 1411 Incorrect datetime value: '2003-01-02 10:11:12AM' for function str_to_time +Error 1411 Incorrect datetime value: '2003-01-02 10:11:12AN' for function str_to_time +Error 1411 Incorrect datetime value: '2003-01-02 10:11:12 PM' for function str_to_time +Error 1411 Incorrect datetime value: '10:20:10AM' for function str_to_time +Error 1411 Incorrect datetime value: '15 Septembei 2001' for function str_to_time +Error 1411 Incorrect datetime value: '15 Ju 2001' for function str_to_time +Error 1411 Incorrect datetime value: 'Sund 15 MA' for function str_to_time +Error 1411 Incorrect datetime value: 'Thursdai 12 1998' for function str_to_time +Error 1411 Incorrect datetime value: 'Sunday 01 2001' for function str_to_time +Error 1411 Incorrect datetime value: 'Tuesday 52 2001' for function str_to_time +Error 1411 Incorrect datetime value: 'Tuesday 52 2001' for function str_to_time +Error 1411 Incorrect datetime value: 'Tuesday 52 2001' for function str_to_time +Error 1411 Incorrect datetime value: '7 53 1998' for function str_to_time select date,format,concat(str_to_date(date, format),'') as con from t1; date format con 2003-01-02 10:11:12 PM %Y-%m-%d %H:%i:%S %p NULL @@ -353,6 +374,22 @@ Tuesday 52 2001 %W %V %Y NULL Tuesday 52 2001 %W %u %x NULL 7 53 1998 %w %u %Y NULL NULL %m.%d.%Y NULL +Warnings: +Error 1411 Incorrect datetime value: '2003-01-02 10:11:12 PM' for function str_to_time +Error 1411 Incorrect datetime value: '2003-01-02 10:11:12.123456' for function str_to_time +Error 1411 Incorrect datetime value: '2003-01-02 10:11:12AM' for function str_to_time +Error 1411 Incorrect datetime value: '2003-01-02 10:11:12AN' for function str_to_time +Error 1411 Incorrect datetime value: '2003-01-02 10:11:12 PM' for function str_to_time +Error 1411 Incorrect datetime value: '10:20:10AM' for function str_to_time +Error 1411 Incorrect datetime value: '15 Septembei 2001' for function str_to_time +Error 1411 Incorrect datetime value: '15 Ju 2001' for function str_to_time +Error 1411 Incorrect datetime value: 'Sund 15 MA' for function str_to_time +Error 1411 Incorrect datetime value: 'Thursdai 12 1998' for function str_to_time +Error 1411 Incorrect datetime value: 'Sunday 01 2001' for function str_to_time +Error 1411 Incorrect datetime value: 'Tuesday 52 2001' for function str_to_time +Error 1411 Incorrect datetime value: 'Tuesday 52 2001' for function str_to_time +Error 1411 Incorrect datetime value: 'Tuesday 52 2001' for function str_to_time +Error 1411 Incorrect datetime value: '7 53 1998' for function str_to_time truncate table t1; insert into t1 values ('10:20:10AM', '%h:%i:%s'), @@ -391,6 +428,8 @@ NULL select str_to_date('15-01-2001 12:59:59', GET_FORMAT(DATE,'USA')); str_to_date('15-01-2001 12:59:59', GET_FORMAT(DATE,'USA')) NULL +Warnings: +Error 1411 Incorrect datetime value: '15-01-2001 12:59:59' for function str_to_time explain extended select makedate(1997,1), addtime("31.12.97 11.59.59.999999 PM", "1 1.1.1.000002"),subtime("31.12.97 11.59.59.999999 PM", "1 1.1.1.000002"),timediff("01.01.97 11:59:59.000001 PM","31.12.95 11:59:59.000002 PM"),cast(str_to_date("15-01-2001 12:59:59", "%d-%m-%Y %H:%i:%S") as TIME), maketime(23,11,12),microsecond("1997-12-31 23:59:59.000001"); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL No tables used diff --git a/mysql-test/r/ps_1general.result b/mysql-test/r/ps_1general.result index ae873460374..06acb72f49b 100644 --- a/mysql-test/r/ps_1general.result +++ b/mysql-test/r/ps_1general.result @@ -342,7 +342,7 @@ EXAMPLE YES/NO Example storage engine ARCHIVE YES/NO Archive storage engine CSV YES/NO CSV storage engine FEDERATED YES/NO Federated MySQL storage engine -BLACKHOLE YES/NO Storage engine designed to act as null storage +BLACKHOLE YES/NO /dev/null storage engine (anything you write to it disappears) drop table if exists t5; prepare stmt1 from ' drop table if exists t5 ' ; execute stmt1 ; diff --git a/mysql-test/r/strict.result b/mysql-test/r/strict.result index c3d2533a2e3..47dbb87b990 100644 --- a/mysql-test/r/strict.result +++ b/mysql-test/r/strict.result @@ -1,3 +1,4 @@ +set @org_mode=@@sql_mode; set @@sql_mode='ansi,traditional'; select @@sql_mode; @@sql_mode @@ -209,8 +210,12 @@ INSERT INTO t1 (col1) VALUES(STR_TO_DATE('0.10.2004 15.30','%d.%m.%Y %H.%i')); ERROR 22007: Incorrect date value: '2004-10-00 15:30:00' for column 'col1' at row 1 INSERT INTO t1 (col1) VALUES(STR_TO_DATE('31.9.2004 15.30','%d.%m.%Y %H.%i')); ERROR 22007: Incorrect date value: '2004-09-31 15:30:00' for column 'col1' at row 1 +INSERT INTO t1 (col1) VALUES(STR_TO_DATE('32.10.2004 15.30','%d.%m.%Y %H.%i')); +ERROR HY000: Incorrect datetime value: '32.10.2004 15.30' for function str_to_time INSERT INTO t1 (col1) VALUES(STR_TO_DATE('29.02.2003 15.30','%d.%m.%Y %H.%i')); ERROR 22007: Incorrect date value: '2003-02-29 15:30:00' for column 'col1' at row 1 +INSERT INTO t1 (col1) VALUES(STR_TO_DATE('15.13.2004 15.30','%d.%m.%Y %H.%i')); +ERROR HY000: Incorrect datetime value: '15.13.2004 15.30' for function str_to_time INSERT INTO t1 (col1) VALUES(STR_TO_DATE('00.00.0000','%d.%m.%Y')); ERROR 22007: Incorrect date value: '0000-00-00' for column 'col1' at row 1 INSERT INTO t1 (col2) VALUES(STR_TO_DATE('31.10.0000 15.30','%d.%m.%Y %H.%i')); @@ -220,8 +225,14 @@ INSERT INTO t1 (col2) VALUES(STR_TO_DATE('0.10.2004 15.30','%d.%m.%Y %H.%i')); ERROR 22007: Incorrect datetime value: '2004-10-00 15:30:00' for column 'col2' at row 1 INSERT INTO t1 (col2) VALUES(STR_TO_DATE('31.9.2004 15.30','%d.%m.%Y %H.%i')); ERROR 22007: Incorrect datetime value: '2004-09-31 15:30:00' for column 'col2' at row 1 +INSERT INTO t1 (col2) VALUES(STR_TO_DATE('32.10.2004 15.30','%d.%m.%Y %H.%i')); +ERROR HY000: Incorrect datetime value: '32.10.2004 15.30' for function str_to_time INSERT INTO t1 (col2) VALUES(STR_TO_DATE('29.02.2003 15.30','%d.%m.%Y %H.%i')); ERROR 22007: Incorrect datetime value: '2003-02-29 15:30:00' for column 'col2' at row 1 +INSERT INTO t1 (col2) VALUES(STR_TO_DATE('15.13.2004 15.30','%d.%m.%Y %H.%i')); +ERROR HY000: Incorrect datetime value: '15.13.2004 15.30' for function str_to_time +INSERT INTO t1 (col2) VALUES(STR_TO_DATE('00.00.0000','%d.%m.%Y')); +ERROR 22007: Incorrect datetime value: '0000-00-00' for column 'col2' at row 1 INSERT INTO t1 (col3) VALUES(STR_TO_DATE('31.10.0000 15.30','%d.%m.%Y %H.%i')); ERROR 22007: Incorrect datetime value: '0000-10-31 15:30:00' for column 'col3' at row 1 INSERT INTO t1 (col3) VALUES(STR_TO_DATE('31.0.2004 15.30','%d.%m.%Y %H.%i')); @@ -230,8 +241,12 @@ INSERT INTO t1 (col3) VALUES(STR_TO_DATE('0.10.2004 15.30','%d.%m.%Y %H.%i')); ERROR 22007: Incorrect datetime value: '2004-10-00 15:30:00' for column 'col3' at row 1 INSERT INTO t1 (col3) VALUES(STR_TO_DATE('31.9.2004 15.30','%d.%m.%Y %H.%i')); ERROR 22007: Incorrect datetime value: '2004-09-31 15:30:00' for column 'col3' at row 1 +INSERT INTO t1 (col3) VALUES(STR_TO_DATE('32.10.2004 15.30','%d.%m.%Y %H.%i')); +ERROR HY000: Incorrect datetime value: '32.10.2004 15.30' for function str_to_time INSERT INTO t1 (col3) VALUES(STR_TO_DATE('29.02.2003 15.30','%d.%m.%Y %H.%i')); ERROR 22007: Incorrect datetime value: '2003-02-29 15:30:00' for column 'col3' at row 1 +INSERT INTO t1 (col3) VALUES(STR_TO_DATE('15.13.2004 15.30','%d.%m.%Y %H.%i')); +ERROR HY000: Incorrect datetime value: '15.13.2004 15.30' for function str_to_time INSERT INTO t1 (col3) VALUES(STR_TO_DATE('00.00.0000','%d.%m.%Y')); ERROR 22007: Incorrect datetime value: '0000-00-00' for column 'col3' at row 1 drop table t1; @@ -1030,3 +1045,48 @@ ERROR 22001: Data too long for column 'tinyblobcol' at row 1 select * from t1; charcol varcharcol binarycol varbinarycol tinytextcol tinyblobcol drop table t1; +set sql_mode='traditional'; +create table t1 (col1 datetime); +insert into t1 values(STR_TO_DATE('31.10.2004 15.30 abc','%d.%m.%Y %H.%i')); +ERROR 22007: Truncated incorrect datetime value: '31.10.2004 15.30 abc' +insert into t1 values(STR_TO_DATE('32.10.2004 15.30','%d.%m.%Y %H.%i')); +ERROR HY000: Incorrect datetime value: '32.10.2004 15.30' for function str_to_time +insert into t1 values(STR_TO_DATE('2004.12.12 22:22:33 AM','%Y.%m.%d %r')); +ERROR HY000: Incorrect time value: '22:22:33 AM' for function str_to_time +insert into t1 values(STR_TO_DATE('2004.12.12 abc','%Y.%m.%d %T')); +ERROR HY000: Incorrect time value: 'abc' for function str_to_time +set sql_mode=''; +insert into t1 values(STR_TO_DATE('31.10.2004 15.30 abc','%d.%m.%Y %H.%i')); +Warnings: +Warning 1292 Truncated incorrect datetime value: '31.10.2004 15.30 abc' +insert into t1 values(STR_TO_DATE('32.10.2004 15.30','%d.%m.%Y %H.%i')); +Warnings: +Error 1411 Incorrect datetime value: '32.10.2004 15.30' for function str_to_time +insert into t1 values(STR_TO_DATE('2004.12.12 22:22:33 AM','%Y.%m.%d %r')); +Warnings: +Error 1411 Incorrect time value: '22:22:33 AM' for function str_to_time +insert into t1 values(STR_TO_DATE('2004.12.12 abc','%Y.%m.%d %T')); +Warnings: +Error 1411 Incorrect time value: 'abc' for function str_to_time +insert into t1 values(STR_TO_DATE('31.10.2004 15.30','%d.%m.%Y %H.%i')); +insert into t1 values(STR_TO_DATE('2004.12.12 11:22:33 AM','%Y.%m.%d %r')); +insert into t1 values(STR_TO_DATE('2004.12.12 10:22:59','%Y.%m.%d %T')); +select * from t1; +col1 +2004-10-31 15:30:00 +NULL +NULL +NULL +2004-10-31 15:30:00 +2004-12-12 11:22:33 +2004-12-12 10:22:59 +set sql_mode='traditional'; +select count(*) from t1 where STR_TO_DATE('2004.12.12 10:22:61','%Y.%m.%d %T') IS NULL; +count(*) +7 +Warnings: +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 +Error 1411 Incorrect datetime value: '2004.12.12 10:22:61' for function str_to_time +drop table t1; +set sql_mode=@org_mode; diff --git a/mysql-test/r/type_decimal.result b/mysql-test/r/type_decimal.result index 2d5c2d2ac97..6a0c0090e79 100644 --- a/mysql-test/r/type_decimal.result +++ b/mysql-test/r/type_decimal.result @@ -693,3 +693,58 @@ SELECT EMPNUM FROM t1 WHERE HOURS IN (SELECT HOURS FROM t1); EMPNUM E1 DROP TABLE t1,t2; +create table t1 (d decimal(64,0)); +insert into t1 values (1); +select * from t1; +d +1 +drop table t1; +create table t1 (d decimal(64,99)); +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `d` decimal(64,30) default NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +insert into t1 values (1); +select * from t1; +d +1.000000000000000000000000000000 +drop table t1; +create table t1 (d decimal(10,12)); +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `d` decimal(13,12) default NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +create table t1 (d decimal(5)); +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `d` decimal(5,0) default NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +create table t1 (d decimal); +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `d` decimal(10,0) default NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +create table t1 (d decimal(65,0)); +ERROR 42000: Incorrect column specifier for column 'd' +CREATE TABLE t1 (i INT, d1 DECIMAL(9,2), d2 DECIMAL(9,2)); +INSERT INTO t1 VALUES (1, 101.40, 21.40), (1, -80.00, 0.00), +(2, 0.00, 0.00), (2, -13.20, 0.00), (2, 59.60, 46.40), +(2, 30.40, 30.40), (3, 37.00, 7.40), (3, -29.60, 0.00), +(4, 60.00, 15.40), (4, -10.60, 0.00), (4, -34.00, 0.00), +(5, 33.00, 0.00), (5, -25.80, 0.00), (5, 0.00, 7.20), +(6, 0.00, 0.00), (6, -51.40, 0.00); +SELECT i, SUM(d1) AS a, SUM(d2) AS b FROM t1 GROUP BY i HAVING a <> b; +i a b +6 -51.40 0.00 +SELECT i, ROUND(SUM(d1), 2) AS a, ROUND(SUM(d2), 2) AS b FROM t1 GROUP BY i +HAVING a <> b; +i a b +6 -51.40 0.00 +drop table t1; diff --git a/mysql-test/t/date_formats.test b/mysql-test/t/date_formats.test index 800e5880b09..e8bd8965b96 100644 --- a/mysql-test/t/date_formats.test +++ b/mysql-test/t/date_formats.test @@ -122,6 +122,7 @@ SET datetime_format=default; --disable_ps_protocol select str_to_date(concat('15-01-2001',' 2:59:58.999'), concat('%d-%m-%Y',' ','%H:%i:%s.%f')); +select STR_TO_DATE('2004.12.12 22.30.61','%Y.%m.%d %T'); --enable_ps_protocol create table t1 (date char(30), format char(30) not null); diff --git a/mysql-test/t/strict.test b/mysql-test/t/strict.test index 8af0d632f7c..96ba9993c49 100644 --- a/mysql-test/t/strict.test +++ b/mysql-test/t/strict.test @@ -2,6 +2,7 @@ -- source include/have_innodb.inc +set @org_mode=@@sql_mode; set @@sql_mode='ansi,traditional'; select @@sql_mode; @@ -197,20 +198,12 @@ INSERT INTO t1 (col1) VALUES(STR_TO_DATE('31.0.2004 15.30','%d.%m.%Y %H.%i')); INSERT INTO t1 (col1) VALUES(STR_TO_DATE('0.10.2004 15.30','%d.%m.%Y %H.%i')); --error 1292 INSERT INTO t1 (col1) VALUES(STR_TO_DATE('31.9.2004 15.30','%d.%m.%Y %H.%i')); - -# deactivated because of Bug#5902 -# Bug#5902: Traditional mode: STR_TO_DATE changes invalid value rather than rejecting -#--error 1292 -#INSERT INTO t1 (col1) VALUES(STR_TO_DATE('32.10.2004 15.30','%d.%m.%Y %H.%i')); - +--error 1411 +INSERT INTO t1 (col1) VALUES(STR_TO_DATE('32.10.2004 15.30','%d.%m.%Y %H.%i')); --error 1292 INSERT INTO t1 (col1) VALUES(STR_TO_DATE('29.02.2003 15.30','%d.%m.%Y %H.%i')); - -# deactivated because of Bug#5902 -# Bug#5902: Traditional mode: STR_TO_DATE changes invalid value rather than rejecting -#--error 1292 -#INSERT INTO t1 (col1) VALUES(STR_TO_DATE('15.13.2004 15.30','%d.%m.%Y %H.%i')); - +--error 1411 +INSERT INTO t1 (col1) VALUES(STR_TO_DATE('15.13.2004 15.30','%d.%m.%Y %H.%i')); --error 1292 INSERT INTO t1 (col1) VALUES(STR_TO_DATE('00.00.0000','%d.%m.%Y')); @@ -226,21 +219,14 @@ INSERT INTO t1 (col2) VALUES(STR_TO_DATE('31.0.2004 15.30','%d.%m.%Y %H.%i')); INSERT INTO t1 (col2) VALUES(STR_TO_DATE('0.10.2004 15.30','%d.%m.%Y %H.%i')); --error 1292 INSERT INTO t1 (col2) VALUES(STR_TO_DATE('31.9.2004 15.30','%d.%m.%Y %H.%i')); - -# deactivated because of Bug#5902 -# Bug#5902: Traditional mode: STR_TO_DATE changes invalid value rather than rejecting -#--error 1292 -#INSERT INTO t1 (col2) VALUES(STR_TO_DATE('32.10.2004 15.30','%d.%m.%Y %H.%i')); - +--error 1411 +INSERT INTO t1 (col2) VALUES(STR_TO_DATE('32.10.2004 15.30','%d.%m.%Y %H.%i')); --error 1292 INSERT INTO t1 (col2) VALUES(STR_TO_DATE('29.02.2003 15.30','%d.%m.%Y %H.%i')); - -# deactivated because of Bug#5902 -# Bug#5902: Traditional mode: STR_TO_DATE changes invalid value rather than rejecting -#--error 1292 -#INSERT INTO t1 (col2) VALUES(STR_TO_DATE('15.13.2004 15.30','%d.%m.%Y %H.%i')); -#--error 1292 -#INSERT INTO t1 (col2) VALUES(STR_TO_DATE('00.00.0000','%d.%m.%Y')); +--error 1411 +INSERT INTO t1 (col2) VALUES(STR_TO_DATE('15.13.2004 15.30','%d.%m.%Y %H.%i')); +--error 1292 +INSERT INTO t1 (col2) VALUES(STR_TO_DATE('00.00.0000','%d.%m.%Y')); ## Test INSERT with STR_TO_DATE into TIMESTAMP # All test cases expected to fail should return @@ -254,20 +240,12 @@ INSERT INTO t1 (col3) VALUES(STR_TO_DATE('31.0.2004 15.30','%d.%m.%Y %H.%i')); INSERT INTO t1 (col3) VALUES(STR_TO_DATE('0.10.2004 15.30','%d.%m.%Y %H.%i')); --error 1292 INSERT INTO t1 (col3) VALUES(STR_TO_DATE('31.9.2004 15.30','%d.%m.%Y %H.%i')); - -# deactivated because of Bug#5902 -# Bug#5902: Traditional mode: STR_TO_DATE changes invalid value rather than rejecting -#--error 1292 -#INSERT INTO t1 (col3) VALUES(STR_TO_DATE('32.10.2004 15.30','%d.%m.%Y %H.%i')); - +--error 1411 +INSERT INTO t1 (col3) VALUES(STR_TO_DATE('32.10.2004 15.30','%d.%m.%Y %H.%i')); --error 1292 INSERT INTO t1 (col3) VALUES(STR_TO_DATE('29.02.2003 15.30','%d.%m.%Y %H.%i')); - -# deactivated because of Bug#5902 -# Bug#5902: Traditional mode: STR_TO_DATE changes invalid value rather than rejecting -#--error 1292 -#INSERT INTO t1 (col3) VALUES(STR_TO_DATE('15.13.2004 15.30','%d.%m.%Y %H.%i')); - +--error 1411 +INSERT INTO t1 (col3) VALUES(STR_TO_DATE('15.13.2004 15.30','%d.%m.%Y %H.%i')); --error 1292 INSERT INTO t1 (col3) VALUES(STR_TO_DATE('00.00.0000','%d.%m.%Y')); @@ -962,3 +940,44 @@ insert into t1 (tinytextcol) values (repeat('x',256)); insert into t1 (tinyblobcol) values (repeat('x',256)); select * from t1; drop table t1; + +# +# Bug #5902: STR_TO_DATE() didn't give errors in traditional mode +# + +set sql_mode='traditional'; +create table t1 (col1 datetime); +--error 1292 +insert into t1 values(STR_TO_DATE('31.10.2004 15.30 abc','%d.%m.%Y %H.%i')); +--error 1411 +insert into t1 values(STR_TO_DATE('32.10.2004 15.30','%d.%m.%Y %H.%i')); +--error 1411 +insert into t1 values(STR_TO_DATE('2004.12.12 22:22:33 AM','%Y.%m.%d %r')); +--error 1411 +insert into t1 values(STR_TO_DATE('2004.12.12 abc','%Y.%m.%d %T')); +set sql_mode=''; +insert into t1 values(STR_TO_DATE('31.10.2004 15.30 abc','%d.%m.%Y %H.%i')); +insert into t1 values(STR_TO_DATE('32.10.2004 15.30','%d.%m.%Y %H.%i')); +insert into t1 values(STR_TO_DATE('2004.12.12 22:22:33 AM','%Y.%m.%d %r')); +insert into t1 values(STR_TO_DATE('2004.12.12 abc','%Y.%m.%d %T')); + +# Some correct values, just to test the functions +insert into t1 values(STR_TO_DATE('31.10.2004 15.30','%d.%m.%Y %H.%i')); +insert into t1 values(STR_TO_DATE('2004.12.12 11:22:33 AM','%Y.%m.%d %r')); +insert into t1 values(STR_TO_DATE('2004.12.12 10:22:59','%Y.%m.%d %T')); + +select * from t1; + +# Check that select don't abort even in strict mode (for now) +set sql_mode='traditional'; + +--disable_ps_warnings +select count(*) from t1 where STR_TO_DATE('2004.12.12 10:22:61','%Y.%m.%d %T') IS NULL; +--enable_ps_warnings + +drop table t1; + +# +# Restore mode +# +set sql_mode=@org_mode; diff --git a/mysql-test/t/type_decimal.test b/mysql-test/t/type_decimal.test index 18ac5d1e467..3205eabe01a 100644 --- a/mysql-test/t/type_decimal.test +++ b/mysql-test/t/type_decimal.test @@ -285,3 +285,44 @@ SELECT EMPNUM FROM t1 WHERE HOURS IN (SELECT HOURS FROM t2); SELECT EMPNUM FROM t1 WHERE HOURS IN (SELECT HOURS FROM t1); DROP TABLE t1,t2; + +# +# Test limits of decimal +# +create table t1 (d decimal(64,0)); +insert into t1 values (1); +select * from t1; +drop table t1; +create table t1 (d decimal(64,99)); +show create table t1; +insert into t1 values (1); +select * from t1; +drop table t1; +create table t1 (d decimal(10,12)); +show create table t1; +drop table t1; +create table t1 (d decimal(5)); +show create table t1; +drop table t1; +create table t1 (d decimal); +show create table t1; +drop table t1; +--error 1063 +create table t1 (d decimal(65,0)); + +# +# Test example from manual +# + +CREATE TABLE t1 (i INT, d1 DECIMAL(9,2), d2 DECIMAL(9,2)); +INSERT INTO t1 VALUES (1, 101.40, 21.40), (1, -80.00, 0.00), +(2, 0.00, 0.00), (2, -13.20, 0.00), (2, 59.60, 46.40), +(2, 30.40, 30.40), (3, 37.00, 7.40), (3, -29.60, 0.00), +(4, 60.00, 15.40), (4, -10.60, 0.00), (4, -34.00, 0.00), +(5, 33.00, 0.00), (5, -25.80, 0.00), (5, 0.00, 7.20), +(6, 0.00, 0.00), (6, -51.40, 0.00); + +SELECT i, SUM(d1) AS a, SUM(d2) AS b FROM t1 GROUP BY i HAVING a <> b; +SELECT i, ROUND(SUM(d1), 2) AS a, ROUND(SUM(d2), 2) AS b FROM t1 GROUP BY i +HAVING a <> b; +drop table t1; |