[DC-1041] Using ->limit() in conjunction with many-to-many with mysql generates wrong SQL Created: 30/Nov/11  Updated: 30/Nov/11

Status: Open
Project: Doctrine 1
Component/s: Query
Affects Version/s: None
Fix Version/s: None

Type: Bug Priority: Major
Reporter: Evgeniy Afonichev Assignee: Guilherme Blanco
Resolution: Unresolved Votes: 0
Labels: None
Environment:

mysql



 Description   

Using ->limit() in conjunction with many-to-many relationships with mysql leads to strange SQL generated. The condition id IS NULL is added in such case which is not correct at all.

Here's example schema

User:
  columns:
    username: { type: string(255) }
  relations:
    Operators:
      foreignAlias: Users
      class:        Operator
      refClass:     OperatorUser

Operator:
  columns:
    username: { type: string(255) }
    type:     { type: integer }


OperatorUser:
  columns:
    user_id:      { type: integer }
    operator_id:  { type: integer }
  relations:
    Operator:
      foreignAlias: OperatorUser
    User:
      foreignAlias: OperatorUser

And here's query which generates wrong SQL

Doctrine_Core::getTable('User')
  ->createQuery('User')
  ->leftJoin('User.Operators Operator')
  ->addWhere('Operator.type = ?', 1)
  ->limit(10)
  ->offset(0)
  ->execute()
;

Expected SQL generated:

SELECT u.id AS u__id, u.username AS u__username, o.id AS o__id, o.username AS o__username, o.type AS o__type
FROM user u
LEFT JOIN operator_user o2  ON (u.id = o2.user_id)
LEFT JOIN operator o        ON o.id = o2.operator_id
WHERE (o.type = '1')
LIMIT 10

Actual SQL generated:

SELECT u.id AS u__id, u.username AS u__username, o.id AS o__id, o.username AS o__username, o.type AS o__type
FROM user u
LEFT JOIN operator_user o2  ON (u.id = o2.user_id)
LEFT JOIN operator o        ON o.id = o2.operator_id
WHERE
  u.id IS NULL # is not expected
  AND (o.type = '1')
# there's no LIMIT clause

Seems like here's code which causes the bug https://github.com/doctrine/doctrine1/blob/master/lib/Doctrine/Query.php#L1307


Generated at Wed Apr 23 12:40:30 UTC 2014 using JIRA 6.2.3#6260-sha1:63ef1d6dac3f4f4d7db4c1effd405ba38ccdc558.