Bug #73954 Performance schema digest_text does not contain same text as original sql

• Submitted: 17 Sep 2014 17:25
• Modified: 18 Sep 2014 11:44
• Reporter: Justin Swanhart
• Status: Not a Bug
• Category: MySQL Server: Performance Schema
• Severity: S2 (Serious)
• Version: 5.6
• OS: Any
• CPU Architecture: Any
• Tags: digests, performance schema

[17 Sep 2014 17:25] Justin Swanhart

Description:
This is the original input SQL:
mysql> select * from mysql.user limit 1;

From the Performance_Schema:
digest_text: SELECT * FROM `mysql` . `user` LIMIT ? 

Notice that the query has been "rewritten" incorrectly: 
`mysql`<space><dot><space>`user` is not valid SQL

How to repeat:
Run a valid SQL that contains <db>.<table> and note the digest_text in the performance schema will look like <db> . <table> which is not correct.

[18 Sep 2014 11:44] MySQL Verification Team

Hello Justin,

Thank you for the report.
Indeed, it adds space in between but the later query(digest_text) is still valid one and parser doesn't complain at all because identifiers and period (“.”) are individual tokens, which may have white space in between. 

Also, Digesting converts a SQL statement to normalized form and computes a hash value for the result, please see http://dev.mysql.com/doc/refman/5.6/en/performance-schema-statement-digests.html

// On 5.6.20, I don't see any issue with parsing rewritten/normalized query, and works same way as original query:

mysql>  SELECT * FROM mysql.user LIMIT 1\G
*************************** 1. row ***************************
                  Host: localhost
                  User: root
..
1 row in set (0.00 sec)

mysql> select * from performance_schema.events_statements_history\G

*************************** 9. row ***************************
              THREAD_ID: 125
               EVENT_ID: 209
           END_EVENT_ID: 246
             EVENT_NAME: statement/sql/select
                 SOURCE: mysqld.cc:965
            TIMER_START: 195020368784000
              TIMER_END: 195020654354000
             TIMER_WAIT: 285570000
              LOCK_TIME: 133000000
               SQL_TEXT: SELECT * FROM mysql.user LIMIT 1
                 DIGEST: 0b870ef5545257d3c71ad4f1acaa5dcd
            DIGEST_TEXT: SELECT * FROM `mysql` . `user` LIMIT ?
 
9 rows in set (0.00 sec)

mysql> SELECT * FROM `mysql` . `user` LIMIT 1\G
*************************** 1. row ***************************
                  Host: localhost
                  User: root
              Password:

..
1 row in set (0.00 sec)

Thanks,
Umesh