ROWS_EXAMINED is added

This didn't make it into 10.2?

Id: 13
User: root
Host: localhost
db: NULL
Command: Query
Time: 0
State: init
Info: show processlist
Progress: 0.000

I am working on adding SENT_ROWS to information_schema.processlist in 10.3

monty, serg, while doing this I think changing the type to BIGINT per MDEV-22860 is called for.

Pull request on that issue.

The patch only adds fields to 'information_schema.processlist' as it easy for a user to decide which fields to select.

I did not change 'show processlist' as I did not want to change the default output (and with SHOW processlist there is no options in selecting just those fields one wants).

As of preview-11.3-preview b09e99ff, the status is as follows:

Must fix:

• MDEV-32441 (SENT_ROWS shows random wrong values when stored function is selected)

Good to have:

• MDEV-32281 (EXAMINED_ROWS is not populated in information_schema.processlist upon SELECT)
  While EXAMINED_ROWS wasn't in fact a part of the current patch and this is an old problem, it would be good time to fix it as we now declare it officially working;
• Add an MTR test which checks the actual value of SENT_ROWS, not just the presence of the column;

Uncertainties:

• SENT_ROWS apparently does not work for HANDLER, I assume it's expected.

After the changes are made, another quick round of testing will be performed.

MDEV-32441 fixed
MDEV-32281 fixed
SENT_ROWS and EXAMINED ROWS now works for HANDLER
Pushed to bb-11.3-monty

As of preview-11.3-preview de145915, the status is as follows:

Must fix:

• main.status2 does not work with ps-protocol (due to 2nd execution). It is apparently a test-only problem, the test needs to be fixed before pushing to main.

Uncertainties:
These observations should be looked at. They are not necessarily bugs at all, and even if they are, in my opinion they don't block pushing this feature. If it's confirmed they are bugs but not critical, I can file separate reports for them. Some of them weren't observed in the previous round because EXAMINED_ROWS didn't work, but now it is fixed, so more subtle issues came up.

• One of comments above says that I_S.EXAMINED_ROWS and I_S.SENT_ROWS in information schema should have been increased to BIGINT. It didn't happen.
• It seems that for queries served from the query cache, I_S.SENT_ROWS is not populated. I'm not 100% sure it is true, but at least I couldn't catch it being populated. Rows_sent in the status however is set.
• For INSERT ... SELECT the value of I_S.EXAMINED_ROWS is double the value of Rows_read in the status.
• For stored procedures I_S.EXAMINED_ROWS and I_S.SENT_ROWS show cumulative values. E.g. if a stored procedure consists of two selects, each of which examines and sends 5 rows, by the end of the second select the values are 10. It would be natural if the processlist was showing CALL as the statement which is being executed, but since it shows the actual SELECT (not the procedure call), it is confusing.
• For GROUP BY and similar queries I_S.EXAMINED_ROWS differs (by 2-3 times) from Rows_read in the status.

• Rows_sent in the status is not populated upon INSERT .. RETURNING / DELETE .. RETURNING (but I_S.SENT_ROWS is). It is a side observation as the status values weren't a part of this MDEV.

If no changes are made other than the test fix, then the feature can be merged into main without another round of testing. If any of the issues listed in the second part are addressed, then I will need to look at it again.

As of bb-11.3-monty 71c9f29a:

Must fix:

• funcs_1.is_columns_is_embedded fails, the result or rdiff needs to be re-recorded. It's a side-effect of the latest fixes (INT => BIGINT).

After the test is fixed, I have no objections against pushing the code into 11.3 and releasing with 11.3.1. I don't know however whether the review of the latest version has been done as it was intended (or maybe it was decided that it's not needed after all).

For the record:
From the notes in the previous comment,

• the data type of EXAMINED_ROWS and SENT_ROWS has been changed to BIGINT;
• SENT_ROWS not populated for queries from query cache confirmed to be by design;
• INSERT ... SELECT (from/into the same table) ending up with EXAMINED_ROWS twice the value of Rows_read confirmed to be by design;
• Cumulative values of EXAMINED_ROWS and SENT_ROWS upon execution of stored procedures changed to per-statement values;
• EXAMINED_ROWS being different from Rows_read for GROUP BY queries and such confirmed to be by design;
• Rows_sent upon INSERT / DELETE .. RETURNING has been fixed.