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

Problem with sql_isnotempty on specific linux version

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Minor
    • Resolution: Fixed
    • Affects Version/s: 1.9.7
    • Fix Version/s: 1.9.8
    • Component/s: Database SQL/XMLDB
    • Labels:
      None
    • Environment:
      CentOS (CentOS 4.5; Linux Kernel 2.6.9-55.EL), with NGINX (0.8.30) with PHP (5.2.9) through FastCGI (spawn-fcgi-1.6.3) and mySQL (4.1.20); it uses Moodle 1.9.7 build 20091126
    • Database:
      MySQL
    • Affected Branches:
      MOODLE_19_STABLE
    • Fixed Branches:
      MOODLE_19_STABLE

      Description

      From: http://moodle.org/mod/forum/discuss.php?d=138680 by Ohad Kravchick - Wednesday, 30 December 2009, 09:10 PM

      Ok. I finally was able to find the problem.

      I could not get permissions to access the server. However, I had created the same environment on my own pc; this is important as the problem seems OS/DB related. The server is CentOS (CentOS 4.5; Linux Kernel 2.6.9-55.EL), with NGINX (0.8.30) with PHP (5.2.9) through FastCGI (spawn-fcgi-1.6.3) and mySQL (4.1.20); it uses Moodle 1.9.7 build 20091126.

      The problem starts in /mod/quiz/locallib.php at quiz_has_feedback (line 345). It creates a query like: select * from quiz_feedback WHERE quizid=1 AND NOT feedbacktext='';

      In /lib/dmllib.php In sql_isnotempty (line 1932), Moodle try to compare using "return ' ( NOT ' . sql_isempty($tablename, $fieldname, $nullablefield, $textfield) . ') ';"

      The query returns no rows no matter what my table contains.

      See:

      mysql> select * from quiz_feedback WHERE quizid=1;
      --------------------------------------

      id quizid feedbacktext mingrade maxgrade

      --------------------------------------

      1 1 Good 5 11
      2 1 Bad 0 5

      --------------------------------------
      2 rows in set (0.00 sec)

      mysql> select * from quiz_feedback WHERE quizid=1 AND NOT feedbacktext='';
      Empty set (0.00 sec)

      mysql> select * from quiz_feedback WHERE quizid=1 AND feedbacktext='Good';
      --------------------------------------

      id quizid feedbacktext mingrade maxgrade

      --------------------------------------

      1 1 Good 5 11

      --------------------------------------
      1 row in set (0.00 sec)

      mysql> select * from quiz_feedback WHERE quizid=1 AND NOT feedbacktext='Good';
      Empty set (0.00 sec)

      It appears that NOT doesn't interact well with = comparizon for string.

      Instead, this should be used:

      mysql> select * from quiz_feedback WHERE quizid=1 AND feedbacktext<>'';
      --------------------------------------

      id quizid feedbacktext mingrade maxgrade

      --------------------------------------

      1 1 Good 5 11
      2 1 Bad 0 5

      --------------------------------------
      2 rows in set (0.00 sec)

      or strcmp, or "not like", etc. Perhaps only for mysql.

      This looked like the sort of thing you should know about Eloy.

        Gliffy Diagrams

        1. MDL-21264_19.patch.txt
          0.6 kB
          Tim Hunt
        2. MDL-21264_HEAD.patch.txt
          2 kB
          Tim Hunt

          Activity

          Hide
          myok12 Ohad Kravchick added a comment -

          Just a sidenote:
          The feedbacktext doesn't allow nulls:

          mysql> describe quiz_feedback;
          -------------------------------------------------------------+

          Field Type Null Key Default Extra

          -------------------------------------------------------------+

          id bigint(10) unsigned   PRI NULL auto_increment
          quizid bigint(10) unsigned   MUL 0  
          feedbacktext text        
          mingrade double     0  
          maxgrade double     0  

          -------------------------------------------------------------+
          5 rows in set (0.00 sec)

          Show
          myok12 Ohad Kravchick added a comment - Just a sidenote: The feedbacktext doesn't allow nulls: mysql> describe quiz_feedback; ------------- ------------------- ---- --- ------- ---------------+ Field Type Null Key Default Extra ------------- ------------------- ---- --- ------- ---------------+ id bigint(10) unsigned   PRI NULL auto_increment quizid bigint(10) unsigned   MUL 0   feedbacktext text         mingrade double     0   maxgrade double     0   ------------- ------------------- ---- --- ------- ---------------+ 5 rows in set (0.00 sec)
          Hide
          timhunt Tim Hunt added a comment -

          If you look at the forum thread, you will see we tracked this down to an operator precidence issue.

          I needs to be (NOT (x = '')) instead of (NOT x = '').

          I think the attached patches fix this. Eloy please review, and then commit, or ask me to commit.

          Thanks.

          Show
          timhunt Tim Hunt added a comment - If you look at the forum thread, you will see we tracked this down to an operator precidence issue. I needs to be (NOT (x = '')) instead of (NOT x = ''). I think the attached patches fix this. Eloy please review, and then commit, or ask me to commit. Thanks.
          Hide
          stronk7 Eloy Lafuente (stronk7) added a comment -

          Done. Thanks for the report and fix.

          I've committed your suggested patches both to 19_STABLE and HEAD. And also have added a bunch of missing tests to HEAD. They are passing perfectly under the BIG-FOUR DBs.

          Ciao

          Side note: Be careful when using the sql_isempty() and sql_isnotempty() functions against nullable columns as far as NULL doesn't match any of them. (NULL isn't equality comparable).

          Show
          stronk7 Eloy Lafuente (stronk7) added a comment - Done. Thanks for the report and fix. I've committed your suggested patches both to 19_STABLE and HEAD. And also have added a bunch of missing tests to HEAD. They are passing perfectly under the BIG-FOUR DBs. Ciao Side note: Be careful when using the sql_isempty() and sql_isnotempty() functions against nullable columns as far as NULL doesn't match any of them. (NULL isn't equality comparable).

            People

            • Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:
                Fix Release Date:
                25/Mar/10