When using the INFORMATION_SCHEMA.PROCESSLIST table in a subquery in an INSERT or UPDATE statement the statement will fail with:

ERROR 1264 (22003): Out of range value for column 'EXAMINED_ROWS' at row 1

if there's a running query in another session that exceeds the EXAMINED_ROWS value range (see also MDEV-22860) and sql_mode=STRICT_TRANS_TABLE is set, otherwise it will just cause a warning.

Just querying the PROCESSLIST table works fine, e.g.:

> select * from processlist where examined_rows > 0\G

*************************** 1. row ***************************

             ID: 12

           USER: root

           HOST: localhost

             DB: test

        COMMAND: Query

           TIME: 265

          STATE: Updating

           INFO: update t2 set id=1 where id=23

        TIME_MS: 265257.998

          STAGE: 0

      MAX_STAGE: 0

       PROGRESS: 0.000

    MEMORY_USED: 77944

MAX_MEMORY_USED: 2211464

  EXAMINED_ROWS: 2147483647

       QUERY_ID: 120

    INFO_BINARY: update t2 set id=1 where id=23

            TID: 5414

1 row in set (0.002 sec)

Note that EXAMINED_ROWS is already at the max. value of 2147483647 here.

This query does not even return a truncation warning, even with

sql_mode=STRICT_TRANS_TABLE,STRICT_ALL_TABLES

is in effect.

Using the PROCESSLIST table in a subquery in an outer SELECT also doesnt't cause any troubles at all, no errors no warnigs. Note that this query does not even use the EXAMINED_ROWS column, and due to the WHERE condition only the row for the current session is selected anyway, not the one of the other session exceeding the row column:

SELECT (SELECT HOST FROM information_schema.processlist WHERE ID=CONNECTION_ID()) as foo;

But when using this in an INSERT, it fails:

> INSERT INTO test.t3 (machine_name) values ( (SELECT HOST FROM information_schema.processlist WHERE ID=CONNECTION_ID()));

ERROR 1264 (22003): Out of range value for column 'EXAMINED_ROWS' at row 1

> INSERT INTO test.t3 SET machine_name = ( (SELECT HOST FROM information_schema.processlist WHERE ID=CONNECTION_ID()));

ERROR 1264 (22003): Out of range value for column 'EXAMINED_ROWS' at row 1

Direct INSERT...SELECT works, but when adding a subquery it fails again:

> INSERT INTO test.t3 SELECT HOST FROM information_schema.processlist WHERE ID=CONNECTION_ID();

Query OK, 1 row affected (0.007 sec)

Records: 1  Duplicates: 0  Warnings: 0

> INSERT INTO test.t3 SELECT (SELECT HOST FROM information_schema.processlist WHERE ID=CONNECTION_ID()) as foo;

ERROR 1264 (22003): Out of range value for column 'EXAMINED_ROWS' at row 1

Changing EXAMINED_ROWS from INT to BIGINT UNSIGNED will probably fix this without any additional code changes, as a DML query having to scan more than 2^64 is far less likely (nobody is probably going to wait for that to complete anyway?), but this should probably still be fixed anyway as there may by other situations where values exceed the I_S column types used for them ...