Bug #92049 bogus data when ordering results from variables_by_thread

Description:
Trying to get some connections variable values to debug a timeout issue,  and I'm seeing nonsensical results.

mysql> SELECT * FROM performance_schema.variables_by_thread WHERE VARIABLE_NAME in ('interactive_timeout','wait_timeout','net_read_timeout','net_write_timeout','connect_timeout') ORDER BY VARIABLE_NAME,THREAD_ID;
+-----------+----------------------------+----------------+
| THREAD_ID | VARIABLE_NAME              | VARIABLE_VALUE |
+-----------+----------------------------+----------------+
|        29 | insert_id                  | 0              |
|        29 | net_buffer_length          | 16384          |
|        29 | net_retry_count            | 10             |
|        29 | updatable_views_with_limit | YES            |
+-----------+----------------------------+----------------+
4 rows in set (0.00 sec)

It is returning rows I didn't ask for!

How to repeat:
SELECT * FROM performance_schema.variables_by_thread WHERE VARIABLE_NAME in ('interactive_timeout','wait_timeout','net_read_timeout','net_write_timeout','connect_timeout') ORDER BY VARIABLE_NAME,THREAD_ID;

SELECT * FROM performance_schema.variables_by_thread WHERE VARIABLE_NAME in ('interactive_timeout','wait_timeout','net_read_timeout','net_write_timeout','connect_timeout');

Hello Shane,

Thank you for the report and test case.
Verified as described with 5.7.23 build.

- Other GA versions checked
8.0.12 - not affected
5.6 - Not checked, variables_by_thread is implemented and available since 5.7.6+

- lowest version checked 5.7.9 - affected

 bin/mysql -uroot -S /tmp/mysql_ushastry.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.9 MySQL Community Server (GPL)

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SELECT * FROM performance_schema.variables_by_thread WHERE VARIABLE_NAME in ('interactive_timeout','wait_timeout','net_read_timeout','net_write_timeout','connect_timeout') ORDER BY VARIABLE_NAME,THREAD_ID;
+-----------+----------------------------+----------------+
| THREAD_ID | VARIABLE_NAME              | VARIABLE_VALUE |
+-----------+----------------------------+----------------+
|        28 | insert_id                  | 0              |
|        28 | net_buffer_length          | 16384          |
|        28 | net_retry_count            | 10             |
|        28 | updatable_views_with_limit | YES            |
+-----------+----------------------------+----------------+
4 rows in set (0.01 sec)

mysql> SELECT * FROM performance_schema.variables_by_thread WHERE VARIABLE_NAME in ('interactive_timeout','wait_timeout','net_read_timeout','net_write_timeout','connect_timeout');
+-----------+---------------------+----------------+
| THREAD_ID | VARIABLE_NAME       | VARIABLE_VALUE |
+-----------+---------------------+----------------+
|        28 | interactive_timeout | 28800          |
|        28 | net_read_timeout    | 30             |
|        28 | net_write_timeout   | 60             |
|        28 | wait_timeout        | 28800          |
+-----------+---------------------+----------------+
4 rows in set (0.00 sec)

Sincerely,
Umesh

Posted by developer:
 
Fixed in 5.7.25, 8.0.14.

Mishandling of deprecated system variables could cause output from
queries on the Performance Schema variables_by_thread table to be
incorrect.