MySQL Bugs: #87165: MySql ignoes stored procedure sql

Bug #87165	MySql ignoes stored procedure sql_mode
Submitted:	21 Jul 2017 17:07	Modified:	24 Jul 2017 15:17
Reporter:	I C	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	5.6.34,5.6.35,5.7.17	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
 When calling a stored procedure, which reports sql_mode as 'NO_BACKSLASH_ESCAPES' using SHOW CREATE PROCEDURE, backslashes are still used as an escape character.

documentation clearly states
"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."

Tried on various versions, various OSs (including AWS RDS, local windows, local Ubuntu).

How to repeat:
Construct a table with a varchar field.
Ensure session sql_mode='NO_BACKSLASH_ESCAPES'
Create procedure to insert string into varchar field.
Example string 'This is total \bs'
Verify sql_mode for procedure using SHOW CREATE PROCEDURE

Change session sql_mode to anything else.
Verify session sql_mode has changed
Call procedure.
Inspect row - this will show 'This is totals'

change session sql_mode to 'NO_BACKSLASH_ESCAPES'
Call procedure.
Inspect row, this will now show as 'This is total \bs'
Verify sql_mode has not changed for procedure using SHOW CREATE PROCEDURE

Thank you for the bug report. Please provide the complete test case printing here the the whole sql statement using the mysql client, the real result and expected one. Thanks.

sql CLI showing outcome

Attachment: output.txt (text/plain), 2.19 KiB.

I've attached a demonstration.
 As you will see, a string is processed according to the session sql_mode of the client when passed in as a parameter, but processed using the procedure's sql_mode for string literals.
 The expected outcome is for the procedure to produce the same result regardless of sql_mode on the client side.

The only bug here is that parameter does not honour sql_mode.

Verified.

This is how I repeated the test case, with some little changes:

ysql> create table texttable (text1 varchar(100), text2 varchar(100));
Query OK, 0 rows affected (0.03 sec)

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

mysql> delimiter $$
mysql> create procedure testboth(IN TextIn varchar(100))
    -> BEGIN
    -> SET @@SESSION.sql_mode='NO_BACKSLASH_ESCAPES';
    -> INSERT INTO texttable (text1, text2) values ("Literal with \newline", TextIn);
    -> END$$
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;

mysql> call testboth("Parameter with \newline");
 Query OK, 1 row affected (0.00 sec)

mysql> select * from texttable;
+-----------------------+-------------------------+
| text1                 | text2                   |
+-----------------------+-------------------------+
| Literal with \newline | Parameter with \newline |
+-----------------------+-------------------------+
1 row in set (0.00 sec)

mysql> drop procedure testboth;
Query OK, 0 rows affected (0.00 sec)

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

mysql> delimiter $$

mysql> create procedure testboth(IN TextIn varchar(100))
    -> BEGIN
    -> SET @@SESSION.sql_mode='';
    -> INSERT INTO texttable (text1, text2) values ("Literal with \newline", TextIn);
    -> END$$
Query OK, 0 rows affected (0.01 sec

mysql> delimiter ;

mysql> delete from texttable;

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

mysql> call testboth("Paarameter with \newline");
Query OK, 1 row affected (0.00 sec)

mysql> select * from texttable;
+----------------------+-------------------------+
| text1                | text2                   |
+----------------------+-------------------------+
| Literal with
ewline | Paarameter with
ewline |
+----------------------+-------------------------+
1 row in set (0.00 sec)

Hence, it is only the parameter that ignores this sql_mode.