Details
-
Type:
Bug
-
Status:
Open
-
Priority:
Major
-
Resolution: Unresolved
-
Affects Version/s: None
-
Fix Version/s: None
-
Component/s: Query
-
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