MySQL Bugs: #73504: performance_schema digests do not handle negative numbers well

Bug #73504	performance_schema digests do not handle negative numbers well
Submitted:	8 Aug 2014 0:51	Modified:	10 Oct 2014 21:18
Reporter:	Ben Krug	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Performance Schema	Severity:	S3 (Non-critical)
Version:	5.6	OS:	Any
Assigned to:	Marc ALFF	CPU Architecture:	Any

Description:
The way digests are generated for insert statements with negative numbers is broken. The '-' is not parsed out properly.  Eg

INSERT INTO digest_test VALUES (1,1,1);
INSERT INTO digest_test VALUES (-1,1,1);

will generate two digests, not one.  Changing the second or third value
to negative would generate separate digests as well.

How to repeat:
USE test
CREATE TABLE digest_test (num1 int(11) NOT NULL, num2 int(11) NOT NULL, num3 int(11) NOT NULL, num4 int(11) NOT NULL, num5 int(11) NOT NULL, PRIMARY KEY (`num1`));
INSERT INTO digest_test VALUES (1, 1, 1, 1, 1);
INSERT INTO digest_test VALUES (2, 2, 2, 2, 2), (3, 3, 3, 3, 3);
INSERT INTO digest_test VALUES (4, 4, 4, 4, 4), (5, 5, 5, 5, 5), (6, 6, 6, 6, 6);
-- at this point events_statements_summary_by_digest looks good
-- there is one digest for singleton inserts and one digest for bulk inserts
INSERT INTO digest_test VALUES (-7, 7, 7, 7, 7);
-- now another digest pops in for singleton
INSERT INTO digest_test VALUES (-8, 8, 8, 8, 8), (-9, 9, 9, 9, 9);
-- now another digest pops in for bulk inserts
-- and this is where you can get multiple digest showing up
INSERT INTO digest_test VALUES (10, -10, 10, 10, 10);
INSERT INTO digest_test VALUES (11, 11, -11, 11, 11);
INSERT INTO digest_test VALUES (12, 12, 12, -12, 12);
INSERT INTO digest_test VALUES (13, 13, 13, 13, -13);
INSERT INTO digest_test VALUES (-14, 1, 1, 1, 1), (15, 1, 1, 1, 1);
INSERT INTO digest_test VALUES (16, 1, 1, 1, 1), (-17, 1, 1, 1, 1);
INSERT INTO digest_test VALUES (18, 1, 1, 1, 1), (19, 1, 1, 1, 1), (-20, 1, 1, 1, 1);
INSERT INTO digest_test VALUES (21, 1, 1, 1, 1), (-22, 1, 1, 1, 1), (23, 1, 1, 1, 1); 

now

SELECT * FROM  events_statements_summary_by_digest\G

and see that any negative numbers caused new digest entries for each location they appeared in.

Suggested fix:
'-' should be stripped out just like spaces or numbers

Noted in 5.6.22, 5.7.6 changelogs.

For statement digest calculation, the Performance Schema failed to
recognize signed literal numbers as values representable by ? and
created multiple digests for statements that should have had the same
signature. Now all instances of unary plus and unary minus followed
by a number reduce to ? in digests.

$ bzr log -r 6159       
------------------------------------------------------------
revno: 6159
committer: Marc Alff <marc.alff@oracle.com>
branch nick: mysql-5.6-bug19389709
timestamp: Fri 2014-09-12 15:37:13 +0200
message:
  Bug#19389709 PERFORMANCE_SCHEMA DIGESTS DO NOT HANDLE NEGATIVE NUMBERS WELL
  
  Fix for MySQL 5.6
  
  Before this fix, query digests computed for the performance schema
  would fail to recognise literal values like "+1" or "-1" as values,
  and generate a digest like "+ ?" or "- ?" instead of just "?".
  
  This in turns caused failures to recognize list of values,
  creating many different digests for queries that should have the same
  signature.
  
  The root cause is that no recognition pattern was implemented
  for unary plus and unary minus operators on literals.
  
  This fix implements the following reduces:
  - <unary +> <NUM> as "?"
  - <unary -> <NUM> as "?"
  in the query digest computation.

$ bzr log -r 6160   
------------------------------------------------------------
revno: 6160
committer: Marc Alff <marc.alff@oracle.com>
branch nick: mysql-5.6-cleanup
timestamp: Sat 2014-09-13 09:42:09 +0200
message:
  Test cleanup