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

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

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Critical
    • Resolution: Fixed
    • Affects Version/s: 2.0.1, 2.3.6, 2.4.3, 2.5
    • Fix Version/s: 2.3.7, 2.4.4
    • Component/s: Database SQL/XMLDB
    • Labels:
    • Environment:
      CentOS 5.5, Oracle 10g, PHP 5.3.5 with oci8 library
    • Database:
      Oracle
    • Testing Instructions:
      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.
    • Workaround:
      Hide

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

      Show
      Fix the code to avoid using such big IN() clauses.
    • Difficulty:
      Easy
    • Affected Branches:
      MOODLE_20_STABLE, MOODLE_23_STABLE, MOODLE_24_STABLE, MOODLE_25_STABLE
    • Fixed Branches:
      MOODLE_23_STABLE, MOODLE_24_STABLE
    • Pull from Repository:
    • Pull Master Branch:

      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

        Gliffy Diagrams

          Attachments

            Issue Links

              Activity

                People

                • Votes:
                  2 Vote for this issue
                  Watchers:
                  10 Start watching this issue

                  Dates

                  • Created:
                    Updated:
                    Resolved:
                    Fix Release Date:
                    13/May/13