MySQL Bugs: #13015: || ansi concat operator doesn't work in stored procedures in 'ansi' sql

Bug #13015	\|\| ansi concat operator doesn't work in stored procedures in 'ansi' sql_mode
Submitted:	6 Sep 2005 14:14	Modified:	19 Sep 2005 18:29
Reporter:	Gleb Paharenko	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S3 (Non-critical)
Version:	5.0.12	OS:	Linux (Linux, Windows)
Assigned to:	Petr Chardin	CPU Architecture:	Any

Description:
ansi || operator should concat strings in sql_mode='ansi',  but for a pity in stored procedures
it doesn't work.

How to repeat:
Here is why I think that this is a bug.

This is simple SQL from command line:

delimiter //

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

mysql> select @@sql_mode;//
+-------------------------------------------------------------+
| @@sql_mode                                                  |
+-------------------------------------------------------------+
| REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI |
+-------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select '2' || '1'//
+------------+
| '2' || '1' |
+------------+
| 21         |
+------------+
1 row in set (0.00 sec)
This is ok, but ...

This is a stored procedure which doesn't work

mysql> call tables_concat2(@bb)//
+-------------------------------------------------------------+
| @@sql_mode                                                  |
+-------------------------------------------------------------+
| REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI |
+-------------------------------------------------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected, 66 warnings (0.01 sec)

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

Warnings are similar to these:

*************************** 61. row ***************************
  Level: Warning
   Code: 1292
Message: Truncated incorrect DOUBLE value: 'time_zone_transition'
*************************** 62. row ***************************
  Level: Warning
   Code: 1292
Message: Truncated incorrect DOUBLE value: '.'
*************************** 63. row ***************************
  Level: Warning
   Code: 1292
Message: Truncated incorrect DOUBLE value: 'time_zone_transition_type'
*************************** 64. row ***************************
  Level: Warning
   Code: 1292
Message: Truncated incorrect DOUBLE value: '.'

mysql> show create procedure tables_concat2\G//
*************************** 1. row ***************************
       Procedure: tables_concat2
        sql_mode:
Create Procedure: CREATE PROCEDURE `test`.`tables_concat2`(OUT parameter1 VARCHAR(1000))
    DETERMINISTIC
BEGIN
  DECLARE variable2 CHAR(100);
  DECLARE c CURSOR FOR
  SELECT table_name FROM information_schema.tables;
  DECLARE EXIT HANDLER FOR NOT FOUND BEGIN END;
  SET @@sql_mode='ansi';
        SELECT @@sql_mode;
  SET parameter1 = '';
  OPEN c;
  LOOP
    FETCH c INTO variable2;
                SET parameter1 = parameter1 || variable2 || '.';

    END LOOP;
  CLOSE c;
  END

While this procedure works fine:

mysql> call tables_concat(@bb)//
+-------------------------------------------------------------+
| @@sql_mode                                                  |
+-------------------------------------------------------------+
| REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI |
+-------------------------------------------------------------+
1 row in set (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

mysql> select @bb\G//
*************************** 1. row ***************************
@bb: CHARACTER_SETS.COLLATIONS.COLLATION_CHARACTER_SET_APPLICABILITY.COLUMNS.COLUMN_PRIVILEGES.KEY_COLUMN_USAGE.ROUTINES.SCHEMATA.SCHEMA_PRIVILEGES.STATISTICS.TABLES.TABLE_CONSTRAINTS.TABLE_PRIVILEGES.TRIGGERS.VIEWS.USER_PRIVILEGES.columns_priv.db.func.help_category.help_keyword.help_relation.help_topic.host.proc.procs_priv.tables_priv.time_zone.time_zone_leap_second.time_zone_name.time_zone_transition.time_zone_transition_type.user.
1 row in set (0.00 sec)

mysql> show create procedure tables_concat\G//
*************************** 1. row ***************************
       Procedure: tables_concat
        sql_mode:
Create Procedure: CREATE PROCEDURE `test`.`tables_concat`(OUT parameter1 VARCHAR(1000))
    DETERMINISTIC
BEGIN
  DECLARE variable2 CHAR(100);
  DECLARE c CURSOR FOR
  SELECT table_name FROM information_schema.tables;
  DECLARE EXIT HANDLER FOR NOT FOUND BEGIN END;
  SET @@sql_mode='ansi';
        SELECT @@sql_mode;
  SET parameter1 = '';
  OPEN c;
  LOOP
    FETCH c INTO variable2;

    SET parameter1 = CONCAT(parameter1, variable2, '.');
    END LOOP;
  CLOSE c;
  END

+-----------------------+
| version()             |
+-----------------------+
| 5.0.12-beta-debug-log |
+-----------------------+

Suggested fix:
Make so that sql_mode='ansi' works as expected

I was able to repeat it on 5.0.12 on Windows just as described in the report:

mysql> use test;
Database changed
mysql> delimiter //
mysql>
mysql> CREATE PROCEDURE `test`.`tables_concat`(OUT parameter1
    -> VARCHAR(1000))
    ->     DETERMINISTIC
    -> BEGIN
    ->   DECLARE variable2 CHAR(100);
    ->   DECLARE c CURSOR FOR
    ->   SELECT table_name FROM information_schema.tables;
    ->   DECLARE EXIT HANDLER FOR NOT FOUND BEGIN END;
    ->   SET @@sql_mode='ansi';
    ->         SELECT @@sql_mode;
    ->   SET parameter1 = '';
    ->   OPEN c;
    ->   LOOP
    ->     FETCH c INTO variable2;
    ->
    ->     SET parameter1 = CONCAT(parameter1, variable2, '.');
    ->     END LOOP;
    ->   CLOSE c;
    ->   END
    -> //
Query OK, 0 rows affected (0.16 sec)

mysql> CREATE PROCEDURE `test`.`tables_concat2`(OUT parameter1
    -> VARCHAR(1000))
    ->     DETERMINISTIC
    -> BEGIN
    ->   DECLARE variable2 CHAR(100);
    ->   DECLARE c CURSOR FOR
    ->   SELECT table_name FROM information_schema.tables;
    ->   DECLARE EXIT HANDLER FOR NOT FOUND BEGIN END;
    ->   SET @@sql_mode='ansi';
    ->         SELECT @@sql_mode;
    ->   SET parameter1 = '';
    ->   OPEN c;
    ->   LOOP
    ->     FETCH c INTO variable2;
    ->                 SET parameter1 = parameter1 || variable2 || '.';
    ->
    ->     END LOOP;
    ->   CLOSE c;
    ->   END
    -> //
Query OK, 0 rows affected (0.00 sec)

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

mysql> select @@sql_mode;//
+-------------------------------------------------------------+
| @@sql_mode                                                  |
+-------------------------------------------------------------+
| REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI |
+-------------------------------------------------------------+
1 row in set (0.01 sec)

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

mysql> call tables_concat2(@bb)//
+-------------------------------------------------------------+
| @@sql_mode                                                  |
+-------------------------------------------------------------+
| REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI |
+-------------------------------------------------------------+
1 row in set (0.02 sec)

Query OK, 0 rows affected, 80 warnings (0.86 sec)

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

mysql> call tables_concat(@aa)//
+-------------------------------------------------------------+
| @@sql_mode                                                  |
+-------------------------------------------------------------+
| REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI |
+-------------------------------------------------------------+
1 row in set (0.02 sec)

Query OK, 0 rows affected (0.09 sec)

mysql> select @aa//
+-------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------+
| @aa

                    |
+-------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------+
| CHARACTER_SETS.COLLATIONS.COLLATION_CHARACTER_SET_APPLICABILITY.COLUMNS.COLUMN
_PRIVILEGES.KEY_COLUMN_USAGE.ROUTINES.SCHEMATA.SCHEMA_PRIVILEGES.STATISTICS.TABL
ES.TABLE_CONSTRAINTS.TABLE_PRIVILEGES.TRIGGERS.VIEWS.USER_PRIVILEGES.columns_pri
v.db.func.help_category.help_keyword.help_relation.help_topic.host.proc.procs_pr
iv.tables_priv.time_zone.time_zone_leap_second.time_zone_name.time_zone_transiti
on.time_zone_transition_type.user._master.applevels.operationstt.sometab.somevie
s.table_1.viewtoop. |
+-------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------+
1 row in set (0.00 sec)

mysql> select version();
    -> //
+----------------+
| version()      |
+----------------+
| 5.0.12-beta-nt |
+----------------+
1 row in set (0.00 sec)

BUT. On today's 5.0.13-BK build () on Linux I've got:

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

mysql> call tables_concat(@a)//
+-------------------------------------------------------------+
| @@sql_mode                                                  |
+-------------------------------------------------------------+
| REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI |
+-------------------------------------------------------------+
1 row in set (0,00 sec)

Query OK, 0 rows affected (0,07 sec)

mysql> select @a\G//
*************************** 1. row ***************************
@a:
1 row in set (0,00 sec)

ERROR:
No query specified

mysql> select @a//;
+------+
| @a   |
+------+
|      |
+------+
1 row in set (0,00 sec)

So, table_concat (with CONCAT) also does not work at all on ChangeSet@1.1958, 2005-09-05 18:28:56+05:00, ramil@mysql.com

This is not a bug. According to the manual section on CRATE PROCEDURE
"MySQL stores the sql_mode system variable setting that is in effect at the time a routine is created, and always executes the routine with this setting in force." Which means that changing of the sql_mode variable within SP won't influence the way, the very procedure is executed.