maria-developers team mailing list archive
-
maria-developers team
-
Mailing list archive
-
Message #07328
Review request[major 5.3/5.5 bug]: MDEV-4511 Assertion `scale <= precision' fails on GROUP BY TIMEDIFF with incorrect types, , Agile Board, , Export
Hello Sergei,
A few weeks ago we fixed this bug for 10.0 by changing temporal items
not to have decimals=NOT_FIXED_DEC (more MySQL-5.6 compatibility),
and agreed to have a separate patch for the pre-10.0 branches.
This patch is for 5.3 and 5.5 only.
It back-ports the mdev-4511 tests from 10.0 as is.
But it does not touch the NOT_FIXED_DEC behavior of the temporal items
and fixes the problem in a different way.
Greetings.
=== modified file 'mysql-test/r/func_time.result'
--- mysql-test/r/func_time.result 2014-01-27 09:15:40 +0000
+++ mysql-test/r/func_time.result 2014-06-02 13:35:14 +0000
@@ -1914,6 +1914,74 @@ SELECT 1 FROM DUAL WHERE MINUTE(TIMEDIFF
SELECT 1 FROM DUAL WHERE SECOND(TIMEDIFF(NULL, '12:12:12'));
1
#
+# MDEV-4511 Assertion `scale <= precision' fails on GROUP BY TIMEDIFF with incorrect types
+#
+CREATE TABLE t1 (a DATE) ENGINE=MyISAM;
+INSERT INTO t1 VALUES ('2005-05-04'),('2000-02-23');
+SELECT a FROM t1 GROUP BY TIMEDIFF('2004-06-12',a) * 1;
+a
+2005-05-04
+Warnings:
+Warning 1292 Truncated incorrect time value: '2004-06-12'
+Warning 1292 Truncated incorrect time value: '2004-06-12'
+DROP TABLE t1;
+CREATE TABLE t1 (a DATE) ENGINE=MyISAM;
+INSERT INTO t1 VALUES ('2005-05-04'),('2000-02-23');
+SELECT a FROM t1 GROUP BY ADDTIME(a,'10')*1;
+a
+2000-02-23
+2005-05-04
+DROP TABLE t1;
+CREATE TABLE t1 (a DATE) ENGINE=MyISAM;
+INSERT INTO t1 VALUES ('2005-05-04'),('2000-02-23');
+SELECT * FROM t1 GROUP BY SEC_TO_TIME(concat(a,'10'))*1;
+a
+2000-02-23
+2005-05-04
+DROP TABLE t1;
+CREATE TABLE t1 (a DATE) ENGINE=MyISAM;
+INSERT INTO t1 VALUES ('2005-05-04'),('2000-02-23');
+SELECT * FROM t1 GROUP BY ADDTIME(timestamp('2001-01-01 00:00:00'),CAST(a AS SIGNED)&0xF)*1;
+a
+2005-05-04
+2000-02-23
+DROP TABLE t1;
+CREATE TABLE t1 (a DATE) ENGINE=MyISAM;
+INSERT INTO t1 VALUES ('2005-05-04'),('2000-02-23');
+SELECT * FROM t1 GROUP BY STR_TO_DATE(a,concat('%Y-%m-%d.%f',if(rand(),'','')))*1;
+a
+2000-02-23
+2005-05-04
+DROP TABLE t1;
+CREATE TABLE t1 AS SELECT
+STR_TO_DATE('2001-01-01', '%Y-%m-%d') AS date_only,
+STR_TO_DATE('10:10:10', '%H:%i:%s') AS time_only,
+STR_TO_DATE('10:10:10.123', '%H:%i:%s.%f') AS time_microsecond,
+STR_TO_DATE('2001-01-01 10:10:10', '%Y-%m-%d %H:%i:%s') AS date_time,
+STR_TO_DATE('2001-01-01 10:10:10.123', '%Y-%m-%d %H:%i:%s.%f') AS date_time_microsecond;
+SHOW COLUMNS FROM t1;
+Field Type Null Key Default Extra
+date_only date YES NULL
+time_only time YES NULL
+time_microsecond time(6) YES NULL
+date_time datetime YES NULL
+date_time_microsecond datetime(6) YES NULL
+DROP TABLE t1;
+CREATE TABLE t1 AS SELECT
+SEC_TO_TIME(1)+0.1,
+SEC_TO_TIME(1.1)+0.1,
+SEC_TO_TIME(1.12)+0.1,
+SEC_TO_TIME(1.123456)+0.1,
+SEC_TO_TIME(1.1234567)+0.1;
+SHOW COLUMNS FROM t1;
+Field Type Null Key Default Extra
+SEC_TO_TIME(1)+0.1 decimal(12,1) YES NULL
+SEC_TO_TIME(1.1)+0.1 decimal(13,1) YES NULL
+SEC_TO_TIME(1.12)+0.1 decimal(14,2) YES NULL
+SEC_TO_TIME(1.123456)+0.1 decimal(18,6) YES NULL
+SEC_TO_TIME(1.1234567)+0.1 decimal(18,6) YES NULL
+DROP TABLE t1;
+#
# MDEV-4635 Crash in UNIX_TIMESTAMP(STR_TO_DATE('2020','%Y'))
#
SET TIME_ZONE='+02:00';
=== modified file 'mysql-test/t/func_time.test'
--- mysql-test/t/func_time.test 2014-01-27 09:15:40 +0000
+++ mysql-test/t/func_time.test 2014-06-02 13:34:27 +0000
@@ -1157,6 +1157,52 @@ SELECT 1 FROM DUAL WHERE SECOND(TIMEDIFF
--echo #
+--echo # MDEV-4511 Assertion `scale <= precision' fails on GROUP BY TIMEDIFF with incorrect types
+--echo #
+CREATE TABLE t1 (a DATE) ENGINE=MyISAM;
+INSERT INTO t1 VALUES ('2005-05-04'),('2000-02-23');
+SELECT a FROM t1 GROUP BY TIMEDIFF('2004-06-12',a) * 1;
+DROP TABLE t1;
+
+CREATE TABLE t1 (a DATE) ENGINE=MyISAM;
+INSERT INTO t1 VALUES ('2005-05-04'),('2000-02-23');
+SELECT a FROM t1 GROUP BY ADDTIME(a,'10')*1;
+DROP TABLE t1;
+
+CREATE TABLE t1 (a DATE) ENGINE=MyISAM;
+INSERT INTO t1 VALUES ('2005-05-04'),('2000-02-23');
+SELECT * FROM t1 GROUP BY SEC_TO_TIME(concat(a,'10'))*1;
+DROP TABLE t1;
+
+CREATE TABLE t1 (a DATE) ENGINE=MyISAM;
+INSERT INTO t1 VALUES ('2005-05-04'),('2000-02-23');
+SELECT * FROM t1 GROUP BY ADDTIME(timestamp('2001-01-01 00:00:00'),CAST(a AS SIGNED)&0xF)*1;
+DROP TABLE t1;
+
+CREATE TABLE t1 (a DATE) ENGINE=MyISAM;
+INSERT INTO t1 VALUES ('2005-05-04'),('2000-02-23');
+SELECT * FROM t1 GROUP BY STR_TO_DATE(a,concat('%Y-%m-%d.%f',if(rand(),'','')))*1;
+DROP TABLE t1;
+
+CREATE TABLE t1 AS SELECT
+ STR_TO_DATE('2001-01-01', '%Y-%m-%d') AS date_only,
+ STR_TO_DATE('10:10:10', '%H:%i:%s') AS time_only,
+ STR_TO_DATE('10:10:10.123', '%H:%i:%s.%f') AS time_microsecond,
+ STR_TO_DATE('2001-01-01 10:10:10', '%Y-%m-%d %H:%i:%s') AS date_time,
+ STR_TO_DATE('2001-01-01 10:10:10.123', '%Y-%m-%d %H:%i:%s.%f') AS date_time_microsecond;
+SHOW COLUMNS FROM t1;
+DROP TABLE t1;
+
+CREATE TABLE t1 AS SELECT
+ SEC_TO_TIME(1)+0.1,
+ SEC_TO_TIME(1.1)+0.1,
+ SEC_TO_TIME(1.12)+0.1,
+ SEC_TO_TIME(1.123456)+0.1,
+ SEC_TO_TIME(1.1234567)+0.1;
+SHOW COLUMNS FROM t1;
+DROP TABLE t1;
+
+--echo #
--echo # MDEV-4635 Crash in UNIX_TIMESTAMP(STR_TO_DATE('2020','%Y'))
--echo #
SET TIME_ZONE='+02:00';
=== modified file 'sql/field.h'
--- sql/field.h 2014-03-16 20:03:01 +0000
+++ sql/field.h 2014-06-03 07:43:08 +0000
@@ -65,6 +65,29 @@ inline bool is_temporal_type(enum_field_
return mysql_type_to_time_type(type) != MYSQL_TIMESTAMP_ERROR;
}
+
+/**
+ Tests if field type is temporal and has time part,
+ i.e. represents TIME, DATETIME or TIMESTAMP types in SQL.
+
+ @param type Field type, as returned by field->type().
+ @retval true If field type is temporal type with time part.
+ @retval false If field type is not temporal type with time part.
+*/
+inline bool is_temporal_type_with_time(enum_field_types type)
+{
+ switch (type)
+ {
+ case MYSQL_TYPE_TIME:
+ case MYSQL_TYPE_DATETIME:
+ case MYSQL_TYPE_TIMESTAMP:
+ return true;
+ default:
+ return false;
+ }
+}
+
+
/*
Virtual_column_info is the class to contain additional
characteristics that is specific for a virtual/computed
=== modified file 'sql/item.h'
--- sql/item.h 2014-03-16 20:03:01 +0000
+++ sql/item.h 2014-06-03 09:16:18 +0000
@@ -909,9 +909,22 @@ class Item {
virtual cond_result eq_cmp_result() const { return COND_OK; }
inline uint float_length(uint decimals_par) const
{ return decimals != NOT_FIXED_DEC ? (DBL_DIG+2+decimals_par) : DBL_DIG+8;}
+ /* Returns total number of decimal digits */
virtual uint decimal_precision() const;
+ /* Returns the number of integer part digits only */
inline int decimal_int_part() const
{ return my_decimal_int_part(decimal_precision(), decimals); }
+ /*
+ Returns the number of fractional digits only.
+ NOT_FIXED_DEC is replaced to the maximum possible number
+ of fractional digits, taking into account the data type.
+ */
+ uint decimal_scale() const
+ {
+ return decimals < NOT_FIXED_DEC ? decimals :
+ is_temporal_type_with_time(field_type()) ?
+ TIME_SECOND_PART_DIGITS : DECIMAL_MAX_SCALE;
+ }
/**
TIME or DATETIME precision of the item: 0..6
*/
=== modified file 'sql/item_func.cc'
--- sql/item_func.cc 2014-03-16 20:03:01 +0000
+++ sql/item_func.cc 2014-06-02 12:35:23 +0000
@@ -1566,7 +1566,7 @@ void Item_func_mul::result_precision()
unsigned_flag= args[0]->unsigned_flag | args[1]->unsigned_flag;
else
unsigned_flag= args[0]->unsigned_flag & args[1]->unsigned_flag;
- decimals= min(args[0]->decimals + args[1]->decimals, DECIMAL_MAX_SCALE);
+ decimals= min(args[0]->decimal_scale() + args[1]->decimal_scale(), DECIMAL_MAX_SCALE);
uint est_prec = args[0]->decimal_precision() + args[1]->decimal_precision();
uint precision= min(est_prec, DECIMAL_MAX_PRECISION);
max_length= my_decimal_precision_to_length_no_truncation(precision, decimals,