Doctrine 1

Using ->limit() in conjunction with many-to-many with mysql generates wrong SQL

Details

  • Type: Bug Bug
  • Status: Open Open
  • Priority: Major 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

Activity

Evgeniy Afonichev made changes -
Field Original Value New Value
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
{code}
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
{code}

And here's query which generates wrong SQL
{code}
Doctrine_Core::getTable('User')
  ->createQuery('User')
  ->leftJoin('User.Operators Operator')
  ->addWhere('Operator.type = ?', 1)
  ->limit(10)
  ->offset(0)
  ->execute()
;
{code}

Expected SQL generated:
{code:sql}
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
{code}

Actual SQL generated:
{code:sql}
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
{code} 		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
{code}
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
{code}

And here's query which generates wrong SQL
{code}
Doctrine_Core::getTable('User')
  ->createQuery('User')
  ->leftJoin('User.Operators Operator')
  ->addWhere('Operator.type = ?', 1)
  ->limit(10)
  ->offset(0)
  ->execute()
;
{code}

Expected SQL generated:
{code:sql}
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
{code}

Actual SQL generated:
{code:sql}
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
{code}

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

People

  • Assignee:
    Guilherme Blanco
    Reporter:
    Evgeniy Afonichev
Vote (0)
Watch (0)

Dates

  • Created:
    Updated: