Bug #14216 UNION + DECIMAL wrong values in result
Submitted: 21 Oct 2005 16:52 Modified: 2 Dec 2005 3:59
Reporter: Matthias Leich Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S1 (Critical)
Version:4.1 OS:
Assigned to: Jim Winstead CPU Architecture:Any

[21 Oct 2005 16:52] Matthias Leich
Description:
There are problems with UNION of DECIMAL data type columns.

Example:
CREATE TABLE test.t1 ( f1 DECIMAL (60,25), f2 DECIMAL (60,25));
INSERT INTO test.t1 VALUES(0.0,0.0);
SELECT f1, f2 FROM t1;
f1	f2
0.0000000000000000000000000	0.0000000000000000000000000

SELECT f1 AS value FROM t1
UNION ALL
SELECT f2 FROM t1;
value
9999999999999999999999   <-- I expect to get 0.000000000000000000000000000000
9999999999999999999999   <-- here

SELECT 'XXXXXXXXXXXXXXXXXXXX' AS description, f1 AS value FROM test.t1
UNION ALL
SELECT 'YYYYYYYYYYYYYYYYYYYY' , f2 FROM t1;
description	value
XXXXXXXXXXXXXXXX.XXX	9999999999999999999999
YYYYYYYYYYYYYYYY.YYY	9999999999999999999999
                            #
                            #
       Please note the wrong additional "." at the 17'th position.

CREATE TABLE test.t1 ( f1 DECIMAL (60,24), f2 DECIMAL (60,24));
         # Attention scale is now one digit less.
INSERT INTO test.t1 VALUES(0.0,0.0);
SELECT 'XXXXXXXXXXXXXXXXXXXX' AS description, f1 AS value FROM test.t1
UNION ALL
SELECT 'YYYYYYYYYYYYYYYYYYYY' , f2 FROM t1;
description	value
XXXXXXXXXXXXXXXXX.XX	9999999999999999999999
YYYYYYYYYYYYYYYYY.YY	9999999999999999999999
                               #  
       Please note the wrong additional "." walked from the 17'th position
       to the 18'th. That seems to be an effect of scale 24 instead of 25.

My environment:
   - Intel PC with Linux(SuSE 9.3)
   - MySQL compiled from source
        Version 4.1 ChangeSet@1.2457, 2005-10-21

MySQL 5.0 does not show this bug. 
I assume the new implemented precision math for 
DECIMAL/NUMERIC prevents the bad effect.

How to repeat:
Please use my attached testscript ml033.test
  copy it to mysql-test/t
  echo "Dummy" > r/ml033.result   # Produce a dummy file with 
                                                   # expected results
  ./mysql-test-run ml033
  inspect r/ml033.reject    # The protocol of the execution.
[21 Oct 2005 16:53] Matthias Leich
test script

Attachment: ml033.test (application/test, text), 757 bytes.

[21 Oct 2005 16:55] Matthias Leich
My reject file

Attachment: ml033.reject (application/octet-stream, text), 907 bytes.

[29 Oct 2005 1:12] 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/31645
[30 Nov 2005 20:08] Jim Winstead
Fixed in 4.1.16 and 5.0.17.
[2 Dec 2005 3:59] Paul DuBois
Noted in 4.1.16, 5.0.17 changelogs.