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

Oracle cannot cope withan IN operator with more than 1000 conditions in it

    XMLWordPrintable

Details

    • Bug
    • Status: Closed
    • Critical
    • Resolution: Fixed
    • 2.0.1, 2.3.6, 2.4.3, 2.5
    • 2.3.7, 2.4.4
    • Database SQL/XMLDB
    • CentOS 5.5, Oracle 10g, PHP 5.3.5 with oci8 library
    • Oracle
    • MOODLE_20_STABLE, MOODLE_23_STABLE, MOODLE_24_STABLE, MOODLE_25_STABLE
    • MOODLE_23_STABLE, MOODLE_24_STABLE
    • Hide

      Fix the code to avoid using such big IN() clauses.

      Show
      Fix the code to avoid using such big IN() clauses.
    • Easy
    • Hide

      In all the branches where this patch is integrated....

      1) Run lib/dml/tests/dml_test.php under all DBs. Getting them passing (see note below) means we can now use bigger (up to 10000) IN() clauses in a cross-db way.

      Note that only master in able to pass the DML tests 100% under all databases, so the stables will show some failures. In any case, the tests verifying this issue are:

      • test_get_in_or_equal()
      • test_get_records_sql_complicated()

      No error related to them should happen at all.

      Show
      In all the branches where this patch is integrated.... 1) Run lib/dml/tests/dml_test.php under all DBs. Getting them passing (see note below) means we can now use bigger (up to 10000) IN() clauses in a cross-db way. Note that only master in able to pass the DML tests 100% under all databases, so the stables will show some failures. In any case, the tests verifying this issue are: test_get_in_or_equal() test_get_records_sql_complicated() No error related to them should happen at all.

    Description

      Problem with query with In operator with more than 1000 condition in it (error ORA-01795: maximum number of expressions in a list is 1000)

      Proposed solution:
      an hack in the lib/dml/oci_nativa_moodle_database.php
      Due to the fact we are working at the library level, it's not possible to use a subquery instead of the "long" list of parameters. We adopted the other suggested approach: to parse the sql code for the IN operator with more than 1000 conditions and to rewrite it as a series of IN operators connected through the OR condition (suggestion from http://forums.oracle.com/forums/thread.jspa?threadID=233143)

      The hack is the following:
      <

      {moodle}/lib/dml/oci_native_moodle_database.php> : 981 //added some lines to use the defined method "ORA01795_hack" on the SQL query
      //LkM79 for ISP, 2010-04-03
      //var_dump(method_exists($this,'ORA01795_hack'));
      $sql=$this->ORA01795_hack($sql);

      <{moodle}

      /lib/dml/oci_native_moodle_database.php> : 1040 //added some lines to use the defined method "ORA01795_hack" on the SQL query
      //LkM79 for ISP, 2010-04-03
      //var_dump(method_exists($this,'ORA01795_hack'));
      $sql=$this->ORA01795_hack($sql);

      <

      {moodle}

      /lib/dml/oci_native_moodle_database.php> : 981 //added some lines (a function) to split every IN clause with more than 1000 conditions in a OR combination of many shorter IN lists
      /**

      • Driver specific hack for introducing a sort of support for IN list with more than 1000 condition
      • split the IN list with more than 1000 co0nditions in many lists, connected through an OR operator
      • @return string
      • LkM79 for ISP, 2010-04-03
        */
        public function ORA01795_hack($sql){
        //var_dump($sql);
        //var_dump(stripos($sql,"IN (?,"));
        //var_dump(explode("?,",$sql));

      $start=stripos($sql," IN (?,");
      if($start>1) {
      $end=stripos(substr($sql,$start),"?)")+2;
      //var_dump($end);
      $in_list=substr($sql, $start,$end);
      //var_dump($in_list);
      $n=explode("?,",$in_list);
      //var_dump($n);
      $slices=(int)((count($n))/1000)+1;
      //var_dump($slices);
      $step=(int)((count($n))/$slices);
      //var_dump($step);
      if($slices>1){
      $sql_i=substr($sql,0,$start);
      $field=strrchr($sql_i, " ");
      $sql_i=substr($sql_i,0,-strlen($field));
      $sql_e=substr($sql,$start+$end);
      $sql_in=' (';
      for($i=0;$i<$slices;$i++){
      $sql_in.=" $field IN (";
      if($i<($slices -1)){
      for($t=0;$t<($step-1);$t++)

      { $sql_in.='?,'; }
      $sql_in.='?) OR ';
      }else{
      for($t=0;$t<((count($n)-(($slices -1)*$step))-1);$t++){ $sql_in.='?,'; }

      $sql_in.='?))';
      }
      }
      $sql=$sql_i.$sql_in.$sql_e;
      }
      }
      //var_dump($sql);
      return $sql;
      }

      }

      Attached, the modified version of the oci_native_moodle_database.php file

      Attachments

        Issue Links

          Activity

            People

              stronk7 Eloy Lafuente (stronk7)
              mazzolal Luca Mazzola
              Petr Skoda Petr Skoda
              Dan Poltawski Dan Poltawski
              Dan Poltawski Dan Poltawski
              David Woloszyn, Huong Nguyen, Jake Dallimore, Meirza, Michael Hawkins, Raquel Ortega, Safat Shahin, Stevani Andolo
              Votes:
              2 Vote for this issue
              Watchers:
              8 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:
                13/May/13