maria-developers team mailing list archive
-
maria-developers team
-
Mailing list archive
-
Message #07314
Re: Please review a patch fixing a few MDEV-6001 blockers
Hello Sergei,
Please find a new version of the patch that addresses your review
suggestions. Also, see comments inline.
Thanks.
On 05/28/2014 09:17 PM, Sergei Golubchik wrote:
<skip>
=== modified file 'mysql-test/r/ps_2myisam.result'
--- mysql-test/r/ps_2myisam.result 2013-11-20 11:05:39 +0000
+++ mysql-test/r/ps_2myisam.result 2014-04-15 04:25:48 +0000
@@ -3256,7 +3256,7 @@ values
( 50, 1.0e+10, 1.0e+10, 1.0e+10, 1.0e+10, 1.0e+10 ) ;
Warnings:
Warning 1265 Data truncated for column 'c15' at row 1
-Warning 1265 Data truncated for column 'c16' at row 1
+Note 1265 Data truncated for column 'c16' at row 1
What bug number is this for? Doesn't look like any of those five that
you've listed.
It converts 1.0e+10 to datetime "2001-00-00 00:00:00".
It looks correct to produce a note instead of a warning here.
Would you like it to be reported separately?
At least, committed separately, if possible. I found "bzr shelve"
command to be very useful in cases like that.
I reported and pushed it as a separate bug:
MDEV-6287 Bad warning level when inserting a DATETIME value into a TIME
column
=== modified file 'sql/field.h'
--- sql/field.h 2014-03-26 21:25:38 +0000
+++ sql/field.h 2014-04-11 10:32:12 +0000
@@ -94,6 +94,28 @@ inline uint get_set_pack_length(int elem
/**
+ Tests if field type is temporal and has date part,
+ i.e. represents DATE, DATETIME or TIMESTAMP types in SQL.
+
+ @param type Field type, as returned by field->type().
+ @retval true If field type is temporal type with date part.
+ @retval false If field type is not temporal type with date part.
+*/
+inline bool is_temporal_type_with_date(enum_field_types type)
+{
+ switch (type)
+ {
+ case MYSQL_TYPE_DATE:
+ case MYSQL_TYPE_DATETIME:
+ case MYSQL_TYPE_TIMESTAMP:
What about MYSQL_TYPE_DATETIME2, MYSQL_TYPE_TIMESTAMP2 types?
They should never appear in this context.
MYSQL_TYPE_XXX2 can only be returned from field->real_type(),
and should never be returned from item->field_type().
Could you add them with an assert then, please? Like
case MYSQL_TYPE_DATE2:
case MYSQL_TYPE_DATETIME2:
case MYSQL_TYPE_TIMESTAMP2:
DBUG_ASSERT(0); // impossible
case MYSQL_TYPE_DATE:
case MYSQL_TYPE_DATETIME:
case MYSQL_TYPE_TIMESTAMP:
return true;
Done.
=== modified file 'sql/item_cmpfunc.cc'
--- sql/item_cmpfunc.cc 2014-03-26 21:25:38 +0000
+++ sql/item_cmpfunc.cc 2014-04-15 02:15:58 +0000
@@ -881,7 +886,8 @@ void Arg_comparator::set_datetime_cmp_fu
longlong
get_datetime_value(THD *thd, Item ***item_arg, Item **cache_arg,
- Item *warn_item, bool *is_null)
+ Item *warn_item, bool *is_null,
+ bool convert_time_to_datetime)
do you need this convert_time_to_datetime?
It might be enough to use is_temporal_type_with_date(warn_item->field_type())
Conversion is needed only if args[0] is datetime and arg[1] is TIME,
or the other way around.
When both args[0] and args[1] are TIMEs, no conversion is needed
for performance purposes.
So knowing only warn_item->field_type() is not enough.
This chunk implements this logic:
--- sql/item_cmpfunc.cc 2014-03-26 21:25:38 +0000
+++ sql/item_cmpfunc.cc 2014-04-15 02:15:58 +0000
@@ -593,6 +593,11 @@ int Arg_comparator::set_compare_func(Ite
switch (type) {
case TIME_RESULT:
cmp_collation.collation= &my_charset_numeric;
+ if ((a[0]->field_type() == MYSQL_TYPE_TIME &&
+ is_temporal_type_with_date(b[0]->field_type())) ||
+ (b[0]->field_type() == MYSQL_TYPE_TIME &&
+ is_temporal_type_with_date(a[0]->field_type())))
+ convert_time_to_datetime= true;
Yes, that's what I mean. Instead of passing convert_time_to_datetime
from the caller, you can put this logic inside get_datetime_value():
if ((item->field_type() == MYSQL_TYPE_TIME &&
is_temporal_type_with_date(warn_item->field_type()))
...
this if() means that TIME value will be compared as datetime, and thus
needs to be converted.
Done. Thant worked fine. Thanks.
Regards,
Sergei
=== modified file 'include/my_time.h'
--- include/my_time.h 2014-03-06 20:21:25 +0000
+++ include/my_time.h 2014-04-15 03:10:39 +0000
@@ -125,7 +125,7 @@ longlong double_to_datetime(double nr, M
ltime, flags, cut);
}
-int number_to_time(my_bool neg, longlong nr, ulong sec_part,
+int number_to_time(my_bool neg, ulonglong nr, ulong sec_part,
MYSQL_TIME *ltime, int *was_cut);
ulonglong TIME_to_ulonglong_datetime(const MYSQL_TIME *);
ulonglong TIME_to_ulonglong_date(const MYSQL_TIME *);
=== modified file 'mysql-test/r/cast.result'
--- mysql-test/r/cast.result 2014-03-26 21:25:38 +0000
+++ mysql-test/r/cast.result 2014-04-09 13:32:17 +0000
@@ -78,7 +78,7 @@ cast(cast(20010203101112.121314 as doubl
2001-02-03 10:11:12
select cast(cast(010203101112.12 as double) as datetime);
cast(cast(010203101112.12 as double) as datetime)
-0001-02-03 10:11:12
+2001-02-03 10:11:12
select cast(cast(20010203101112.121314 as decimal(32,6)) as datetime);
cast(cast(20010203101112.121314 as decimal(32,6)) as datetime)
2001-02-03 10:11:12
@@ -87,10 +87,10 @@ cast(20010203101112.121314 as datetime)
2001-02-03 10:11:12
select cast(110203101112.121314 as datetime);
cast(110203101112.121314 as datetime)
-0011-02-03 10:11:12
+2011-02-03 10:11:12
select cast(cast(010203101112.12 as double) as datetime);
cast(cast(010203101112.12 as double) as datetime)
-0001-02-03 10:11:12
+2001-02-03 10:11:12
select cast("2011-02-03 10:11:12.123456" as datetime);
cast("2011-02-03 10:11:12.123456" as datetime)
2011-02-03 10:11:12
@@ -111,7 +111,7 @@ cast(cast(20010203101112.5 as double) as
2001-02-03 10:11:12.5
select cast(cast(010203101112.12 as double) as datetime(2));
cast(cast(010203101112.12 as double) as datetime(2))
-0001-02-03 10:11:12.12
+2001-02-03 10:11:12.12
select cast(cast(20010203101112.121314 as decimal(32,6)) as datetime(6));
cast(cast(20010203101112.121314 as decimal(32,6)) as datetime(6))
2001-02-03 10:11:12.121314
@@ -120,10 +120,10 @@ cast(20010203101112.121314 as datetime(6
2001-02-03 10:11:12.121314
select cast(110203101112.121314 as datetime(6));
cast(110203101112.121314 as datetime(6))
-0011-02-03 10:11:12.121314
+2011-02-03 10:11:12.121314
select cast(cast(010203101112.12 as double) as datetime(6));
cast(cast(010203101112.12 as double) as datetime(6))
-0001-02-03 10:11:12.120000
+2001-02-03 10:11:12.120000
select cast("2011-02-03 10:11:12.123456" as time);
cast("2011-02-03 10:11:12.123456" as time)
10:11:12
@@ -266,7 +266,7 @@ cast(-1000 as double(5,2))
Warning 1264 Out of range value for column 'cast(-1000 as double(5,2))' at row 1
select cast(010203101112.121314 as datetime);
cast(010203101112.121314 as datetime)
-0001-02-03 10:11:12
+2001-02-03 10:11:12
select cast(120010203101112.121314 as datetime);
cast(120010203101112.121314 as datetime)
NULL
@@ -327,7 +327,7 @@ cast(cast(120010203101112.121314 as doub
NULL
select cast(cast(1.1 as double) as datetime);
cast(cast(1.1 as double) as datetime)
-0000-00-00 00:00:01
+NULL
select cast(cast(-1.1 as double) as datetime);
cast(cast(-1.1 as double) as datetime)
NULL
=== modified file 'mysql-test/r/dyncol.result'
--- mysql-test/r/dyncol.result 2014-03-13 08:38:41 +0000
+++ mysql-test/r/dyncol.result 2014-04-15 06:42:12 +0000
@@ -1001,29 +1001,29 @@ column_get(column_create(1, "1223.5aa" A
Warning 1292 Truncated incorrect time value: '1223.5aa'
select column_get(column_create(1, 18446744073709551615 AS unsigned int), 1 as time);
column_get(column_create(1, 18446744073709551615 AS unsigned int), 1 as time)
-NULL
+838:59:59
Warnings:
-Warning 1292 Incorrect datetime value: '1.8446744073709552e19'
+Warning 1292 Truncated incorrect time value: '1.8446744073709552e19'
select column_get(column_create(1, 9223372036854775807 AS int), 1 as time);
column_get(column_create(1, 9223372036854775807 AS int), 1 as time)
-NULL
+838:59:59
Warnings:
-Warning 1292 Incorrect datetime value: '9223372036854775807'
+Warning 1292 Truncated incorrect time value: '9223372036854775807'
select column_get(column_create(1, -9223372036854775808 AS int), 1 as time);
column_get(column_create(1, -9223372036854775808 AS int), 1 as time)
-NULL
+-838:59:59
Warnings:
-Warning 1292 Incorrect datetime value: '-9223372036854775808'
+Warning 1292 Truncated incorrect time value: '-9223372036854775808'
select column_get(column_create(1, 99999999999999999999999999999 AS decimal(32,10)), 1 as time);
column_get(column_create(1, 99999999999999999999999999999 AS decimal(32,10)), 1 as time)
-NULL
+838:59:59
Warnings:
-Warning 1292 Incorrect datetime value: '99999999999999999999999999999'
+Warning 1292 Truncated incorrect time value: '99999999999999999999999999999'
select column_get(column_create(1, 99999999999999999999999999999 AS double), 1 as time);
column_get(column_create(1, 99999999999999999999999999999 AS double), 1 as time)
-NULL
+838:59:59
Warnings:
-Warning 1292 Incorrect datetime value: '1e29'
+Warning 1292 Truncated incorrect time value: '1e29'
select column_get(column_create(1, "2011-02-32 8:46:06.23434" AS CHAR), 1 as time);
column_get(column_create(1, "2011-02-32 8:46:06.23434" AS CHAR), 1 as time)
NULL
@@ -1766,5 +1766,16 @@ group_concat(cast(column_json(dyn) as ch
{"name1":"value1","name2":"value2"}
drop table t1;
#
+# MDEV-4858 Wrong results for a huge unsigned value inserted into a TIME column
+#
+SELECT
+column_get(column_create(1, -999999999999999 AS int), 1 AS TIME) AS t1,
+column_get(column_create(1, -9223372036854775808 AS int), 1 AS TIME) AS t2;
+t1 t2
+-838:59:59 -838:59:59
+Warnings:
+Warning 1292 Truncated incorrect time value: '-999999999999999'
+Warning 1292 Truncated incorrect time value: '-9223372036854775808'
+#
# end of 10.0 tests
#
=== modified file 'mysql-test/r/func_time.result'
--- mysql-test/r/func_time.result 2014-03-23 10:22:44 +0000
+++ mysql-test/r/func_time.result 2014-04-15 06:56:53 +0000
@@ -1091,9 +1091,9 @@ NULL
select isnull(week(now() + 0)), isnull(week(now() + 0.2)),
week(20061108), week(20061108.01), week(20061108085411.000002);
isnull(week(now() + 0)) isnull(week(now() + 0.2)) week(20061108) week(20061108.01) week(20061108085411.000002)
-0 0 45 NULL 45
+0 0 45 45 45
Warnings:
-Warning 1292 Incorrect datetime value: '20061108.01'
+Warning 1292 Truncated incorrect datetime value: '20061108.01'
End of 4.1 tests
select time_format('100:00:00', '%H %k %h %I %l');
time_format('100:00:00', '%H %k %h %I %l')
@@ -1878,9 +1878,15 @@ least(1, f1)
Warnings:
Warning 1292 Incorrect datetime value: '1'
drop table t1;
+SET timestamp=UNIX_TIMESTAMP('2014-04-14 10:10:10');
+select now() > coalesce(time('21:43:24'), date('2010-05-03'));
+now() > coalesce(time('21:43:24'), date('2010-05-03'))
+0
+SET timestamp=UNIX_TIMESTAMP('2014-04-14 22:22:22');
select now() > coalesce(time('21:43:24'), date('2010-05-03'));
now() > coalesce(time('21:43:24'), date('2010-05-03'))
1
+SET timestamp=DEFAULT;
create table t1 (f1 timestamp);
select * from t1 where f1 > f1 and f1 <=> timestampadd(hour, 9 , '2010-01-01 16:55:35');
f1
@@ -2066,15 +2072,16 @@ DROP TABLE t1;
#
# MDEV-4870 Wrong values of CASE, COALESCE, IFNULL on a combination of different temporal types
#
+SET timestamp=UNIX_TIMESTAMP('2001-01-01 10:20:30');
CREATE TABLE t1 (dt2 DATETIME(2), t3 TIME(3), d DATE);
INSERT INTO t1 VALUES ('2001-01-01 00:00:00.12', '00:00:00.567', '2002-01-01');
SELECT CASE WHEN 0 THEN dt2 ELSE t3 END FROM t1;
CASE WHEN 0 THEN dt2 ELSE t3 END
-0000-00-00 00:00:00.567
+2001-01-01 00:00:00.567
CREATE TABLE t2 AS SELECT CASE WHEN 0 THEN dt2 ELSE t3 END FROM t1;
SELECT * FROM t2;
CASE WHEN 0 THEN dt2 ELSE t3 END
-0000-00-00 00:00:00.567
+2001-01-01 00:00:00.567
SHOW COLUMNS FROM t2;
Field Type Null Key Default Extra
CASE WHEN 0 THEN dt2 ELSE t3 END datetime(3) YES NULL
@@ -2093,7 +2100,7 @@ CONCAT(CASE WHEN 1 THEN d ELSE t3 END)
2002-01-01 00:00:00.000
SELECT CASE WHEN 1 THEN t3 ELSE d END FROM t1;
CASE WHEN 1 THEN t3 ELSE d END
-0000-00-00 00:00:00.567
+2001-01-01 00:00:00.567
SELECT COALESCE(d, t3) FROM t1;
COALESCE(d, t3)
2002-01-01 00:00:00.000
@@ -2113,6 +2120,7 @@ SELECT IFNULL(d, t3), CONCAT(IFNULL(d, t
IFNULL(d, t3) CONCAT(IFNULL(d, t3))
2002-01-01 00:00:00.000 2002-01-01 00:00:00.000
DROP TABLE t1;
+SET timestamp=DEFAULT;
#
# MDEV-4724 Some temporal functions do not preserve microseconds
#
@@ -2494,3 +2502,28 @@ COALESCE(ADDDATE(MAKEDATE(2011,121), dt)
Warning 1441 Datetime function: datetime field overflow
Warning 1441 Datetime function: datetime field overflow
DROP TABLE t1;
+#
+# MDEV-6099 Bad results for DATE_ADD(.., INTERVAL 2000000000000000000.0 SECOND)
+#
+SELECT DATE_ADD('2001-01-01 10:20:30',INTERVAL 250000000000.0 SECOND) AS c1, DATE_ADD('2001-01-01 10:20:30',INTERVAL 2000000000000000000.0 SECOND) AS c2;
+c1 c2
+9923-03-10 22:47:10.0 NULL
+Warnings:
+Warning 1292 Truncated incorrect DECIMAL value: '2000000000000000000.0'
+#
+# MDEV-6101 Hybrid functions do not add CURRENT_DATE when converting TIME to DATETIME
+#
+SET timestamp=UNIX_TIMESTAMP('2014-04-15 01:02:03');
+SELECT IF(1,TIME'10:20:30',DATE'2001-01-01');
+IF(1,TIME'10:20:30',DATE'2001-01-01')
+2014-04-15 10:20:30
+SELECT IFNULL(TIME'10:20:30',DATE'2001-01-01');
+IFNULL(TIME'10:20:30',DATE'2001-01-01')
+2014-04-15 10:20:30
+SELECT CASE WHEN 1 THEN TIME'10:20:30' ELSE DATE'2001-01-01' END;
+CASE WHEN 1 THEN TIME'10:20:30' ELSE DATE'2001-01-01' END
+2014-04-15 10:20:30
+SELECT COALESCE(TIME'10:20:30',DATE'2001-01-01');
+COALESCE(TIME'10:20:30',DATE'2001-01-01')
+2014-04-15 10:20:30
+SET timestamp=DEFAULT;
=== modified file 'mysql-test/r/old-mode.result'
--- mysql-test/r/old-mode.result 2014-03-07 20:05:28 +0000
+++ mysql-test/r/old-mode.result 2014-04-14 12:51:24 +0000
@@ -95,8 +95,9 @@ INSERT INTO t1 VALUES (NULL, '00:20:12')
INSERT INTO t1 VALUES (NULL, '-00:20:12');
SELECT IF(1,ADDDATE(IFNULL(a,b),0),1) FROM t1;
IF(1,ADDDATE(IFNULL(a,b),0),1)
-0000-00-00 00:20:12
+NULL
NULL
Warnings:
+Warning 1292 Incorrect datetime value: '0000-00-00 00:20:12'
Warning 1292 Truncated incorrect datetime value: '-00:20:12'
DROP TABLE t1;
=== modified file 'mysql-test/r/ps_2myisam.result'
--- mysql-test/r/ps_2myisam.result 2013-11-20 11:05:39 +0000
+++ mysql-test/r/ps_2myisam.result 2014-04-15 04:25:48 +0000
@@ -3256,7 +3256,7 @@ values
( 50, 1.0e+10, 1.0e+10, 1.0e+10, 1.0e+10, 1.0e+10 ) ;
Warnings:
Warning 1265 Data truncated for column 'c15' at row 1
-Warning 1265 Data truncated for column 'c16' at row 1
+Note 1265 Data truncated for column 'c16' at row 1
Warning 1264 Out of range value for column 'c17' at row 1
insert into t9
( c1, c13, c14, c15, c16, c17 )
@@ -3264,7 +3264,7 @@ values
( 51, @arg00, @arg00, @arg00, @arg00, @arg00) ;
Warnings:
Warning 1265 Data truncated for column 'c15' at row 1
-Warning 1265 Data truncated for column 'c16' at row 1
+Note 1265 Data truncated for column 'c16' at row 1
Warning 1264 Out of range value for column 'c17' at row 1
prepare stmt1 from "insert into t9
( c1, c13, c14, c15, c16, c17 )
@@ -3273,7 +3273,7 @@ values
execute stmt1 ;
Warnings:
Warning 1265 Data truncated for column 'c15' at row 1
-Warning 1265 Data truncated for column 'c16' at row 1
+Note 1265 Data truncated for column 'c16' at row 1
Warning 1264 Out of range value for column 'c17' at row 1
prepare stmt2 from "insert into t9
( c1, c13, c14, c15, c16, c17 )
@@ -3282,7 +3282,7 @@ values
execute stmt2 using @arg00, @arg00, @arg00, @arg00, @arg00 ;
Warnings:
Warning 1265 Data truncated for column 'c15' at row 1
-Warning 1265 Data truncated for column 'c16' at row 1
+Note 1265 Data truncated for column 'c16' at row 1
Warning 1264 Out of range value for column 'c17' at row 1
set @arg00= 'abc' ;
set @arg00= NULL ;
=== modified file 'mysql-test/r/ps_3innodb.result'
--- mysql-test/r/ps_3innodb.result 2013-11-20 11:05:39 +0000
+++ mysql-test/r/ps_3innodb.result 2014-04-15 04:25:45 +0000
@@ -3239,7 +3239,7 @@ values
( 50, 1.0e+10, 1.0e+10, 1.0e+10, 1.0e+10, 1.0e+10 ) ;
Warnings:
Warning 1265 Data truncated for column 'c15' at row 1
-Warning 1265 Data truncated for column 'c16' at row 1
+Note 1265 Data truncated for column 'c16' at row 1
Warning 1264 Out of range value for column 'c17' at row 1
insert into t9
( c1, c13, c14, c15, c16, c17 )
@@ -3247,7 +3247,7 @@ values
( 51, @arg00, @arg00, @arg00, @arg00, @arg00) ;
Warnings:
Warning 1265 Data truncated for column 'c15' at row 1
-Warning 1265 Data truncated for column 'c16' at row 1
+Note 1265 Data truncated for column 'c16' at row 1
Warning 1264 Out of range value for column 'c17' at row 1
prepare stmt1 from "insert into t9
( c1, c13, c14, c15, c16, c17 )
@@ -3256,7 +3256,7 @@ values
execute stmt1 ;
Warnings:
Warning 1265 Data truncated for column 'c15' at row 1
-Warning 1265 Data truncated for column 'c16' at row 1
+Note 1265 Data truncated for column 'c16' at row 1
Warning 1264 Out of range value for column 'c17' at row 1
prepare stmt2 from "insert into t9
( c1, c13, c14, c15, c16, c17 )
@@ -3265,7 +3265,7 @@ values
execute stmt2 using @arg00, @arg00, @arg00, @arg00, @arg00 ;
Warnings:
Warning 1265 Data truncated for column 'c15' at row 1
-Warning 1265 Data truncated for column 'c16' at row 1
+Note 1265 Data truncated for column 'c16' at row 1
Warning 1264 Out of range value for column 'c17' at row 1
set @arg00= 'abc' ;
set @arg00= NULL ;
=== modified file 'mysql-test/r/ps_4heap.result'
--- mysql-test/r/ps_4heap.result 2013-11-20 11:05:39 +0000
+++ mysql-test/r/ps_4heap.result 2014-04-15 04:25:48 +0000
@@ -3240,7 +3240,7 @@ values
( 50, 1.0e+10, 1.0e+10, 1.0e+10, 1.0e+10, 1.0e+10 ) ;
Warnings:
Warning 1265 Data truncated for column 'c15' at row 1
-Warning 1265 Data truncated for column 'c16' at row 1
+Note 1265 Data truncated for column 'c16' at row 1
Warning 1264 Out of range value for column 'c17' at row 1
insert into t9
( c1, c13, c14, c15, c16, c17 )
@@ -3248,7 +3248,7 @@ values
( 51, @arg00, @arg00, @arg00, @arg00, @arg00) ;
Warnings:
Warning 1265 Data truncated for column 'c15' at row 1
-Warning 1265 Data truncated for column 'c16' at row 1
+Note 1265 Data truncated for column 'c16' at row 1
Warning 1264 Out of range value for column 'c17' at row 1
prepare stmt1 from "insert into t9
( c1, c13, c14, c15, c16, c17 )
@@ -3257,7 +3257,7 @@ values
execute stmt1 ;
Warnings:
Warning 1265 Data truncated for column 'c15' at row 1
-Warning 1265 Data truncated for column 'c16' at row 1
+Note 1265 Data truncated for column 'c16' at row 1
Warning 1264 Out of range value for column 'c17' at row 1
prepare stmt2 from "insert into t9
( c1, c13, c14, c15, c16, c17 )
@@ -3266,7 +3266,7 @@ values
execute stmt2 using @arg00, @arg00, @arg00, @arg00, @arg00 ;
Warnings:
Warning 1265 Data truncated for column 'c15' at row 1
-Warning 1265 Data truncated for column 'c16' at row 1
+Note 1265 Data truncated for column 'c16' at row 1
Warning 1264 Out of range value for column 'c17' at row 1
set @arg00= 'abc' ;
set @arg00= NULL ;
=== modified file 'mysql-test/r/ps_5merge.result'
--- mysql-test/r/ps_5merge.result 2013-11-20 11:05:39 +0000
+++ mysql-test/r/ps_5merge.result 2014-04-15 04:25:49 +0000
@@ -3176,7 +3176,7 @@ values
( 50, 1.0e+10, 1.0e+10, 1.0e+10, 1.0e+10, 1.0e+10 ) ;
Warnings:
Warning 1265 Data truncated for column 'c15' at row 1
-Warning 1265 Data truncated for column 'c16' at row 1
+Note 1265 Data truncated for column 'c16' at row 1
Warning 1264 Out of range value for column 'c17' at row 1
insert into t9
( c1, c13, c14, c15, c16, c17 )
@@ -3184,7 +3184,7 @@ values
( 51, @arg00, @arg00, @arg00, @arg00, @arg00) ;
Warnings:
Warning 1265 Data truncated for column 'c15' at row 1
-Warning 1265 Data truncated for column 'c16' at row 1
+Note 1265 Data truncated for column 'c16' at row 1
Warning 1264 Out of range value for column 'c17' at row 1
prepare stmt1 from "insert into t9
( c1, c13, c14, c15, c16, c17 )
@@ -3193,7 +3193,7 @@ values
execute stmt1 ;
Warnings:
Warning 1265 Data truncated for column 'c15' at row 1
-Warning 1265 Data truncated for column 'c16' at row 1
+Note 1265 Data truncated for column 'c16' at row 1
Warning 1264 Out of range value for column 'c17' at row 1
prepare stmt2 from "insert into t9
( c1, c13, c14, c15, c16, c17 )
@@ -3202,7 +3202,7 @@ values
execute stmt2 using @arg00, @arg00, @arg00, @arg00, @arg00 ;
Warnings:
Warning 1265 Data truncated for column 'c15' at row 1
-Warning 1265 Data truncated for column 'c16' at row 1
+Note 1265 Data truncated for column 'c16' at row 1
Warning 1264 Out of range value for column 'c17' at row 1
set @arg00= 'abc' ;
set @arg00= NULL ;
@@ -6530,7 +6530,7 @@ values
( 50, 1.0e+10, 1.0e+10, 1.0e+10, 1.0e+10, 1.0e+10 ) ;
Warnings:
Warning 1265 Data truncated for column 'c15' at row 1
-Warning 1265 Data truncated for column 'c16' at row 1
+Note 1265 Data truncated for column 'c16' at row 1
Warning 1264 Out of range value for column 'c17' at row 1
insert into t9
( c1, c13, c14, c15, c16, c17 )
@@ -6538,7 +6538,7 @@ values
( 51, @arg00, @arg00, @arg00, @arg00, @arg00) ;
Warnings:
Warning 1265 Data truncated for column 'c15' at row 1
-Warning 1265 Data truncated for column 'c16' at row 1
+Note 1265 Data truncated for column 'c16' at row 1
Warning 1264 Out of range value for column 'c17' at row 1
prepare stmt1 from "insert into t9
( c1, c13, c14, c15, c16, c17 )
@@ -6547,7 +6547,7 @@ values
execute stmt1 ;
Warnings:
Warning 1265 Data truncated for column 'c15' at row 1
-Warning 1265 Data truncated for column 'c16' at row 1
+Note 1265 Data truncated for column 'c16' at row 1
Warning 1264 Out of range value for column 'c17' at row 1
prepare stmt2 from "insert into t9
( c1, c13, c14, c15, c16, c17 )
@@ -6556,7 +6556,7 @@ values
execute stmt2 using @arg00, @arg00, @arg00, @arg00, @arg00 ;
Warnings:
Warning 1265 Data truncated for column 'c15' at row 1
-Warning 1265 Data truncated for column 'c16' at row 1
+Note 1265 Data truncated for column 'c16' at row 1
Warning 1264 Out of range value for column 'c17' at row 1
set @arg00= 'abc' ;
set @arg00= NULL ;
=== modified file 'mysql-test/r/type_datetime.result'
--- mysql-test/r/type_datetime.result 2014-02-26 14:28:07 +0000
+++ mysql-test/r/type_datetime.result 2014-04-15 06:48:37 +0000
@@ -653,13 +653,15 @@ SELECT * FROM t1;
dt1
DROP TABLE t1;
End of 5.1 tests
+SET timestamp=UNIX_TIMESTAMP('2001-01-01 10:20:30');
create table t1 (d date, t time) engine=myisam;
insert into t1 values ('2000-12-03','22:55:23'),('2008-05-03','10:19:31');
select case when d = '2012-12-12' then d else t end as cond, group_concat( d ) from t1 group by cond;
cond group_concat( d )
-0000-00-00 10:19:31 2008-05-03
-0000-00-00 22:55:23 2000-12-03
+2001-01-01 10:19:31 2008-05-03
+2001-01-01 22:55:23 2000-12-03
drop table t1;
+SET timestamp=DEFAULT;
#
# Semantics of the condition <non-nullable datetime field> IS NULL
# when the field belongs to an inner table of an outer join
@@ -787,3 +789,18 @@ DROP TABLE t1,t2;
#
# End of 5.5 tests
#
+#
+# Start of 10.0 tests
+#
+#
+# MDEV-6097 Inconsistent results for CAST(int,decimal,double AS DATETIME)
+#
+SELECT
+CAST(010203101112 AS DATETIME(1)) AS c1,
+CAST(010203101112.2 AS DATETIME(1)) AS c2,
+CAST(010203101112.2+0e0 AS DATETIME(1)) AS c3;
+c1 c2 c3
+2001-02-03 10:11:12.0 2001-02-03 10:11:12.2 2001-02-03 10:11:12.2
+#
+# End of 10.0 tests
+#
=== modified file 'mysql-test/r/type_time.result'
--- mysql-test/r/type_time.result 2014-03-06 20:21:25 +0000
+++ mysql-test/r/type_time.result 2014-04-15 06:58:35 +0000
@@ -163,12 +163,26 @@ create table t1 (f1 time , f2 varchar(5)
insert into t1 values ('00:20:01','a'),('00:20:03','b');
select * from t1 force key (f1) where f1 < curdate();
f1 f2
+select * from t1 ignore key (f1) where f1 < curdate();
+f1 f2
+select * from t1 force key (f1) where f1 > curdate();
+f1 f2
00:20:01 a
00:20:03 b
-select * from t1 ignore key (f1) where f1 < curdate();
+select * from t1 ignore key (f1) where f1 > curdate();
f1 f2
00:20:01 a
00:20:03 b
+delete from t1;
+insert into t1 values ('-00:20:01','a'),('-00:20:03','b');
+select * from t1 force key (f1) where f1 < curdate();
+f1 f2
+-00:20:01 a
+-00:20:03 b
+select * from t1 ignore key (f1) where f1 < curdate();
+f1 f2
+-00:20:01 a
+-00:20:03 b
drop table t1;
create table t1(f1 time);
insert into t1 values ('23:38:57');
@@ -326,3 +340,45 @@ SELECT '-24:00:00' = (SELECT f1 FROM t1)
'-24:00:00' = (SELECT f1 FROM t1)
1
DROP TABLE t1;
+#
+# Start of 10.0 tests
+#
+#
+# MDEV-4858 Wrong results for a huge unsigned value inserted into a TIME column
+#
+SET sql_mode=traditional;
+CREATE TABLE t1 (a TIME(6));
+INSERT INTO t1 VALUES (CAST(0xFFFFFFFF00000000 AS UNSIGNED));
+ERROR 22007: Incorrect time value: '18446744069414584320' for column 'a' at row 1
+SET sql_mode=DEFAULT;
+INSERT INTO t1 VALUES (CAST(0xFFFFFFFF00000000 AS UNSIGNED));
+Warnings:
+Warning 1264 Out of range value for column 'a' at row 1
+SELECT * FROM t1;
+a
+838:59:59.999999
+DROP TABLE t1;
+SELECT TIME'00:00:00'> CAST(0xFFFFFFFF00000000 AS UNSIGNED);
+TIME'00:00:00'> CAST(0xFFFFFFFF00000000 AS UNSIGNED)
+0
+Warnings:
+Warning 1292 Truncated incorrect time value: '18446744069414584320'
+#
+# MDEV-6100 No warning on CAST(9000000 AS TIME)
+#
+SELECT CAST(9000000 AS TIME);
+CAST(9000000 AS TIME)
+838:59:59
+Warnings:
+Warning 1292 Truncated incorrect time value: '9000000'
+#
+# MDEV-6102 Comparison between TIME and DATETIME does not use CURRENT_DATE
+#
+SET timestamp=UNIX_TIMESTAMP('2014-04-14 01:02:03');
+SELECT CAST(TIME'10:20:30' AS DATETIME), TIME'10:20:30'=TIMESTAMP'2014-04-14 10:20:30' AS cmp;
+CAST(TIME'10:20:30' AS DATETIME) cmp
+2014-04-14 10:20:30 1
+SET timestamp=DEFAULT;
+#
+# End of 10.0 tests
+#
=== modified file 'mysql-test/r/type_time_hires.result'
--- mysql-test/r/type_time_hires.result 2014-03-06 20:21:25 +0000
+++ mysql-test/r/type_time_hires.result 2014-04-15 04:21:17 +0000
@@ -11,52 +11,52 @@ insert t1 values ('2010-12-11 15:47:11.1
Note 1265 Data truncated for column 'a' at row 1
insert t1 values (20101211010203.45678);
Warnings:
-Warning 1265 Data truncated for column 'a' at row 1
+Note 1265 Data truncated for column 'a' at row 1
insert t1 values (20101211030405.789e0);
Warnings:
-Warning 1265 Data truncated for column 'a' at row 1
+Note 1265 Data truncated for column 'a' at row 1
insert t1 values (99991231235959e1);
Warnings:
-Warning 1265 Data truncated for column 'a' at row 1
+Warning 1264 Out of range value for column 'a' at row 1
select * from t1;
a
-00:00:00.000
00:20:03.123
01:02:03.456
03:04:05.789
15:47:11.123
+838:59:59.999
select truncate(a, 6) from t1;
truncate(a, 6)
-0.000000
2003.123000
10203.456000
30405.789062
154711.123000
+8385959.999000
select a DIV 1 from t1;
a DIV 1
-0
2003
10203
30405
154711
+8385959
select group_concat(distinct a) from t1;
group_concat(distinct a)
-00:00:00.000,00:20:03.123,01:02:03.456,03:04:05.789,15:47:11.123
+00:20:03.123,01:02:03.456,03:04:05.789,15:47:11.123,838:59:59.999
alter table t1 engine=innodb;
select * from t1 order by a;
a
-00:00:00.000
00:20:03.123
01:02:03.456
03:04:05.789
15:47:11.123
+838:59:59.999
select * from t1 order by a+0;
a
-00:00:00.000
00:20:03.123
01:02:03.456
03:04:05.789
15:47:11.123
+838:59:59.999
drop table t1;
create table t1 (a time(4)) engine=innodb;
insert t1 values ('2010-12-11 01:02:03.456789');
=== modified file 'mysql-test/suite/funcs_1/r/innodb_func_view.result'
--- mysql-test/suite/funcs_1/r/innodb_func_view.result 2014-03-06 20:21:25 +0000
+++ mysql-test/suite/funcs_1/r/innodb_func_view.result 2014-04-15 04:21:56 +0000
@@ -3695,14 +3695,14 @@ my_double, id FROM t1_values
WHERE select_id = 53 OR select_id IS NULL order by id;
CAST(my_double AS TIME) my_double id
NULL NULL 1
-NULL -1.7976931348623e308 2
-NULL 1.7976931348623e308 3
+-838:59:59 -1.7976931348623e308 2
+838:59:59 1.7976931348623e308 3
00:00:00 0 4
-00:00:01 -1 5
00:17:58 1758 25
Warnings:
-Warning 1292 Incorrect datetime value: '-1.7976931348623e308' for column 'my_double' at row 2
-Warning 1292 Incorrect datetime value: '1.7976931348623e308' for column 'my_double' at row 3
+Warning 1292 Incorrect time value: '-1.7976931348623e308' for column 'my_double' at row 2
+Warning 1292 Incorrect time value: '1.7976931348623e308' for column 'my_double' at row 3
SHOW CREATE VIEW v1;
View Create View character_set_client collation_connection
v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select cast(`t1_values`.`my_double` as time) AS `CAST(my_double AS TIME)`,`t1_values`.`my_double` AS `my_double`,`t1_values`.`id` AS `id` from `t1_values` latin1 latin1_swedish_ci
@@ -3711,14 +3711,14 @@ WHERE v1.id IN (SELECT id FROM t1_values
WHERE select_id = 53 OR select_id IS NULL) order by id;
CAST(my_double AS TIME) my_double id
NULL NULL 1
-NULL -1.7976931348623e308 2
-NULL 1.7976931348623e308 3
+-838:59:59 -1.7976931348623e308 2
+838:59:59 1.7976931348623e308 3
00:00:00 0 4
-00:00:01 -1 5
00:17:58 1758 25
Warnings:
-Warning 1292 Incorrect datetime value: '-1.7976931348623e308' for column 'my_double' at row 1
-Warning 1292 Incorrect datetime value: '1.7976931348623e308' for column 'my_double' at row 1
+Warning 1292 Incorrect time value: '-1.7976931348623e308' for column 'my_double' at row 1
+Warning 1292 Incorrect time value: '1.7976931348623e308' for column 'my_double' at row 1
DROP VIEW v1;
=== modified file 'mysql-test/suite/funcs_1/r/memory_func_view.result'
--- mysql-test/suite/funcs_1/r/memory_func_view.result 2014-03-06 20:21:25 +0000
+++ mysql-test/suite/funcs_1/r/memory_func_view.result 2014-04-15 04:22:20 +0000
@@ -3696,14 +3696,14 @@ my_double, id FROM t1_values
WHERE select_id = 53 OR select_id IS NULL order by id;
CAST(my_double AS TIME) my_double id
NULL NULL 1
-NULL -1.7976931348623e308 2
-NULL 1.7976931348623e308 3
+-838:59:59 -1.7976931348623e308 2
+838:59:59 1.7976931348623e308 3
00:00:00 0 4
-00:00:01 -1 5
00:17:58 1758 25
Warnings:
-Warning 1292 Incorrect datetime value: '-1.7976931348623e308' for column 'my_double' at row 2
-Warning 1292 Incorrect datetime value: '1.7976931348623e308' for column 'my_double' at row 3
+Warning 1292 Incorrect time value: '-1.7976931348623e308' for column 'my_double' at row 2
+Warning 1292 Incorrect time value: '1.7976931348623e308' for column 'my_double' at row 3
SHOW CREATE VIEW v1;
View Create View character_set_client collation_connection
v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select cast(`t1_values`.`my_double` as time) AS `CAST(my_double AS TIME)`,`t1_values`.`my_double` AS `my_double`,`t1_values`.`id` AS `id` from `t1_values` latin1 latin1_swedish_ci
@@ -3712,14 +3712,14 @@ WHERE v1.id IN (SELECT id FROM t1_values
WHERE select_id = 53 OR select_id IS NULL) order by id;
CAST(my_double AS TIME) my_double id
NULL NULL 1
-NULL -1.7976931348623e308 2
-NULL 1.7976931348623e308 3
+-838:59:59 -1.7976931348623e308 2
+838:59:59 1.7976931348623e308 3
00:00:00 0 4
-00:00:01 -1 5
00:17:58 1758 25
Warnings:
-Warning 1292 Incorrect datetime value: '-1.7976931348623e308' for column 'my_double' at row 1
-Warning 1292 Incorrect datetime value: '1.7976931348623e308' for column 'my_double' at row 1
+Warning 1292 Incorrect time value: '-1.7976931348623e308' for column 'my_double' at row 1
+Warning 1292 Incorrect time value: '1.7976931348623e308' for column 'my_double' at row 1
DROP VIEW v1;
=== modified file 'mysql-test/suite/funcs_1/r/myisam_func_view.result'
--- mysql-test/suite/funcs_1/r/myisam_func_view.result 2014-03-06 20:21:25 +0000
+++ mysql-test/suite/funcs_1/r/myisam_func_view.result 2014-04-15 04:22:31 +0000
@@ -3696,14 +3696,14 @@ my_double, id FROM t1_values
WHERE select_id = 53 OR select_id IS NULL order by id;
CAST(my_double AS TIME) my_double id
NULL NULL 1
-NULL -1.7976931348623e308 2
-NULL 1.7976931348623e308 3
+-838:59:59 -1.7976931348623e308 2
+838:59:59 1.7976931348623e308 3
00:00:00 0 4
-00:00:01 -1 5
00:17:58 1758 25
Warnings:
-Warning 1292 Incorrect datetime value: '-1.7976931348623e308' for column 'my_double' at row 2
-Warning 1292 Incorrect datetime value: '1.7976931348623e308' for column 'my_double' at row 3
+Warning 1292 Incorrect time value: '-1.7976931348623e308' for column 'my_double' at row 2
+Warning 1292 Incorrect time value: '1.7976931348623e308' for column 'my_double' at row 3
SHOW CREATE VIEW v1;
View Create View character_set_client collation_connection
v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select cast(`t1_values`.`my_double` as time) AS `CAST(my_double AS TIME)`,`t1_values`.`my_double` AS `my_double`,`t1_values`.`id` AS `id` from `t1_values` latin1 latin1_swedish_ci
@@ -3712,14 +3712,14 @@ WHERE v1.id IN (SELECT id FROM t1_values
WHERE select_id = 53 OR select_id IS NULL) order by id;
CAST(my_double AS TIME) my_double id
NULL NULL 1
-NULL -1.7976931348623e308 2
-NULL 1.7976931348623e308 3
+-838:59:59 -1.7976931348623e308 2
+838:59:59 1.7976931348623e308 3
00:00:00 0 4
-00:00:01 -1 5
00:17:58 1758 25
Warnings:
-Warning 1292 Incorrect datetime value: '-1.7976931348623e308' for column 'my_double' at row 1
-Warning 1292 Incorrect datetime value: '1.7976931348623e308' for column 'my_double' at row 1
+Warning 1292 Incorrect time value: '-1.7976931348623e308' for column 'my_double' at row 1
+Warning 1292 Incorrect time value: '1.7976931348623e308' for column 'my_double' at row 1
DROP VIEW v1;
=== modified file 'mysql-test/suite/maria/ps_maria.result'
--- mysql-test/suite/maria/ps_maria.result 2013-11-20 11:05:39 +0000
+++ mysql-test/suite/maria/ps_maria.result 2014-04-15 05:55:08 +0000
@@ -3256,7 +3256,7 @@ values
( 50, 1.0e+10, 1.0e+10, 1.0e+10, 1.0e+10, 1.0e+10 ) ;
Warnings:
Warning 1265 Data truncated for column 'c15' at row 1
-Warning 1265 Data truncated for column 'c16' at row 1
+Note 1265 Data truncated for column 'c16' at row 1
Warning 1264 Out of range value for column 'c17' at row 1
insert into t9
( c1, c13, c14, c15, c16, c17 )
@@ -3264,7 +3264,7 @@ values
( 51, @arg00, @arg00, @arg00, @arg00, @arg00) ;
Warnings:
Warning 1265 Data truncated for column 'c15' at row 1
-Warning 1265 Data truncated for column 'c16' at row 1
+Note 1265 Data truncated for column 'c16' at row 1
Warning 1264 Out of range value for column 'c17' at row 1
prepare stmt1 from "insert into t9
( c1, c13, c14, c15, c16, c17 )
@@ -3273,7 +3273,7 @@ values
execute stmt1 ;
Warnings:
Warning 1265 Data truncated for column 'c15' at row 1
-Warning 1265 Data truncated for column 'c16' at row 1
+Note 1265 Data truncated for column 'c16' at row 1
Warning 1264 Out of range value for column 'c17' at row 1
prepare stmt2 from "insert into t9
( c1, c13, c14, c15, c16, c17 )
@@ -3282,7 +3282,7 @@ values
execute stmt2 using @arg00, @arg00, @arg00, @arg00, @arg00 ;
Warnings:
Warning 1265 Data truncated for column 'c15' at row 1
-Warning 1265 Data truncated for column 'c16' at row 1
+Note 1265 Data truncated for column 'c16' at row 1
Warning 1264 Out of range value for column 'c17' at row 1
set @arg00= 'abc' ;
set @arg00= NULL ;
=== modified file 'mysql-test/t/dyncol.test'
--- mysql-test/t/dyncol.test 2014-03-13 08:38:41 +0000
+++ mysql-test/t/dyncol.test 2014-04-15 06:41:48 +0000
@@ -832,5 +832,12 @@ select group_concat(cast(column_json(dyn
drop table t1;
--echo #
+--echo # MDEV-4858 Wrong results for a huge unsigned value inserted into a TIME column
+--echo #
+SELECT
+ column_get(column_create(1, -999999999999999 AS int), 1 AS TIME) AS t1,
+ column_get(column_create(1, -9223372036854775808 AS int), 1 AS TIME) AS t2;
+
+--echo #
--echo # end of 10.0 tests
--echo #
=== modified file 'mysql-test/t/func_time.test'
--- mysql-test/t/func_time.test 2014-03-23 10:22:44 +0000
+++ mysql-test/t/func_time.test 2014-04-15 06:55:58 +0000
@@ -1126,7 +1126,11 @@ drop table t1;
#
# lp:737092 Assertion `item->null_value' failed in get_datetime_value in 5.1-micro
#
+SET timestamp=UNIX_TIMESTAMP('2014-04-14 10:10:10');
select now() > coalesce(time('21:43:24'), date('2010-05-03'));
+SET timestamp=UNIX_TIMESTAMP('2014-04-14 22:22:22');
+select now() > coalesce(time('21:43:24'), date('2010-05-03'));
+SET timestamp=DEFAULT;
#
# lp:737104 Crash in DTCollation::set in 5.1-micro
@@ -1294,6 +1298,7 @@ DROP TABLE t1;
--echo #
--echo # MDEV-4870 Wrong values of CASE, COALESCE, IFNULL on a combination of different temporal types
--echo #
+SET timestamp=UNIX_TIMESTAMP('2001-01-01 10:20:30');
CREATE TABLE t1 (dt2 DATETIME(2), t3 TIME(3), d DATE);
INSERT INTO t1 VALUES ('2001-01-01 00:00:00.12', '00:00:00.567', '2002-01-01');
SELECT CASE WHEN 0 THEN dt2 ELSE t3 END FROM t1;
@@ -1313,7 +1318,7 @@ SELECT CONCAT(COALESCE(dt2, t3)) FROM t1
SELECT IFNULL(dt2, t3), CONCAT(IFNULL(dt2, t3)) FROM t1;
SELECT IFNULL(d, t3), CONCAT(IFNULL(d, t3)) FROM t1;
DROP TABLE t1;
-
+SET timestamp=DEFAULT;
--echo #
--echo # MDEV-4724 Some temporal functions do not preserve microseconds
@@ -1520,3 +1525,18 @@ CREATE TABLE t1 (dt DATETIME);
INSERT INTO t1 VALUES ('2003-05-13 19:36:05'), ('2012-12-12 09:20:06');
SELECT COALESCE(ADDDATE(MAKEDATE(2011,121), dt), '2006-09-12' ) FROM t1;
DROP TABLE t1;
+
+--echo #
+--echo # MDEV-6099 Bad results for DATE_ADD(.., INTERVAL 2000000000000000000.0 SECOND)
+--echo #
+SELECT DATE_ADD('2001-01-01 10:20:30',INTERVAL 250000000000.0 SECOND) AS c1, DATE_ADD('2001-01-01 10:20:30',INTERVAL 2000000000000000000.0 SECOND) AS c2;
+
+--echo #
+--echo # MDEV-6101 Hybrid functions do not add CURRENT_DATE when converting TIME to DATETIME
+--echo #
+SET timestamp=UNIX_TIMESTAMP('2014-04-15 01:02:03');
+SELECT IF(1,TIME'10:20:30',DATE'2001-01-01');
+SELECT IFNULL(TIME'10:20:30',DATE'2001-01-01');
+SELECT CASE WHEN 1 THEN TIME'10:20:30' ELSE DATE'2001-01-01' END;
+SELECT COALESCE(TIME'10:20:30',DATE'2001-01-01');
+SET timestamp=DEFAULT;
=== modified file 'mysql-test/t/type_datetime.test'
--- mysql-test/t/type_datetime.test 2013-09-14 01:09:36 +0000
+++ mysql-test/t/type_datetime.test 2014-04-15 06:48:16 +0000
@@ -466,10 +466,12 @@ DROP TABLE t1;
#
# MDEV-4281 Assertion `maybe_null && item->null_value' fails in make_sortkey on CASE with different return types, GROUP_CONCAT, GROUP BY
#
+SET timestamp=UNIX_TIMESTAMP('2001-01-01 10:20:30');
create table t1 (d date, t time) engine=myisam;
insert into t1 values ('2000-12-03','22:55:23'),('2008-05-03','10:19:31');
select case when d = '2012-12-12' then d else t end as cond, group_concat( d ) from t1 group by cond;
drop table t1;
+SET timestamp=DEFAULT;
--echo #
--echo # Semantics of the condition <non-nullable datetime field> IS NULL
@@ -573,3 +575,21 @@ DROP TABLE t1,t2;
--echo #
--echo # End of 5.5 tests
--echo #
+
+
+--echo #
+--echo # Start of 10.0 tests
+--echo #
+
+--echo #
+--echo # MDEV-6097 Inconsistent results for CAST(int,decimal,double AS DATETIME)
+--echo #
+SELECT
+ CAST(010203101112 AS DATETIME(1)) AS c1,
+ CAST(010203101112.2 AS DATETIME(1)) AS c2,
+ CAST(010203101112.2+0e0 AS DATETIME(1)) AS c3;
+
+
+--echo #
+--echo # End of 10.0 tests
+--echo #
=== modified file 'mysql-test/t/type_time.test'
--- mysql-test/t/type_time.test 2014-03-06 20:21:25 +0000
+++ mysql-test/t/type_time.test 2014-04-15 06:58:18 +0000
@@ -115,6 +115,12 @@ create table t1 (f1 time , f2 varchar(5)
insert into t1 values ('00:20:01','a'),('00:20:03','b');
select * from t1 force key (f1) where f1 < curdate();
select * from t1 ignore key (f1) where f1 < curdate();
+select * from t1 force key (f1) where f1 > curdate();
+select * from t1 ignore key (f1) where f1 > curdate();
+delete from t1;
+insert into t1 values ('-00:20:01','a'),('-00:20:03','b');
+select * from t1 force key (f1) where f1 < curdate();
+select * from t1 ignore key (f1) where f1 < curdate();
drop table t1;
#
@@ -226,3 +232,36 @@ SELECT CAST('-24:00:00' AS TIME) = (SELE
SELECT '-24:00:00' = (SELECT f1 FROM t1);
DROP TABLE t1;
+--echo #
+--echo # Start of 10.0 tests
+--echo #
+
+--echo #
+--echo # MDEV-4858 Wrong results for a huge unsigned value inserted into a TIME column
+--echo #
+SET sql_mode=traditional;
+CREATE TABLE t1 (a TIME(6));
+--error ER_TRUNCATED_WRONG_VALUE
+INSERT INTO t1 VALUES (CAST(0xFFFFFFFF00000000 AS UNSIGNED));
+SET sql_mode=DEFAULT;
+INSERT INTO t1 VALUES (CAST(0xFFFFFFFF00000000 AS UNSIGNED));
+SELECT * FROM t1;
+DROP TABLE t1;
+SELECT TIME'00:00:00'> CAST(0xFFFFFFFF00000000 AS UNSIGNED);
+
+--echo #
+--echo # MDEV-6100 No warning on CAST(9000000 AS TIME)
+--echo #
+SELECT CAST(9000000 AS TIME);
+
+--echo #
+--echo # MDEV-6102 Comparison between TIME and DATETIME does not use CURRENT_DATE
+--echo #
+SET timestamp=UNIX_TIMESTAMP('2014-04-14 01:02:03');
+SELECT CAST(TIME'10:20:30' AS DATETIME), TIME'10:20:30'=TIMESTAMP'2014-04-14 10:20:30' AS cmp;
+SET timestamp=DEFAULT;
+
+--echo #
+--echo # End of 10.0 tests
+--echo #
+
=== modified file 'sql-common/my_time.c'
--- sql-common/my_time.c 2014-03-06 20:21:25 +0000
+++ sql-common/my_time.c 2014-04-15 04:12:35 +0000
@@ -1214,7 +1214,7 @@ longlong number_to_datetime(longlong nr,
*was_cut= 0;
time_res->time_type=MYSQL_TIMESTAMP_DATE;
- if (nr == 0 || nr >= 10000101000000LL || sec_part)
+ if (nr == 0 || nr >= 10000101000000LL)
{
time_res->time_type=MYSQL_TIMESTAMP_DATETIME;
goto ok;
@@ -1272,7 +1272,11 @@ longlong number_to_datetime(longlong nr,
time_res->minute <= 59 && time_res->second <= 59 &&
sec_part <= TIME_MAX_SECOND_PART &&
!check_date(time_res, nr || sec_part, flags, was_cut))
+ {
+ if (time_res->time_type == MYSQL_TIMESTAMP_DATE && sec_part != 0)
+ *was_cut= MYSQL_TIME_NOTE_TRUNCATED;
return nr;
+ }
/* Don't want to have was_cut get set if NO_ZERO_DATE was violated. */
if (nr || !(flags & TIME_NO_ZERO_DATE))
@@ -1306,10 +1310,10 @@ longlong number_to_datetime(longlong nr,
0 time value is valid, but was possibly truncated
-1 time value is invalid
*/
-int number_to_time(my_bool neg, longlong nr, ulong sec_part,
+int number_to_time(my_bool neg, ulonglong nr, ulong sec_part,
MYSQL_TIME *ltime, int *was_cut)
{
- if (nr > 9999999 && neg == 0)
+ if (nr > 9999999 && nr < 99991231235959ULL && neg == 0)
{
if (number_to_datetime(nr, sec_part, ltime,
TIME_INVALID_DATES, was_cut) < 0)
@@ -1317,7 +1321,7 @@ int number_to_time(my_bool neg, longlong
ltime->year= ltime->month= ltime->day= 0;
ltime->time_type= MYSQL_TIMESTAMP_TIME;
- *was_cut= MYSQL_TIME_WARN_TRUNCATED;
+ *was_cut= MYSQL_TIME_NOTE_TRUNCATED;
return 0;
}
=== modified file 'sql/field.cc'
--- sql/field.cc 2014-03-27 10:55:29 +0000
+++ sql/field.cc 2014-04-15 06:21:32 +0000
@@ -4706,7 +4706,7 @@ int Field_timestamp::store(longlong nr,
{
MYSQL_TIME l_time;
int error;
- ErrConvInteger str(nr);
+ ErrConvInteger str(nr, unsigned_val);
THD *thd= get_thd();
/* We don't want to store invalid or fuzzy datetime values in TIMESTAMP */
@@ -5210,7 +5210,7 @@ int Field_temporal_with_date::store(long
MYSQL_TIME ltime;
longlong tmp;
THD *thd= get_thd();
- ErrConvInteger str(nr);
+ ErrConvInteger str(nr, unsigned_val);
tmp= number_to_datetime(nr, 0, <ime, sql_mode_for_dates(thd), &error);
@@ -5346,8 +5346,10 @@ int Field_time::store(double nr)
int Field_time::store(longlong nr, bool unsigned_val)
{
MYSQL_TIME ltime;
- ErrConvInteger str(nr);
+ ErrConvInteger str(nr, unsigned_val);
int was_cut;
+ if (nr < 0 && unsigned_val)
+ nr= 99991231235959LL + 1;
int have_smth_to_conv= !number_to_time(nr < 0, nr < 0 ? -nr : nr,
0, <ime, &was_cut);
@@ -5705,7 +5707,8 @@ bool Field_year::get_date(MYSQL_TIME *lt
int tmp= (int) ptr[0];
if (tmp || field_length != 4)
tmp+= 1900;
- return int_to_datetime_with_warn(tmp * 10000, ltime, fuzzydate, field_name);
+ return int_to_datetime_with_warn(false, tmp * 10000,
+ ltime, fuzzydate, field_name);
}
=== modified file 'sql/field.h'
--- sql/field.h 2014-03-26 21:25:38 +0000
+++ sql/field.h 2014-04-11 10:32:12 +0000
@@ -94,6 +94,28 @@ inline uint get_set_pack_length(int elem
/**
+ Tests if field type is temporal and has date part,
+ i.e. represents DATE, DATETIME or TIMESTAMP types in SQL.
+
+ @param type Field type, as returned by field->type().
+ @retval true If field type is temporal type with date part.
+ @retval false If field type is not temporal type with date part.
+*/
+inline bool is_temporal_type_with_date(enum_field_types type)
+{
+ switch (type)
+ {
+ case MYSQL_TYPE_DATE:
+ case MYSQL_TYPE_DATETIME:
+ case MYSQL_TYPE_TIMESTAMP:
+ return true;
+ default:
+ return false;
+ }
+}
+
+
+/**
Recognizer for concrete data type (called real_type for some reason),
returning true if it is one of the TIMESTAMP types.
*/
=== modified file 'sql/item.cc'
--- sql/item.cc 2014-03-29 10:33:20 +0000
+++ sql/item.cc 2014-04-15 06:22:29 +0000
@@ -1352,6 +1352,7 @@ bool Item::get_date(MYSQL_TIME *ltime,ul
case INT_RESULT:
{
longlong value= val_int();
+ bool neg= !unsigned_flag && value < 0;
if (field_type() == MYSQL_TYPE_YEAR)
{
if (max_length == 2)
@@ -1363,7 +1364,8 @@ bool Item::get_date(MYSQL_TIME *ltime,ul
}
value*= 10000; /* make it YYYYMMHH */
}
- if (null_value || int_to_datetime_with_warn(value, ltime, fuzzydate,
+ if (null_value || int_to_datetime_with_warn(neg, neg ? -value : value,
+ ltime, fuzzydate,
field_name_or_null()))
goto err;
break;
=== modified file 'sql/item_cmpfunc.cc'
--- sql/item_cmpfunc.cc 2014-03-26 21:25:38 +0000
+++ sql/item_cmpfunc.cc 2014-04-15 02:15:58 +0000
@@ -593,6 +593,11 @@ int Arg_comparator::set_compare_func(Ite
switch (type) {
case TIME_RESULT:
cmp_collation.collation= &my_charset_numeric;
+ if ((a[0]->field_type() == MYSQL_TYPE_TIME &&
+ is_temporal_type_with_date(b[0]->field_type())) ||
+ (b[0]->field_type() == MYSQL_TYPE_TIME &&
+ is_temporal_type_with_date(a[0]->field_type())))
+ convert_time_to_datetime= true;
break;
case ROW_RESULT:
{
@@ -881,7 +886,8 @@ void Arg_comparator::set_datetime_cmp_fu
longlong
get_datetime_value(THD *thd, Item ***item_arg, Item **cache_arg,
- Item *warn_item, bool *is_null)
+ Item *warn_item, bool *is_null,
+ bool convert_time_to_datetime)
{
longlong UNINIT_VAR(value);
Item *item= **item_arg;
@@ -900,9 +906,10 @@ get_datetime_value(THD *thd, Item ***ite
{
MYSQL_TIME ltime;
uint fuzzydate= TIME_FUZZY_DATES | TIME_INVALID_DATES;
- if (f_type == MYSQL_TYPE_TIME)
- fuzzydate|= TIME_TIME_ONLY;
- if (item->get_date(<ime, fuzzydate))
+ if (convert_time_to_datetime ?
+ item->get_date_with_conversion(<ime, fuzzydate) :
+ item->get_date(<ime, fuzzydate |
+ (f_type == MYSQL_TYPE_TIME ? TIME_TIME_ONLY : 0)))
value= 0; /* invalid date */
else
value= pack_time(<ime);
@@ -952,12 +959,14 @@ int Arg_comparator::compare_datetime()
owner->null_value= 1;
/* Get DATE/DATETIME/TIME value of the 'a' item. */
- a_value= get_datetime_value(thd, &a, &a_cache, *b, &a_is_null);
+ a_value= get_datetime_value(thd, &a, &a_cache, *b, &a_is_null,
+ convert_time_to_datetime);
if (a_is_null)
return -1;
/* Get DATE/DATETIME/TIME value of the 'b' item. */
- b_value= get_datetime_value(thd, &b, &b_cache, *a, &b_is_null);
+ b_value= get_datetime_value(thd, &b, &b_cache, *a, &b_is_null,
+ convert_time_to_datetime);
if (b_is_null)
return -1;
@@ -975,10 +984,12 @@ int Arg_comparator::compare_e_datetime()
longlong a_value, b_value;
/* Get DATE/DATETIME/TIME value of the 'a' item. */
- a_value= get_datetime_value(thd, &a, &a_cache, *b, &a_is_null);
+ a_value= get_datetime_value(thd, &a, &a_cache, *b, &a_is_null,
+ convert_time_to_datetime);
/* Get DATE/DATETIME/TIME value of the 'b' item. */
- b_value= get_datetime_value(thd, &b, &b_cache, *a, &b_is_null);
+ b_value= get_datetime_value(thd, &b, &b_cache, *a, &b_is_null,
+ convert_time_to_datetime);
return a_is_null || b_is_null ? a_is_null == b_is_null
: a_value == b_value;
}
@@ -2289,10 +2300,11 @@ longlong Item_func_between::val_int()
longlong value, a, b;
Item *cache, **ptr;
bool value_is_null, a_is_null, b_is_null;
+ bool is_datetime= compare_as_dates->field_type() != MYSQL_TYPE_TIME;
ptr= &args[0];
value= get_datetime_value(thd, &ptr, &cache, compare_as_dates,
- &value_is_null);
+ &value_is_null, is_datetime);
if (ptr != &args[0])
thd->change_item_tree(&args[0], *ptr);
@@ -2300,12 +2312,14 @@ longlong Item_func_between::val_int()
return 0;
ptr= &args[1];
- a= get_datetime_value(thd, &ptr, &cache, compare_as_dates, &a_is_null);
+ a= get_datetime_value(thd, &ptr, &cache, compare_as_dates, &a_is_null,
+ is_datetime);
if (ptr != &args[1])
thd->change_item_tree(&args[1], *ptr);
ptr= &args[2];
- b= get_datetime_value(thd, &ptr, &cache, compare_as_dates, &b_is_null);
+ b= get_datetime_value(thd, &ptr, &cache, compare_as_dates, &b_is_null,
+ is_datetime);
if (ptr != &args[2])
thd->change_item_tree(&args[2], *ptr);
@@ -2559,9 +2573,9 @@ Item_func_ifnull::str_op(String *str)
bool Item_func_ifnull::date_op(MYSQL_TIME *ltime, uint fuzzydate)
{
DBUG_ASSERT(fixed == 1);
- if (!args[0]->get_date(ltime, fuzzydate & ~TIME_FUZZY_DATES))
+ if (!args[0]->get_date_with_conversion(ltime, fuzzydate & ~TIME_FUZZY_DATES))
return (null_value= false);
- if (!args[1]->get_date(ltime, fuzzydate & ~TIME_FUZZY_DATES))
+ if (!args[1]->get_date_with_conversion(ltime, fuzzydate & ~TIME_FUZZY_DATES))
return (null_value= false);
bzero((char*) ltime,sizeof(*ltime));
return null_value= !(fuzzydate & TIME_FUZZY_DATES);
@@ -2752,7 +2766,7 @@ bool Item_func_if::date_op(MYSQL_TIME *l
{
DBUG_ASSERT(fixed == 1);
Item *arg= args[0]->val_bool() ? args[1] : args[2];
- return (null_value= arg->get_date(ltime, fuzzydate));
+ return (null_value= arg->get_date_with_conversion(ltime, fuzzydate));
}
@@ -2997,7 +3011,7 @@ bool Item_func_case::date_op(MYSQL_TIME
Item *item= find_item(&dummy_str);
if (!item)
return (null_value= true);
- return (null_value= item->get_date(ltime, fuzzydate));
+ return (null_value= item->get_date_with_conversion(ltime, fuzzydate));
}
@@ -3315,7 +3329,8 @@ bool Item_func_coalesce::date_op(MYSQL_T
null_value= 0;
for (uint i= 0; i < arg_count; i++)
{
- bool res= args[i]->get_date(ltime, fuzzydate & ~TIME_FUZZY_DATES);
+ bool res= args[i]->get_date_with_conversion(ltime,
+ fuzzydate & ~TIME_FUZZY_DATES);
if (!args[i]->null_value)
return res;
}
=== modified file 'sql/item_cmpfunc.h'
--- sql/item_cmpfunc.h 2014-03-26 21:25:38 +0000
+++ sql/item_cmpfunc.h 2014-04-15 02:12:29 +0000
@@ -42,6 +42,7 @@ class Arg_comparator: public Sql_alloc
arg_cmp_func func;
Item_result_field *owner;
bool set_null; // TRUE <=> set owner->null_value
+ bool convert_time_to_datetime;
Arg_comparator *comparators; // used only for compare_row()
double precision;
/* Fields used in DATE/DATETIME comparison. */
@@ -53,9 +54,11 @@ class Arg_comparator: public Sql_alloc
/* Allow owner function to use string buffers. */
String value1, value2;
- Arg_comparator(): set_null(TRUE), comparators(0), thd(0),
+ Arg_comparator(): set_null(TRUE), convert_time_to_datetime(false),
+ comparators(0), thd(0),
a_cache(0), b_cache(0) {};
Arg_comparator(Item **a1, Item **a2): a(a1), b(a2), set_null(TRUE),
+ convert_time_to_datetime(false),
comparators(0), thd(0), a_cache(0), b_cache(0) {};
int set_compare_func(Item_result_field *owner, Item_result type);
@@ -2068,7 +2071,8 @@ inline Item *and_conds(Item *a, Item *b)
Item *and_expressions(Item *a, Item *b, Item **org_item);
longlong get_datetime_value(THD *thd, Item ***item_arg, Item **cache_arg,
- Item *warn_item, bool *is_null);
+ Item *warn_item, bool *is_null,
+ bool time_to_datetime= false);
bool get_mysql_time_from_str(THD *thd, String *str, timestamp_type warn_type,
=== modified file 'sql/item_func.cc'
--- sql/item_func.cc 2014-03-26 21:25:38 +0000
+++ sql/item_func.cc 2014-04-15 06:23:06 +0000
@@ -1116,7 +1116,9 @@ bool Item_func_hybrid_result_type::get_d
case INT_RESULT:
{
longlong value= int_op();
- if (null_value || int_to_datetime_with_warn(value, ltime, fuzzydate,
+ bool neg= !unsigned_flag && value < 0;
+ if (null_value || int_to_datetime_with_warn(neg, neg ? -value : value,
+ ltime, fuzzydate,
field_name_or_null()))
goto err;
break;
=== modified file 'sql/item_strfunc.cc'
--- sql/item_strfunc.cc 2014-03-28 19:18:33 +0000
+++ sql/item_strfunc.cc 2014-04-15 06:40:07 +0000
@@ -5094,8 +5094,10 @@ bool Item_dyncol_get::get_date(MYSQL_TIM
case DYN_COL_UINT:
if (signed_value || val.x.ulong_value <= LONGLONG_MAX)
{
- if (int_to_datetime_with_warn(val.x.ulong_value, ltime, fuzzy_date,
- 0 /* TODO */))
+ bool neg= val.x.ulong_value > LONGLONG_MAX;
+ if (int_to_datetime_with_warn(neg, neg ? -val.x.ulong_value :
+ val.x.ulong_value,
+ ltime, fuzzy_date, 0 /* TODO */))
goto null;
return 0;
}
=== modified file 'sql/item_timefunc.cc'
--- sql/item_timefunc.cc 2014-03-06 20:21:25 +0000
+++ sql/item_timefunc.cc 2014-04-11 07:56:55 +0000
@@ -1298,6 +1298,15 @@ bool get_interval_value(Item *args,inter
if (!(val= args->val_decimal(&decimal_value)))
return true;
interval->neg= my_decimal2seconds(val, &second, &second_part);
+ if (second == LONGLONG_MAX)
+ {
+ ErrConvDecimal err(val);
+ push_warning_printf(current_thd, Sql_condition::WARN_LEVEL_WARN,
+ ER_TRUNCATED_WRONG_VALUE,
+ ER(ER_TRUNCATED_WRONG_VALUE), "DECIMAL",
+ err.ptr());
+ return true;
+ }
interval->second= second;
interval->second_part= second_part;
return false;
=== modified file 'sql/sql_error.h'
--- sql/sql_error.h 2013-11-20 11:05:39 +0000
+++ sql/sql_error.h 2014-04-15 02:54:30 +0000
@@ -575,11 +575,16 @@ class ErrConvString : public ErrConv
class ErrConvInteger : public ErrConv
{
- longlong num;
+ longlong m_value;
+ bool m_unsigned;
public:
- ErrConvInteger(longlong num_arg) : ErrConv(), num(num_arg) {}
+ ErrConvInteger(longlong num_arg, bool unsigned_flag= false) :
+ ErrConv(), m_value(num_arg), m_unsigned(unsigned_flag) {}
const char *ptr() const
- { return llstr(num, err_buffer); }
+ {
+ return m_unsigned ? ullstr(m_value, err_buffer) :
+ llstr(m_value, err_buffer);
+ }
};
class ErrConvDouble: public ErrConv
=== modified file 'sql/sql_time.cc'
--- sql/sql_time.cc 2014-03-23 10:22:44 +0000
+++ sql/sql_time.cc 2014-04-15 06:28:59 +0000
@@ -358,20 +358,23 @@ static bool number_to_time_with_warn(boo
int was_cut;
longlong res;
enum_field_types f_type;
+ bool have_warnings;
if (fuzzydate & TIME_TIME_ONLY)
{
fuzzydate= TIME_TIME_ONLY; // clear other flags
f_type= MYSQL_TYPE_TIME;
res= number_to_time(neg, nr, sec_part, ltime, &was_cut);
+ have_warnings= MYSQL_TIME_WARN_HAVE_WARNINGS(was_cut);
}
else
{
f_type= MYSQL_TYPE_DATETIME;
res= neg ? -1 : number_to_datetime(nr, sec_part, ltime, fuzzydate, &was_cut);
+ have_warnings= was_cut && (fuzzydate & TIME_NO_ZERO_IN_DATE);
}
- if (res < 0 || (was_cut && (fuzzydate & TIME_NO_ZERO_IN_DATE)))
+ if (res < 0 || have_warnings)
{
make_truncated_value_warning(current_thd,
Sql_condition::WARN_LEVEL_WARN, str,
@@ -414,12 +417,11 @@ bool decimal_to_datetime_with_warn(const
}
-bool int_to_datetime_with_warn(longlong value, MYSQL_TIME *ltime,
+bool int_to_datetime_with_warn(bool neg, ulonglong value, MYSQL_TIME *ltime,
ulonglong fuzzydate, const char *field_name)
{
- const ErrConvInteger str(value);
- bool neg= value < 0;
- return number_to_time_with_warn(neg, neg ? -value : value, 0, ltime,
+ const ErrConvInteger str(neg ? -value : value, !neg);
+ return number_to_time_with_warn(neg, value, 0, ltime,
fuzzydate, &str, field_name);
}
=== modified file 'sql/sql_time.h'
--- sql/sql_time.h 2014-03-06 20:21:25 +0000
+++ sql/sql_time.h 2014-04-15 06:16:57 +0000
@@ -45,7 +45,7 @@ bool double_to_datetime_with_warn(double
bool decimal_to_datetime_with_warn(const my_decimal *value, MYSQL_TIME *ltime,
ulonglong fuzzydate,
const char *name);
-bool int_to_datetime_with_warn(longlong value, MYSQL_TIME *ltime,
+bool int_to_datetime_with_warn(bool neg, ulonglong value, MYSQL_TIME *ltime,
ulonglong fuzzydate,
const char *name);
Follow ups
References
