Doctrine 1
  1. Doctrine 1
  2. DC-24

"AS" clause in DQL crashes the further update of the row in one-many relationship

    Details

    • Type: Bug Bug
    • Status: Resolved
    • Priority: Blocker Blocker
    • Resolution: Won't Fix
    • Affects Version/s: 1.1.4
    • Fix Version/s: 1.2.0-ALPHA1
    • Component/s: Query
    • Labels:
      None

      Description

      moving from trac, issue #2410 by zyxist

      This ticket is related to the MySQL database and InnoDB engine with exporting all the information about the models (including foreign keys).

      I have two tables connected with one-to-many relationship. First, I select a row from a "master" table with attached a field from the related "servant" row:

      $master = Doctrine_Query::create()
      	->select('m.*, s.bar AS joe')
      	->from('Ticket_9999_Master m')
      	->innerJoin('m.Ticket_9999_Servant s')
      	->where('m.id = 1')
      	->fetchOne();
      

      Next, I modify one of the columns in the master row and save everything:

      $master->foo = 5;
      $master->save();
      

      Expected result: the master row is updated.

      Actual result: MySQL error:

      Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`doctrine`.`ticket_9999_master`, CONSTRAINT `ticket_9999_master_servant_id_ticket_9999_servant_id` FOREIGN KEY (`servant_id`) REFERENCES `ticket_9999_servant` (`id`))

      If we remove "AS joe" part from the query, it gives the correct result. I noticed that with this alias, Doctrine forgets to retrieve the primary key of the servant row and this produces an invalid UPDATE query. There are two possible ways to retrieve this:

      1. Use the data from the "Master" row, as we fetch everything, including the ID of the servant row. Doctrine knows about the relationship and potentially can use it.

      2. Simply select the servant id automatically with the "s.bar" column.

      Tested on software:
      PHP 5.2.10, PHP 5.3.0
      MySQL 5.1.36
      Doctrine 1.1-DEV (rev. 6217)

      1. 9999bTestCase.php
        4 kB
        Jacek Jędrzejewski
      2. 9999cTestCase.php
        4 kB
        Jacek Jędrzejewski
      3. 9999TestCase.php
        5 kB
        Jacek Jędrzejewski
      4. select_alias.diff
        1 kB
        Ionut E

        Activity

        Hide
        Ionut E added a comment -

        Caching seems to be badly implemented. See DC-41

        Show
        Ionut E added a comment - Caching seems to be badly implemented. See DC-41
        Hide
        Roman S. Borschel added a comment -

        Jacek,

        because it is broken by design. Whoever made the decision to put scalar values into record objects (I think it was Konsta) did not think that through. Doctrine itself does not make a distinction internally between "real" records and "records with only scalars". Hence why it pukes on save(). I just dont see a good way to fix this without significant work and breaking existing code. If you have an idea I am all ears. If we start to add the primary key even if only scalar values are selected of a class we start to make all kinds of queries impossible that compute aggregate values.

        Ionut,

        this has nothing to do with DC-41, as far as I can see.

        Show
        Roman S. Borschel added a comment - Jacek, because it is broken by design. Whoever made the decision to put scalar values into record objects (I think it was Konsta) did not think that through. Doctrine itself does not make a distinction internally between "real" records and "records with only scalars". Hence why it pukes on save(). I just dont see a good way to fix this without significant work and breaking existing code. If you have an idea I am all ears. If we start to add the primary key even if only scalar values are selected of a class we start to make all kinds of queries impossible that compute aggregate values. Ionut, this has nothing to do with DC-41 , as far as I can see.
        Hide
        Roman S. Borschel added a comment -

        Of course, if anyone has a decent idea on how to fix this inconsistency, ideally without breaking backwards-compatibility, that would be great.

        Show
        Roman S. Borschel added a comment - Of course, if anyone has a decent idea on how to fix this inconsistency, ideally without breaking backwards-compatibility, that would be great.
        Hide
        Jonathan H. Wage added a comment -

        In 1.2 I fixed this by removing the hydration of aggregate values in to the relationship record. This was something that was built by design in 1.0, we realized it was not good so in 1.1 I made it so the aggregate values ALSO went in to the root component, and I deprecated the use of the value from the relationship. So now, in 1.2 we can remove it. I committed your test case and it passes now with this changeset.

        Thanks, Jon

        Show
        Jonathan H. Wage added a comment - In 1.2 I fixed this by removing the hydration of aggregate values in to the relationship record. This was something that was built by design in 1.0, we realized it was not good so in 1.1 I made it so the aggregate values ALSO went in to the root component, and I deprecated the use of the value from the relationship. So now, in 1.2 we can remove it. I committed your test case and it passes now with this changeset. Thanks, Jon
        Hide
        Jacek Jędrzejewski added a comment - - edited

        Thanks Jon! Hell, I want doctrine 1.2 and symfony 1.3!

        BTW. it can be now marked as "fixed" xd

        Show
        Jacek Jędrzejewski added a comment - - edited Thanks Jon! Hell, I want doctrine 1.2 and symfony 1.3! BTW. it can be now marked as "fixed" xd

          People

          • Assignee:
            Jonathan H. Wage
            Reporter:
            Jacek Jędrzejewski
          • Votes:
            1 Vote for this issue
            Watchers:
            1 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved: