[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    

 Description   
            $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')
               ->orderBy('r.registrationDateTime')
               ->setParameter(1, $bracket);

Generates:

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.



 Comments   
Comment by Benjamin Eberlei [ 28/Jul/11 ]

Fixed

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')
               ->select('p')
               ->where('p.stageBracket = ?1')
               ->andWhere('p.player = ?2')
               ->setParameter(1, $bracket)
               ->setParameter(2, $player)
               ->getQuery()
               ->getOneOrNullResult();

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 ]

Hi,

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?

Cheers,

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')
           ->select('te1')
           ->where('te1.testEntity2 = ?1')
           ->setParameter(1, 0)
           ->getQuery()
           ->getOneOrNullResult();

I'm running this against 2.1.0 + this change.

Thanks.

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 Sat Oct 25 11:13:27 UTC 2014 using JIRA 6.2.3#6260-sha1:63ef1d6dac3f4f4d7db4c1effd405ba38ccdc558.