Uploaded image for project: 'Moodle'
  1. Moodle
  2. MDL-35334

Create oracle-specific version of get_in_or_equal() to handle limit of 1000 items

    XMLWordPrintable

    Details

    • Type: Improvement
    • Status: Closed
    • Priority: Minor
    • Resolution: Inactive
    • Affects Version/s: 2.3.2
    • Fix Version/s: None
    • Component/s: Database SQL/XMLDB
    • Labels:
    • Affected Branches:
      MOODLE_23_STABLE

      Description

      Oracle doesn't support more than 1000 items in an IN() clause - any more and the query fails with error ORA-01795.

      The problem is that often the list of items is generated dynamically, so most sites work fine but a big site will suddenly get breaking queries when it gets big enough.

      Back on 1.9 we had a function that we used when building an IN() clause from an array that would check the number of items and the database in use, and split the IN() for Oracle if there are too many items - changing this:

      IN (1,2,3,...,2009,2010)

      to:

      IN (1,2,3,...,999,1000) OR IN (1001,1002,...,1999,2000) OR IN (2001,...,2010)

      In Moodle 2, we use get_in_or_equal(), which could have the same issue - I was wondering if it would make sense to have an Oracle-specific version of get_in_or_equal() which did something similar?

      I'd be happy to write it (based off the code below) if it's something that would be wanted in core.

      Here's the code we were using (ported to 2.x):

      /**
       * Return the proper SQL to compare a field to multiple items
       *
       * By default it uses IN but can be negated (to NOT IN) using the 3rd argument
       *
       * The output from this is safe for Oracle, which has a limit of 1000 items in an
       * IN () call.
       *
       * @param string $field The field to compare against
       * @param array $items Array of items. If text they must already be quoted.
       * @param boolean $negate Return code for NOT IN () instead of IN ()
       *
       * @return array In the form array(sql, params) The SQL needed to compare $field to the items
       *              in $items and associated parameters
       */
      function sql_sequence($field, $items, $type=SQL_PARAMS_QM, $negate = false) {
          global $DB;
       
          if (!is_array($items) || count($items) == 0) {
              return ($negate) ? array('1=1', array()) : array('1=0', array());
          }
       
          $not = $negate ? 'NOT' : '';
          if ($DB->get_dbfamily() != 'oracle' || $count($items) <= 1000) {
              list($sql, $params) = $DB->get_in_or_equal($items, $type, 'param', !$negate);
       
              return array(" $field " . $sql, $params);
          }
       
          $out = array();
          while ($some_items = totara_pop_n($items, 1000)) {
              list($sql, $params) = $DB->get_in_or_equal($items, $type, 'param', !$negate);
              $out[] =" $field " . $sql;
              $outparams = array_merge($outparams, $params);
       
          }
       
          $operator = $negate ? ' AND ' : ' OR ';
          return array('(' . implode($operator, $out) . ')', $outparams);
      }
       
      /**
       * Pop N items off the beginning of $items and return them as an array
       *
       * @param array &$items Array of items (passed by reference)
       * @param integer $number Number of items to remove from the start of $items
       *
       * @return array Array of $number items from $items, or false if $items is empty
       */
      function totara_pop_n(&$items, $number) {
          if (count($items) == 0) {
              // none left, return false
              return false;
          } else if (count($items) < $number) {
              // return all remaining items
              $return = $items;
              $items = array();
              return $return;
          } else {
              // return the first N and shorten $items
              $return = array_slice($items, 0, $number, true);
              $items = array_slice($items, $number, null, true);
              return $return;
          }
      }

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              Unassigned
              Reporter:
              simoncoggins Simon Coggins
              Participants:
              Component watchers:
              Andrew Nicols, Jun Pataleta, Michael Hawkins, Shamim Rezaie, Simey Lameze
              Votes:
              1 Vote for this issue
              Watchers:
              3 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: