Doctrine 2 - ORM
  1. Doctrine 2 - ORM
  2. DDC-3065

Generated 'IN' clause doesn't handle 'null' values (needs to add 'IS NULL' check)

    Details

    • Type: Bug Bug
    • Status: Resolved
    • Priority: Critical Critical
    • Resolution: Fixed
    • Affects Version/s: 2.4.2
    • Fix Version/s: 2.5
    • Component/s: ORM
    • Security Level: All
    • Labels:

      Description

      BasicEntityPersister::getSelectSQL($criteria) first argument can take an array.
      However, if that array contains an 'or' structure like so:

      array(
        'mycol'=>array(
          'couldbethis','orthis',null
        )
      );

      it is converted into:

      mycol IN (?)

      With the final query looking like:

      WHERE mycol IN ('couldbethis','orthis',null)

      The problem is, mysql will never be able to match the null.

      Possible change to getSelectConditionStatementSQL method:

              if (is_array($value)) {
                  $in = sprintf('%s IN (%s)' , $condition, $placeholder);
                  $nullKey = array_search(null, $value, true);
      
                  if ($nullKey) {
                      return sprintf('(%s OR %s IS NULL)' , $in, $condition);
                  } else {
                      return $in;
                  }
              }

      resulting in a final query like:

      WHERE (mycol IN ('couldbethis','orthis',null) OR mycol IS NULL)

        Issue Links

          Activity

          Hide
          Marco Pivetta added a comment -

          Please see https://github.com/doctrine/doctrine2/pull/998 - I applied your suggested fix and tested it carefully

          Show
          Marco Pivetta added a comment - Please see https://github.com/doctrine/doctrine2/pull/998 - I applied your suggested fix and tested it carefully
          Hide
          Doctrine Bot added a comment -

          A related Github Pull-Request [GH-998] was closed:
          https://github.com/doctrine/doctrine2/pull/998

          Show
          Doctrine Bot added a comment - A related Github Pull-Request [GH-998] was closed: https://github.com/doctrine/doctrine2/pull/998
          Show
          Guilherme Blanco added a comment - As of https://github.com/doctrine/doctrine2/commit/4185a9ce4b205e683097842916c8a8929659c6d1 this issue is fixed.

            People

            • Assignee:
              Guilherme Blanco
              Reporter:
              Sam Adams
            • Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: