Moodle
  1. Moodle
  2. MDL-21264

Problem with sql_isnotempty on specific linux version

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Minor 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
    • Rank:
      26519

      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.

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

        Activity

        Hide
        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
        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
        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
        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
        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
        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: