diff options
author | Alexander Barkov <bar@mariadb.com> | 2018-09-30 17:24:19 +0400 |
---|---|---|
committer | Alexander Barkov <bar@mariadb.com> | 2018-09-30 17:24:19 +0400 |
commit | f79bab3ae68393cb2f36fa83546e8ea02b7d0420 (patch) | |
tree | 5173271a32426e2e772e432b0be6900562b54bd0 /mysql-test/main/func_hybrid_type.result | |
parent | 23740441d847c92754695796d521958ec161872d (diff) | |
download | mariadb-git-f79bab3ae68393cb2f36fa83546e8ea02b7d0420.tar.gz |
MDEV-17318 CAST(LEAST(zero_date,non_zero_date) AS numeric_data_type) returns a wrong result
Also fixes:
MDEV-17330 Wrong result for 0 + LEAST(TIME'-10:00:00',TIME'10:00:00')
Problems:
1. These methods did not take into account the current session date flags
and passed date_mode_t(0) to func->get_date():
Type_handler_temporal_result::Item_func_min_max_val_real
Type_handler_temporal_result::Item_func_min_max_val_int
Type_handler_temporal_result::Item_func_min_max_val_decimal
Fixing to pass sql_mode_for_dates(thd) instead of date_mode_t(0).
Note, sql_mode_for_dates(thd) is only needed for DATE/DATETIME
data types. It is not needed for TIME.
So splitting value methods Type_handler_temporal_result::Item_func_min_max_xxx
into individual implementations for
Type_handler_{time|date|datetime|timestamp}_common
and, instead of calling get_date(), reusing inside classes
Time(), Date(), Datetime() and their methods to_longlong().
sql_mode_for_dates(thd) is automatically passed to get_date()
inside Date() and Datetime() constructors.
The switch to classes also fixed the problem reported in MDEV-17330.
Type_handler_temporal_result::Item_func_min_max_val_int() used to
call TIME_to_ulonglong(), which was not correct for TIME.
Changing the code to use Time().to_longlong() solved this.
2. Type_handler_temporal_result::Item_func_min_max_get_date
also did not take into account the current session
date flags in case of conversion from DATE/DATETIME to time
and passed date_mode_t(0) to get_date_native().
Fixing to pass sql_mode_for_dates(thd) in case of conversion
from DATE/DATETIME to TIME.
Diffstat (limited to 'mysql-test/main/func_hybrid_type.result')
-rw-r--r-- | mysql-test/main/func_hybrid_type.result | 70 |
1 files changed, 70 insertions, 0 deletions
diff --git a/mysql-test/main/func_hybrid_type.result b/mysql-test/main/func_hybrid_type.result index 2c1d03859f7..043f2fab3d2 100644 --- a/mysql-test/main/func_hybrid_type.result +++ b/mysql-test/main/func_hybrid_type.result @@ -3873,5 +3873,75 @@ t1 CREATE TABLE `t1` ( DROP TABLE t1; SET sql_mode=DEFAULT; # +# MDEV-17318 CAST(LEAST(zero_date,non_zero_date) AS numeric_data_type) returns a wrong result +# +SET sql_mode='NO_ZERO_DATE,NO_ZERO_IN_DATE'; +SELECT +LEAST('0000-00-00',DATE'2001-01-01') AS c0, +CAST(LEAST('0000-00-00',DATE'2001-01-01') AS CHAR) AS string, +CAST(LEAST('0000-00-00',DATE'2001-01-01') AS DATE) AS date, +CAST(LEAST('0000-00-00',DATE'2001-01-01') AS DATETIME) AS datetime, +CAST(LEAST('0000-00-00',DATE'2001-01-01') AS TIME) AS time, +CAST(LEAST('0000-00-00',DATE'2001-01-01') AS DECIMAL) AS dc, +CAST(LEAST('0000-00-00',DATE'2001-01-01') AS DOUBLE) AS dbl, +CAST(LEAST('0000-00-00',DATE'2001-01-01') AS SIGNED) AS sint, +CAST(LEAST('0000-00-00',DATE'2001-01-01') AS UNSIGNED) AS uint; +c0 string date datetime time dc dbl sint uint +NULL NULL NULL NULL NULL NULL NULL NULL NULL +Warnings: +Warning 1292 Incorrect datetime value: '0000-00-00' +Warning 1292 Incorrect datetime value: '0000-00-00' +Warning 1292 Incorrect datetime value: '0000-00-00' +Warning 1292 Incorrect datetime value: '0000-00-00' +Warning 1292 Incorrect datetime value: '0000-00-00' +Warning 1292 Incorrect datetime value: '0000-00-00' +Warning 1292 Incorrect datetime value: '0000-00-00' +Warning 1292 Incorrect datetime value: '0000-00-00' +Warning 1292 Incorrect datetime value: '0000-00-00' +CREATE TABLE t1 AS SELECT +LEAST('0000-00-00',DATE'2001-01-01') AS c0, +CAST(LEAST('0000-00-00',DATE'2001-01-01') AS CHAR) AS string, +CAST(LEAST('0000-00-00',DATE'2001-01-01') AS DATE) AS date, +CAST(LEAST('0000-00-00',DATE'2001-01-01') AS DATETIME) AS datetime, +CAST(LEAST('0000-00-00',DATE'2001-01-01') AS TIME) AS time, +CAST(LEAST('0000-00-00',DATE'2001-01-01') AS DECIMAL) AS dc, +CAST(LEAST('0000-00-00',DATE'2001-01-01') AS DOUBLE) AS dbl, +CAST(LEAST('0000-00-00',DATE'2001-01-01') AS SIGNED) AS sint, +CAST(LEAST('0000-00-00',DATE'2001-01-01') AS UNSIGNED) AS uint; +Warnings: +Warning 1292 Incorrect datetime value: '0000-00-00' +Warning 1292 Incorrect datetime value: '0000-00-00' +Warning 1292 Incorrect datetime value: '0000-00-00' +Warning 1292 Incorrect datetime value: '0000-00-00' +Warning 1292 Incorrect datetime value: '0000-00-00' +Warning 1292 Incorrect datetime value: '0000-00-00' +Warning 1292 Incorrect datetime value: '0000-00-00' +Warning 1292 Incorrect datetime value: '0000-00-00' +Warning 1292 Incorrect datetime value: '0000-00-00' +SELECT * FROM t1; +c0 string date datetime time dc dbl sint uint +NULL NULL NULL NULL NULL NULL NULL NULL NULL +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c0` date DEFAULT NULL, + `string` varchar(10) DEFAULT NULL, + `date` date DEFAULT NULL, + `datetime` datetime DEFAULT NULL, + `time` time DEFAULT NULL, + `dc` decimal(10,0) DEFAULT NULL, + `dbl` double DEFAULT NULL, + `sint` bigint(10) DEFAULT NULL, + `uint` bigint(20) unsigned DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1; +SET sql_mode=DEFAULT; +# +# MDEV-17330 Wrong result for 0 + LEAST(TIME'-10:00:00',TIME'10:00:00') +# +SELECT 0 + LEAST(TIME'-10:00:00',TIME'10:00:00') AS c; +c +-100000 +# # End of 10.4 tests # |