Bug #101756 Invalid assigment of space char to variables in stored routines

• Submitted: 25 Nov 2020 15:23
• Modified: 26 Nov 2020 8:52
• Reporter: Aurel Pekarcik
• Status: Not a Bug
• Category: MySQL Server: Stored Routines
• Severity: S3 (Non-critical)
• Version: 5.7.32-log
• OS: Windows (Windows 10 Home)
• CPU Architecture: Any (win64)
• Tags: assignments, space char, stored routines, string function left

[25 Nov 2020 15:23] Aurel Pekarcik

Description:
When I trying to get first char of string by calling function "left", I have got blank string when first char is space. 

How to repeat:
try :

delimiter ||

function TestSpace(S text) returns text
begin
  declare C char;
  set C=left(S,1);
  
  return 
    replace(
      concat('S="',S,'",Left(S,1)="',left(S,1),'",C="',C,'"'),
    ' ','<space>');

end||

delimiter ;

select TestSpace("ABC");

# return correct result : S="ABC",Left(S,1)="A",C="A"

select TestSpace(" ABC");

# return invalid result : S="<space>ABC",Left(S,1)="<space>",C=""
# function Left(S,1) return correct first char of input, (space), 
# but variable C contains empty string !#?$????

Suggested fix:
Variable C after statement  
  
  C = left(S,1)

supposed to contains first char of S regardless first char is space or not.

[26 Nov 2020 8:52] MySQL Verification Team

Imho you should prefix user defined variables with @. Please see https://dev.mysql.com/doc/refman/5.7/en/user-variables.html

 bin/mysql -uroot -S /tmp/mysql_ushastry.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.32 MySQL Community Server (GPL)

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use test
Database changed
mysql> delimiter ||
mysql>
mysql> CREATE function TestSpace(S text) returns text
    -> begin
    ->   declare C char;
    ->   set @C=left(S,1);
    ->
    ->   return
    ->     replace(
    ->       concat('S="',S,'",Left(S,1)="',left(S,1),'",C="',@C,'"'),
    ->     ' ','<space>');
    ->
    -> end||
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> delimiter ;
mysql>
mysql> select TestSpace("ABC");
+-----------------------------+
| TestSpace("ABC")            |
+-----------------------------+
| S="ABC",Left(S,1)="A",C="A" |
+-----------------------------+
1 row in set (0.00 sec)

mysql> select TestSpace(" ABC");
+------------------------------------------------+
| TestSpace(" ABC")                              |
+------------------------------------------------+
| S="<space>ABC",Left(S,1)="<space>",C="<space>" |
+------------------------------------------------+
1 row in set (0.00 sec)