Below is the list of changes that have just been committed into a local
5.0 repository of martin. When martin does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html
ChangeSet@stripped, 2008-01-08 16:25:28+01:00, mhansson@stripped +4 -0
Bug#33143: Incorrect ORDER BY for ROUND()/TRUNCATE() result
The ROUND(X, D) function would change the Item::decimals field during
execution to achieve the effect of a dynamic number of decimal digits.
This caused a series of bugs as a lot of code is dependent on the
mentioned field being stable after fix_fields phase.
Fixed by never changing the number of shown digits for DECIMAL when
used with a nonconstant number of decimal digits.
mysql-test/r/type_decimal.result@stripped, 2008-01-08 16:25:24+01:00, mhansson@stripped +62 -1
Bug#33143: Test result. 1 changed test case, as ROUND( decimal, nonconstant )
works differently after fix.
mysql-test/t/type_decimal.test@stripped, 2008-01-08 16:25:25+01:00, mhansson@stripped +44 -0
Bug#33143: Test case
sql/item_func.cc@stripped, 2008-01-08 16:25:25+01:00, mhansson@stripped +6 -6
Bug#33143:
- Moved the DECIMAL_MAX_SCALE limitation to fix_length_and_dec.
- Removed resetting of Item::decimals field.
- set the frac field of the output value to current scale.
strings/decimal.c@stripped, 2008-01-08 16:25:25+01:00, mhansson@stripped +18 -2
Bug#33143: It is necessary to set all digits following the rounded one
to zero, as they may now be visible.
diff -Nrup a/mysql-test/r/type_decimal.result b/mysql-test/r/type_decimal.result
--- a/mysql-test/r/type_decimal.result 2007-10-08 00:43:23 +02:00
+++ b/mysql-test/r/type_decimal.result 2008-01-08 16:25:24 +01:00
@@ -797,7 +797,7 @@ dps tinyint(3) unsigned default NULL
INSERT INTO t1 VALUES (1.1325,3);
SELECT ROUND(qty,3), dps, ROUND(qty,dps) FROM t1;
ROUND(qty,3) dps ROUND(qty,dps)
-1.133 3 1.133
+1.133 3 1.133000
DROP TABLE t1;
SELECT 1 % .123456789123456789123456789123456789123456789123456789123456789123456789123456789 AS '%';
%
@@ -805,3 +805,64 @@ SELECT 1 % .1234567891234567891234567891
SELECT MOD(1, .123456789123456789123456789123456789123456789123456789123456789123456789123456789) AS 'MOD()';
MOD()
0.012345687012345687012345687012345687012345687012345687012345687012345687000000000
+CREATE TABLE t1( a DECIMAL(4, 3), b INT );
+INSERT INTO t1 VALUES ( 1, 5 ), ( 2, 4 ), ( 3, 3 ), ( 4, 2 ), ( 5, 1 );
+SELECT a, b, ROUND( a, b ) AS c FROM t1 ORDER BY c;
+a b c
+1.000 5 1.000
+2.000 4 2.000
+3.000 3 3.000
+4.000 2 4.000
+5.000 1 5.000
+SELECT a, b, ROUND( a, b ) AS c FROM t1 ORDER BY c DESC;
+a b c
+5.000 1 5.000
+4.000 2 4.000
+3.000 3 3.000
+2.000 4 2.000
+1.000 5 1.000
+CREATE TABLE t2 ( a INT, b INT, c DECIMAL(5, 4) );
+INSERT INTO t2 VALUES ( 0, 1, 1.2345 ), ( 1, 2, 1.2345 ),
+( 3, 3, 1.2345 ), ( 2, 4, 1.2345 );
+SELECT a, b, MAX(ROUND(c, a))
+FROM t2
+GROUP BY a, b
+ORDER BY b;
+a b MAX(ROUND(c, a))
+0 1 1.0000
+1 2 1.2000
+3 3 1.2350
+2 4 1.2300
+SELECT a, b, ROUND(c, a)
+FROM t2;
+a b ROUND(c, a)
+0 1 1.0000
+1 2 1.2000
+3 3 1.2350
+2 4 1.2300
+CREATE TABLE t3( a INT, b DECIMAL(6, 3) );
+INSERT INTO t3 VALUES( 0, 1.5 );
+SELECT ROUND( b, a ) FROM t3;
+ROUND( b, a )
+2.000
+CREATE TABLE t4( a INT, b DECIMAL( 12, 0) );
+INSERT INTO t4 VALUES( -9, 1.5e9 );
+SELECT ROUND( b, a ) FROM t4;
+ROUND( b, a )
+2000000000
+CREATE TABLE t5( a INT, b DECIMAL( 13, 12 ) );
+INSERT INTO t5 VALUES( 0, 1.5 );
+INSERT INTO t5 VALUES( 9, 1.5e-9 );
+SELECT ROUND( b, a ) FROM t5;
+ROUND( b, a )
+2.000000000000
+0.000000002000
+CREATE TABLE t6( a INT );
+INSERT INTO t6 VALUES( 6 / 8 );
+SELECT * FROM t6;
+a
+1
+select ROUND(20061108085411.000002);
+ROUND(20061108085411.000002)
+20061108085411
+DROP TABLE t1, t2, t3, t4, t5, t6;
diff -Nrup a/mysql-test/t/type_decimal.test b/mysql-test/t/type_decimal.test
--- a/mysql-test/t/type_decimal.test 2007-10-08 00:43:15 +02:00
+++ b/mysql-test/t/type_decimal.test 2008-01-08 16:25:25 +01:00
@@ -416,3 +416,47 @@ DROP TABLE t1;
SELECT 1 % .123456789123456789123456789123456789123456789123456789123456789123456789123456789 AS '%';
SELECT MOD(1, .123456789123456789123456789123456789123456789123456789123456789123456789123456789) AS 'MOD()';
+
+#
+# Bug #33143: Incorrect ORDER BY for ROUND()/TRUNCATE() result
+#
+
+CREATE TABLE t1( a DECIMAL(4, 3), b INT );
+INSERT INTO t1 VALUES ( 1, 5 ), ( 2, 4 ), ( 3, 3 ), ( 4, 2 ), ( 5, 1 );
+SELECT a, b, ROUND( a, b ) AS c FROM t1 ORDER BY c;
+SELECT a, b, ROUND( a, b ) AS c FROM t1 ORDER BY c DESC;
+
+CREATE TABLE t2 ( a INT, b INT, c DECIMAL(5, 4) );
+
+INSERT INTO t2 VALUES ( 0, 1, 1.2345 ), ( 1, 2, 1.2345 ),
+ ( 3, 3, 1.2345 ), ( 2, 4, 1.2345 );
+
+SELECT a, b, MAX(ROUND(c, a))
+FROM t2
+GROUP BY a, b
+ORDER BY b;
+
+SELECT a, b, ROUND(c, a)
+FROM t2;
+
+CREATE TABLE t3( a INT, b DECIMAL(6, 3) );
+INSERT INTO t3 VALUES( 0, 1.5 );
+SELECT ROUND( b, a ) FROM t3;
+
+CREATE TABLE t4( a INT, b DECIMAL( 12, 0) );
+INSERT INTO t4 VALUES( -9, 1.5e9 );
+SELECT ROUND( b, a ) FROM t4;
+
+CREATE TABLE t5( a INT, b DECIMAL( 13, 12 ) );
+INSERT INTO t5 VALUES( 0, 1.5 );
+INSERT INTO t5 VALUES( 9, 1.5e-9 );
+SELECT ROUND( b, a ) FROM t5;
+
+CREATE TABLE t6( a INT );
+INSERT INTO t6 VALUES( 6 / 8 );
+SELECT * FROM t6;
+
+select ROUND(20061108085411.000002);
+
+DROP TABLE t1, t2, t3, t4, t5, t6;
+
diff -Nrup a/sql/item_func.cc b/sql/item_func.cc
--- a/sql/item_func.cc 2007-09-28 15:57:34 +02:00
+++ b/sql/item_func.cc 2008-01-08 16:25:25 +01:00
@@ -2008,7 +2008,7 @@ void Item_func_round::fix_length_and_dec
int length_increase= ((decimals_delta <= 0) || truncate) ? 0:1;
precision-= decimals_delta - length_increase;
- decimals= decimals_to_set;
+ decimals= min(decimals_to_set, DECIMAL_MAX_SCALE);
max_length= my_decimal_precision_to_length(precision, decimals,
unsigned_flag);
break;
@@ -2107,18 +2107,18 @@ my_decimal *Item_func_round::decimal_op(
{
my_decimal val, *value= args[0]->val_decimal(&val);
longlong dec= args[1]->val_int();
- if (dec > 0 || (dec < 0 && args[1]->unsigned_flag))
- {
+ if (dec >= 0 || args[1]->unsigned_flag)
dec= min((ulonglong) dec, DECIMAL_MAX_SCALE);
- decimals= (uint8) dec; // to get correct output
- }
else if (dec < INT_MIN)
dec= INT_MIN;
if (!(null_value= (args[0]->null_value || args[1]->null_value ||
my_decimal_round(E_DEC_FATAL_ERROR, value, (int) dec,
- truncate, decimal_value) > 1)))
+ truncate, decimal_value) > 1)))
+ {
+ decimal_value->frac= decimals;
return decimal_value;
+ }
return 0;
}
diff -Nrup a/strings/decimal.c b/strings/decimal.c
--- a/strings/decimal.c 2007-10-08 00:43:04 +02:00
+++ b/strings/decimal.c 2008-01-08 16:25:25 +01:00
@@ -1595,9 +1595,25 @@ decimal_round(decimal_t *from, decimal_t
x+=10;
*buf1=powers10[pos]*(x-y);
}
- if (frac0 < 0)
+ /*
+ In case we're rounding e.g. 1.5e9 to 2.0e9, the decimal_digit_t's inside
+ the buffer are as follows.
+
+ Before <1, 5e8>
+ After <2, 5e8>
+
+ Hence we need to set the 2nd field to 0.
+ The same holds if we round 1.5e-9 to 2e-9 and the number of shown decimals
+ is greater than 9.
+
+ We don't need to care about greater scale than from->frac, however. These
+ are never visible.
+ */
+ if (frac0 < frac1)
{
- dec1 *end=to->buf+intg0, *buf=buf1+1;
+ dec1 *buf= to->buf + ( (scale == 0 && intg0 == 0) ? 1 : intg0 + frac0 );
+ dec1 *end= (buf + frac1 - frac0);
+
while (buf < end)
*buf++=0;
}
