Description:
As a human that may want to query against the digests stored in the P_S tables, or indeed as an author of code that allows a human user to do the same thing, the spaces that are injected into the query digests are problematic.
If wanting to search for a query where the format present at execution on the client and only only the digests are available then it requires far more effort to find queries.
Whilst statement_digest_text does indeed exist to help those on MySQL 8.0, using a function shouldn't really be necessary and its existence indicates that there is already a problem :) .. although, I would hazard a guess that it is simply exposing the internal function to the user.
How to repeat:
Execute: select distinct plugin from mysql.user
Search: find that query by only using digest text fields, e.g.
SELECT digest_text
FROM events_statements_summary_by_digest
WHERE digest_text RLIKE '`?mysql`?\.`?user`?'
Matches will not be found due to the injected spaces.
The following is a slightly different issue as it does not involve spaces, but nonetheless make the user think more than necessary.
Search: find that query by only using the digest text fields and the query prefix, e.g.
SELECT digest_text
FROM events_statements_summary_by_digest
WHERE digest_text LIKE 'SELECT DISTINCT PLUGIN%'
Matches will not be found due to the change of DISTINCT to DISTINCTROW.
Suggested fix:
To solve the issue of spaces, provide a means for the user to configure the server so that spaces are not used in statement digests.
It would probably be sufficient to allow for a variable that can either be applied at the global, or session level and a means to update historical records. That would then allow a user to test out records that only affect their own queries before applying globally and then repairing any existing history. A system-level configuration option would be sufficient though.
Removing spaces completely could cause problems if there is any ecosystem code already accounting for the spaces.
