[MDEV-25182] Complex query in Store procedure corrupts results Created: 2021-03-17  Updated: 2023-08-07  Resolved: 2021-04-12

Status: Closed
Project: MariaDB Server
Component/s: Prepared Statements, Stored routines
Affects Version/s: 5.5, 10.0, 10.1, 10.5.9, 10.3, 10.4, 10.5
Fix Version/s: 10.2.38, 10.3.29, 10.4.19, 10.5.10

Type: Bug Priority: Blocker
Reporter: Kyle Joiner (Inactive) Assignee: Oleksandr Byelkin
Resolution: Fixed Votes: 1
Labels: None

Attachments: Zip Archive CS0236378.zip    
Issue Links:
Duplicate
is duplicated by MDEV-23130 Server crashes on 2nd execution of sp Closed

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



 Comments   
Comment by Alice Sherepa [ 2021-03-18 ]

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

Comment by Alice Sherepa [ 2021-03-18 ]

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

Comment by Oleksandr Byelkin [ 2021-03-27 ]

Try

set optimizer_switch="derived_merge=off";

as workaround

Comment by Oleksandr Byelkin [ 2021-04-07 ]

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.

Comment by Oleksandr Byelkin [ 2021-04-07 ]

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

Comment by Oleksandr Byelkin [ 2021-04-07 ] 

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.

Comment by Sergei Golubchik [ 2021-04-09 ]

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.

Comment by Igor Babaev [ 2021-04-10 ]

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.

Comment by Igor Babaev [ 2021-04-12 ]

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.

Comment by Igor Babaev [ 2021-04-12 ]

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

Generated at Thu Feb 08 09:35:46 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.