Hi Tatjana!
I verified that the behavior of HOUR(), MINUTE(), and SECOND() is indeed
how a DATE column is treated, so the patch makes the behavior consistent
when using the CAST.
Patch is OK to push.
Just my few cents,
Mats Kindahl
Tatjana A Nuernberg wrote:
> Below is the list of changes that have just been committed into a local
> 5.0 repository of tnurnberg. When tnurnberg does a push these changes will
> be propagated to the main repository and, within 24 hours after the
> push, to the public repository.
> For information on how to access the public repository
> see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html
>
> ChangeSet@stripped, 2007-11-08 06:08:44+01:00, tnurnberg@stripped +4 -0
> 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.
>
> mysql-test/r/cast.result@stripped, 2007-11-08 06:08:42+01:00, tnurnberg@stripped +24
> -0
> 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/cast.test@stripped, 2007-11-08 06:08:42+01:00, tnurnberg@stripped +22 -0
> 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.
>
> sql/item_timefunc.cc@stripped, 2007-11-08 06:08:42+01:00, tnurnberg@stripped +7 -0
> 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@stripped, 2007-11-08 06:08:43+01:00, tnurnberg@stripped +1 -0
> Add get_time() declaration to DATE-cast object.
>
> diff -Nrup a/mysql-test/r/cast.result b/mysql-test/r/cast.result
> --- a/mysql-test/r/cast.result 2007-07-19 22:05:28 +02:00
> +++ b/mysql-test/r/cast.result 2007-11-08 06:08:42 +01:00
> @@ -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 -Nrup a/mysql-test/t/cast.test b/mysql-test/t/cast.test
> --- a/mysql-test/t/cast.test 2007-07-19 22:05:09 +02:00
> +++ b/mysql-test/t/cast.test 2007-11-08 06:08:42 +01:00
> @@ -246,4 +246,26 @@ INSERT INTO t1(d1) VALUES ('2007-07-19 0
> 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 -Nrup a/sql/item_timefunc.cc b/sql/item_timefunc.cc
> --- a/sql/item_timefunc.cc 2007-10-18 14:19:03 +02:00
> +++ b/sql/item_timefunc.cc 2007-11-08 06:08:42 +01:00
> @@ -2645,6 +2645,13 @@ bool Item_date_typecast::get_date(MYSQL_
> }
>
>
> +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 -Nrup a/sql/item_timefunc.h b/sql/item_timefunc.h
> --- a/sql/item_timefunc.h 2007-10-23 15:48:56 +02:00
> +++ b/sql/item_timefunc.h 2007-11-08 06:08:43 +01:00
> @@ -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)
>
>
--
Mats Kindahl
Lead Software Developer
Replication Team
MySQL AB, www.mysql.com
