List:Commits« Previous MessageNext Message »
From:Alexander Barkov Date:February 17 2012 1:56pm
Subject:bzr push into mysql-trunk branch (alexander.barkov:3922 to 3923) Bug#13375823
View as plain text  
 3923 Alexander Barkov	2012-02-17
            Bug#13375823 - FSP(DECIMAL) RESULT DIFFERENCE WITH QUERY USING UNION ALL
            
            
            1. Introducing Item::temporal_with_date_as_number_result_type()
            and fixing Item_func_min_max::fix_length_and_dec() to
            use the new method so DATE, DATETIME, TIME data type arguments
            passed to GREATEST/LEAST now report themself as DECIMAL_RESULT
            rather than STRING_RESULT.
            
            In the previous code they reported themself as STRING_RESULT,
            so while mixing a DATETIME argument with a INT/DECIMAL argument,
            item_cmp_type() chose REAL_RESULT for cmp_type and therefore
            some precision got lost:
            
            CREATE TABLE t1 (a DATETIME(6));
            INSERT INTO t1 VALUES ('2001-01-01 01:01:01.123456');
            SELECT GREATEST(a,10) FROM t1; --> 20010101010101.125000
            
            Now item_cmp_type() sets cmp_type to DECIMAL_RESULT
            (or INT_RESULT in case of no fractional digits)
            and preserves the full precision.
            
            This change also makes Item_func_min_max::save_in_field()
            go through Field_longstr::store(my_decimal *dec) rather
            than Field_longstr::store(double nr) when processing the
            UNION query from the bug report.
            Decimal routine (unlike double routine) gives control
            over the amount of fractional digits stored into the
            temporary UNION field.
            
            2. Fixing Item_func_min_max::result_type() to return STRING_RESULT
            when we compare as dates, because a lot of the code assumes that
            temporal types have STRING_RESULT.
            For example, in create_table_from_items() we need to use
            item->tmp_table_field_from_field_type() rather than
            item->tmp_table_field() to make
            "CREATE TABLE t1 AS SELECT LEAST(datetime_expr1,datetime_expr2)"
            create a DATETIME field.
            
            3. Changing Item_func_min_max::val_decimal() to set the number
            of fractional digits according to "decimals",
            so Field_longstr::store(my_decimal *dec) writes fixed amount
            of fractional digits, independingly from which argument the value
            came from.

    modified:
      mysql-test/r/type_newdecimal.result
      mysql-test/r/type_temporal_fractional.result
      mysql-test/t/type_newdecimal.test
      mysql-test/t/type_temporal_fractional.test
      sql/item.h
      sql/item_func.cc
      sql/item_func.h
 3922 Vasil Dimov	2012-02-17 [merge]
      Merge mysql-5.5 -> mysql-trunk
      
      In addition to emitting a warning when ignore-builtin-innodb is used, in
      5.6 we ignore that option and start with InnoDB even when it is specified.

    modified:
      sql/mysqld.cc
      sql/sql_plugin.cc
      sql/sys_vars.cc
=== modified file 'mysql-test/r/type_newdecimal.result'
--- a/mysql-test/r/type_newdecimal.result	2011-11-21 11:58:25 +0000
+++ b/mysql-test/r/type_newdecimal.result	2012-02-17 13:55:18 +0000
@@ -1988,3 +1988,27 @@ SELECT d1 * d2 FROM t1;
 d1 * d2
 0
 DROP TABLE t1;
+#
+# Start of 5.6 tests
+#
+#
+# Bug#13375823 - FSP(DECIMAL) RESULT DIFFERENCE WITH QUERY USING UNION ALL
+#
+CREATE TABLE t1 (a DECIMAL(20,3) NOT NULL);
+INSERT INTO t1 VALUES (20000716055804.035);
+INSERT INTO t1 VALUES (20080821000000.000);
+INSERT INTO t1 VALUES (0);
+SELECT GREATEST(a, 1323) FROM t1;
+GREATEST(a, 1323)
+20000716055804.035
+20080821000000.000
+1323.000
+(SELECT GREATEST(a, 1323) FROM t1) UNION ALL (SELECT GREATEST(a, 1323) FROM t1 LIMIT 0);
+GREATEST(a, 1323)
+20000716055804.035
+20080821000000.000
+1323.000
+DROP TABLE t1;
+#
+# End of 5.6 tests
+#

=== modified file 'mysql-test/r/type_temporal_fractional.result'
--- a/mysql-test/r/type_temporal_fractional.result	2012-02-13 06:26:52 +0000
+++ b/mysql-test/r/type_temporal_fractional.result	2012-02-17 13:55:18 +0000
@@ -17178,4 +17178,82 @@ Warning	1292	Incorrect datetime value: '
 Warning	1292	Incorrect datetime value: '-0.1111111111'
 Warning	1292	Incorrect datetime value: '-0.1111111'
 Warning	1292	Incorrect datetime value: '-0.1111111111'
+#
+# Bug#13375823 - FSP(DECIMAL) RESULT DIFFERENCE WITH QUERY USING UNION ALL
+#
+CREATE TABLE t1 (a DATETIME(3) NOT NULL);
+INSERT INTO t1 VALUES ('2000-07-16 05:58:04.035');
+INSERT INTO t1 VALUES ('2008-08-21 00:00:00.000');
+INSERT INTO t1 VALUES ('0000-00-00 00:00:00.000');
+# Testing INT number
+SELECT GREATEST(a, 1323) FROM t1;
+GREATEST(a, 1323)
+20000716055804.035
+20080821000000.000
+1323.000
+(SELECT GREATEST(a, 1323) FROM t1) UNION ALL (SELECT GREATEST(a, 1323) FROM t1 LIMIT 0);
+GREATEST(a, 1323)
+20000716055804.035
+20080821000000.000
+1323.000
+SELECT LEAST(a, 1323) FROM t1;
+LEAST(a, 1323)
+1323.000
+1323.000
+0.000
+(SELECT LEAST(a, 1323) FROM t1) UNION ALL (SELECT LEAST(a, 1323) FROM t1 LIMIT 0);
+LEAST(a, 1323)
+1323.000
+1323.000
+0.000
+# Testing DECIMAL number 
+SELECT GREATEST(a, 1323.123456) FROM t1;
+GREATEST(a, 1323.123456)
+20000716055804.035000
+20080821000000.000000
+1323.123456
+(SELECT GREATEST(a, 1323.123456) FROM t1) UNION ALL (SELECT GREATEST(a, 1323.123456) FROM t1 LIMIT 0);
+GREATEST(a, 1323.123456)
+20000716055804.035000
+20080821000000.000000
+1323.123456
+SELECT LEAST(a, 1323.123456) FROM t1;
+LEAST(a, 1323.123456)
+1323.123456
+1323.123456
+0.000000
+(SELECT LEAST(a, 1323.123456) FROM t1) UNION ALL (SELECT LEAST(a, 1323.123456) FROM t1 LIMIT 0);
+LEAST(a, 1323.123456)
+1323.123456
+1323.123456
+0.000000
+# Testing REAL number
+SELECT GREATEST(a, 1323e0) FROM t1;
+GREATEST(a, 1323e0)
+20000716055804.035
+20080821000000
+1323
+(SELECT GREATEST(a, 1323e0) FROM t1) UNION ALL (SELECT GREATEST(a, 1323e0) FROM t1 LIMIT 0);
+GREATEST(a, 1323e0)
+20000716055804.035
+20080821000000
+1323
+SELECT LEAST(a, 1323e0) FROM t1;
+LEAST(a, 1323e0)
+1323
+1323
+0
+(SELECT LEAST(a, 1323e0) FROM t1) UNION ALL (SELECT LEAST(a, 1323e0) FROM t1 LIMIT 0);
+LEAST(a, 1323e0)
+1323
+1323
+0
+DROP TABLE t1;
+# Make sure precision of 6 fractional digits does not get lost
+CREATE TABLE t1 (a DATETIME(6));
+INSERT INTO t1 VALUES ('2001-01-01 01:01:01.123456');
+SELECT GREATEST(a,10), LEAST(a,10) FROM t1;
+GREATEST(a,10)	LEAST(a,10)
+20010101010101.123456	10.000000
+DROP TABLE t1;
 # End of 5.6 tests

=== modified file 'mysql-test/t/type_newdecimal.test'
--- a/mysql-test/t/type_newdecimal.test	2011-10-14 09:40:10 +0000
+++ b/mysql-test/t/type_newdecimal.test	2012-02-17 13:55:18 +0000
@@ -1570,3 +1570,22 @@ SELECT d1 * d2 FROM t1;
 
 DROP TABLE t1;
 
+
+--echo #
+--echo # Start of 5.6 tests
+--echo #
+
+--echo #
+--echo # Bug#13375823 - FSP(DECIMAL) RESULT DIFFERENCE WITH QUERY USING UNION ALL
+--echo #
+CREATE TABLE t1 (a DECIMAL(20,3) NOT NULL);
+INSERT INTO t1 VALUES (20000716055804.035);
+INSERT INTO t1 VALUES (20080821000000.000);
+INSERT INTO t1 VALUES (0);
+SELECT GREATEST(a, 1323) FROM t1;
+(SELECT GREATEST(a, 1323) FROM t1) UNION ALL (SELECT GREATEST(a, 1323) FROM t1 LIMIT 0);
+DROP TABLE t1;
+
+--echo #
+--echo # End of 5.6 tests
+--echo #

=== modified file 'mysql-test/t/type_temporal_fractional.test'
--- a/mysql-test/t/type_temporal_fractional.test	2012-02-02 10:58:18 +0000
+++ b/mysql-test/t/type_temporal_fractional.test	2012-02-17 13:55:18 +0000
@@ -7635,4 +7635,34 @@ SELECT
   CAST(-0.1111111111 AS DATE);
 
 
+--echo #
+--echo # Bug#13375823 - FSP(DECIMAL) RESULT DIFFERENCE WITH QUERY USING UNION ALL
+--echo #
+CREATE TABLE t1 (a DATETIME(3) NOT NULL);
+INSERT INTO t1 VALUES ('2000-07-16 05:58:04.035');
+INSERT INTO t1 VALUES ('2008-08-21 00:00:00.000');
+INSERT INTO t1 VALUES ('0000-00-00 00:00:00.000');
+--echo # Testing INT number
+SELECT GREATEST(a, 1323) FROM t1;
+(SELECT GREATEST(a, 1323) FROM t1) UNION ALL (SELECT GREATEST(a, 1323) FROM t1 LIMIT 0);
+SELECT LEAST(a, 1323) FROM t1;
+(SELECT LEAST(a, 1323) FROM t1) UNION ALL (SELECT LEAST(a, 1323) FROM t1 LIMIT 0);
+--echo # Testing DECIMAL number 
+SELECT GREATEST(a, 1323.123456) FROM t1;
+(SELECT GREATEST(a, 1323.123456) FROM t1) UNION ALL (SELECT GREATEST(a, 1323.123456) FROM t1 LIMIT 0);
+SELECT LEAST(a, 1323.123456) FROM t1;
+(SELECT LEAST(a, 1323.123456) FROM t1) UNION ALL (SELECT LEAST(a, 1323.123456) FROM t1 LIMIT 0);
+--echo # Testing REAL number
+SELECT GREATEST(a, 1323e0) FROM t1;
+(SELECT GREATEST(a, 1323e0) FROM t1) UNION ALL (SELECT GREATEST(a, 1323e0) FROM t1 LIMIT 0);
+SELECT LEAST(a, 1323e0) FROM t1;
+(SELECT LEAST(a, 1323e0) FROM t1) UNION ALL (SELECT LEAST(a, 1323e0) FROM t1 LIMIT 0);
+DROP TABLE t1;
+
+--echo # Make sure precision of 6 fractional digits does not get lost
+CREATE TABLE t1 (a DATETIME(6));
+INSERT INTO t1 VALUES ('2001-01-01 01:01:01.123456');
+SELECT GREATEST(a,10), LEAST(a,10) FROM t1;
+DROP TABLE t1;
+
 --echo # End of 5.6 tests

=== modified file 'sql/item.h'
--- a/sql/item.h	2012-02-02 13:44:26 +0000
+++ b/sql/item.h	2012-02-17 13:55:18 +0000
@@ -666,6 +666,15 @@ public:
       return REAL_RESULT; 
     return result_type();
   }
+  /**
+    Similar to result_type() but makes DATE, DATETIME, TIMESTAMP
+    pretend to be numbers rather than strings.
+  */
+  inline enum Item_result temporal_with_date_as_number_result_type() const
+  {
+    return is_temporal_with_date() ? 
+           (decimals ? DECIMAL_RESULT : INT_RESULT) : result_type();
+  }
   virtual Item_result cast_to_int_type() const { return result_type(); }
   virtual enum_field_types string_field_type() const;
   virtual enum_field_types field_type() const;

=== modified file 'sql/item_func.cc'
--- a/sql/item_func.cc	2012-01-30 06:59:39 +0000
+++ b/sql/item_func.cc	2012-02-17 13:55:18 +0000
@@ -2702,12 +2702,13 @@ double Item_func_units::val_real()
 
 void Item_func_min_max::fix_length_and_dec()
 {
+  uint string_arg_count= 0;
   int max_int_part=0;
   bool datetime_found= FALSE;
   decimals=0;
   max_length=0;
   maybe_null=0;
-  cmp_type=args[0]->result_type();
+  cmp_type= args[0]->temporal_with_date_as_number_result_type();
 
   for (uint i=0 ; i < arg_count ; i++)
   {
@@ -2716,7 +2717,10 @@ void Item_func_min_max::fix_length_and_d
     set_if_bigger(max_int_part, args[i]->decimal_int_part());
     if (args[i]->maybe_null)
       maybe_null=1;
-    cmp_type=item_cmp_type(cmp_type,args[i]->result_type());
+    cmp_type= item_cmp_type(cmp_type,
+                            args[i]->temporal_with_date_as_number_result_type());
+    if (args[i]->result_type() == STRING_RESULT)
+     string_arg_count++;
     if (args[i]->result_type() != ROW_RESULT &&
         args[i]->is_temporal_with_date())
     {
@@ -2725,8 +2729,10 @@ void Item_func_min_max::fix_length_and_d
         datetime_item= args[i];
     }
   }
-  if (cmp_type == STRING_RESULT)
+  
+  if (string_arg_count == arg_count)
   {
+    // We compare as strings only if all arguments were strings.
     agg_arg_charsets_for_string_result_with_comparison(collation,
                                                        args, arg_count);
     if (datetime_found)
@@ -3105,6 +3111,15 @@ my_decimal *Item_func_min_max::val_decim
       break;
     }
   }
+  
+  if (res)
+  {
+    /*
+      Need this to make val_str() always return fixed
+      number of fractional digits, according to "decimals".
+    */
+    my_decimal_round(E_DEC_FATAL_ERROR, res, decimals, false, res);
+  }
   return res;
 }
 

=== modified file 'sql/item_func.h'
--- a/sql/item_func.h	2012-01-27 05:57:35 +0000
+++ b/sql/item_func.h	2012-02-17 13:55:18 +0000
@@ -961,7 +961,18 @@ public:
   bool get_date(MYSQL_TIME *ltime, uint fuzzydate);
   bool get_time(MYSQL_TIME *ltime);  
   void fix_length_and_dec();
-  enum Item_result result_type () const { return cmp_type; }
+  enum Item_result result_type () const
+  {
+    /*
+      If we compare as dates, then:
+      - field_type is MYSQL_TYPE_VARSTRING, MYSQL_TYPE_DATETIME
+        or MYSQL_TYPE_DATE.
+      - cmp_type is INT_RESULT or DECIMAL_RESULT,
+        depending on the amount of fractional digits.
+      We need to return STRING_RESULT in this case instead of cmp_type.
+    */
+    return compare_as_dates ? STRING_RESULT : cmp_type;
+  }
   enum Item_result cast_to_int_type () const
   {
     /*

No bundle (reason: useless for push emails).
Thread
bzr push into mysql-trunk branch (alexander.barkov:3922 to 3923) Bug#13375823Alexander Barkov20 Feb