MySQL Bugs: #62129: Stored programs with string functions + quote ('') in argument distorted in I

Bug #62129	Stored programs with string functions + quote ('') in argument distorted in I_S
Submitted:	9 Aug 2011 18:33	Modified:	20 Jul 2012 13:48
Reporter:	Peter Laursen (Basic Quality Contributor)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Information schema	Severity:	S2 (Serious)
Version:	5.1.58, 5.1.59, 5.6.3, 5.5.15	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	regression

Description:
A trigger with a CONCAT construction inside distorts wiht SHOW CREATE TRIGGER. Likely it affects more string functions.

Same does not happen to a stored procedure when same construction is used in a SELECT.

How to repeat:
DELIMITER $$

CREATE
    TRIGGER `ttest`.`quotes` BEFORE DELETE
    ON `ttest`.`a`
    FOR EACH ROW BEGIN
	INSERT INTO b (txt) VALUES (CONCAT('number=''','9',''''));
END$$

DELIMITER ;

SHOW CREATE TRIGGER `ttest`.`quotes`;
/*
BEGIN  INSERT INTO b (txt) values (CONCAT('number='','9',''')); END
*/

-- note that 3 quotes became 2 quotes and 4 quotes became 3 quotes.

Suggested fix:
no idea.

Thank you for the report.

I can not repeat described behavior nor on Mac, neither on Linux:

mysql> create table a(f1 int);
Query OK, 0 rows affected (0.22 sec)

mysql> \d |
mysql> 
mysql> CREATE
    ->     TRIGGER `test`.`quotes` BEFORE DELETE
    ->     ON `test`.`a`
    ->     FOR EACH ROW BEGIN
    -> INSERT INTO b (txt) VALUES (CONCAT('number=''','9',''''));
    -> END
    -> |
Query OK, 0 rows affected (0.22 sec)

mysql> \d ;
mysql> SHOW CREATE TRIGGER `test`.`quotes`\G
*************************** 1. row ***************************
               Trigger: quotes
              sql_mode: 
SQL Original Statement: CREATE DEFINER=`root`@`localhost` TRIGGER `test`.`quotes` BEFORE DELETE
    ON `test`.`a`
    FOR EACH ROW BEGIN
INSERT INTO b (txt) VALUES (CONCAT('number=''','9',''''));
END
  character_set_client: latin1
  collation_connection: latin1_swedish_ci
    Database Collation: utf8_general_ci
1 row in set (0.00 sec)

Please indicate package name which you use and provide your configuration file.

closing. This is a client issue.

I am sorry.  The bug does not appear with SHOW CREATE TRIGGER but with:

SHOW TRIGGERS FROM `ttest` WHERE `Trigger`='quotes';

So reopening!

Problem also reproducible whne querying I_S:

SELECT action_statement FROM information_schema.triggers WHERE event_object_schema = 'ttest';

-- returns

action_statement                                                   
-------------------------------------------------------------------
BEGIN                                                              
	INSERT INTO b (txt) values (CONCAT('number='','9','''));          
END  

(and besides I clarified synopsis a little.  Feel free t change category to I_S if more appropriate)

Also reproducible with SP's:

DELIMITER $$

CREATE PROCEDURE `ttest`.`spstring`()
    BEGIN
	SELECT CONCAT('number=''','9','''');
    END$$

DELIMITER ;

SELECT routine_definition FROM information_schema.routines WHERE routine_schema = 'ttest';

-- returns

routine_definition                               
-------------------------------------------------
BEGIN                                            
	SELECT CONCAT('number='','9',''');              
END 

(again I updated synopsis and changed category to I_S)

an example with EVENTS:

DELIMITER $$

-- SET GLOBAL event_scheduler = ON$$     -- required for event to execute but not create    

CREATE EVENT `ttest`.`stringev`

ON SCHEDULE EVERY 1 HOUR DO
	BEGIN
	    SET @test = CONCAT('number=''','9','''');
	END$$

DELIMITER ;

SELECT event_definition FROM information_schema.events WHERE event_schema = 'ttest';

OK .. I think I understand!

This " '' " is an escaped " ' ".  It is actually only " ' " that is stored. That would apply to physical tables at least. 

SELECT FROM I_S in a similar way returns *what is stored*. SHOW CREATE 'compensates' by padding extra " ' "'s in the output. But why does not SHOW TRIGGERS then?

And besides I do not find the consideration *what is stored* relevant for I_S.  This is not a real database (it is only accessed with SQL like you would access a real database) and nothing is really stored. 

In my opinion I_S should return information about *how things were created* and *how things can be (re-)created*. And at least SHOW TRIGGERS should.

Please update here as soon as possible what you want to do about it?
1) nothing?
2) fix SHOW TRIGGERS (only)?
3) fix SHOW TRIGGERS + SELECT FROM I_S?

marking as regression. 

SHOW TRIGGERS is OK in MySQL 5.0.90

Thank you for the feedback.

Verified as described.

Can be related to bug #58342

#58342 is a complaint (like mine here) that SELECT FROM I_S does not return reliable metadata (in specific cases like when a string function in a stored program definition has a single-quote as argument).  But this is not a regression.  It is the same in 5.0.

I think you should keep both reports open and let this one handle the regression in 5.1 with SHOW TRIGGERS. This I would actually like to see fixed soon.

5.5.15 also affected by bug in SHOW TRIGGERS

Any chance that the issue with SHOW TRIGGERS can be fixed soon?

Any chance that this could be fixed?  It is not satisfactory that a serious bug like this takes 1+ year to fix.