MDEV-15310 Range optimizer does not work well for "WHERE temporal_column NOT IN (const_list)"

There were two problems related to the bug report:
1. Item_datetime::get_date() was not implemented.
   So execution went through val_int() followed
   by int-to-datetime or int-to-time conversion.
   This was the reason why the optimizer did not
   work well on data with fractional seconds.
2. Item_datetime::set() did not have a TIME specific code
   to mix months and days to hours after unpack_time().
   This is why the optimizer did not work well with negative
   TIME values, as well as huge time values.

Changes:

1. Overriding Item_datetime::get_date(), to return ltime.
   This fixes the problem N1.
2. Cleanup: Moving pack_time() and unpack_time() from
   sql-common/my_time.c and include/my_time.h to
   sql/sql_time.cc and sql/sql_time.h, as they are not needed
   on the client side.
3. Adding a new "enum_mysql_timestamp_type ts_type" parameter
   to unpack_time() and moving the TIME specific code to mix
   months and days with hours inside unpack_time().
   Adding a new "ts_type" parameter to Item_datetime::set(),
   to pass it from the caller down to unpack_time().
   So now the TIME specific code is automatically called
   from Item_datetime::set(). This fixes the problem N2.
   This change also helped to get rid of duplicate TIME specific code
   from other three places, where mixing month/days to hours
   was done immediately after unpack_time().
   Moving the DATE specific code to zero hhmmssff
   from Item_func_min_max::get_date_native to inside unpack_time(),
   for symmetry.
4. Removing the virtual method in_vector::result_type(),
   adding in_vector::type_handler() instead.
   This helps to get result_type(), field_type(),
   mysql_timestamp_type() of an in_vector easier.
   Passing type_handler()->mysql_timestamp_type() as
   a new parameter to Item_datetime::set() inside
   in_temporal::value_to_item().
5. Cleaup: Removing separate implementations of in_datetime::get_value()
   and in_time::get_value(). Adding a single implementation
   in_temporal::get_value() instead.
   Passing type_handler()->field_type() to get_value_internal().

15 files changed, 994 insertions, 81 deletions

diff --git a/include/my_time.h b/include/my_time.h
index 02e8f32ee23..b17bb5fe2b7 100644
--- a/include/my_time.h
+++ b/include/my_time.h
@@ -135,9 +135,6 @@ ulonglong TIME_to_ulonglong_time(const MYSQL_TIME *);
 ulonglong TIME_to_ulonglong(const MYSQL_TIME *);
 double TIME_to_double(const MYSQL_TIME *my_time);
 
-longlong pack_time(const MYSQL_TIME *my_time);
-MYSQL_TIME *unpack_time(longlong packed, MYSQL_TIME *my_time);
-
 int check_time_range(struct st_mysql_time *my_time, uint dec, int *warning);
 my_bool check_datetime_range(const MYSQL_TIME *ltime);
 
diff --git a/mysql-test/r/type_datetime.result b/mysql-test/r/type_datetime.result
index 95e421821c1..74b761a2e8f 100644
--- a/mysql-test/r/type_datetime.result
+++ b/mysql-test/r/type_datetime.result
@@ -1218,5 +1218,86 @@ a
 DROP TABLE t1;
 SET timestamp=DEFAULT;
 #
+# MDEV-15310 Range optimizer does not work well for "WHERE temporal_column NOT IN (const_list)"
+#
+#
+# DATETIME(0)
+#
+CREATE TABLE t1 (a DATETIME, filler CHAR(200), KEY(a));
+INSERT INTO t1 VALUES ('2001-01-01 23:00:01', 'no');
+INSERT INTO t1 VALUES ('2001-01-01 23:00:01', 'no');
+INSERT INTO t1 VALUES ('2001-01-01 23:00:01', 'no');
+INSERT INTO t1 VALUES ('2001-01-01 23:00:01', 'no');
+INSERT INTO t1 VALUES ('2001-01-01 23:00:01', 'no');
+INSERT INTO t1 VALUES ('2001-01-01 23:00:01', 'no');
+INSERT INTO t1 VALUES ('2001-01-01 23:00:01', 'no');
+INSERT INTO t1 VALUES ('2001-01-01 23:00:01', 'no');
+INSERT INTO t1 VALUES ('2001-01-01 23:00:01', 'no');
+INSERT INTO t1 VALUES ('2001-01-01 23:00:01', 'no');
+INSERT INTO t1 VALUES ('2001-01-01 23:00:01', 'no');
+INSERT INTO t1 VALUES ('2001-01-01 23:00:01', 'no');
+INSERT INTO t1 VALUES ('2001-01-01 23:00:01', 'no');
+INSERT INTO t1 VALUES ('2001-01-01 23:00:01', 'no');
+INSERT INTO t1 VALUES ('2001-01-01 23:00:01', 'no');
+INSERT INTO t1 VALUES ('2001-01-01 23:00:01', 'no');
+INSERT INTO t1 VALUES ('2001-01-01 23:00:01', 'no');
+INSERT INTO t1 VALUES ('2001-01-01 23:00:01', 'no');
+INSERT INTO t1 VALUES ('2001-01-01 23:00:01', 'no');
+INSERT INTO t1 VALUES ('2001-01-01 23:00:01', 'no');
+INSERT INTO t1 VALUES ('2001-01-01 23:00:01', 'no');
+INSERT INTO t1 VALUES ('2001-01-01 23:00:01', 'no');
+INSERT INTO t1 VALUES ('2001-01-01 23:00:01', 'no');
+INSERT INTO t1 VALUES ('2001-01-01 23:00:01', 'no');
+INSERT INTO t1 VALUES ('2001-01-01 23:00:02', 'no');
+INSERT INTO t1 VALUES ('2001-01-01 23:00:03', 'yes');
+INSERT INTO t1 VALUES ('2001-01-01 23:00:04', 'yes');
+EXPLAIN SELECT * FROM t1 WHERE a NOT IN ('2001-01-01 23:00:01','2001-01-01 23:00:02');
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	a	a	6	NULL	5	Using index condition
+SELECT * FROM t1 WHERE a NOT IN ('2001-01-01 23:00:01','2001-01-01 23:00:02');
+a	filler
+2001-01-01 23:00:03	yes
+2001-01-01 23:00:04	yes
+DROP TABLE t1;
+#
+# DATETIME(1)
+#
+CREATE TABLE t1 (a DATETIME(1), filler CHAR(200), KEY(a));
+INSERT INTO t1 VALUES ('2001-01-01 23:00:01.1', 'no');
+INSERT INTO t1 VALUES ('2001-01-01 23:00:01.1', 'no');
+INSERT INTO t1 VALUES ('2001-01-01 23:00:01.1', 'no');
+INSERT INTO t1 VALUES ('2001-01-01 23:00:01.1', 'no');
+INSERT INTO t1 VALUES ('2001-01-01 23:00:01.1', 'no');
+INSERT INTO t1 VALUES ('2001-01-01 23:00:01.1', 'no');
+INSERT INTO t1 VALUES ('2001-01-01 23:00:01.1', 'no');
+INSERT INTO t1 VALUES ('2001-01-01 23:00:01.1', 'no');
+INSERT INTO t1 VALUES ('2001-01-01 23:00:01.1', 'no');
+INSERT INTO t1 VALUES ('2001-01-01 23:00:01.1', 'no');
+INSERT INTO t1 VALUES ('2001-01-01 23:00:01.1', 'no');
+INSERT INTO t1 VALUES ('2001-01-01 23:00:01.1', 'no');
+INSERT INTO t1 VALUES ('2001-01-01 23:00:01.1', 'no');
+INSERT INTO t1 VALUES ('2001-01-01 23:00:01.1', 'no');
+INSERT INTO t1 VALUES ('2001-01-01 23:00:01.1', 'no');
+INSERT INTO t1 VALUES ('2001-01-01 23:00:01.1', 'no');
+INSERT INTO t1 VALUES ('2001-01-01 23:00:01.1', 'no');
+INSERT INTO t1 VALUES ('2001-01-01 23:00:01.1', 'no');
+INSERT INTO t1 VALUES ('2001-01-01 23:00:01.1', 'no');
+INSERT INTO t1 VALUES ('2001-01-01 23:00:01.1', 'no');
+INSERT INTO t1 VALUES ('2001-01-01 23:00:01.1', 'no');
+INSERT INTO t1 VALUES ('2001-01-01 23:00:01.1', 'no');
+INSERT INTO t1 VALUES ('2001-01-01 23:00:01.1', 'no');
+INSERT INTO t1 VALUES ('2001-01-01 23:00:01.1', 'no');
+INSERT INTO t1 VALUES ('2001-01-01 23:00:02.1', 'no');
+INSERT INTO t1 VALUES ('2001-01-01 23:00:03.1', 'yes');
+INSERT INTO t1 VALUES ('2001-01-01 23:00:04.1', 'yes');
+EXPLAIN SELECT * FROM t1 WHERE a NOT IN ('2001-01-01 23:00:01.1','2001-01-01 23:00:02.1');
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	a	a	7	NULL	5	Using index condition
+SELECT * FROM t1 WHERE a NOT IN ('2001-01-01 23:00:01.1','2001-01-01 23:00:02.1');
+a	filler
+2001-01-01 23:00:03.1	yes
+2001-01-01 23:00:04.1	yes
+DROP TABLE t1;
+#
 # End of 10.3 tests
 #
diff --git a/mysql-test/r/type_time.result b/mysql-test/r/type_time.result
index c08575776e9..3f34ae6c09b 100644
--- a/mysql-test/r/type_time.result
+++ b/mysql-test/r/type_time.result
@@ -1529,3 +1529,396 @@ ORDER BY country, dt;
 dt	country	amount	first	t_first	last	t_last
 2000-01-01	DE	102	2000-01-01	00:00:00	2000-01-01	00:00:00
 DROP TABLE t1;
+#
+#  MDEV-15310 Range optimizer does not work well for "WHERE temporal_column NOT IN (const_list)"
+#
+#
+# TIME(0), positive within 24 hour
+#
+CREATE TABLE t1 (a TIME, filler CHAR(200), KEY(a));
+INSERT INTO t1 VALUES ('23:00:01', 'no');
+INSERT INTO t1 VALUES ('23:00:01', 'no');
+INSERT INTO t1 VALUES ('23:00:01', 'no');
+INSERT INTO t1 VALUES ('23:00:01', 'no');
+INSERT INTO t1 VALUES ('23:00:01', 'no');
+INSERT INTO t1 VALUES ('23:00:01', 'no');
+INSERT INTO t1 VALUES ('23:00:01', 'no');
+INSERT INTO t1 VALUES ('23:00:01', 'no');
+INSERT INTO t1 VALUES ('23:00:01', 'no');
+INSERT INTO t1 VALUES ('23:00:01', 'no');
+INSERT INTO t1 VALUES ('23:00:01', 'no');
+INSERT INTO t1 VALUES ('23:00:01', 'no');
+INSERT INTO t1 VALUES ('23:00:01', 'no');
+INSERT INTO t1 VALUES ('23:00:01', 'no');
+INSERT INTO t1 VALUES ('23:00:01', 'no');
+INSERT INTO t1 VALUES ('23:00:01', 'no');
+INSERT INTO t1 VALUES ('23:00:01', 'no');
+INSERT INTO t1 VALUES ('23:00:01', 'no');
+INSERT INTO t1 VALUES ('23:00:01', 'no');
+INSERT INTO t1 VALUES ('23:00:01', 'no');
+INSERT INTO t1 VALUES ('23:00:01', 'no');
+INSERT INTO t1 VALUES ('23:00:01', 'no');
+INSERT INTO t1 VALUES ('23:00:01', 'no');
+INSERT INTO t1 VALUES ('23:00:01', 'no');
+INSERT INTO t1 VALUES ('23:00:02', 'no');
+INSERT INTO t1 VALUES ('23:00:03', 'yes');
+INSERT INTO t1 VALUES ('23:00:04', 'yes');
+EXPLAIN SELECT * FROM t1 WHERE a NOT IN ('23:00:01','23:00:02');
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	a	a	4	NULL	5	Using index condition
+SELECT * FROM t1 WHERE a NOT IN ('23:00:01','23:00:02');
+a	filler
+23:00:03	yes
+23:00:04	yes
+DROP TABLE t1;
+#
+# TIME(0), negative
+#
+CREATE TABLE t1 (a TIME, filler CHAR(200), KEY(a));
+INSERT INTO t1 VALUES ('-23:00:01', 'no');
+INSERT INTO t1 VALUES ('-23:00:01', 'no');
+INSERT INTO t1 VALUES ('-23:00:01', 'no');
+INSERT INTO t1 VALUES ('-23:00:01', 'no');
+INSERT INTO t1 VALUES ('-23:00:01', 'no');
+INSERT INTO t1 VALUES ('-23:00:01', 'no');
+INSERT INTO t1 VALUES ('-23:00:01', 'no');
+INSERT INTO t1 VALUES ('-23:00:01', 'no');
+INSERT INTO t1 VALUES ('-23:00:01', 'no');
+INSERT INTO t1 VALUES ('-23:00:01', 'no');
+INSERT INTO t1 VALUES ('-23:00:01', 'no');
+INSERT INTO t1 VALUES ('-23:00:01', 'no');
+INSERT INTO t1 VALUES ('-23:00:01', 'no');
+INSERT INTO t1 VALUES ('-23:00:01', 'no');
+INSERT INTO t1 VALUES ('-23:00:01', 'no');
+INSERT INTO t1 VALUES ('-23:00:01', 'no');
+INSERT INTO t1 VALUES ('-23:00:01', 'no');
+INSERT INTO t1 VALUES ('-23:00:01', 'no');
+INSERT INTO t1 VALUES ('-23:00:01', 'no');
+INSERT INTO t1 VALUES ('-23:00:01', 'no');
+INSERT INTO t1 VALUES ('-23:00:01', 'no');
+INSERT INTO t1 VALUES ('-23:00:01', 'no');
+INSERT INTO t1 VALUES ('-23:00:01', 'no');
+INSERT INTO t1 VALUES ('-23:00:01', 'no');
+INSERT INTO t1 VALUES ('-23:00:02', 'no');
+INSERT INTO t1 VALUES ('-23:00:03', 'yes');
+INSERT INTO t1 VALUES ('-23:00:04', 'yes');
+EXPLAIN SELECT * FROM t1 WHERE a NOT IN ('-23:00:01','-23:00:02');
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	a	a	4	NULL	4	Using index condition
+SELECT * FROM t1 WHERE a NOT IN ('-23:00:01','-23:00:02');
+a	filler
+-23:00:04	yes
+-23:00:03	yes
+DROP TABLE t1;
+#
+# TIME(0), positive ouside 24 hours
+#
+CREATE TABLE t1 (a TIME, filler CHAR(200), KEY(a));
+INSERT INTO t1 VALUES ('24:00:01', 'no');
+INSERT INTO t1 VALUES ('24:00:01', 'no');
+INSERT INTO t1 VALUES ('24:00:01', 'no');
+INSERT INTO t1 VALUES ('24:00:01', 'no');
+INSERT INTO t1 VALUES ('24:00:01', 'no');
+INSERT INTO t1 VALUES ('24:00:01', 'no');
+INSERT INTO t1 VALUES ('24:00:01', 'no');
+INSERT INTO t1 VALUES ('24:00:01', 'no');
+INSERT INTO t1 VALUES ('24:00:01', 'no');
+INSERT INTO t1 VALUES ('24:00:01', 'no');
+INSERT INTO t1 VALUES ('24:00:01', 'no');
+INSERT INTO t1 VALUES ('24:00:01', 'no');
+INSERT INTO t1 VALUES ('24:00:01', 'no');
+INSERT INTO t1 VALUES ('24:00:01', 'no');
+INSERT INTO t1 VALUES ('24:00:01', 'no');
+INSERT INTO t1 VALUES ('24:00:01', 'no');
+INSERT INTO t1 VALUES ('24:00:01', 'no');
+INSERT INTO t1 VALUES ('24:00:01', 'no');
+INSERT INTO t1 VALUES ('24:00:01', 'no');
+INSERT INTO t1 VALUES ('24:00:01', 'no');
+INSERT INTO t1 VALUES ('24:00:01', 'no');
+INSERT INTO t1 VALUES ('24:00:01', 'no');
+INSERT INTO t1 VALUES ('24:00:01', 'no');
+INSERT INTO t1 VALUES ('24:00:01', 'no');
+INSERT INTO t1 VALUES ('24:00:02', 'no');
+INSERT INTO t1 VALUES ('24:00:03', 'yes');
+INSERT INTO t1 VALUES ('24:00:04', 'yes');
+EXPLAIN SELECT * FROM t1 WHERE a NOT IN ('24:00:01','24:00:02');
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	a	a	4	NULL	5	Using index condition
+SELECT * FROM t1 WHERE a NOT IN ('24:00:01','24:00:02');
+a	filler
+24:00:03	yes
+24:00:04	yes
+DROP TABLE t1;
+#
+# TIME(0), negative, ouside 24 hours
+#
+CREATE TABLE t1 (a TIME, filler CHAR(200), KEY(a));
+INSERT INTO t1 VALUES ('-24:00:01', 'no');
+INSERT INTO t1 VALUES ('-24:00:01', 'no');
+INSERT INTO t1 VALUES ('-24:00:01', 'no');
+INSERT INTO t1 VALUES ('-24:00:01', 'no');
+INSERT INTO t1 VALUES ('-24:00:01', 'no');
+INSERT INTO t1 VALUES ('-24:00:01', 'no');
+INSERT INTO t1 VALUES ('-24:00:01', 'no');
+INSERT INTO t1 VALUES ('-24:00:01', 'no');
+INSERT INTO t1 VALUES ('-24:00:01', 'no');
+INSERT INTO t1 VALUES ('-24:00:01', 'no');
+INSERT INTO t1 VALUES ('-24:00:01', 'no');
+INSERT INTO t1 VALUES ('-24:00:01', 'no');
+INSERT INTO t1 VALUES ('-24:00:01', 'no');
+INSERT INTO t1 VALUES ('-24:00:01', 'no');
+INSERT INTO t1 VALUES ('-24:00:01', 'no');
+INSERT INTO t1 VALUES ('-24:00:01', 'no');
+INSERT INTO t1 VALUES ('-24:00:01', 'no');
+INSERT INTO t1 VALUES ('-24:00:01', 'no');
+INSERT INTO t1 VALUES ('-24:00:01', 'no');
+INSERT INTO t1 VALUES ('-24:00:01', 'no');
+INSERT INTO t1 VALUES ('-24:00:01', 'no');
+INSERT INTO t1 VALUES ('-24:00:01', 'no');
+INSERT INTO t1 VALUES ('-24:00:01', 'no');
+INSERT INTO t1 VALUES ('-24:00:01', 'no');
+INSERT INTO t1 VALUES ('-24:00:02', 'no');
+INSERT INTO t1 VALUES ('-24:00:03', 'yes');
+INSERT INTO t1 VALUES ('-24:00:04', 'yes');
+EXPLAIN SELECT * FROM t1 WHERE a NOT IN ('-24:00:01','-24:00:02');
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	a	a	4	NULL	4	Using index condition
+SELECT * FROM t1 WHERE a NOT IN ('-24:00:01','-24:00:02');
+a	filler
+-24:00:04	yes
+-24:00:03	yes
+DROP TABLE t1;
+#
+# TIME(0), positive, huge
+#
+CREATE TABLE t1 (a TIME, filler CHAR(200), KEY(a));
+INSERT INTO t1 VALUES ('838:00:01', 'no');
+INSERT INTO t1 VALUES ('838:00:01', 'no');
+INSERT INTO t1 VALUES ('838:00:01', 'no');
+INSERT INTO t1 VALUES ('838:00:01', 'no');
+INSERT INTO t1 VALUES ('838:00:01', 'no');
+INSERT INTO t1 VALUES ('838:00:01', 'no');
+INSERT INTO t1 VALUES ('838:00:01', 'no');
+INSERT INTO t1 VALUES ('838:00:01', 'no');
+INSERT INTO t1 VALUES ('838:00:01', 'no');
+INSERT INTO t1 VALUES ('838:00:01', 'no');
+INSERT INTO t1 VALUES ('838:00:01', 'no');
+INSERT INTO t1 VALUES ('838:00:01', 'no');
+INSERT INTO t1 VALUES ('838:00:01', 'no');
+INSERT INTO t1 VALUES ('838:00:01', 'no');
+INSERT INTO t1 VALUES ('838:00:01', 'no');
+INSERT INTO t1 VALUES ('838:00:01', 'no');
+INSERT INTO t1 VALUES ('838:00:01', 'no');
+INSERT INTO t1 VALUES ('838:00:01', 'no');
+INSERT INTO t1 VALUES ('838:00:01', 'no');
+INSERT INTO t1 VALUES ('838:00:01', 'no');
+INSERT INTO t1 VALUES ('838:00:01', 'no');
+INSERT INTO t1 VALUES ('838:00:01', 'no');
+INSERT INTO t1 VALUES ('838:00:01', 'no');
+INSERT INTO t1 VALUES ('838:00:01', 'no');
+INSERT INTO t1 VALUES ('838:00:02', 'no');
+INSERT INTO t1 VALUES ('838:00:03', 'yes');
+INSERT INTO t1 VALUES ('838:00:04', 'yes');
+EXPLAIN SELECT * FROM t1 WHERE a NOT IN ('838:00:01','838:00:02');
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	a	a	4	NULL	5	Using index condition
+SELECT * FROM t1 WHERE a NOT IN ('838:00:01','838:00:02');
+a	filler
+838:00:03	yes
+838:00:04	yes
+DROP TABLE t1;
+#
+# TIME(0), negative, huge
+#
+CREATE TABLE t1 (a TIME, filler CHAR(200), KEY(a));
+INSERT INTO t1 VALUES ('-838:00:01', 'no');
+INSERT INTO t1 VALUES ('-838:00:01', 'no');
+INSERT INTO t1 VALUES ('-838:00:01', 'no');
+INSERT INTO t1 VALUES ('-838:00:01', 'no');
+INSERT INTO t1 VALUES ('-838:00:01', 'no');
+INSERT INTO t1 VALUES ('-838:00:01', 'no');
+INSERT INTO t1 VALUES ('-838:00:01', 'no');
+INSERT INTO t1 VALUES ('-838:00:01', 'no');
+INSERT INTO t1 VALUES ('-838:00:01', 'no');
+INSERT INTO t1 VALUES ('-838:00:01', 'no');
+INSERT INTO t1 VALUES ('-838:00:01', 'no');
+INSERT INTO t1 VALUES ('-838:00:01', 'no');
+INSERT INTO t1 VALUES ('-838:00:01', 'no');
+INSERT INTO t1 VALUES ('-838:00:01', 'no');
+INSERT INTO t1 VALUES ('-838:00:01', 'no');
+INSERT INTO t1 VALUES ('-838:00:01', 'no');
+INSERT INTO t1 VALUES ('-838:00:01', 'no');
+INSERT INTO t1 VALUES ('-838:00:01', 'no');
+INSERT INTO t1 VALUES ('-838:00:01', 'no');
+INSERT INTO t1 VALUES ('-838:00:01', 'no');
+INSERT INTO t1 VALUES ('-838:00:01', 'no');
+INSERT INTO t1 VALUES ('-838:00:01', 'no');
+INSERT INTO t1 VALUES ('-838:00:01', 'no');
+INSERT INTO t1 VALUES ('-838:00:01', 'no');
+INSERT INTO t1 VALUES ('-838:00:02', 'no');
+INSERT INTO t1 VALUES ('-838:00:03', 'yes');
+INSERT INTO t1 VALUES ('-838:00:04', 'yes');
+EXPLAIN SELECT * FROM t1 WHERE a NOT IN ('-838:00:01','-838:00:02');
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	a	a	4	NULL	4	Using index condition
+SELECT * FROM t1 WHERE a NOT IN ('-838:00:01','-838:00:02');
+a	filler
+-838:00:04	yes
+-838:00:03	yes
+DROP TABLE t1;
+#
+# TIME(1), positive within 24 hours
+#
+CREATE TABLE t1 (a TIME(1), filler CHAR(200), KEY(a));
+INSERT INTO t1 VALUES ('23:00:01.1', 'no');
+INSERT INTO t1 VALUES ('23:00:01.1', 'no');
+INSERT INTO t1 VALUES ('23:00:01.1', 'no');
+INSERT INTO t1 VALUES ('23:00:01.1', 'no');
+INSERT INTO t1 VALUES ('23:00:01.1', 'no');
+INSERT INTO t1 VALUES ('23:00:01.1', 'no');
+INSERT INTO t1 VALUES ('23:00:01.1', 'no');
+INSERT INTO t1 VALUES ('23:00:01.1', 'no');
+INSERT INTO t1 VALUES ('23:00:01.1', 'no');
+INSERT INTO t1 VALUES ('23:00:01.1', 'no');
+INSERT INTO t1 VALUES ('23:00:01.1', 'no');
+INSERT INTO t1 VALUES ('23:00:01.1', 'no');
+INSERT INTO t1 VALUES ('23:00:01.1', 'no');
+INSERT INTO t1 VALUES ('23:00:01.1', 'no');
+INSERT INTO t1 VALUES ('23:00:01.1', 'no');
+INSERT INTO t1 VALUES ('23:00:01.1', 'no');
+INSERT INTO t1 VALUES ('23:00:01.1', 'no');
+INSERT INTO t1 VALUES ('23:00:01.1', 'no');
+INSERT INTO t1 VALUES ('23:00:01.1', 'no');
+INSERT INTO t1 VALUES ('23:00:01.1', 'no');
+INSERT INTO t1 VALUES ('23:00:01.1', 'no');
+INSERT INTO t1 VALUES ('23:00:01.1', 'no');
+INSERT INTO t1 VALUES ('23:00:01.1', 'no');
+INSERT INTO t1 VALUES ('23:00:01.1', 'no');
+INSERT INTO t1 VALUES ('23:00:02.1', 'no');
+INSERT INTO t1 VALUES ('23:00:03.1', 'yes');
+INSERT INTO t1 VALUES ('23:00:04.1', 'yes');
+EXPLAIN SELECT * FROM t1 WHERE a NOT IN ('23:00:01.1','23:00:02.1');
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	a	a	5	NULL	5	Using index condition
+SELECT * FROM t1 WHERE a NOT IN ('23:00:01.1','23:00:02.1');
+a	filler
+23:00:03.1	yes
+23:00:04.1	yes
+DROP TABLE t1;
+#
+# TIME(1), negative within 24 hours
+#
+CREATE TABLE t1 (a TIME(1), filler CHAR(200), KEY(a));
+INSERT INTO t1 VALUES ('-23:00:01.1', 'no');
+INSERT INTO t1 VALUES ('-23:00:01.1', 'no');
+INSERT INTO t1 VALUES ('-23:00:01.1', 'no');
+INSERT INTO t1 VALUES ('-23:00:01.1', 'no');
+INSERT INTO t1 VALUES ('-23:00:01.1', 'no');
+INSERT INTO t1 VALUES ('-23:00:01.1', 'no');
+INSERT INTO t1 VALUES ('-23:00:01.1', 'no');
+INSERT INTO t1 VALUES ('-23:00:01.1', 'no');
+INSERT INTO t1 VALUES ('-23:00:01.1', 'no');
+INSERT INTO t1 VALUES ('-23:00:01.1', 'no');
+INSERT INTO t1 VALUES ('-23:00:01.1', 'no');
+INSERT INTO t1 VALUES ('-23:00:01.1', 'no');
+INSERT INTO t1 VALUES ('-23:00:01.1', 'no');
+INSERT INTO t1 VALUES ('-23:00:01.1', 'no');
+INSERT INTO t1 VALUES ('-23:00:01.1', 'no');
+INSERT INTO t1 VALUES ('-23:00:01.1', 'no');
+INSERT INTO t1 VALUES ('-23:00:01.1', 'no');
+INSERT INTO t1 VALUES ('-23:00:01.1', 'no');
+INSERT INTO t1 VALUES ('-23:00:01.1', 'no');
+INSERT INTO t1 VALUES ('-23:00:01.1', 'no');
+INSERT INTO t1 VALUES ('-23:00:01.1', 'no');
+INSERT INTO t1 VALUES ('-23:00:01.1', 'no');
+INSERT INTO t1 VALUES ('-23:00:01.1', 'no');
+INSERT INTO t1 VALUES ('-23:00:01.1', 'no');
+INSERT INTO t1 VALUES ('-23:00:02.1', 'no');
+INSERT INTO t1 VALUES ('-23:00:03.1', 'yes');
+INSERT INTO t1 VALUES ('-23:00:04.1', 'yes');
+EXPLAIN SELECT * FROM t1 WHERE a NOT IN ('-23:00:01.1','-23:00:02.1');
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	a	a	5	NULL	4	Using index condition
+SELECT * FROM t1 WHERE a NOT IN ('-23:00:01.1','-23:00:02.1');
+a	filler
+-23:00:04.1	yes
+-23:00:03.1	yes
+DROP TABLE t1;
+#
+# TIME(1), positive, huge
+#
+CREATE TABLE t1 (a TIME(1), filler CHAR(200), KEY(a));
+INSERT INTO t1 VALUES ('838:00:01.1', 'no');
+INSERT INTO t1 VALUES ('838:00:01.1', 'no');
+INSERT INTO t1 VALUES ('838:00:01.1', 'no');
+INSERT INTO t1 VALUES ('838:00:01.1', 'no');
+INSERT INTO t1 VALUES ('838:00:01.1', 'no');
+INSERT INTO t1 VALUES ('838:00:01.1', 'no');
+INSERT INTO t1 VALUES ('838:00:01.1', 'no');
+INSERT INTO t1 VALUES ('838:00:01.1', 'no');
+INSERT INTO t1 VALUES ('838:00:01.1', 'no');
+INSERT INTO t1 VALUES ('838:00:01.1', 'no');
+INSERT INTO t1 VALUES ('838:00:01.1', 'no');
+INSERT INTO t1 VALUES ('838:00:01.1', 'no');
+INSERT INTO t1 VALUES ('838:00:01.1', 'no');
+INSERT INTO t1 VALUES ('838:00:01.1', 'no');
+INSERT INTO t1 VALUES ('838:00:01.1', 'no');
+INSERT INTO t1 VALUES ('838:00:01.1', 'no');
+INSERT INTO t1 VALUES ('838:00:01.1', 'no');
+INSERT INTO t1 VALUES ('838:00:01.1', 'no');
+INSERT INTO t1 VALUES ('838:00:01.1', 'no');
+INSERT INTO t1 VALUES ('838:00:01.1', 'no');
+INSERT INTO t1 VALUES ('838:00:01.1', 'no');
+INSERT INTO t1 VALUES ('838:00:01.1', 'no');
+INSERT INTO t1 VALUES ('838:00:01.1', 'no');
+INSERT INTO t1 VALUES ('838:00:01.1', 'no');
+INSERT INTO t1 VALUES ('838:00:02.1', 'no');
+INSERT INTO t1 VALUES ('838:00:03.1', 'yes');
+INSERT INTO t1 VALUES ('838:00:04.1', 'yes');
+EXPLAIN SELECT * FROM t1 WHERE a NOT IN ('838:00:01.1','838:00:02.1');
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	a	a	5	NULL	5	Using index condition
+SELECT * FROM t1 WHERE a NOT IN ('838:00:01.1','838:00:02.1');
+a	filler
+838:00:03.1	yes
+838:00:04.1	yes
+DROP TABLE t1;
+#
+# TIME(1), negative, huge
+#
+CREATE TABLE t1 (a TIME(1), filler CHAR(200), KEY(a));
+INSERT INTO t1 VALUES ('-838:00:01.1', 'no');
+INSERT INTO t1 VALUES ('-838:00:01.1', 'no');
+INSERT INTO t1 VALUES ('-838:00:01.1', 'no');
+INSERT INTO t1 VALUES ('-838:00:01.1', 'no');
+INSERT INTO t1 VALUES ('-838:00:01.1', 'no');
+INSERT INTO t1 VALUES ('-838:00:01.1', 'no');
+INSERT INTO t1 VALUES ('-838:00:01.1', 'no');
+INSERT INTO t1 VALUES ('-838:00:01.1', 'no');
+INSERT INTO t1 VALUES ('-838:00:01.1', 'no');
+INSERT INTO t1 VALUES ('-838:00:01.1', 'no');
+INSERT INTO t1 VALUES ('-838:00:01.1', 'no');
+INSERT INTO t1 VALUES ('-838:00:01.1', 'no');
+INSERT INTO t1 VALUES ('-838:00:01.1', 'no');
+INSERT INTO t1 VALUES ('-838:00:01.1', 'no');
+INSERT INTO t1 VALUES ('-838:00:01.1', 'no');
+INSERT INTO t1 VALUES ('-838:00:01.1', 'no');
+INSERT INTO t1 VALUES ('-838:00:01.1', 'no');
+INSERT INTO t1 VALUES ('-838:00:01.1', 'no');
+INSERT INTO t1 VALUES ('-838:00:01.1', 'no');
+INSERT INTO t1 VALUES ('-838:00:01.1', 'no');
+INSERT INTO t1 VALUES ('-838:00:01.1', 'no');
+INSERT INTO t1 VALUES ('-838:00:01.1', 'no');
+INSERT INTO t1 VALUES ('-838:00:01.1', 'no');
+INSERT INTO t1 VALUES ('-838:00:01.1', 'no');
+INSERT INTO t1 VALUES ('-838:00:02.1', 'no');
+INSERT INTO t1 VALUES ('-838:00:03.1', 'yes');
+INSERT INTO t1 VALUES ('-838:00:04.1', 'yes');
+EXPLAIN SELECT * FROM t1 WHERE a NOT IN ('-838:00:01.1','-838:00:02.1');
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	a	a	5	NULL	4	Using index condition
+SELECT * FROM t1 WHERE a NOT IN ('-838:00:01.1','-838:00:02.1');
+a	filler
+-838:00:04.1	yes
+-838:00:03.1	yes
+DROP TABLE t1;
diff --git a/mysql-test/t/type_datetime.test b/mysql-test/t/type_datetime.test
index b8029d616a5..dafa9f9456e 100644
--- a/mysql-test/t/type_datetime.test
+++ b/mysql-test/t/type_datetime.test
@@ -768,6 +768,83 @@ SELECT * FROM t1;
 DROP TABLE t1;
 SET timestamp=DEFAULT;
 
+--echo #
+--echo # MDEV-15310 Range optimizer does not work well for "WHERE temporal_column NOT IN (const_list)"
+--echo #
+
+--echo #
+--echo # DATETIME(0)
+--echo #
+
+CREATE TABLE t1 (a DATETIME, filler CHAR(200), KEY(a));
+INSERT INTO t1 VALUES ('2001-01-01 23:00:01', 'no');
+INSERT INTO t1 VALUES ('2001-01-01 23:00:01', 'no');
+INSERT INTO t1 VALUES ('2001-01-01 23:00:01', 'no');
+INSERT INTO t1 VALUES ('2001-01-01 23:00:01', 'no');
+INSERT INTO t1 VALUES ('2001-01-01 23:00:01', 'no');
+INSERT INTO t1 VALUES ('2001-01-01 23:00:01', 'no');
+INSERT INTO t1 VALUES ('2001-01-01 23:00:01', 'no');
+INSERT INTO t1 VALUES ('2001-01-01 23:00:01', 'no');
+INSERT INTO t1 VALUES ('2001-01-01 23:00:01', 'no');
+INSERT INTO t1 VALUES ('2001-01-01 23:00:01', 'no');
+INSERT INTO t1 VALUES ('2001-01-01 23:00:01', 'no');
+INSERT INTO t1 VALUES ('2001-01-01 23:00:01', 'no');
+INSERT INTO t1 VALUES ('2001-01-01 23:00:01', 'no');
+INSERT INTO t1 VALUES ('2001-01-01 23:00:01', 'no');
+INSERT INTO t1 VALUES ('2001-01-01 23:00:01', 'no');
+INSERT INTO t1 VALUES ('2001-01-01 23:00:01', 'no');
+INSERT INTO t1 VALUES ('2001-01-01 23:00:01', 'no');
+INSERT INTO t1 VALUES ('2001-01-01 23:00:01', 'no');
+INSERT INTO t1 VALUES ('2001-01-01 23:00:01', 'no');
+INSERT INTO t1 VALUES ('2001-01-01 23:00:01', 'no');
+INSERT INTO t1 VALUES ('2001-01-01 23:00:01', 'no');
+INSERT INTO t1 VALUES ('2001-01-01 23:00:01', 'no');
+INSERT INTO t1 VALUES ('2001-01-01 23:00:01', 'no');
+INSERT INTO t1 VALUES ('2001-01-01 23:00:01', 'no');
+INSERT INTO t1 VALUES ('2001-01-01 23:00:02', 'no');
+INSERT INTO t1 VALUES ('2001-01-01 23:00:03', 'yes');
+INSERT INTO t1 VALUES ('2001-01-01 23:00:04', 'yes');
+EXPLAIN SELECT * FROM t1 WHERE a NOT IN ('2001-01-01 23:00:01','2001-01-01 23:00:02');
+SELECT * FROM t1 WHERE a NOT IN ('2001-01-01 23:00:01','2001-01-01 23:00:02');
+DROP TABLE t1;
+
+
+--echo #
+--echo # DATETIME(1)
+--echo #
+
+CREATE TABLE t1 (a DATETIME(1), filler CHAR(200), KEY(a));
+INSERT INTO t1 VALUES ('2001-01-01 23:00:01.1', 'no');
+INSERT INTO t1 VALUES ('2001-01-01 23:00:01.1', 'no');
+INSERT INTO t1 VALUES ('2001-01-01 23:00:01.1', 'no');
+INSERT INTO t1 VALUES ('2001-01-01 23:00:01.1', 'no');
+INSERT INTO t1 VALUES ('2001-01-01 23:00:01.1', 'no');
+INSERT INTO t1 VALUES ('2001-01-01 23:00:01.1', 'no');
+INSERT INTO t1 VALUES ('2001-01-01 23:00:01.1', 'no');
+INSERT INTO t1 VALUES ('2001-01-01 23:00:01.1', 'no');
+INSERT INTO t1 VALUES ('2001-01-01 23:00:01.1', 'no');
+INSERT INTO t1 VALUES ('2001-01-01 23:00:01.1', 'no');
+INSERT INTO t1 VALUES ('2001-01-01 23:00:01.1', 'no');
+INSERT INTO t1 VALUES ('2001-01-01 23:00:01.1', 'no');
+INSERT INTO t1 VALUES ('2001-01-01 23:00:01.1', 'no');
+INSERT INTO t1 VALUES ('2001-01-01 23:00:01.1', 'no');
+INSERT INTO t1 VALUES ('2001-01-01 23:00:01.1', 'no');
+INSERT INTO t1 VALUES ('2001-01-01 23:00:01.1', 'no');
+INSERT INTO t1 VALUES ('2001-01-01 23:00:01.1', 'no');
+INSERT INTO t1 VALUES ('2001-01-01 23:00:01.1', 'no');
+INSERT INTO t1 VALUES ('2001-01-01 23:00:01.1', 'no');
+INSERT INTO t1 VALUES ('2001-01-01 23:00:01.1', 'no');
+INSERT INTO t1 VALUES ('2001-01-01 23:00:01.1', 'no');
+INSERT INTO t1 VALUES ('2001-01-01 23:00:01.1', 'no');
+INSERT INTO t1 VALUES ('2001-01-01 23:00:01.1', 'no');
+INSERT INTO t1 VALUES ('2001-01-01 23:00:01.1', 'no');
+INSERT INTO t1 VALUES ('2001-01-01 23:00:02.1', 'no');
+INSERT INTO t1 VALUES ('2001-01-01 23:00:03.1', 'yes');
+INSERT INTO t1 VALUES ('2001-01-01 23:00:04.1', 'yes');
+EXPLAIN SELECT * FROM t1 WHERE a NOT IN ('2001-01-01 23:00:01.1','2001-01-01 23:00:02.1');
+SELECT * FROM t1 WHERE a NOT IN ('2001-01-01 23:00:01.1','2001-01-01 23:00:02.1');
+DROP TABLE t1;
+
 
 --echo #
 --echo # End of 10.3 tests
diff --git a/mysql-test/t/type_time.test b/mysql-test/t/type_time.test
index 4bb4e5c4267..6fffd948dc5 100644
--- a/mysql-test/t/type_time.test
+++ b/mysql-test/t/type_time.test
@@ -902,3 +902,368 @@ SELECT
 FROM t1
 ORDER BY country, dt;
 DROP TABLE t1;
+
+
+--echo #
+--echo #  MDEV-15310 Range optimizer does not work well for "WHERE temporal_column NOT IN (const_list)"
+--echo #
+
+--echo #
+--echo # TIME(0), positive within 24 hour
+--echo #
+
+CREATE TABLE t1 (a TIME, filler CHAR(200), KEY(a));
+INSERT INTO t1 VALUES ('23:00:01', 'no');
+INSERT INTO t1 VALUES ('23:00:01', 'no');
+INSERT INTO t1 VALUES ('23:00:01', 'no');
+INSERT INTO t1 VALUES ('23:00:01', 'no');
+INSERT INTO t1 VALUES ('23:00:01', 'no');
+INSERT INTO t1 VALUES ('23:00:01', 'no');
+INSERT INTO t1 VALUES ('23:00:01', 'no');
+INSERT INTO t1 VALUES ('23:00:01', 'no');
+INSERT INTO t1 VALUES ('23:00:01', 'no');
+INSERT INTO t1 VALUES ('23:00:01', 'no');
+INSERT INTO t1 VALUES ('23:00:01', 'no');
+INSERT INTO t1 VALUES ('23:00:01', 'no');
+INSERT INTO t1 VALUES ('23:00:01', 'no');
+INSERT INTO t1 VALUES ('23:00:01', 'no');
+INSERT INTO t1 VALUES ('23:00:01', 'no');
+INSERT INTO t1 VALUES ('23:00:01', 'no');
+INSERT INTO t1 VALUES ('23:00:01', 'no');
+INSERT INTO t1 VALUES ('23:00:01', 'no');
+INSERT INTO t1 VALUES ('23:00:01', 'no');
+INSERT INTO t1 VALUES ('23:00:01', 'no');
+INSERT INTO t1 VALUES ('23:00:01', 'no');
+INSERT INTO t1 VALUES ('23:00:01', 'no');
+INSERT INTO t1 VALUES ('23:00:01', 'no');
+INSERT INTO t1 VALUES ('23:00:01', 'no');
+INSERT INTO t1 VALUES ('23:00:02', 'no');
+INSERT INTO t1 VALUES ('23:00:03', 'yes');
+INSERT INTO t1 VALUES ('23:00:04', 'yes');
+EXPLAIN SELECT * FROM t1 WHERE a NOT IN ('23:00:01','23:00:02');
+SELECT * FROM t1 WHERE a NOT IN ('23:00:01','23:00:02');
+DROP TABLE t1;
+
+--echo #
+--echo # TIME(0), negative
+--echo #
+
+CREATE TABLE t1 (a TIME, filler CHAR(200), KEY(a));
+INSERT INTO t1 VALUES ('-23:00:01', 'no');
+INSERT INTO t1 VALUES ('-23:00:01', 'no');
+INSERT INTO t1 VALUES ('-23:00:01', 'no');
+INSERT INTO t1 VALUES ('-23:00:01', 'no');
+INSERT INTO t1 VALUES ('-23:00:01', 'no');
+INSERT INTO t1 VALUES ('-23:00:01', 'no');
+INSERT INTO t1 VALUES ('-23:00:01', 'no');
+INSERT INTO t1 VALUES ('-23:00:01', 'no');
+INSERT INTO t1 VALUES ('-23:00:01', 'no');
+INSERT INTO t1 VALUES ('-23:00:01', 'no');
+INSERT INTO t1 VALUES ('-23:00:01', 'no');
+INSERT INTO t1 VALUES ('-23:00:01', 'no');
+INSERT INTO t1 VALUES ('-23:00:01', 'no');
+INSERT INTO t1 VALUES ('-23:00:01', 'no');
+INSERT INTO t1 VALUES ('-23:00:01', 'no');
+INSERT INTO t1 VALUES ('-23:00:01', 'no');
+INSERT INTO t1 VALUES ('-23:00:01', 'no');
+INSERT INTO t1 VALUES ('-23:00:01', 'no');
+INSERT INTO t1 VALUES ('-23:00:01', 'no');
+INSERT INTO t1 VALUES ('-23:00:01', 'no');
+INSERT INTO t1 VALUES ('-23:00:01', 'no');
+INSERT INTO t1 VALUES ('-23:00:01', 'no');
+INSERT INTO t1 VALUES ('-23:00:01', 'no');
+INSERT INTO t1 VALUES ('-23:00:01', 'no');
+INSERT INTO t1 VALUES ('-23:00:02', 'no');
+INSERT INTO t1 VALUES ('-23:00:03', 'yes');
+INSERT INTO t1 VALUES ('-23:00:04', 'yes');
+EXPLAIN SELECT * FROM t1 WHERE a NOT IN ('-23:00:01','-23:00:02');
+SELECT * FROM t1 WHERE a NOT IN ('-23:00:01','-23:00:02');
+DROP TABLE t1;
+
+--echo #
+--echo # TIME(0), positive ouside 24 hours
+--echo #
+
+CREATE TABLE t1 (a TIME, filler CHAR(200), KEY(a));
+INSERT INTO t1 VALUES ('24:00:01', 'no');
+INSERT INTO t1 VALUES ('24:00:01', 'no');
+INSERT INTO t1 VALUES ('24:00:01', 'no');
+INSERT INTO t1 VALUES ('24:00:01', 'no');
+INSERT INTO t1 VALUES ('24:00:01', 'no');
+INSERT INTO t1 VALUES ('24:00:01', 'no');
+INSERT INTO t1 VALUES ('24:00:01', 'no');
+INSERT INTO t1 VALUES ('24:00:01', 'no');
+INSERT INTO t1 VALUES ('24:00:01', 'no');
+INSERT INTO t1 VALUES ('24:00:01', 'no');
+INSERT INTO t1 VALUES ('24:00:01', 'no');
+INSERT INTO t1 VALUES ('24:00:01', 'no');
+INSERT INTO t1 VALUES ('24:00:01', 'no');
+INSERT INTO t1 VALUES ('24:00:01', 'no');
+INSERT INTO t1 VALUES ('24:00:01', 'no');
+INSERT INTO t1 VALUES ('24:00:01', 'no');
+INSERT INTO t1 VALUES ('24:00:01', 'no');
+INSERT INTO t1 VALUES ('24:00:01', 'no');
+INSERT INTO t1 VALUES ('24:00:01', 'no');
+INSERT INTO t1 VALUES ('24:00:01', 'no');
+INSERT INTO t1 VALUES ('24:00:01', 'no');
+INSERT INTO t1 VALUES ('24:00:01', 'no');
+INSERT INTO t1 VALUES ('24:00:01', 'no');
+INSERT INTO t1 VALUES ('24:00:01', 'no');
+INSERT INTO t1 VALUES ('24:00:02', 'no');
+INSERT INTO t1 VALUES ('24:00:03', 'yes');
+INSERT INTO t1 VALUES ('24:00:04', 'yes');
+EXPLAIN SELECT * FROM t1 WHERE a NOT IN ('24:00:01','24:00:02');
+SELECT * FROM t1 WHERE a NOT IN ('24:00:01','24:00:02');
+DROP TABLE t1;
+
+--echo #
+--echo # TIME(0), negative, ouside 24 hours
+--echo #
+
+CREATE TABLE t1 (a TIME, filler CHAR(200), KEY(a));
+INSERT INTO t1 VALUES ('-24:00:01', 'no');
+INSERT INTO t1 VALUES ('-24:00:01', 'no');
+INSERT INTO t1 VALUES ('-24:00:01', 'no');
+INSERT INTO t1 VALUES ('-24:00:01', 'no');
+INSERT INTO t1 VALUES ('-24:00:01', 'no');
+INSERT INTO t1 VALUES ('-24:00:01', 'no');
+INSERT INTO t1 VALUES ('-24:00:01', 'no');
+INSERT INTO t1 VALUES ('-24:00:01', 'no');
+INSERT INTO t1 VALUES ('-24:00:01', 'no');
+INSERT INTO t1 VALUES ('-24:00:01', 'no');
+INSERT INTO t1 VALUES ('-24:00:01', 'no');
+INSERT INTO t1 VALUES ('-24:00:01', 'no');
+INSERT INTO t1 VALUES ('-24:00:01', 'no');
+INSERT INTO t1 VALUES ('-24:00:01', 'no');
+INSERT INTO t1 VALUES ('-24:00:01', 'no');
+INSERT INTO t1 VALUES ('-24:00:01', 'no');
+INSERT INTO t1 VALUES ('-24:00:01', 'no');
+INSERT INTO t1 VALUES ('-24:00:01', 'no');
+INSERT INTO t1 VALUES ('-24:00:01', 'no');
+INSERT INTO t1 VALUES ('-24:00:01', 'no');
+INSERT INTO t1 VALUES ('-24:00:01', 'no');
+INSERT INTO t1 VALUES ('-24:00:01', 'no');
+INSERT INTO t1 VALUES ('-24:00:01', 'no');
+INSERT INTO t1 VALUES ('-24:00:01', 'no');
+INSERT INTO t1 VALUES ('-24:00:02', 'no');
+INSERT INTO t1 VALUES ('-24:00:03', 'yes');
+INSERT INTO t1 VALUES ('-24:00:04', 'yes');
+EXPLAIN SELECT * FROM t1 WHERE a NOT IN ('-24:00:01','-24:00:02');
+SELECT * FROM t1 WHERE a NOT IN ('-24:00:01','-24:00:02');
+DROP TABLE t1;
+
+--echo #
+--echo # TIME(0), positive, huge
+--echo #
+
+CREATE TABLE t1 (a TIME, filler CHAR(200), KEY(a));
+INSERT INTO t1 VALUES ('838:00:01', 'no');
+INSERT INTO t1 VALUES ('838:00:01', 'no');
+INSERT INTO t1 VALUES ('838:00:01', 'no');
+INSERT INTO t1 VALUES ('838:00:01', 'no');
+INSERT INTO t1 VALUES ('838:00:01', 'no');
+INSERT INTO t1 VALUES ('838:00:01', 'no');
+INSERT INTO t1 VALUES ('838:00:01', 'no');
+INSERT INTO t1 VALUES ('838:00:01', 'no');
+INSERT INTO t1 VALUES ('838:00:01', 'no');
+INSERT INTO t1 VALUES ('838:00:01', 'no');
+INSERT INTO t1 VALUES ('838:00:01', 'no');
+INSERT INTO t1 VALUES ('838:00:01', 'no');
+INSERT INTO t1 VALUES ('838:00:01', 'no');
+INSERT INTO t1 VALUES ('838:00:01', 'no');
+INSERT INTO t1 VALUES ('838:00:01', 'no');
+INSERT INTO t1 VALUES ('838:00:01', 'no');
+INSERT INTO t1 VALUES ('838:00:01', 'no');
+INSERT INTO t1 VALUES ('838:00:01', 'no');
+INSERT INTO t1 VALUES ('838:00:01', 'no');
+INSERT INTO t1 VALUES ('838:00:01', 'no');
+INSERT INTO t1 VALUES ('838:00:01', 'no');
+INSERT INTO t1 VALUES ('838:00:01', 'no');
+INSERT INTO t1 VALUES ('838:00:01', 'no');
+INSERT INTO t1 VALUES ('838:00:01', 'no');
+INSERT INTO t1 VALUES ('838:00:02', 'no');
+INSERT INTO t1 VALUES ('838:00:03', 'yes');
+INSERT INTO t1 VALUES ('838:00:04', 'yes');
+EXPLAIN SELECT * FROM t1 WHERE a NOT IN ('838:00:01','838:00:02');
+SELECT * FROM t1 WHERE a NOT IN ('838:00:01','838:00:02');
+DROP TABLE t1;
+
+--echo #
+--echo # TIME(0), negative, huge
+--echo #
+
+CREATE TABLE t1 (a TIME, filler CHAR(200), KEY(a));
+INSERT INTO t1 VALUES ('-838:00:01', 'no');
+INSERT INTO t1 VALUES ('-838:00:01', 'no');
+INSERT INTO t1 VALUES ('-838:00:01', 'no');
+INSERT INTO t1 VALUES ('-838:00:01', 'no');
+INSERT INTO t1 VALUES ('-838:00:01', 'no');
+INSERT INTO t1 VALUES ('-838:00:01', 'no');
+INSERT INTO t1 VALUES ('-838:00:01', 'no');
+INSERT INTO t1 VALUES ('-838:00:01', 'no');
+INSERT INTO t1 VALUES ('-838:00:01', 'no');
+INSERT INTO t1 VALUES ('-838:00:01', 'no');
+INSERT INTO t1 VALUES ('-838:00:01', 'no');
+INSERT INTO t1 VALUES ('-838:00:01', 'no');
+INSERT INTO t1 VALUES ('-838:00:01', 'no');
+INSERT INTO t1 VALUES ('-838:00:01', 'no');
+INSERT INTO t1 VALUES ('-838:00:01', 'no');
+INSERT INTO t1 VALUES ('-838:00:01', 'no');
+INSERT INTO t1 VALUES ('-838:00:01', 'no');
+INSERT INTO t1 VALUES ('-838:00:01', 'no');
+INSERT INTO t1 VALUES ('-838:00:01', 'no');
+INSERT INTO t1 VALUES ('-838:00:01', 'no');
+INSERT INTO t1 VALUES ('-838:00:01', 'no');
+INSERT INTO t1 VALUES ('-838:00:01', 'no');
+INSERT INTO t1 VALUES ('-838:00:01', 'no');
+INSERT INTO t1 VALUES ('-838:00:01', 'no');
+INSERT INTO t1 VALUES ('-838:00:02', 'no');
+INSERT INTO t1 VALUES ('-838:00:03', 'yes');
+INSERT INTO t1 VALUES ('-838:00:04', 'yes');
+EXPLAIN SELECT * FROM t1 WHERE a NOT IN ('-838:00:01','-838:00:02');
+SELECT * FROM t1 WHERE a NOT IN ('-838:00:01','-838:00:02');
+DROP TABLE t1;
+
+--echo #
+--echo # TIME(1), positive within 24 hours
+--echo #
+
+CREATE TABLE t1 (a TIME(1), filler CHAR(200), KEY(a));
+INSERT INTO t1 VALUES ('23:00:01.1', 'no');
+INSERT INTO t1 VALUES ('23:00:01.1', 'no');
+INSERT INTO t1 VALUES ('23:00:01.1', 'no');
+INSERT INTO t1 VALUES ('23:00:01.1', 'no');
+INSERT INTO t1 VALUES ('23:00:01.1', 'no');
+INSERT INTO t1 VALUES ('23:00:01.1', 'no');
+INSERT INTO t1 VALUES ('23:00:01.1', 'no');
+INSERT INTO t1 VALUES ('23:00:01.1', 'no');
+INSERT INTO t1 VALUES ('23:00:01.1', 'no');
+INSERT INTO t1 VALUES ('23:00:01.1', 'no');
+INSERT INTO t1 VALUES ('23:00:01.1', 'no');
+INSERT INTO t1 VALUES ('23:00:01.1', 'no');
+INSERT INTO t1 VALUES ('23:00:01.1', 'no');
+INSERT INTO t1 VALUES ('23:00:01.1', 'no');
+INSERT INTO t1 VALUES ('23:00:01.1', 'no');
+INSERT INTO t1 VALUES ('23:00:01.1', 'no');
+INSERT INTO t1 VALUES ('23:00:01.1', 'no');
+INSERT INTO t1 VALUES ('23:00:01.1', 'no');
+INSERT INTO t1 VALUES ('23:00:01.1', 'no');
+INSERT INTO t1 VALUES ('23:00:01.1', 'no');
+INSERT INTO t1 VALUES ('23:00:01.1', 'no');
+INSERT INTO t1 VALUES ('23:00:01.1', 'no');
+INSERT INTO t1 VALUES ('23:00:01.1', 'no');
+INSERT INTO t1 VALUES ('23:00:01.1', 'no');
+INSERT INTO t1 VALUES ('23:00:02.1', 'no');
+INSERT INTO t1 VALUES ('23:00:03.1', 'yes');
+INSERT INTO t1 VALUES ('23:00:04.1', 'yes');
+EXPLAIN SELECT * FROM t1 WHERE a NOT IN ('23:00:01.1','23:00:02.1');
+SELECT * FROM t1 WHERE a NOT IN ('23:00:01.1','23:00:02.1');
+DROP TABLE t1;
+
+--echo #
+--echo # TIME(1), negative within 24 hours
+--echo #
+
+CREATE TABLE t1 (a TIME(1), filler CHAR(200), KEY(a));
+INSERT INTO t1 VALUES ('-23:00:01.1', 'no');
+INSERT INTO t1 VALUES ('-23:00:01.1', 'no');
+INSERT INTO t1 VALUES ('-23:00:01.1', 'no');
+INSERT INTO t1 VALUES ('-23:00:01.1', 'no');
+INSERT INTO t1 VALUES ('-23:00:01.1', 'no');
+INSERT INTO t1 VALUES ('-23:00:01.1', 'no');
+INSERT INTO t1 VALUES ('-23:00:01.1', 'no');
+INSERT INTO t1 VALUES ('-23:00:01.1', 'no');
+INSERT INTO t1 VALUES ('-23:00:01.1', 'no');
+INSERT INTO t1 VALUES ('-23:00:01.1', 'no');
+INSERT INTO t1 VALUES ('-23:00:01.1', 'no');
+INSERT INTO t1 VALUES ('-23:00:01.1', 'no');
+INSERT INTO t1 VALUES ('-23:00:01.1', 'no');
+INSERT INTO t1 VALUES ('-23:00:01.1', 'no');
+INSERT INTO t1 VALUES ('-23:00:01.1', 'no');
+INSERT INTO t1 VALUES ('-23:00:01.1', 'no');
+INSERT INTO t1 VALUES ('-23:00:01.1', 'no');
+INSERT INTO t1 VALUES ('-23:00:01.1', 'no');
+INSERT INTO t1 VALUES ('-23:00:01.1', 'no');
+INSERT INTO t1 VALUES ('-23:00:01.1', 'no');
+INSERT INTO t1 VALUES ('-23:00:01.1', 'no');
+INSERT INTO t1 VALUES ('-23:00:01.1', 'no');
+INSERT INTO t1 VALUES ('-23:00:01.1', 'no');
+INSERT INTO t1 VALUES ('-23:00:01.1', 'no');
+INSERT INTO t1 VALUES ('-23:00:02.1', 'no');
+INSERT INTO t1 VALUES ('-23:00:03.1', 'yes');
+INSERT INTO t1 VALUES ('-23:00:04.1', 'yes');
+EXPLAIN SELECT * FROM t1 WHERE a NOT IN ('-23:00:01.1','-23:00:02.1');
+SELECT * FROM t1 WHERE a NOT IN ('-23:00:01.1','-23:00:02.1');
+DROP TABLE t1;
+
+--echo #
+--echo # TIME(1), positive, huge
+--echo #
+
+CREATE TABLE t1 (a TIME(1), filler CHAR(200), KEY(a));
+INSERT INTO t1 VALUES ('838:00:01.1', 'no');
+INSERT INTO t1 VALUES ('838:00:01.1', 'no');
+INSERT INTO t1 VALUES ('838:00:01.1', 'no');
+INSERT INTO t1 VALUES ('838:00:01.1', 'no');
+INSERT INTO t1 VALUES ('838:00:01.1', 'no');
+INSERT INTO t1 VALUES ('838:00:01.1', 'no');
+INSERT INTO t1 VALUES ('838:00:01.1', 'no');
+INSERT INTO t1 VALUES ('838:00:01.1', 'no');
+INSERT INTO t1 VALUES ('838:00:01.1', 'no');
+INSERT INTO t1 VALUES ('838:00:01.1', 'no');
+INSERT INTO t1 VALUES ('838:00:01.1', 'no');
+INSERT INTO t1 VALUES ('838:00:01.1', 'no');
+INSERT INTO t1 VALUES ('838:00:01.1', 'no');
+INSERT INTO t1 VALUES ('838:00:01.1', 'no');
+INSERT INTO t1 VALUES ('838:00:01.1', 'no');
+INSERT INTO t1 VALUES ('838:00:01.1', 'no');
+INSERT INTO t1 VALUES ('838:00:01.1', 'no');
+INSERT INTO t1 VALUES ('838:00:01.1', 'no');
+INSERT INTO t1 VALUES ('838:00:01.1', 'no');
+INSERT INTO t1 VALUES ('838:00:01.1', 'no');
+INSERT INTO t1 VALUES ('838:00:01.1', 'no');
+INSERT INTO t1 VALUES ('838:00:01.1', 'no');
+INSERT INTO t1 VALUES ('838:00:01.1', 'no');
+INSERT INTO t1 VALUES ('838:00:01.1', 'no');
+INSERT INTO t1 VALUES ('838:00:02.1', 'no');
+INSERT INTO t1 VALUES ('838:00:03.1', 'yes');
+INSERT INTO t1 VALUES ('838:00:04.1', 'yes');
+EXPLAIN SELECT * FROM t1 WHERE a NOT IN ('838:00:01.1','838:00:02.1');
+SELECT * FROM t1 WHERE a NOT IN ('838:00:01.1','838:00:02.1');
+DROP TABLE t1;
+
+--echo #
+--echo # TIME(1), negative, huge
+--echo #
+
+CREATE TABLE t1 (a TIME(1), filler CHAR(200), KEY(a));
+INSERT INTO t1 VALUES ('-838:00:01.1', 'no');
+INSERT INTO t1 VALUES ('-838:00:01.1', 'no');
+INSERT INTO t1 VALUES ('-838:00:01.1', 'no');
+INSERT INTO t1 VALUES ('-838:00:01.1', 'no');
+INSERT INTO t1 VALUES ('-838:00:01.1', 'no');
+INSERT INTO t1 VALUES ('-838:00:01.1', 'no');
+INSERT INTO t1 VALUES ('-838:00:01.1', 'no');
+INSERT INTO t1 VALUES ('-838:00:01.1', 'no');
+INSERT INTO t1 VALUES ('-838:00:01.1', 'no');
+INSERT INTO t1 VALUES ('-838:00:01.1', 'no');
+INSERT INTO t1 VALUES ('-838:00:01.1', 'no');
+INSERT INTO t1 VALUES ('-838:00:01.1', 'no');
+INSERT INTO t1 VALUES ('-838:00:01.1', 'no');
+INSERT INTO t1 VALUES ('-838:00:01.1', 'no');
+INSERT INTO t1 VALUES ('-838:00:01.1', 'no');
+INSERT INTO t1 VALUES ('-838:00:01.1', 'no');
+INSERT INTO t1 VALUES ('-838:00:01.1', 'no');
+INSERT INTO t1 VALUES ('-838:00:01.1', 'no');
+INSERT INTO t1 VALUES ('-838:00:01.1', 'no');
+INSERT INTO t1 VALUES ('-838:00:01.1', 'no');
+INSERT INTO t1 VALUES ('-838:00:01.1', 'no');
+INSERT INTO t1 VALUES ('-838:00:01.1', 'no');
+INSERT INTO t1 VALUES ('-838:00:01.1', 'no');
+INSERT INTO t1 VALUES ('-838:00:01.1', 'no');
+INSERT INTO t1 VALUES ('-838:00:02.1', 'no');
+INSERT INTO t1 VALUES ('-838:00:03.1', 'yes');
+INSERT INTO t1 VALUES ('-838:00:04.1', 'yes');
+EXPLAIN SELECT * FROM t1 WHERE a NOT IN ('-838:00:01.1','-838:00:02.1');
+SELECT * FROM t1 WHERE a NOT IN ('-838:00:01.1','-838:00:02.1');
+DROP TABLE t1;
diff --git a/sql-common/my_time.c b/sql-common/my_time.c
index d7953fe36bf..0fa5e5ff1bb 100644
--- a/sql-common/my_time.c
+++ b/sql-common/my_time.c
@@ -1425,31 +1425,3 @@ double TIME_to_double(const MYSQL_TIME *my_time)
   d+= my_time->second_part/(double)TIME_SECOND_PART_FACTOR;
   return my_time->neg ? -d : d;
 }
-
-longlong pack_time(const MYSQL_TIME *my_time)
-{
-  return  ((((((my_time->year     * 13ULL +
-               my_time->month)    * 32ULL +
-               my_time->day)      * 24ULL +
-               my_time->hour)     * 60ULL +
-               my_time->minute)   * 60ULL +
-               my_time->second)   * 1000000ULL +
-               my_time->second_part) * (my_time->neg ? -1 : 1);
-}
-
-#define get_one(WHERE, FACTOR) WHERE= (ulong)(packed % FACTOR); packed/= FACTOR
-
-MYSQL_TIME *unpack_time(longlong packed, MYSQL_TIME *my_time)
-{
-  if ((my_time->neg= packed < 0))
-    packed= -packed;
-  get_one(my_time->second_part, 1000000ULL);
-  get_one(my_time->second,           60U);
-  get_one(my_time->minute,           60U);
-  get_one(my_time->hour,             24U);
-  get_one(my_time->day,              32U);
-  get_one(my_time->month,            13U);
-  my_time->year= (uint)packed;
-  my_time->time_type= MYSQL_TIMESTAMP_DATETIME;
-  return my_time;
-}
diff --git a/sql/field.cc b/sql/field.cc
index d05b08fde74..9b6465988f6 100644
--- a/sql/field.cc
+++ b/sql/field.cc
@@ -5763,7 +5763,10 @@ static void calc_datetime_days_diff(MYSQL_TIME *ltime, long days)
   long daydiff= calc_daynr(ltime->year, ltime->month, ltime->day) - days;
   ltime->year= ltime->month= 0;
   if (daydiff >=0 )
+  {
     ltime->day= daydiff;
+    ltime->time_type= MYSQL_TIMESTAMP_TIME;
+  }
   else
   {
     longlong timediff= ((((daydiff        * 24LL +
@@ -5771,16 +5774,8 @@ static void calc_datetime_days_diff(MYSQL_TIME *ltime, long days)
                            ltime->minute) * 60LL +
                            ltime->second) * 1000000LL +
                            ltime->second_part);
-    unpack_time(timediff, ltime);
-    /*
-      unpack_time() broke down hours into ltime members hour,day,month.
-      Mix them back to ltime->hour using the same factors
-      that pack_time()/unpack_time() use (i.e. 32 for month).
-    */
-    ltime->hour+= (ltime->month * 32 + ltime->day) * 24;
-    ltime->month= ltime->day= 0;
+    unpack_time(timediff, ltime, MYSQL_TIMESTAMP_TIME);
   }
-  ltime->time_type= MYSQL_TIMESTAMP_TIME;
 }
 
 
@@ -6111,14 +6106,7 @@ bool Field_time_hires::get_date(MYSQL_TIME *ltime, ulonglong fuzzydate)
 
   packed= sec_part_unshift(packed - zero_point, dec);
 
-  unpack_time(packed, ltime);
-  /*
-    unpack_time() returns MYSQL_TIMESTAMP_DATETIME.
-    To get MYSQL_TIMESTAMP_TIME we need few adjustments
-  */
-  ltime->time_type= MYSQL_TIMESTAMP_TIME;
-  ltime->hour+= (ltime->month*32+ltime->day)*24;
-  ltime->month= ltime->day= 0;
+  unpack_time(packed, ltime, MYSQL_TIMESTAMP_TIME);
   return false;
 }
 
@@ -6781,7 +6769,7 @@ bool Field_datetime_hires::get_TIME(MYSQL_TIME *ltime, const uchar *pos,
 {
   ASSERT_COLUMN_MARKED_FOR_READ;
   ulonglong packed= read_bigendian(pos, Field_datetime_hires::pack_length());
-  unpack_time(sec_part_unshift(packed, dec), ltime);
+  unpack_time(sec_part_unshift(packed, dec), ltime, MYSQL_TIMESTAMP_DATETIME);
   return validate_MMDD(packed, ltime->month, ltime->day, fuzzydate);
 }
 
diff --git a/sql/item.cc b/sql/item.cc
index e05ba7d6389..83321ee2b06 100644
--- a/sql/item.cc
+++ b/sql/item.cc
@@ -6743,9 +6743,9 @@ Item *Item_int::clone_item(THD *thd)
 }
 
 
-void Item_datetime::set(longlong packed)
+void Item_datetime::set(longlong packed, enum_mysql_timestamp_type ts_type)
 {
-  unpack_time(packed, &ltime);
+  unpack_time(packed, &ltime, ts_type);
 }
 
 int Item_datetime::save_in_field(Field *field, bool no_conversions)
@@ -9801,13 +9801,7 @@ bool Item_cache_temporal::get_date(MYSQL_TIME *ltime, ulonglong fuzzydate)
     return 1;
   }
 
-  unpack_time(value, ltime);
-  ltime->time_type= mysql_timestamp_type();
-  if (ltime->time_type == MYSQL_TIMESTAMP_TIME)
-  {
-    ltime->hour+= (ltime->month*32+ltime->day)*24;
-    ltime->month= ltime->day= 0;
-  }
+  unpack_time(value, ltime, mysql_timestamp_type());
   return 0;
  
 }
@@ -9852,7 +9846,7 @@ Item *Item_cache_temporal::convert_to_basic_const_item(THD *thd)
   else
   {
     MYSQL_TIME ltime;
-    unpack_time(val_datetime_packed(), &ltime);
+    unpack_time(val_datetime_packed(), &ltime, MYSQL_TIMESTAMP_DATETIME);
     new_item= (Item*) new (thd->mem_root) Item_datetime_literal(thd, &ltime,
                                                                 decimals);
   }
diff --git a/sql/item.h b/sql/item.h
index 77aa6eb901e..951f7a19207 100644
--- a/sql/item.h
+++ b/sql/item.h
@@ -3558,7 +3558,12 @@ public:
   int save_in_field(Field *field, bool no_conversions);
   longlong val_int();
   double val_real() { return (double)val_int(); }
-  void set(longlong packed);
+  void set(longlong packed, enum_mysql_timestamp_type ts_type);
+  bool get_date(MYSQL_TIME *to, ulonglong fuzzydate)
+  {
+    *to= ltime;
+    return false;
+  }
 };
 
 
diff --git a/sql/item_cmpfunc.h b/sql/item_cmpfunc.h
index d4908ddf48d..900a979ce97 100644
--- a/sql/item_cmpfunc.h
+++ b/sql/item_cmpfunc.h
@@ -1321,7 +1321,7 @@ public:
   {
     return MY_TEST(compare(collation, base + pos1 * size, base + pos2 * size));
   }
-  virtual Item_result result_type()= 0;
+  virtual const Type_handler *type_handler() const= 0;
 };
 
 class in_string :public in_vector
@@ -1352,7 +1352,7 @@ public:
     Item_string_for_in_vector *to= (Item_string_for_in_vector*) item;
     to->set_value(str);
   }
-  Item_result result_type() { return STRING_RESULT; }
+  const Type_handler *type_handler() const { return &type_handler_varchar; }
 };
 
 class in_longlong :public in_vector
@@ -1379,7 +1379,7 @@ public:
     ((Item_int*) item)->unsigned_flag= (bool)
       ((packed_longlong*) base)[pos].unsigned_flag;
   }
-  Item_result result_type() { return INT_RESULT; }
+  const Type_handler *type_handler() const { return &type_handler_longlong; }
 
   friend int cmp_longlong(void *cmp_arg, packed_longlong *a,packed_longlong *b);
 };
@@ -1405,9 +1405,11 @@ public:
   void value_to_item(uint pos, Item *item)
   {
     packed_longlong *val= reinterpret_cast<packed_longlong*>(base)+pos;
-    Item_datetime *dt= reinterpret_cast<Item_datetime*>(item);
-    dt->set(val->val);
+    Item_datetime *dt= static_cast<Item_datetime*>(item);
+    dt->set(val->val, type_handler()->mysql_timestamp_type());
   }
+  uchar *get_value(Item *item)
+  { return get_value_internal(item, type_handler()->field_type()); }
   friend int cmp_longlong(void *cmp_arg, packed_longlong *a,packed_longlong *b);
 };
 
@@ -1419,8 +1421,7 @@ public:
    :in_temporal(thd, elements)
   {}
   void set(uint pos,Item *item);
-  uchar *get_value(Item *item)
-  { return get_value_internal(item, MYSQL_TYPE_DATETIME); }
+  const Type_handler *type_handler() const { return &type_handler_datetime2; }
 };
 
 
@@ -1431,8 +1432,7 @@ public:
    :in_temporal(thd, elements)
   {}
   void set(uint pos,Item *item);
-  uchar *get_value(Item *item)
-  { return get_value_internal(item, MYSQL_TYPE_TIME); }
+  const Type_handler *type_handler() const { return &type_handler_time2; }
 };
 
 
@@ -1448,7 +1448,7 @@ public:
   {
     ((Item_float*)item)->value= ((double*) base)[pos];
   }
-  Item_result result_type() { return REAL_RESULT; }
+  const Type_handler *type_handler() const { return &type_handler_double; }
 };
 
 
@@ -1466,7 +1466,7 @@ public:
     Item_decimal *item_dec= (Item_decimal*)item;
     item_dec->set_decimal_value(dec);
   }
-  Item_result result_type() { return DECIMAL_RESULT; }
+  const Type_handler *type_handler() const { return &type_handler_newdecimal; }
 };
 
 
@@ -2431,7 +2431,7 @@ public:
   void set(uint pos,Item *item);
   uchar *get_value(Item *item);
   friend class Item_func_in;
-  Item_result result_type() { return ROW_RESULT; }
+  const Type_handler *type_handler() const { return &type_handler_row; }
   cmp_item *get_cmp_item() { return &tmp; }
 };
 
diff --git a/sql/item_func.cc b/sql/item_func.cc
index bea57d6c938..4903e552c9d 100644
--- a/sql/item_func.cc
+++ b/sql/item_func.cc
@@ -2713,13 +2713,7 @@ bool Item_func_min_max::get_date_native(MYSQL_TIME *ltime, ulonglong fuzzy_date)
     if (i == 0 || (res < min_max ? cmp_sign : -cmp_sign) > 0)
       min_max= res;
   }
-  unpack_time(min_max, ltime);
-
-  if (Item_func_min_max::field_type() == MYSQL_TYPE_DATE)
-  {
-    ltime->time_type= MYSQL_TIMESTAMP_DATE;
-    ltime->hour= ltime->minute= ltime->second= ltime->second_part= 0;
-  }
+  unpack_time(min_max, ltime, mysql_timestamp_type());
 
   if (!(fuzzy_date & TIME_TIME_ONLY) &&
       ((null_value= check_date_with_warn(ltime, fuzzy_date,
diff --git a/sql/opt_range.cc b/sql/opt_range.cc
index e90dbb468f1..8b7cffef647 100644
--- a/sql/opt_range.cc
+++ b/sql/opt_range.cc
@@ -7055,7 +7055,7 @@ SEL_TREE *Item_func_in::get_func_mm_tree(RANGE_OPT_PARAM *param,
 
   if (negated)
   {
-    if (array && array->result_type() != ROW_RESULT)
+    if (array && array->type_handler()->result_type() != ROW_RESULT)
     {
       /*
         We get here for conditions in form "t.key NOT IN (c1, c2, ...)",
diff --git a/sql/sql_time.cc b/sql/sql_time.cc
index 986be049810..192d6a9d5c2 100644
--- a/sql/sql_time.cc
+++ b/sql/sql_time.cc
@@ -1391,3 +1391,46 @@ bool datetime_to_time_with_warn(THD *thd, const MYSQL_TIME *dt,
   int warnings= 0;
   return check_time_range(tm, dec, &warnings);
 }
+
+
+longlong pack_time(const MYSQL_TIME *my_time)
+{
+  return  ((((((my_time->year     * 13ULL +
+               my_time->month)    * 32ULL +
+               my_time->day)      * 24ULL +
+               my_time->hour)     * 60ULL +
+               my_time->minute)   * 60ULL +
+               my_time->second)   * 1000000ULL +
+               my_time->second_part) * (my_time->neg ? -1 : 1);
+}
+
+#define get_one(WHERE, FACTOR) WHERE= (ulong)(packed % FACTOR); packed/= FACTOR
+
+void unpack_time(longlong packed, MYSQL_TIME *my_time,
+                     enum_mysql_timestamp_type ts_type)
+{
+  if ((my_time->neg= packed < 0))
+    packed= -packed;
+  get_one(my_time->second_part, 1000000ULL);
+  get_one(my_time->second,           60U);
+  get_one(my_time->minute,           60U);
+  get_one(my_time->hour,             24U);
+  get_one(my_time->day,              32U);
+  get_one(my_time->month,            13U);
+  my_time->year= (uint)packed;
+  my_time->time_type= ts_type;
+  switch (ts_type) {
+  case MYSQL_TIMESTAMP_TIME:
+    my_time->hour+= (my_time->month * 32 + my_time->day) * 24;
+    my_time->month= my_time->day= 0;
+    break;
+  case MYSQL_TIMESTAMP_DATE:
+    my_time->hour= my_time->minute= my_time->second= my_time->second_part= 0;
+    break;
+  case MYSQL_TIMESTAMP_NONE:
+  case MYSQL_TIMESTAMP_ERROR:
+    DBUG_ASSERT(0);
+  case MYSQL_TIMESTAMP_DATETIME:
+    break;
+  }
+}
diff --git a/sql/sql_time.h b/sql/sql_time.h
index 4e8f280514f..a85fa16d5eb 100644
--- a/sql/sql_time.h
+++ b/sql/sql_time.h
@@ -233,4 +233,8 @@ bool make_date_with_warn(MYSQL_TIME *ltime,
                          ulonglong fuzzy_date, timestamp_type ts_type);
 bool adjust_time_range_with_warn(MYSQL_TIME *ltime, uint dec);
 
+longlong pack_time(const MYSQL_TIME *my_time);
+void unpack_time(longlong packed, MYSQL_TIME *my_time,
+                 enum_mysql_timestamp_type ts_type);
+
 #endif /* SQL_TIME_INCLUDED */
diff --git a/sql/sql_type.h b/sql/sql_type.h
index f4482c6fbdd..9afdf1a915f 100644
--- a/sql/sql_type.h
+++ b/sql/sql_type.h
@@ -24,7 +24,7 @@
 #include "mysqld.h"
 #include "sql_array.h"
 #include "sql_const.h"
-#include "my_time.h"
+#include "sql_time.h"
 
 class Field;
 class Column_definition;