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, 2007-12-19 12:23:33+01:00, mhansson@stripped +5 -0
Bug#33143: Incorrect ORDER BY for ROUND()/TRUNCATE() result
The ROUND(X, D) function changes the scale of a result field X to D digits.
When D is not constant, the binary encoding of a DECIMAL value, used in
filesort cannot guarantee proper ordering.
Fixed by using the scale of X (as opposed to that of any expression
containong ROUND) in the temporary table, and changing
number of digits only when retreiving sorted values from the temporary
table.
mysql-test/r/type_decimal.result@stripped, 2007-12-19 12:23:04+01:00, mhansson@stripped +32 -0
Bug#33143: Test result
mysql-test/t/type_decimal.test@stripped, 2007-12-19 12:23:30+01:00, mhansson@stripped +20 -0
Bug#33143: Test case
sql/filesort.cc@stripped, 2007-12-19 12:23:30+01:00, mhansson@stripped +2 -2
Bug#33143: We use the original scale, since ROUND() might change it.
sql/item.h@stripped, 2007-12-19 12:23:30+01:00, mhansson@stripped +13 -0
Bug#33143: New member that acts as a hook for Item_func_round to return
the scale from its argument.
sql/item_func.h@stripped, 2007-12-19 12:23:30+01:00, mhansson@stripped +12 -0
Bug#33143: Override in Item_func_round.
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 2007-12-19 12:23:04 +01:00
@@ -805,3 +805,35 @@ SELECT 1 % .1234567891234567891234567891
SELECT MOD(1, .123456789123456789123456789123456789123456789123456789123456789123456789123456789) AS 'MOD()';
MOD()
0.012345687012345687012345687012345687012345687012345687012345687012345687000000000
+CREATE TABLE t1( a DECIMAL, b INT );
+INSERT INTO t1 VALUES ( 1, 5 ), ( 2, 2 ), ( 3, 3 ), ( 4, 4 ), ( 5, 1 );
+SELECT a, b, ROUND( a, b ) AS c FROM t1 ORDER BY c;
+a b c
+1 5 1.00000
+2 2 2.00
+3 3 3.000
+4 4 4.0000
+5 1 5.0
+CREATE TABLE t2( a DECIMAL(31, 30), b INT );
+INSERT INTO t2 VALUES ( 1e-30, 5 ),
+( 2e-30, 2 ),
+( 3e-30, 3 ),
+( 4e-30, 4 ),
+( 5e-30, 1 );
+Warnings:
+Note 1265 Data truncated for column 'a' at row 1
+SELECT a, b, ROUND( a, b ) AS c FROM t2 ORDER BY c;
+a b c
+0.000000000000000000000000000001 5 0.00000
+0.000000000000000000000000000002 2 0.00
+0.000000000000000000000000000003 3 0.000
+0.000000000000000000000000000004 4 0.0000
+0.000000000000000000000000000005 1 0.0
+SELECT a, b, ROUND( a, b ) + 1 AS c FROM t2 ORDER BY c;
+a b c
+0.000000000000000000000000000001 5 1.000000000000000000000000000000
+0.000000000000000000000000000002 2 1.000000000000000000000000000000
+0.000000000000000000000000000003 3 1.000000000000000000000000000000
+0.000000000000000000000000000004 4 1.000000000000000000000000000000
+0.000000000000000000000000000005 1 1.000000000000000000000000000000
+DROP TABLE t1, t2;
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 2007-12-19 12:23:30 +01:00
@@ -416,3 +416,23 @@ 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, b INT );
+INSERT INTO t1 VALUES ( 1, 5 ), ( 2, 2 ), ( 3, 3 ), ( 4, 4 ), ( 5, 1 );
+
+SELECT a, b, ROUND( a, b ) AS c FROM t1 ORDER BY c;
+
+CREATE TABLE t2( a DECIMAL(31, 30), b INT );
+INSERT INTO t2 VALUES ( 1e-30, 5 ),
+ ( 2e-30, 2 ),
+ ( 3e-30, 3 ),
+ ( 4e-30, 4 ),
+ ( 5e-30, 1 );
+
+SELECT a, b, ROUND( a, b ) AS c FROM t2 ORDER BY c;
+SELECT a, b, ROUND( a, b ) + 1 AS c FROM t2 ORDER BY c;
+
+DROP TABLE t1, t2;
diff -Nrup a/sql/filesort.cc b/sql/filesort.cc
--- a/sql/filesort.cc 2007-07-24 16:15:23 +02:00
+++ b/sql/filesort.cc 2007-12-19 12:23:30 +01:00
@@ -799,9 +799,9 @@ static void make_sortkey(register SORTPA
}
*to++=1;
}
+ uint8 decimals= item->get_original_scale();
my_decimal2binary(E_DEC_FATAL_ERROR, dec_val, (char*)to,
- item->max_length - (item->decimals ? 1:0),
- item->decimals);
+ item->max_length - (decimals ? 1:0), decimals);
break;
}
case REAL_RESULT:
diff -Nrup a/sql/item.h b/sql/item.h
--- a/sql/item.h 2007-10-16 10:19:00 +02:00
+++ b/sql/item.h 2007-12-19 12:23:30 +01:00
@@ -873,6 +873,19 @@ public:
virtual Field::geometry_type get_geometry_type() const
{ return Field::GEOM_GEOMETRY; };
String *check_well_formed_result(String *str, bool send_error= 0);
+ /**
+ @brief Stable scale for filesort.
+
+ Filesort needs a constant number of digits in order to sort correctly.
+ Therefore, Item's that change their scale (Item::decimals) throughout
+ execution must submit a constant scale through this method. If the number
+ is not constant, the scale may be different for different rows, causing
+ filesort not to work; filesort uses a binary representation of a DECIMAL
+ that does not accurately compare values with different scale.
+
+ @see filesort
+ */
+ virtual uint8 get_original_scale() { return decimals; }
};
diff -Nrup a/sql/item_func.h b/sql/item_func.h
--- a/sql/item_func.h 2007-10-09 11:36:00 +02:00
+++ b/sql/item_func.h 2007-12-19 12:23:30 +01:00
@@ -643,6 +643,18 @@ public:
longlong int_op();
my_decimal *decimal_op(my_decimal *);
void fix_length_and_dec();
+ /**
+ The ROUND function (currently the only case) will change its scale
+ according to the requested number of digits. If the number is not
+ constant the scale may be different for different rows, causing filesort
+ not to work. Filesort sorts according to a binary representation of a
+ DECIMAL that does not accurately compare values with different scale.
+ */
+ uint8 get_original_scale() {
+ if (!args[1]->const_item())
+ return args[0]->decimals;
+ return decimals;
+ }
};
