diff options
author | Toon Claes <toon@iotcl.com> | 2017-07-14 16:56:36 +0200 |
---|---|---|
committer | Toon Claes <toon@iotcl.com> | 2017-08-03 16:31:05 +0200 |
commit | a488fc0add73963c4839f49a7fe0f7b5b014d15a (patch) | |
tree | 8208aa29b1f738cc0d3300ae3dab8355a50e5b96 /app/finders/todos_finder.rb | |
parent | a723cba57493ec3220596ca8543a8b1b1ec118fe (diff) | |
download | gitlab-ce-a488fc0add73963c4839f49a7fe0f7b5b014d15a.tar.gz |
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
Diffstat (limited to 'app/finders/todos_finder.rb')
-rw-r--r-- | app/finders/todos_finder.rb | 13 |
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? |