Add workaround for UPDATE with subquery when using MySQL

When trying to run an UPDATE, this query is ran:

```sql
UPDATE `todos`
INNER JOIN `projects` ON `projects`.`id` = `todos`.`project_id`
SET `todos`.`state` = 'done'
WHERE `todos`.`user_id` = 4
  AND (`todos`.`state` IN ('pending'))
  AND (EXISTS
         (SELECT 1
          FROM `project_authorizations`
          WHERE `project_authorizations`.`user_id` = 4
            AND (project_authorizations.project_id = projects.id))
       OR projects.visibility_level IN (10,
                                        20))
  AND `projects`.`id` IN
    (SELECT `todos`.`project_id`
     FROM `todos`
     WHERE `todos`.`user_id` = 4
       AND (`todos`.`state` IN ('pending')))
  AND (`todos`.`state` != 'done')
```

But MySQL does not like the subquery used to filter on
`projects.id IN (SELECT ...`

Because the subquery queries from the same table:

> Error: You can’t specify target table ‘todos’ for update in FROM clause

So as workaround, wrap it in another subquery, where the original
subquery is aliased using the `AS` statement.

Mostly inspired by https://stackoverflow.com/a/43610081/89376

1 files changed, 11 insertions, 2 deletions

diff --git a/app/finders/todos_finder.rb b/app/finders/todos_finder.rb
index 3fe37c75381..b276116f0c6 100644
--- a/app/finders/todos_finder.rb
+++ b/app/finders/todos_finder.rb
@@ -95,9 +95,18 @@ class TodosFinder
     @project
   end
 
+  def project_ids(items)
+    ids = items.except(:order).select(:project_id)
+    if Gitlab::Database.mysql?
+      # To make UPDATE work on MySQL, wrap it in a SELECT with an alias
+      ids = Todo.except(:order).select('*').from("(#{ids.to_sql}) AS t")
+    end
+
+    ids
+  end
+
   def projects(items)
-    item_project_ids = items.reorder(nil).select(:project_id)
-    ProjectsFinder.new(current_user: current_user, project_ids_relation: item_project_ids).execute
+    ProjectsFinder.new(current_user: current_user, project_ids_relation: project_ids(items)).execute
   end
 
   def type?