Error when chatroom created on MySQL 4.0

I've installed the chatroom. The same thing happened. The error is:

user warning: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT COUNT(*) FROM ffworld_dp_chatroom AS cr INNE query: SELECT ( SELECT COUNT(*) FROM ffworld_dp_chatroom AS cr INNER JOIN ffworld_dp_chatroom_chat AS cc ON cc.crid = cr.crid INNER JOIN ffworld_dp_chatroom_msg AS cm ON cm.ccid = cc.ccid WHERE cr.crid = 1 AND cm.recipient = '' ) + ( SELECT COUNT(*) FROM ffworld_dp_chatroom AS cr INNER JOIN ffworld_dp_chatroom_chat AS cc ON cc.crid = cr.crid INNER JOIN ffworld_dp_chatroom_msg_archive AS cma ON cma.ccid = cc.ccid WHERE cr.crid = 1 AND cma.recipient = '' ) AS msg_count, MAX(cm.cmid) AS last_cmid, cc.ccid, cc.chatname, cc.when_archived FROM ffworld_dp_chatroom AS cr INNER JOIN ffworld_dp_chatroom_chat AS cc ON cc.crid = cr.crid LEFT JOIN ffworld_dp_chatroom_msg AS cm in /home/yookoala/public_html/ffworld/includes/database.mysql.inc on line 172.

I figured out that the original matter caused by this query:

    SELECT (
      SELECT COUNT(*)
      FROM ffworld_dp_chatroom AS cr
      INNER JOIN ffworld_dp_chatroom_chat AS cc
      ON cc.crid = cr.crid
      INNER JOIN ffworld_dp_chatroom_msg AS cm
      ON cm.ccid = cc.ccid
      WHERE cr.crid = 1
      AND cm.recipient = ''
    ) + (
      SELECT COUNT(*)
      FROM ffworld_dp_chatroom AS cr
      INNER JOIN ffworld_dp_chatroom_chat AS cc
      ON cc.crid = cr.crid
      INNER JOIN ffworld_dp_chatroom_msg_archive AS cma
      ON cma.ccid = cc.ccid
      WHERE cr.crid = 1
      AND cma.recipient = ''
    ) AS msg_count, MAX(cm.cmid) AS last_cmid, cc.ccid, cc.chatname, cc.when_archived
    FROM ffworld_dp_chatroom AS cr
    INNER JOIN ffworld_dp_chatroom_chat AS cc
    ON cc.crid = cr.crid
    LEFT JOIN ffworld_dp_chatroom_msg AS cm
    ON cm.ccid = cc.ccid
    AND cm.recipient = ''
    WHERE cr.crid = 1
    GROUP BY cc.ccid, cc.chatname, cc.when_archived

I then tried to run the query under phpmyadmin environment. It resulted in the similar error:

Error

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

SQL query:

SELECT (

SELECT COUNT( * )
FROM ffworld_dp_chatroom AS cr
INNER JOIN ffworld_dp_chatroom_chat AS cc ON cc.crid = cr.crid
INNER JOIN ffworld_dp_chatroom_msg AS cm ON cm.ccid = cc.ccid
WHERE cr.crid =1
AND cm.recipient = ''
) + (
SELECT COUNT( * )
FROM ffworld_dp_chatroom AS cr
INNER JOIN ffworld_dp_chatroom_chat AS cc ON cc.crid = cr.crid
INNER JOIN ffworld_dp_chatroom_msg_archive AS cma ON cma.ccid = cc.ccid
WHERE cr.crid =1
AND cma.recipient = '' ) AS msg_count, MAX( cm.cmid ) AS last_cmid, cc.ccid, cc.chatname, cc.when_archived
FROM ffworld_dp_chatroom AS cr
INNER JOIN ffworld_dp_chatroom_chat AS cc ON cc.crid = cr.crid
LEFT JOIN ffworld_dp_chatroom_msg AS cm ON cm.ccid = cc.ccid
AND cm.recipient = ''
WHERE cr.crid =1
GROUP BY cc.ccid, cc.chatname, cc.when_archived

MySQL said:

#1064 - You have an error in your SQL syntax.  Check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT COUNT(*)
      FROM ffworld_dp_chatroom AS cr
      IN 

I read from MySQL documentation that subquery is only support on or after MySQL version 4.1. Which means that my database version is too old for subquery used in chatroom to work. Will you please rewrite the query with non-subquery form?

Thanks.

I'm willing to write a patch on it.

I got rid of the first error yesterday, but I found more subquery in the module. When I test the module further, I founded that the chat Ajax script doesn't work at all. I could create a chatroom and create a chat in it, but the chat didn't respond to my input. I kept pressing the "send" button, but nothing shown up in the chat box. I could not do any further test on it then.

Should I provide the "half-way fix" patch here?