Bug #80372 Built-in mysql functions are case sensitive when used in query digest hash
Submitted: 15 Feb 2016 12:58 Modified: 16 Feb 2016 0:47
Reporter: Morgan Tocker Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Performance Schema Severity:S3 (Non-critical)
Version:8.0.0-m17-enterprise-commercial-advanced OS:Any
Assigned to: Marc ALFF CPU Architecture:Any

[15 Feb 2016 12:58] Morgan Tocker
Description:
When using built-in functions (i.e. get_lock) the digest hashing depends on the case used in the function.  This makes it much harder to use a query rewrite rule, since there needs to be one-rule per case.

How to repeat:
The last select returns 2 rows.  Ideally it should return 1:

use performance_schema;
truncate events_statements_summary_by_digest;
select * from events_statements_summary_by_digest where DIGEST_TEXT like '%get_lock%';
select GET_LOCK('abcdef', 10);
select GET_LOCK('abc', 10);
select get_lock('abcn', 10);
select get_lock('abcn2', 10);
select * from events_statements_summary_by_digest where DIGEST_TEXT like '%get_lock%'\G

Example:

mysql> truncate events_statements_summary_by_digest;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from events_statements_summary_by_digest where DIGEST_TEXT like '%get_lock%';
Empty set (0.00 sec)

mysql> select GET_LOCK('abcdef', 10);
+------------------------+
| GET_LOCK('abcdef', 10) |
+------------------------+
|                      1 |
+------------------------+
1 row in set (0.00 sec)

mysql> select GET_LOCK('abc', 10);
+---------------------+
| GET_LOCK('abc', 10) |
+---------------------+
|                   1 |
+---------------------+
1 row in set (0.00 sec)

mysql> select get_lock('abcn', 10);
+----------------------+
| get_lock('abcn', 10) |
+----------------------+
|                    1 |
+----------------------+
1 row in set (0.00 sec)

mysql> select get_lock('abcn2', 10);
+-----------------------+
| get_lock('abcn2', 10) |
+-----------------------+
|                     1 |
+-----------------------+
1 row in set (0.00 sec)

mysql> select * from events_statements_summary_by_digest where DIGEST_TEXT like '%get_lock%'\G
*************************** 1. row ***************************
                SCHEMA_NAME: performance_schema
                     DIGEST: ee7c2b19baee272d6a3df36aff4ab009
                DIGEST_TEXT: SELECT `GET_LOCK` (...)
                 COUNT_STAR: 2
             SUM_TIMER_WAIT: 334159000
             MIN_TIMER_WAIT: 135749000
             AVG_TIMER_WAIT: 167079000
             MAX_TIMER_WAIT: 198410000
              SUM_LOCK_TIME: 0
                 SUM_ERRORS: 0
               SUM_WARNINGS: 0
          SUM_ROWS_AFFECTED: 0
              SUM_ROWS_SENT: 2
          SUM_ROWS_EXAMINED: 0
SUM_CREATED_TMP_DISK_TABLES: 0
     SUM_CREATED_TMP_TABLES: 0
       SUM_SELECT_FULL_JOIN: 0
 SUM_SELECT_FULL_RANGE_JOIN: 0
           SUM_SELECT_RANGE: 0
     SUM_SELECT_RANGE_CHECK: 0
            SUM_SELECT_SCAN: 0
      SUM_SORT_MERGE_PASSES: 0
             SUM_SORT_RANGE: 0
              SUM_SORT_ROWS: 0
              SUM_SORT_SCAN: 0
          SUM_NO_INDEX_USED: 0
     SUM_NO_GOOD_INDEX_USED: 0
                 FIRST_SEEN: 2016-02-15 12:51:44
                  LAST_SEEN: 2016-02-15 12:51:52
*************************** 2. row ***************************
                SCHEMA_NAME: performance_schema
                     DIGEST: 0007fbbcc280ace574bcf43940f522da
                DIGEST_TEXT: SELECT `get_lock` (...)
                 COUNT_STAR: 2
             SUM_TIMER_WAIT: 298418000
             MIN_TIMER_WAIT: 131014000
             AVG_TIMER_WAIT: 149209000
             MAX_TIMER_WAIT: 167404000
              SUM_LOCK_TIME: 0
                 SUM_ERRORS: 0
               SUM_WARNINGS: 0
          SUM_ROWS_AFFECTED: 0
              SUM_ROWS_SENT: 2
          SUM_ROWS_EXAMINED: 0
SUM_CREATED_TMP_DISK_TABLES: 0
     SUM_CREATED_TMP_TABLES: 0
       SUM_SELECT_FULL_JOIN: 0
 SUM_SELECT_FULL_RANGE_JOIN: 0
           SUM_SELECT_RANGE: 0
     SUM_SELECT_RANGE_CHECK: 0
            SUM_SELECT_SCAN: 0
      SUM_SORT_MERGE_PASSES: 0
             SUM_SORT_RANGE: 0
              SUM_SORT_ROWS: 0
              SUM_SORT_SCAN: 0
          SUM_NO_INDEX_USED: 0
     SUM_NO_GOOD_INDEX_USED: 0
                 FIRST_SEEN: 2016-02-15 12:52:01
                  LAST_SEEN: 2016-02-15 12:52:03
2 rows in set (0.00 sec)

Suggested fix:
Built-ins should be insensitive similar to tokens like SELECT being insensitive.
[16 Feb 2016 0:47] MySQL Verification Team
Thank you for the bug report.

miguel@tikal ~/dbs/5.8 $ bin/mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.8.0-m17-debug Source distribution

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>
mysql>
mysql> use performance_schema;
truncate events_statements_summary_by_digest;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

select * from events_statements_summary_by_digest where DIGEST_TEXT like '%get_lock%';
select GET_LOCK('abcdef', 10);
select GET_LOCK('abc', 10);
select get_lock('abcn', 10);
select get_lock('abcn2', 10);
select * from events_statements_summary_by_digest where DIGEST_TEXT like '%get_lock%'\G
Database changed
mysql> truncate events_statements_summary_by_digest;
Query OK, 0 rows affected (0,00 sec)

mysql> select * from events_statements_summary_by_digest where DIGEST_TEXT like '%get_lock%';
Empty set (0,00 sec)

mysql> select GET_LOCK('abcdef', 10);
+------------------------+
| GET_LOCK('abcdef', 10) |
+------------------------+
|                      1 |
+------------------------+
1 row in set (0,00 sec)

mysql> select GET_LOCK('abc', 10);
+---------------------+
| GET_LOCK('abc', 10) |
+---------------------+
|                   1 |
+---------------------+
1 row in set (0,00 sec)

mysql> select get_lock('abcn', 10);
+----------------------+
| get_lock('abcn', 10) |
+----------------------+
|                    1 |
+----------------------+
1 row in set (0,00 sec)

mysql> select get_lock('abcn2', 10);
+-----------------------+
| get_lock('abcn2', 10) |
+-----------------------+
|                     1 |
+-----------------------+
1 row in set (0,00 sec)

mysql> select * from events_statements_summary_by_digest where DIGEST_TEXT like '%get_lock%'\G
*************************** 1. row ***************************
                SCHEMA_NAME: performance_schema
                     DIGEST: f9adb7466b20afb5d684a86c66f80b96
                DIGEST_TEXT: SELECT `GET_LOCK` (...)
                 COUNT_STAR: 2
             SUM_TIMER_WAIT: 146510000
             MIN_TIMER_WAIT: 67902000
             AVG_TIMER_WAIT: 73255000
             MAX_TIMER_WAIT: 78608000
              SUM_LOCK_TIME: 0
                 SUM_ERRORS: 0
               SUM_WARNINGS: 0
          SUM_ROWS_AFFECTED: 0
              SUM_ROWS_SENT: 2
          SUM_ROWS_EXAMINED: 0
SUM_CREATED_TMP_DISK_TABLES: 0
     SUM_CREATED_TMP_TABLES: 0
       SUM_SELECT_FULL_JOIN: 0
 SUM_SELECT_FULL_RANGE_JOIN: 0
           SUM_SELECT_RANGE: 0
     SUM_SELECT_RANGE_CHECK: 0
            SUM_SELECT_SCAN: 0
      SUM_SORT_MERGE_PASSES: 0
             SUM_SORT_RANGE: 0
              SUM_SORT_ROWS: 0
              SUM_SORT_SCAN: 0
          SUM_NO_INDEX_USED: 0
     SUM_NO_GOOD_INDEX_USED: 0
                 FIRST_SEEN: 2016-02-15 22:45:35
                  LAST_SEEN: 2016-02-15 22:45:35
*************************** 2. row ***************************
                SCHEMA_NAME: performance_schema
                     DIGEST: 5b8047c8a72ce408986e9aad8aa60b4f
                DIGEST_TEXT: SELECT `get_lock` (...)
                 COUNT_STAR: 2
             SUM_TIMER_WAIT: 143761000
             MIN_TIMER_WAIT: 66465000
             AVG_TIMER_WAIT: 71880000
             MAX_TIMER_WAIT: 77296000
              SUM_LOCK_TIME: 0
                 SUM_ERRORS: 0
               SUM_WARNINGS: 0
          SUM_ROWS_AFFECTED: 0
              SUM_ROWS_SENT: 2
          SUM_ROWS_EXAMINED: 0
SUM_CREATED_TMP_DISK_TABLES: 0
     SUM_CREATED_TMP_TABLES: 0
       SUM_SELECT_FULL_JOIN: 0
 SUM_SELECT_FULL_RANGE_JOIN: 0
           SUM_SELECT_RANGE: 0
     SUM_SELECT_RANGE_CHECK: 0
            SUM_SELECT_SCAN: 0
      SUM_SORT_MERGE_PASSES: 0
             SUM_SORT_RANGE: 0
              SUM_SORT_ROWS: 0
              SUM_SORT_SCAN: 0
          SUM_NO_INDEX_USED: 0
     SUM_NO_GOOD_INDEX_USED: 0
                 FIRST_SEEN: 2016-02-15 22:45:35
                  LAST_SEEN: 2016-02-15 22:45:35
2 rows in set (0,00 sec)

mysql>
[19 Feb 2016 6:02] Erlend Dahl
Posted by developer:
 
It's the same all the way back to 5.6 (the feature doesn't exist in 5.5).
[18 Jun 2016 21:36] Omer Barnir
Posted by developer:
 
Reported version value updated to reflect release name change from 5.8 to 8.0