From: Alexander Barkov Date: February 17 2012 1:56pm Subject: bzr push into mysql-trunk branch (alexander.barkov:3922 to 3923) Bug#13375823 List-Archive: http://lists.mysql.com/commits/142950 X-Bug: 13375823 Message-Id: <201202171400.q1HE0KGG027990@acsmt358.oracle.com> MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit 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).