Skip to content
Snippets Groups Projects
Select Git revision
  • master default protected
  • 12-9-stable
  • 12-7-stable
  • 12-6-stable
  • 12-8-stable
  • github/fork/Kloppi313/patch-1
  • 12-5-stable
  • 12-4-stable
  • github/fork/ramalokesh8477/master
  • 12-1-stable
  • 12-2-stable
  • 12-0-stable
  • 12-3-stable
  • 42-42-stable
  • github/fork/hussamgit398/patch-2
  • 12-3-auto-deploy-20190911
  • 12-3-auto-deploy-20190916
  • 12-3-auto-deploy-20190908
  • 12-3-auto-deploy-20190901
  • 12-3-auto-deploy-20190901-32664
  • v12.10.0.pre
  • v12.9.0
  • v12.9.0-rc42
  • v12.8.7
  • v12.8.6
  • v12.8.5
  • v12.8.4
  • v12.8.3
  • v12.6.8
  • v12.7.7
  • v12.8.2
  • v12.8.1
  • v12.9.0.pre
  • v12.8.0
  • v12.8.0-rc42
  • v12.5.10
  • v12.7.6
  • v12.6.7
  • v12.7.5
  • v12.5.9
40 results

todos_finder.rb

  • Toon Claes's avatar
    a488fc0a
    Add workaround for UPDATE with subquery when using MySQL · a488fc0a
    Toon Claes authored
    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
    a488fc0a
    History
    Add workaround for UPDATE with subquery when using MySQL
    Toon Claes authored
    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