Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-22861

INSERT with subquery on I_S.PROCESSLIST fails when EXAMINED_ROWS value range is exceeded

    XMLWordPrintable

    Details

      Description

      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 ...

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              sanja Oleksandr Byelkin
              Reporter:
              hholzgra Hartmut Holzgraefe
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

                Dates

                Created:
                Updated: