Bug #10232 update with subquery, precision math, another column gets rotten value
Submitted: 28 Apr 2005 9:03 Modified: 10 May 2005 9:53
Reporter: Matthias Leich Email Updates:
Status: Closed Impact on me:
Category:MySQL Server Severity:S2 (Serious)
Version:5.0 OS:
Assigned to: Jani Tolonen CPU Architecture:Any

[28 Apr 2005 9:03] Matthias Leich
It looks like bad effects occur, when the following conditions are fulfilled:
1. There is a column of precision math data type.
2. This column is physical followed by another column 
    of precision math data type.
When the first column is updated via subquery, the second column is
modified to a rotten value.
CREATE TABLE t1 ( f1 DECIMAL (63,29), 
                           f2 NUMERIC NOT NULL, pk BIGINT);
INSERT INTO t1 VALUES ( 5.5, 0, 6);

SELECT t1.f1, t1.f2 FROM t1 WHERE t1.pk = 6;
f1      f2
5.50000000000000000000000000000 0

UPDATE t1 t SET f1 = ( SELECT 6.6 ) WHERE pk = 6 ;
SELECT t1.f1, t1.f2 FROM t1 WHERE t1.pk = 6;
f1      f2
6.60000000000000000000000000000 -700000000/
                                      I expect here  0   .

A second bad effect is, that the server accepts the rotten
value within numerics. Maybe this is another bug.
SELECT t1.f2 + 1 AS "numerics with f2", t1.f2 FROM t1 WHERE t1.pk = 6;
numerics with f2        f2
-126999999998   -700000000/

My environment:
   - Intel PC with Linux(SuSE 9.1)
   - MySQL compiled from source
        Version 5.0 ChangeSet@1.1900, 2005-04-27

Maybe this bug has the same reason like bug#7589, but there is an
important difference. We have here the situation that an update of
column f1 harms the content of the following column f2.

How to repeat:
Please use the attached test case ml61.test  with
same additional condition variations.
  copy it to mysql-test/t
  touch r/ml61.result     # Produce a dummy file with 
                                   # expected results
  ./mysql-test-run ml61
  inspect r/ml61.reject    # The protocol of the execution.
[28 Apr 2005 9:04] Matthias Leich
test case

Attachment: ml61.test (application/test, text), 2.48 KiB.

[10 May 2005 9:48] 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:

[10 May 2005 9:53] Jani Tolonen
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