MySQL Bugs: #110045: Negative numbers on digests

Bug #110045	Negative numbers on digests
Submitted:	13 Feb 2023 11:07	Modified:	6 Nov 14:42
Reporter:	Yakir Gibraltar (OCA)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Performance Schema	Severity:	S3 (Non-critical)
Version:	8.0.32	OS:	Linux
Assigned to:	Marc ALFF	CPU Architecture:	x86
Tags:	digest, digests, performance_schema

Description:
Hi, 
Negative numbers on digests in "where" not parsed out properly .
For example query:
"SELECT -1 WHERE 1 = -1" 
Generating:
"SELECT ? WHERE ? = - ?"
Instead:
"SELECT ? WHERE ? = ?"

Thank you. 

How to repeat:
db13:test> SELECT statement_digest_text("SELECT -1 WHERE 1 = 1");
+------------------------------------------------+
| statement_digest_text("SELECT -1 WHERE 1 = 1") |
+------------------------------------------------+
| SELECT ? WHERE ? = ?                           |
+------------------------------------------------+
1 row in set (0.00 sec)

db13:test> SELECT statement_digest_text("SELECT -1 WHERE 1 = -1");
+-------------------------------------------------+
| statement_digest_text("SELECT -1 WHERE 1 = -1") |
+-------------------------------------------------+
| SELECT ? WHERE ? = - ?                          |
+-------------------------------------------------+
1 row in set (0.00 sec)

Suggested fix:
SELECT statement_digest_text("SELECT -1 WHERE 1 = -1");
Should return : 
SELECT ? WHERE ? = ?

Hello Yakir,

Thank you for the report.

regards,
Umesh

Patch for gen_lex_token.cc and tests to fix this bug (*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: bug110045_complete.patch (application/octet-stream, text), 8.38 KiB.

I've created a patch that fixes the digest normalization issue for unary operators (+ and -) after comparison operators and CASE keywords.

Current MySQL Behavior (Broken):

mysql>   SELECT
    ->     STATEMENT_DIGEST_TEXT('SELECT * FROM t WHERE a = 1') AS digest_positive,
    ->     STATEMENT_DIGEST_TEXT('SELECT * FROM t WHERE a = -1') AS digest_negative,
    ->     STATEMENT_DIGEST_TEXT('SELECT * FROM t WHERE a = 1') =
    ->     STATEMENT_DIGEST_TEXT('SELECT * FROM t WHERE a = -1') AS are_equal;
+---------------------------------+-----------------------------------+-----------+
| digest_positive                 | digest_negative                   | are_equal |
+---------------------------------+-----------------------------------+-----------+
| SELECT * FROM `t` WHERE `a` = ? | SELECT * FROM `t` WHERE `a` = - ? |         0 |
+---------------------------------+-----------------------------------+-----------+
1 row in set (0.00 sec)

mysql>   SELECT
    ->     STATEMENT_DIGEST_TEXT('SELECT CASE WHEN a > 0 THEN 1 END FROM t') AS digest_positive,
    ->     STATEMENT_DIGEST_TEXT('SELECT CASE WHEN a > 0 THEN -1 END FROM t') AS digest_negative,
    ->     STATEMENT_DIGEST_TEXT('SELECT CASE WHEN a > 0 THEN 1 END FROM t') =
    ->     STATEMENT_DIGEST_TEXT('SELECT CASE WHEN a > 0 THEN -1 END FROM t') AS are_equal;
+----------------------------------------------+------------------------------------------------+-----------+
| digest_positive                              | digest_negative                                | are_equal |
+----------------------------------------------+------------------------------------------------+-----------+
| SELECT CASE WHEN `a` > ? THEN ? END FROM `t` | SELECT CASE WHEN `a` > ? THEN - ? END FROM `t` |         0 |
+----------------------------------------------+------------------------------------------------+-----------+
1 row in set (0.01 sec)

mysql>   SELECT
    ->     STATEMENT_DIGEST_TEXT('SELECT CASE WHEN a > 0 THEN 1 ELSE 2 END FROM t') AS digest_positive,
    ->     STATEMENT_DIGEST_TEXT('SELECT CASE WHEN a > 0 THEN -1 ELSE -2 END FROM t') AS digest_negative,
    ->     STATEMENT_DIGEST_TEXT('SELECT CASE WHEN a > 0 THEN 1 ELSE 2 END FROM t') =
    ->     STATEMENT_DIGEST_TEXT('SELECT CASE WHEN a > 0 THEN -1 ELSE -2 END FROM t') AS are_equal;
+-----------------------------------------------------+---------------------------------------------------------+-----------+
| digest_positive                                     | digest_negative                                         | are_equal |
+-----------------------------------------------------+---------------------------------------------------------+-----------+
| SELECT CASE WHEN `a` > ? THEN ? ELSE ? END FROM `t` | SELECT CASE WHEN `a` > ? THEN - ? ELSE - ? END FROM `t` |         0 |
+-----------------------------------------------------+---------------------------------------------------------+-----------+
1 row in set (0.00 sec)

Fixed Version:

mysql>   SELECT
    ->     STATEMENT_DIGEST_TEXT('SELECT * FROM t WHERE a = 1') AS digest_positive,
    ->     STATEMENT_DIGEST_TEXT('SELECT * FROM t WHERE a = -1') AS digest_negative,
    ->     STATEMENT_DIGEST_TEXT('SELECT * FROM t WHERE a = 1') =
    ->     STATEMENT_DIGEST_TEXT('SELECT * FROM t WHERE a = -1') AS are_equal;
+---------------------------------+---------------------------------+-----------+
| digest_positive                 | digest_negative                 | are_equal |
+---------------------------------+---------------------------------+-----------+
| SELECT * FROM `t` WHERE `a` = ? | SELECT * FROM `t` WHERE `a` = ? |         1 |
+---------------------------------+---------------------------------+-----------+
1 row in set (0.00 sec)

mysql>   SELECT
    ->     STATEMENT_DIGEST_TEXT('SELECT CASE WHEN a > 0 THEN 1 END FROM t') AS digest_positive,
    ->     STATEMENT_DIGEST_TEXT('SELECT CASE WHEN a > 0 THEN -1 END FROM t') AS digest_negative,
    ->     STATEMENT_DIGEST_TEXT('SELECT CASE WHEN a > 0 THEN 1 END FROM t') =
    ->     STATEMENT_DIGEST_TEXT('SELECT CASE WHEN a > 0 THEN -1 END FROM t') AS are_equal;
+----------------------------------------------+----------------------------------------------+-----------+
| digest_positive                              | digest_negative                              | are_equal |
+----------------------------------------------+----------------------------------------------+-----------+
| SELECT CASE WHEN `a` > ? THEN ? END FROM `t` | SELECT CASE WHEN `a` > ? THEN ? END FROM `t` |         1 |
+----------------------------------------------+----------------------------------------------+-----------+
1 row in set (0.00 sec)

mysql>   SELECT
    ->     STATEMENT_DIGEST_TEXT('SELECT CASE WHEN a > 0 THEN 1 ELSE 2 END FROM t') AS digest_positive,
    ->     STATEMENT_DIGEST_TEXT('SELECT CASE WHEN a > 0 THEN -1 ELSE -2 END FROM t') AS digest_negative,
    ->     STATEMENT_DIGEST_TEXT('SELECT CASE WHEN a > 0 THEN 1 ELSE 2 END FROM t') =
    ->     STATEMENT_DIGEST_TEXT('SELECT CASE WHEN a > 0 THEN -1 ELSE -2 END FROM t') AS are_equal;
+-----------------------------------------------------+-----------------------------------------------------+-----------+
| digest_positive                                     | digest_negative                                     | are_equal |
+-----------------------------------------------------+-----------------------------------------------------+-----------+
| SELECT CASE WHEN `a` > ? THEN ? ELSE ? END FROM `t` | SELECT CASE WHEN `a` > ? THEN ? ELSE ? END FROM `t` |         1 |
+-----------------------------------------------------+-----------------------------------------------------+-----------+
1 row in set (0.00 sec)

Note:
This bug was originally reported for WHERE clauses. During testing, I found the same issue with CASE statements, so the patch fixes both.

Solution:
The patch marks comparison operators (=, <=>, !=, <>, <, >, <=, >=) and CASE keywords (THEN, ELSE) as start_expr tokens in sql/gen_lex_token.cc. This ensures unary +/- are properly normalized.

Files Modified:
- sql/gen_lex_token.cc
- mysql-test/suite/perfschema/t/unary_digest.test
- mysql-test/suite/perfschema/r/unary_digest.result

Patch applies cleanly to MySQL 8.0, 8.4, and trunk. All tests pass.
Patch file attached.

Thank you,
 Yakir Gibraltar

Hi Team,

I’ve reviewed and tested the PR, and can confirm that it is a valid issue and the PR correctly addresses it. Please review the PR from your side as well and include the fix in an upcoming MySQL release, if possible.

Thanks for the contribution.