Doctrine 2 - ORM
  1. Doctrine 2 - ORM
  2. DDC-1494

Query results are overwritten by previous query.

    Details

    • Type: Bug Bug
    • Status: Awaiting Feedback
    • Priority: Minor Minor
    • Resolution: Unresolved
    • Affects Version/s: 2.1.2
    • Fix Version/s: None
    • Component/s: ORM
    • Security Level: All
    • Labels:
      None
    • Environment:
      PHP 5.3 + MySQL 5.5

      Description

      I am running a query that JOINs three tables, with a simple WHERE:

      $q = $em->createQuery("
      
      SELECT cat, n, c
      FROM Project_Model_NoticeCategory cat
      JOIN cat.notices n
      JOIN n.chapters c
      WHERE
      c.id = :chapter_id
      
      ");
      

      When I do this:

        $q->setParameter('chapter_id', 1);
        $a = $q->getResult();
      
        $q->setParameter('chapter_id', 2);
        $b = $q->getResult();
      

      $b always has the wrong results. Running the following code:

        $q->setParameter('chapter_id', 1);
        $a = $q->getResult();
      
        $q->setParameter('chapter_id', 2);
        $b = $q->getResult();
        $z = $q->getArrayResult();
      

      BUG Results: $b != $z (getArrayResult IS CORRECT, it refreshes the results) Note: $a==$b (which is wrong)

      Explanation:

      There is a chapter table, this has a many-to-many join to notices (these are meta info
      about the chapter – a little like tagging a blog post) the notices are grouped into
      categories.

      Data model:

      /**
       * @Entity
       * @Table(name="chapter")
       */
      class Project_Model_Chapter
      {
          /**
           * @Id @Column(type="integer")
           * @GeneratedValue(strategy="AUTO")
           */
          private $id;
       
          /** @Column(type="string") */
          private $title;
      
      	/**
      	 * @ManyToMany(targetEntity="Project_Model_Notice", mappedBy="chapters")
      	 */
      	private $notices;
      	
      	.... /lots of code snipped/ ....
      	
      }
      
      
      /**
       * @Entity
       * @Table(name="notice")
       */
      class Project_Model_Notice
      {
      	/**
           * @Id @Column(type="integer")
           * @GeneratedValue(strategy="AUTO")
           */
          private $id;
       
          /** @Column(type="string") */
          private $title;
      	
      	/**
      	 * @ManyToMany(targetEntity="Project_Model_Chapter", inversedBy="notices")
      	 * @JoinTable(name="chapter_notice")
      	 */
      	private $chapters;
      	
      	/**
      	 * @ManyToOne(targetEntity="Project_Model_NoticeCategory", inversedBy="notices")
      	 */
      	private $notice_category;
      	
      	.... /lots of code snipped/ ....
      	
      }
      
      /**
       * @Entity
       * @Table(name="notice_category")
       */
      class Project_Model_NoticeCategory
      {
          /**
           * @Id @Column(type="integer")
           * @GeneratedValue(strategy="AUTO")
           */
          private $id;
      	/** @Column(type="string") */
          private $title;
      	
      	/**
      	 * Bidirectional - One-To-Many (INVERSE SIDE)
      	 *
      	 * @OneToMany(targetEntity="Project_Model_Notice", mappedBy="notice_category", cascade={"persist", "remove"})
      	 */
      	private $notices;
      
      	.... /lots of code snipped/ ....
      	
      }
      

      Data fixtures:

      $tools = new \Project_Model_NoticeCategory;
      $tools->setTitle('Tools');
      		
      $spanner = new \Project_Model_Notice;
      $spanner->setTitle('spanner');
      $tools->addNotice($spanner);
      		
      $drill = new \Project_Model_Notice;
      $drill->setTitle('power drill');
      $tools->addNotice($drill);
      		
      $this->em->persist($tools);
      $this->em->flush();
      
      $tools = new \Project_Model_NoticeCategory;
      $tools->setTitle('Safety');
      		
      $gloves = new \Project_Model_Notice;
      $gloves->setTitle('gloves');
      $tools->addNotice($gloves);
      		
      $goggles = new \Project_Model_Notice;
      $goggles->setTitle('goggles');
      $tools->addNotice($goggles);
      		
      $this->em->persist($tools);
      $this->em->flush();
      
      $chapter1 = new \Project_Model_Chapter;
      $chapter1->setTitle('Chapter 1');
      $this->em->persist($chapter1);
      
      $chapter2 = new \Project_Model_Chapter;
      $chapter2->setTitle('Chapter 2');
      $this->em->persist($chapter2);
      
      $chapter1->addNotice($spanner);
      $chapter1->addNotice($gloves);
      
      $chapter2->addNotice($spanner);
      $chapter2->addNotice($gloves);
      $chapter2->addNotice($drill);
      $chapter2->addNotice($goggles);
      
      // now persist and flush everything
      

      Initial investigation:

      I think it has something to do with HINT_REFRESH ? Stepping through:

      ObjectHydrator->_hydrateRow
      ObjectHydrator->_getEntity

      when it requests the Project_Model_Category from the unit of work, it
      seems that the second query is simply grabbing the cached results from
      the first results. This MUST be wrong as the second query uses a
      different query (the ID changes) and all the results are wrong.

        Activity

        J created issue -
        J made changes -
        Field Original Value New Value
        Description I am running a query that JOINs three tables, with a simple WHERE:

        $q = $em->createQuery("

        SELECT cat, n, c
        FROM Project_Model_NoticeCategory cat
        JOIN cat.notices n
        JOIN n.chapters c
        WHERE
        c.id = :chapter_id

        ");

        When I do this:

          $q->setParameter('chapter_id', 1);
          $a = $q->getResult();

          $q->setParameter('chapter_id', 2);
          $b = $q->getResult();

        $b always has the wrong results. Running the following code:

          $q->setParameter('chapter_id', 1);
          $a = $q->getResult();

          $q->setParameter('chapter_id', 2);
          $b = $q->getResult();
          $z = $q2->getArrayResult();

        BUG Results: $b != $z (getArrayResult IS CORRECT, it refreshes the results) Note: $a==$b (which is wrong)

        Explanation:

        There is a chapter table, this has a many-to-many join to notices (these are meta info
        about the chapter -- a little like tagging a blog post) the notices are grouped into
        categories.

        Data model:

        /**
         * @Entity
         * @Table(name="chapter")
         */
        class Project_Model_Chapter
        {
            /**
             * @Id @Column(type="integer")
             * @GeneratedValue(strategy="AUTO")
             */
            private $id;
         
            /** @Column(type="string") */
            private $title;

        /**
        * @ManyToMany(targetEntity="Project_Model_Notice", mappedBy="chapters")
        */
        private $notices;

        .... /lots of code snipped/ ....

        }


        /**
         * @Entity
         * @Table(name="notice")
         */
        class Project_Model_Notice
        {
        /**
             * @Id @Column(type="integer")
             * @GeneratedValue(strategy="AUTO")
             */
            private $id;
         
            /** @Column(type="string") */
            private $title;

        /**
        * @ManyToMany(targetEntity="Project_Model_Chapter", inversedBy="notices")
        * @JoinTable(name="chapter_notice")
        */
        private $chapters;

        /**
        * @ManyToOne(targetEntity="Project_Model_NoticeCategory", inversedBy="notices")
        */
        private $notice_category;

        .... /lots of code snipped/ ....

        }

        /**
         * @Entity
         * @Table(name="notice_category")
         */
        class Project_Model_NoticeCategory
        {
            /**
             * @Id @Column(type="integer")
             * @GeneratedValue(strategy="AUTO")
             */
            private $id;
        /** @Column(type="string") */
            private $title;

        /**
        * Bidirectional - One-To-Many (INVERSE SIDE)
        *
        * @OneToMany(targetEntity="Project_Model_Notice", mappedBy="notice_category", cascade={"persist", "remove"})
        */
        private $notices;

        .... /lots of code snipped/ ....

        }


        Data fixtures:

        $tools = new \Project_Model_NoticeCategory;
        $tools->setTitle('Tools');

        $spanner = new \Project_Model_Notice;
        $spanner->setTitle('spanner');
        $tools->addNotice($spanner);

        $drill = new \Project_Model_Notice;
        $drill->setTitle('power drill');
        $tools->addNotice($drill);

        $this->em->persist($tools);
        $this->em->flush();

        $tools = new \Project_Model_NoticeCategory;
        $tools->setTitle('Safety');

        $gloves = new \Project_Model_Notice;
        $gloves->setTitle('gloves');
        $tools->addNotice($gloves);

        $goggles = new \Project_Model_Notice;
        $goggles->setTitle('goggles');
        $tools->addNotice($goggles);

        $this->em->persist($tools);
        $this->em->flush();

        $chapter1 = new \Project_Model_Chapter;
        $chapter1->setTitle('Chapter 1');
        $this->em->persist($chapter1);

        $chapter2 = new \Project_Model_Chapter;
        $chapter2->setTitle('Chapter 2');
        $this->em->persist($chapter2);

        $chapter1->addNotice($spanner);
        $chapter1->addNotice($gloves);

        $chapter2->addNotice($spanner);
        $chapter2->addNotice($gloves);
        $chapter2->addNotice($drill);
        $chapter2->addNotice($goggles);

        // now persist and flush everything


        Initial investigation:

        I think it has something to do with HINT_REFRESH ? Stepping through:

        ObjectHydrator->_hydrateRow
        ObjectHydrator->_getEntity

        when it requests the Project_Model_Category from the unit of work, it
        seems that the second query is simply grabbing the cached results from
        the first results. This MUST be wrong as the second query uses a
        different query (the ID changes) and all the results are wrong.

        I am running a query that JOINs three tables, with a simple WHERE:

        $q = $em->createQuery("

        SELECT cat, n, c
        FROM Project_Model_NoticeCategory cat
        JOIN cat.notices n
        JOIN n.chapters c
        WHERE
        c.id = :chapter_id

        ");

        When I do this:

          $q->setParameter('chapter_id', 1);
          $a = $q->getResult();

          $q->setParameter('chapter_id', 2);
          $b = $q->getResult();

        $b always has the wrong results. Running the following code:

          $q->setParameter('chapter_id', 1);
          $a = $q->getResult();

          $q->setParameter('chapter_id', 2);
          $b = $q->getResult();
          $z = $q->getArrayResult();

        BUG Results: $b != $z (getArrayResult IS CORRECT, it refreshes the results) Note: $a==$b (which is wrong)

        Explanation:

        There is a chapter table, this has a many-to-many join to notices (these are meta info
        about the chapter -- a little like tagging a blog post) the notices are grouped into
        categories.

        Data model:

        /**
         * @Entity
         * @Table(name="chapter")
         */
        class Project_Model_Chapter
        {
            /**
             * @Id @Column(type="integer")
             * @GeneratedValue(strategy="AUTO")
             */
            private $id;
         
            /** @Column(type="string") */
            private $title;

        /**
        * @ManyToMany(targetEntity="Project_Model_Notice", mappedBy="chapters")
        */
        private $notices;

        .... /lots of code snipped/ ....

        }


        /**
         * @Entity
         * @Table(name="notice")
         */
        class Project_Model_Notice
        {
        /**
             * @Id @Column(type="integer")
             * @GeneratedValue(strategy="AUTO")
             */
            private $id;
         
            /** @Column(type="string") */
            private $title;

        /**
        * @ManyToMany(targetEntity="Project_Model_Chapter", inversedBy="notices")
        * @JoinTable(name="chapter_notice")
        */
        private $chapters;

        /**
        * @ManyToOne(targetEntity="Project_Model_NoticeCategory", inversedBy="notices")
        */
        private $notice_category;

        .... /lots of code snipped/ ....

        }

        /**
         * @Entity
         * @Table(name="notice_category")
         */
        class Project_Model_NoticeCategory
        {
            /**
             * @Id @Column(type="integer")
             * @GeneratedValue(strategy="AUTO")
             */
            private $id;
        /** @Column(type="string") */
            private $title;

        /**
        * Bidirectional - One-To-Many (INVERSE SIDE)
        *
        * @OneToMany(targetEntity="Project_Model_Notice", mappedBy="notice_category", cascade={"persist", "remove"})
        */
        private $notices;

        .... /lots of code snipped/ ....

        }


        Data fixtures:

        $tools = new \Project_Model_NoticeCategory;
        $tools->setTitle('Tools');

        $spanner = new \Project_Model_Notice;
        $spanner->setTitle('spanner');
        $tools->addNotice($spanner);

        $drill = new \Project_Model_Notice;
        $drill->setTitle('power drill');
        $tools->addNotice($drill);

        $this->em->persist($tools);
        $this->em->flush();

        $tools = new \Project_Model_NoticeCategory;
        $tools->setTitle('Safety');

        $gloves = new \Project_Model_Notice;
        $gloves->setTitle('gloves');
        $tools->addNotice($gloves);

        $goggles = new \Project_Model_Notice;
        $goggles->setTitle('goggles');
        $tools->addNotice($goggles);

        $this->em->persist($tools);
        $this->em->flush();

        $chapter1 = new \Project_Model_Chapter;
        $chapter1->setTitle('Chapter 1');
        $this->em->persist($chapter1);

        $chapter2 = new \Project_Model_Chapter;
        $chapter2->setTitle('Chapter 2');
        $this->em->persist($chapter2);

        $chapter1->addNotice($spanner);
        $chapter1->addNotice($gloves);

        $chapter2->addNotice($spanner);
        $chapter2->addNotice($gloves);
        $chapter2->addNotice($drill);
        $chapter2->addNotice($goggles);

        // now persist and flush everything


        Initial investigation:

        I think it has something to do with HINT_REFRESH ? Stepping through:

        ObjectHydrator->_hydrateRow
        ObjectHydrator->_getEntity

        when it requests the Project_Model_Category from the unit of work, it
        seems that the second query is simply grabbing the cached results from
        the first results. This MUST be wrong as the second query uses a
        different query (the ID changes) and all the results are wrong.

        Benjamin Eberlei made changes -
        Description I am running a query that JOINs three tables, with a simple WHERE:

        $q = $em->createQuery("

        SELECT cat, n, c
        FROM Project_Model_NoticeCategory cat
        JOIN cat.notices n
        JOIN n.chapters c
        WHERE
        c.id = :chapter_id

        ");

        When I do this:

          $q->setParameter('chapter_id', 1);
          $a = $q->getResult();

          $q->setParameter('chapter_id', 2);
          $b = $q->getResult();

        $b always has the wrong results. Running the following code:

          $q->setParameter('chapter_id', 1);
          $a = $q->getResult();

          $q->setParameter('chapter_id', 2);
          $b = $q->getResult();
          $z = $q->getArrayResult();

        BUG Results: $b != $z (getArrayResult IS CORRECT, it refreshes the results) Note: $a==$b (which is wrong)

        Explanation:

        There is a chapter table, this has a many-to-many join to notices (these are meta info
        about the chapter -- a little like tagging a blog post) the notices are grouped into
        categories.

        Data model:

        /**
         * @Entity
         * @Table(name="chapter")
         */
        class Project_Model_Chapter
        {
            /**
             * @Id @Column(type="integer")
             * @GeneratedValue(strategy="AUTO")
             */
            private $id;
         
            /** @Column(type="string") */
            private $title;

        /**
        * @ManyToMany(targetEntity="Project_Model_Notice", mappedBy="chapters")
        */
        private $notices;

        .... /lots of code snipped/ ....

        }


        /**
         * @Entity
         * @Table(name="notice")
         */
        class Project_Model_Notice
        {
        /**
             * @Id @Column(type="integer")
             * @GeneratedValue(strategy="AUTO")
             */
            private $id;
         
            /** @Column(type="string") */
            private $title;

        /**
        * @ManyToMany(targetEntity="Project_Model_Chapter", inversedBy="notices")
        * @JoinTable(name="chapter_notice")
        */
        private $chapters;

        /**
        * @ManyToOne(targetEntity="Project_Model_NoticeCategory", inversedBy="notices")
        */
        private $notice_category;

        .... /lots of code snipped/ ....

        }

        /**
         * @Entity
         * @Table(name="notice_category")
         */
        class Project_Model_NoticeCategory
        {
            /**
             * @Id @Column(type="integer")
             * @GeneratedValue(strategy="AUTO")
             */
            private $id;
        /** @Column(type="string") */
            private $title;

        /**
        * Bidirectional - One-To-Many (INVERSE SIDE)
        *
        * @OneToMany(targetEntity="Project_Model_Notice", mappedBy="notice_category", cascade={"persist", "remove"})
        */
        private $notices;

        .... /lots of code snipped/ ....

        }


        Data fixtures:

        $tools = new \Project_Model_NoticeCategory;
        $tools->setTitle('Tools');

        $spanner = new \Project_Model_Notice;
        $spanner->setTitle('spanner');
        $tools->addNotice($spanner);

        $drill = new \Project_Model_Notice;
        $drill->setTitle('power drill');
        $tools->addNotice($drill);

        $this->em->persist($tools);
        $this->em->flush();

        $tools = new \Project_Model_NoticeCategory;
        $tools->setTitle('Safety');

        $gloves = new \Project_Model_Notice;
        $gloves->setTitle('gloves');
        $tools->addNotice($gloves);

        $goggles = new \Project_Model_Notice;
        $goggles->setTitle('goggles');
        $tools->addNotice($goggles);

        $this->em->persist($tools);
        $this->em->flush();

        $chapter1 = new \Project_Model_Chapter;
        $chapter1->setTitle('Chapter 1');
        $this->em->persist($chapter1);

        $chapter2 = new \Project_Model_Chapter;
        $chapter2->setTitle('Chapter 2');
        $this->em->persist($chapter2);

        $chapter1->addNotice($spanner);
        $chapter1->addNotice($gloves);

        $chapter2->addNotice($spanner);
        $chapter2->addNotice($gloves);
        $chapter2->addNotice($drill);
        $chapter2->addNotice($goggles);

        // now persist and flush everything


        Initial investigation:

        I think it has something to do with HINT_REFRESH ? Stepping through:

        ObjectHydrator->_hydrateRow
        ObjectHydrator->_getEntity

        when it requests the Project_Model_Category from the unit of work, it
        seems that the second query is simply grabbing the cached results from
        the first results. This MUST be wrong as the second query uses a
        different query (the ID changes) and all the results are wrong.

        I am running a query that JOINs three tables, with a simple WHERE:

        {code}
        $q = $em->createQuery("

        SELECT cat, n, c
        FROM Project_Model_NoticeCategory cat
        JOIN cat.notices n
        JOIN n.chapters c
        WHERE
        c.id = :chapter_id

        ");
        {code}

        When I do this:

        {code}
          $q->setParameter('chapter_id', 1);
          $a = $q->getResult();

          $q->setParameter('chapter_id', 2);
          $b = $q->getResult();
        {code}

        $b always has the wrong results. Running the following code:

        {code}
          $q->setParameter('chapter_id', 1);
          $a = $q->getResult();

          $q->setParameter('chapter_id', 2);
          $b = $q->getResult();
          $z = $q->getArrayResult();
        {code}

        BUG Results: $b != $z (getArrayResult IS CORRECT, it refreshes the results) Note: $a==$b (which is wrong)

        Explanation:

        There is a chapter table, this has a many-to-many join to notices (these are meta info
        about the chapter -- a little like tagging a blog post) the notices are grouped into
        categories.

        Data model:

        {code}
        /**
         * @Entity
         * @Table(name="chapter")
         */
        class Project_Model_Chapter
        {
            /**
             * @Id @Column(type="integer")
             * @GeneratedValue(strategy="AUTO")
             */
            private $id;
         
            /** @Column(type="string") */
            private $title;

        /**
        * @ManyToMany(targetEntity="Project_Model_Notice", mappedBy="chapters")
        */
        private $notices;

        .... /lots of code snipped/ ....

        }


        /**
         * @Entity
         * @Table(name="notice")
         */
        class Project_Model_Notice
        {
        /**
             * @Id @Column(type="integer")
             * @GeneratedValue(strategy="AUTO")
             */
            private $id;
         
            /** @Column(type="string") */
            private $title;

        /**
        * @ManyToMany(targetEntity="Project_Model_Chapter", inversedBy="notices")
        * @JoinTable(name="chapter_notice")
        */
        private $chapters;

        /**
        * @ManyToOne(targetEntity="Project_Model_NoticeCategory", inversedBy="notices")
        */
        private $notice_category;

        .... /lots of code snipped/ ....

        }

        /**
         * @Entity
         * @Table(name="notice_category")
         */
        class Project_Model_NoticeCategory
        {
            /**
             * @Id @Column(type="integer")
             * @GeneratedValue(strategy="AUTO")
             */
            private $id;
        /** @Column(type="string") */
            private $title;

        /**
        * Bidirectional - One-To-Many (INVERSE SIDE)
        *
        * @OneToMany(targetEntity="Project_Model_Notice", mappedBy="notice_category", cascade={"persist", "remove"})
        */
        private $notices;

        .... /lots of code snipped/ ....

        }
        {code}

        Data fixtures:

        {code}
        $tools = new \Project_Model_NoticeCategory;
        $tools->setTitle('Tools');

        $spanner = new \Project_Model_Notice;
        $spanner->setTitle('spanner');
        $tools->addNotice($spanner);

        $drill = new \Project_Model_Notice;
        $drill->setTitle('power drill');
        $tools->addNotice($drill);

        $this->em->persist($tools);
        $this->em->flush();

        $tools = new \Project_Model_NoticeCategory;
        $tools->setTitle('Safety');

        $gloves = new \Project_Model_Notice;
        $gloves->setTitle('gloves');
        $tools->addNotice($gloves);

        $goggles = new \Project_Model_Notice;
        $goggles->setTitle('goggles');
        $tools->addNotice($goggles);

        $this->em->persist($tools);
        $this->em->flush();

        $chapter1 = new \Project_Model_Chapter;
        $chapter1->setTitle('Chapter 1');
        $this->em->persist($chapter1);

        $chapter2 = new \Project_Model_Chapter;
        $chapter2->setTitle('Chapter 2');
        $this->em->persist($chapter2);

        $chapter1->addNotice($spanner);
        $chapter1->addNotice($gloves);

        $chapter2->addNotice($spanner);
        $chapter2->addNotice($gloves);
        $chapter2->addNotice($drill);
        $chapter2->addNotice($goggles);

        // now persist and flush everything
        {code}

        Initial investigation:

        I think it has something to do with HINT_REFRESH ? Stepping through:

        ObjectHydrator->_hydrateRow
        ObjectHydrator->_getEntity

        when it requests the Project_Model_Category from the unit of work, it
        seems that the second query is simply grabbing the cached results from
        the first results. This MUST be wrong as the second query uses a
        different query (the ID changes) and all the results are wrong.

        Benjamin Eberlei made changes -
        Attachment DDC1494Test.php [ 11131 ]
        Benjamin Eberlei made changes -
        Priority Major [ 3 ] Minor [ 4 ]
        Benjamin Eberlei made changes -
        Workflow jira [ 13188 ] jira-feedback [ 13990 ]
        Benjamin Eberlei made changes -
        Workflow jira-feedback [ 13990 ] jira-feedback2 [ 15854 ]
        Benjamin Eberlei made changes -
        Workflow jira-feedback2 [ 15854 ] jira-feedback3 [ 18110 ]
        Alexander made changes -
        Status Open [ 1 ] Awaiting Feedback [ 10000 ]

          People

          • Assignee:
            Benjamin Eberlei
            Reporter:
            J
          • Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

            • Created:
              Updated: