Problem with sql query in DB Oracle (1)

[jaume77]

Hello!

At the Universitat Autonoma de Barcelona (UAB) we use the Quikmail block (version 2021030200) in our Moodle institutional environment (3.11 on Oracle Database), and we have detected a problem with the sql query that is responsible for retrieving all messages pending delivery;

File: ./quickmail/classes/repos/queued_repo.php Function: get_all_messages_to_send()

...
$sql = 'SELECT m.*
            FROM {block_quickmail_messages} m
            INNER JOIN {block_quickmail_msg_recips} mr ON m.id = mr.message_id
            WHERE m.is_draft = 0
            AND m.is_sending = 0
            AND m.timedeleted = 0
            AND m.to_send_at <= :now
            AND mr.sent_at = 0
            GROUP BY m.id';
...

In Oracle it is necessary to explicitly indicate the fields, both in the SELECT and in the GROUP BY. Besides, it is not possible to GROUP BY CLOB fields, as is the case of the "subject" and "body" fields of the "block_quickmail_messages" table.

For all this, we saw that this function failed, and the messages that were in the queue were not sent.

We modify it by the following equivalent query:

$sql = 'SELECT me.*
            FROM {block_quickmail_messages} me,
                    (SELECT m.id
                    FROM m_block_quickmail_messages m
                    INNER JOIN m_block_quickmail_msg_recips mr ON m.id = mr.message_id
                    WHERE m.is_draft = 0
                    AND m.is_sending = 0
                    AND m.timedeleted = 0
                    AND m.to_send_at <= :now
                    AND mr.sent_at = 0
                    GROUP BY m.id) t
             WHERE t.id = me.id';

Would it be possible to take this modification into account in future versions?

Cheers

Attach error image with original sql

Backtrace:
* line 277 of /lib/dml/oci_native_moodle_database.php: call to moodle_database->query_end()
* line 1120 of /lib/dml/oci_native_moodle_database.php: call to oci_native_moodle_database->query_end()
* line 239 of /blocks/quickmail/classes/repos/queued_repo.php: call to oci_native_moodle_database->get_recordset_sql()
* line 49 of /blocks/quickmail/classes/tasks/send_all_ready_messages_task.php: call to block_quickmail\repos\queued_repo::get_all_messages_to_send()
* line 253 of /lib/cronlib.php: call to block_quickmail\tasks\send_all_ready_messages_task->execute()
* line 167 of /admin/cli/scheduled_task.php: call to cron_run_inner_scheduled_task()