summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--mysql-test/r/func_time.result8
-rw-r--r--mysql-test/r/type_time.result107
-rw-r--r--mysql-test/t/type_time.test68
-rw-r--r--sql/item.cc69
-rw-r--r--sql/item.h6
-rw-r--r--sql/item_cmpfunc.cc7
-rw-r--r--sql/item_func.cc2
-rw-r--r--sql/item_sum.cc2
-rw-r--r--sql/item_timefunc.cc21
-rw-r--r--sql/item_timefunc.h4
10 files changed, 264 insertions, 30 deletions
diff --git a/mysql-test/r/func_time.result b/mysql-test/r/func_time.result
index fc070f6abf9..e8d2e0056e9 100644
--- a/mysql-test/r/func_time.result
+++ b/mysql-test/r/func_time.result
@@ -948,10 +948,10 @@ sec_to_time(1) + 0, from_unixtime(1) + 0;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
- `now() - now()` decimal(20,0) NOT NULL DEFAULT '0',
- `curtime() - curtime()` decimal(11,0) NOT NULL DEFAULT '0',
- `sec_to_time(1) + 0` decimal(11,0) DEFAULT NULL,
- `from_unixtime(1) + 0` decimal(20,0) DEFAULT NULL
+ `now() - now()` bigint(21) NOT NULL DEFAULT '0',
+ `curtime() - curtime()` bigint(12) NOT NULL DEFAULT '0',
+ `sec_to_time(1) + 0` bigint(12) DEFAULT NULL,
+ `from_unixtime(1) + 0` bigint(21) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
SELECT SEC_TO_TIME(3300000);
diff --git a/mysql-test/r/type_time.result b/mysql-test/r/type_time.result
index 32bbb6baa0b..bdbfba84994 100644
--- a/mysql-test/r/type_time.result
+++ b/mysql-test/r/type_time.result
@@ -192,6 +192,113 @@ SELECT CONCAT(GREATEST(TIME('32 00:00:01'),TIME('00:00:00')));
CONCAT(GREATEST(TIME('32 00:00:01'),TIME('00:00:00')))
768:00:01
#
+# MDEV-4859 Wrong value and data type of "SELECT MAX(time_column) + 1 FROM t1"
+#
+CREATE TABLE t1 (t0 TIME);
+INSERT INTO t1 VALUES ('10:10:10');
+SELECT MAX(t0)+1 FROM t1;
+MAX(t0)+1
+101011
+CREATE TABLE t2 AS SELECT MAX(t0)+1 FROM t1;
+SELECT * FROM t2;
+MAX(t0)+1
+101011
+SHOW COLUMNS FROM t2;
+Field Type Null Key Default Extra
+MAX(t0)+1 bigint(12) YES NULL
+DROP TABLE t2,t1;
+CREATE TABLE t1 (t0 TIME);
+INSERT INTO t1 VALUES ('10:10:10');
+SELECT MAX(t0)+1.1 FROM t1;
+MAX(t0)+1.1
+101011.1
+CREATE TABLE t2 AS SELECT MAX(t0)+1.1 FROM t1;
+SELECT * FROM t2;
+MAX(t0)+1.1
+101011.1
+SHOW COLUMNS FROM t2;
+Field Type Null Key Default Extra
+MAX(t0)+1.1 decimal(12,1) YES NULL
+DROP TABLE t2,t1;
+CREATE TABLE t1 (t0 TIME);
+INSERT INTO t1 VALUES ('10:10:10');
+SELECT MAX(t0)+1e0 FROM t1;
+MAX(t0)+1e0
+101011
+CREATE TABLE t2 AS SELECT MAX(t0)+1e0 FROM t1;
+SELECT * FROM t2;
+MAX(t0)+1e0
+101011
+SHOW COLUMNS FROM t2;
+Field Type Null Key Default Extra
+MAX(t0)+1e0 double YES NULL
+DROP TABLE t2,t1;
+CREATE TABLE t1 (t1 TIME(1));
+INSERT INTO t1 VALUES ('10:10:10');
+SELECT MAX(t1)+1 FROM t1;
+MAX(t1)+1
+101011.0
+CREATE TABLE t2 AS SELECT MAX(t1)+1 FROM t1;
+SELECT * FROM t2;
+MAX(t1)+1
+101011.0
+SHOW COLUMNS FROM t2;
+Field Type Null Key Default Extra
+MAX(t1)+1 decimal(13,1) YES NULL
+DROP TABLE t2,t1;
+CREATE TABLE t1 (t0 DATETIME);
+INSERT INTO t1 VALUES ('2001-01-01 10:10:10');
+SELECT MAX(t0)+1 FROM t1;
+MAX(t0)+1
+20010101101011
+CREATE TABLE t2 AS SELECT MAX(t0)+1 FROM t1;
+SELECT * FROM t2;
+MAX(t0)+1
+20010101101011
+SHOW COLUMNS FROM t2;
+Field Type Null Key Default Extra
+MAX(t0)+1 bigint(21) YES NULL
+DROP TABLE t2,t1;
+CREATE TABLE t1 (t0 DATETIME);
+INSERT INTO t1 VALUES ('2001-01-01 10:10:10');
+SELECT MAX(t0)+1.1 FROM t1;
+MAX(t0)+1.1
+20010101101011.1
+CREATE TABLE t2 AS SELECT MAX(t0)+1.1 FROM t1;
+SELECT * FROM t2;
+MAX(t0)+1.1
+20010101101011.1
+SHOW COLUMNS FROM t2;
+Field Type Null Key Default Extra
+MAX(t0)+1.1 decimal(21,1) YES NULL
+DROP TABLE t2,t1;
+CREATE TABLE t1 (t0 DATETIME);
+INSERT INTO t1 VALUES ('2001-01-01 10:10:10');
+SELECT MAX(t0)+1e0 FROM t1;
+MAX(t0)+1e0
+20010101101011
+CREATE TABLE t2 AS SELECT MAX(t0)+1e0 FROM t1;
+SELECT * FROM t2;
+MAX(t0)+1e0
+20010101101011
+SHOW COLUMNS FROM t2;
+Field Type Null Key Default Extra
+MAX(t0)+1e0 double YES NULL
+DROP TABLE t2,t1;
+CREATE TABLE t1 (t1 DATETIME(1));
+INSERT INTO t1 VALUES ('2001-01-01 10:10:10');
+SELECT MAX(t1)+1 FROM t1;
+MAX(t1)+1
+20010101101011.0
+CREATE TABLE t2 AS SELECT MAX(t1)+1 FROM t1;
+SELECT * FROM t2;
+MAX(t1)+1
+20010101101011.0
+SHOW COLUMNS FROM t2;
+Field Type Null Key Default Extra
+MAX(t1)+1 decimal(22,1) YES NULL
+DROP TABLE t2,t1;
+#
# End of 5.3 tests
#
CREATE TABLE t1 (f1 TIME);
diff --git a/mysql-test/t/type_time.test b/mysql-test/t/type_time.test
index 1dd0f4363b9..9ecd5d22a81 100644
--- a/mysql-test/t/type_time.test
+++ b/mysql-test/t/type_time.test
@@ -135,6 +135,74 @@ SELECT CONCAT(GREATEST(TIME('00:00:01'),TIME('00:00:00')));
SELECT CONCAT(GREATEST(TIME('32 00:00:01'),TIME('00:00:00')));
--echo #
+--echo # MDEV-4859 Wrong value and data type of "SELECT MAX(time_column) + 1 FROM t1"
+--echo #
+CREATE TABLE t1 (t0 TIME);
+INSERT INTO t1 VALUES ('10:10:10');
+SELECT MAX(t0)+1 FROM t1;
+CREATE TABLE t2 AS SELECT MAX(t0)+1 FROM t1;
+SELECT * FROM t2;
+SHOW COLUMNS FROM t2;
+DROP TABLE t2,t1;
+
+CREATE TABLE t1 (t0 TIME);
+INSERT INTO t1 VALUES ('10:10:10');
+SELECT MAX(t0)+1.1 FROM t1;
+CREATE TABLE t2 AS SELECT MAX(t0)+1.1 FROM t1;
+SELECT * FROM t2;
+SHOW COLUMNS FROM t2;
+DROP TABLE t2,t1;
+
+CREATE TABLE t1 (t0 TIME);
+INSERT INTO t1 VALUES ('10:10:10');
+SELECT MAX(t0)+1e0 FROM t1;
+CREATE TABLE t2 AS SELECT MAX(t0)+1e0 FROM t1;
+SELECT * FROM t2;
+SHOW COLUMNS FROM t2;
+DROP TABLE t2,t1;
+
+CREATE TABLE t1 (t1 TIME(1));
+INSERT INTO t1 VALUES ('10:10:10');
+SELECT MAX(t1)+1 FROM t1;
+CREATE TABLE t2 AS SELECT MAX(t1)+1 FROM t1;
+SELECT * FROM t2;
+SHOW COLUMNS FROM t2;
+DROP TABLE t2,t1;
+
+CREATE TABLE t1 (t0 DATETIME);
+INSERT INTO t1 VALUES ('2001-01-01 10:10:10');
+SELECT MAX(t0)+1 FROM t1;
+CREATE TABLE t2 AS SELECT MAX(t0)+1 FROM t1;
+SELECT * FROM t2;
+SHOW COLUMNS FROM t2;
+DROP TABLE t2,t1;
+
+CREATE TABLE t1 (t0 DATETIME);
+INSERT INTO t1 VALUES ('2001-01-01 10:10:10');
+SELECT MAX(t0)+1.1 FROM t1;
+CREATE TABLE t2 AS SELECT MAX(t0)+1.1 FROM t1;
+SELECT * FROM t2;
+SHOW COLUMNS FROM t2;
+DROP TABLE t2,t1;
+
+CREATE TABLE t1 (t0 DATETIME);
+INSERT INTO t1 VALUES ('2001-01-01 10:10:10');
+SELECT MAX(t0)+1e0 FROM t1;
+CREATE TABLE t2 AS SELECT MAX(t0)+1e0 FROM t1;
+SELECT * FROM t2;
+SHOW COLUMNS FROM t2;
+DROP TABLE t2,t1;
+
+CREATE TABLE t1 (t1 DATETIME(1));
+INSERT INTO t1 VALUES ('2001-01-01 10:10:10');
+SELECT MAX(t1)+1 FROM t1;
+CREATE TABLE t2 AS SELECT MAX(t1)+1 FROM t1;
+SELECT * FROM t2;
+SHOW COLUMNS FROM t2;
+DROP TABLE t2,t1;
+
+
+--echo #
--echo # End of 5.3 tests
--echo #
diff --git a/sql/item.cc b/sql/item.cc
index 2bf149c58ba..bfe065ceced 100644
--- a/sql/item.cc
+++ b/sql/item.cc
@@ -396,6 +396,27 @@ my_decimal *Item::val_decimal_from_time(my_decimal *decimal_value)
}
+longlong Item::val_int_from_date()
+{
+ DBUG_ASSERT(fixed == 1);
+ MYSQL_TIME ltime;
+ if (get_date(&ltime, 0))
+ return 0;
+ longlong v= TIME_to_ulonglong(&ltime);
+ return ltime.neg ? -v : v;
+}
+
+
+double Item::val_real_from_date()
+{
+ DBUG_ASSERT(fixed == 1);
+ MYSQL_TIME ltime;
+ if (get_date(&ltime, 0))
+ return 0;
+ return TIME_to_double(&ltime);
+}
+
+
double Item::val_real_from_decimal()
{
/* Note that fix_fields may not be called for Item_avg_field items */
@@ -8831,6 +8852,18 @@ Item_cache_temporal::Item_cache_temporal(enum_field_types field_type_arg):
}
+longlong Item_cache_temporal::val_temporal_packed()
+{
+ DBUG_ASSERT(fixed == 1);
+ if ((!value_cached && !cache_value()) || null_value)
+ {
+ null_value= TRUE;
+ return 0;
+ }
+ return value;
+}
+
+
String *Item_cache_temporal::val_str(String *str)
{
DBUG_ASSERT(fixed == 1);
@@ -8843,6 +8876,42 @@ String *Item_cache_temporal::val_str(String *str)
}
+my_decimal *Item_cache_temporal::val_decimal(my_decimal *decimal_value)
+{
+ DBUG_ASSERT(fixed == 1);
+ if ((!value_cached && !cache_value()) || null_value)
+ {
+ null_value= true;
+ return NULL;
+ }
+ return val_decimal_from_date(decimal_value);
+}
+
+
+longlong Item_cache_temporal::val_int()
+{
+ DBUG_ASSERT(fixed == 1);
+ if ((!value_cached && !cache_value()) || null_value)
+ {
+ null_value= true;
+ return 0;
+ }
+ return val_int_from_date();
+}
+
+
+double Item_cache_temporal::val_real()
+{
+ DBUG_ASSERT(fixed == 1);
+ if ((!value_cached && !cache_value()) || null_value)
+ {
+ null_value= true;
+ return 0;
+ }
+ return val_real_from_date();
+}
+
+
bool Item_cache_temporal::cache_value()
{
if (!example)
diff --git a/sql/item.h b/sql/item.h
index 27d42eb4389..8074f11da6b 100644
--- a/sql/item.h
+++ b/sql/item.h
@@ -950,7 +950,9 @@ public:
my_decimal *val_decimal_from_date(my_decimal *decimal_value);
my_decimal *val_decimal_from_time(my_decimal *decimal_value);
longlong val_int_from_decimal();
+ longlong val_int_from_date();
double val_real_from_decimal();
+ double val_real_from_date();
int save_time_in_field(Field *field);
int save_date_in_field(Field *field);
@@ -4128,6 +4130,10 @@ class Item_cache_temporal: public Item_cache_int
public:
Item_cache_temporal(enum_field_types field_type_arg);
String* val_str(String *str);
+ my_decimal *val_decimal(my_decimal *);
+ longlong val_int();
+ longlong val_temporal_packed();
+ double val_real();
bool cache_value();
bool get_date(MYSQL_TIME *ltime, ulonglong fuzzydate);
int save_in_field(Field *field, bool no_conversions);
diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc
index 22049ac26b4..7a07299bf2c 100644
--- a/sql/item_cmpfunc.cc
+++ b/sql/item_cmpfunc.cc
@@ -888,10 +888,13 @@ get_datetime_value(THD *thd, Item ***item_arg, Item **cache_arg,
enum_field_types f_type= item->cmp_type() == TIME_RESULT ?
item->field_type() : warn_item->field_type();
- if (item->result_type() == INT_RESULT && item->cmp_type() == TIME_RESULT)
+ if (item->result_type() == INT_RESULT &&
+ item->cmp_type() == TIME_RESULT &&
+ item->type() == Item::CACHE_ITEM)
{
/* it's our Item_cache_temporal, as created below */
- value= item->val_int();
+ DBUG_ASSERT(is_temporal_type(((Item_cache *) item)->field_type()));
+ value= ((Item_cache_temporal*) item)->val_temporal_packed();
}
else
{
diff --git a/sql/item_func.cc b/sql/item_func.cc
index 85c3a66d68b..fe746a1cadb 100644
--- a/sql/item_func.cc
+++ b/sql/item_func.cc
@@ -826,6 +826,8 @@ void Item_num_op::find_num_type(void)
cached_result_type= DECIMAL_RESULT;
result_precision();
fix_decimals();
+ if ((r0 == TIME_RESULT || r1 == TIME_RESULT) && decimals == 0)
+ cached_result_type= INT_RESULT;
}
else
{
diff --git a/sql/item_sum.cc b/sql/item_sum.cc
index 7c2eea6258b..092531c1c9e 100644
--- a/sql/item_sum.cc
+++ b/sql/item_sum.cc
@@ -1250,7 +1250,7 @@ Item_sum_hybrid::fix_fields(THD *thd, Item **ref)
void Item_sum_hybrid::setup_hybrid(Item *item, Item *value_arg)
{
- if (!(value= Item_cache::get_cache(item)))
+ if (!(value= Item_cache::get_cache(item, item->cmp_type())))
return;
value->setup(item);
value->store(value_arg);
diff --git a/sql/item_timefunc.cc b/sql/item_timefunc.cc
index 40068bc49c0..ab536e58f1c 100644
--- a/sql/item_timefunc.cc
+++ b/sql/item_timefunc.cc
@@ -1475,27 +1475,6 @@ String *Item_temporal_func::val_str(String *str)
}
-longlong Item_temporal_func::val_int()
-{
- DBUG_ASSERT(fixed == 1);
- MYSQL_TIME ltime;
- if (get_date(&ltime, sql_mode))
- return 0;
- longlong v= TIME_to_ulonglong(&ltime);
- return ltime.neg ? -v : v;
-}
-
-
-double Item_temporal_func::val_real()
-{
- DBUG_ASSERT(fixed == 1);
- MYSQL_TIME ltime;
- if (get_date(&ltime, sql_mode))
- return 0;
- return TIME_to_double(&ltime);
-}
-
-
bool Item_func_from_days::get_date(MYSQL_TIME *ltime, ulonglong fuzzy_date)
{
longlong value=args[0]->val_int();
diff --git a/sql/item_timefunc.h b/sql/item_timefunc.h
index c54490f45af..c086579e34b 100644
--- a/sql/item_timefunc.h
+++ b/sql/item_timefunc.h
@@ -499,8 +499,8 @@ public:
enum_field_types field_type() const { return MYSQL_TYPE_DATETIME; }
Item_result cmp_type() const { return TIME_RESULT; }
String *val_str(String *str);
- longlong val_int();
- double val_real();
+ longlong val_int() { return val_int_from_date(); }
+ double val_real() { return val_real_from_date(); }
bool get_date(MYSQL_TIME *res, ulonglong fuzzy_date) { DBUG_ASSERT(0); return 1; }
my_decimal *val_decimal(my_decimal *decimal_value)
{ return val_decimal_from_date(decimal_value); }
View Lorry Controller Status