Bug #94344 `SHOW TRIGGERS LIKE` doesn't correctly interpret the underscore symbol (`_`)
Submitted: 15 Feb 11:35 Modified: 18 Feb 16:14
Reporter: Pippo Pisellino Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Parser Severity:S3 (Non-critical)
Version:5.7.25 OS:Any
Assigned to: CPU Architecture:Any

[15 Feb 11:35] Pippo Pisellino
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 16:14] Bogdan Kecman
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) >