From 08b256f9df73fab94cc662d83b14131c59056385 Mon Sep 17 00:00:00 2001 From: unknown Date: Tue, 11 Dec 2007 10:12:05 +0100 Subject: Bug#31990: MINUTE() and SECOND() return bogus results when used on a DATE HOUR(), MINUTE(), ... returned spurious results when used on a DATE-cast. This happened because DATE-cast object did not overload get_time() method in superclass Item. The default method was inappropriate here and misinterpreted the data. Patch adds missing method; get_time() on DATE-casts now returns SQL-NULL on NULL input, 0 otherwise. This coincides with the way DATE-columns behave. Also fixes similar bug in Date-Field now. mysql-test/r/cast.result: Show that HOUR(), MINUTE(), ... return sensible values when used on DATE-cast objects, namely NULL for NULL-dates and 0 otherwise. Show that this coincides with how DATE-columns behave. mysql-test/r/type_date.result: Show that HOUR(), MINUTE(), ... return sensible values when used on DATE-fields. mysql-test/t/cast.test: Show that HOUR(), MINUTE(), ... return sensible values when used on DATE-cast objects, namely NULL for NULL-dates and 0 otherwise. Show that this coincides with how DATE-columns behave. mysql-test/t/type_date.test: Show that HOUR(), MINUTE(), ... return sensible values when used on DATE-fields. sql/field.cc: Add get_time() method to DATE-field object to overload the method in Field superclass that would return spurious results. Return zero-result. sql/field.h: Add get_time() declaration to date-field class sql/item_timefunc.cc: Add get_time() method to DATE-cast object to overload the method in Item superclass that would return spurious results. Return zero-result; flag NULL if input was NULL. sql/item_timefunc.h: Add get_time() declaration to DATE-cast object. --- mysql-test/r/cast.result | 24 ++++++++++++++++++++++++ mysql-test/r/type_date.result | 5 +++++ mysql-test/t/cast.test | 22 ++++++++++++++++++++++ mysql-test/t/type_date.test | 8 ++++++++ sql/field.cc | 7 +++++++ sql/field.h | 1 + sql/item_timefunc.cc | 7 +++++++ sql/item_timefunc.h | 1 + 8 files changed, 75 insertions(+) diff --git a/mysql-test/r/cast.result b/mysql-test/r/cast.result index 524ff48d69e..88601eceb0a 100644 --- a/mysql-test/r/cast.result +++ b/mysql-test/r/cast.result @@ -414,4 +414,28 @@ NULL NULL 20070719 drop table t1; +CREATE TABLE t1 (f1 DATE); +INSERT INTO t1 VALUES ('2007-07-19'), (NULL); +SELECT HOUR(f1), +MINUTE(f1), +SECOND(f1) FROM t1; +HOUR(f1) MINUTE(f1) SECOND(f1) +0 0 0 +NULL NULL NULL +SELECT HOUR(CAST('2007-07-19' AS DATE)), +MINUTE(CAST('2007-07-19' AS DATE)), +SECOND(CAST('2007-07-19' AS DATE)); +HOUR(CAST('2007-07-19' AS DATE)) MINUTE(CAST('2007-07-19' AS DATE)) SECOND(CAST('2007-07-19' AS DATE)) +0 0 0 +SELECT HOUR(CAST(NULL AS DATE)), +MINUTE(CAST(NULL AS DATE)), +SECOND(CAST(NULL AS DATE)); +HOUR(CAST(NULL AS DATE)) MINUTE(CAST(NULL AS DATE)) SECOND(CAST(NULL AS DATE)) +NULL NULL NULL +SELECT HOUR(NULL), +MINUTE(NULL), +SECOND(NULL); +HOUR(NULL) MINUTE(NULL) SECOND(NULL) +NULL NULL NULL +DROP TABLE t1; End of 5.0 tests diff --git a/mysql-test/r/type_date.result b/mysql-test/r/type_date.result index bd2a43569dd..f16b873f37b 100644 --- a/mysql-test/r/type_date.result +++ b/mysql-test/r/type_date.result @@ -214,4 +214,9 @@ INSERT INTO t1 VALUES ('0000-00-00'); ERROR 22007: Incorrect date value: '0000-00-00' for column 'a' at row 1 SET SQL_MODE=DEFAULT; DROP TABLE t1,t2; +CREATE TABLE t1 SELECT curdate() AS f1; +SELECT hour(f1), minute(f1), second(f1) FROM t1; +hour(f1) minute(f1) second(f1) +0 0 0 +DROP TABLE t1; End of 5.0 tests diff --git a/mysql-test/t/cast.test b/mysql-test/t/cast.test index 316b79efe4d..df475b49746 100644 --- a/mysql-test/t/cast.test +++ b/mysql-test/t/cast.test @@ -246,4 +246,26 @@ INSERT INTO t1(d1) VALUES ('2007-07-19 08:30:00'), (NULL), SELECT cast(date(d1) as signed) FROM t1; drop table t1; +# +# Bug #31990: MINUTE() and SECOND() return bogus results when used on a DATE +# + +# Show that HH:MM:SS of a DATE are 0, and that it's the same for columns +# and typecasts (NULL in, NULL out). +CREATE TABLE t1 (f1 DATE); +INSERT INTO t1 VALUES ('2007-07-19'), (NULL); +SELECT HOUR(f1), + MINUTE(f1), + SECOND(f1) FROM t1; +SELECT HOUR(CAST('2007-07-19' AS DATE)), + MINUTE(CAST('2007-07-19' AS DATE)), + SECOND(CAST('2007-07-19' AS DATE)); +SELECT HOUR(CAST(NULL AS DATE)), + MINUTE(CAST(NULL AS DATE)), + SECOND(CAST(NULL AS DATE)); +SELECT HOUR(NULL), + MINUTE(NULL), + SECOND(NULL); +DROP TABLE t1; + --echo End of 5.0 tests diff --git a/mysql-test/t/type_date.test b/mysql-test/t/type_date.test index 507537457d3..3f2ee4234bd 100644 --- a/mysql-test/t/type_date.test +++ b/mysql-test/t/type_date.test @@ -190,4 +190,12 @@ INSERT INTO t1 VALUES ('0000-00-00'); SET SQL_MODE=DEFAULT; DROP TABLE t1,t2; +# +# Bug #31990: MINUTE() and SECOND() return bogus results when used on a DATE +# + +CREATE TABLE t1 SELECT curdate() AS f1; +SELECT hour(f1), minute(f1), second(f1) FROM t1; +DROP TABLE t1; + --echo End of 5.0 tests diff --git a/sql/field.cc b/sql/field.cc index 86853389c64..db438c7c4d0 100644 --- a/sql/field.cc +++ b/sql/field.cc @@ -5194,6 +5194,13 @@ String *Field_date::val_str(String *val_buffer, } +bool Field_date::get_time(MYSQL_TIME *ltime) +{ + bzero((char *)ltime, sizeof(MYSQL_TIME)); + return 0; +} + + int Field_date::cmp(const char *a_ptr, const char *b_ptr) { int32 a,b; diff --git a/sql/field.h b/sql/field.h index 8c01931fa21..8ae39f78558 100644 --- a/sql/field.h +++ b/sql/field.h @@ -933,6 +933,7 @@ public: double val_real(void); longlong val_int(void); String *val_str(String*,String *); + bool get_time(MYSQL_TIME *ltime); bool send_binary(Protocol *protocol); int cmp(const char *,const char*); void sort_string(char *buff,uint length); diff --git a/sql/item_timefunc.cc b/sql/item_timefunc.cc index c1fa9dce038..7ed5e375f5b 100644 --- a/sql/item_timefunc.cc +++ b/sql/item_timefunc.cc @@ -2645,6 +2645,13 @@ bool Item_date_typecast::get_date(MYSQL_TIME *ltime, uint fuzzy_date) } +bool Item_date_typecast::get_time(MYSQL_TIME *ltime) +{ + bzero((char *)ltime, sizeof(MYSQL_TIME)); + return args[0]->null_value; +} + + String *Item_date_typecast::val_str(String *str) { DBUG_ASSERT(fixed == 1); diff --git a/sql/item_timefunc.h b/sql/item_timefunc.h index a5ecbc57e8d..b647e93b700 100644 --- a/sql/item_timefunc.h +++ b/sql/item_timefunc.h @@ -779,6 +779,7 @@ public: const char *func_name() const { return "cast_as_date"; } String *val_str(String *str); bool get_date(MYSQL_TIME *ltime, uint fuzzy_date); + bool get_time(MYSQL_TIME *ltime); const char *cast_type() const { return "date"; } enum_field_types field_type() const { return MYSQL_TYPE_DATE; } Field *tmp_table_field(TABLE *t_arg) -- cgit v1.2.1