Details

    • Type: Sub-task Sub-task
    • Status: Resolved
    • Priority: Minor Minor
    • Resolution: Fixed
    • Affects Version/s: 2.0-ALPHA4
    • Fix Version/s: 2.3
    • Component/s: ORM
    • Security Level: All
    • Labels:
      None

      Description

          public function testDeleteAs()
          {
              $dql = 'DELETE Doctrine\Tests\Models\Company\CompanyEmployee AS p';
              $this->_em->createQuery($dql)->getResult();
      
              $this->assertEquals(0, count($this->_em->createQuery(
                  'SELECT count(p) FROM Doctrine\Tests\Models\Company\CompanyEmployee p')->getResult()));
          }
      

      fails with:

      Doctrine\Tests\ORM\Functional\AdvancedDqlQueryTest::testDeleteAs()
      Exception: [PDOException] SQLSTATE[23000]: Integrity constraint violation: 1451 Cannot delete or update a parent row: a foreign key constraint fails (`doctrine_tests`.`company_persons`, CONSTRAINT `company_persons_ibfk_1` FOREIGN KEY (`spouse_id`) REFERENCES `company_persons` (`id`))
      With queries:
      25. SQL: 'DELETE FROM company_persons WHERE (id) IN (SELECT id FROM company_persons_id_tmp)' Params:
      24. SQL: 'DELETE FROM company_employees WHERE (id) IN (SELECT id FROM company_persons_id_tmp)' Params:
      23. SQL: 'DELETE FROM company_managers WHERE (id) IN (SELECT id FROM company_persons_id_tmp)' Params:
      22. SQL: 'INSERT INTO company_persons_id_tmp (id) SELECT t0.id FROM company_employees t0 INNER JOIN company_persons c0_ ON t0.id = c0_.id LEFT JOIN company_managers c1_ ON t0.id = c1_.id' Params:
      21. SQL: 'CREATE TEMPORARY TABLE company_persons_id_tmp (id INT NOT NULL, PRIMARY KEY(id))' Params:
      20. SQL: 'INSERT INTO company_persons_friends (person_id, friend_id) VALUES (?, ?)' Params: '15', '14'
      19. SQL: 'INSERT INTO company_persons_friends (person_id, friend_id) VALUES (?, ?)' Params: '15', '13'
      18. SQL: 'INSERT INTO company_persons_friends (person_id, friend_id) VALUES (?, ?)' Params: '15', '16'
      17. SQL: 'INSERT INTO company_persons_friends (person_id, friend_id) VALUES (?, ?)' Params: '14', '15'
      16. SQL: 'INSERT INTO company_persons_friends (person_id, friend_id) VALUES (?, ?)' Params: '14', '13'
      15. SQL: 'INSERT INTO company_persons_friends (person_id, friend_id) VALUES (?, ?)' Params: '13', '15'
      14. SQL: 'INSERT INTO company_persons_friends (person_id, friend_id) VALUES (?, ?)' Params: '13', '14'
      13. SQL: 'INSERT INTO company_persons_friends (person_id, friend_id) VALUES (?, ?)' Params: '16', '15'
      12. SQL: 'UPDATE company_persons SET spouse_id = ? WHERE id = ?' Params: '13', '14'
      11. SQL: 'UPDATE company_persons SET spouse_id = ? WHERE id = ?' Params: '14', '13'
      10. SQL: 'INSERT INTO company_employees (id, salary, department) VALUES (?, ?, ?)' Params: '16', '100000', 'IT'
      9. SQL: 'INSERT INTO company_managers (id, title, car_id) VALUES (?, ?, ?)' Params: '16', 'Foo', '4'
      8. SQL: 'INSERT INTO company_persons (name, spouse_id, discr) VALUES (?, ?, ?)' Params: 'Roman B.', '', 'manager'
      7. SQL: 'INSERT INTO company_cars (brand) VALUES (?)' Params: 'BMW'
      6. SQL: 'INSERT INTO company_employees (id, salary, department) VALUES (?, ?, ?)' Params: '15', '800000', 'IT2'
      5. SQL: 'INSERT INTO company_persons (name, spouse_id, discr) VALUES (?, ?, ?)' Params: 'Jonathan W.', '', 'employee'
      4. SQL: 'INSERT INTO company_employees (id, salary, department) VALUES (?, ?, ?)' Params: '14', '400000', 'IT2'
      3. SQL: 'INSERT INTO company_persons (name, spouse_id, discr) VALUES (?, ?, ?)' Params: 'Guilherme B.', '', 'employee'
      2. SQL: 'INSERT INTO company_employees (id, salary, department) VALUES (?, ?, ?)' Params: '13', '200000', 'IT'
      Trace:
      /home/benny/code/php/wsnetbeans/Doctrine/trunk/lib/Doctrine/DBAL/Connection.php:630
      /home/benny/code/php/wsnetbeans/Doctrine/trunk/lib/Doctrine/ORM/Query/Exec/MultiTableDeleteExecutor.php:123
      /home/benny/code/php/wsnetbeans/Doctrine/trunk/lib/Doctrine/ORM/Query.php:198
      /home/benny/code/php/wsnetbeans/Doctrine/trunk/lib/Doctrine/ORM/AbstractQuery.php:511
      /home/benny/code/php/wsnetbeans/Doctrine/trunk/lib/Doctrine/ORM/AbstractQuery.php:349
      /home/benny/code/php/wsnetbeans/Doctrine/trunk/tests/Doctrine/Tests/ORM/Functional/AdvancedDqlQueryTest.php:93
      /home/benny/code/php/wsnetbeans/Doctrine/trunk/tests/Doctrine/Tests/OrmFunctionalTestCase.php:253
      

      fixture is:

          public function generateFixture()
          {
              $car = new CompanyCar('BMW');
      
              $manager1 = new CompanyManager();
              $manager1->setName('Roman B.');
              $manager1->setTitle('Foo');
              $manager1->setDepartment('IT');
              $manager1->setSalary(100000);
              $manager1->setCar($car);
      
              $person2 = new CompanyEmployee();
              $person2->setName('Benjamin E.');
              $person2->setDepartment('IT');
              $person2->setSalary(200000);
      
              $person3 = new CompanyEmployee();
              $person3->setName('Guilherme B.');
              $person3->setDepartment('IT2');
              $person3->setSalary(400000);
      
              $person4 = new CompanyEmployee();
              $person4->setName('Jonathan W.');
              $person4->setDepartment('IT2');
              $person4->setSalary(800000);
      
              $person2->setSpouse($person3);
      
              $manager1->addFriend($person4);
              $person2->addFriend($person3);
              $person2->addFriend($person4);
              $person3->addFriend($person4);
      
              $this->_em->persist($car);
              $this->_em->persist($manager1);
              $this->_em->persist($person2);
              $this->_em->persist($person3);
              $this->_em->persist($person4);
              $this->_em->flush();
              $this->_em->clear();
          }
      

        Activity

        Hide
        Roman S. Borschel added a comment -

        That should rather be ->executeUpdate() instead of ->getResult() but thats surely not the problem here.

        Show
        Roman S. Borschel added a comment - That should rather be ->executeUpdate() instead of ->getResult() but thats surely not the problem here.
        Hide
        Roman S. Borschel added a comment -

        An option is to check all associations and if there is a self-referential one execute UPDATE statements to set the FKs to null.

        However, this is a bit of extra work and I dont consider this scenario extremely common together with the combination of bulk deletes.

        Pushing priority down for now.

        Show
        Roman S. Borschel added a comment - An option is to check all associations and if there is a self-referential one execute UPDATE statements to set the FKs to null. However, this is a bit of extra work and I dont consider this scenario extremely common together with the combination of bulk deletes. Pushing priority down for now.
        Hide
        Guilherme Blanco added a comment -

        Added coverage to this specific situation. All passing.
        Closing the ticket as fixed for 2.3.

        https://github.com/doctrine/doctrine2/commit/3e601c3a53226ce981fe82db164f297a8d605ae7

        Show
        Guilherme Blanco added a comment - Added coverage to this specific situation. All passing. Closing the ticket as fixed for 2.3. https://github.com/doctrine/doctrine2/commit/3e601c3a53226ce981fe82db164f297a8d605ae7

          People

          • Assignee:
            Guilherme Blanco
            Reporter:
            Benjamin Eberlei
          • Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved: