summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--mysql-test/main/type_time.result122
-rw-r--r--mysql-test/main/type_time.test60
-rw-r--r--sql/field.cc34
-rw-r--r--sql/field.h4
-rw-r--r--sql/item.cc5
-rw-r--r--sql/item.h16
-rw-r--r--sql/item_func.h14
-rw-r--r--sql/item_timefunc.h4
-rw-r--r--sql/sql_type.cc100
-rw-r--r--sql/sql_type.h12
10 files changed, 367 insertions, 4 deletions
diff --git a/mysql-test/main/type_time.result b/mysql-test/main/type_time.result
index 8ef8e981f57..ead494e3ace 100644
--- a/mysql-test/main/type_time.result
+++ b/mysql-test/main/type_time.result
@@ -2286,5 +2286,127 @@ SELECT '1972-11-06 16:58:58' < TIME'20:31:05';
'1972-11-06 16:58:58' < TIME'20:31:05'
1
#
+# MDEV-23525 Wrong result of MIN(time_expr) and MAX(time_expr) with GROUP BY
+#
+SET timestamp=UNIX_TIMESTAMP('2020-08-21 18:19:20');
+CREATE PROCEDURE p1()
+BEGIN
+SELECT MIN(t), MAX(t) FROM t1;
+SELECT i, MIN(t), MAX(t) FROM t1 GROUP BY i;
+SELECT i, MIN(COALESCE(t)), MAX(COALESCE(t)) FROM t1 GROUP BY i;
+SELECT i, MIN(t+INTERVAL 1 SECOND), MAX(t+INTERVAL 1 SECOND) FROM t1 GROUP BY i;
+SELECT i, MIN(TIME'10:20:30'+INTERVAL 1 SECOND) FROM t1 GROUP BY i;
+SELECT i, MIN(CURRENT_TIME), MAX(CURRENT_TIME) FROM t1 GROUP BY i;
+SELECT
+i,
+MIN((SELECT MAX(CURRENT_TIME) FROM t1)),
+MAX((SELECT MAX(CURRENT_TIME) FROM t1))
+FROM t1 GROUP BY i;
+SELECT
+i,
+MIN(NAME_CONST('name',TIME'10:20:30')),
+MAX(NAME_CONST('name',TIME'10:20:30'))
+FROM t1 GROUP BY i;
+EXECUTE IMMEDIATE "SELECT i, MIN(?),MAX(?) FROM t1 GROUP BY i"
+ USING TIME'10:20:30', TIME'10:20:30';
+END;
+$$
+CREATE TABLE t1 (i INT, t TIME);
+INSERT INTO t1 VALUES (1,'10:20:30');
+INSERT INTO t1 VALUES (1,'100:20:20');
+CALL p1;
+MIN(t) MAX(t)
+10:20:30 100:20:20
+i MIN(t) MAX(t)
+1 10:20:30 100:20:20
+i MIN(COALESCE(t)) MAX(COALESCE(t))
+1 10:20:30 100:20:20
+i MIN(t+INTERVAL 1 SECOND) MAX(t+INTERVAL 1 SECOND)
+1 10:20:31 100:20:21
+i MIN(TIME'10:20:30'+INTERVAL 1 SECOND)
+1 10:20:31
+i MIN(CURRENT_TIME) MAX(CURRENT_TIME)
+1 18:19:20 18:19:20
+i MIN((SELECT MAX(CURRENT_TIME) FROM t1)) MAX((SELECT MAX(CURRENT_TIME) FROM t1))
+1 18:19:20 18:19:20
+i MIN(NAME_CONST('name',TIME'10:20:30')) MAX(NAME_CONST('name',TIME'10:20:30'))
+1 10:20:30 10:20:30
+i MIN(?) MAX(?)
+1 10:20:30 10:20:30
+DROP TABLE t1;
+CREATE TABLE t1 (i INT, t TIME(3));
+INSERT INTO t1 VALUES (1,'10:20:30.123');
+INSERT INTO t1 VALUES (1,'100:20:20.123');
+CALL p1;
+MIN(t) MAX(t)
+10:20:30.123 100:20:20.123
+i MIN(t) MAX(t)
+1 10:20:30.123 100:20:20.123
+i MIN(COALESCE(t)) MAX(COALESCE(t))
+1 10:20:30.123 100:20:20.123
+i MIN(t+INTERVAL 1 SECOND) MAX(t+INTERVAL 1 SECOND)
+1 10:20:31.123 100:20:21.123
+i MIN(TIME'10:20:30'+INTERVAL 1 SECOND)
+1 10:20:31
+i MIN(CURRENT_TIME) MAX(CURRENT_TIME)
+1 18:19:20 18:19:20
+i MIN((SELECT MAX(CURRENT_TIME) FROM t1)) MAX((SELECT MAX(CURRENT_TIME) FROM t1))
+1 18:19:20 18:19:20
+i MIN(NAME_CONST('name',TIME'10:20:30')) MAX(NAME_CONST('name',TIME'10:20:30'))
+1 10:20:30 10:20:30
+i MIN(?) MAX(?)
+1 10:20:30 10:20:30
+DROP TABLE t1;
+CREATE TABLE t1 (i INT, t TIME(6));
+INSERT INTO t1 VALUES (1,'10:20:30.123456');
+INSERT INTO t1 VALUES (1,'100:20:20.123456');
+CALL p1;
+MIN(t) MAX(t)
+10:20:30.123456 100:20:20.123456
+i MIN(t) MAX(t)
+1 10:20:30.123456 100:20:20.123456
+i MIN(COALESCE(t)) MAX(COALESCE(t))
+1 10:20:30.123456 100:20:20.123456
+i MIN(t+INTERVAL 1 SECOND) MAX(t+INTERVAL 1 SECOND)
+1 10:20:31.123456 100:20:21.123456
+i MIN(TIME'10:20:30'+INTERVAL 1 SECOND)
+1 10:20:31
+i MIN(CURRENT_TIME) MAX(CURRENT_TIME)
+1 18:19:20 18:19:20
+i MIN((SELECT MAX(CURRENT_TIME) FROM t1)) MAX((SELECT MAX(CURRENT_TIME) FROM t1))
+1 18:19:20 18:19:20
+i MIN(NAME_CONST('name',TIME'10:20:30')) MAX(NAME_CONST('name',TIME'10:20:30'))
+1 10:20:30 10:20:30
+i MIN(?) MAX(?)
+1 10:20:30 10:20:30
+DROP TABLE t1;
+SET @@global.mysql56_temporal_format=false;
+CREATE TABLE t1 (i INT, t TIME(6));
+INSERT INTO t1 VALUES (1,'10:20:30.123456');
+INSERT INTO t1 VALUES (1,'100:20:20.123456');
+CALL p1;
+MIN(t) MAX(t)
+10:20:30.123456 100:20:20.123456
+i MIN(t) MAX(t)
+1 10:20:30.123456 100:20:20.123456
+i MIN(COALESCE(t)) MAX(COALESCE(t))
+1 10:20:30.123456 100:20:20.123456
+i MIN(t+INTERVAL 1 SECOND) MAX(t+INTERVAL 1 SECOND)
+1 10:20:31.123456 100:20:21.123456
+i MIN(TIME'10:20:30'+INTERVAL 1 SECOND)
+1 10:20:31
+i MIN(CURRENT_TIME) MAX(CURRENT_TIME)
+1 18:19:20 18:19:20
+i MIN((SELECT MAX(CURRENT_TIME) FROM t1)) MAX((SELECT MAX(CURRENT_TIME) FROM t1))
+1 18:19:20 18:19:20
+i MIN(NAME_CONST('name',TIME'10:20:30')) MAX(NAME_CONST('name',TIME'10:20:30'))
+1 10:20:30 10:20:30
+i MIN(?) MAX(?)
+1 10:20:30 10:20:30
+DROP TABLE t1;
+SET @@global.mysql56_temporal_format=default;
+DROP PROCEDURE p1;
+SET timestamp=DEFAULT;
+#
# End of 10.4 tests
#
diff --git a/mysql-test/main/type_time.test b/mysql-test/main/type_time.test
index 521953a5078..1d4d157b976 100644
--- a/mysql-test/main/type_time.test
+++ b/mysql-test/main/type_time.test
@@ -1491,5 +1491,65 @@ DROP TABLE t1;
SELECT '1972-11-06 16:58:58' < TIME'20:31:05';
--echo #
+--echo # MDEV-23525 Wrong result of MIN(time_expr) and MAX(time_expr) with GROUP BY
+--echo #
+
+SET timestamp=UNIX_TIMESTAMP('2020-08-21 18:19:20');
+
+DELIMITER $$;
+CREATE PROCEDURE p1()
+BEGIN
+ SELECT MIN(t), MAX(t) FROM t1;
+ SELECT i, MIN(t), MAX(t) FROM t1 GROUP BY i;
+ SELECT i, MIN(COALESCE(t)), MAX(COALESCE(t)) FROM t1 GROUP BY i;
+ SELECT i, MIN(t+INTERVAL 1 SECOND), MAX(t+INTERVAL 1 SECOND) FROM t1 GROUP BY i;
+ SELECT i, MIN(TIME'10:20:30'+INTERVAL 1 SECOND) FROM t1 GROUP BY i;
+ SELECT i, MIN(CURRENT_TIME), MAX(CURRENT_TIME) FROM t1 GROUP BY i;
+ SELECT
+ i,
+ MIN((SELECT MAX(CURRENT_TIME) FROM t1)),
+ MAX((SELECT MAX(CURRENT_TIME) FROM t1))
+ FROM t1 GROUP BY i;
+ SELECT
+ i,
+ MIN(NAME_CONST('name',TIME'10:20:30')),
+ MAX(NAME_CONST('name',TIME'10:20:30'))
+ FROM t1 GROUP BY i;
+ EXECUTE IMMEDIATE "SELECT i, MIN(?),MAX(?) FROM t1 GROUP BY i"
+ USING TIME'10:20:30', TIME'10:20:30';
+END;
+$$
+DELIMITER ;$$
+
+CREATE TABLE t1 (i INT, t TIME);
+INSERT INTO t1 VALUES (1,'10:20:30');
+INSERT INTO t1 VALUES (1,'100:20:20');
+CALL p1;
+DROP TABLE t1;
+
+CREATE TABLE t1 (i INT, t TIME(3));
+INSERT INTO t1 VALUES (1,'10:20:30.123');
+INSERT INTO t1 VALUES (1,'100:20:20.123');
+CALL p1;
+DROP TABLE t1;
+
+CREATE TABLE t1 (i INT, t TIME(6));
+INSERT INTO t1 VALUES (1,'10:20:30.123456');
+INSERT INTO t1 VALUES (1,'100:20:20.123456');
+CALL p1;
+DROP TABLE t1;
+
+SET @@global.mysql56_temporal_format=false;
+CREATE TABLE t1 (i INT, t TIME(6));
+INSERT INTO t1 VALUES (1,'10:20:30.123456');
+INSERT INTO t1 VALUES (1,'100:20:20.123456');
+CALL p1;
+DROP TABLE t1;
+SET @@global.mysql56_temporal_format=default;
+
+DROP PROCEDURE p1;
+SET timestamp=DEFAULT;
+
+--echo #
--echo # End of 10.4 tests
--echo #
diff --git a/sql/field.cc b/sql/field.cc
index d20c2c3af5c..2fc943f1eb7 100644
--- a/sql/field.cc
+++ b/sql/field.cc
@@ -6085,6 +6085,24 @@ bool Field_time0::get_date(MYSQL_TIME *ltime, date_mode_t fuzzydate)
}
+int Field_time::store_native(const Native &value)
+{
+ Time t(value);
+ DBUG_ASSERT(t.is_valid_time());
+ store_TIME(t);
+ return 0;
+}
+
+
+bool Field_time::val_native(Native *to)
+{
+ MYSQL_TIME ltime;
+ get_date(&ltime, date_mode_t(0));
+ int warn;
+ return Time(&warn, &ltime, 0).to_native(to, decimals());
+}
+
+
bool Field_time::send(Protocol *protocol)
{
MYSQL_TIME ltime;
@@ -6321,6 +6339,22 @@ Binlog_type_info Field_timef::binlog_type_info() const
return Binlog_type_info(Field_timef::binlog_type(), decimals(), 1);
}
+int Field_timef::store_native(const Native &value)
+{
+ DBUG_ASSERT(value.length() == my_time_binary_length(dec));
+ DBUG_ASSERT(Time(value).is_valid_time());
+ memcpy(ptr, value.ptr(), value.length());
+ return 0;
+}
+
+
+bool Field_timef::val_native(Native *to)
+{
+ uint32 binlen= my_time_binary_length(dec);
+ return to->copy((const char*) ptr, binlen);
+}
+
+
/****************************************************************************
** year type
** Save in a byte the year 0, 1901->2155
diff --git a/sql/field.h b/sql/field.h
index 2c73fed708b..178ed4b42a0 100644
--- a/sql/field.h
+++ b/sql/field.h
@@ -3560,6 +3560,8 @@ public:
decimals() == from->decimals();
}
sql_mode_t conversion_depends_on_sql_mode(THD *, Item *) const override;
+ int store_native(const Native &value) override;
+ bool val_native(Native *to) override;
int store_time_dec(const MYSQL_TIME *ltime, uint dec) override;
int store(const char *to,size_t length,CHARSET_INFO *charset) override;
int store(double nr) override;
@@ -3711,6 +3713,8 @@ public:
}
int reset() override;
bool get_date(MYSQL_TIME *ltime, date_mode_t fuzzydate) override;
+ int store_native(const Native &value) override;
+ bool val_native(Native *to) override;
uint size_of() const override { return sizeof *this; }
Binlog_type_info binlog_type_info() const override;
};
diff --git a/sql/item.cc b/sql/item.cc
index b164b945abf..9929002200a 100644
--- a/sql/item.cc
+++ b/sql/item.cc
@@ -2064,6 +2064,11 @@ bool Item_name_const::get_date(THD *thd, MYSQL_TIME *ltime, date_mode_t fuzzydat
return rc;
}
+bool Item_name_const::val_native(THD *thd, Native *to)
+{
+ return val_native_from_item(thd, value_item, to);
+}
+
bool Item_name_const::is_null()
{
return value_item->is_null();
diff --git a/sql/item.h b/sql/item.h
index c4fbf8f9c0a..e66d815d484 100644
--- a/sql/item.h
+++ b/sql/item.h
@@ -1333,16 +1333,13 @@ public:
{
/*
The default implementation for the Items that do not need native format:
- - Item_basic_value
+ - Item_basic_value (default implementation)
- Item_copy
- Item_exists_subselect
- Item_sum_field
- Item_sum_or_func (default implementation)
- Item_proc
- Item_type_holder (as val_xxx() are never called for it);
- - TODO: Item_name_const will need val_native() in the future,
- when we add this syntax:
- TIMESTAMP WITH LOCAL TIMEZONE'2001-01-01 00:00:00'
These hybrid Item types override val_native():
- Item_field
@@ -1353,6 +1350,8 @@ public:
- Item_direct_ref
- Item_direct_view_ref
- Item_ref_null_helper
+ - Item_name_const
+ - Item_time_literal
- Item_sum_or_func
Note, these hybrid type Item_sum_or_func descendants
override the default implementation:
@@ -3173,6 +3172,7 @@ public:
String *val_str(String *sp);
my_decimal *val_decimal(my_decimal *);
bool get_date(THD *thd, MYSQL_TIME *ltime, date_mode_t fuzzydate);
+ bool val_native(THD *thd, Native *to);
bool is_null();
virtual void print(String *str, enum_query_type query_type);
@@ -4897,6 +4897,10 @@ public:
String *val_str(String *to) { return Time(this).to_string(to, decimals); }
my_decimal *val_decimal(my_decimal *to) { return Time(this).to_decimal(to); }
bool get_date(THD *thd, MYSQL_TIME *res, date_mode_t fuzzydate);
+ bool val_native(THD *thd, Native *to)
+ {
+ return Time(thd, this).to_native(to, decimals);
+ }
Item *get_copy(THD *thd)
{ return get_item_copy<Item_time_literal>(thd, this); }
};
@@ -6874,6 +6878,10 @@ public:
{
return has_value() ? Time(this).to_decimal(to) : NULL;
}
+ bool val_native(THD *thd, Native *to)
+ {
+ return has_value() ? Time(thd, this).to_native(to, decimals) : true;
+ }
};
diff --git a/sql/item_func.h b/sql/item_func.h
index 5b4acdce1c6..6a4a9fa5dae 100644
--- a/sql/item_func.h
+++ b/sql/item_func.h
@@ -485,6 +485,12 @@ public:
virtual longlong val_int(Item_handled_func *) const= 0;
virtual my_decimal *val_decimal(Item_handled_func *, my_decimal *) const= 0;
virtual bool get_date(THD *thd, Item_handled_func *, MYSQL_TIME *, date_mode_t fuzzydate) const= 0;
+ virtual bool val_native(THD *thd, Item_handled_func *, Native *to) const
+ {
+ DBUG_ASSERT(0);
+ to->length(0);
+ return true;
+ }
virtual const Type_handler *
return_type_handler(const Item_handled_func *item) const= 0;
virtual const Type_handler *
@@ -631,6 +637,10 @@ public:
{
return Time(item).to_string(to, item->decimals);
}
+ bool val_native(THD *thd, Item_handled_func *item, Native *to) const
+ {
+ return Time(thd, item).to_native(to, item->decimals);
+ }
};
@@ -788,6 +798,10 @@ public:
{
return m_func_handler->get_date(thd, this, to, fuzzydate);
}
+ bool val_native(THD *thd, Native *to)
+ {
+ return m_func_handler->val_native(thd, this, to);
+ }
};
diff --git a/sql/item_timefunc.h b/sql/item_timefunc.h
index c9a493f8efc..35c9df533c4 100644
--- a/sql/item_timefunc.h
+++ b/sql/item_timefunc.h
@@ -598,6 +598,10 @@ public:
double val_real() { return Time(this).to_double(); }
String *val_str(String *to) { return Time(this).to_string(to, decimals); }
my_decimal *val_decimal(my_decimal *to) { return Time(this).to_decimal(to); }
+ bool val_native(THD *thd, Native *to)
+ {
+ return Time(thd, this).to_native(to, decimals);
+ }
};
diff --git a/sql/sql_type.cc b/sql/sql_type.cc
index adceb5013c8..fdd92d3d7bb 100644
--- a/sql/sql_type.cc
+++ b/sql/sql_type.cc
@@ -734,6 +734,23 @@ uint Interval_DDhhmmssff::fsp(THD *thd, Item *item)
}
+bool Time::to_native(Native *to, uint decimals) const
+{
+ if (!is_valid_time())
+ {
+ to->length(0);
+ return true;
+ }
+ uint len= my_time_binary_length(decimals);
+ if (to->reserve(len))
+ return true;
+ longlong tmp= TIME_to_longlong_time_packed(get_mysql_time());
+ my_time_packed_to_binary(tmp, (uchar*) to->ptr(), decimals);
+ to->length(len);
+ return false;
+}
+
+
void Time::make_from_item(THD *thd, int *warn, Item *item, const Options opt)
{
*warn= 0;
@@ -918,6 +935,28 @@ void Time::make_from_time(int *warn, const MYSQL_TIME *from)
}
+uint Time::binary_length_to_precision(uint length)
+{
+ switch (length) {
+ case 3: return 0;
+ case 4: return 2;
+ case 5: return 4;
+ case 6: return 6;
+ }
+ DBUG_ASSERT(0);
+ return 0;
+}
+
+
+Time::Time(const Native &native)
+{
+ uint dec= binary_length_to_precision(native.length());
+ longlong tmp= my_time_packed_from_binary((const uchar *) native.ptr(), dec);
+ TIME_from_longlong_time_packed(this, tmp);
+ DBUG_ASSERT(is_valid_time());
+}
+
+
Time::Time(int *warn, const MYSQL_TIME *from, long curdays)
{
switch (from->time_type) {
@@ -1685,6 +1724,13 @@ Type_handler_timestamp_common::type_handler_for_native_format() const
}
+const Type_handler *
+Type_handler_time_common::type_handler_for_native_format() const
+{
+ return &type_handler_time2;
+}
+
+
/***************************************************************************/
const Type_handler *Type_handler_typelib::type_handler_for_item_field() const
@@ -8820,6 +8866,51 @@ Type_handler_time_common::create_literal_item(THD *thd,
}
+bool
+Type_handler_time_common::Item_val_native_with_conversion(THD *thd,
+ Item *item,
+ Native *to) const
+{
+ if (item->type_handler()->type_handler_for_native_format() ==
+ &type_handler_time2)
+ return item->val_native(thd, to);
+ return Time(thd, item).to_native(to, item->time_precision(thd));
+}
+
+
+bool
+Type_handler_time_common::Item_val_native_with_conversion_result(THD *thd,
+ Item *item,
+ Native *to)
+ const
+{
+ if (item->type_handler()->type_handler_for_native_format() ==
+ &type_handler_time2)
+ return item->val_native_result(thd, to);
+ MYSQL_TIME ltime;
+ if (item->get_date_result(thd, &ltime, Time::Options(thd)))
+ return true;
+ int warn;
+ return Time(&warn, &ltime, 0).to_native(to, item->time_precision(thd));
+}
+
+
+int Type_handler_time_common::cmp_native(const Native &a,
+ const Native &b) const
+{
+ // Optimize a simple case: equal fractional precision:
+ if (a.length() == b.length())
+ return memcmp(a.ptr(), b.ptr(), a.length());
+ longlong lla= Time(a).to_packed();
+ longlong llb= Time(b).to_packed();
+ if (lla < llb)
+ return -1;
+ if (lla> llb)
+ return 1;
+ return 0;
+}
+
+
bool Type_handler_timestamp_common::TIME_to_native(THD *thd,
const MYSQL_TIME *ltime,
Native *to,
@@ -8930,6 +9021,15 @@ Type_handler_timestamp_common::Item_param_val_native(THD *thd,
}
+bool
+Type_handler_time_common::Item_param_val_native(THD *thd,
+ Item_param *item,
+ Native *to) const
+{
+ return Time(thd, item).to_native(to, item->decimals);
+}
+
+
/***************************************************************************/
bool Type_handler::validate_implicit_default_value(THD *thd,
diff --git a/sql/sql_type.h b/sql/sql_type.h
index a6d85b5bb47..b87b7df1220 100644
--- a/sql/sql_type.h
+++ b/sql/sql_type.h
@@ -1453,6 +1453,7 @@ class Schema;
*/
class Time: public Temporal
{
+ static uint binary_length_to_precision(uint length);
public:
enum datetime_to_time_mode_t
{
@@ -1685,6 +1686,7 @@ public:
*/
Time(int *warn, bool neg, ulonglong hour, uint minute, const Sec6 &second);
Time() { time_type= MYSQL_TIMESTAMP_NONE; }
+ Time(const Native &native);
Time(Item *item)
:Time(current_thd, item)
{ }
@@ -1840,6 +1842,7 @@ public:
return !is_valid_time() ? 0 :
Temporal::to_double(neg, TIME_to_ulonglong_time(this), second_part);
}
+ bool to_native(Native *to, uint decimals) const;
String *to_string(String *str, uint dec) const
{
if (!is_valid_time())
@@ -5897,6 +5900,15 @@ public:
{
return MYSQL_TIMESTAMP_TIME;
}
+ bool is_val_native_ready() const override { return true; }
+ const Type_handler *type_handler_for_native_format() const override;
+ int cmp_native(const Native &a, const Native &b) const override;
+ bool Item_val_native_with_conversion(THD *thd, Item *, Native *to)
+ const override;
+ bool Item_val_native_with_conversion_result(THD *thd, Item *, Native *to)
+ const override;
+ bool Item_param_val_native(THD *thd, Item_param *item, Native *to)
+ const override;
bool partition_field_check(const LEX_CSTRING &field_name,
Item *item_expr) const override
{
View Lorry Controller Status