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.
Attachments
Activity
- All
- Comments
- History
- Activity
- Source
- Test Sessions
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).
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). 
Just a sidenote:
The feedbacktext doesn't allow nulls:
mysql> describe quiz_feedback;
-------------
------------------------------------------------+-------------
------------------------------------------------+-------------
------------------------------------------------+5 rows in set (0.00 sec)
------------------------------------------------+------------------------------------------------+------------------------------------------------+ 5 rows in set (0.00 sec)