← Back to team overview

maria-developers team mailing list archive

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,