Doctrine 1
  1. Doctrine 1
  2. DC-1041

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

    Details

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

          People

          • Assignee:
            Guilherme Blanco
            Reporter:
            Evgeniy Afonichev
          • Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

            • Created:
              Updated: