Error reading from Database PostgreSQL

[Avupius]

There is an error read from database, the generated SQL in /mod/grouptool/classes/output/sortlist.php line 141 is syntactically invalid for PostgreSQL 15 because the parameter placeholder is immediately followed by the keyword LEFT. This produces $1LEFT, which PostgreSQL treats as an invalid parameter reference and reports the error.

The workaround is editing the source code and adding space between $1 and LEFT.

Grouptool version: 4.5.2

Debug-Info: ERROR: trailing junk after parameter at or near "$1LEFT"
LINE 8: ... ON agrp.groupid = grp.id AND agrp.grouptoolid = $1LEFT JOI...
^

SELECT MAX(grp.id) AS groupid, MAX(agrp.id) AS id,
MAX(agrp.grouptoolid) AS grouptoolid, MAX(grp.name) AS name,
MAX(agrp.grpsize) AS size, MAX(agrp.sort_order) AS sort_order,
MAX(agrp.active) AS status
FROM mdl_groups grp
LEFT JOIN mdl_grouptool_agrps agrp
ON agrp.groupid = grp.id AND agrp.grouptoolid = $1LEFT JOIN mdl_groupings_groups ON mdl_groupings_groups.groupid = grp.id
WHERE grp.id IN ($2,$3) AND mdl_groupings_groups.groupingid = 2330
GROUP BY grp.id
ORDER BY status DESC, sort_order ASC, name ASC
[array (
0 => '3406',
1 => '46450',
2 => '46451',
)]
Error code: dmlreadexception

line 497 of /lib/dml/moodle_database.php: dml_read_exception thrown
line 293 of /lib/dml/moodle_read_slave_trait.php: call to moodle_database->query_end()
line 358 of /lib/dml/pgsql_native_moodle_database.php: call to pgsql_native_moodle_database->read_slave_query_end()
line 1044 of /lib/dml/pgsql_native_moodle_database.php: call to pgsql_native_moodle_database->query_end()
line 134 of /mod/grouptool/classes/output/sortlist.php: call to pgsql_native_moodle_database->get_records_sql()
line 73 of /mod/grouptool/classes/output/sortlist.php: call to mod_grouptool\output\sortlist->loadgroups()
line 1456 of /mod/grouptool/locallib.php: call to mod_grouptool\output\sortlist->__construct()
line 156 of /mod/grouptool/administration.php: call to mod_grouptool->view_administration()

[caiadogithub]

Same error here.

Using PostgreSQL 16

PS: "The workaround is editing the source code and adding space between $1 and LEFT."

Can't find the "$1LEFT" on the source code

[Avupius]

$1LEFT is not in the source, it only appears at runtime: the ? placeholder is rewritten to $1 by the pgsql driver, and $grouping is concatenated right after it without a space, resulting in $1LEFT JOIN ....

$grouping is set in line 124 and is only non-empty when a grouping filter is active, which is why the error does not always occur.

Fix in classes/output/sortlist.php, line 139:

ON agrp.groupid = grp.id AND agrp.grouptoolid = ?" . $grouping . "

change to:

ON agrp.groupid = grp.id AND agrp.grouptoolid = ? " . $grouping . "

moodle-mod_grouptool/classes/output/sortlist.php

Line 139 in fe728fa

ON agrp.groupid = grp.id AND agrp.grouptoolid = ?" . $grouping . "

[caiadogithub]

$1LEFT is not in the source, it only appears at runtime: the ? placeholder is rewritten to $1 by the pgsql driver, and $grouping is concatenated right after it without a space, resulting in $1LEFT JOIN ....

$grouping is set in line 124 and is only non-empty when a grouping filter is active, which is why the error does not always occur.

Fix in classes/output/sortlist.php, line 139:

ON agrp.groupid = grp.id AND agrp.grouptoolid = ?" . $grouping . "

change to:

ON agrp.groupid = grp.id AND agrp.grouptoolid = ? " . $grouping . "

moodle-mod_grouptool/classes/output/sortlist.php

Line 139 in fe728fa
ON agrp.groupid = grp.id AND agrp.grouptoolid = ?" . $grouping . "

THANKS!!!

It worked!