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

Complex query in Store procedure corrupts results

Details

    Description

      When running the complex query repeatedly in the stored procedure random garbage or empty results are returned on occasion.

      Data dump with SP and test are contained in attachment. With the SP rewritten to store the output in a table the results that are correct and incorrect are as follows:

      +-------------------+--------------+-----------------+------------+----------------------+-------------------------+-----------------------+--------------+-----------------+--------+----------+----------------+---------------------+
      		 
      | customerReference | policyNumber | schemeReference | schemeName | applicationReference | modifiedCreatedDateTime | demandCreatedDateTime | countDemands | genericDemandId | status | isUrgent | reactivateDate | isBusinessException |
      		 
      +-------------------+--------------+-----------------+------------+----------------------+-------------------------+-----------------------+--------------+-----------------+--------+----------+----------------+---------------------+
      		 
      | reference1        |            1 | NULL            | NULL       | NULL                 | 2020-01-01 10:10:10     | 2020-01-01 10:10:10   |            1 |               1 |      1 |        0 | NULL           |                   0 |
      		 
      +-------------------+--------------+-----------------+------------+----------------------+-------------------------+-----------------------+--------------+-----------------+--------+----------+----------------+---------------------+

      Under high concurrency (should be a static result) from 10000 iterations the following rows were inserted:

      mysql ds2 -e "select * from results where customerReference<>'reference1'" 
      +--------------------------------+--------------+-----------------+------------+----------------------------------------------------+-------------------------+-----------------------+--------------+-----------------+--------+----------+---------------------+---------------------+
      		 
      | customerReference              | policyNumber | schemeReference | schemeName | applicationReference                               | modifiedCreatedDateTime | demandCreatedDateTime | countDemands | genericDemandId | status | isUrgent | reactivateDate      | isBusinessException |
      		 
      +--------------------------------+--------------+-----------------+------------+----------------------------------------------------+-------------------------+-----------------------+--------------+-----------------+--------+----------+---------------------+---------------------+
      		 
      | w  (       ??      0??K    |            0 | NULL            | NULL       | NULL                                               | 2020-01-01 10:10:10     | NULL                  |   2147483647 |            NULL |   NULL |        0 | NULL                |                NULL |
      		 
      |                                |            0 |                 |            |                                                    | 2020-01-01 10:10:10     | 2020-01-01 10:10:10   |            1 |               1 |      1 |        0 | 0000-00-00 00:00:00 |                   0 |
      		 
      | w  rence1                     |            1 |                 |            |                                                    | 2020-01-01 10:10:10     | 2020-01-01 10:10:10   |            1 |               1 |      1 |        0 | NULL                |                NULL |
      		 
      | w  rence1                     |            1 |                 |            |                                                    | 2020-01-01 10:10:10     | 2020-01-01 10:10:10   |            1 |            NULL |   NULL |        0 | 0000-00-00 00:00:00 |                NULL |
      		 
      |                                |            1 |                 |            |                                                    | 2020-01-01 10:10:10     | 2020-01-01 10:10:10   |            1 |               1 |      1 |        0 | 0000-00-00 00:00:00 |                   0 |
      		 
      | w  rence1                     |            1 |                 | NULL       | NULL                                               | 2020-01-01 10:10:10     | 2020-01-01 10:10:10   |            1 |            NULL |      1 |     NULL | NULL                |                NULL |
      		 
      |                                |            1 |                 |            |                                                    | 2020-01-01 10:10:10     | 2020-01-01 10:10:10   |            1 |               1 |      1 |        0 | 0000-00-00 00:00:00 |                   0 |
      		 
      | w  rence1                     |            1 |                 |            | NULL                                               | NULL                    | 2020-01-01 10:10:10   |            1 |            NULL |   NULL |     NULL | 0000-00-00 00:00:00 |                   0 |
      		 
      |                                |            1 |                 |            |                                                    | 2020-01-01 10:10:10     | 2020-01-01 10:10:10   |            1 |               1 |      1 |        0 | 0000-00-00 00:00:00 |                   0 |
      		 
      | w  rence1                     |            1 |                 | NULL       |                                                    | 2020-01-01 10:10:10     | 2020-01-01 10:10:10   |            1 |               1 |      1 |        0 | NULL                |                NULL |
      		 
      |                                |            0 |                 |            |                                                    | 2020-01-01 10:10:10     | 2020-01-01 10:10:10   |            1 |               1 |      1 |        0 | 0000-00-00 00:00:00 |                   0 |
      		 
      | w  rence1                     |            1 |                 | NULL       |                                                    | NULL                    | NULL                  |            1 |               1 |      1 |        0 | NULL                |                NULL |
      		 
      |                                |            1 |                 |            |                                                    | 2020-01-01 10:10:10     | 2020-01-01 10:10:10   |            1 |               1 |      1 |        0 | 0000-00-00 00:00:00 |                   0 |
      		 
      | w  (       ??      0??K    |            0 | NULL            | NULL       |                                                    | 2020-01-01 10:10:10     | NULL                  |   2147483647 |               0 |      1 |        0 | 7036-87-44 17:76:64 |                NULL |
      		 
      | w  rence1                     |            1 |                 |            | NULL                                               | 2020-01-01 10:10:10     | 2020-01-01 10:10:10   |            1 |               1 |   NULL |     NULL | 0000-00-00 00:00:00 |                   0 |
      		 
      |                                |            1 |                 |            |                                                    | 2020-01-01 10:10:10     | 2020-01-01 10:10:10   |            1 |               1 |      1 |        0 | 0000-00-00 00:00:00 |                   0 |
      		 
      |                                |            1 |                 |            |                                                    | 2020-01-01 10:10:10     | 2020-01-01 10:10:10   |            1 |               1 |      1 |        0 | 0000-00-00 00:00:00 |                   0 |
      		 
      |                                |            0 |                 |            |                                                    | 2020-01-01 10:10:10     | 2020-01-01 10:10:10   |            1 |               1 |      1 |        0 | 0000-00-00 00:00:00 |                   0 |
      		 
      |                                |            0 |                 |            |                                                    | 2020-01-01 10:10:10     | 2020-01-01 10:10:10   |            1 |               1 |      1 |        0 | 0000-00-00 00:00:00 |                   0 |
      		 
      |                                |            0 |                 |            |                                                    | 2020-01-01 10:10:10     | 2020-01-01 10:10:10   |   2147483647 |               0 |      1 |        0 | 0000-00-00 00:00:00 |                   0 |
      		 
      |                                |            0 |                 |            | ?K                                               | 2020-01-01 10:10:10     | 2020-01-01 10:10:10   |            1 |               1 |      1 |        0 | 0000-00-00 00:00:00 |                   0 |
      		 
      |                                |            1 |                 |            |                                                    | 2020-01-01 10:10:10     | 2020-01-01 10:10:10   |            1 |               1 |      1 |        0 | 0000-00-00 00:00:00 |                   0 |
      		 
      |                                |            1 |                 |            |                                                    | 2020-01-01 10:10:10     | 2020-01-01 10:10:10   |            1 |               1 |      1 |        0 | 0000-00-00 00:00:00 |                   0 |
      		 
      |                                |            1 |                 |            |                                                    | 2020-01-01 10:10:10     | 2020-01-01 10:10:10   |            1 |               1 |      1 |        0 | 0000-00-00 00:00:00 |                   0 |
      		 
      |                                |            1 |                 |            |                                                    | 2020-01-01 10:10:10     | 2020-01-01 10:10:10   |            1 |               1 |      1 |        0 | 0000-00-00 00:00:00 |                   0 |
      		 
      |                                |            1 |                 |            |                                                    | 2020-01-01 10:10:10     | 2020-01-01 10:10:10   |            1 |               1 |      1 |        0 | 0000-00-00 00:00:00 |                   0 |
      		 
      |                                |            1 |                 |            |                                                    | 2020-01-01 10:10:10     | 2020-01-01 10:10:10   |            1 |               1 |      1 |        0 | 0000-00-00 00:00:00 |                   0 |
      		 
      +--------------------------------+--------------+-----------------+------------+----------------------------------------------------+-------------------------+-----------------------+--------------+-----------------+--------+----------+---------------------+---------------------+

      Attachments

        Issue Links

          is duplicated by

          Bug - A problem which impairs or prevents the functions of the product. MDEV-23130 Server crashes on 2nd execution of sp

          • Major - Major loss of function.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            kjoiner Kyle Joiner (Inactive) created issue -
            kjoiner Kyle Joiner (Inactive) made changes -
            Field Original Value New Value
            Description When running the complex query repeatedly in the stored procedure random garbage or empty results are returned on occasion.

            Data dump with SP and test are contained in attachment. With the SP rewritten to store the output in a table the results that are correct and incorrect are as follows:

            +-------------------+--------------+-----------------+------------+----------------------+-------------------------+-----------------------+--------------+-----------------+--------+----------+----------------+---------------------+
            | customerReference | policyNumber | schemeReference | schemeName | applicationReference | modifiedCreatedDateTime | demandCreatedDateTime | countDemands | genericDemandId | status | isUrgent | reactivateDate | isBusinessException |
            +-------------------+--------------+-----------------+------------+----------------------+-------------------------+-----------------------+--------------+-----------------+--------+----------+----------------+---------------------+
            | reference1 | 1 | NULL | NULL | NULL | 2020-01-01 10:10:10 | 2020-01-01 10:10:10 | 1 | 1 | 1 | 0 | NULL | 0 |
            +-------------------+--------------+-----------------+------------+----------------------+-------------------------+-----------------------+--------------+-----------------+--------+----------+----------------+---------------------+

            Under high concurrency (should be a static result) from 1000 iterations the following rows were inserted:
            mysql ds2 -e "select * from results where customerReference<>'reference1'"
            +--------------------------------+--------------+-----------------+------------+----------------------------------------------------+-------------------------+-----------------------+--------------+-----------------+--------+----------+---------------------+---------------------+
            | customerReference | policyNumber | schemeReference | schemeName | applicationReference | modifiedCreatedDateTime | demandCreatedDateTime | countDemands | genericDemandId | status | isUrgent | reactivateDate | isBusinessException |
            +--------------------------------+--------------+-----------------+------------+----------------------------------------------------+-------------------------+-----------------------+--------------+-----------------+--------+----------+---------------------+---------------------+
            | w ( ?? 0??K | 0 | NULL | NULL | NULL | 2020-01-01 10:10:10 | NULL | 2147483647 | NULL | NULL | 0 | NULL | NULL |
            | | 0 | | | | 2020-01-01 10:10:10 | 2020-01-01 10:10:10 | 1 | 1 | 1 | 0 | 0000-00-00 00:00:00 | 0 |
            | w rence1 | 1 | | | | 2020-01-01 10:10:10 | 2020-01-01 10:10:10 | 1 | 1 | 1 | 0 | NULL | NULL |
            | w rence1 | 1 | | | | 2020-01-01 10:10:10 | 2020-01-01 10:10:10 | 1 | NULL | NULL | 0 | 0000-00-00 00:00:00 | NULL |
            | | 1 | | | | 2020-01-01 10:10:10 | 2020-01-01 10:10:10 | 1 | 1 | 1 | 0 | 0000-00-00 00:00:00 | 0 |
            | w rence1 | 1 | | NULL | NULL | 2020-01-01 10:10:10 | 2020-01-01 10:10:10 | 1 | NULL | 1 | NULL | NULL | NULL |
            | | 1 | | | | 2020-01-01 10:10:10 | 2020-01-01 10:10:10 | 1 | 1 | 1 | 0 | 0000-00-00 00:00:00 | 0 |
            | w rence1 | 1 | | | NULL | NULL | 2020-01-01 10:10:10 | 1 | NULL | NULL | NULL | 0000-00-00 00:00:00 | 0 |
            | | 1 | | | | 2020-01-01 10:10:10 | 2020-01-01 10:10:10 | 1 | 1 | 1 | 0 | 0000-00-00 00:00:00 | 0 |
            | w rence1 | 1 | | NULL | | 2020-01-01 10:10:10 | 2020-01-01 10:10:10 | 1 | 1 | 1 | 0 | NULL | NULL |
            | | 0 | | | | 2020-01-01 10:10:10 | 2020-01-01 10:10:10 | 1 | 1 | 1 | 0 | 0000-00-00 00:00:00 | 0 |
            | w rence1 | 1 | | NULL | | NULL | NULL | 1 | 1 | 1 | 0 | NULL | NULL |
            | | 1 | | | | 2020-01-01 10:10:10 | 2020-01-01 10:10:10 | 1 | 1 | 1 | 0 | 0000-00-00 00:00:00 | 0 |
            | w ( ?? 0??K | 0 | NULL | NULL | | 2020-01-01 10:10:10 | NULL | 2147483647 | 0 | 1 | 0 | 7036-87-44 17:76:64 | NULL |
            | w rence1 | 1 | | | NULL | 2020-01-01 10:10:10 | 2020-01-01 10:10:10 | 1 | 1 | NULL | NULL | 0000-00-00 00:00:00 | 0 |
            | | 1 | | | | 2020-01-01 10:10:10 | 2020-01-01 10:10:10 | 1 | 1 | 1 | 0 | 0000-00-00 00:00:00 | 0 |
            | | 1 | | | | 2020-01-01 10:10:10 | 2020-01-01 10:10:10 | 1 | 1 | 1 | 0 | 0000-00-00 00:00:00 | 0 |
            | | 0 | | | | 2020-01-01 10:10:10 | 2020-01-01 10:10:10 | 1 | 1 | 1 | 0 | 0000-00-00 00:00:00 | 0 |
            | | 0 | | | | 2020-01-01 10:10:10 | 2020-01-01 10:10:10 | 1 | 1 | 1 | 0 | 0000-00-00 00:00:00 | 0 |
            | | 0 | | | | 2020-01-01 10:10:10 | 2020-01-01 10:10:10 | 2147483647 | 0 | 1 | 0 | 0000-00-00 00:00:00 | 0 |
            | | 0 | | | ?K | 2020-01-01 10:10:10 | 2020-01-01 10:10:10 | 1 | 1 | 1 | 0 | 0000-00-00 00:00:00 | 0 |
            | | 1 | | | | 2020-01-01 10:10:10 | 2020-01-01 10:10:10 | 1 | 1 | 1 | 0 | 0000-00-00 00:00:00 | 0 |
            | | 1 | | | | 2020-01-01 10:10:10 | 2020-01-01 10:10:10 | 1 | 1 | 1 | 0 | 0000-00-00 00:00:00 | 0 |
            | | 1 | | | | 2020-01-01 10:10:10 | 2020-01-01 10:10:10 | 1 | 1 | 1 | 0 | 0000-00-00 00:00:00 | 0 |
            | | 1 | | | | 2020-01-01 10:10:10 | 2020-01-01 10:10:10 | 1 | 1 | 1 | 0 | 0000-00-00 00:00:00 | 0 |
            | | 1 | | | | 2020-01-01 10:10:10 | 2020-01-01 10:10:10 | 1 | 1 | 1 | 0 | 0000-00-00 00:00:00 | 0 |
            | | 1 | | | | 2020-01-01 10:10:10 | 2020-01-01 10:10:10 | 1 | 1 | 1 | 0 | 0000-00-00 00:00:00 | 0 |
            +--------------------------------+--------------+-----------------+------------+----------------------------------------------------+-------------------------+-----------------------+--------------+-----------------+--------+----------+---------------------+---------------------+

            		When running the complex query repeatedly in the stored procedure random garbage or empty results are returned on occasion.

            Data dump with SP and test are contained in attachment. With the SP rewritten to store the output in a table the results that are correct and incorrect are as follows:

            +-------------------+--------------+-----------------+------------+----------------------+-------------------------+-----------------------+--------------+-----------------+--------+----------+----------------+---------------------+
            | customerReference | policyNumber | schemeReference | schemeName | applicationReference | modifiedCreatedDateTime | demandCreatedDateTime | countDemands | genericDemandId | status | isUrgent | reactivateDate | isBusinessException |
            +-------------------+--------------+-----------------+------------+----------------------+-------------------------+-----------------------+--------------+-----------------+--------+----------+----------------+---------------------+
            | reference1 | 1 | NULL | NULL | NULL | 2020-01-01 10:10:10 | 2020-01-01 10:10:10 | 1 | 1 | 1 | 0 | NULL | 0 |
            +-------------------+--------------+-----------------+------------+----------------------+-------------------------+-----------------------+--------------+-----------------+--------+----------+----------------+---------------------+

            Under high concurrency (should be a static result) from 10000 iterations the following rows were inserted:
            mysql ds2 -e "select * from results where customerReference<>'reference1'"
            +--------------------------------+--------------+-----------------+------------+----------------------------------------------------+-------------------------+-----------------------+--------------+-----------------+--------+----------+---------------------+---------------------+
            | customerReference | policyNumber | schemeReference | schemeName | applicationReference | modifiedCreatedDateTime | demandCreatedDateTime | countDemands | genericDemandId | status | isUrgent | reactivateDate | isBusinessException |
            +--------------------------------+--------------+-----------------+------------+----------------------------------------------------+-------------------------+-----------------------+--------------+-----------------+--------+----------+---------------------+---------------------+
            | w ( ?? 0??K | 0 | NULL | NULL | NULL | 2020-01-01 10:10:10 | NULL | 2147483647 | NULL | NULL | 0 | NULL | NULL |
            | | 0 | | | | 2020-01-01 10:10:10 | 2020-01-01 10:10:10 | 1 | 1 | 1 | 0 | 0000-00-00 00:00:00 | 0 |
            | w rence1 | 1 | | | | 2020-01-01 10:10:10 | 2020-01-01 10:10:10 | 1 | 1 | 1 | 0 | NULL | NULL |
            | w rence1 | 1 | | | | 2020-01-01 10:10:10 | 2020-01-01 10:10:10 | 1 | NULL | NULL | 0 | 0000-00-00 00:00:00 | NULL |
            | | 1 | | | | 2020-01-01 10:10:10 | 2020-01-01 10:10:10 | 1 | 1 | 1 | 0 | 0000-00-00 00:00:00 | 0 |
            | w rence1 | 1 | | NULL | NULL | 2020-01-01 10:10:10 | 2020-01-01 10:10:10 | 1 | NULL | 1 | NULL | NULL | NULL |
            | | 1 | | | | 2020-01-01 10:10:10 | 2020-01-01 10:10:10 | 1 | 1 | 1 | 0 | 0000-00-00 00:00:00 | 0 |
            | w rence1 | 1 | | | NULL | NULL | 2020-01-01 10:10:10 | 1 | NULL | NULL | NULL | 0000-00-00 00:00:00 | 0 |
            | | 1 | | | | 2020-01-01 10:10:10 | 2020-01-01 10:10:10 | 1 | 1 | 1 | 0 | 0000-00-00 00:00:00 | 0 |
            | w rence1 | 1 | | NULL | | 2020-01-01 10:10:10 | 2020-01-01 10:10:10 | 1 | 1 | 1 | 0 | NULL | NULL |
            | | 0 | | | | 2020-01-01 10:10:10 | 2020-01-01 10:10:10 | 1 | 1 | 1 | 0 | 0000-00-00 00:00:00 | 0 |
            | w rence1 | 1 | | NULL | | NULL | NULL | 1 | 1 | 1 | 0 | NULL | NULL |
            | | 1 | | | | 2020-01-01 10:10:10 | 2020-01-01 10:10:10 | 1 | 1 | 1 | 0 | 0000-00-00 00:00:00 | 0 |
            | w ( ?? 0??K | 0 | NULL | NULL | | 2020-01-01 10:10:10 | NULL | 2147483647 | 0 | 1 | 0 | 7036-87-44 17:76:64 | NULL |
            | w rence1 | 1 | | | NULL | 2020-01-01 10:10:10 | 2020-01-01 10:10:10 | 1 | 1 | NULL | NULL | 0000-00-00 00:00:00 | 0 |
            | | 1 | | | | 2020-01-01 10:10:10 | 2020-01-01 10:10:10 | 1 | 1 | 1 | 0 | 0000-00-00 00:00:00 | 0 |
            | | 1 | | | | 2020-01-01 10:10:10 | 2020-01-01 10:10:10 | 1 | 1 | 1 | 0 | 0000-00-00 00:00:00 | 0 |
            | | 0 | | | | 2020-01-01 10:10:10 | 2020-01-01 10:10:10 | 1 | 1 | 1 | 0 | 0000-00-00 00:00:00 | 0 |
            | | 0 | | | | 2020-01-01 10:10:10 | 2020-01-01 10:10:10 | 1 | 1 | 1 | 0 | 0000-00-00 00:00:00 | 0 |
            | | 0 | | | | 2020-01-01 10:10:10 | 2020-01-01 10:10:10 | 2147483647 | 0 | 1 | 0 | 0000-00-00 00:00:00 | 0 |
            | | 0 | | | ?K | 2020-01-01 10:10:10 | 2020-01-01 10:10:10 | 1 | 1 | 1 | 0 | 0000-00-00 00:00:00 | 0 |
            | | 1 | | | | 2020-01-01 10:10:10 | 2020-01-01 10:10:10 | 1 | 1 | 1 | 0 | 0000-00-00 00:00:00 | 0 |
            | | 1 | | | | 2020-01-01 10:10:10 | 2020-01-01 10:10:10 | 1 | 1 | 1 | 0 | 0000-00-00 00:00:00 | 0 |
            | | 1 | | | | 2020-01-01 10:10:10 | 2020-01-01 10:10:10 | 1 | 1 | 1 | 0 | 0000-00-00 00:00:00 | 0 |
            | | 1 | | | | 2020-01-01 10:10:10 | 2020-01-01 10:10:10 | 1 | 1 | 1 | 0 | 0000-00-00 00:00:00 | 0 |
            | | 1 | | | | 2020-01-01 10:10:10 | 2020-01-01 10:10:10 | 1 | 1 | 1 | 0 | 0000-00-00 00:00:00 | 0 |
            | | 1 | | | | 2020-01-01 10:10:10 | 2020-01-01 10:10:10 | 1 | 1 | 1 | 0 | 0000-00-00 00:00:00 | 0 |
            +--------------------------------+--------------+-----------------+------------+----------------------------------------------------+-------------------------+-----------------------+--------------+-----------------+--------+----------+---------------------+---------------------+


            alice Alice Sherepa made changes -
            Affects Version/s 10.3 [ 22126 ]
            Affects Version/s 10.4 [ 22408 ]
            Affects Version/s 10.5 [ 23123 ]
            alice Alice Sherepa made changes -
            Fix Version/s 10.3 [ 22126 ]
            Fix Version/s 10.4 [ 22408 ]
            Fix Version/s 10.5 [ 23123 ]
            alice Alice Sherepa added a comment -

            test to reproduce the issue, but not simplified, problem after the 2.nd execution of the stored procedure:

            --source include/have_innodb.inc
            		 
             
            		 
            create table t1 ( id int not null , customerreference varchar(255), primary key (id)) engine=innodb;
            		 
            insert into t1 values (1,'reference1'),(2,'reference2'),(3,'reference3');
            		 
             
            		 
             
            		 
            create table t2 (
            		 
              schemeid int not null ,
            		 
              schemereference varchar(8),
            		 
              schemename varchar(255),
            		 
              isnewbusiness tinyint(1),
            		 
              schemetolerance int,
            		 
              isdetailsretained tinyint(1),
            		 
              issubjecttowarningmessage tinyint(1),
            		 
              istrust tinyint(1),
            		 
              isbpa tinyint(1),
            		 
              primary key (schemeid),
            		 
              key ix1_t2 (schemereference) using btree
            		 
            ) engine=innodb;
            		 
             
            		 
            insert into t2 values (1,'ref1','schemename1',0,1,0,0,0,0),(2,'ref2','schemename2',0,2,0,0,0,0),(3,'ref3','schemename3',0,3,0,0,0,0);
            		 
             
            		 
            create table t3 (
            		 
              id int not null ,
            		 
              policynumber varchar(255),
            		 
              customerreference varchar(255),
            		 
              demandtypeid int,
            		 
              demandid int,
            		 
              initiator varchar(255),
            		 
              startdatetime datetime,
            		 
              enddatetime datetime,
            		 
              isactive tinyint(1),
            		 
              demandcreateddatetime datetime,
            		 
              demandenddatetime datetime,
            		 
              status int,
            		 
              documentuniquereference varchar(255),
            		 
              isurgent bit(1),
            		 
              modifiedcreateddatetime datetime,
            		 
              demandsource varchar(255),
            		 
              genericdemandid int,
            		 
              schemereference varchar(8),
            		 
              categoryreference varchar(8),
            		 
              aggregationkey varchar(255),
            		 
              contractsystem varchar(255),
            		 
              productgroup varchar(255),
            		 
              isbusinessexception tinyint(1),
            		 
              applicationreference varchar(25),
            		 
              stageid int default 1,
            		 
              isstuckdemand tinyint(1) default 0,
            		 
              sla int,
            		 
              slaenddatetime datetime,
            		 
              identifier varchar(100),
            		 
              primary key (id)) engine=innodb;
            		 
             
            		 
             
            		 
            insert into t3 values (1,'1','reference1',1,1,'initiator1','2020-01-01 10:10:10','2020-01-01 10:10:10',0,'2020-01-01 10:10:10','2020-01-01 10:10:10',1,'documentuniquereference1','\0','2020-01-01 10:10:10','demandsource1',1,'scheme1','cat1','aggregationkey1','contractsystem1','productgroup1',0,'appref1',1,0,1,'2020-01-01 10:10:10','identifier1'),(2,'2','reference2',2,2,'initiator2','2020-01-01 10:10:10','2020-01-01 10:10:10',0,'2020-01-01 10:10:10','2020-01-01 10:10:10',2,'documentuniquereference2','\0','2020-01-01 10:10:10','demandsource2',2,'scheme2','cat2','aggregationkey2','contractsystem2','productgroup2',0,'appref2',2,0,2,'2020-01-01 10:10:10','identifier2'),(3,'3','reference3',3,3,'initiator3','2020-01-01 10:10:10','2020-01-01 10:10:10',0,'2020-01-01 10:10:10','2020-01-01 10:10:10',3,'documentuniquereference3','\0','2020-01-01 10:10:10','demandsource3',3,'scheme3','cat3','aggregationkey3','contractsystem3','productgroup3',0,'appref3',3,0,3,'2020-01-01 10:10:10','identifier3');
            		 
             
            		 
             
            		 
            create table t4 (
            		 
              id int not null ,
            		 
              demandtypeid int,
            		 
              demandid int,
            		 
              pendreasonid int,
            		 
              pendedby varchar(50),
            		 
              comments varchar(500),
            		 
              ispend tinyint(1),
            		 
              pendeddatetime datetime,
            		 
              reactivatedate datetime,
            		 
              autopended tinyint(1),
            		 
              ismanualreactivated tinyint(1),
            		 
              genericdemandid int,
            		 
              primary key (id),
            		 
              key ix1_t4 (genericdemandid,ispend,reactivatedate) using btree
            		 
            ) engine=innodb;
            		 
             
            		 
             
            		 
            insert into t4 values (1,2,2,2,'example','comment',1,'2020-01-01 10:10:10','2020-01-01 10:10:10',0,0,1),(2,1,1,1,'me','comment2',1,'2020-01-01 10:10:10','2020-01-01 10:10:10',0,0,2),(3,2,3,4,'you','comment3',1,'2020-01-01 10:10:10','2020-01-01 10:10:10',0,0,2);
            		 
             
            		 
             
            		 
            create table t5 ( id int not null , value1 int, value2 varchar(255), primary key (id)) engine=innodb;
            		 
             
            		 
            create procedure sp(in pinuserid varchar(255))
            		 
             
            		 
            SELECT * from
            		 
             (SELECT gdd.customerreference AS customerreference, gdd.policynumber AS policynumber, NULL AS schemereference, NULL AS schemename, NULL AS applicationreference, min(gdd.modifiedcreateddatetime) AS modifiedcreateddatetime, min(gdd.demandcreateddatetime) AS demandcreateddatetime,count(distinct(gdd.genericdemandid)) AS countdemands, min(gdd.genericdemandid) AS genericdemandid,min(gdd.status) AS status, max(CASE gdd.isurgent WHEN 1 THEN '1' WHEN 0 THEN '0' END) AS isurgent,min(gdd.reactivatedate) AS 'reactivatedate',max(CASE gdd.isbusinessexception WHEN 1 THEN '1' WHEN 0 THEN '0' END) AS isbusinessexception
            		 
              FROM
            		 
               (SELECT t3.customerreference AS customerreference,t3.policynumber AS policynumber, t3.modifiedcreateddatetime AS modifiedcreateddatetime,t3.demandcreateddatetime AS demandcreateddatetime,t3.status AS status, t3.genericdemandid AS genericdemandid,t3.isurgent AS isurgent, t3.isbusinessexception AS isbusinessexception,
            		 
                 (SELECT t4.reactivatedate
            		 
                  FROM t4 
                  WHERE t3.genericdemandid = t4.genericdemandid
            		 
                   AND t4.ispend = 1
            		 
                   AND t4.reactivatedate > utc_timestamp()
            		 
                  ORDER BY t4.id DESC LIMIT 1) AS 'reactivatedate'
            		 
                FROM t3 
                WHERE (t3.initiator = pinuserid AND t3.status<250)) gdd
            		 
              JOIN t1 phd on(gdd.customerreference = phd.customerreference)
            		 
              WHERE phd.customerreference<>''
            		 
              GROUP BY gdd.customerreference
            		 
              UNION ALL SELECT NULL AS customerreference, NULL AS policynumber, gdd.schemereference AS schemereference, sd.schemename AS schemename, NULL AS applicationreference, min(gdd.modifiedcreateddatetime) AS modifiedcreateddatetime,min(gdd.demandcreateddatetime) AS demandcreateddatetime, count(distinct(gdd.genericdemandid)) AS countdemands, min(gdd.genericdemandid) AS genericdemandid,min(gdd.status) AS status, max(CASE gdd.isurgent WHEN 1 THEN '1' WHEN 0 THEN '0' END) AS isurgent,min(gdd.reactivatedate)AS 'reactivatedate',max(CASE gdd.isbusinessexception WHEN 1 THEN '1'WHEN 0 THEN '0' END) AS isbusinessexception
            		 
              FROM
            		 
               (SELECT t3.schemereference AS schemereference, t3.modifiedcreateddatetime AS modifiedcreateddatetime,t3.demandcreateddatetime AS demandcreateddatetime,t3.status AS status, t3.genericdemandid AS genericdemandid,t3.isurgent AS isurgent, t3.isbusinessexception AS isbusinessexception,
            		 
                 (SELECT t4.reactivatedate
            		 
                  FROM t4 
                  WHERE t3.genericdemandid = t4.genericdemandid
            		 
                   AND t4.ispend = 1
            		 
                   AND t4.reactivatedate > utc_timestamp()
            		 
                  ORDER BY t4.id DESC LIMIT 1) AS 'reactivatedate'
            		 
                FROM t3 
                WHERE (t3.initiator=pinuserid
            		 
                       AND t3.status<250
            		 
                       AND t3.policynumber=''
            		 
                       AND t3.applicationreference='')) gdd
            		 
              JOIN t2 sd on(gdd.schemereference = sd.schemereference)
            		 
              WHERE (sd.schemereference<>'')
            		 
              GROUP BY gdd.schemereference
            		 
              UNION ALL SELECT NULL AS customerreference, NULL AS policynumber, NULL AS schemereference, NULL AS schemename, gdd.applicationreference AS applicationreference, min(gdd.modifiedcreateddatetime) AS modifiedcreateddatetime, min(gdd.demandcreateddatetime) AS demandcreateddatetime,count(distinct(gdd.genericdemandid)) AS countdemands, min(gdd.genericdemandid) AS genericdemandid,min(gdd.status) AS status, max(CASE gdd.isurgent WHEN 1 THEN '1' WHEN 0 THEN '0' END) AS isurgent,min(gdd.reactivatedate) AS 'reactivatedate', max(CASE gdd.isbusinessexception WHEN 1 THEN '1' WHEN 0 THEN '0' END) AS isbusinessexception
            		 
              FROM
            		 
               (SELECT t3.applicationreference AS applicationreference, t3.modifiedcreateddatetime AS modifiedcreateddatetime,t3.demandcreateddatetime AS demandcreateddatetime,t3.status AS status,t3.genericdemandid AS genericdemandid,t3.isurgent AS isurgent, t3.isbusinessexception AS isbusinessexception,
            		 
                 (SELECT t4.reactivatedate
            		 
                  FROM t4 
                  WHERE t3.genericdemandid = t4.genericdemandid
            		 
                   AND t4.ispend = 1
            		 
                   AND t4.reactivatedate > utc_timestamp()
            		 
                  ORDER BY t4.id DESC LIMIT 1) AS 'reactivatedate'
            		 
                FROM t3 
                WHERE (t3.initiator=pinuserid
            		 
                       AND t3.status<250
            		 
                       AND t3.policynumber=''
            		 
                       AND t3.applicationreference<>''
            		 
                       AND t3.schemereference <>'')) gdd
            		 
              GROUP BY gdd.applicationreference
            		 
              UNION ALL SELECT NULL AS customerreference, NULL AS policynumber, NULL AS schemereference, NULL AS schemename, gdd.aggregationkey AS aggregationkey, min(gdd.modifiedcreateddatetime) AS modifiedcreateddatetime, min(gdd.demandcreateddatetime) AS demandcreateddatetime,count(distinct(gdd.genericdemandid)) AS countdemands, min(gdd.genericdemandid) AS genericdemandid,min(gdd.status) AS status, max(CASE gdd.isurgent WHEN 1 THEN '1' WHEN 0 THEN '0' END) AS isurgent,min(gdd.reactivatedate) AS 'reactivatedate', max(CASE gdd.isbusinessexception WHEN 1 THEN '1' WHEN 0 THEN '0' END) AS isbusinessexception
            		 
              FROM
            		 
               (SELECT t3.aggregationkey AS aggregationkey, t3.modifiedcreateddatetime AS modifiedcreateddatetime,t3.demandcreateddatetime AS demandcreateddatetime,t3.status AS status,t3.genericdemandid AS genericdemandid,t3.isurgent AS isurgent, t3.isbusinessexception AS isbusinessexception,
            		 
                 (SELECT t4.reactivatedate
            		 
                  FROM t4 
                  WHERE t3.genericdemandid = t4.genericdemandid
            		 
                   AND t4.ispend = 1
            		 
                   AND t4.reactivatedate > utc_timestamp()
            		 
                  ORDER BY t4.id DESC LIMIT 1) AS 'reactivatedate'
            		 
                FROM t3 
                WHERE (t3.initiator=pinuserid
            		 
                       AND t3.status<250
            		 
                       AND t3.policynumber=''
            		 
                       AND t3.aggregationkey<>''
            		 
                       AND t3.applicationreference<>''
            		 
                       AND t3.schemereference ='')) gdd
            		 
              GROUP BY gdd.aggregationkey) ca
            		 
            ORDER BY ca.isurgent DESC,
            		 
                     if(ca.modifiedcreateddatetime = ''
            		 
                        OR ca.modifiedcreateddatetime IS NULL,1,0),
            		 
                     ca.modifiedcreateddatetime,
            		 
                     ca.genericdemandid ;
            		 
             
            		 
            call sp("initiator1");
            		 
            call sp("initiator1");

            On 5.5-10.1 it fails as

            5.5 821a6939087d411f97256d00

            		 
            Version: '5.5.69-MariaDB-debug'  socket: '/5.5/mysql-test/var/tmp/mysqld.1.sock'  port: 16000  Source distribution
            		 
            mysqld: /5.5/sql/table.cc:5012: void TABLE_LIST::set_check_merged(): Assertion `!derived->first_select()->exclude_from_table_unique_test || derived->outer_select()-> exclude_from_table_unique_test' failed.
            		 
            210318 12:51:14 [ERROR] mysqld got signal 6 ;
            		 
             
            		 
            Server version: 5.5.69-MariaDB-debug
            		 
             
            		 
            stdlib/abort.c:81(__GI_abort)[0x7f4fd247a535]
            		 
            intl/loadmsgcat.c:1177(_nl_load_domain)[0x7f4fd247a40f]
            		 
            /lib/x86_64-linux-gnu/libc.so.6(+0x30102)[0x7f4fd2488102]
            		 
            sql/table.cc:5010(TABLE_LIST::set_check_merged())[0x557adfe051a3]
            		 
            sql/table.h:2119(TABLE_LIST::init_derived(THD*, bool))[0x557adfe10954]
            		 
            sql/sql_derived.cc:546(mysql_derived_init(THD*, LEX*, TABLE_LIST*))[0x557adfb34e9e]
            		 
            sql/sql_derived.cc:104(mysql_handle_derived(LEX*, unsigned int))[0x557adfb3a7ef]
            		 
            sql/sql_base.cc:5756(open_and_lock_tables(THD*, TABLE_LIST*, bool, unsigned int, Prelocking_strategy*))[0x557adfad4421]
            		 
            sql/sql_base.h:528(open_and_lock_tables(THD*, TABLE_LIST*, bool, unsigned int))[0x557adfb98cc4]
            		 
            sql/sql_parse.cc:2221(mysql_execute_command(THD*))[0x557adfbb30e4]
            		 
            sql/sp_head.cc:3223(sp_instr_stmt::exec_core(THD*, unsigned int*))[0x557ae03b29ea]
            		 
            sql/sp_head.cc:2999(sp_lex_keeper::reset_lex_and_exec_core(THD*, unsigned int*, bool, sp_instr*))[0x557ae03ca893]
            		 
            sql/sp_head.cc:3145(sp_instr_stmt::execute(THD*, unsigned int*))[0x557ae03cb964]
            		 
            sql/sp_head.cc:1432(sp_head::execute(THD*, bool))[0x557ae03bbcb1]
            		 
            sql/sp_head.cc:2200(sp_head::execute_procedure(THD*, List<Item>*))[0x557ae03c09c0]
            		 
            sql/sql_parse.cc:4120(mysql_execute_command(THD*))[0x557adfbc9ba3]
            		 
            sql/sql_parse.cc:5941(mysql_parse(THD*, char*, unsigned int, Parser_state*))[0x557adfbce4e0]
            		 
            sql/sql_parse.cc:1069(dispatch_command(enum_server_command, THD*, char*, unsigned int))[0x557adfbd24a6]
            		 
            sql/sql_parse.cc:793(do_command(THD*))[0x557adfbd70d0]
            		 
            sql/sql_connect.cc:1268(do_handle_one_connection(THD*))[0x557adfe94b67]
            		 
            sql/sql_connect.cc:1186(handle_one_connection)[0x557adfe94e51]
            		 
            perfschema/pfs.cc:1018(pfs_spawn_thread)[0x557ae07bd5f7]
            		 
            nptl/pthread_create.c:487(start_thread)[0x7f4fd2e64fa3]
            		 
            x86_64/clone.S:97(clone)[0x7f4fd25514cf]

            on 10.3-10.5 fails with ASAN, while not on 10.2

            10.3 867724fd304caf714d3cd2

            		 
            Version: '10.3.29-MariaDB-debug-log'  socket: '/10.3/mysql-test/var/tmp/mysqld.1.sock'  port: 16000  Source distribution
            		 
            =================================================================
            		 
            ==6797==ERROR: AddressSanitizer: heap-use-after-free on address 0x634000160ca7 at pc 0x562bbdc5d4cd bp 0x7f3a86937f50 sp 0x7f3a86937f48
            		 
            READ of size 1 at 0x634000160ca7 thread T27
            		 
                #0 0x562bbdc5d4cc in heap_rrnd /10.3/storage/heap/hp_rrnd.c:40
            		 
                #1 0x562bbdc47ffa in ha_heap::rnd_pos(unsigned char*, unsigned char*) /10.3/storage/heap/ha_heap.cc:353
            		 
                #2 0x562bbd6e1552 in handler::ha_rnd_pos(unsigned char*, unsigned char*) /10.3/sql/handler.cc:2881
            		 
                #3 0x562bbdb038b5 in rr_from_pointers(READ_RECORD*) /10.3/sql/records.cc:551
            		 
                #4 0x562bbcd90b31 in READ_RECORD::read_record() /10.3/sql/records.h:70
            		 
                #5 0x562bbd06f982 in join_init_read_record(st_join_table*) /10.3/sql/sql_select.cc:20739
            		 
                #6 0x562bbd067f81 in sub_select(JOIN*, st_join_table*, bool) /10.3/sql/sql_select.cc:19800
            		 
                #7 0x562bbd065e45 in do_select /10.3/sql/sql_select.cc:19343
            		 
                #8 0x562bbcff6337 in JOIN::exec_inner() /10.3/sql/sql_select.cc:4125
            		 
                #9 0x562bbcff3d0f in JOIN::exec() /10.3/sql/sql_select.cc:3919
            		 
                #10 0x562bbcff7645 in mysql_select(THD*, TABLE_LIST*, unsigned int, List<Item>&, Item*, unsigned int, st_order*, st_order*, Item*, st_order*, unsigned long long, select_result*, st_select_lex_unit*, st_select_lex*) /10.3/sql/sql_select.cc:4327
            		 
                #11 0x562bbcfce0d5 in handle_select(THD*, LEX*, select_result*, unsigned long) /10.3/sql/sql_select.cc:370
            		 
                #12 0x562bbcf39df3 in execute_sqlcom_select /10.3/sql/sql_parse.cc:6342
            		 
                #13 0x562bbcf27dfc in mysql_execute_command(THD*) /10.3/sql/sql_parse.cc:3873
            		 
                #14 0x562bbccf8ab9 in sp_instr_stmt::exec_core(THD*, unsigned int*) /10.3/sql/sp_head.cc:3702
            		 
                #15 0x562bbccf73a1 in sp_lex_keeper::reset_lex_and_exec_core(THD*, unsigned int*, bool, sp_instr*) /10.3/sql/sp_head.cc:3434
            		 
                #16 0x562bbccf8288 in sp_instr_stmt::execute(THD*, unsigned int*) /10.3/sql/sp_head.cc:3608
            		 
                #17 0x562bbcce9668 in sp_head::execute(THD*, bool) /10.3/sql/sp_head.cc:1377
            		 
                #18 0x562bbccefb0c in sp_head::execute_procedure(THD*, List<Item>*) /10.3/sql/sp_head.cc:2404
            		 
                #19 0x562bbcf21652 in do_execute_sp /10.3/sql/sql_parse.cc:3019
            		 
                #20 0x562bbcf232ae in Sql_cmd_call::execute(THD*) /10.3/sql/sql_parse.cc:3259
            		 
                #21 0x562bbcf37894 in mysql_execute_command(THD*) /10.3/sql/sql_parse.cc:6078
            		 
                #22 0x562bbcf43efe in mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool) /10.3/sql/sql_parse.cc:7871
            		 
                #23 0x562bbcf1a5cc in dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool) /10.3/sql/sql_parse.cc:1852
            		 
                #24 0x562bbcf16d07 in do_command(THD*) /10.3/sql/sql_parse.cc:1398
            		 
                #25 0x562bbd309030 in do_handle_one_connection(CONNECT*) /10.3/sql/sql_connect.cc:1403
            		 
                #26 0x562bbd3088e8 in handle_one_connection /10.3/sql/sql_connect.cc:1308
            		 
                #27 0x562bbea78782 in pfs_spawn_thread /10.3/storage/perfschema/pfs.cc:1869
            		 
                #28 0x7f3a9ce0bfa2 in start_thread /build/glibc-vjB4T1/glibc-2.28/nptl/pthread_create.c:486
            		 
                #29 0x7f3a9c78f4ce in clone (/lib/x86_64-linux-gnu/libc.so.6+0xf94ce)
            		 
             
            		 
            0x634000160ca7 is located 1191 bytes inside of 127556-byte region [0x634000160800,0x63400017fa44)
            		 
            freed by thread T27 here:
            		 
                #0 0x7f3a9cf0dfb0 in __interceptor_free (/usr/lib/x86_64-linux-gnu/libasan.so.5+0xe8fb0)
            		 
                #1 0x562bbec8da26 in free_memory /10.3/mysys/safemalloc.c:279
            		 
                #2 0x562bbec8d054 in sf_free /10.3/mysys/safemalloc.c:197
            		 
                #3 0x562bbec5ca5b in my_free /10.3/mysys/my_malloc.c:223
            		 
                #4 0x562bbdc63de8 in hp_free_level /10.3/storage/heap/hp_block.c:151
            		 
                #5 0x562bbdc63f76 in hp_clear /10.3/storage/heap/hp_clear.c:35
            		 
                #6 0x562bbdc63e40 in heap_clear /10.3/storage/heap/hp_clear.c:27
            		 
                #7 0x562bbdc487d1 in ha_heap::delete_all_rows() /10.3/storage/heap/ha_heap.cc:404
            		 
                #8 0x562bbd6ef47f in handler::ha_delete_all_rows() /10.3/sql/handler.cc:4437
            		 
                #9 0x562bbce633c3 in mysql_derived_fill(THD*, LEX*, TABLE_LIST*) /10.3/sql/sql_derived.cc:1118
            		 
                #10 0x562bbce5cea0 in mysql_handle_single_derived(LEX*, TABLE_LIST*, unsigned int) /10.3/sql/sql_derived.cc:199
            		 
                #11 0x562bbd0375ac in st_join_table::preread_init() /10.3/sql/sql_select.cc:12851
            		 
                #12 0x562bbd06f840 in join_init_read_record(st_join_table*) /10.3/sql/sql_select.cc:20734
            		 
                #13 0x562bbd067f81 in sub_select(JOIN*, st_join_table*, bool) /10.3/sql/sql_select.cc:19800
            		 
                #14 0x562bbd065e45 in do_select /10.3/sql/sql_select.cc:19343
            		 
                #15 0x562bbcff6337 in JOIN::exec_inner() /10.3/sql/sql_select.cc:4125
            		 
                #16 0x562bbcff3d0f in JOIN::exec() /10.3/sql/sql_select.cc:3919
            		 
                #17 0x562bbcff7645 in mysql_select(THD*, TABLE_LIST*, unsigned int, List<Item>&, Item*, unsigned int, st_order*, st_order*, Item*, st_order*, unsigned long long, select_result*, st_select_lex_unit*, st_select_lex*) /10.3/sql/sql_select.cc:4327
            		 
                #18 0x562bbcfce0d5 in handle_select(THD*, LEX*, select_result*, unsigned long) /10.3/sql/sql_select.cc:370
            		 
                #19 0x562bbcf39df3 in execute_sqlcom_select /10.3/sql/sql_parse.cc:6342
            		 
                #20 0x562bbcf27dfc in mysql_execute_command(THD*) /10.3/sql/sql_parse.cc:3873
            		 
                #21 0x562bbccf8ab9 in sp_instr_stmt::exec_core(THD*, unsigned int*) /10.3/sql/sp_head.cc:3702
            		 
                #22 0x562bbccf73a1 in sp_lex_keeper::reset_lex_and_exec_core(THD*, unsigned int*, bool, sp_instr*) /10.3/sql/sp_head.cc:3434
            		 
                #23 0x562bbccf8288 in sp_instr_stmt::execute(THD*, unsigned int*) /10.3/sql/sp_head.cc:3608
            		 
                #24 0x562bbcce9668 in sp_head::execute(THD*, bool) /10.3/sql/sp_head.cc:1377
            		 
                #25 0x562bbccefb0c in sp_head::execute_procedure(THD*, List<Item>*) /10.3/sql/sp_head.cc:2404
            		 
                #26 0x562bbcf21652 in do_execute_sp /10.3/sql/sql_parse.cc:3019
            		 
                #27 0x562bbcf232ae in Sql_cmd_call::execute(THD*) /10.3/sql/sql_parse.cc:3259
            		 
                #28 0x562bbcf37894 in mysql_execute_command(THD*) /10.3/sql/sql_parse.cc:6078
            		 
                #29 0x562bbcf43efe in mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool) /10.3/sql/sql_parse.cc:7871
            		 
             
            		 
            previously allocated by thread T27 here:
            		 
                #0 0x7f3a9cf0e330 in __interceptor_malloc (/usr/lib/x86_64-linux-gnu/libasan.so.5+0xe9330)
            		 
                #1 0x562bbec8ca3a in sf_malloc /10.3/mysys/safemalloc.c:118
            		 
                #2 0x562bbec5bf98 in my_malloc /10.3/mysys/my_malloc.c:101
            		 
                #3 0x562bbdc6373a in hp_get_new_block /10.3/storage/heap/hp_block.c:81
            		 
                #4 0x562bbdc607cc in next_free_record_pos /10.3/storage/heap/hp_write.c:163
            		 
                #5 0x562bbdc5f2fb in heap_write /10.3/storage/heap/hp_write.c:45
            		 
                #6 0x562bbdc46db0 in ha_heap::write_row(unsigned char*) /10.3/storage/heap/ha_heap.cc:235
            		 
                #7 0x562bbd0b294e in handler::ha_write_tmp_row(unsigned char*) /10.3/sql/sql_class.h:6494
            		 
                #8 0x562bbd1cddb2 in select_unit::send_data(List<Item>&) /10.3/sql/sql_union.cc:149
            		 
                #9 0x562bbd1d1ed7 in select_union_direct::send_data(List<Item>&) /10.3/sql/sql_union.cc:544
            		 
                #10 0x562bbd073be7 in end_send_group(JOIN*, st_join_table*, bool) /10.3/sql/sql_select.cc:21131
            		 
                #11 0x562bbd067866 in sub_select(JOIN*, st_join_table*, bool) /10.3/sql/sql_select.cc:19754
            		 
                #12 0x562bbd066d0a in sub_select_postjoin_aggr(JOIN*, st_join_table*, bool) /10.3/sql/sql_select.cc:19521
            		 
                #13 0x562bbd067866 in sub_select(JOIN*, st_join_table*, bool) /10.3/sql/sql_select.cc:19754
            		 
                #14 0x562bbd067140 in sub_select_cache(JOIN*, st_join_table*, bool) /10.3/sql/sql_select.cc:19586
            		 
                #15 0x562bbd067866 in sub_select(JOIN*, st_join_table*, bool) /10.3/sql/sql_select.cc:19754
            		 
                #16 0x562bbd065f47 in do_select /10.3/sql/sql_select.cc:19345
            		 
                #17 0x562bbcff6337 in JOIN::exec_inner() /10.3/sql/sql_select.cc:4125
            		 
                #18 0x562bbcff3d0f in JOIN::exec() /10.3/sql/sql_select.cc:3919
            		 
                #19 0x562bbd1dcebc in st_select_lex_unit::exec() /10.3/sql/sql_union.cc:1544
            		 
                #20 0x562bbce6374d in mysql_derived_fill(THD*, LEX*, TABLE_LIST*) /10.3/sql/sql_derived.cc:1147
            		 
                #21 0x562bbce5cea0 in mysql_handle_single_derived(LEX*, TABLE_LIST*, unsigned int) /10.3/sql/sql_derived.cc:199
            		 
                #22 0x562bbd0375ac in st_join_table::preread_init() /10.3/sql/sql_select.cc:12851
            		 
                #23 0x562bbd08106b in create_sort_index(THD*, JOIN*, st_join_table*, Filesort*) /10.3/sql/sql_select.cc:22961
            		 
                #24 0x562bbd06ff70 in st_join_table::sort_table() /10.3/sql/sql_select.cc:20778
            		 
                #25 0x562bbd06f47a in join_init_read_record(st_join_table*) /10.3/sql/sql_select.cc:20719
            		 
                #26 0x562bbd067f81 in sub_select(JOIN*, st_join_table*, bool) /10.3/sql/sql_select.cc:19800
            		 
                #27 0x562bbd065e45 in do_select /10.3/sql/sql_select.cc:19343
            		 
                #28 0x562bbcff6337 in JOIN::exec_inner() /10.3/sql/sql_select.cc:4125
            		 
                #29 0x562bbcff3d0f in JOIN::exec() /10.3/sql/sql_select.cc:3919
            		 
             
            		 
            Thread T27 created by T0 here:
            		 
                #0 0x7f3a9ce75db0 in __interceptor_pthread_create (/usr/lib/x86_64-linux-gnu/libasan.so.5+0x50db0)
            		 
                #1 0x562bbea78bbe in spawn_thread_v1 /10.3/storage/perfschema/pfs.cc:1919
            		 
                #2 0x562bbcc296f4 in inline_mysql_thread_create /10.3/include/mysql/psi/mysql_thread.h:1275
            		 
                #3 0x562bbcc42fe3 in create_thread_to_handle_connection(CONNECT*) /10.3/sql/mysqld.cc:6664
            		 
                #4 0x562bbcc43738 in create_new_thread /10.3/sql/mysqld.cc:6734
            		 
                #5 0x562bbcc448b9 in handle_connections_sockets() /10.3/sql/mysqld.cc:6992
            		 
                #6 0x562bbcc42357 in mysqld_main(int, char**) /10.3/sql/mysqld.cc:6286
            		 
                #7 0x562bbcc27df4 in main /10.3/sql/main.cc:25
            		 
                #8 0x7f3a9c6ba09a in __libc_start_main ../csu/libc-start.c:308
            		 
             
            		 
            SUMMARY: AddressSanitizer: heap-use-after-free /10.3/storage/heap/hp_rrnd.c:40 in heap_rrnd
            		 
            Shadow bytes around the buggy address:
            		 
              0x0c6880024140: fd fd fd fd fd fd fd fd fd fd fd fd fd fd fd fd
            		 
              0x0c6880024150: fd fd fd fd fd fd fd fd fd fd fd fd fd fd fd fd
            		 
              0x0c6880024160: fd fd fd fd fd fd fd fd fd fd fd fd fd fd fd fd
            		 
              0x0c6880024170: fd fd fd fd fd fd fd fd fd fd fd fd fd fd fd fd
            		 
              0x0c6880024180: fd fd fd fd fd fd fd fd fd fd fd fd fd fd fd fd
            		 
            =>0x0c6880024190: fd fd fd fd[fd]fd fd fd fd fd fd fd fd fd fd fd
            		 
              0x0c68800241a0: fd fd fd fd fd fd fd fd fd fd fd fd fd fd fd fd
            		 
              0x0c68800241b0: fd fd fd fd fd fd fd fd fd fd fd fd fd fd fd fd
            		 
              0x0c68800241c0: fd fd fd fd fd fd fd fd fd fd fd fd fd fd fd fd
            		 
              0x0c68800241d0: fd fd fd fd fd fd fd fd fd fd fd fd fd fd fd fd
            		 
              0x0c68800241e0: fd fd fd fd fd fd fd fd fd fd fd fd fd fd fd fd
            		 
            Shadow byte legend (one shadow byte represents 8 application bytes):
            		 
              Addressable:           00
            		 
              Partially addressable: 01 02 03 04 05 06 07 
              Heap left redzone:       fa
            		 
              Freed heap region:       fd
            		 
              Stack left redzone:      f1
            		 
              Stack mid redzone:       f2
            		 
              Stack right redzone:     f3
            		 
              Stack after return:      f5
            		 
              Stack use after scope:   f8
            		 
              Global redzone:          f9
            		 
              Global init order:       f6
            		 
              Poisoned by user:        f7
            		 
              Container overflow:      fc
            		 
              Array cookie:            ac
            		 
              Intra object redzone:    bb
            		 
              ASan internal:           fe
            		 
              Left alloca redzone:     ca
            		 
              Right alloca redzone:    cb
            		 
            ==6797==ABORTING
            		 
            ----------SERVER LOG END-------------

            alice Alice Sherepa added a comment - test to reproduce the issue, but not simplified, problem after the 2.nd execution of the stored procedure: --source include/have_innodb.inc   create table t1 ( id int not null , customerreference varchar (255), primary key (id)) engine=innodb; insert into t1 values (1, 'reference1' ),(2, 'reference2' ),(3, 'reference3' );     create table t2 ( schemeid int not null , schemereference varchar (8), schemename varchar (255), isnewbusiness tinyint(1), schemetolerance int , isdetailsretained tinyint(1), issubjecttowarningmessage tinyint(1), istrust tinyint(1), isbpa tinyint(1), primary key (schemeid), key ix1_t2 (schemereference) using btree ) engine=innodb;   insert into t2 values (1, 'ref1' , 'schemename1' ,0,1,0,0,0,0),(2, 'ref2' , 'schemename2' ,0,2,0,0,0,0),(3, 'ref3' , 'schemename3' ,0,3,0,0,0,0);   create table t3 ( id int not null , policynumber varchar (255), customerreference varchar (255), demandtypeid int , demandid int , initiator varchar (255), startdatetime datetime, enddatetime datetime, isactive tinyint(1), demandcreateddatetime datetime, demandenddatetime datetime, status int , documentuniquereference varchar (255), isurgent bit (1), modifiedcreateddatetime datetime, demandsource varchar (255), genericdemandid int , schemereference varchar (8), categoryreference varchar (8), aggregationkey varchar (255), contractsystem varchar (255), productgroup varchar (255), isbusinessexception tinyint(1), applicationreference varchar (25), stageid int default 1, isstuckdemand tinyint(1) default 0, sla int , slaenddatetime datetime, identifier varchar (100), primary key (id)) engine=innodb;     insert into t3 values (1, '1' , 'reference1' ,1,1, 'initiator1' , '2020-01-01 10:10:10' , '2020-01-01 10:10:10' ,0, '2020-01-01 10:10:10' , '2020-01-01 10:10:10' ,1, 'documentuniquereference1' , '\0' , '2020-01-01 10:10:10' , 'demandsource1' ,1, 'scheme1' , 'cat1' , 'aggregationkey1' , 'contractsystem1' , 'productgroup1' ,0, 'appref1' ,1,0,1, '2020-01-01 10:10:10' , 'identifier1' ),(2, '2' , 'reference2' ,2,2, 'initiator2' , '2020-01-01 10:10:10' , '2020-01-01 10:10:10' ,0, '2020-01-01 10:10:10' , '2020-01-01 10:10:10' ,2, 'documentuniquereference2' , '\0' , '2020-01-01 10:10:10' , 'demandsource2' ,2, 'scheme2' , 'cat2' , 'aggregationkey2' , 'contractsystem2' , 'productgroup2' ,0, 'appref2' ,2,0,2, '2020-01-01 10:10:10' , 'identifier2' ),(3, '3' , 'reference3' ,3,3, 'initiator3' , '2020-01-01 10:10:10' , '2020-01-01 10:10:10' ,0, '2020-01-01 10:10:10' , '2020-01-01 10:10:10' ,3, 'documentuniquereference3' , '\0' , '2020-01-01 10:10:10' , 'demandsource3' ,3, 'scheme3' , 'cat3' , 'aggregationkey3' , 'contractsystem3' , 'productgroup3' ,0, 'appref3' ,3,0,3, '2020-01-01 10:10:10' , 'identifier3' );     create table t4 ( id int not null , demandtypeid int , demandid int , pendreasonid int , pendedby varchar (50), comments varchar (500), ispend tinyint(1), pendeddatetime datetime, reactivatedate datetime, autopended tinyint(1), ismanualreactivated tinyint(1), genericdemandid int , primary key (id), key ix1_t4 (genericdemandid,ispend,reactivatedate) using btree ) engine=innodb;     insert into t4 values (1,2,2,2, 'example' , 'comment' ,1, '2020-01-01 10:10:10' , '2020-01-01 10:10:10' ,0,0,1),(2,1,1,1, 'me' , 'comment2' ,1, '2020-01-01 10:10:10' , '2020-01-01 10:10:10' ,0,0,2),(3,2,3,4, 'you' , 'comment3' ,1, '2020-01-01 10:10:10' , '2020-01-01 10:10:10' ,0,0,2);     create table t5 ( id int not null , value1 int , value2 varchar (255), primary key (id)) engine=innodb;   create procedure sp( in pinuserid varchar (255))   SELECT * from ( SELECT gdd.customerreference AS customerreference, gdd.policynumber AS policynumber, NULL AS schemereference, NULL AS schemename, NULL AS applicationreference, min (gdd.modifiedcreateddatetime) AS modifiedcreateddatetime, min (gdd.demandcreateddatetime) AS demandcreateddatetime, count ( distinct (gdd.genericdemandid)) AS countdemands, min (gdd.genericdemandid) AS genericdemandid, min (gdd.status) AS status, max ( CASE gdd.isurgent WHEN 1 THEN '1' WHEN 0 THEN '0' END ) AS isurgent, min (gdd.reactivatedate) AS 'reactivatedate' , max ( CASE gdd.isbusinessexception WHEN 1 THEN '1' WHEN 0 THEN '0' END ) AS isbusinessexception FROM ( SELECT t3.customerreference AS customerreference,t3.policynumber AS policynumber, t3.modifiedcreateddatetime AS modifiedcreateddatetime,t3.demandcreateddatetime AS demandcreateddatetime,t3.status AS status, t3.genericdemandid AS genericdemandid,t3.isurgent AS isurgent, t3.isbusinessexception AS isbusinessexception, ( SELECT t4.reactivatedate FROM t4 WHERE t3.genericdemandid = t4.genericdemandid AND t4.ispend = 1 AND t4.reactivatedate > utc_timestamp() ORDER BY t4.id DESC LIMIT 1) AS 'reactivatedate' FROM t3 WHERE (t3.initiator = pinuserid AND t3.status<250)) gdd JOIN t1 phd on (gdd.customerreference = phd.customerreference) WHERE phd.customerreference<> '' GROUP BY gdd.customerreference UNION ALL SELECT NULL AS customerreference, NULL AS policynumber, gdd.schemereference AS schemereference, sd.schemename AS schemename, NULL AS applicationreference, min (gdd.modifiedcreateddatetime) AS modifiedcreateddatetime, min (gdd.demandcreateddatetime) AS demandcreateddatetime, count ( distinct (gdd.genericdemandid)) AS countdemands, min (gdd.genericdemandid) AS genericdemandid, min (gdd.status) AS status, max ( CASE gdd.isurgent WHEN 1 THEN '1' WHEN 0 THEN '0' END ) AS isurgent, min (gdd.reactivatedate) AS 'reactivatedate' , max ( CASE gdd.isbusinessexception WHEN 1 THEN '1' WHEN 0 THEN '0' END ) AS isbusinessexception FROM ( SELECT t3.schemereference AS schemereference, t3.modifiedcreateddatetime AS modifiedcreateddatetime,t3.demandcreateddatetime AS demandcreateddatetime,t3.status AS status, t3.genericdemandid AS genericdemandid,t3.isurgent AS isurgent, t3.isbusinessexception AS isbusinessexception, ( SELECT t4.reactivatedate FROM t4 WHERE t3.genericdemandid = t4.genericdemandid AND t4.ispend = 1 AND t4.reactivatedate > utc_timestamp() ORDER BY t4.id DESC LIMIT 1) AS 'reactivatedate' FROM t3 WHERE (t3.initiator=pinuserid AND t3.status<250 AND t3.policynumber= '' AND t3.applicationreference= '' )) gdd JOIN t2 sd on (gdd.schemereference = sd.schemereference) WHERE (sd.schemereference<> '' ) GROUP BY gdd.schemereference UNION ALL SELECT NULL AS customerreference, NULL AS policynumber, NULL AS schemereference, NULL AS schemename, gdd.applicationreference AS applicationreference, min (gdd.modifiedcreateddatetime) AS modifiedcreateddatetime, min (gdd.demandcreateddatetime) AS demandcreateddatetime, count ( distinct (gdd.genericdemandid)) AS countdemands, min (gdd.genericdemandid) AS genericdemandid, min (gdd.status) AS status, max ( CASE gdd.isurgent WHEN 1 THEN '1' WHEN 0 THEN '0' END ) AS isurgent, min (gdd.reactivatedate) AS 'reactivatedate' , max ( CASE gdd.isbusinessexception WHEN 1 THEN '1' WHEN 0 THEN '0' END ) AS isbusinessexception FROM ( SELECT t3.applicationreference AS applicationreference, t3.modifiedcreateddatetime AS modifiedcreateddatetime,t3.demandcreateddatetime AS demandcreateddatetime,t3.status AS status,t3.genericdemandid AS genericdemandid,t3.isurgent AS isurgent, t3.isbusinessexception AS isbusinessexception, ( SELECT t4.reactivatedate FROM t4 WHERE t3.genericdemandid = t4.genericdemandid AND t4.ispend = 1 AND t4.reactivatedate > utc_timestamp() ORDER BY t4.id DESC LIMIT 1) AS 'reactivatedate' FROM t3 WHERE (t3.initiator=pinuserid AND t3.status<250 AND t3.policynumber= '' AND t3.applicationreference<> '' AND t3.schemereference <> '' )) gdd GROUP BY gdd.applicationreference UNION ALL SELECT NULL AS customerreference, NULL AS policynumber, NULL AS schemereference, NULL AS schemename, gdd.aggregationkey AS aggregationkey, min (gdd.modifiedcreateddatetime) AS modifiedcreateddatetime, min (gdd.demandcreateddatetime) AS demandcreateddatetime, count ( distinct (gdd.genericdemandid)) AS countdemands, min (gdd.genericdemandid) AS genericdemandid, min (gdd.status) AS status, max ( CASE gdd.isurgent WHEN 1 THEN '1' WHEN 0 THEN '0' END ) AS isurgent, min (gdd.reactivatedate) AS 'reactivatedate' , max ( CASE gdd.isbusinessexception WHEN 1 THEN '1' WHEN 0 THEN '0' END ) AS isbusinessexception FROM ( SELECT t3.aggregationkey AS aggregationkey, t3.modifiedcreateddatetime AS modifiedcreateddatetime,t3.demandcreateddatetime AS demandcreateddatetime,t3.status AS status,t3.genericdemandid AS genericdemandid,t3.isurgent AS isurgent, t3.isbusinessexception AS isbusinessexception, ( SELECT t4.reactivatedate FROM t4 WHERE t3.genericdemandid = t4.genericdemandid AND t4.ispend = 1 AND t4.reactivatedate > utc_timestamp() ORDER BY t4.id DESC LIMIT 1) AS 'reactivatedate' FROM t3 WHERE (t3.initiator=pinuserid AND t3.status<250 AND t3.policynumber= '' AND t3.aggregationkey<> '' AND t3.applicationreference<> '' AND t3.schemereference = '' )) gdd GROUP BY gdd.aggregationkey) ca ORDER BY ca.isurgent DESC , if (ca.modifiedcreateddatetime = '' OR ca.modifiedcreateddatetime IS NULL ,1,0), ca.modifiedcreateddatetime, ca.genericdemandid ;   call sp( "initiator1" ); call sp( "initiator1" ); On 5.5-10.1 it fails as 5.5 821a6939087d411f97256d00 Version: '5.5.69-MariaDB-debug' socket: '/5.5/mysql-test/var/tmp/mysqld.1.sock' port: 16000 Source distribution mysqld: /5.5/sql/table.cc:5012: void TABLE_LIST::set_check_merged(): Assertion `!derived->first_select()->exclude_from_table_unique_test || derived->outer_select()-> exclude_from_table_unique_test' failed. 210318 12:51:14 [ERROR] mysqld got signal 6 ;   Server version: 5.5.69-MariaDB-debug   stdlib/abort.c:81(__GI_abort)[0x7f4fd247a535] intl/loadmsgcat.c:1177(_nl_load_domain)[0x7f4fd247a40f] /lib/x86_64-linux-gnu/libc.so.6(+0x30102)[0x7f4fd2488102] sql/table.cc:5010(TABLE_LIST::set_check_merged())[0x557adfe051a3] sql/table.h:2119(TABLE_LIST::init_derived(THD*, bool))[0x557adfe10954] sql/sql_derived.cc:546(mysql_derived_init(THD*, LEX*, TABLE_LIST*))[0x557adfb34e9e] sql/sql_derived.cc:104(mysql_handle_derived(LEX*, unsigned int))[0x557adfb3a7ef] sql/sql_base.cc:5756(open_and_lock_tables(THD*, TABLE_LIST*, bool, unsigned int, Prelocking_strategy*))[0x557adfad4421] sql/sql_base.h:528(open_and_lock_tables(THD*, TABLE_LIST*, bool, unsigned int))[0x557adfb98cc4] sql/sql_parse.cc:2221(mysql_execute_command(THD*))[0x557adfbb30e4] sql/sp_head.cc:3223(sp_instr_stmt::exec_core(THD*, unsigned int*))[0x557ae03b29ea] sql/sp_head.cc:2999(sp_lex_keeper::reset_lex_and_exec_core(THD*, unsigned int*, bool, sp_instr*))[0x557ae03ca893] sql/sp_head.cc:3145(sp_instr_stmt::execute(THD*, unsigned int*))[0x557ae03cb964] sql/sp_head.cc:1432(sp_head::execute(THD*, bool))[0x557ae03bbcb1] sql/sp_head.cc:2200(sp_head::execute_procedure(THD*, List<Item>*))[0x557ae03c09c0] sql/sql_parse.cc:4120(mysql_execute_command(THD*))[0x557adfbc9ba3] sql/sql_parse.cc:5941(mysql_parse(THD*, char*, unsigned int, Parser_state*))[0x557adfbce4e0] sql/sql_parse.cc:1069(dispatch_command(enum_server_command, THD*, char*, unsigned int))[0x557adfbd24a6] sql/sql_parse.cc:793(do_command(THD*))[0x557adfbd70d0] sql/sql_connect.cc:1268(do_handle_one_connection(THD*))[0x557adfe94b67] sql/sql_connect.cc:1186(handle_one_connection)[0x557adfe94e51] perfschema/pfs.cc:1018(pfs_spawn_thread)[0x557ae07bd5f7] nptl/pthread_create.c:487(start_thread)[0x7f4fd2e64fa3] x86_64/clone.S:97(clone)[0x7f4fd25514cf] on 10.3-10.5 fails with ASAN, while not on 10.2 10.3 867724fd304caf714d3cd2 Version: '10.3.29-MariaDB-debug-log' socket: '/10.3/mysql-test/var/tmp/mysqld.1.sock' port: 16000 Source distribution ================================================================= ==6797==ERROR: AddressSanitizer: heap-use-after-free on address 0x634000160ca7 at pc 0x562bbdc5d4cd bp 0x7f3a86937f50 sp 0x7f3a86937f48 READ of size 1 at 0x634000160ca7 thread T27 #0 0x562bbdc5d4cc in heap_rrnd /10.3/storage/heap/hp_rrnd.c:40 #1 0x562bbdc47ffa in ha_heap::rnd_pos(unsigned char*, unsigned char*) /10.3/storage/heap/ha_heap.cc:353 #2 0x562bbd6e1552 in handler::ha_rnd_pos(unsigned char*, unsigned char*) /10.3/sql/handler.cc:2881 #3 0x562bbdb038b5 in rr_from_pointers(READ_RECORD*) /10.3/sql/records.cc:551 #4 0x562bbcd90b31 in READ_RECORD::read_record() /10.3/sql/records.h:70 #5 0x562bbd06f982 in join_init_read_record(st_join_table*) /10.3/sql/sql_select.cc:20739 #6 0x562bbd067f81 in sub_select(JOIN*, st_join_table*, bool) /10.3/sql/sql_select.cc:19800 #7 0x562bbd065e45 in do_select /10.3/sql/sql_select.cc:19343 #8 0x562bbcff6337 in JOIN::exec_inner() /10.3/sql/sql_select.cc:4125 #9 0x562bbcff3d0f in JOIN::exec() /10.3/sql/sql_select.cc:3919 #10 0x562bbcff7645 in mysql_select(THD*, TABLE_LIST*, unsigned int, List<Item>&, Item*, unsigned int, st_order*, st_order*, Item*, st_order*, unsigned long long, select_result*, st_select_lex_unit*, st_select_lex*) /10.3/sql/sql_select.cc:4327 #11 0x562bbcfce0d5 in handle_select(THD*, LEX*, select_result*, unsigned long) /10.3/sql/sql_select.cc:370 #12 0x562bbcf39df3 in execute_sqlcom_select /10.3/sql/sql_parse.cc:6342 #13 0x562bbcf27dfc in mysql_execute_command(THD*) /10.3/sql/sql_parse.cc:3873 #14 0x562bbccf8ab9 in sp_instr_stmt::exec_core(THD*, unsigned int*) /10.3/sql/sp_head.cc:3702 #15 0x562bbccf73a1 in sp_lex_keeper::reset_lex_and_exec_core(THD*, unsigned int*, bool, sp_instr*) /10.3/sql/sp_head.cc:3434 #16 0x562bbccf8288 in sp_instr_stmt::execute(THD*, unsigned int*) /10.3/sql/sp_head.cc:3608 #17 0x562bbcce9668 in sp_head::execute(THD*, bool) /10.3/sql/sp_head.cc:1377 #18 0x562bbccefb0c in sp_head::execute_procedure(THD*, List<Item>*) /10.3/sql/sp_head.cc:2404 #19 0x562bbcf21652 in do_execute_sp /10.3/sql/sql_parse.cc:3019 #20 0x562bbcf232ae in Sql_cmd_call::execute(THD*) /10.3/sql/sql_parse.cc:3259 #21 0x562bbcf37894 in mysql_execute_command(THD*) /10.3/sql/sql_parse.cc:6078 #22 0x562bbcf43efe in mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool) /10.3/sql/sql_parse.cc:7871 #23 0x562bbcf1a5cc in dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool) /10.3/sql/sql_parse.cc:1852 #24 0x562bbcf16d07 in do_command(THD*) /10.3/sql/sql_parse.cc:1398 #25 0x562bbd309030 in do_handle_one_connection(CONNECT*) /10.3/sql/sql_connect.cc:1403 #26 0x562bbd3088e8 in handle_one_connection /10.3/sql/sql_connect.cc:1308 #27 0x562bbea78782 in pfs_spawn_thread /10.3/storage/perfschema/pfs.cc:1869 #28 0x7f3a9ce0bfa2 in start_thread /build/glibc-vjB4T1/glibc-2.28/nptl/pthread_create.c:486 #29 0x7f3a9c78f4ce in clone (/lib/x86_64-linux-gnu/libc.so.6+0xf94ce)   0x634000160ca7 is located 1191 bytes inside of 127556-byte region [0x634000160800,0x63400017fa44) freed by thread T27 here: #0 0x7f3a9cf0dfb0 in __interceptor_free (/usr/lib/x86_64-linux-gnu/libasan.so.5+0xe8fb0) #1 0x562bbec8da26 in free_memory /10.3/mysys/safemalloc.c:279 #2 0x562bbec8d054 in sf_free /10.3/mysys/safemalloc.c:197 #3 0x562bbec5ca5b in my_free /10.3/mysys/my_malloc.c:223 #4 0x562bbdc63de8 in hp_free_level /10.3/storage/heap/hp_block.c:151 #5 0x562bbdc63f76 in hp_clear /10.3/storage/heap/hp_clear.c:35 #6 0x562bbdc63e40 in heap_clear /10.3/storage/heap/hp_clear.c:27 #7 0x562bbdc487d1 in ha_heap::delete_all_rows() /10.3/storage/heap/ha_heap.cc:404 #8 0x562bbd6ef47f in handler::ha_delete_all_rows() /10.3/sql/handler.cc:4437 #9 0x562bbce633c3 in mysql_derived_fill(THD*, LEX*, TABLE_LIST*) /10.3/sql/sql_derived.cc:1118 #10 0x562bbce5cea0 in mysql_handle_single_derived(LEX*, TABLE_LIST*, unsigned int) /10.3/sql/sql_derived.cc:199 #11 0x562bbd0375ac in st_join_table::preread_init() /10.3/sql/sql_select.cc:12851 #12 0x562bbd06f840 in join_init_read_record(st_join_table*) /10.3/sql/sql_select.cc:20734 #13 0x562bbd067f81 in sub_select(JOIN*, st_join_table*, bool) /10.3/sql/sql_select.cc:19800 #14 0x562bbd065e45 in do_select /10.3/sql/sql_select.cc:19343 #15 0x562bbcff6337 in JOIN::exec_inner() /10.3/sql/sql_select.cc:4125 #16 0x562bbcff3d0f in JOIN::exec() /10.3/sql/sql_select.cc:3919 #17 0x562bbcff7645 in mysql_select(THD*, TABLE_LIST*, unsigned int, List<Item>&, Item*, unsigned int, st_order*, st_order*, Item*, st_order*, unsigned long long, select_result*, st_select_lex_unit*, st_select_lex*) /10.3/sql/sql_select.cc:4327 #18 0x562bbcfce0d5 in handle_select(THD*, LEX*, select_result*, unsigned long) /10.3/sql/sql_select.cc:370 #19 0x562bbcf39df3 in execute_sqlcom_select /10.3/sql/sql_parse.cc:6342 #20 0x562bbcf27dfc in mysql_execute_command(THD*) /10.3/sql/sql_parse.cc:3873 #21 0x562bbccf8ab9 in sp_instr_stmt::exec_core(THD*, unsigned int*) /10.3/sql/sp_head.cc:3702 #22 0x562bbccf73a1 in sp_lex_keeper::reset_lex_and_exec_core(THD*, unsigned int*, bool, sp_instr*) /10.3/sql/sp_head.cc:3434 #23 0x562bbccf8288 in sp_instr_stmt::execute(THD*, unsigned int*) /10.3/sql/sp_head.cc:3608 #24 0x562bbcce9668 in sp_head::execute(THD*, bool) /10.3/sql/sp_head.cc:1377 #25 0x562bbccefb0c in sp_head::execute_procedure(THD*, List<Item>*) /10.3/sql/sp_head.cc:2404 #26 0x562bbcf21652 in do_execute_sp /10.3/sql/sql_parse.cc:3019 #27 0x562bbcf232ae in Sql_cmd_call::execute(THD*) /10.3/sql/sql_parse.cc:3259 #28 0x562bbcf37894 in mysql_execute_command(THD*) /10.3/sql/sql_parse.cc:6078 #29 0x562bbcf43efe in mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool) /10.3/sql/sql_parse.cc:7871   previously allocated by thread T27 here: #0 0x7f3a9cf0e330 in __interceptor_malloc (/usr/lib/x86_64-linux-gnu/libasan.so.5+0xe9330) #1 0x562bbec8ca3a in sf_malloc /10.3/mysys/safemalloc.c:118 #2 0x562bbec5bf98 in my_malloc /10.3/mysys/my_malloc.c:101 #3 0x562bbdc6373a in hp_get_new_block /10.3/storage/heap/hp_block.c:81 #4 0x562bbdc607cc in next_free_record_pos /10.3/storage/heap/hp_write.c:163 #5 0x562bbdc5f2fb in heap_write /10.3/storage/heap/hp_write.c:45 #6 0x562bbdc46db0 in ha_heap::write_row(unsigned char*) /10.3/storage/heap/ha_heap.cc:235 #7 0x562bbd0b294e in handler::ha_write_tmp_row(unsigned char*) /10.3/sql/sql_class.h:6494 #8 0x562bbd1cddb2 in select_unit::send_data(List<Item>&) /10.3/sql/sql_union.cc:149 #9 0x562bbd1d1ed7 in select_union_direct::send_data(List<Item>&) /10.3/sql/sql_union.cc:544 #10 0x562bbd073be7 in end_send_group(JOIN*, st_join_table*, bool) /10.3/sql/sql_select.cc:21131 #11 0x562bbd067866 in sub_select(JOIN*, st_join_table*, bool) /10.3/sql/sql_select.cc:19754 #12 0x562bbd066d0a in sub_select_postjoin_aggr(JOIN*, st_join_table*, bool) /10.3/sql/sql_select.cc:19521 #13 0x562bbd067866 in sub_select(JOIN*, st_join_table*, bool) /10.3/sql/sql_select.cc:19754 #14 0x562bbd067140 in sub_select_cache(JOIN*, st_join_table*, bool) /10.3/sql/sql_select.cc:19586 #15 0x562bbd067866 in sub_select(JOIN*, st_join_table*, bool) /10.3/sql/sql_select.cc:19754 #16 0x562bbd065f47 in do_select /10.3/sql/sql_select.cc:19345 #17 0x562bbcff6337 in JOIN::exec_inner() /10.3/sql/sql_select.cc:4125 #18 0x562bbcff3d0f in JOIN::exec() /10.3/sql/sql_select.cc:3919 #19 0x562bbd1dcebc in st_select_lex_unit::exec() /10.3/sql/sql_union.cc:1544 #20 0x562bbce6374d in mysql_derived_fill(THD*, LEX*, TABLE_LIST*) /10.3/sql/sql_derived.cc:1147 #21 0x562bbce5cea0 in mysql_handle_single_derived(LEX*, TABLE_LIST*, unsigned int) /10.3/sql/sql_derived.cc:199 #22 0x562bbd0375ac in st_join_table::preread_init() /10.3/sql/sql_select.cc:12851 #23 0x562bbd08106b in create_sort_index(THD*, JOIN*, st_join_table*, Filesort*) /10.3/sql/sql_select.cc:22961 #24 0x562bbd06ff70 in st_join_table::sort_table() /10.3/sql/sql_select.cc:20778 #25 0x562bbd06f47a in join_init_read_record(st_join_table*) /10.3/sql/sql_select.cc:20719 #26 0x562bbd067f81 in sub_select(JOIN*, st_join_table*, bool) /10.3/sql/sql_select.cc:19800 #27 0x562bbd065e45 in do_select /10.3/sql/sql_select.cc:19343 #28 0x562bbcff6337 in JOIN::exec_inner() /10.3/sql/sql_select.cc:4125 #29 0x562bbcff3d0f in JOIN::exec() /10.3/sql/sql_select.cc:3919   Thread T27 created by T0 here: #0 0x7f3a9ce75db0 in __interceptor_pthread_create (/usr/lib/x86_64-linux-gnu/libasan.so.5+0x50db0) #1 0x562bbea78bbe in spawn_thread_v1 /10.3/storage/perfschema/pfs.cc:1919 #2 0x562bbcc296f4 in inline_mysql_thread_create /10.3/include/mysql/psi/mysql_thread.h:1275 #3 0x562bbcc42fe3 in create_thread_to_handle_connection(CONNECT*) /10.3/sql/mysqld.cc:6664 #4 0x562bbcc43738 in create_new_thread /10.3/sql/mysqld.cc:6734 #5 0x562bbcc448b9 in handle_connections_sockets() /10.3/sql/mysqld.cc:6992 #6 0x562bbcc42357 in mysqld_main(int, char**) /10.3/sql/mysqld.cc:6286 #7 0x562bbcc27df4 in main /10.3/sql/main.cc:25 #8 0x7f3a9c6ba09a in __libc_start_main ../csu/libc-start.c:308   SUMMARY: AddressSanitizer: heap-use-after-free /10.3/storage/heap/hp_rrnd.c:40 in heap_rrnd Shadow bytes around the buggy address: 0x0c6880024140: fd fd fd fd fd fd fd fd fd fd fd fd fd fd fd fd 0x0c6880024150: fd fd fd fd fd fd fd fd fd fd fd fd fd fd fd fd 0x0c6880024160: fd fd fd fd fd fd fd fd fd fd fd fd fd fd fd fd 0x0c6880024170: fd fd fd fd fd fd fd fd fd fd fd fd fd fd fd fd 0x0c6880024180: fd fd fd fd fd fd fd fd fd fd fd fd fd fd fd fd =>0x0c6880024190: fd fd fd fd[fd]fd fd fd fd fd fd fd fd fd fd fd 0x0c68800241a0: fd fd fd fd fd fd fd fd fd fd fd fd fd fd fd fd 0x0c68800241b0: fd fd fd fd fd fd fd fd fd fd fd fd fd fd fd fd 0x0c68800241c0: fd fd fd fd fd fd fd fd fd fd fd fd fd fd fd fd 0x0c68800241d0: fd fd fd fd fd fd fd fd fd fd fd fd fd fd fd fd 0x0c68800241e0: fd fd fd fd fd fd fd fd fd fd fd fd fd fd fd fd Shadow byte legend (one shadow byte represents 8 application bytes): Addressable: 00 Partially addressable: 01 02 03 04 05 06 07 Heap left redzone: fa Freed heap region: fd Stack left redzone: f1 Stack mid redzone: f2 Stack right redzone: f3 Stack after return: f5 Stack use after scope: f8 Global redzone: f9 Global init order: f6 Poisoned by user: f7 Container overflow: fc Array cookie: ac Intra object redzone: bb ASan internal: fe Left alloca redzone: ca Right alloca redzone: cb ==6797==ABORTING ----------SERVER LOG END-------------
            alice Alice Sherepa made changes -
            Affects Version/s 5.5 [ 15800 ]
            Affects Version/s 10.1 [ 16100 ]
            Affects Version/s 10.0 [ 16000 ]
            alice Alice Sherepa added a comment -

            Reproducible with InnoDB/Myisam, also if there is prepared statement instead of sp:

            CREATE TABLE t1 (id int, d2 datetime, id1 int) ;
            		 
            insert into t1 values (1,'2020-01-01 10:10:10',1),(2,'2020-01-01 10:10:10',2),(3,'2020-01-01 10:10:10',3);
            		 
             
            		 
            CREATE TABLE t2 (id int, d1 datetime, id1 int) ;
            		 
            insert into t2 values (1,'2020-01-01 10:10:10',1),(2,'2020-01-01 10:10:10',2),(3,'2020-01-01 10:10:10',2);
            		 
             
            		 
            prepare stmt from "
            		 
            SELECT * from 
            	(SELECT min(d2) AS d2, min(d1) AS d1 FROM 
            		(SELECT  t1.d2 AS d2, (SELECT t2.d1 FROM t2 WHERE t1.id1 = t2.id1 ORDER BY t2.id DESC LIMIT 1) AS d1 
               		FROM t1 ) dt2 ) ca
            		 
            ORDER BY ca.d2;";
            		 
             
            		 
            execute stmt;
            		 
            execute stmt;


            Version: '10.3.29-MariaDB-debug-log'  socket: '/10.3/mysql-test/var/tmp/mysqld.1.sock'  port: 16000  Source distribution
            		 
            =================================================================
            		 
            ==7422==ERROR: AddressSanitizer: heap-use-after-free on address 0x633000070881 at pc 0x55ec180364cd bp 0x7fbe3b274fc0 sp 0x7fbe3b274fb8
            		 
            READ of size 1 at 0x633000070881 thread T5
            		 
                #0 0x55ec180364cc in heap_rrnd /10.3/storage/heap/hp_rrnd.c:40
            		 
                #1 0x55ec18020ffa in ha_heap::rnd_pos(unsigned char*, unsigned char*) /10.3/storage/heap/ha_heap.cc:353
            		 
                #2 0x55ec17aba552 in handler::ha_rnd_pos(unsigned char*, unsigned char*) /10.3/sql/handler.cc:2881
            		 
                #3 0x55ec17edc8b5 in rr_from_pointers(READ_RECORD*) /10.3/sql/records.cc:551
            		 
                #4 0x55ec17169b31 in READ_RECORD::read_record() /10.3/sql/records.h:70
            		 
                #5 0x55ec17448982 in join_init_read_record(st_join_table*) /10.3/sql/sql_select.cc:20739
            		 
                #6 0x55ec17440f81 in sub_select(JOIN*, st_join_table*, bool) /10.3/sql/sql_select.cc:19800
            		 
                #7 0x55ec1743ee45 in do_select /10.3/sql/sql_select.cc:19343
            		 
                #8 0x55ec173cf337 in JOIN::exec_inner() /10.3/sql/sql_select.cc:4125
            		 
                #9 0x55ec173ccd0f in JOIN::exec() /10.3/sql/sql_select.cc:3919
            		 
                #10 0x55ec173d0645 in mysql_select(THD*, TABLE_LIST*, unsigned int, List<Item>&, Item*, unsigned int, st_order*, st_order*, Item*, st_order*, unsigned long long, select_result*, st_select_lex_unit*, st_select_lex*) /10.3/sql/sql_select.cc:4327
            		 
                #11 0x55ec173a70d5 in handle_select(THD*, LEX*, select_result*, unsigned long) /10.3/sql/sql_select.cc:370
            		 
                #12 0x55ec17312df3 in execute_sqlcom_select /10.3/sql/sql_parse.cc:6342
            		 
                #13 0x55ec17300dfc in mysql_execute_command(THD*) /10.3/sql/sql_parse.cc:3873
            		 
                #14 0x55ec17371107 in Prepared_statement::execute(String*, bool) /10.3/sql/sql_prepare.cc:5023
            		 
                #15 0x55ec1736c1cc in Prepared_statement::execute_loop(String*, bool, unsigned char*, unsigned char*) /10.3/sql/sql_prepare.cc:4451
            		 
                #16 0x55ec17365fec in mysql_sql_stmt_execute(THD*) /10.3/sql/sql_prepare.cc:3543
            		 
                #17 0x55ec17300e41 in mysql_execute_command(THD*) /10.3/sql/sql_parse.cc:3889
            		 
                #18 0x55ec1731cefe in mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool) /10.3/sql/sql_parse.cc:7871
            		 
                #19 0x55ec172f35cc in dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool) /10.3/sql/sql_parse.cc:1852
            		 
                #20 0x55ec172efd07 in do_command(THD*) /10.3/sql/sql_parse.cc:1398
            		 
                #21 0x55ec176e2030 in do_handle_one_connection(CONNECT*) /10.3/sql/sql_connect.cc:1403
            		 
                #22 0x55ec176e18e8 in handle_one_connection /10.3/sql/sql_connect.cc:1308
            		 
                #23 0x55ec18e51782 in pfs_spawn_thread /10.3/storage/perfschema/pfs.cc:1869
            		 
                #24 0x7fbe45b74fa2 in start_thread /build/glibc-vjB4T1/glibc-2.28/nptl/pthread_create.c:486
            		 
                #25 0x7fbe454f84ce in clone (/lib/x86_64-linux-gnu/libc.so.6+0xf94ce)
            		 
             
            		 
            0x633000070881 is located 129 bytes inside of 104972-byte region [0x633000070800,0x63300008a20c)
            		 
            freed by thread T5 here:
            		 
                #0 0x7fbe45c76fb0 in __interceptor_free (/usr/lib/x86_64-linux-gnu/libasan.so.5+0xe8fb0)
            		 
                #1 0x55ec19066a26 in free_memory /10.3/mysys/safemalloc.c:279
            		 
                #2 0x55ec19066054 in sf_free /10.3/mysys/safemalloc.c:197
            		 
                #3 0x55ec19035a5b in my_free /10.3/mysys/my_malloc.c:223
            		 
                #4 0x55ec1803cde8 in hp_free_level /10.3/storage/heap/hp_block.c:151
            		 
                #5 0x55ec1803cf76 in hp_clear /10.3/storage/heap/hp_clear.c:35
            		 
                #6 0x55ec1803ce40 in heap_clear /10.3/storage/heap/hp_clear.c:27
            		 
                #7 0x55ec180217d1 in ha_heap::delete_all_rows() /10.3/storage/heap/ha_heap.cc:404
            		 
                #8 0x55ec17ac847f in handler::ha_delete_all_rows() /10.3/sql/handler.cc:4437
            		 
                #9 0x55ec1723c3c3 in mysql_derived_fill(THD*, LEX*, TABLE_LIST*) /10.3/sql/sql_derived.cc:1118
            		 
                #10 0x55ec17235ea0 in mysql_handle_single_derived(LEX*, TABLE_LIST*, unsigned int) /10.3/sql/sql_derived.cc:199
            		 
                #11 0x55ec174105ac in st_join_table::preread_init() /10.3/sql/sql_select.cc:12851
            		 
                #12 0x55ec17448840 in join_init_read_record(st_join_table*) /10.3/sql/sql_select.cc:20734
            		 
                #13 0x55ec17440f81 in sub_select(JOIN*, st_join_table*, bool) /10.3/sql/sql_select.cc:19800
            		 
                #14 0x55ec1743ee45 in do_select /10.3/sql/sql_select.cc:19343
            		 
                #15 0x55ec173cf337 in JOIN::exec_inner() /10.3/sql/sql_select.cc:4125
            		 
                #16 0x55ec173ccd0f in JOIN::exec() /10.3/sql/sql_select.cc:3919
            		 
                #17 0x55ec173d0645 in mysql_select(THD*, TABLE_LIST*, unsigned int, List<Item>&, Item*, unsigned int, st_order*, st_order*, Item*, st_order*, unsigned long long, select_result*, st_select_lex_unit*, st_select_lex*) /10.3/sql/sql_select.cc:4327
            		 
                #18 0x55ec173a70d5 in handle_select(THD*, LEX*, select_result*, unsigned long) /10.3/sql/sql_select.cc:370
            		 
                #19 0x55ec17312df3 in execute_sqlcom_select /10.3/sql/sql_parse.cc:6342
            		 
                #20 0x55ec17300dfc in mysql_execute_command(THD*) /10.3/sql/sql_parse.cc:3873
            		 
                #21 0x55ec17371107 in Prepared_statement::execute(String*, bool) /10.3/sql/sql_prepare.cc:5023
            		 
                #22 0x55ec1736c1cc in Prepared_statement::execute_loop(String*, bool, unsigned char*, unsigned char*) /10.3/sql/sql_prepare.cc:4451
            		 
                #23 0x55ec17365fec in mysql_sql_stmt_execute(THD*) /10.3/sql/sql_prepare.cc:3543
            		 
                #24 0x55ec17300e41 in mysql_execute_command(THD*) /10.3/sql/sql_parse.cc:3889
            		 
                #25 0x55ec1731cefe in mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool) /10.3/sql/sql_parse.cc:7871
            		 
                #26 0x55ec172f35cc in dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool) /10.3/sql/sql_parse.cc:1852
            		 
                #27 0x55ec172efd07 in do_command(THD*) /10.3/sql/sql_parse.cc:1398
            		 
                #28 0x55ec176e2030 in do_handle_one_connection(CONNECT*) /10.3/sql/sql_connect.cc:1403
            		 
                #29 0x55ec176e18e8 in handle_one_connection /10.3/sql/sql_connect.cc:1308
            		 
             
            		 
            previously allocated by thread T5 here:
            		 
                #0 0x7fbe45c77330 in __interceptor_malloc (/usr/lib/x86_64-linux-gnu/libasan.so.5+0xe9330)
            		 
                #1 0x55ec19065a3a in sf_malloc /10.3/mysys/safemalloc.c:118
            		 
                #2 0x55ec19034f98 in my_malloc /10.3/mysys/my_malloc.c:101
            		 
                #3 0x55ec1803c73a in hp_get_new_block /10.3/storage/heap/hp_block.c:81
            		 
                #4 0x55ec180397cc in next_free_record_pos /10.3/storage/heap/hp_write.c:163
            		 
                #5 0x55ec180382fb in heap_write /10.3/storage/heap/hp_write.c:45
            		 
                #6 0x55ec1801fdb0 in ha_heap::write_row(unsigned char*) /10.3/storage/heap/ha_heap.cc:235
            		 
                #7 0x55ec1748b94e in handler::ha_write_tmp_row(unsigned char*) /10.3/sql/sql_class.h:6494
            		 
                #8 0x55ec175a6db2 in select_unit::send_data(List<Item>&) /10.3/sql/sql_union.cc:149
            		 
                #9 0x55ec1744cbe7 in end_send_group(JOIN*, st_join_table*, bool) /10.3/sql/sql_select.cc:21131
            		 
                #10 0x55ec17440866 in sub_select(JOIN*, st_join_table*, bool) /10.3/sql/sql_select.cc:19754
            		 
                #11 0x55ec1743ef47 in do_select /10.3/sql/sql_select.cc:19345
            		 
                #12 0x55ec173cf337 in JOIN::exec_inner() /10.3/sql/sql_select.cc:4125
            		 
                #13 0x55ec173ccd0f in JOIN::exec() /10.3/sql/sql_select.cc:3919
            		 
                #14 0x55ec173d0645 in mysql_select(THD*, TABLE_LIST*, unsigned int, List<Item>&, Item*, unsigned int, st_order*, st_order*, Item*, st_order*, unsigned long long, select_result*, st_select_lex_unit*, st_select_lex*) /10.3/sql/sql_select.cc:4327
            		 
                #15 0x55ec1723cb30 in mysql_derived_fill(THD*, LEX*, TABLE_LIST*) /10.3/sql/sql_derived.cc:1157
            		 
                #16 0x55ec17235ea0 in mysql_handle_single_derived(LEX*, TABLE_LIST*, unsigned int) /10.3/sql/sql_derived.cc:199
            		 
                #17 0x55ec174105ac in st_join_table::preread_init() /10.3/sql/sql_select.cc:12851
            		 
                #18 0x55ec1745a06b in create_sort_index(THD*, JOIN*, st_join_table*, Filesort*) /10.3/sql/sql_select.cc:22961
            		 
                #19 0x55ec17448f70 in st_join_table::sort_table() /10.3/sql/sql_select.cc:20778
            		 
                #20 0x55ec1744847a in join_init_read_record(st_join_table*) /10.3/sql/sql_select.cc:20719
            		 
                #21 0x55ec17440f81 in sub_select(JOIN*, st_join_table*, bool) /10.3/sql/sql_select.cc:19800
            		 
                #22 0x55ec1743ee45 in do_select /10.3/sql/sql_select.cc:19343
            		 
                #23 0x55ec173cf337 in JOIN::exec_inner() /10.3/sql/sql_select.cc:4125
            		 
                #24 0x55ec173ccd0f in JOIN::exec() /10.3/sql/sql_select.cc:3919
            		 
                #25 0x55ec173d0645 in mysql_select(THD*, TABLE_LIST*, unsigned int, List<Item>&, Item*, unsigned int, st_order*, st_order*, Item*, st_order*, unsigned long long, select_result*, st_select_lex_unit*, st_select_lex*) /10.3/sql/sql_select.cc:4327
            		 
                #26 0x55ec173a70d5 in handle_select(THD*, LEX*, select_result*, unsigned long) /10.3/sql/sql_select.cc:370
            		 
                #27 0x55ec17312df3 in execute_sqlcom_select /10.3/sql/sql_parse.cc:6342
            		 
                #28 0x55ec17300dfc in mysql_execute_command(THD*) /10.3/sql/sql_parse.cc:3873
            		 
                #29 0x55ec17371107 in Prepared_statement::execute(String*, bool) /10.3/sql/sql_prepare.cc:5023
            		 
             
            		 
            Thread T5 created by T0 here:
            		 
                #0 0x7fbe45bdedb0 in __interceptor_pthread_create (/usr/lib/x86_64-linux-gnu/libasan.so.5+0x50db0)
            		 
                #1 0x55ec18e51bbe in spawn_thread_v1 /10.3/storage/perfschema/pfs.cc:1919
            		 
                #2 0x55ec170026f4 in inline_mysql_thread_create /10.3/include/mysql/psi/mysql_thread.h:1275
            		 
                #3 0x55ec1701bfe3 in create_thread_to_handle_connection(CONNECT*) /10.3/sql/mysqld.cc:6664
            		 
                #4 0x55ec1701c738 in create_new_thread /10.3/sql/mysqld.cc:6734
            		 
                #5 0x55ec1701d8b9 in handle_connections_sockets() /10.3/sql/mysqld.cc:6992
            		 
                #6 0x55ec1701b357 in mysqld_main(int, char**) /10.3/sql/mysqld.cc:6286
            		 
                #7 0x55ec17000df4 in main /10.3/sql/main.cc:25
            		 
                #8 0x7fbe4542309a in __libc_start_main ../csu/libc-start.c:308
            		 
             
            		 
            SUMMARY: AddressSanitizer: heap-use-after-free /10.3/storage/heap/hp_rrnd.c:40 in heap_rrnd
            		 
            Shadow bytes around the buggy address:
            		 
              0x0c66800060c0: fa fa fa fa fa fa fa fa fa fa fa fa fa fa fa fa
            		 
              0x0c66800060d0: fa fa fa fa fa fa fa fa fa fa fa fa fa fa fa fa
            		 
              0x0c66800060e0: fa fa fa fa fa fa fa fa fa fa fa fa fa fa fa fa
            		 
              0x0c66800060f0: fa fa fa fa fa fa fa fa fa fa fa fa fa fa fa fa
            		 
              0x0c6680006100: fd fd fd fd fd fd fd fd fd fd fd fd fd fd fd fd
            		 
            =>0x0c6680006110:[fd]fd fd fd fd fd fd fd fd fd fd fd fd fd fd fd
            		 
              0x0c6680006120: fd fd fd fd fd fd fd fd fd fd fd fd fd fd fd fd
            		 
              0x0c6680006130: fd fd fd fd fd fd fd fd fd fd fd fd fd fd fd fd
            		 
              0x0c6680006140: fd fd fd fd fd fd fd fd fd fd fd fd fd fd fd fd
            		 
              0x0c6680006150: fd fd fd fd fd fd fd fd fd fd fd fd fd fd fd fd
            		 
              0x0c6680006160: fd fd fd fd fd fd fd fd fd fd fd fd fd fd fd fd
            		 
            Shadow byte legend (one shadow byte represents 8 application bytes):
            		 
              Addressable:           00
            		 
              Partially addressable: 01 02 03 04 05 06 07 
              Heap left redzone:       fa
            		 
              Freed heap region:       fd
            		 
              Stack left redzone:      f1
            		 
              Stack mid redzone:       f2
            		 
              Stack right redzone:     f3
            		 
              Stack after return:      f5
            		 
              Stack use after scope:   f8
            		 
              Global redzone:          f9
            		 
              Global init order:       f6
            		 
              Poisoned by user:        f7
            		 
              Container overflow:      fc
            		 
              Array cookie:            ac
            		 
              Intra object redzone:    bb
            		 
              ASan internal:           fe
            		 
              Left alloca redzone:     ca
            		 
              Right alloca redzone:    cb
            		 
            ==7422==ABORTING
            		 
            ----------SERVER LOG END-------------

            alice Alice Sherepa added a comment - Reproducible with InnoDB/Myisam, also if there is prepared statement instead of sp: CREATE TABLE t1 (id int , d2 datetime, id1 int ) ; insert into t1 values (1, '2020-01-01 10:10:10' ,1),(2, '2020-01-01 10:10:10' ,2),(3, '2020-01-01 10:10:10' ,3);   CREATE TABLE t2 (id int , d1 datetime, id1 int ) ; insert into t2 values (1, '2020-01-01 10:10:10' ,1),(2, '2020-01-01 10:10:10' ,2),(3, '2020-01-01 10:10:10' ,2);   prepare stmt from " SELECT * from (SELECT min(d2) AS d2, min(d1) AS d1 FROM (SELECT t1.d2 AS d2, (SELECT t2.d1 FROM t2 WHERE t1.id1 = t2.id1 ORDER BY t2.id DESC LIMIT 1) AS d1 FROM t1 ) dt2 ) ca ORDER BY ca.d2;" ;   execute stmt; execute stmt; Version: '10.3.29-MariaDB-debug-log' socket: '/10.3/mysql-test/var/tmp/mysqld.1.sock' port: 16000 Source distribution ================================================================= ==7422==ERROR: AddressSanitizer: heap-use-after-free on address 0x633000070881 at pc 0x55ec180364cd bp 0x7fbe3b274fc0 sp 0x7fbe3b274fb8 READ of size 1 at 0x633000070881 thread T5 #0 0x55ec180364cc in heap_rrnd /10.3/storage/heap/hp_rrnd.c:40 #1 0x55ec18020ffa in ha_heap::rnd_pos(unsigned char*, unsigned char*) /10.3/storage/heap/ha_heap.cc:353 #2 0x55ec17aba552 in handler::ha_rnd_pos(unsigned char*, unsigned char*) /10.3/sql/handler.cc:2881 #3 0x55ec17edc8b5 in rr_from_pointers(READ_RECORD*) /10.3/sql/records.cc:551 #4 0x55ec17169b31 in READ_RECORD::read_record() /10.3/sql/records.h:70 #5 0x55ec17448982 in join_init_read_record(st_join_table*) /10.3/sql/sql_select.cc:20739 #6 0x55ec17440f81 in sub_select(JOIN*, st_join_table*, bool) /10.3/sql/sql_select.cc:19800 #7 0x55ec1743ee45 in do_select /10.3/sql/sql_select.cc:19343 #8 0x55ec173cf337 in JOIN::exec_inner() /10.3/sql/sql_select.cc:4125 #9 0x55ec173ccd0f in JOIN::exec() /10.3/sql/sql_select.cc:3919 #10 0x55ec173d0645 in mysql_select(THD*, TABLE_LIST*, unsigned int, List<Item>&, Item*, unsigned int, st_order*, st_order*, Item*, st_order*, unsigned long long, select_result*, st_select_lex_unit*, st_select_lex*) /10.3/sql/sql_select.cc:4327 #11 0x55ec173a70d5 in handle_select(THD*, LEX*, select_result*, unsigned long) /10.3/sql/sql_select.cc:370 #12 0x55ec17312df3 in execute_sqlcom_select /10.3/sql/sql_parse.cc:6342 #13 0x55ec17300dfc in mysql_execute_command(THD*) /10.3/sql/sql_parse.cc:3873 #14 0x55ec17371107 in Prepared_statement::execute(String*, bool) /10.3/sql/sql_prepare.cc:5023 #15 0x55ec1736c1cc in Prepared_statement::execute_loop(String*, bool, unsigned char*, unsigned char*) /10.3/sql/sql_prepare.cc:4451 #16 0x55ec17365fec in mysql_sql_stmt_execute(THD*) /10.3/sql/sql_prepare.cc:3543 #17 0x55ec17300e41 in mysql_execute_command(THD*) /10.3/sql/sql_parse.cc:3889 #18 0x55ec1731cefe in mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool) /10.3/sql/sql_parse.cc:7871 #19 0x55ec172f35cc in dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool) /10.3/sql/sql_parse.cc:1852 #20 0x55ec172efd07 in do_command(THD*) /10.3/sql/sql_parse.cc:1398 #21 0x55ec176e2030 in do_handle_one_connection(CONNECT*) /10.3/sql/sql_connect.cc:1403 #22 0x55ec176e18e8 in handle_one_connection /10.3/sql/sql_connect.cc:1308 #23 0x55ec18e51782 in pfs_spawn_thread /10.3/storage/perfschema/pfs.cc:1869 #24 0x7fbe45b74fa2 in start_thread /build/glibc-vjB4T1/glibc-2.28/nptl/pthread_create.c:486 #25 0x7fbe454f84ce in clone (/lib/x86_64-linux-gnu/libc.so.6+0xf94ce)   0x633000070881 is located 129 bytes inside of 104972-byte region [0x633000070800,0x63300008a20c) freed by thread T5 here: #0 0x7fbe45c76fb0 in __interceptor_free (/usr/lib/x86_64-linux-gnu/libasan.so.5+0xe8fb0) #1 0x55ec19066a26 in free_memory /10.3/mysys/safemalloc.c:279 #2 0x55ec19066054 in sf_free /10.3/mysys/safemalloc.c:197 #3 0x55ec19035a5b in my_free /10.3/mysys/my_malloc.c:223 #4 0x55ec1803cde8 in hp_free_level /10.3/storage/heap/hp_block.c:151 #5 0x55ec1803cf76 in hp_clear /10.3/storage/heap/hp_clear.c:35 #6 0x55ec1803ce40 in heap_clear /10.3/storage/heap/hp_clear.c:27 #7 0x55ec180217d1 in ha_heap::delete_all_rows() /10.3/storage/heap/ha_heap.cc:404 #8 0x55ec17ac847f in handler::ha_delete_all_rows() /10.3/sql/handler.cc:4437 #9 0x55ec1723c3c3 in mysql_derived_fill(THD*, LEX*, TABLE_LIST*) /10.3/sql/sql_derived.cc:1118 #10 0x55ec17235ea0 in mysql_handle_single_derived(LEX*, TABLE_LIST*, unsigned int) /10.3/sql/sql_derived.cc:199 #11 0x55ec174105ac in st_join_table::preread_init() /10.3/sql/sql_select.cc:12851 #12 0x55ec17448840 in join_init_read_record(st_join_table*) /10.3/sql/sql_select.cc:20734 #13 0x55ec17440f81 in sub_select(JOIN*, st_join_table*, bool) /10.3/sql/sql_select.cc:19800 #14 0x55ec1743ee45 in do_select /10.3/sql/sql_select.cc:19343 #15 0x55ec173cf337 in JOIN::exec_inner() /10.3/sql/sql_select.cc:4125 #16 0x55ec173ccd0f in JOIN::exec() /10.3/sql/sql_select.cc:3919 #17 0x55ec173d0645 in mysql_select(THD*, TABLE_LIST*, unsigned int, List<Item>&, Item*, unsigned int, st_order*, st_order*, Item*, st_order*, unsigned long long, select_result*, st_select_lex_unit*, st_select_lex*) /10.3/sql/sql_select.cc:4327 #18 0x55ec173a70d5 in handle_select(THD*, LEX*, select_result*, unsigned long) /10.3/sql/sql_select.cc:370 #19 0x55ec17312df3 in execute_sqlcom_select /10.3/sql/sql_parse.cc:6342 #20 0x55ec17300dfc in mysql_execute_command(THD*) /10.3/sql/sql_parse.cc:3873 #21 0x55ec17371107 in Prepared_statement::execute(String*, bool) /10.3/sql/sql_prepare.cc:5023 #22 0x55ec1736c1cc in Prepared_statement::execute_loop(String*, bool, unsigned char*, unsigned char*) /10.3/sql/sql_prepare.cc:4451 #23 0x55ec17365fec in mysql_sql_stmt_execute(THD*) /10.3/sql/sql_prepare.cc:3543 #24 0x55ec17300e41 in mysql_execute_command(THD*) /10.3/sql/sql_parse.cc:3889 #25 0x55ec1731cefe in mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool) /10.3/sql/sql_parse.cc:7871 #26 0x55ec172f35cc in dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool) /10.3/sql/sql_parse.cc:1852 #27 0x55ec172efd07 in do_command(THD*) /10.3/sql/sql_parse.cc:1398 #28 0x55ec176e2030 in do_handle_one_connection(CONNECT*) /10.3/sql/sql_connect.cc:1403 #29 0x55ec176e18e8 in handle_one_connection /10.3/sql/sql_connect.cc:1308   previously allocated by thread T5 here: #0 0x7fbe45c77330 in __interceptor_malloc (/usr/lib/x86_64-linux-gnu/libasan.so.5+0xe9330) #1 0x55ec19065a3a in sf_malloc /10.3/mysys/safemalloc.c:118 #2 0x55ec19034f98 in my_malloc /10.3/mysys/my_malloc.c:101 #3 0x55ec1803c73a in hp_get_new_block /10.3/storage/heap/hp_block.c:81 #4 0x55ec180397cc in next_free_record_pos /10.3/storage/heap/hp_write.c:163 #5 0x55ec180382fb in heap_write /10.3/storage/heap/hp_write.c:45 #6 0x55ec1801fdb0 in ha_heap::write_row(unsigned char*) /10.3/storage/heap/ha_heap.cc:235 #7 0x55ec1748b94e in handler::ha_write_tmp_row(unsigned char*) /10.3/sql/sql_class.h:6494 #8 0x55ec175a6db2 in select_unit::send_data(List<Item>&) /10.3/sql/sql_union.cc:149 #9 0x55ec1744cbe7 in end_send_group(JOIN*, st_join_table*, bool) /10.3/sql/sql_select.cc:21131 #10 0x55ec17440866 in sub_select(JOIN*, st_join_table*, bool) /10.3/sql/sql_select.cc:19754 #11 0x55ec1743ef47 in do_select /10.3/sql/sql_select.cc:19345 #12 0x55ec173cf337 in JOIN::exec_inner() /10.3/sql/sql_select.cc:4125 #13 0x55ec173ccd0f in JOIN::exec() /10.3/sql/sql_select.cc:3919 #14 0x55ec173d0645 in mysql_select(THD*, TABLE_LIST*, unsigned int, List<Item>&, Item*, unsigned int, st_order*, st_order*, Item*, st_order*, unsigned long long, select_result*, st_select_lex_unit*, st_select_lex*) /10.3/sql/sql_select.cc:4327 #15 0x55ec1723cb30 in mysql_derived_fill(THD*, LEX*, TABLE_LIST*) /10.3/sql/sql_derived.cc:1157 #16 0x55ec17235ea0 in mysql_handle_single_derived(LEX*, TABLE_LIST*, unsigned int) /10.3/sql/sql_derived.cc:199 #17 0x55ec174105ac in st_join_table::preread_init() /10.3/sql/sql_select.cc:12851 #18 0x55ec1745a06b in create_sort_index(THD*, JOIN*, st_join_table*, Filesort*) /10.3/sql/sql_select.cc:22961 #19 0x55ec17448f70 in st_join_table::sort_table() /10.3/sql/sql_select.cc:20778 #20 0x55ec1744847a in join_init_read_record(st_join_table*) /10.3/sql/sql_select.cc:20719 #21 0x55ec17440f81 in sub_select(JOIN*, st_join_table*, bool) /10.3/sql/sql_select.cc:19800 #22 0x55ec1743ee45 in do_select /10.3/sql/sql_select.cc:19343 #23 0x55ec173cf337 in JOIN::exec_inner() /10.3/sql/sql_select.cc:4125 #24 0x55ec173ccd0f in JOIN::exec() /10.3/sql/sql_select.cc:3919 #25 0x55ec173d0645 in mysql_select(THD*, TABLE_LIST*, unsigned int, List<Item>&, Item*, unsigned int, st_order*, st_order*, Item*, st_order*, unsigned long long, select_result*, st_select_lex_unit*, st_select_lex*) /10.3/sql/sql_select.cc:4327 #26 0x55ec173a70d5 in handle_select(THD*, LEX*, select_result*, unsigned long) /10.3/sql/sql_select.cc:370 #27 0x55ec17312df3 in execute_sqlcom_select /10.3/sql/sql_parse.cc:6342 #28 0x55ec17300dfc in mysql_execute_command(THD*) /10.3/sql/sql_parse.cc:3873 #29 0x55ec17371107 in Prepared_statement::execute(String*, bool) /10.3/sql/sql_prepare.cc:5023   Thread T5 created by T0 here: #0 0x7fbe45bdedb0 in __interceptor_pthread_create (/usr/lib/x86_64-linux-gnu/libasan.so.5+0x50db0) #1 0x55ec18e51bbe in spawn_thread_v1 /10.3/storage/perfschema/pfs.cc:1919 #2 0x55ec170026f4 in inline_mysql_thread_create /10.3/include/mysql/psi/mysql_thread.h:1275 #3 0x55ec1701bfe3 in create_thread_to_handle_connection(CONNECT*) /10.3/sql/mysqld.cc:6664 #4 0x55ec1701c738 in create_new_thread /10.3/sql/mysqld.cc:6734 #5 0x55ec1701d8b9 in handle_connections_sockets() /10.3/sql/mysqld.cc:6992 #6 0x55ec1701b357 in mysqld_main(int, char**) /10.3/sql/mysqld.cc:6286 #7 0x55ec17000df4 in main /10.3/sql/main.cc:25 #8 0x7fbe4542309a in __libc_start_main ../csu/libc-start.c:308   SUMMARY: AddressSanitizer: heap-use-after-free /10.3/storage/heap/hp_rrnd.c:40 in heap_rrnd Shadow bytes around the buggy address: 0x0c66800060c0: fa fa fa fa fa fa fa fa fa fa fa fa fa fa fa fa 0x0c66800060d0: fa fa fa fa fa fa fa fa fa fa fa fa fa fa fa fa 0x0c66800060e0: fa fa fa fa fa fa fa fa fa fa fa fa fa fa fa fa 0x0c66800060f0: fa fa fa fa fa fa fa fa fa fa fa fa fa fa fa fa 0x0c6680006100: fd fd fd fd fd fd fd fd fd fd fd fd fd fd fd fd =>0x0c6680006110:[fd]fd fd fd fd fd fd fd fd fd fd fd fd fd fd fd 0x0c6680006120: fd fd fd fd fd fd fd fd fd fd fd fd fd fd fd fd 0x0c6680006130: fd fd fd fd fd fd fd fd fd fd fd fd fd fd fd fd 0x0c6680006140: fd fd fd fd fd fd fd fd fd fd fd fd fd fd fd fd 0x0c6680006150: fd fd fd fd fd fd fd fd fd fd fd fd fd fd fd fd 0x0c6680006160: fd fd fd fd fd fd fd fd fd fd fd fd fd fd fd fd Shadow byte legend (one shadow byte represents 8 application bytes): Addressable: 00 Partially addressable: 01 02 03 04 05 06 07 Heap left redzone: fa Freed heap region: fd Stack left redzone: f1 Stack mid redzone: f2 Stack right redzone: f3 Stack after return: f5 Stack use after scope: f8 Global redzone: f9 Global init order: f6 Poisoned by user: f7 Container overflow: fc Array cookie: ac Intra object redzone: bb ASan internal: fe Left alloca redzone: ca Right alloca redzone: cb ==7422==ABORTING ----------SERVER LOG END-------------
            alice Alice Sherepa made changes -
            Status Open [ 1 ] Confirmed [ 10101 ]
            alice Alice Sherepa made changes -
            Assignee Oleksandr Byelkin [ sanja ]
            serg Sergei Golubchik made changes -
            Description When running the complex query repeatedly in the stored procedure random garbage or empty results are returned on occasion.

            Data dump with SP and test are contained in attachment. With the SP rewritten to store the output in a table the results that are correct and incorrect are as follows:

            +-------------------+--------------+-----------------+------------+----------------------+-------------------------+-----------------------+--------------+-----------------+--------+----------+----------------+---------------------+
            | customerReference | policyNumber | schemeReference | schemeName | applicationReference | modifiedCreatedDateTime | demandCreatedDateTime | countDemands | genericDemandId | status | isUrgent | reactivateDate | isBusinessException |
            +-------------------+--------------+-----------------+------------+----------------------+-------------------------+-----------------------+--------------+-----------------+--------+----------+----------------+---------------------+
            | reference1 | 1 | NULL | NULL | NULL | 2020-01-01 10:10:10 | 2020-01-01 10:10:10 | 1 | 1 | 1 | 0 | NULL | 0 |
            +-------------------+--------------+-----------------+------------+----------------------+-------------------------+-----------------------+--------------+-----------------+--------+----------+----------------+---------------------+

            Under high concurrency (should be a static result) from 10000 iterations the following rows were inserted:
            mysql ds2 -e "select * from results where customerReference<>'reference1'"
            +--------------------------------+--------------+-----------------+------------+----------------------------------------------------+-------------------------+-----------------------+--------------+-----------------+--------+----------+---------------------+---------------------+
            | customerReference | policyNumber | schemeReference | schemeName | applicationReference | modifiedCreatedDateTime | demandCreatedDateTime | countDemands | genericDemandId | status | isUrgent | reactivateDate | isBusinessException |
            +--------------------------------+--------------+-----------------+------------+----------------------------------------------------+-------------------------+-----------------------+--------------+-----------------+--------+----------+---------------------+---------------------+
            | w ( ?? 0??K | 0 | NULL | NULL | NULL | 2020-01-01 10:10:10 | NULL | 2147483647 | NULL | NULL | 0 | NULL | NULL |
            | | 0 | | | | 2020-01-01 10:10:10 | 2020-01-01 10:10:10 | 1 | 1 | 1 | 0 | 0000-00-00 00:00:00 | 0 |
            | w rence1 | 1 | | | | 2020-01-01 10:10:10 | 2020-01-01 10:10:10 | 1 | 1 | 1 | 0 | NULL | NULL |
            | w rence1 | 1 | | | | 2020-01-01 10:10:10 | 2020-01-01 10:10:10 | 1 | NULL | NULL | 0 | 0000-00-00 00:00:00 | NULL |
            | | 1 | | | | 2020-01-01 10:10:10 | 2020-01-01 10:10:10 | 1 | 1 | 1 | 0 | 0000-00-00 00:00:00 | 0 |
            | w rence1 | 1 | | NULL | NULL | 2020-01-01 10:10:10 | 2020-01-01 10:10:10 | 1 | NULL | 1 | NULL | NULL | NULL |
            | | 1 | | | | 2020-01-01 10:10:10 | 2020-01-01 10:10:10 | 1 | 1 | 1 | 0 | 0000-00-00 00:00:00 | 0 |
            | w rence1 | 1 | | | NULL | NULL | 2020-01-01 10:10:10 | 1 | NULL | NULL | NULL | 0000-00-00 00:00:00 | 0 |
            | | 1 | | | | 2020-01-01 10:10:10 | 2020-01-01 10:10:10 | 1 | 1 | 1 | 0 | 0000-00-00 00:00:00 | 0 |
            | w rence1 | 1 | | NULL | | 2020-01-01 10:10:10 | 2020-01-01 10:10:10 | 1 | 1 | 1 | 0 | NULL | NULL |
            | | 0 | | | | 2020-01-01 10:10:10 | 2020-01-01 10:10:10 | 1 | 1 | 1 | 0 | 0000-00-00 00:00:00 | 0 |
            | w rence1 | 1 | | NULL | | NULL | NULL | 1 | 1 | 1 | 0 | NULL | NULL |
            | | 1 | | | | 2020-01-01 10:10:10 | 2020-01-01 10:10:10 | 1 | 1 | 1 | 0 | 0000-00-00 00:00:00 | 0 |
            | w ( ?? 0??K | 0 | NULL | NULL | | 2020-01-01 10:10:10 | NULL | 2147483647 | 0 | 1 | 0 | 7036-87-44 17:76:64 | NULL |
            | w rence1 | 1 | | | NULL | 2020-01-01 10:10:10 | 2020-01-01 10:10:10 | 1 | 1 | NULL | NULL | 0000-00-00 00:00:00 | 0 |
            | | 1 | | | | 2020-01-01 10:10:10 | 2020-01-01 10:10:10 | 1 | 1 | 1 | 0 | 0000-00-00 00:00:00 | 0 |
            | | 1 | | | | 2020-01-01 10:10:10 | 2020-01-01 10:10:10 | 1 | 1 | 1 | 0 | 0000-00-00 00:00:00 | 0 |
            | | 0 | | | | 2020-01-01 10:10:10 | 2020-01-01 10:10:10 | 1 | 1 | 1 | 0 | 0000-00-00 00:00:00 | 0 |
            | | 0 | | | | 2020-01-01 10:10:10 | 2020-01-01 10:10:10 | 1 | 1 | 1 | 0 | 0000-00-00 00:00:00 | 0 |
            | | 0 | | | | 2020-01-01 10:10:10 | 2020-01-01 10:10:10 | 2147483647 | 0 | 1 | 0 | 0000-00-00 00:00:00 | 0 |
            | | 0 | | | ?K | 2020-01-01 10:10:10 | 2020-01-01 10:10:10 | 1 | 1 | 1 | 0 | 0000-00-00 00:00:00 | 0 |
            | | 1 | | | | 2020-01-01 10:10:10 | 2020-01-01 10:10:10 | 1 | 1 | 1 | 0 | 0000-00-00 00:00:00 | 0 |
            | | 1 | | | | 2020-01-01 10:10:10 | 2020-01-01 10:10:10 | 1 | 1 | 1 | 0 | 0000-00-00 00:00:00 | 0 |
            | | 1 | | | | 2020-01-01 10:10:10 | 2020-01-01 10:10:10 | 1 | 1 | 1 | 0 | 0000-00-00 00:00:00 | 0 |
            | | 1 | | | | 2020-01-01 10:10:10 | 2020-01-01 10:10:10 | 1 | 1 | 1 | 0 | 0000-00-00 00:00:00 | 0 |
            | | 1 | | | | 2020-01-01 10:10:10 | 2020-01-01 10:10:10 | 1 | 1 | 1 | 0 | 0000-00-00 00:00:00 | 0 |
            | | 1 | | | | 2020-01-01 10:10:10 | 2020-01-01 10:10:10 | 1 | 1 | 1 | 0 | 0000-00-00 00:00:00 | 0 |
            +--------------------------------+--------------+-----------------+------------+----------------------------------------------------+-------------------------+-----------------------+--------------+-----------------+--------+----------+---------------------+---------------------+


            		When running the complex query repeatedly in the stored procedure random garbage or empty results are returned on occasion.

            Data dump with SP and test are contained in attachment. With the SP rewritten to store the output in a table the results that are correct and incorrect are as follows:
            {noformat}
            +-------------------+--------------+-----------------+------------+----------------------+-------------------------+-----------------------+--------------+-----------------+--------+----------+----------------+---------------------+
            | customerReference | policyNumber | schemeReference | schemeName | applicationReference | modifiedCreatedDateTime | demandCreatedDateTime | countDemands | genericDemandId | status | isUrgent | reactivateDate | isBusinessException |
            +-------------------+--------------+-----------------+------------+----------------------+-------------------------+-----------------------+--------------+-----------------+--------+----------+----------------+---------------------+
            | reference1 | 1 | NULL | NULL | NULL | 2020-01-01 10:10:10 | 2020-01-01 10:10:10 | 1 | 1 | 1 | 0 | NULL | 0 |
            +-------------------+--------------+-----------------+------------+----------------------+-------------------------+-----------------------+--------------+-----------------+--------+----------+----------------+---------------------+
            {noformat}
            Under high concurrency (should be a static result) from 10000 iterations the following rows were inserted:
            {noformat}
            mysql ds2 -e "select * from results where customerReference<>'reference1'"
            +--------------------------------+--------------+-----------------+------------+----------------------------------------------------+-------------------------+-----------------------+--------------+-----------------+--------+----------+---------------------+---------------------+
            | customerReference | policyNumber | schemeReference | schemeName | applicationReference | modifiedCreatedDateTime | demandCreatedDateTime | countDemands | genericDemandId | status | isUrgent | reactivateDate | isBusinessException |
            +--------------------------------+--------------+-----------------+------------+----------------------------------------------------+-------------------------+-----------------------+--------------+-----------------+--------+----------+---------------------+---------------------+
            | w ( ?? 0??K | 0 | NULL | NULL | NULL | 2020-01-01 10:10:10 | NULL | 2147483647 | NULL | NULL | 0 | NULL | NULL |
            | | 0 | | | | 2020-01-01 10:10:10 | 2020-01-01 10:10:10 | 1 | 1 | 1 | 0 | 0000-00-00 00:00:00 | 0 |
            | w rence1 | 1 | | | | 2020-01-01 10:10:10 | 2020-01-01 10:10:10 | 1 | 1 | 1 | 0 | NULL | NULL |
            | w rence1 | 1 | | | | 2020-01-01 10:10:10 | 2020-01-01 10:10:10 | 1 | NULL | NULL | 0 | 0000-00-00 00:00:00 | NULL |
            | | 1 | | | | 2020-01-01 10:10:10 | 2020-01-01 10:10:10 | 1 | 1 | 1 | 0 | 0000-00-00 00:00:00 | 0 |
            | w rence1 | 1 | | NULL | NULL | 2020-01-01 10:10:10 | 2020-01-01 10:10:10 | 1 | NULL | 1 | NULL | NULL | NULL |
            | | 1 | | | | 2020-01-01 10:10:10 | 2020-01-01 10:10:10 | 1 | 1 | 1 | 0 | 0000-00-00 00:00:00 | 0 |
            | w rence1 | 1 | | | NULL | NULL | 2020-01-01 10:10:10 | 1 | NULL | NULL | NULL | 0000-00-00 00:00:00 | 0 |
            | | 1 | | | | 2020-01-01 10:10:10 | 2020-01-01 10:10:10 | 1 | 1 | 1 | 0 | 0000-00-00 00:00:00 | 0 |
            | w rence1 | 1 | | NULL | | 2020-01-01 10:10:10 | 2020-01-01 10:10:10 | 1 | 1 | 1 | 0 | NULL | NULL |
            | | 0 | | | | 2020-01-01 10:10:10 | 2020-01-01 10:10:10 | 1 | 1 | 1 | 0 | 0000-00-00 00:00:00 | 0 |
            | w rence1 | 1 | | NULL | | NULL | NULL | 1 | 1 | 1 | 0 | NULL | NULL |
            | | 1 | | | | 2020-01-01 10:10:10 | 2020-01-01 10:10:10 | 1 | 1 | 1 | 0 | 0000-00-00 00:00:00 | 0 |
            | w ( ?? 0??K | 0 | NULL | NULL | | 2020-01-01 10:10:10 | NULL | 2147483647 | 0 | 1 | 0 | 7036-87-44 17:76:64 | NULL |
            | w rence1 | 1 | | | NULL | 2020-01-01 10:10:10 | 2020-01-01 10:10:10 | 1 | 1 | NULL | NULL | 0000-00-00 00:00:00 | 0 |
            | | 1 | | | | 2020-01-01 10:10:10 | 2020-01-01 10:10:10 | 1 | 1 | 1 | 0 | 0000-00-00 00:00:00 | 0 |
            | | 1 | | | | 2020-01-01 10:10:10 | 2020-01-01 10:10:10 | 1 | 1 | 1 | 0 | 0000-00-00 00:00:00 | 0 |
            | | 0 | | | | 2020-01-01 10:10:10 | 2020-01-01 10:10:10 | 1 | 1 | 1 | 0 | 0000-00-00 00:00:00 | 0 |
            | | 0 | | | | 2020-01-01 10:10:10 | 2020-01-01 10:10:10 | 1 | 1 | 1 | 0 | 0000-00-00 00:00:00 | 0 |
            | | 0 | | | | 2020-01-01 10:10:10 | 2020-01-01 10:10:10 | 2147483647 | 0 | 1 | 0 | 0000-00-00 00:00:00 | 0 |
            | | 0 | | | ?K | 2020-01-01 10:10:10 | 2020-01-01 10:10:10 | 1 | 1 | 1 | 0 | 0000-00-00 00:00:00 | 0 |
            | | 1 | | | | 2020-01-01 10:10:10 | 2020-01-01 10:10:10 | 1 | 1 | 1 | 0 | 0000-00-00 00:00:00 | 0 |
            | | 1 | | | | 2020-01-01 10:10:10 | 2020-01-01 10:10:10 | 1 | 1 | 1 | 0 | 0000-00-00 00:00:00 | 0 |
            | | 1 | | | | 2020-01-01 10:10:10 | 2020-01-01 10:10:10 | 1 | 1 | 1 | 0 | 0000-00-00 00:00:00 | 0 |
            | | 1 | | | | 2020-01-01 10:10:10 | 2020-01-01 10:10:10 | 1 | 1 | 1 | 0 | 0000-00-00 00:00:00 | 0 |
            | | 1 | | | | 2020-01-01 10:10:10 | 2020-01-01 10:10:10 | 1 | 1 | 1 | 0 | 0000-00-00 00:00:00 | 0 |
            | | 1 | | | | 2020-01-01 10:10:10 | 2020-01-01 10:10:10 | 1 | 1 | 1 | 0 | 0000-00-00 00:00:00 | 0 |
            +--------------------------------+--------------+-----------------+------------+----------------------------------------------------+-------------------------+-----------------------+--------------+-----------------+--------+----------+---------------------+---------------------+
            {noformat}
            serg Sergei Golubchik made changes -
            Priority Critical [ 2 ] Blocker [ 1 ]
            sanja Oleksandr Byelkin made changes -
            Status Confirmed [ 10101 ] In Progress [ 3 ]
            sanja Oleksandr Byelkin added a comment -

            Try

            set optimizer_switch="derived_merge=off";

            as workaround

            sanja Oleksandr Byelkin added a comment - Try set optimizer_switch="derived_merge=off"; as workaround
            julien.fritsch Julien Fritsch made changes -
            Labels need_feedback
            ccalender Chris Calender (Inactive) made changes -
            Labels need_feedback
            sanja Oleksandr Byelkin added a comment -

            The problem is that subquery (selevct #2) marked dependent (it is also done in 10.2 but 10.2 does not react on the marking).

            The cause of marking is removing (merging) derived table (select #3). so on second execution st_select_lex::mark_as_dependent get parameters to go from select #4 to select #3 but after select #4 goes select #2 and so it does not stopp.

            The solution is go not by SELECTs tree but by name resolution context tree. (the fast and dirty patch fix the problem, but unexpectedly some times Item name contect does not point on current select which shoud be investigated.

            sanja Oleksandr Byelkin added a comment - The problem is that subquery (selevct #2) marked dependent (it is also done in 10.2 but 10.2 does not react on the marking). The cause of marking is removing (merging) derived table (select #3). so on second execution st_select_lex::mark_as_dependent get parameters to go from select #4 to select #3 but after select #4 goes select #2 and so it does not stopp. The solution is go not by SELECTs tree but by name resolution context tree. (the fast and dirty patch fix the problem, but unexpectedly some times Item name contect does not point on current select which shoud be investigated.
            sanja Oleksandr Byelkin made changes -
            Component/s Prepared Statements [ 10804 ]
            sanja Oleksandr Byelkin made changes -
            Summary Complex query in Store procedure corrupts results under high concurrency Complex query in Store procedure corrupts results
            sanja Oleksandr Byelkin added a comment -

            Both test suites are checked but only small included (because thay tests the same).

            sanja Oleksandr Byelkin added a comment - Both test suites are checked but only small included (because thay tests the same).
            sanja Oleksandr Byelkin made changes -
            Assignee Oleksandr Byelkin [ sanja ] Igor Babaev [ igor ]
            Status In Progress [ 3 ] In Review [ 10002 ]
            sanja Oleksandr Byelkin added a comment - 

            commit c4ae6b240abff7f3df2c76c8a3ffccc0775470c1 (HEAD -> bb-10.3-MDEV-25182, origin/bb-10.3-MDEV-25182)
            		 
            Author: Oleksandr Byelkin <sanja@mariadb.com>
            		 
            Date:   Wed Apr 7 12:59:43 2021 +0200
            		 
             
            		 
                MDEV-25182 Complex query in Store procedure corrupts results
            		 
                
                Problem was that on second execution marked more selects as needed:
            		 
                  merged derived removed from SELECT tree and limit (last) in
            		 
                  st_select_lex::mark_as_dependent is skipped.
            		 
                
                To avoid the problem we use name resolution context to go by it "up".
            		 
                
                NOTE: problem also exists in 10.2 but has no vosoble effect on execution.
            		 
                
                The patch also add debug logging of important procedures and
            		 
                 bettr specify parametrs types of st_select_lex::mark_as_dependent.

            sanja Oleksandr Byelkin added a comment - commit c4ae6b240abff7f3df2c76c8a3ffccc0775470c1 (HEAD -> bb-10.3-MDEV-25182, origin/bb-10.3-MDEV-25182) Author: Oleksandr Byelkin <sanja@mariadb.com> Date: Wed Apr 7 12:59:43 2021 +0200   MDEV-25182 Complex query in Store procedure corrupts results Problem was that on second execution marked more selects as needed: merged derived removed from SELECT tree and limit (last) in st_select_lex::mark_as_dependent is skipped. To avoid the problem we use name resolution context to go by it "up". NOTE: problem also exists in 10.2 but has no vosoble effect on execution. The patch also add debug logging of important procedures and bettr specify parametrs types of st_select_lex::mark_as_dependent.
            serg Sergei Golubchik added a comment -

            the actual crash in

                #0 in heap_rrnd /10.3/storage/heap/hp_rrnd.c:40
            		 
                #1 in ha_heap::rnd_pos(unsigned char*, unsigned char*) /10.3/storage/heap/ha_heap.cc:353
            		 
                #2 in handler::ha_rnd_pos(unsigned char*, unsigned char*) /10.3/sql/handler.cc:2881
            		 
                #3 in rr_from_pointers(READ_RECORD*) /10.3/sql/records.cc:551
            		 
                #4 in READ_RECORD::read_record() /10.3/sql/records.h:70
            		 
                #5 in join_init_read_record(st_join_table*) /10.3/sql/sql_select.cc:20739

            happens because first rows are written into a temporary HEAP table, then filesort (?) saves row positions (just pointers in memory) and sorts them. Then the table is emptied (with ha_heap::delete_all_rows) — which frees all memory and makes pointers to rows invalid, and then the executor uses rr_from_pointers (see above) that reads these now obsolete positions, and with ha_heap::rnd_pos tries to read the row, which is no longer there.

            serg Sergei Golubchik added a comment - the actual crash in #0 in heap_rrnd /10.3/storage/heap/hp_rrnd.c:40 #1 in ha_heap::rnd_pos(unsigned char*, unsigned char*) /10.3/storage/heap/ha_heap.cc:353 #2 in handler::ha_rnd_pos(unsigned char*, unsigned char*) /10.3/sql/handler.cc:2881 #3 in rr_from_pointers(READ_RECORD*) /10.3/sql/records.cc:551 #4 in READ_RECORD::read_record() /10.3/sql/records.h:70 #5 in join_init_read_record(st_join_table*) /10.3/sql/sql_select.cc:20739 happens because first rows are written into a temporary HEAP table, then filesort (?) saves row positions (just pointers in memory) and sorts them. Then the table is emptied (with ha_heap::delete_all_rows ) — which frees all memory and makes pointers to rows invalid, and then the executor uses rr_from_pointers (see above) that reads these now obsolete positions, and with ha_heap::rnd_pos tries to read the row, which is no longer there.
            igor Igor Babaev (Inactive) added a comment -

            More details how we come to the above crash/memory problems.
            After the first execution of the prepared statement (PS) all mergeable derived are already merged and the query of the following form is processed at the second execution of PS:

            select ca.d2 AS d2,ca.d1 AS d1 
              from (select min(t1.d2) AS d2,min((subquery#4)) AS d1 from test.t1) ca
            		 
             order by ca.d2

            Here's how the query is executed:
            1. The query is is a join containing only one table 'ca' whose result set requires sorting. So single-table join with join_tab 'ca' with join_tab->filesort defined is executed.
            2. As join_tab->join_tab->preread_init_done is false for 'ca' join_tab->preread_init() is called that checks that 'ca' is materialized derived and requires filling.
            3. mysql_derived_fill is called for 'ca' where mysql_select() is called for the select

            select min(t1.d2) AS d2,min((subquery#4)) AS d1 from test.t1

            4. Execution of the above query fills the temp table for 'ca'.
            5. As the unit for 'ca' is marked as uncacheable preread_init() does not set join_tab->preread_init_done to TRUE.
            6. join_init_read_record() is called for JOIN_TAB 'ca'. It sees that tab->filesort != NULL and calls sort_table() for 'ca'.
            7. sort_table() calls create_sort_index().
            8. As preread_init_done is still false for 'ca' preread_init() is called for 'ca'.
            9. As ca is marked as uncacheable mysql_derived_fill() is called for'ca'. This is not good by itself as the temp_table has been already filled with all needed rows.
            (TODO. This code should be revised. It does not cause any problems now because the uncacheable derived table that we have now are produced by split optimization and there we have no order by associated with them.)
            10. In mysql_derived_fill() as the unit for 'ca' has been already executed and it is marked as uncacheable
            table->file->ha_delete_all_rows() is called for 'ca' and the mysql_select is called to fill the temp table with the rows for 'ca'. No problems are observed so far except that the temp table for 'ca' is filled twice, but this is just a performance issue.
            11. in preread_init() called for 'ca' the flag preread_init_done remains false.
            12. in create_sort_index() filesort() is called that sets array of pointers to positions of records in the temp table for 'ca'.
            13. in join_init_read_record() for 'ca' preread_init() is called again and it fills the temp table for 'ca' again. At the same time it makes the pointers set by filesort obsolete as mysql_derived_fill first deletes all records from the temp table just by freeing the piece of memory with them.
            (TODO. Here we have another potential problem for future uncacheable derived tables that should be resolved.)
            14. rr_from_pointers() ties to read the first record for the sorted result set. it use the array of pointers to the temp table, but they not actual anymore.

            igor Igor Babaev (Inactive) added a comment - More details how we come to the above crash/memory problems. After the first execution of the prepared statement (PS) all mergeable derived are already merged and the query of the following form is processed at the second execution of PS: select ca.d2 AS d2,ca.d1 AS d1 from ( select min (t1.d2) AS d2, min ((subquery#4)) AS d1 from test.t1) ca order by ca.d2 Here's how the query is executed: 1. The query is is a join containing only one table 'ca' whose result set requires sorting. So single-table join with join_tab 'ca' with join_tab->filesort defined is executed. 2. As join_tab->join_tab->preread_init_done is false for 'ca' join_tab->preread_init() is called that checks that 'ca' is materialized derived and requires filling. 3. mysql_derived_fill is called for 'ca' where mysql_select() is called for the select select min (t1.d2) AS d2, min ((subquery#4)) AS d1 from test.t1 4. Execution of the above query fills the temp table for 'ca'. 5. As the unit for 'ca' is marked as uncacheable preread_init() does not set join_tab->preread_init_done to TRUE. 6. join_init_read_record() is called for JOIN_TAB 'ca'. It sees that tab->filesort != NULL and calls sort_table() for 'ca'. 7. sort_table() calls create_sort_index(). 8. As preread_init_done is still false for 'ca' preread_init() is called for 'ca'. 9. As ca is marked as uncacheable mysql_derived_fill() is called for'ca'. This is not good by itself as the temp_table has been already filled with all needed rows. (TODO. This code should be revised. It does not cause any problems now because the uncacheable derived table that we have now are produced by split optimization and there we have no order by associated with them.) 10. In mysql_derived_fill() as the unit for 'ca' has been already executed and it is marked as uncacheable table->file->ha_delete_all_rows() is called for 'ca' and the mysql_select is called to fill the temp table with the rows for 'ca'. No problems are observed so far except that the temp table for 'ca' is filled twice, but this is just a performance issue. 11. in preread_init() called for 'ca' the flag preread_init_done remains false. 12. in create_sort_index() filesort() is called that sets array of pointers to positions of records in the temp table for 'ca'. 13. in join_init_read_record() for 'ca' preread_init() is called again and it fills the temp table for 'ca' again. At the same time it makes the pointers set by filesort obsolete as mysql_derived_fill first deletes all records from the temp table just by freeing the piece of memory with them. (TODO. Here we have another potential problem for future uncacheable derived tables that should be resolved.) 14. rr_from_pointers() ties to read the first record for the sorted result set. it use the array of pointers to the temp table, but they not actual anymore.
            igor Igor Babaev (Inactive) added a comment -

            Here's a detailed explanation how the select that specifies the derived table 'ca' becomes marked as unchacheable.
            At the first execution of the PS
            1. When the column outer reference t1.id1 that used in the subquery 

            SELECT t2.d1  FROM t2 WHERE t1.id1 = t2.id1 ORDER BY t2.id DESC LIMIT 1 // subquery#4

            is resolved in Item_field::fix_outer_field() the table reference against which it is resolved is the same as it was when PS was processed. This table reference was saved in Item_field::cached_table. This table reference belongs to the from list of this select.

            select t1.d2 AS d2,(subquery#4) AS d1 from test.t1   // outer_select

            2. mark_as_dependent() is called to which outer_select is passed as the second parameter and subquery#4 is passed as the third parameter.
            3. the function mark_as_dependent() marks all selects starting from subquery#4 up to outer_select
            as uncacheable. it uses the functionouter_select() to move towards outer_select.
            At the second execution of the PS
            1. mark_as_dependent() is called with the same parameters as at the first execution.
            2. as outer_select has been already merged at the first execution of PS it cannot be reached using the outer_select() function anymore.
            3. as a result all selects towards the top level select including the select for 'ca' are marked as uncacheable.

            igor Igor Babaev (Inactive) added a comment - Here's a detailed explanation how the select that specifies the derived table 'ca' becomes marked as unchacheable. At the first execution of the PS 1. When the column outer reference t1.id1 that used in the subquery SELECT t2.d1 FROM t2 WHERE t1.id1 = t2.id1 ORDER BY t2.id DESC LIMIT 1 // subquery#4 is resolved in Item_field::fix_outer_field() the table reference against which it is resolved is the same as it was when PS was processed. This table reference was saved in Item_field::cached_table. This table reference belongs to the from list of this select. select t1.d2 AS d2,(subquery#4) AS d1 from test.t1 // outer_select 2. mark_as_dependent() is called to which outer_select is passed as the second parameter and subquery#4 is passed as the third parameter. 3. the function mark_as_dependent() marks all selects starting from subquery#4 up to outer_select as uncacheable. it uses the functionouter_select() to move towards outer_select. At the second execution of the PS 1. mark_as_dependent() is called with the same parameters as at the first execution. 2. as outer_select has been already merged at the first execution of PS it cannot be reached using the outer_select() function anymore. 3. as a result all selects towards the top level select including the select for 'ca' are marked as uncacheable.
            igor Igor Babaev (Inactive) added a comment -

            Ok to push the fix into 10.2 after providing a proper comment for the patch.

            igor Igor Babaev (Inactive) added a comment - Ok to push the fix into 10.2 after providing a proper comment for the patch.
            igor Igor Babaev (Inactive) made changes -
            Status In Review [ 10002 ] Stalled [ 10000 ]
            igor Igor Babaev (Inactive) made changes -
            Assignee Igor Babaev [ igor ] Oleksandr Byelkin [ sanja ]
            sanja Oleksandr Byelkin made changes -
            Fix Version/s 10.2.38 [ 25207 ]
            Fix Version/s 10.3.29 [ 25206 ]
            Fix Version/s 10.4.19 [ 25205 ]
            Fix Version/s 10.5.10 [ 25204 ]
            Fix Version/s 10.3 [ 22126 ]
            Fix Version/s 10.4 [ 22408 ]
            Fix Version/s 10.5 [ 23123 ]
            sanja Oleksandr Byelkin made changes -
            Resolution Fixed [ 1 ]
            Status Stalled [ 10000 ] Closed [ 6 ]
            serg Sergei Golubchik made changes -
            Workflow MariaDB v3 [ 120234 ] MariaDB v4 [ 159045 ]
            Johnston Rex Johnston made changes -
            Roel Roel Van de Paar made changes -
            Roel Roel Van de Paar made changes -
            mariadb-jira-automation Jira Automation (IT) made changes -
            Zendesk Related Tickets 144504

            People

              sanja Oleksandr Byelkin
              kjoiner Kyle Joiner (Inactive)
              Votes:
              1 Vote for this issue
              Watchers:
              11 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.