Doctrine 2 - ORM
  1. Doctrine 2 - ORM
  2. DDC-3063

Unexpected behavior with 'WHERE NOT IN' and empty array

    Details

    • Type: Bug Bug
    • Status: Reopened
    • Priority: Major Major
    • Resolution: Unresolved
    • Affects Version/s: 2.4
    • Fix Version/s: None
    • Component/s: DQL
    • Security Level: All
    • Labels:
      None
    • Environment:
      CentOS, PHP 5.4, mysql

      Description

      I tried to set version to 2.4.0 but was prevented.

      Assume the set 'n' contains 10 records, all with id > 0.

      ->andWhere('n.id NOT IN (:ids)')->setParameter('ids', [])
      returns 0 records.
      
      ->andWhere('n.id NOT IN (:ids)')->setParameter('ids', [0])
      returns 10 records.
      

        Activity

        Hide
        Steve Müller added a comment -

        The question is if we can change this behaviour without breaking applications that rely on the current one. Because changing the code to throw an error breaks applications that insist on returning 0 rows for an empty array. I don't know what rules apply here concerning BC. What do you think Marco Pivetta?

        Show
        Steve Müller added a comment - The question is if we can change this behaviour without breaking applications that rely on the current one. Because changing the code to throw an error breaks applications that insist on returning 0 rows for an empty array. I don't know what rules apply here concerning BC. What do you think Marco Pivetta ?
        Hide
        Marco Pivetta added a comment -

        Steve Müller existing apps should do following anyway:

        if ($productIds) {
            $qb->andWhere('p.id IN (:productIds)')->setParameter('productIds', $productIds);
        }

        If they are not, that's most likely a bug in their codebase.

        Show
        Marco Pivetta added a comment - Steve Müller existing apps should do following anyway: if ($productIds) { $qb->andWhere('p.id IN (:productIds)')->setParameter('productIds', $productIds); } If they are not, that's most likely a bug in their codebase.
        Hide
        Steve Müller added a comment -

        Agree. Was just wondering about what we can expect and what we can't expect.

        Show
        Steve Müller added a comment - Agree. Was just wondering about what we can expect and what we can't expect.
        Hide
        Guilherme Blanco added a comment -

        Steve Müller feel free to patch DBAL.
        It's a bug, not a supported feature. If we have 2 tickets:
        1- If I pass an empty array, I get nullable rows and I can't fix this
        2- If I pass an empty array, it used to return nullable rows, now it returns nothing
        Which one would you fix?

        Show
        Guilherme Blanco added a comment - Steve Müller feel free to patch DBAL. It's a bug, not a supported feature. If we have 2 tickets: 1- If I pass an empty array, I get nullable rows and I can't fix this 2- If I pass an empty array, it used to return nullable rows, now it returns nothing Which one would you fix?
        Hide
        Tim Stamp added a comment -

        Guilherme Blanco shouldn't both these use cases throw an exception, as both cases in MySQL would return an error?

        Show
        Tim Stamp added a comment - Guilherme Blanco shouldn't both these use cases throw an exception, as both cases in MySQL would return an error?

          People

          • Assignee:
            Marco Pivetta
            Reporter:
            Tim Stamp
          • Votes:
            0 Vote for this issue
            Watchers:
            4 Start watching this issue

            Dates

            • Created:
              Updated: