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-03-14 18:37:58+01:00, mhansson@stripped +8 -0
Bug #24791: Union with AVG-groups generates wrong results
The problem in this bug is when we create temporary tables. When
temporary tables are created for unions, there is some
inferrence being carried out regarding the type of the column.
Whenever this column type is inferred to be REAL (i.e. FLOAT or
DOUBLE), MySQL will always try to maintain exact precision, and
if that is not possible (there are hardware limits, since FLOAT
and DOUBLE are stored as approximate values) will switch to
using approximate values. The problem here is that at this point
the information about number of significant digits is not
available. Furthermore, the number of significant digits should
be increased for the AVG function, however, this was not properly
handled. There are 4 parts to the problem:
#1: DOUBLE and FLOAT fields don't display their proper display
lengths in max_display_length(). This is hard-coded as 53 for
DOUBLE and 24 for FLOAT. Now changed to instead return the
field_length.
#2: Type holders for temporary tables do not preserve the
max_length of the Item's from which they are created, and is
instead reverted to the 53 and 24 from above. This causes
*all* fields to get non-fixed significant digits.
#3: AVG function does not update max_length (display length)
when updating number of decimals.
#4: The function that switches to non-fixed number of
significant digits should use DBL_DIG + 2 or FLT_DIG + 2 as
cut-off values (Since fixed precision does not use the 'e'
notation)
Of these points, #1 is the controversial one, but this
change is preferred and has been cleared with Monty. The
function causes quite a few unit tests to blow up and they had
to b changed, but each one is annotated and motivated. We
frequently see the magical 53 and 24 give way to more relevant
numbers.
mysql-test/r/create.result@stripped, 2007-03-14 18:37:50+01:00, mhansson@stripped +2 -2
bug#24791
changed test result
With the changes made for FLOAT and DOUBLE, the original display
lengths are now preserved.
mysql-test/r/temp_table.result@stripped, 2007-03-14 18:37:50+01:00, mhansson@stripped +21 -0
bug#24791
changed test resullt
Test case added
mysql-test/r/type_float.result@stripped, 2007-03-14 18:37:50+01:00, mhansson@stripped +4 -4
bug#24791
changed test result
delta 1: field was originally declared as DOUBLE with no display
length, so the hardware maximum is chosen rather than 53.
delta 2: fields exceed the maximum precision and thus switch to
non-fixed significant digits
delta 3: Same as above, number of decmals and significant digits
was not specified when t3 was created.
mysql-test/r/union.result@stripped, 2007-03-14 18:37:51+01:00, mhansson@stripped +1 -1
bug#24791
changed test result
The type of a FLOAT field with unspecified display length will
have the smallest exact precision display length.
mysql-test/t/temp_table.test@stripped, 2007-03-14 18:37:51+01:00, mhansson@stripped +16 -0
bug#24791
Test case
sql/field.h@stripped, 2007-03-14 18:37:52+01:00, mhansson@stripped +1 -3
bug#24791
The method max_display_length is reimplemented as
uint32 max_display_length() { return field_length; }
in Field_double and Field_float. Since all subclasses of
Field_real now have the same implementation of this method, the
implementation has been moved up the hierarchy to Field_real.
sql/item.cc@stripped, 2007-03-14 18:37:52+01:00, mhansson@stripped +11 -7
bug#24791
We switch to a non-fixed number of significant digits
(by setting decimals=NOT_FIXED_DECIMAL) if the calculated
display length is greater than the display length of a value
with the maximum precision. These values differ for double and
float, obviously.
sql/item_sum.cc@stripped, 2007-03-14 18:37:52+01:00, mhansson@stripped +3 -1
bug#24791
We must increase the display length accordinly whenever we
change number of decimal places.
# This is a BitKeeper patch. What follows are the unified diffs for the
# set of deltas contained in the patch. The rest of the patch, the part
# that BitKeeper cares about, is below these diffs.
# User: mhansson
# Host: linux-st28.site
# Root: /home/martin/mysql/src/5.0o-bug24791
--- 1.196/sql/field.h 2007-02-21 13:07:05 +01:00
+++ 1.197/sql/field.h 2007-03-14 18:37:52 +01:00
@@ -432,6 +432,7 @@ public:
int store_decimal(const my_decimal *);
my_decimal *val_decimal(my_decimal *);
+ uint32 max_display_length() { return field_length; }
};
@@ -461,7 +462,6 @@ public:
void overflow(bool negative);
bool zero_pack() const { return 0; }
void sql_type(String &str) const;
- uint32 max_display_length() { return field_length; }
};
@@ -719,7 +719,6 @@ public:
void sort_string(char *buff,uint length);
uint32 pack_length() const { return sizeof(float); }
void sql_type(String &str) const;
- uint32 max_display_length() { return 24; }
};
@@ -762,7 +761,6 @@ public:
void sort_string(char *buff,uint length);
uint32 pack_length() const { return sizeof(double); }
void sql_type(String &str) const;
- uint32 max_display_length() { return 53; }
uint size_of() const { return sizeof(*this); }
};
--- 1.256/sql/item.cc 2007-03-12 07:34:39 +01:00
+++ 1.257/sql/item.cc 2007-03-14 18:37:52 +01:00
@@ -6348,8 +6348,6 @@ Item_type_holder::Item_type_holder(THD *
:Item(thd, item), enum_set_typelib(0), fld_type(get_real_type(item))
{
DBUG_ASSERT(item->fixed);
-
- max_length= display_length(item);
maybe_null= item->maybe_null;
collation.set(item->collation);
get_full_info(item);
@@ -6521,11 +6519,17 @@ bool Item_type_holder::join_types(THD *t
{
int delta1= max_length_orig - decimals_orig;
int delta2= item->max_length - item->decimals;
- if (fld_type == MYSQL_TYPE_DECIMAL)
- max_length= max(delta1, delta2) + decimals;
- else
- max_length= min(max(delta1, delta2) + decimals,
- (fld_type == MYSQL_TYPE_FLOAT) ? FLT_DIG+6 : DBL_DIG+7);
+ max_length= max(delta1, delta2) + decimals;
+ if (fld_type == MYSQL_TYPE_FLOAT && max_length > FLT_DIG + 2)
+ {
+ max_length= FLT_DIG + 6;
+ decimals= NOT_FIXED_DEC;
+ }
+ if (fld_type == MYSQL_TYPE_DOUBLE && max_length > DBL_DIG + 2)
+ {
+ max_length= DBL_DIG + 7;
+ decimals= NOT_FIXED_DEC;
+ }
}
else
max_length= (fld_type == MYSQL_TYPE_FLOAT) ? FLT_DIG+6 : DBL_DIG+7;
--- 1.196/sql/item_sum.cc 2007-02-21 12:05:00 +01:00
+++ 1.197/sql/item_sum.cc 2007-03-14 18:37:52 +01:00
@@ -1096,8 +1096,10 @@ void Item_sum_avg::fix_length_and_dec()
f_scale= args[0]->decimals;
dec_bin_size= my_decimal_get_binary_size(f_precision, f_scale);
}
- else
+ else {
decimals= min(args[0]->decimals + prec_increment, NOT_FIXED_DEC);
+ max_length= args[0]->max_length + prec_increment;
+ }
}
--- 1.89/mysql-test/r/union.result 2007-03-10 17:50:55 +01:00
+++ 1.90/mysql-test/r/union.result 2007-03-14 18:37:51 +01:00
@@ -655,7 +655,7 @@ f
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
- `f` varbinary(24) default NULL
+ `f` varbinary(12) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
create table t1 SELECT y from t2 UNION select da from t2;
--- 1.124/mysql-test/r/create.result 2007-02-26 11:49:23 +01:00
+++ 1.125/mysql-test/r/create.result 2007-03-14 18:37:50 +01:00
@@ -447,8 +447,8 @@ t2 CREATE TABLE `t2` (
`ifnull(c,c)` mediumint(8) default NULL,
`ifnull(d,d)` int(11) default NULL,
`ifnull(e,e)` bigint(20) default NULL,
- `ifnull(f,f)` float(24,2) default NULL,
- `ifnull(g,g)` double(53,3) default NULL,
+ `ifnull(f,f)` float(3,2) default NULL,
+ `ifnull(g,g)` double(4,3) default NULL,
`ifnull(h,h)` decimal(5,4) default NULL,
`ifnull(i,i)` year(4) default NULL,
`ifnull(j,j)` date default NULL,
--- 1.21/mysql-test/r/temp_table.result 2006-09-29 10:30:28 +02:00
+++ 1.22/mysql-test/r/temp_table.result 2007-03-14 18:37:50 +01:00
@@ -152,3 +152,24 @@ SELECT * FROM t1;
i
DROP TABLE t1;
End of 4.1 tests.
+CREATE TABLE t1 ( c FLOAT( 20, 14 ) );
+INSERT INTO t1 VALUES( 12139 );
+CREATE TABLE t2 ( c FLOAT(30,18) );
+INSERT INTO t2 VALUES( 123456 );
+SELECT AVG( c ) FROM t1 UNION SELECT 1;
+AVG( c )
+12139
+1
+SELECT 1 UNION SELECT AVG( c ) FROM t1;
+1
+1
+12139
+SELECT 1 UNION SELECT * FROM t2 UNION SELECT 1;
+1
+1
+123456
+SELECT c/1 FROM t1 UNION SELECT 1;
+c/1
+12139
+1
+DROP TABLE t1, t2;
--- 1.49/mysql-test/r/type_float.result 2007-01-31 06:56:14 +01:00
+++ 1.50/mysql-test/r/type_float.result 2007-03-14 18:37:50 +01:00
@@ -92,7 +92,7 @@ show create table t2;
Table Create Table
t2 CREATE TABLE `t2` (
`col1` double default NULL,
- `col2` double(53,5) default NULL,
+ `col2` double(22,5) default NULL,
`col3` double default NULL,
`col4` double default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
@@ -232,12 +232,12 @@ insert into t2 values ("1.23456780");
create table t3 select * from t2 union select * from t1;
select * from t3;
d
-1.234567800
-100000000.000000000
+1.2345678
+100000000
show create table t3;
Table Create Table
t3 CREATE TABLE `t3` (
- `d` double(22,9) default NULL
+ `d` double default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1, t2, t3;
create table t1 select 105213674794682365.00 + 0.0 x;
--- 1.15/mysql-test/t/temp_table.test 2006-09-29 10:30:28 +02:00
+++ 1.16/mysql-test/t/temp_table.test 2007-03-14 18:37:51 +01:00
@@ -163,3 +163,19 @@ DROP TABLE t1;
--echo End of 4.1 tests.
+
+#
+# Bug #24791: Union with AVG-groups generates wrong results
+#
+CREATE TABLE t1 ( c FLOAT( 20, 14 ) );
+INSERT INTO t1 VALUES( 12139 );
+
+CREATE TABLE t2 ( c FLOAT(30,18) );
+INSERT INTO t2 VALUES( 123456 );
+
+SELECT AVG( c ) FROM t1 UNION SELECT 1;
+SELECT 1 UNION SELECT AVG( c ) FROM t1;
+SELECT 1 UNION SELECT * FROM t2 UNION SELECT 1;
+SELECT c/1 FROM t1 UNION SELECT 1;
+
+DROP TABLE t1, t2;
| Thread |
|---|
| • bk commit into 5.0 tree (mhansson:1.2479) BUG#24791 | mhansson | 14 Mar |
