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: | |
Category: | MySQL Server: Stored Routines | Severity: | S3 (Non-critical) |
Version: | 5.0.12 | OS: | Linux (Linux, Windows) |
Assigned to: | Petr Chardin | CPU Architecture: | Any |
[6 Sep 2005 14:14]
Gleb Paharenko
[6 Sep 2005 15:19]
Valeriy Kravchuk
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
[19 Sep 2005 18:29]
Petr Chardin
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.