--disable_warnings DROP TABLE IF EXISTS t1, t2; --enable_warnings CREATE TABLE t1 ( f1 DECIMAL (63,29), f2 NUMERIC NOT NULL, pk BIGINT); INSERT INTO t1 VALUES ( 4.4, -1, 5); INSERT INTO t1 VALUES ( 5.5, 0, 6); CREATE TABLE t2 LIKE t1; INSERT INTO t2 VALUES ( 7.7, 2, 8); SELECT t1.f1, t1.f2, t2.f1, t2.f2 FROM t1, t2 WHERE t1.pk = 5 AND t2.pk = 8; # !! This statement produces the rotten value. UPDATE t1 t SET f1 = ( SELECT f1 FROM t2 WHERE pk = 8) WHERE pk = 5 ; # Here is the visible bad effect. SELECT t1.f1, t1.f2, t2.f1, t2.f2 FROM t1, t2 WHERE t1.pk = 5 AND t2.pk = 8; # We do not need the table within the subquery. SELECT t1.f1, t1.f2 FROM t1 WHERE t1.pk = 6; # !! This statement produces the rotten value. UPDATE t1 t SET f1 = ( SELECT 6.6 ) WHERE pk = 6 ; # Here is the visible bad effect. SELECT t1.f1, t1.f2 FROM t1 WHERE t1.pk = 6; # The server treats this value as usable for numerics, even when the printout # (SELECT f2) looks rotten. # Maybe this is another bug. If yes, it must be fixed first. SELECT t1.f2 + 1 AS "numerics with f2", t1.f2 FROM t1 WHERE t1.pk = 6; UPDATE t1 SET f2 = f2 + 1 WHERE pk = 6; SELECT t1.f2 AS "numerics with f2" FROM t1 WHERE t1.pk = 6; DROP TABLE t1, t2; ################ Some variations of the conditions #################### # f1 DECIMAL (63,29) seems to be 'guilty', because f1 of data type CHAR does # not cause any problem. # CREATE TABLE t1 ( f1 VARCHAR(10), f2 NUMERIC NOT NULL, pk BIGINT); INSERT INTO t1 VALUES ( 'a', 0, 6); SELECT t1.f1, t1.f2 FROM t1 WHERE t1.pk = 6; # UPDATE t1 t SET f1 = ( SELECT '1234567890' ) WHERE pk = 6 ; # no bug SELECT t1.f1, t1.f2 FROM t1 WHERE t1.pk = 6; DROP TABLE t1; # f2 NUMERIC seems to be also a condition needed for the problem # not cause any problem. # CREATE TABLE t1 ( f1 DECIMAL (63,29), f2 DOUBLE NOT NULL, pk BIGINT); INSERT INTO t1 VALUES ( 5.5, 0, 6); SELECT t1.f1, t1.f2 FROM t1 WHERE t1.pk = 6; # UPDATE t1 t SET f1 = ( SELECT 6.6 ) WHERE pk = 6 ; # no bug SELECT t1.f1, t1.f2 FROM t1 WHERE t1.pk = 6; DROP TABLE t1; ############################################################################# # Conclusion: # It looks like the bug occurs, when the following conditions are fulfilled: # 1. There is a column of precision math data type. # 2. This column is followed(I assume physical) 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. #############################################################################