Bug #94344 `SHOW TRIGGERS LIKE` doesn't correctly interpret the underscore symbol (`_`)

• Submitted: 15 Feb 2019 11:35
• Modified: 18 Feb 2019 16:14
• Reporter: S. M.
• Status: Verified
• Category: MySQL Server: Parser
• Severity: S3 (Non-critical)
• Version: 5.7.25
• OS: Any
• CPU Architecture: Any

[15 Feb 2019 11:35] S. M.

Description:
`SHOW TRIGGERS LIKE` doesn't correctly interpret the underscore symbol (`_`), not returning any of the expected triggers.

Interestingly, this problem doesn't apply to all the triggers. The underscore works in some cases, and not others.

How to repeat:
Create a trigger with the following name:

    CREATE TRIGGER enqueue_customers_update ...

The `LIKE` clause with percent (`%`) works as expected:

    SHOW TRIGGERS LIKE '%customers%'\G

    *************************** 1. row ***************************
    Trigger: enqueue_customers_update
    ...

The `WHERE` clause with percent and underscore (`_`) works as expected:

    SHOW TRIGGERS WHERE `Trigger` LIKE '%_customers%'\G

    *************************** 1. row ***************************
    Trigger: enqueue_customers_update
    ...

The `LIKE` clause with percent and underscore doesn't work as expected, returning no triggers:

    SHOW TRIGGERS LIKE '%_customers%'\G
    Empty set (0,00 sec)

Note that I'm aware that the underscore is a wildcard symbol, and should be escaped for exactness, but it's irrelevant in this context.

Suggested fix:
The underscore should be evaluated as in any LIKE clause.

[18 Feb 2019 16:14] MySQL Verification Team

Hi,

on 8.0.14 I got different results but it's still a bug IMO so I'm verifying this

thanks for report
Bogdan

master [localhost:20415] {msandbox} (test) > CREATE TABLE account (acct_num INT, amount DECIMAL(10,2));
Query OK, 0 rows affected (0.09 sec)

master [localhost:20415] {msandbox} (test) > CREATE TRIGGER enqueue_customers_update BEFORE INSERT ON account FOR EACH ROW SET @sum = @sum + NEW.amount;
Query OK, 0 rows affected (0.04 sec)

master [localhost:20415] {msandbox} (test) > SHOW TRIGGERS LIKE '%customers%'\G
Empty set (0.00 sec)

master [localhost:20415] {msandbox} (test) > SHOW TRIGGERS WHERE `Trigger` LIKE '%_customers%'\G
*************************** 1. row ***************************
             Trigger: enqueue_customers_update
               Event: INSERT
               Table: account
           Statement: SET @sum = @sum + NEW.amount
              Timing: BEFORE
             Created: 2019-02-18 17:11:33.17
            sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
             Definer: msandbox@localhost
character_set_client: utf8mb4
collation_connection: utf8mb4_0900_ai_ci
  Database Collation: utf8mb4_0900_ai_ci
1 row in set (0.01 sec)

master [localhost:20415] {msandbox} (test) > SHOW TRIGGERS LIKE '%_customers%'\G
Empty set (0.00 sec)

master [localhost:20415] {msandbox} (test) > SHOW TRIGGERS LIKE '%_cus%'\G
Empty set (0.00 sec)

master [localhost:20415] {msandbox} (test) > SHOW TRIGGERS LIKE '%'\G
*************************** 1. row ***************************
             Trigger: enqueue_customers_update
               Event: INSERT
               Table: account
           Statement: SET @sum = @sum + NEW.amount
              Timing: BEFORE
             Created: 2019-02-18 17:11:33.17
            sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
             Definer: msandbox@localhost
character_set_client: utf8mb4
collation_connection: utf8mb4_0900_ai_ci
  Database Collation: utf8mb4_0900_ai_ci
1 row in set (0.01 sec)

master [localhost:20415] {msandbox} (test) >
master [localhost:20415] {msandbox} (test) > SHOW TRIGGERS LIKE '%_c%'\G
*************************** 1. row ***************************
             Trigger: enqueue_customers_update
               Event: INSERT
               Table: account
           Statement: SET @sum = @sum + NEW.amount
              Timing: BEFORE
             Created: 2019-02-18 17:11:33.17
            sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
             Definer: msandbox@localhost
character_set_client: utf8mb4
collation_connection: utf8mb4_0900_ai_ci
  Database Collation: utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

master [localhost:20415] {msandbox} (test) > SHOW TRIGGERS LIKE '%_cu%'\G
Empty set (0.01 sec)

master [localhost:20415] {msandbox} (test) >