MySQL Bugs: #111477: If Table contains Floating Point with limited digits, UPDATE may silently fail

Bug #111477	If Table contains Floating Point with limited digits, UPDATE may silently fail
Submitted:	18 Jun 2023 15:14	Modified:	19 Jun 2023 11:59
Reporter:	Till Wimmer	Email Updates:
Status:	Unsupported	Impact on me:	None
Category:	MySQL Server: Federated storage engine	Severity:	S2 (Serious)
Version:	8.0.33	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	double, federated, FLOAT, floating point types, update failed

Description:
When doing an UPDATE statement on the FEDERATED table with floating point field(s) and limited digits, nothing is updated even when it says: Query OK, 1 row affected.

How to repeat:
On (master) server add table containing at least one floating point field, e.g.

CREATE TABLE `k` (
  `uid` INT NOT NULL,
  `object` VARCHAR(4),
  `length` DOUBLE(8,6) NOT NULL DEFAULT 0.000000,
  PRIMARY KEY (`uid`)
);

INSERT INTO `k` (`uid`, `object`, `length`) VALUES (1, 'ABCD', '8.730550');

On client server with FEDERATED engine add remote copy of 'k'  table.
Then update row:

UPDATE `k` SET object='EFGH' WHERE uid=1;

SELECT * FROM `k` WHERE `uid` = 1;

`project` is still 'ABCD'.

Suggested fix:
FEDERATED uses all table rows for matching on UPDATE:

  UPDATE k ... WHERE ... AND length = 8.730550.

This may not match the record because the floating point field is stored in full length (8.730550000000001).

Do a CAST to full sized floating point type:

  SELECT ..., CAST(`length` AS DOUBLE) FROM k;

So it will match the record:

  UPDATE k ... WHERE ... AND length = 8.730550000000001.

Hi Mr. Wimmer,

Thank you for your bug report.

However, federated SE is not supported any more. It will be removed in the next version, id est 8.1.

Unsupported.