Bug #110045 Negative numbers on digests

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.

Posted by developer:
 
Added the following note to the MySQL Server 9.6.0 release notes:
		
Negative numbers on digests in WHERE were not parsed properly.