[MDEV-23189] Double backslashes reduced to one in routine definition in information_schema.routines Created: 2020-07-16  Updated: 2020-08-04

Status: Open
Project: MariaDB Server
Component/s: Information Schema, Stored routines
Affects Version/s: 10.5.4
Fix Version/s: 10.5

Type: Bug Priority: Minor
Reporter: Konstantin Schmidt Assignee: Alexander Barkov
Resolution: Unresolved Votes: 0
Labels: None
Environment:

Windows 10 1909
intel i7

Issue Links:
Relates
relates to MDEV-23170 Characterset missing in routine defin... Closed

 Description   

Double backslashes get reduced to one in routine definition in information_schema.routines

1. create routine with literal strings

CREATE 
PROCEDURE testprocedure(OUT `out1` VARCHAR(255))
 
BEGIN
 
    # double backslash in regex literal 
    # becomes single backslash in information_schema.routines ROUTINE_DEFINITION
 
    SET out1 = REGEXP_SUBSTR('abc1234ddef', '\\d+');
 
END

2. Routine returns 1234

3. select definition record in information_schema.ROUTINES

SELECT * FROM information_schema.ROUTINES
 
WHERE routine_name = 'testprocedure'

Column ROUTINE_DEFINITION shows routine definition as 

BEGIN
 
    # double backslash in regex literal 
    # becomes single backslash in information_schema.routines ROUTINE_DEFINITION
 
    SET out1 = REGEXP_SUBSTR('abc1234ddef', '\d+');
 
END

Routine would return 'dd'.

I read in MDEV-6508 that the ROUTINE_DEFINITION "may not always be used to recreate the routine and won't necessarily be valid SQL" but this missing backslash seems to be a bug anyway.

 Comments   
Comment by Elena Stepanova [ 2020-08-04 ]

Same note as in MDEV-23170, about the difference between mysql.proc.body and mysql.proc.body_utf8, applies here.

Generated at Thu Feb 08 09:20:32 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.