| Bug #9939 | outer join with COALESCE, precision math, strange result | ||
|---|---|---|---|
| Submitted: | 15 Apr 2005 18:28 | Modified: | 23 Jun 2005 13:04 |
| Reporter: | Matthias Leich | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S2 (Serious) |
| Version: | 5.0 | OS: | |
| Assigned to: | Igor Babaev | CPU Architecture: | Any |
[27 May 2005 14:40]
Sergei Golubchik
IF() is affected too: select concat(if(0,1.00,1));
[2 Jun 2005 14:27]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/internals/25517
[2 Jun 2005 17:00]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/internals/25524
[6 Jun 2005 14:27]
Igor Babaev
ChangeSet
1.1940 05/06/02 07:27:02 igor@igor-inspiron.creware.com +7 -0
item_func.h:
Fixed bug #9939: a wrong conversion of arguments
for functions COALESCE and IFNULL.
The str_op virtual method was added into Item_func_numhybrid.
item_func.cc:
Fixed bug #9939: a wrong conversion of arguments
for functions COALESCE and IFNULL.
The str_op virtual method was added into Item_func_numhybrid.
item_cmpfunc.h, item_cmpfunc.cc:
Fixed bug #9939: a wrong conversion of arguments
for functions COALESCE and IFNULL.
Item_func_coalesce and Item_func_ifnull now
inherit from a modified Item_func_numhybrid.
case.test, case.result:
Added test cases for bug #9939.
This fix will appear in 5.0.7.
[23 Jun 2005 13:04]
Jon Stephens
Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.
If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information
about accessing the source trees is available at
http://www.mysql.com/doc/en/Installing_source_tree.html
Additional info:
Documented fix in 5.0.7 changelog; bug closed.
Description: The following outer join gives a strange result: CREATE TABLE t1 (EMPNUM INT); INSERT INTO t1 VALUES (0), (2); CREATE TABLE t2 (EMPNUM DECIMAL (4, 2)); INSERT INTO t2 VALUES (0.0), (9.0); SELECT COALESCE(t2.EMPNUM,t1.EMPNUM) AS CEMPNUM, t2.EMPNUM AS EMPNUM2, t1.EMPNUM AS EMPNUM1 FROM t2 RIGHT OUTER JOIN t1 USING (EMPNUM); CEMPNUM EMPNUM2 EMPNUM1 0.00 0.00 0 <--- I expect that both values of EMPNUM are 2 NULL 2 <--- printed in the style of "n.nn". I guess that the printout difference is caused by some wrong physical formatting of the second row. Therefore I fear, that the use of COALESCE(<DECIMAL>,<INT>) outer join within bigger statements(VIEWS?) with a qualification for the column CEMPNUM, might cause serious trouble. My environment: - Intel PC with Linux(SuSE 9.1) - MySQL compiled from source Version 5.0 ChangeSet@1.1836, 2005-04-14 How to repeat: Please use the statements above. Suggested fix: