Bug #74546 PERFORMANCE SCHEMA QUERY ON EVENTS_STATEMENTS_CURRENT WRONG WITH ORDER BY

• Submitted: 24 Oct 2014 11:31
• Modified: 24 Oct 2014 11:43
• Reporter: Mayank Prasad
• Status: Closed
• Category: MySQL Server: Performance Schema
• Severity: S3 (Non-critical)
• OS: Any
• Assigned to: Mayank Prasad
• CPU Architecture: Any

[24 Oct 2014 11:31] Mayank Prasad

Description:
When querying the events_statements_current table with an ORDER BY clause and
the result set includes more than one row, the first row is repeated for all
rows. Without the ORDER BY, the query returns the expected result.

How to repeat:
1) Create a stored procedure:

DELIMITER //
DROP PROCEDURE IF EXISTS myproc//
CREATE PROCEDURE myproc(IN in_sleep INT UNSIGNED)
BEGIN
   WHILE in_sleep > 0 DO
      SELECT CONCAT('Sleeping for ', in_sleep, ' seconds.') AS 'Info';
      DO SLEEP(in_sleep);
      SET in_sleep = in_sleep - 1;
   END WHILE;
END//
DELIMITER ;

2) Create two connections to the database.
     A) One that calls the procedure above, e.g.:
           CALL proctest.myproc(5);
     B) One that queries the events_statements_current table.

3) While the procedure is executing in the connection from B) execute:

     SELECT EVENT_ID, OBJECT_TYPE, SQL_TEXT FROM events_statements_current
WHERE THREAD_ID = 21 ORDER BY EVENT_ID;

(replace 21 with the THREAD_ID for the connection from 2A).

This gives:

mysql> SELECT EVENT_ID, OBJECT_TYPE, SQL_TEXT FROM events_statements_current
WHERE THREAD_ID = 21 ORDER BY EVENT_ID;
+----------+-------------+-------------------------+
| EVENT_ID | OBJECT_TYPE | SQL_TEXT                |
+----------+-------------+-------------------------+
|      289 | NULL        | CALL proctest.myproc(5) |
|      289 | NULL        | CALL proctest.myproc(5) |
+----------+-------------+-------------------------+
2 rows in set (0.00 sec)

But should give:

mysql> SELECT EVENT_ID, OBJECT_TYPE, SQL_TEXT FROM events_statements_current
WHERE THREAD_ID = 21;
+----------+-------------+-------------------------+
| EVENT_ID | OBJECT_TYPE | SQL_TEXT                |
+----------+-------------+-------------------------+
|      289 | NULL        | CALL proctest.myproc(5) |
|      297 | PROCEDURE   | DO SLEEP(in_sleep)      |
+----------+-------------+-------------------------+
2 rows in set (0.00 sec)

Suggested fix:
N/A