Database Error: Non-grouping field 'id_pm' is used in HAVING clause

[tomreyn]

SMF 2.0.4, Sources/PersonalMessage.php, line 2667+ says:

Code Select Expand

        // If sender and recipients all have deleted their message, it can be removed.
        $request = $smcFunc['db_query']('', '
                SELECT pm.id_pm AS sender, pmr.id_pm
                FROM {db_prefix}personal_messages AS pm
                        LEFT JOIN {db_prefix}pm_recipients AS pmr ON (pmr.id_pm = pm.id_pm AND pmr.deleted = {int:not_deleted})
                WHERE pm.deleted_by_sender = {int:is_deleted}
                        ' . str_replace('id_pm', 'pm.id_pm', $where) . '
                GROUP BY sender, pmr.id_pm
                HAVING pmr.id_pm IS null',
                array(
                        'not_deleted' => 0,
                        'is_deleted' => 1,
                        'pm_list' => $personal_messages !== null ? array_unique($personal_messages) : array(),
                )
        );


Using InnoDB tables (I'm not sure that's related) this results in an (admin panel) error message when deleting a user:

Code Select Expand

/index.php?action=profile;area=deleteaccount;save=
Database Error: Non-grouping field 'id_pm' is used in HAVING clause


A post on StackOverflow suggests appending (with logical AND) to the WHERE portion of the SQL statement instead of using HAVING.

Other locations using HAVING statements in 2.0.4:

Code Select Expand

# rgrep -n HAVING .
./Sources/Display.php:803: HAVING (approved = {int:is_approved}' . ($user_info['is_guest'] ? '' : ' OR id_member = {int:current_member}') . ')') . '
./Sources/ManageSearch.php:681: HAVING COUNT(id_word) > {int:minimum_messages}',
./Sources/Profile-View.php:1059: HAVING mem.id_member != {int:current_member}',
./Sources/ManageMaintenance.php:1028: HAVING CASE WHEN COUNT(ma.id_msg) >= 1 THEN COUNT(ma.id_msg) - 1 ELSE 0 END != MAX(t.num_replies)',
./Sources/ManageMaintenance.php:1056: HAVING COUNT(mu.id_msg) != MAX(t.unapproved_posts)',
./Sources/ManageMaintenance.php:1312: HAVING COUNT(pmr.id_pm) != MAX(mem.instant_messages)',
./Sources/ManageMaintenance.php:1327: HAVING COUNT(pmr.id_pm) != MAX(mem.unread_messages)',
./Sources/RepairBoards.php:322: HAVING COUNT(m.id_msg) = 0',
./Sources/RepairBoards.php:571: HAVING unapproved_posts != COUNT(mu.id_msg)
./Sources/PersonalMessage.php:2675: HAVING pmr.id_pm IS null',
./Sources/Subs-Db-postgresql.php:253: '~GROUP BY id_msg\s+HAVING~' => 'AND',


Thanks for looking into it!

[Arantor]

It's server configuration related, you have ONLY_FULL_GROUP_BY enabled, which is not a standard operating mode for MySQL (at least, given by the number of people who haven't encountered this, as it isn't related to InnoDB)

The issue is that fields not mentioned in the GROUP BY are used in the HAVING (this is primarily an issue related to other systems, e.g. Oracle, where you can't use criteria in WHERE if the column isn't referenced in the SELECT), it's effectively a strict mode.

It will need to be fixed but I wouldn't expect it to get fixed in 2.0.