Yet another FEDERATED oddity - or something wrong with this SQL statement?

Hi there,

We have an update on a join statement on one server which updates a remote federated table from another server.

Local tables
Year

+-----+-------+
| uid | name  |
+-----+-------+
|  9  |  2021 |
| 10  |  2022 |
| 11  |  2023 |
+-----+-------+

Site

+-----+-----------+---------+---------+
| uid | portal_id | concern | deleted |
+-----+-----------+---------+---------+
| 202 |  1099     | 361     | 0       |
| 203 |  7088     | 362     | 0       |
| 204 |  4033     | 363     | 0       |
+-----+-----------+---------+---------+

Federated table
Clinic

+-----+-----------+---------+----------+
| uid | portal_id | upd_year | deleted |
+-----+-----------+---------+----------+
| 123 |  1099     | 2021    | 0        |
| 124 |  7088     | 2021    | 0        |
| 125 |  4033     | 2017    | 0        |
+-----+-----------+---------+----------+

Then the update statement:

UPDATE 
	federated.clinic c
JOIN 
	local.site s
ON 
	s.portal_id = c.portal_id
JOIN 
	local.year y
SET
	c.upd_year = y.name
WHERE 1=1
	AND y.uid = 10
	AND s.concern = 363
	AND s.deleted = 0
	AND c.deleted = 0
;
Query OK, 1 row affected (0.07 sec)
Rows matched: 1  Changed: 1  Warnings: 0

And then we check the result:

SELECT y.name, c.upd_year FROM federated.clinic c
JOIN 
	local.site s
ON 
	s.portal_id = c.portal_id
JOIN 
	local.year y
WHERE 1=1
	AND y.uid = 10
	AND s.concern = 363
	AND s.deleted = 0
	AND c.deleted = 0
;
+------+----------+
| name | upd_year |
+------+----------+
| 2022 |  2017    |
+------+----------+
1 row in set (0.08 sec)

The years differ. How is this possible?

In the update statement we set c.upd_year = y.name. So when we use the exact same join for the select we should get exactly what we set before…

Even more odd: This behavior is not the same for all records. I.e. when I change the WHERE clause to AND s.concern = 361 (different concern ID), the year gets updated as expected:

UPDATE ...
 ...
WHERE 1=1
	AND y.uid = 10
	AND s.concern = 361
 ...
Rows matched: 1  Changed: 1  Warnings: 0
SELECT ...
+------+----------+
| name | upd_year |
+------+----------+
| 2022 |  2022    |
+------+----------+

I have no idea what’s going on here. Maybe I can’t see the wood for the trees?

Update: It has nothing to do with the JOIN statement. But watch this:

mysql> SELECT upd_year FROM federated.clinic WHERE uid = 125;
+----------+
| upd_year |
+----------+
| 2017     |
+----------+
1 row in set (0.02 sec)

mysql> UPDATE federated.clinic SET upd_year = '2022' WHERE uid = 125;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT upd_year FROM federated.clinic WHERE uid = 125;
+----------+
| upd_year |
+----------+
| 2017     |
+----------+
1 row in set (0.02 sec)

This is crazy.

UPDATE: Found this bug report here https://bugs.mysql.com/bug.php?id=63446
There’s a comment “Happens in version 8 as well.” from 7 Dec 2018. Was this never fixed?

I will do more testing if this is the same issue in my case.

I finally had time to narrow down this issue. It can be reproduced, and the problem is a DOUBLE field.
I didn’t test this on “vanilla” MySQL 8.x though…

To reproduce it these steps are needed:

Server 1:

CREATE DATABASE test;

CREATE TABLE `test`.`k` (
  `uid` INT NOT NULL AUTO_INCREMENT,
  `year` varchar(4) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `longitude` DOUBLE(8,6) NOT NULL DEFAULT '0.000000 ',
  PRIMARY KEY (`uid`)
) ENGINE = InnoDB;

INSERT INTO `k` (`uid`, `year`, `longitude`) VALUES (1, '2017', '8.730550');

Server 2 (with FEDERATED):

CREATE DATABASE test;
CREATE SERVER test FOREIGN DATA WRAPPER test1 OPTIONS(user 'root', password '', host '127.0.0.1', port 5000, database 'test');

CREATE TABLE `k` (
  `uid` INT NOT NULL AUTO_INCREMENT ,
  `year` varchar(4) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `longitude` DOUBLE(8,6) NOT NULL DEFAULT '0.000000 ',
  PRIMARY KEY (`uid`)
) ENGINE=FEDERATED CONNECTION='test';

UPDATE `k` SET year='2023' WHERE uid=1;

year will remain ‘2017’.

UPDATE: Bug report here: [PS-8810] FEDERATED Update on Table can silently fail when Table contains DOUBLE Columns - Percona JIRA