MySQL Bugs: #94184: Stored procedures not using latest ONLY_FULL_GROUP

Bug #94184	Stored procedures not using latest ONLY_FULL_GROUP_BY value
Submitted:	3 Feb 2019 20:47	Modified:	5 Feb 2019 9:29
Reporter:	Lorenzo Pena	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S2 (Serious)
Version:	8.0.15	OS:	CentOS
Assigned to:		CPU Architecture:	Any
Tags:	error code 1055, ONLY_FULL_GROUP_BY, stored procedure

Description:
It seems like the stored procedures are using the sql_mode value at the creation moment, not the current one. If you were to disable ONLY_FULL_GROUP_BY, and try to execute a procedure that was created while ONLY_FULL_GROUP_BY was enabled, it will throw an Error Code 1055.

How to repeat:
1) Set the sql_mode to ONLY_FULL_GROUP_BY and try to execute a no-valid query.
SET SESSION sql_mode = 'ONLY_FULL_GROUP_BY';
SELECT @@sql_mode; #ONLY_FULL_GROUP_BY
SELECT name, address, MAX(age) FROM test GROUP BY name; #Error Code: 1055.

2) Create a procedure with the same query and try to execute it.
DELIMITER $$
CREATE PROCEDURE `test_procedure` ()
BEGIN
	SELECT name, address, MAX(age) FROM test GROUP BY name;
END$$
DELIMITER ;
CALL test_procedure();  #Error Code: 1055

3) Disable ONLY_FULL_GROUP_BY and test again.
SET SESSION sql_mode = '';
SELECT @@sql_mode; #empty
CALL test_procedure();  #Error Code: 1055. It should work this time
SELECT name, address, MAX(age) FROM test GROUP BY name; #Now returns fine, as it should.

4) Recreate the procedure and try again.
DROP procedure IF EXISTS `test_procedure`;
DELIMITER $$
CREATE PROCEDURE `test_procedure` ()
BEGIN
	SELECT name, address, MAX(age) FROM test GROUP BY name;
END$$
DELIMITER ;
CALL test_procedure();  #Returns fine

Suggested fix:
When calling a procedure, MySQL should be taking into consideration the current sql_mode value.

Hello Lorenzo,

Thank you for the report.
Imho this is not a bug but known and documented behavior. Quoting from our manual - "MySQL stores the sql_mode system variable setting in effect when a routine is created or altered, and always executes the routine with this setting in force, regardless of the current server SQL mode when the routine begins executing." please refer https://dev.mysql.com/doc/refman/8.0/en/create-procedure.html

-- 
mysql> use test;
Database changed
mysql> create table test(id int not null auto_increment primary key, name varchar(100), address varchar(250), age int not null);
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql> SET SESSION sql_mode = 'ONLY_FULL_GROUP_BY';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @@sql_mode; #ONLY_FULL_GROUP_BY
+--------------------+
| @@sql_mode         |
+--------------------+
| ONLY_FULL_GROUP_BY |
+--------------------+
1 row in set (0.00 sec)

mysql> SELECT name, address, MAX(age) FROM test GROUP BY name; #Error Code: 1055.
ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'test.test.address' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
mysql>
mysql> DELIMITER $$
mysql> CREATE PROCEDURE `test_procedure` ()
    -> BEGIN
    -> SELECT name, address, MAX(age) FROM test GROUP BY name;
    -> END$$
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;
mysql> CALL test_procedure();  #Error Code: 1055
ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'test.test.address' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
mysql>

mysql> show create procedure test.test_procedure\G
*************************** 1. row ***************************
           Procedure: test_procedure
            sql_mode: ONLY_FULL_GROUP_BY
    Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `test_procedure`()
BEGIN
SELECT name, address, MAX(age) FROM test GROUP BY name;
END
character_set_client: utf8mb4
collation_connection: utf8mb4_0900_ai_ci
  Database Collation: utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

mysql> select ROUTINE_NAME, SQL_MODE from information_schema.ROUTINES where ROUTINE_SCHEMA='test'\G
*************************** 1. row ***************************
ROUTINE_NAME: test_procedure
    SQL_MODE: ONLY_FULL_GROUP_BY
1 row in set (0.00 sec)

mysql> SET SESSION sql_mode = '';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @@sql_mode; #empty
+------------+
| @@sql_mode |
+------------+
|            |
+------------+
1 row in set (0.00 sec)

mysql> CALL test_procedure();  #Error Code: 1055. It should work this time
ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'test.test.address' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
mysql> SELECT name, address, MAX(age) FROM test GROUP BY name; #Now returns fine, as it should.
Empty set (0.00 sec)

mysql> DROP procedure IF EXISTS `test_procedure`;
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER $$
mysql> CREATE PROCEDURE `test_procedure` ()
    -> BEGIN
    -> SELECT name, address, MAX(age) FROM test GROUP BY name;
    -> END$$
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;
mysql> CALL test_procedure();  #Returns fine
Empty set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> show create procedure test.test_procedure\G
*************************** 1. row ***************************
           Procedure: test_procedure
            sql_mode:
    Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `test_procedure`()
BEGIN
SELECT name, address, MAX(age) FROM test GROUP BY name;
END
character_set_client: utf8mb4
collation_connection: utf8mb4_0900_ai_ci
  Database Collation: utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

mysql>  select ROUTINE_NAME, SQL_MODE from information_schema.ROUTINES where ROUTINE_SCHEMA='test'\G
*************************** 1. row ***************************
ROUTINE_NAME: test_procedure
    SQL_MODE:
1 row in set (0.00 sec)

Thanks,
Umesh