#At file:///C:/Work/bzr/Chg-20/mysql-5.1-bugteam/ based on revid:sven.sandberg@stripped
3208 Kevin Lewis 2009-11-23
Bug#47631 - Math on DATETIME types is now consistently done as a longlong.
@ mysql-test/r/cast.result
Expanded and improved this test while checking for similarities to Bug#47631. All of these results were correct before the fix for 47631.
@ mysql-test/r/func_time.result
Bug#47631 - Math on DATETIME types is now consistently done as a longlong. This afected the results of tests that referred to now()-now().
Added an array of tests that were used while debugging 47631.
@ mysql-test/r/sysdate_is_now.result
Bug#47631 - Math on DATETIME types is now consistently done as a longlong.
@ mysql-test/t/cast.test
Expanded and improved this test while checking for similarities to Bug#47631. All of these results were correct before the fix for 47631.
@ mysql-test/t/func_time.test
Bug#47631 - Math on DATETIME types is now consistently done as a longlong. This afected the results of tests that referred to now()-now().
Added an array of tests that were used while debugging 47631.
@ sql/item.cc
Bug#47631 - Added Item::is_time(), similar to Item::is_datetime()
@ sql/item.h
Bug#47631 - Added Item::is_time(), similar to Item::is_datetime()
@ sql/item_func.cc
Bug#47631 - Math on date and time types is now consistently done as a longlong. This is accomplished by setting the Item_func_minus::hybrid_type to INT_RESULT in calls to fix_length_and_desc and by doing the correct conversion to this longlong by calling get_datetime_value() or get_time_value().
@ sql/item_func.h
Bug#47631 - Math on DATETIME types is now consistently done as a longlong.
@ sql/mysql_priv.h
Bug#47631 - Allow get_time_value() to be called from item_func.cc like get_datetime_value() is.
modified:
mysql-test/r/cast.result
mysql-test/r/func_time.result
mysql-test/r/sysdate_is_now.result
mysql-test/t/cast.test
mysql-test/t/func_time.test
sql/item.cc
sql/item.h
sql/item_func.cc
sql/item_func.h
sql/mysql_priv.h
=== modified file 'mysql-test/r/cast.result'
--- a/mysql-test/r/cast.result 2009-05-21 08:06:43 +0000
+++ b/mysql-test/r/cast.result 2009-11-23 21:17:29 +0000
@@ -351,12 +351,44 @@ DROP TABLE t1;
select isnull(date(NULL)), isnull(cast(NULL as DATE));
isnull(date(NULL)) isnull(cast(NULL as DATE))
1 1
-SELECT CAST(cast('01-01-01' as date) AS UNSIGNED);
-CAST(cast('01-01-01' as date) AS UNSIGNED)
+SELECT CAST('01-01-01 01:01:01' as DATETIME);
+CAST('01-01-01 01:01:01' as DATETIME)
+2001-01-01 01:01:01
+SELECT CAST('01-01-01' as DATE);
+CAST('01-01-01' as DATE)
+2001-01-01
+SELECT CAST('01:01:01' as TIME);
+CAST('01:01:01' as TIME)
+01:01:01
+SELECT CAST(CAST('01-01-01 01:01:01' as DATETIME) AS SIGNED);
+CAST(CAST('01-01-01 01:01:01' as DATETIME) AS SIGNED)
+20010101010101
+SELECT CAST(CAST('01-01-01' as DATE) AS SIGNED);
+CAST(CAST('01-01-01' as DATE) AS SIGNED)
20010101
-SELECT CAST(cast('01-01-01' as date) AS SIGNED);
-CAST(cast('01-01-01' as date) AS SIGNED)
+SELECT CAST(CAST('01:01:01' as TIME) AS SIGNED);
+CAST(CAST('01:01:01' as TIME) AS SIGNED)
+10101
+SELECT CAST(CAST('01-01-01 01:01:01' as DATETIME) AS UNSIGNED);
+CAST(CAST('01-01-01 01:01:01' as DATETIME) AS UNSIGNED)
+20010101010101
+SELECT CAST(CAST('01-01-01' as DATE) AS UNSIGNED);
+CAST(CAST('01-01-01' as DATE) AS UNSIGNED)
20010101
+SELECT CAST(CAST('01:01:01' as TIME) AS UNSIGNED);
+CAST(CAST('01:01:01' as TIME) AS UNSIGNED)
+10101
+SELECT CAST(CAST('01-01-01 01:01:01' as DATETIME) AS DECIMAL);
+CAST(CAST('01-01-01 01:01:01' as DATETIME) AS DECIMAL)
+9999999999
+Warnings:
+Error 1264 Out of range value for column 'CAST(CAST('01-01-01 01:01:01' as DATETIME) AS DECIMAL)' at row 1
+SELECT CAST(CAST('01-01-01' as DATE) AS DECIMAL);
+CAST(CAST('01-01-01' as DATE) AS DECIMAL)
+20010101
+SELECT CAST(CAST('01:01:01' as TIME) AS DECIMAL);
+CAST(CAST('01:01:01' as TIME) AS DECIMAL)
+10101
End of 4.1 tests
select cast('1.2' as decimal(3,2));
cast('1.2' as decimal(3,2))
=== modified file 'mysql-test/r/func_time.result'
--- a/mysql-test/r/func_time.result 2009-01-23 12:22:05 +0000
+++ b/mysql-test/r/func_time.result 2009-11-23 21:17:29 +0000
@@ -8,7 +8,7 @@ period_add("9602",-12) period_diff(19950
199502 13
select now()-now(),weekday(curdate())-weekday(now()),unix_timestamp()-unix_timestamp(now());
now()-now() weekday(curdate())-weekday(now()) unix_timestamp()-unix_timestamp(now())
-0.000000 0 0
+0 0 0
select from_unixtime(unix_timestamp("1994-03-02 10:11:12")),from_unixtime(unix_timestamp("1994-03-02 10:11:12"),"%Y-%m-%d %h:%i:%s"),from_unixtime(unix_timestamp("1994-03-02 10:11:12"))+0;
from_unixtime(unix_timestamp("1994-03-02 10:11:12")) from_unixtime(unix_timestamp("1994-03-02 10:11:12"),"%Y-%m-%d %h:%i:%s") from_unixtime(unix_timestamp("1994-03-02 10:11:12"))+0
1994-03-02 10:11:12 1994-03-02 10:11:12 19940302101112.000000
@@ -21,7 +21,7 @@ sec_to_time(time_to_sec('-838:59:59'))
-838:59:59
select now()-curdate()*1000000-curtime();
now()-curdate()*1000000-curtime()
-0.000000
+0
select strcmp(current_timestamp(),concat(current_date()," ",current_time()));
strcmp(current_timestamp(),concat(current_date()," ",current_time()))
0
@@ -941,8 +941,8 @@ sec_to_time(1) + 0, from_unixtime(1) + 0
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
- `now() - now()` double(23,6) NOT NULL DEFAULT '0.000000',
- `curtime() - curtime()` double(23,6) NOT NULL DEFAULT '0.000000',
+ `now() - now()` bigint(22) NOT NULL DEFAULT '0',
+ `curtime() - curtime()` int(11) NOT NULL DEFAULT '0',
`sec_to_time(1) + 0` double(23,6) DEFAULT NULL,
`from_unixtime(1) + 0` double(23,6) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
@@ -1335,4 +1335,79 @@ date_sub("0069-01-01 00:00:01",INTERVAL
select date_sub("0169-01-01 00:00:01",INTERVAL 2 SECOND);
date_sub("0169-01-01 00:00:01",INTERVAL 2 SECOND)
0168-12-31 23:59:59
+# Bug #47631
+CREATE TABLE t1 (a INTEGER, b TIMESTAMP) engine innodb;
+INSERT INTO t1 VALUES (1, '1993-12-1 12:00:00');
+INSERT INTO t1 VALUES (2, '1993-12-31 12:00:00');
+CREATE TABLE t2 (a INTEGER, b TIMESTAMP) engine innodb;
+INSERT INTO t2 VALUES (1, '1994-12-1 12:00:00');
+INSERT INTO t2 VALUES (2, '1994-12-31 12:00:00');
+CREATE TABLE t3( a INTEGER(4), b DATE, c DATE) engine innodb;
+INSERT INTO t3 (select 0 a, max(b), min(b) FROM t1);
+Warnings:
+Note 1265 Data truncated for column 'b' at row 2
+Note 1265 Data truncated for column 'c' at row 2
+INSERT INTO t3 (select 1 a, max(b), min(b) FROM t2);
+Warnings:
+Note 1265 Data truncated for column 'b' at row 2
+Note 1265 Data truncated for column 'c' at row 2
+SELECT * FROM t3;
+a b c
+0 1993-12-31 1993-12-01
+1 1994-12-31 1994-12-01
+SELECT a, MAX(b), MIN(c), (MAX(b) - MIN(c)) FROM t3 GROUP BY a;
+a MAX(b) MIN(c) (MAX(b) - MIN(c))
+0 1993-12-31 1993-12-01 30000000
+1 1994-12-31 1994-12-01 30000000
+CREATE index t3_i on t3 (a);
+SELECT a, MAX(b), MIN(c), (MAX(b) - MIN(c)) FROM t3 GROUP BY a;
+a MAX(b) MIN(c) (MAX(b) - MIN(c))
+0 1993-12-31 1993-12-01 30000000
+1 1994-12-31 1994-12-01 30000000
+DROP TABLE t1, t2, t3;
+CREATE TABLE t1 (a INTEGER(4), b DATE, c DATE);
+INSERT INTO t1 VALUES (0, '1994-12-31', '1994-12-1');
+SELECT *, MAX(b) - MIN(c), DATEDIFF(MAX(b), MIN(c)) FROM t1 GROUP BY a;
+a b c MAX(b) - MIN(c) DATEDIFF(MAX(b), MIN(c))
+0 1994-12-31 1994-12-01 30000000 30
+DROP TABLE t1;
+CREATE TABLE t1(a DATE, b DATE, c DATETIME, d DATETIME, e TIME, f TIME);
+INSERT INTO t1 VALUES ('2000-01-01', '2010-10-20', '2000-01-01 00:00:00',
+'2010-10-09 07:06:05', '10:10:10', '50:40:30');
+INSERT INTO t1 VALUES ('2005-01-01', '2015-09-08', '2005-05-05 05:15:15',
+'2015-09-08 07:06:05', '11:00:00', '51:00:00');
+INSERT INTO t1 VALUES ('2010-02-02', '2020-12-30', '2010-02-02 11:11:11',
+'2020-01-09 11:22:33', '22:22:22', '55:44:33');
+SELECT * FROM t1;
+a b c d e f
+2000-01-01 2010-10-20 2000-01-01 00:00:00 2010-10-09 07:06:05 10:10:10 50:40:30
+2005-01-01 2015-09-08 2005-05-05 05:15:15 2015-09-08 07:06:05 11:00:00 51:00:00
+2010-02-02 2020-12-30 2010-02-02 11:11:11 2020-01-09 11:22:33 22:22:22 55:44:33
+SELECT a, b, b - a, TIMESTAMPDIFF(day, a, b) DiffDay FROM t1;
+a b b - a DiffDay
+2000-01-01 2010-10-20 100919000000 3945
+2005-01-01 2015-09-08 100807000000 3902
+2010-02-02 2020-12-30 101028000000 3984
+SELECT c, d, d - c, TIMESTAMPDIFF(day, c, d) DiffDay FROM t1;
+c d d - c DiffDay
+2000-01-01 00:00:00 2010-10-09 07:06:05 100908070605 3934
+2005-05-05 05:15:15 2015-09-08 07:06:05 100403019090 3778
+2010-02-02 11:11:11 2020-01-09 11:22:33 99907001122 3628
+SELECT e, f, f - e, SUBTIME(f, e) FROM t1;
+e f f - e SUBTIME(f, e)
+10:10:10 50:40:30 403020 40:30:20
+11:00:00 51:00:00 400000 40:00:00
+22:22:22 55:44:33 332211 33:22:11
+SELECT MIN(a), MAX(b), MAX(b) - MIN(a),
+TIMESTAMPDIFF(day, MIN(a), MAX(b)) DiffDay FROM t1;
+MIN(a) MAX(b) MAX(b) - MIN(a) DiffDay
+2000-01-01 2020-12-30 201129000000 7669
+SELECT MIN(c), MAX(d), MAX(d) - MIN(c),
+TIMESTAMPDIFF(day, MIN(c), MAX(d)) DiffDay FROM t1;
+MIN(c) MAX(d) MAX(d) - MIN(c) DiffDay
+2000-01-01 00:00:00 2020-01-09 11:22:33 200008112233 7313
+SELECT MIN(e), MAX(f), MAX(f) - MIN(e), SUBTIME(MAX(f), MIN(e)) FROM t1;
+MIN(e) MAX(f) MAX(f) - MIN(e) SUBTIME(MAX(f), MIN(e))
+10:10:10 55:44:33 453423 45:34:23
+DROP TABLE t1;
End of 5.1 tests
=== modified file 'mysql-test/r/sysdate_is_now.result'
--- a/mysql-test/r/sysdate_is_now.result 2006-06-10 02:29:39 +0000
+++ b/mysql-test/r/sysdate_is_now.result 2009-11-23 21:17:29 +0000
@@ -1,4 +1,4 @@
set timestamp=1;
SELECT sleep(1),NOW()-SYSDATE() as zero;
sleep(1) zero
-0 0.000000
+0 0
=== modified file 'mysql-test/t/cast.test'
--- a/mysql-test/t/cast.test 2009-05-21 08:06:43 +0000
+++ b/mysql-test/t/cast.test 2009-11-23 21:17:29 +0000
@@ -191,8 +191,18 @@ select isnull(date(NULL)), isnull(cast(N
#
# Bug#23656: Wrong result of CAST from DATE to int
#
-SELECT CAST(cast('01-01-01' as date) AS UNSIGNED);
-SELECT CAST(cast('01-01-01' as date) AS SIGNED);
+SELECT CAST('01-01-01 01:01:01' as DATETIME);
+SELECT CAST('01-01-01' as DATE);
+SELECT CAST('01:01:01' as TIME);
+SELECT CAST(CAST('01-01-01 01:01:01' as DATETIME) AS SIGNED);
+SELECT CAST(CAST('01-01-01' as DATE) AS SIGNED);
+SELECT CAST(CAST('01:01:01' as TIME) AS SIGNED);
+SELECT CAST(CAST('01-01-01 01:01:01' as DATETIME) AS UNSIGNED);
+SELECT CAST(CAST('01-01-01' as DATE) AS UNSIGNED);
+SELECT CAST(CAST('01:01:01' as TIME) AS UNSIGNED);
+SELECT CAST(CAST('01-01-01 01:01:01' as DATETIME) AS DECIMAL);
+SELECT CAST(CAST('01-01-01' as DATE) AS DECIMAL);
+SELECT CAST(CAST('01:01:01' as TIME) AS DECIMAL);
--echo End of 4.1 tests
=== modified file 'mysql-test/t/func_time.test'
--- a/mysql-test/t/func_time.test 2009-01-23 12:22:05 +0000
+++ b/mysql-test/t/func_time.test 2009-11-23 21:17:29 +0000
@@ -1,6 +1,7 @@
#
# time functions
#
+--source include/have_innodb.inc
--disable_warnings
drop table if exists t1,t2,t3;
--enable_warnings
@@ -838,4 +839,59 @@ select date_sub("0069-01-01 00:00:01",IN
select date_sub("0169-01-01 00:00:01",INTERVAL 2 SECOND);
+#
+# Bug #47631: Inconsistent handling of date and time output
+# when subtracting MIN/MAX
+#
+--echo # Bug #47631
+# Original for bug#47631 which occured for InnoDB but not MyISAM
+CREATE TABLE t1 (a INTEGER, b TIMESTAMP) engine innodb;
+INSERT INTO t1 VALUES (1, '1993-12-1 12:00:00');
+INSERT INTO t1 VALUES (2, '1993-12-31 12:00:00');
+CREATE TABLE t2 (a INTEGER, b TIMESTAMP) engine innodb;
+INSERT INTO t2 VALUES (1, '1994-12-1 12:00:00');
+INSERT INTO t2 VALUES (2, '1994-12-31 12:00:00');
+CREATE TABLE t3( a INTEGER(4), b DATE, c DATE) engine innodb;
+INSERT INTO t3 (select 0 a, max(b), min(b) FROM t1);
+INSERT INTO t3 (select 1 a, max(b), min(b) FROM t2);
+SELECT * FROM t3;
+SELECT a, MAX(b), MIN(c), (MAX(b) - MIN(c)) FROM t3 GROUP BY a;
+CREATE index t3_i on t3 (a);
+SELECT a, MAX(b), MIN(c), (MAX(b) - MIN(c)) FROM t3 GROUP BY a;
+DROP TABLE t1, t2, t3;
+
+
+# Second testcase
+# Shows that DATEDIFF works as well and aught to be used instead.
+CREATE TABLE t1 (a INTEGER(4), b DATE, c DATE);
+INSERT INTO t1 VALUES (0, '1994-12-31', '1994-12-1');
+SELECT *, MAX(b) - MIN(c), DATEDIFF(MAX(b), MIN(c)) FROM t1 GROUP BY a;
+DROP TABLE t1;
+
+# Third Testcase
+# This Shows that TIMESTAMPDIFF and SUBTIME aught to be used instead.
+# This also shows how ridiculous it is to do math on the internal longlong
+# representation of timestamps. For example, see d - c for the second row;
+# 2015090807060 - 20050505051515 = 100403019090!!! 10-04-03 01:90:90 !!!
+# That number makes no sense since it contains 90 minutes and 90 seconds
+# AND it does not account for leap years AND it represents an interval of
+# time in inexact 'years' and 'months'.
+CREATE TABLE t1(a DATE, b DATE, c DATETIME, d DATETIME, e TIME, f TIME);
+INSERT INTO t1 VALUES ('2000-01-01', '2010-10-20', '2000-01-01 00:00:00',
+ '2010-10-09 07:06:05', '10:10:10', '50:40:30');
+INSERT INTO t1 VALUES ('2005-01-01', '2015-09-08', '2005-05-05 05:15:15',
+ '2015-09-08 07:06:05', '11:00:00', '51:00:00');
+INSERT INTO t1 VALUES ('2010-02-02', '2020-12-30', '2010-02-02 11:11:11',
+ '2020-01-09 11:22:33', '22:22:22', '55:44:33');
+SELECT * FROM t1;
+SELECT a, b, b - a, TIMESTAMPDIFF(day, a, b) DiffDay FROM t1;
+SELECT c, d, d - c, TIMESTAMPDIFF(day, c, d) DiffDay FROM t1;
+SELECT e, f, f - e, SUBTIME(f, e) FROM t1;
+SELECT MIN(a), MAX(b), MAX(b) - MIN(a),
+ TIMESTAMPDIFF(day, MIN(a), MAX(b)) DiffDay FROM t1;
+SELECT MIN(c), MAX(d), MAX(d) - MIN(c),
+ TIMESTAMPDIFF(day, MIN(c), MAX(d)) DiffDay FROM t1;
+SELECT MIN(e), MAX(f), MAX(f) - MIN(e), SUBTIME(MAX(f), MIN(e)) FROM t1;
+DROP TABLE t1;
+
--echo End of 5.1 tests
=== modified file 'sql/item.cc'
--- a/sql/item.cc 2009-11-06 19:42:24 +0000
+++ b/sql/item.cc 2009-11-23 21:17:29 +0000
@@ -4760,6 +4760,10 @@ bool Item::is_datetime()
return FALSE;
}
+bool Item::is_time()
+{
+ return (MYSQL_TYPE_TIME == field_type());
+}
String *Item::check_well_formed_result(String *str, bool send_error)
{
=== modified file 'sql/item.h'
--- a/sql/item.h 2009-11-06 19:42:24 +0000
+++ b/sql/item.h 2009-11-23 21:17:29 +0000
@@ -1012,6 +1012,7 @@ public:
*/
virtual bool result_as_longlong() { return FALSE; }
bool is_datetime();
+ bool is_time();
virtual Field::geometry_type get_geometry_type() const
{ return Field::GEOM_GEOMETRY; };
String *check_well_formed_result(String *str, bool send_error= 0);
=== modified file 'sql/item_func.cc'
--- a/sql/item_func.cc 2009-11-02 11:21:39 +0000
+++ b/sql/item_func.cc 2009-11-23 21:17:29 +0000
@@ -1193,12 +1193,41 @@ void Item_func_additive_op::result_preci
void Item_func_minus::fix_length_and_dec()
{
- Item_num_op::fix_length_and_dec();
+ arg_is_date[0]= args[0]->is_datetime();
+ arg_is_date[1]= args[1]->is_datetime();
+ arg_is_time[0]= args[0]->is_time();
+ arg_is_time[1]= args[1]->is_time();
+ if (arg_is_date[0] || arg_is_date[1] || arg_is_time[0] || arg_is_time[1])
+ {
+ decimals= 0;
+ hybrid_type=INT_RESULT;
+ result_precision();
+ }
+ else
+ Item_num_op::fix_length_and_dec();
+
if (unsigned_flag &&
(current_thd->variables.sql_mode & MODE_NO_UNSIGNED_SUBTRACTION))
unsigned_flag=0;
}
+longlong Item_func_minus::get_datetime_arg(uint arg_num)
+{
+ bool is_null;
+ Item **tmp_item;
+ if (arg_is_date[arg_num])
+ {
+ tmp_item= &args[arg_num];
+ return get_datetime_value(current_thd, &tmp_item, NULL, args[arg_num], &is_null);
+ }
+ if (arg_is_time[arg_num])
+ {
+ tmp_item= &args[arg_num];
+ return get_time_value(current_thd, &tmp_item, NULL, args[arg_num], &is_null);
+ }
+
+ return args[arg_num]->val_int();
+}
double Item_func_minus::real_op()
{
@@ -1211,7 +1240,7 @@ double Item_func_minus::real_op()
longlong Item_func_minus::int_op()
{
- longlong value=args[0]->val_int() - args[1]->val_int();
+ longlong value= get_datetime_arg(0) - get_datetime_arg(1);
if ((null_value=args[0]->null_value || args[1]->null_value))
return 0;
return value;
=== modified file 'sql/item_func.h'
--- a/sql/item_func.h 2009-11-02 11:21:39 +0000
+++ b/sql/item_func.h 2009-11-23 21:17:29 +0000
@@ -414,6 +414,10 @@ public:
class Item_func_minus :public Item_func_additive_op
{
+ bool arg_is_date[2];
+ bool arg_is_time[2];
+ longlong get_datetime_arg(uint arg_num);
+
public:
Item_func_minus(Item *a,Item *b) :Item_func_additive_op(a,b) {}
const char *func_name() const { return "-"; }
=== modified file 'sql/mysql_priv.h'
--- a/sql/mysql_priv.h 2009-10-27 13:20:34 +0000
+++ b/sql/mysql_priv.h 2009-11-23 21:17:29 +0000
@@ -2224,6 +2224,8 @@ void make_time(const DATE_TIME_FORMAT *f
int my_time_compare(MYSQL_TIME *a, MYSQL_TIME *b);
longlong get_datetime_value(THD *thd, Item ***item_arg, Item **cache_arg,
Item *warn_item, bool *is_null);
+longlong get_time_value(THD *thd, Item ***item_arg, Item **cache_arg,
+ Item *warn_item, bool *is_null);
int test_if_number(char *str,int *res,bool allow_wildcards);
void change_byte(uchar *,uint,char,char);
Attachment: [text/bzr-bundle] bzr/kevin.lewis@sun.com-20091123211729-bua7lelapzjo5qsg.bundle
