MySQL Bugs: #13665: concat with '' produce incorrect results in SP

Bug #13665	concat with '' produce incorrect results in SP
Submitted:	30 Sep 2005 17:14	Modified:	24 Nov 2005 14:26
Reporter:	peter droogmans	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S2 (Serious)
Version:	5.0.13-rc-nt	OS:	Windows (Win XP - SP 2 fully patched)
Assigned to:	Alexander Nozdrin	CPU Architecture:	Any

Description:
I have a cursor that loops all the colums in a table, I have to skip certain columns, this worked in the latest beta (till yesterday), after installing the RC1 it stopped working.

Included is some code to mimic the problem:
If I change on line 39 'ModifiedBy' to 'xModifiedBy' it is working
If I change on line 40 '' to pCOLUMN_NAME it is working

How to repeat:
DELIMITER $$

DROP FUNCTION IF EXISTS `sys_Test`$$
CREATE FUNCTION `sys_Test`(pTable VARCHAR(50)) RETURNS varchar(20000)
BEGIN

DECLARE done INT default 0;
DECLARE first INT default 0;

DECLARE pCOLUMN_NAME VARCHAR(50);
DECLARE pDATA_TYPE VARCHAR(50);
DECLARE pCHARACTER_MAXIMUM_LENGTH int;

DECLARE pOutput varchar(2000);

DECLARE cur1 CURSOR FOR
SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE table_name = pTable;

DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;

  set pOutput = '';

  set done = 0;
  set first = 0;
  OPEN cur1;

  REPEAT

    FETCH cur1 INTO pCOLUMN_NAME, pDATA_TYPE, pCHARACTER_MAXIMUM_LENGTH;

    if not done then

      IF NOT first THEN
        set pOutput = CONCAT(pOutput, 'INOUT p', pCOLUMN_NAME, ' ', pDATA_TYPE);
      ELSEIF pCOLUMN_NAME = 'ModifiedOn' THEN
        set pOutput = CONCAT(pOutput, ', INOUT p', pCOLUMN_NAME, ' ', pDATA_TYPE);
      ELSEIF (pCOLUMN_NAME = 'ModifiedBy') THEN
        set pOutput = CONCAT(pOutput, '');
      ELSE
        set pOutput = CONCAT(pOutput, ', IN p', pCOLUMN_NAME, ' ', pDATA_TYPE);
      END IF;

      set first = 1;

   end if;

  UNTIL done END REPEAT;

  CLOSE cur1;

  set pOutput = CONCAT(pOutput, ')', '\n');
  set pOutput = CONCAT(pOutput, 'DONE', '\n');

  return pOutput;

END$$

DELIMITER ;

Suggested fix:
to be honest, no idea

I do not really understand, what should be changed and why. For me your code just works:

mysql> use test;
Database changed
mysql> DELIMITER $$
mysql>
mysql> DROP FUNCTION IF EXISTS `sys_Test`$$
Query OK, 0 rows affected, 1 warning (0.28 sec)

mysql> CREATE FUNCTION `sys_Test`(pTable VARCHAR(50)) RETURNS varchar(20000)
    -> BEGIN
    ->
    -> DECLARE done INT default 0;
    -> DECLARE first INT default 0;
    ->
    -> DECLARE pCOLUMN_NAME VARCHAR(50);
    -> DECLARE pDATA_TYPE VARCHAR(50);
    -> DECLARE pCHARACTER_MAXIMUM_LENGTH int;
    ->
    -> DECLARE pOutput varchar(2000);
    ->
    -> DECLARE cur1 CURSOR FOR
    -> SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH
    ->   FROM INFORMATION_SCHEMA.COLUMNS
    ->   WHERE table_name = pTable;
    ->
    -> DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
    ->
    ->   set pOutput = '';
    ->
    ->   set done = 0;
    ->   set first = 0;
    ->   OPEN cur1;
    ->
    ->   REPEAT
    ->
    ->     FETCH cur1 INTO pCOLUMN_NAME, pDATA_TYPE, pCHARACTER_MAXIMUM_LENGTH;
    ->
    ->     if not done then
    ->
    ->       IF NOT first THEN
    ->         set pOutput = CONCAT(pOutput, 'INOUT p', pCOLUMN_NAME, ' ',
    -> pDATA_TYPE);
    ->       ELSEIF pCOLUMN_NAME = 'ModifiedOn' THEN
    ->         set pOutput = CONCAT(pOutput, ', INOUT p', pCOLUMN_NAME, ' ',
    -> pDATA_TYPE);
    ->       ELSEIF (pCOLUMN_NAME = 'ModifiedBy') THEN
    ->         set pOutput = CONCAT(pOutput, '');
    ->       ELSE
    ->         set pOutput = CONCAT(pOutput, ', IN p', pCOLUMN_NAME, ' ', pDATA_TYPE);
    ->       END IF;
    ->
    ->       set first = 1;
    ->
    ->    end if;
    ->
    ->   UNTIL done END REPEAT;
    ->
    ->   CLOSE cur1;
    ->
    ->   set pOutput = CONCAT(pOutput, ')', '\n');
    ->   set pOutput = CONCAT(pOutput, 'DONE', '\n');
    ->
    ->   return pOutput;
    ->
    -> END$$
Query OK, 0 rows affected (0.07 sec)

mysql>
mysql> DELIMITER ;
mysql> select sys_Test('test');
+------------------------------+
| sys_Test('test')             |
+------------------------------+
| INOUT pField1 varchar)
DONE
 |
+------------------------------+
1 row in set (0.30 sec)

mysql> desc test;
+--------+--------------+------+-----+---------+-------+
| Field  | Type         | Null | Key | Default | Extra |
+--------+--------------+------+-----+---------+-------+
| Field1 | varchar(255) | NO   | PRI |         |       |
+--------+--------------+------+-----+---------+-------+
1 row in set (0.01 sec)

mysql> select version();
+--------------+
| version()    |
+--------------+
| 5.0.13-rc-nt |
+--------------+
1 row in set (0.01 sec)

What results did you expect? Have you installed a fresh copy of 5.0.13-nt-rc or upgraded?

DROP TABLE IF EXISTS `vinov`.`klant`;
CREATE TABLE  `vinov`.`klant` (
  `KlantId` int(11) NOT NULL auto_increment,
  `TypeKlant` int(11) NOT NULL,
  `RefId` int(11) NOT NULL,
  `Kaartnummer` varchar(100) NOT NULL,
  `TypeKaart` int(11) NOT NULL,
  `Betalingswijze` int(11) NOT NULL,
  `CreatedOn` datetime default NULL,
  `CreatedBy` varchar(50) default NULL,
  `ModifiedOn` datetime default NULL,
  `ModifiedBy` varchar(50) default NULL,
  `DeletedOn` datetime default NULL,
  `DeletedBy` varchar(50) default NULL,
  PRIMARY KEY  (`KlantId`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Output is the return value, I used this to create insert, update, delete and select statements.

The column 'ModifiedBy' has to exist in the table otherwise it does work, see previous comment.

BTW: I upgraded from the latest beta

Thank you for a bug report. The problem in your code is related to the line:

set pOutput = CONCAT(pOutput, '');

If you change it to:

set pOutput = CONCAT(pOutput, ' ');

your code will work as expected. 

The real bug can be repeated using much simpler test case, without any cursor (that is why I changed synopsis):

mysql> delimiter //
mysql> create function f2(pInput varchar(50)) returns varchar(20000)
    -> begin
    ->   declare pOutput varchar(2000);
    ->
    ->   set pOutput = '';
    ->   set pOutput = concat(pOutput, 'abc');
    ->   set pOutput = concat(pOutput, '');
    ->
    ->   return pOutput;
    -> end//
Query OK, 0 rows affected (0.00 sec)

mysql> select f2('abc')//
+-----------+
| f2('abc') |
+-----------+
|           |
+-----------+
1 row in set (0.00 sec)

mysql> select version()//
+--------------+
| version()    |
+--------------+
| 5.0.13-rc-nt |
+--------------+
1 row in set (0.00 sec)

Outside stored procedures CONCAT works as described in the manual ('' is not the same as NULL in MySQL):

mysql> set @a=''//
Query OK, 0 rows affected (0.00 sec)

mysql> set @a=concat(@a, 'abc')//
Query OK, 0 rows affected (0.00 sec)

mysql> select @a//
+------+
| @a   |
+------+
| abc  |
+------+
1 row in set (0.00 sec)

mysql> set @a=concat(@a, '')//
Query OK, 0 rows affected (0.00 sec)

mysql> select @a//
+------+
| @a   |
+------+
| abc  |
+------+
1 row in set (0.00 sec)

Thanks for the work-around, you saved my day

mysql> delimiter |
mysql> 
mysql> CREATE FUNCTION f1() RETURNS VARCHAR(20000)
    -> BEGIN
    ->   DECLARE var VARCHAR(2000);
    -> 
    ->   SET var = '';
    ->   SET var = CONCAT(var, 'abc');
    ->   SET var = CONCAT(var, '');
    -> 
    ->   RETURN var;
    -> end|
Query OK, 0 rows affected (0.00 sec)

mysql> 
mysql> delimiter ;
mysql> 
mysql> SELECT f1();
+------+
| f1() |
+------+
| abc  |
+------+
1 row in set (0.00 sec)

mysql> 
mysql> SELECT VERSION();
+-------------------------------+
| VERSION()                     |
+-------------------------------+
| 5.0.17-valgrind-max-debug-log |
+-------------------------------+
1 row in set (0.00 sec)

Alik, please add a test case for this bug to the test suite (sp.test), unless you're sure the case is covered already.

Added into sp-vars.test.