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?