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

        Evgeniy Afonichev created issue -
        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
          • Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

            • Created:
              Updated: