MySQL Bugs: #115649: Mixed quoting styles in SHOW CREATE TRIGGER

Bug #115649	Mixed quoting styles in SHOW CREATE TRIGGER
Submitted:	19 Jul 11:38	Modified:	19 Aug 12:24
Reporter:	dbForge Team	Email Updates:
Status:	In progress	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S3 (Non-critical)
Version:	8.0.38	OS:	Linux (version_compile_machine: x86_64)
Assigned to:		CPU Architecture:	Any
Tags:	ANSI_QUOTES, DDL, TRIGGER BEGIN END

Description:
If a trigger is created without the ansi_quotes setting and then SHOW CREATE TRIGGER is executed with sql_mode = 'ansi_quotes', the trigger definition will have mixed quoting styles, which do not correspond to any specific mode.

How to repeat:
SET sql_mode = '';

CREATE TABLE ttrg_str(`col umn` varchar(100));
 
CREATE TRIGGER `t rg_str` BEFORE INSERT ON ttrg_str FOR EACH ROW SET NEW.`col umn` = "string";

SET sql_mode = 'ansi_quotes';

SHOW CREATE TRIGGER `t rg_str`;

Result:

CREATE DEFINER="root"@"%" TRIGGER "t rg_str" BEFORE INSERT ON "ttrg_str" FOR EACH ROW SET NEW.`col umn` = "string"

Hi Mr. dbForge,

Thank you for your bug report.

However, this is not a bug.

Setting sql_mode to empty string is not supported.

Regarding ansi quotes that are fully explained here:

https://dev.mysql.com/doc/refman/8.0/en/identifiers.html

If anything has to be done, that we shall update that page .....

Not a bug.

Hi,

This also looks like expected behaviour when you mix ansi quotes.

We shall ask our Documentation team to update that page with a warning  ..........

I think both the `SHOW CREATE TRIGGER` statement and `information_schema.TRIGGERS` table show the `sql_mode` that was used for the `CREATE TRIGGER` statement.

The `sql_mode` would likely also affect things like PIPES_AS_CONCAT, etc.

Thank you Mr. Van Eeden.

The installation of an empty string is unnecessary; this behavior occurs with any sql_mode that does not include ansi_quotes.

For instance:
SET sql_mode = 'NO_ENGINE_SUBSTITUTION';
CREATE TABLE ttrg_str(`col umn` varchar(100));
CREATE TRIGGER `t rg_str` BEFORE INSERT ON ttrg_str FOR EACH ROW SET NEW.`col umn` = "string";
SET sql_mode = 'ansi_quotes';
SHOW CREATE TRIGGER `t rg_str`;

Output:
CREATE DEFINER="root"@"%" TRIGGER "t rg_str" BEFORE INSERT ON "ttrg_str" FOR EACH ROW SET NEW.`col umn` = "string"

In contrast, MySQL 5.7.36 provides a result with consistent quoting:
CREATE DEFINER=`root`@`%` TRIGGER `t rg_str` BEFORE INSERT ON ttrg_str FOR EACH ROW SET NEW.`col umn` = "string"

Notably, this issue does not impact stored procedures:
CREATE TABLE `t1 1`(id INT PRIMARY KEY);

SET sql_mode = 'no_engine_substitution';
DELIMITER $$

CREATE PROCEDURE `p quotes`(i int) 
BEGIN 
SELECT id,  "string" FROM `t1 1` ;
END;
$$
DELIMITER ;

SET sql_mode = 'ansi_quotes';
SHOW CREATE PROCEDURE "p quotes";

Output:
CREATE DEFINER=`root`@`%` PROCEDURE `p quotes`(i int)
BEGIN 
SELECT id,  "string" FROM `t1 1` ;
END

HI Mr. Team,

We have informed our Documentation team to update the page in our Reference manual, regarding the manner in which ansi_quotes changes SHOW CREATE TRIGGER.

However, we do not know whether it will be accepted, since both the `SHOW CREATE TRIGGER` statement and `information_schema.TRIGGERS` table show the `sql_mode` that was used for the `CREATE TRIGGER` statement.

Also empty sql_mode is not supported.

Not a bug.

Please inform us about these changes in the documentation.

The script from SHOW CREATE TRIGGER cannot be executed in the sql_mode that SHOW CREATE TRIGGER returned.

Hi,

As soon as we know it, you will know it ......

However, before it is documented, our Development has to make a decision whether it is necessary or not. That decision will be probably made after the period of vacations .......

Accepted as a Documentation issue, assigning to myself for resolution.

Thanks!

Thank you, Jon.

Hello,

We still do not understand why this behavior is not considered a bug. My server is configured with ANSI_QUOTES set to ON. I execute the following script in one session:
 
SET sql_mode = 'NO_ENGINE_SUBSTITUTION';
CREATE TABLE ttrg_str (`col umn` varchar(100));
CREATE TRIGGER `t rg_str` BEFORE INSERT ON ttrg_str FOR EACH ROW SET NEW.`col umn` = "string";
 
Then, I open a new session and execute the following query:
 
SHOW CREATE TRIGGER `t rg_str`;
 
I get the following DDL statement as a result:
 
CREATE DEFINER=`root`@`%` TRIGGER "t rg_str" BEFORE INSERT ON "ttrg_str" FOR EACH ROW SET NEW.`col umn` = "string";
 
This is obviously invalid because if I turn ANSI_QUOTES off, the server considers the trigger and table names as string literals. Conversely, if ANSI_QUOTES is on, the trigger body is incorrect due to the unknown identifier "string".

This behavior occurs only for triggers and only when executing SHOW CREATE in a session where the ANSI_QUOTES mode is different from the value used when the CREATE TRIGGER statement was executed. It is clear as a day that this is a bug.

Hi,

We already informed you that our Development team will make a decision on this bug report, after vacations.

Vacations are not over yet ......

So, this can yet become a code bug, but you will have to wait on that decision.