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

Two words from me:

Well, the problem is not the saving process but the thing that selecting a relation only with aliased field does not retrieve the ID. I mean that this query:

SELECT m.*, s.bar FROM Master m, m.Servant S

is also selecting s.id field, because Doctrine always needs it, right? Adding an alias "s.bar AS something" makes the query not select "s.id", so in save() Doctrine thinks Servant is a new record. And that's why mysql throws "Integrity constraint violation".

Patch for the 1.1 branch. Fixes the alias problem but messes a lot of other tests which are query related.

My fix forces all fields of the table with the column alias to be inserted in sql as well.

You just need to select the id.

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

If you have a valid suggestion for how to fix this or make it "smarter" we're open to suggestions.

lol, I know that selecting ID solves this issue. But it is illogical, because a query on field without alias select ID automatically while aliased - no.

It doesn't automatically select the id unless you also select some real property. In your case you selected and aliased so the object has no real properties loaded.

It actually is logical but I absolutely understand why it looks illogical to you.

Doctrine 1.x auto-adds the primary key only if it is needed. It is needed only if the hydration mode is record/array and any non-scalar value is selected. If neither the hydration mode requires the primary key, nor the selected result expressions contains object fields (not object fields selected as scalar values, i.e. what AS .. does) then it is not added.

"select x.foo as something" only selects a scalar value. The fact that makes this so confusing is that scalar values are put into Record objects in Doctrine 1.x, so to you it looks like a full object was hydrated when in fact it was just a scalar value.

in Doctrine 1.x this is unfortunate but expected behavior. In Doctrine 2 this confusion will not arise as there is a clear separation and scalar values are never put into objects.

Jacek, could you please show me an result of the select from the test? On my computer foreign key gets deleted and I have a really hard time beliving this is logical and expected behaviour.

Roman S. Borschel - so if it is not a "real" record but only a "plain" record with a scalar value, why doctrine tries to use it for save? I'm not sure about the internals of this process but something must be wrong there

lonut: this is from toArray(true)

Array
(
    [id] => 1
    [foo] => 6
    [servant_id] =>
    [Ticket_9999_Servant] => Array
        (
            [id] =>
            [bar] =>
            [joe] => 6
        )

    [joe] => 6
)

OKOK, so now try new testcase (attachment 9999b):
I have checked it and this issue is not only related to MySQL and InnoDB. This testcase fails with this error:
Doctrine_Ticket_9999b_TestCase : method testTest failed on line 72 SQLSTATE[23000]: Integrity constraint violation: 19 ticket_9999b__master.servant_id may not be NULL

So now let's try this without notnull constraint on "servant_id" (testcase 9999c):
It works ok, no error on saving. But now let's retrieve that record from DB:

$master2 = Doctrine_Query::create()->select('m.*')->from('Ticket_9999c_Master m')->where('m.id = 1')->fetchOne(array(), Doctrine::HYDRATE_ARRAY);

"servant_id" is empty! Please don't tell me it is a correct behavior!

Caching seems to be badly implemented. See DC-41

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.

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

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

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

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