Doctrine 2 - ORM
  1. Doctrine 2 - ORM
  2. DDC-1389

Querying subclass entities using DQL results in broken SQL being generated

    Details

    • Type: Bug Bug
    • Status: Resolved
    • Priority: Major Major
    • Resolution: Invalid
    • Affects Version/s: 2.1.1
    • Fix Version/s: None
    • Component/s: ORM
    • Security Level: All
    • Labels:
      None
    • Environment:
      Debian Linux 6.0, MySQL Server 5.0.51a

      Description

      For entities that are instances of an entity class that inherits from another entity class, querying them directly using DQL will generate a broken SQL statement in which a table alias is referenced but never declared.

      Here is an example SQL query:

      SELECT a from \A a where a.someField = 1

      Assume that A is an entity class that inherits from another entity class, say B.

      The generated query will then be of the following (wrong) form:

      SELECT i0_.someField as someField0, a1_.someOtherfield AS someOtherfield1 FROM A a1_ WHERE a1_.someField = 1 LIMIT 1

      You can see that the generated query references two table aliases in the select list (i0_ and a1_), but only declares one (a1_). This can't possibly work. Apparently the inheritance join does not make it into SQL. As inheritance joins are documented to happen transparently, I believe this is unintended behaviour.

      The error message is then, of course, something like this:

      SQLSTATE[42S22]: Column not found: 1054 Unknown column 'i0_.somefield' in 'field list'' ...

      Strangely, it also happens for fields that are replicated to the subclass, e. g. the primary key.

      Querying entity classes that do not inherit from other entity classes works as expected though, including the base classes in an entity inheritance hierarchy.

        Activity

        Hide
        Daniel Alvarez Arribas added a comment -

        I can provide you with additional sourcecode at any time, if you require it, or live contact and access to a testing system.

        Show
        Daniel Alvarez Arribas added a comment - I can provide you with additional sourcecode at any time, if you require it, or live contact and access to a testing system.
        Hide
        Daniel Alvarez Arribas added a comment -

        Here is a complete stack trace:

        #0 /var/www/invoiceCreator/src/thirdPartyComponents/doctrine/Doctrine/ORM/Mapping/ClassMetadataFactory.php(364): Doctrine\ORM\Mapping\ClassMetadata->__construct(NULL)
        #1 /var/www/invoiceCreator/src/thirdPartyComponents/doctrine/Doctrine/ORM/Mapping/ClassMetadataFactory.php(265): Doctrine\ORM\Mapping\ClassMetadataFactory->newClassMetadataInstance(NULL)
        #2 /var/www/invoiceCreator/src/thirdPartyComponents/doctrine/Doctrine/ORM/Mapping/ClassMetadataFactory.php(170): Doctrine\ORM\Mapping\ClassMetadataFactory->loadMetadata(NULL)
        #3 /var/www/invoiceCreator/src/thirdPartyComponents/doctrine/Doctrine/ORM/EntityManager.php(257): Doctrine\ORM\Mapping\ClassMetadataFactory->getMetadataFor(NULL)
        #4 /var/www/invoiceCreator/src/thirdPartyComponents/doctrine/Doctrine/ORM/UnitOfWork.php(1862): Doctrine\ORM\EntityManager->getClassMetadata(NULL)
        #5 /var/www/invoiceCreator/src/thirdPartyComponents/doctrine/Doctrine/ORM/Internal/Hydration/SimpleObjectHydrator.php(139): Doctrine\ORM\UnitOfWork->createEntity(NULL, Array, Array)
        #6 /var/www/invoiceCreator/src/thirdPartyComponents/doctrine/Doctrine/ORM/Internal/Hydration/SimpleObjectHydrator.php(44): Doctrine\ORM\Internal\Hydration\SimpleObjectHydrator->_hydrateRow(Array, Array, Array)
        #7 /var/www/invoiceCreator/src/thirdPartyComponents/doctrine/Doctrine/ORM/Internal/Hydration/AbstractHydrator.php(99): Doctrine\ORM\Internal\Hydration\SimpleObjectHydrator->_hydrateAll()
        #8 /var/www/invoiceCreator/src/thirdPartyComponents/doctrine/Doctrine/ORM/Persisters/BasicEntityPersister.php(581): Doctrine\ORM\Internal\Hydration\AbstractHydrator->hydrateAll(Object(Doctrine\DBAL\Driver\PDOStatement), Object(Doctrine\ORM\Query\ResultSetMapping), Array)
        #9 /var/www/invoiceCreator/src/persistentData/doctrine/proxies/persistentDatamodelcoreinvoiceCreatorGrandInvoiceTotalProxy.php(23): Doctrine\ORM\Persisters\BasicEntityPersister->load(Array, Object(persistentData\doctrine\proxies\persistentDatamodelcoreinvoiceCreatorGrandInvoiceTotalProxy))
        #10 /var/www/invoiceCreator/src/thirdPartyComponents/doctrine/Doctrine/ORM/UnitOfWork.php(1718): persistentData\doctrine\proxies\persistentDatamodelcoreinvoiceCreatorGrandInvoiceTotalProxy->__load()
        #11 /var/www/invoiceCreator/src/thirdPartyComponents/doctrine/Doctrine/ORM/UnitOfWork.php(1303): Doctrine\ORM\UnitOfWork->cascadeRemove(Object(persistentData\doctrine\proxies\persistentDatamodelcoreinvoiceCreatorGrandInvoiceTotalProxy), Array)
        #12 /var/www/invoiceCreator/src/thirdPartyComponents/doctrine/Doctrine/ORM/UnitOfWork.php(1728): Doctrine\ORM\UnitOfWork->doRemove(Object(persistentData\doctrine\proxies\persistentDatamodelcoreinvoiceCreatorGrandInvoiceTotalProxy), Array)
        #13 /var/www/invoiceCreator/src/thirdPartyComponents/doctrine/Doctrine/ORM/UnitOfWork.php(1303): Doctrine\ORM\UnitOfWork->cascadeRemove(Object(persistentData\model\core\invoiceCreator\Invoice), Array)
        #14 /var/www/invoiceCreator/src/thirdPartyComponents/doctrine/Doctrine/ORM/UnitOfWork.php(1725): Doctrine\ORM\UnitOfWork->doRemove(Object(persistentData\model\core\invoiceCreator\Invoice), Array)
        #15 /var/www/invoiceCreator/src/thirdPartyComponents/doctrine/Doctrine/ORM/UnitOfWork.php(1303): Doctrine\ORM\UnitOfWork->cascadeRemove(Object(persistentData\doctrine\proxies\persistentDatamodelcoreinvoiceCreatorInvoiceCreatorResultProxy), Array)
        #16 /var/www/invoiceCreator/src/thirdPartyComponents/doctrine/Doctrine/ORM/UnitOfWork.php(1728): Doctrine\ORM\UnitOfWork->doRemove(Object(persistentData\doctrine\proxies\persistentDatamodelcoreinvoiceCreatorInvoiceCreatorResultProxy), Array)
        #17 /var/www/invoiceCreator/src/thirdPartyComponents/doctrine/Doctrine/ORM/UnitOfWork.php(1303): Doctrine\ORM\UnitOfWork->cascadeRemove(Object(persistentData\model\core\Run), Array)
        #18 /var/www/invoiceCreator/src/thirdPartyComponents/doctrine/Doctrine/ORM/UnitOfWork.php(1279): Doctrine\ORM\UnitOfWork->doRemove(Object(persistentData\model\core\Run), Array)
        #19 /var/www/invoiceCreator/src/thirdPartyComponents/doctrine/Doctrine/ORM/EntityManager.php(481): Doctrine\ORM\UnitOfWork->remove(Object(persistentData\model\core\Run))
        #20 /var/www/invoiceCreator/src/persistentData/DB.php(212): Doctrine\ORM\EntityManager->remove(Object(persistentData\model\core\Run))
        #21 /var/www/invoiceCreator/src/frontend/mainPage/MainPageActions.php(50): persistentData\DB::delete(Object(persistentData\model\core\Run))
        #22 /var/www/invoiceCreator/src/frontend/mainPage/MainPageAdapter.php(87): frontend\mainPage\MainPageActions::deleteRun('23')
        #23 /var/www/invoiceCreator/documentRoot/admin/index.php(10): frontend\mainPage\MainPageAdapter::handleRequest()
        #24

        {main}
        Show
        Daniel Alvarez Arribas added a comment - Here is a complete stack trace: #0 /var/www/invoiceCreator/src/thirdPartyComponents/doctrine/Doctrine/ORM/Mapping/ClassMetadataFactory.php(364): Doctrine\ORM\Mapping\ClassMetadata->__construct(NULL) #1 /var/www/invoiceCreator/src/thirdPartyComponents/doctrine/Doctrine/ORM/Mapping/ClassMetadataFactory.php(265): Doctrine\ORM\Mapping\ClassMetadataFactory->newClassMetadataInstance(NULL) #2 /var/www/invoiceCreator/src/thirdPartyComponents/doctrine/Doctrine/ORM/Mapping/ClassMetadataFactory.php(170): Doctrine\ORM\Mapping\ClassMetadataFactory->loadMetadata(NULL) #3 /var/www/invoiceCreator/src/thirdPartyComponents/doctrine/Doctrine/ORM/EntityManager.php(257): Doctrine\ORM\Mapping\ClassMetadataFactory->getMetadataFor(NULL) #4 /var/www/invoiceCreator/src/thirdPartyComponents/doctrine/Doctrine/ORM/UnitOfWork.php(1862): Doctrine\ORM\EntityManager->getClassMetadata(NULL) #5 /var/www/invoiceCreator/src/thirdPartyComponents/doctrine/Doctrine/ORM/Internal/Hydration/SimpleObjectHydrator.php(139): Doctrine\ORM\UnitOfWork->createEntity(NULL, Array, Array) #6 /var/www/invoiceCreator/src/thirdPartyComponents/doctrine/Doctrine/ORM/Internal/Hydration/SimpleObjectHydrator.php(44): Doctrine\ORM\Internal\Hydration\SimpleObjectHydrator->_hydrateRow(Array, Array, Array) #7 /var/www/invoiceCreator/src/thirdPartyComponents/doctrine/Doctrine/ORM/Internal/Hydration/AbstractHydrator.php(99): Doctrine\ORM\Internal\Hydration\SimpleObjectHydrator->_hydrateAll() #8 /var/www/invoiceCreator/src/thirdPartyComponents/doctrine/Doctrine/ORM/Persisters/BasicEntityPersister.php(581): Doctrine\ORM\Internal\Hydration\AbstractHydrator->hydrateAll(Object(Doctrine\DBAL\Driver\PDOStatement), Object(Doctrine\ORM\Query\ResultSetMapping), Array) #9 /var/www/invoiceCreator/src/persistentData/doctrine/proxies/persistentDatamodelcoreinvoiceCreatorGrandInvoiceTotalProxy.php(23): Doctrine\ORM\Persisters\BasicEntityPersister->load(Array, Object(persistentData\doctrine\proxies\persistentDatamodelcoreinvoiceCreatorGrandInvoiceTotalProxy)) #10 /var/www/invoiceCreator/src/thirdPartyComponents/doctrine/Doctrine/ORM/UnitOfWork.php(1718): persistentData\doctrine\proxies\persistentDatamodelcoreinvoiceCreatorGrandInvoiceTotalProxy->__load() #11 /var/www/invoiceCreator/src/thirdPartyComponents/doctrine/Doctrine/ORM/UnitOfWork.php(1303): Doctrine\ORM\UnitOfWork->cascadeRemove(Object(persistentData\doctrine\proxies\persistentDatamodelcoreinvoiceCreatorGrandInvoiceTotalProxy), Array) #12 /var/www/invoiceCreator/src/thirdPartyComponents/doctrine/Doctrine/ORM/UnitOfWork.php(1728): Doctrine\ORM\UnitOfWork->doRemove(Object(persistentData\doctrine\proxies\persistentDatamodelcoreinvoiceCreatorGrandInvoiceTotalProxy), Array) #13 /var/www/invoiceCreator/src/thirdPartyComponents/doctrine/Doctrine/ORM/UnitOfWork.php(1303): Doctrine\ORM\UnitOfWork->cascadeRemove(Object(persistentData\model\core\invoiceCreator\Invoice), Array) #14 /var/www/invoiceCreator/src/thirdPartyComponents/doctrine/Doctrine/ORM/UnitOfWork.php(1725): Doctrine\ORM\UnitOfWork->doRemove(Object(persistentData\model\core\invoiceCreator\Invoice), Array) #15 /var/www/invoiceCreator/src/thirdPartyComponents/doctrine/Doctrine/ORM/UnitOfWork.php(1303): Doctrine\ORM\UnitOfWork->cascadeRemove(Object(persistentData\doctrine\proxies\persistentDatamodelcoreinvoiceCreatorInvoiceCreatorResultProxy), Array) #16 /var/www/invoiceCreator/src/thirdPartyComponents/doctrine/Doctrine/ORM/UnitOfWork.php(1728): Doctrine\ORM\UnitOfWork->doRemove(Object(persistentData\doctrine\proxies\persistentDatamodelcoreinvoiceCreatorInvoiceCreatorResultProxy), Array) #17 /var/www/invoiceCreator/src/thirdPartyComponents/doctrine/Doctrine/ORM/UnitOfWork.php(1303): Doctrine\ORM\UnitOfWork->cascadeRemove(Object(persistentData\model\core\Run), Array) #18 /var/www/invoiceCreator/src/thirdPartyComponents/doctrine/Doctrine/ORM/UnitOfWork.php(1279): Doctrine\ORM\UnitOfWork->doRemove(Object(persistentData\model\core\Run), Array) #19 /var/www/invoiceCreator/src/thirdPartyComponents/doctrine/Doctrine/ORM/EntityManager.php(481): Doctrine\ORM\UnitOfWork->remove(Object(persistentData\model\core\Run)) #20 /var/www/invoiceCreator/src/persistentData/DB.php(212): Doctrine\ORM\EntityManager->remove(Object(persistentData\model\core\Run)) #21 /var/www/invoiceCreator/src/frontend/mainPage/MainPageActions.php(50): persistentData\DB::delete(Object(persistentData\model\core\Run)) #22 /var/www/invoiceCreator/src/frontend/mainPage/MainPageAdapter.php(87): frontend\mainPage\MainPageActions::deleteRun('23') #23 /var/www/invoiceCreator/documentRoot/admin/index.php(10): frontend\mainPage\MainPageAdapter::handleRequest() #24 {main}
        Hide
        Daniel Alvarez Arribas added a comment -

        Now I found the root cause. In the database, the doctrineTypeDiscriminator column was not initialized.

        Apparently, after adding the discriminator map to the entity class InvoiceTotal, the schema-tool:update command added the column, but never initialized it.

        This means that if a discriminator map is added to already existing entities, all instances existing in the database must be manually initialized with discriminator values. This is a classic pain in the arse, but thinking about it, it is logical, because the update command has nothing to do with data, it just manipulates the database schema. So conceptually, it is clean. I'll post another update once it finally works, because right now I the cascading delete still runs into an integrity constraint violation error.

        Show
        Daniel Alvarez Arribas added a comment - Now I found the root cause. In the database, the doctrineTypeDiscriminator column was not initialized. Apparently, after adding the discriminator map to the entity class InvoiceTotal, the schema-tool:update command added the column, but never initialized it. This means that if a discriminator map is added to already existing entities, all instances existing in the database must be manually initialized with discriminator values. This is a classic pain in the arse, but thinking about it, it is logical, because the update command has nothing to do with data, it just manipulates the database schema. So conceptually, it is clean. I'll post another update once it finally works, because right now I the cascading delete still runs into an integrity constraint violation error.
        Hide
        Daniel Alvarez Arribas added a comment -

        Up to now I have not been able to reproduce the integrity constraint violation in an isolated test case.

        It is there, reliably reproducible, but only in the context of the application. I know which constraint fails, and when, but not why. It should not. The only possibility is that things get deleted in the wrong order.

        I will close this as invalid, because anyway, technically it is not the original issue. I will go on to write a manual delete routine to avoid the cascading delete. Maybe on the way I will find out what exactly caused the integrity constraint violation.

        Show
        Daniel Alvarez Arribas added a comment - Up to now I have not been able to reproduce the integrity constraint violation in an isolated test case. It is there, reliably reproducible, but only in the context of the application. I know which constraint fails, and when, but not why. It should not. The only possibility is that things get deleted in the wrong order. I will close this as invalid, because anyway, technically it is not the original issue. I will go on to write a manual delete routine to avoid the cascading delete. Maybe on the way I will find out what exactly caused the integrity constraint violation.
        Hide
        Benjamin Eberlei added a comment -

        Added validation for this inside the hydrators. Hydration will now fail if a discriminator value is an empty string (0 is still allowed).

        Show
        Benjamin Eberlei added a comment - Added validation for this inside the hydrators. Hydration will now fail if a discriminator value is an empty string (0 is still allowed).

          People

          • Assignee:
            Guilherme Blanco
            Reporter:
            Daniel Alvarez Arribas
          • Votes:
            1 Vote for this issue
            Watchers:
            1 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved: