Fix migration for removing orphaned issues.moved_to_id values in MySQL and PostgreSQL
The PostgreSQL implementation was broken in that it wiped out all moved_to_id
entries. For PostgreSQL, notice the subtle differences:
Incorrect:
gitlabhq_test=# SELECT id, moved_to_id
FROM issues
WHERE NOT EXISTS (SELECT moved_to_id
FROM issues
WHERE issues.id = issues.moved_to_id)
AND moved_to_id IS NOT NULL;
id | moved_to_id
----+-------------
2 | 1
3 | 2
1 | 100000
(3 rows)
Incorrect:
gitlabhq_test=# SELECT id, moved_to_id
FROM issues A
WHERE NOT EXISTS (SELECT moved_to_id
FROM issues B
WHERE A.id = B.moved_to_id)
AND ( moved_to_id IS NOT NULL );
id | moved_to_id
----+-------------
3 | 2
(1 row)
Correct:
gitlabhq_test=# SELECT id, moved_to_id
FROM issues A
WHERE NOT EXISTS (SELECT moved_to_id
FROM issues B
WHERE A.moved_to_id = B.id)
AND ( moved_to_id IS NOT NULL );
id | moved_to_id
----+-------------
1 | 100000
(1 row)
For MySQL:
According to https://dev.mysql.com/doc/refman/5.7/en/update.html, "You cannot update a table and select from the same table in a subquery." Attempting to do so results in the error:
Mysql2::Error: Table 'issues' is specified twice, both as a target for 'UPDATE' and as a separate source for data
Instead, we can use a LEFT JOIN on the same table to make MySQL do the right thing.
Closes #41498 (closed)
Edited by Stan Hu