In MySQL, the @@sql_mode parameter should generally use ONLY_FULL_GROUP_BY. If it doesn’t include it and you don’t have the ability to change the database parameters, you can use a MySQL PSM (Persistent Stored Module), like:
Create the set_full_group_by procedure:
— Drop procedure conditionally on whether it exists already.
DROP PROCEDURE IF EXISTS set_full_group_by;
— Reset delimter to allow semicolons to terminate statements.
DELIMITER $$
— Create a procedure to verify and set connection parameter.
CREATE PROCEDURE set_full_group_by()
LANGUAGE SQL
NOT DETERMINISTIC
SQL SECURITY DEFINER
COMMENT ‘Set connection parameter when not set.’
BEGIN
/* Check whether full group by is set in the connection and
if unset, set it in the scope of the connection. */
IF NOT EXISTS
(SELECT NULL
WHERE REGEXP_LIKE(@@SQL_MODE,’ONLY_FULL_GROUP_BY’))
THEN
SET SQL_MODE=(SELECT CONCAT(@@sql_mode,’,ONLY_FULL_GROUP_BY’));
END IF;
END;
$$
— Reset the default delimiter.
DELIMITER ;
Run the following SQL command before you attempt the exercises in the same session scope:
CALL set_full_group_by();
As always, I hope this helps those looking for a solution. Naturally, you can simply use the SET command on line #21 above.
