| Bug #115943 | STATEMENT_DIGEST() adds trailing semicolon to digest | ||
|---|---|---|---|
| Submitted: | 28 Aug 2024 6:50 | Modified: | 27 Jul 19:34 |
| Reporter: | Kaiwang CHen (OCA) | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Performance Schema | Severity: | S3 (Non-critical) |
| Version: | 8.0, 8.4, 9.0, 8.0.39, 8.4.2 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | Contribution | ||
[28 Aug 2024 6:51]
Kaiwang CHen
Enforce same trimming in these digest functions as alloc_query() does. (*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.
Contribution: bug_statement_digest_garbage.patch (application/octet-stream, text), 2.55 KiB.
[28 Aug 2024 7:00]
MySQL Verification Team
Hello Kaiwang, Thank you for the report and contribution. regards, Umesh
[2 Sep 2024 5:43]
MySQL Verification Team
Bug #115968 marked as duplicate of this one
[28 May 1:13]
MySQL Admin
Posted by developer: Bug status updated to 'Documenting'
[27 Jul 19:34]
Edward Gilmore
Posted by developer: Added the following note to the MySQL Server 26.7.0 release notes: Queries with the same SQL_DIGEST could produce identical summaries, even if they had distinct characteristics, such as differing ORDER BY clauses. The digest computation has been improved to better distinguish between these queries. Our thanks to Kaiwang Chen for the contribution.
Description: STATEMENT_DIGEST_TEXT('select 1') gets 'SELECT ?' as digest text, while STATEMENT_DIGEST_TEXT('select 1;') gets 'SELECT ? ;'. Thus different digest texts for the same statement. STATEMENT_DIGEST() gets different digest hashes as well. This is not natural and not intended. How to repeat: mysql> SELECT statement_digest_text('select 1;'); +------------------------------------+ | statement_digest_text('select 1;') | +------------------------------------+ | SELECT ? ; | +------------------------------------+ mysql> SELECT statement_digest_text('select 1'); +-----------------------------------+ | statement_digest_text('select 1') | +-----------------------------------+ | SELECT ? | +-----------------------------------+ 1 row in set (0.00 sec) mysql> SELECT statement_digest('select 1;'); +------------------------------------------------------------------+ | statement_digest('select 1;') | +------------------------------------------------------------------+ | 4b46f54bd8065b8dc5777a0cc14bcefc2be16c230edaa024ddd28ebf988a865c | +------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> SELECT statement_digest('select 1'); +------------------------------------------------------------------+ | statement_digest('select 1') | +------------------------------------------------------------------+ | d1b44b0c19af710b5a679907e284acd2ddc285201794bc69a2389d77baedddae | +------------------------------------------------------------------+ 1 row in set (0.00 sec) Suggested fix: The root cause is the digest functions do not trim garbage before invoking parser, which includes whitespaces and semicolon. While regular execution and parser service invoke alloc_query() and use trimmed query text. Enforce same trimming in these digest functions. Digest text with no trailing semicolon are better for usability in general.