[DDC-1225] Invalid SQL generated (extra comma) when joining to entity with composite PK Created: 23/Jun/11  Updated: 29/Aug/11  Resolved: 15/Aug/11

Status: Resolved
Project: Doctrine 2 - ORM
Component/s: ORM
Affects Version/s: 2.1
Fix Version/s: 2.1.2, 2.2
Security Level: All

Type: Bug Priority: Critical
Reporter: Glen Ainscow Assignee: Guilherme Blanco
Resolution: Fixed Votes: 1
Labels: None

Attachments: Zip Archive models.zip     Zip Archive models.zip    

            $qb->from('Tournaments_Model_StageBracketTeamRegistration', 'r')
               ->innerJoin('r.teamSelection', 'ts')
               ->innerJoin('ts.players', 'tsp')
               ->select('r, ts, tsp')
               ->where('r.stageBracket = ?1')
               ->andWhere('r.opponentIsReserve = false')
               ->setParameter(1, $bracket);


SELECT s0_.id AS id0, s0_.opponent_is_reserve AS opponent_is_reserve1, s0_.opponent_checked_in AS opponent_checked_in2, s0_.registration_date_time AS registration_date_time3, t1_.id AS id4,, s0_.type AS type5, s0_.stage_bracket_id AS stage_bracket_id6, s2_.team_selection_id AS team_selection_id7, t1_.team_id AS team_id8, t3_.team_selection_id AS team_selection_id9, t3_.player_id AS player_id10
FROM stage_bracket_team_registrations s2_
INNER JOIN stage_bracket_registrations s0_ ON s2_.id = s0_.id
INNER JOIN team_selections t1_ ON s2_.team_selection_id = t1_.id
INNER JOIN team_selection_players t3_ ON t1_.id = t3_.team_selection_id
WHERE s0_.stage_bracket_id = 22 AND s0_.opponent_is_reserve = 0
ORDER BY s0_.registration_date_time ASC

Note the 2nd comma after "t1_.id AS id4". TeamSelectionPlayer uses a composite PK. I have attached the relevant entity classes.

Comment by Benjamin Eberlei [ 28/Jul/11 ]


Comment by Glen Ainscow [ 12/Aug/11 ]

Where can I find the changeset? Would it be easy for me to apply the changes to 2.1.0?

Comment by Glen Ainscow [ 12/Aug/11 ]

No worries, found the changes here: https://github.com/doctrine/doctrine2/commit/196632978cf39bc3914e14739767cb5b72a8df9d

Comment by Glen Ainscow [ 13/Aug/11 ]

This is still an issue:

            $qb->from('Tournaments_Model_StageBracketRegisteredPlayer', 'p')
               ->where('p.stageBracket = ?1')
               ->andWhere('p.player = ?2')
               ->setParameter(1, $bracket)
               ->setParameter(2, $player)

Results in:

SELECT , s0_.stage_bracket_id AS stage_bracket_id0, s0_.player_id AS player_id1, s0_.game_account_id AS game_account_id2 FROM stage_bracket_registered_players s0_ WHERE s0_.stage_bracket_id = 14 AND s0_.player_id = 5
Comment by Guilherme Blanco [ 14/Aug/11 ]


I attempted to create a failing test case for this issue, but either the provided entities are not enough or the issue is not reproducible anymore (it was already fixed in latest 2.2-DEV).

Could you please try to compile everything into a test case?


Comment by Glen Ainscow [ 15/Aug/11 ]

Hi Guilherme,

I'll attach 2 simple entities for testing. You can run the following query:

        $qb->from('App_Model_TestEntity1', 'te1')
           ->where('te1.testEntity2 = ?1')
           ->setParameter(1, 0)

I'm running this against 2.1.0 + this change.


Comment by Guilherme Blanco [ 15/Aug/11 ]

Fixed in this commit https://github.com/doctrine/doctrine2/commit/6857134f36097187ab2f0d932f4f1d9ffab12854

Thanks for the report!

Comment by Benjamin Eberlei [ 29/Aug/11 ]

Merged into 2.1.x

Generated at Sun Apr 20 11:27:24 UTC 2014 using JIRA 6.2.3#6260-sha1:63ef1d6dac3f4f4d7db4c1effd405ba38ccdc558.