[MDEV-11566] Server crashes in get_store_key / create_ref_for_key / get_best_combination Created: 2016-12-14  Updated: 2023-06-08  Resolved: 2023-06-08

Status: Closed
Project: MariaDB Server
Component/s: Optimizer, Storage Engine - InnoDB
Affects Version/s: 5.5, 10.0, 10.1, 10.2
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Elena Stepanova Assignee: Igor Babaev
Resolution: Won't Fix Votes: 1
Labels: None

Attachments: File diff-11566.diff    
Issue Links:
Duplicate
is duplicated by MDEV-15787 centos 7 MariaDB-5.5.56 ERROR 2013 (H... Closed
is duplicated by MDEV-16233 Update w/ sub-query causes signal 11 ... Open
Sprint: 10.2.6-3, 5.5.58

 Description   

Initially reported on #maria IRC channel.

Test case

 
--source include/have_innodb.inc
 
 
 
CREATE TABLE `stats_disk_usage` (
 
  `id` varchar(16) NOT NULL,
 
  `date_scanned` date NOT NULL,
 
  `instances_id` varchar(16) NOT NULL,
 
  `stat_type` varchar(16) NOT NULL,
 
  `kilobytes` bigint(20) unsigned NOT NULL,
 
  `date_created` datetime NOT NULL,
 
  `date_changed` datetime DEFAULT NULL,
 
  PRIMARY KEY (`id`),
 
  UNIQUE KEY `date_scanned_instance_idx` (`date_scanned`,`instances_id`,`stat_type`),
 
  KEY `date_scanned_idx` (`date_scanned`),
 
  KEY `instances_id_stat_type_idx` (`instances_id`,`stat_type`),
 
  KEY `kilobytes_idx` (`kilobytes`),
 
  KEY `date_created_idx` (`date_created`),
 
  KEY `date_changed_idx` (`date_changed`),
 
  KEY `date_type_size_idx` (`date_scanned`,`stat_type`,`kilobytes`)
 
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
 
 
CREATE TABLE `stats_disk_usage_monthly` (
 
  `id` varchar(16) NOT NULL,
 
  `date_scanned` date NOT NULL,
 
  `date_month` date NOT NULL,
 
  `instances_id` varchar(16) NOT NULL,
 
  `stat_type` varchar(16) NOT NULL,
 
  `kilobytes` bigint(20) unsigned NOT NULL,
 
  `date_created` datetime NOT NULL,
 
  `date_changed` datetime DEFAULT NULL,
 
  PRIMARY KEY (`id`),
 
  UNIQUE KEY `date_month_instance_idx` (`date_month`,`instances_id`,`stat_type`),
 
  KEY `date_scanned_idx` (`date_scanned`),
 
  KEY `date_month_idx` (`date_month`),
 
  KEY `instances_id_stat_type_idx` (`instances_id`,`stat_type`),
 
  KEY `kilobytes_idx` (`kilobytes`),
 
  KEY `date_created_idx` (`date_created`),
 
  KEY `date_changed_idx` (`date_changed`),
 
  KEY `date_type_size_idx` (`date_scanned`,`stat_type`,`kilobytes`)
 
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
 
 
 INSERT into stats_disk_usage_monthly
 
 (id, date_scanned, date_month, instances_id, stat_type,
 
  kilobytes, date_created, date_changed)
 
 
 SELECT CONCAT(LEFT( id, LENGTH(id)-1 ), 'm'), max(date_scanned), '2016-12-01', instances_id, stat_type,
 
 kilobytes AS total_quantity, '2016-12-13 12:11:26', NULL
 
 
 FROM stats_disk_usage AS orig
 
 WHERE orig.date_scanned >= '2016-12-01' AND orig.date_scanned <= '2016-12-31'
 
 AND   orig.stat_type='files'
 
 GROUP BY orig.instances_id
 
 
 ON DUPLICATE KEY UPDATE
 
 stats_disk_usage_monthly.kilobytes =
 
 (
 
   SELECT kilobytes FROM stats_disk_usage AS target
 
   WHERE target.date_scanned >= '2016-12-01' AND target.date_scanned <= '2016-12-31'
 
   AND   target.instances_id = orig.instances_id
 
   AND   target.stat_type='files'
 
   ORDER BY target.date_scanned DESC
 
   LIMIT 1
 
 ),
 
 stats_disk_usage_monthly.date_changed='2016-12-13 12:11:26';

5.5 03dabfa84d6bc9a8197c8d9fbe80f2a7f6a5b6ac

 
#3  <signal handler called>
 
#4  0x0000000000000000 in ?? ()
 
#5  0x0000000000647450 in get_store_key (thd=0x7f6181360060, keyuse=0x7f6179091460, used_tables=0, key_part=0x7f6179078618, key_buff=0x7f617919e988 "", maybe_null=0) at /data/src/5.5/sql/sql_select.cc:8222
 
#6  0x0000000000647100 in create_ref_for_key (join=0x7f6179109620, j=0x7f617919e668, org_keyuse=0x7f6179091460, allow_full_scan=true, used_tables=4611686018427387905) at /data/src/5.5/sql/sql_select.cc:8153
 
#7  0x0000000000646031 in get_best_combination (join=0x7f6179109620) at /data/src/5.5/sql/sql_select.cc:7819
 
#8  0x000000000063c70d in make_join_statistics (join=0x7f6179109620, tables_list=..., conds=0x7f61790f14d8, keyuse_array=0x7f6179109938) at /data/src/5.5/sql/sql_select.cc:3832
 
#9  0x0000000000633402 in JOIN::optimize (this=0x7f6179109620) at /data/src/5.5/sql/sql_select.cc:1229
 
#10 0x00000000005f8ebb in st_select_lex::optimize_unflattened_subqueries (this=0x7f6181363a70, const_only=false) at /data/src/5.5/sql/sql_lex.cc:3549
 
#11 0x0000000000744ad4 in JOIN::optimize_unflattened_subqueries (this=0x7f61791084f0) at /data/src/5.5/sql/opt_subselect.cc:4984
 
#12 0x0000000000634ea7 in JOIN::optimize (this=0x7f61791084f0) at /data/src/5.5/sql/sql_select.cc:1665
 
#13 0x0000000000639d87 in mysql_select (thd=0x7f6181360060, rref_pointer_array=0x7f6181363ce0, tables=0x7f617918b858, wild_num=0, fields=..., conds=0x7f617918c500, og_num=1, order=0x0, group=0x7f617918cab0, having=0x0, proc_param=0x0, select_options=3489925888, result=0x7f6179108458, unit=0x7f6181363390, select_lex=0x7f6181363a70) at /data/src/5.5/sql/sql_select.cc:3080
 
#14 0x0000000000630702 in handle_select (thd=0x7f6181360060, lex=0x7f61813632e0, result=0x7f6179108458, setup_tables_done_option=1073741824) at /data/src/5.5/sql/sql_select.cc:319
 
#15 0x0000000000604f52 in mysql_execute_command (thd=0x7f6181360060) at /data/src/5.5/sql/sql_parse.cc:3060
 
#16 0x000000000060c834 in mysql_parse (thd=0x7f6181360060, rawbuf=0x7f6179191078 "INSERT into stats_disk_usage_monthly\n(id, date_scanned, date_month, instances_id, stat_type,\nkilobytes, date_created, date_changed)\nSELECT CONCAT(LEFT( id, LENGTH(id)-1 ), 'm'), max(date_scanned), '20"..., length=840, parser_state=0x7f6189bfe650) at /data/src/5.5/sql/sql_parse.cc:5934
 
#17 0x0000000000600549 in dispatch_command (command=COM_QUERY, thd=0x7f6181360060, packet=0x7f6181206061 "INSERT into stats_disk_usage_monthly\n(id, date_scanned, date_month, instances_id, stat_type,\nkilobytes, date_created, date_changed)\nSELECT CONCAT(LEFT( id, LENGTH(id)-1 ), 'm'), max(date_scanned), '20"..., packet_length=840) at /data/src/5.5/sql/sql_parse.cc:1079
 
#18 0x00000000005ff703 in do_command (thd=0x7f6181360060) at /data/src/5.5/sql/sql_parse.cc:793
 
#19 0x000000000070190f in do_handle_one_connection (thd_arg=0x7f6181360060) at /data/src/5.5/sql/sql_connect.cc:1270
 
#20 0x000000000070169c in handle_one_connection (arg=0x7f6181360060) at /data/src/5.5/sql/sql_connect.cc:1186
 
#21 0x000000000094409f in pfs_spawn_thread (arg=0x7f61813ffe80) at /data/src/5.5/storage/perfschema/pfs.cc:1015
 
#22 0x00007f61898450a4 in start_thread (arg=0x7f6189bff700) at pthread_create.c:309
 
#23 0x00007f6187c6b87d in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:111

Does not crash with MyISAM.
Does not crash on MySQL 5.5.
MySQL 5.6/5.7 produce the error:

1054: Unknown column 'orig.instances_id' in 'where clause'



 Comments   
Comment by Sergei Petrunia [ 2017-02-17 ]

Varun, please investigate.

Comment by Varun Gupta (Inactive) [ 2017-03-13 ]

shows segfault for this 

(*((Item_ref**)((Item_ref*)keyuse->val)->ref)

so clearly we can see that Item_ref::ref is not pointing to a valid memory address

Comment by Varun Gupta (Inactive) [ 2017-03-15 ]

So now we start to find where Item_ref->ref is initialised

In function Item_field::fix_outer_field

if (!(ref= resolve_ref_in_select_and_group(thd, this, select)))
 
   return -1; /* Some error occurred (e.g. ambiguous names). */

The value returned from the function resolve_ref_in_select_and_group is 

return &select->ref_pointer_array[counter];
 
 
 
(lldb) p select->ref_pointer_array[counter]
 
(Item_field *) $14 = 0x000000010aa4c6e8
 
 
 
Return value: (Item **) $15 = 0x000000010aa58300

Comment by Varun Gupta (Inactive) [ 2017-03-20 ]

In function find_field_in_tables, we find the reference for orig_instances_id

5864	    Field *cur_field= find_field_in_table_ref(thd, cur_table, name, length,
 
5865	                                              item->name, db, table_name, ref,
 
5866	                                              (thd->lex->sql_command ==
 
5867	                                               SQLCOM_SHOW_FIELDS)
 
 
 
(lldb) p cur_field
 
(Field *) $5 = 0x0000000000000000

Next we find try find the outer reference for orig.instances_id in the outer select and group by clause

Inside the function resolve_ref_in_select_and_group, we first find the ref in 

4944	  if (!(select_ref= find_item_in_list(ref, *(select->get_item_list()),
 
4945	                                      &counter, REPORT_EXCEPT_NOT_FOUND,
 
4946	                                      &resolution))
 
 
 
(lldb) p select_ref
 
(Item **) $11 = 0x000000010b251ef8
 
 
 
then we return
 
4993	      return &select->ref_pointer_array[counter];
 
(lldb) p (select->ref_pointer_array[counter])->name
 
(char *) $14 = 0x000000010b251de8 "instances_id"
 
Return value: (Item **) $15 = 0x000000010b25d1c0

This all looks to work correctly.

Now back to function Item_field:: fix_outer_field

if (!(ref= resolve_ref_in_select_and_group(thd, this, select)))
 
(lldb) p ref
 
(Item **) $16 = 0x000000010b25d1c0
 



 
 
(lldb) p rf
 
(Item_outer_ref *) $26 = 0x000000010b25e488
 
(lldb) p rf->field_name
 
(const char *) $27 = 0x000000010b255198 "instances_id"
 
(lldb) p rf->table_name
 
(const char *) $28 = 0x000000010b255190 "orig"

Comment by Varun Gupta (Inactive) [ 2017-03-21 ]

One strange thing I found is

(gdb) p (*(Item_ref**)((Item_ref*)keyuse->val)->ref)->type()
 
$33 = Item::FIELD_ITEM

the type is FIELD_ITEM which is correct, so we should only compute 

((*(Item_ref**)((Item_ref*)keyuse->val)->ref)->ref_type() == Item_ref::DIRECT_REF))

if the type of the object is REF_ITEM

Comment by Varun Gupta (Inactive) [ 2017-03-23 ] 

diff --git a/sql/sql_select.cc b/sql/sql_select.cc
 
index 5633881..1a5fefe 100644
 
--- a/sql/sql_select.cc
 
+++ b/sql/sql_select.cc
 
@@ -9236,9 +9236,10 @@ get_store_key(THD *thd, KEYUSE *keyuse, table_map used_tables,
 
   else if (keyuse->val->type() == Item::FIELD_ITEM ||
 
            (keyuse->val->type() == Item::REF_ITEM &&
 
            ((((Item_ref*)keyuse->val)->ref_type() == Item_ref::OUTER_REF &&
 
-              (*(Item_ref**)((Item_ref*)keyuse->val)->ref)->ref_type() ==
 
-              Item_ref::DIRECT_REF) || 
-             ((Item_ref*)keyuse->val)->ref_type() == Item_ref::VIEW_REF) &&
 
+              ((((*(Item_ref**)((Item_ref*)keyuse->val)->ref)->type() == Item::REF_ITEM) &&
 
+              ((*(Item_ref**)((Item_ref*)keyuse->val)->ref)->ref_type() ==
 
+              Item_ref::DIRECT_REF)) || 
+             ((Item_ref*)keyuse->val)->ref_type() == Item_ref::VIEW_REF))) &&
 
             keyuse->val->real_item()->type() == Item::FIELD_ITEM))

Comment by Varun Gupta (Inactive) [ 2017-03-23 ]

diff-11566.diff

Comment by Sergei Petrunia [ 2017-06-29 ]

Looking at the code before the patch... 

  else if (keyuse->val->type() == Item::FIELD_ITEM ||
 
           (keyuse->val->type() == Item::REF_ITEM 
            &&
 
	    (
 
                (
 
                  ((Item_ref*)keyuse->val)->ref_type() == Item_ref::OUTER_REF &&
 
                  (*(Item_ref**)((Item_ref*)keyuse->val)->ref)->ref_type() == Item_ref::DIRECT_REF   // (*)
 
                ) 
                || 
                ((Item_ref*)keyuse->val)->ref_type() == Item_ref::VIEW_REF
 
            )
 
            &&
 
               keyuse->val->real_item()->type() == Item::FIELD_ITEM
 
           )
 
          )
 
 
 
make use of
 
          ((Item_field*) keyuse->val->real_item())->field,

Debugging the bug's example:

(gdb) set $kv=keyuse->val
 
(gdb) p $kv
 
  $97 = (Item_outer_ref *) 0x7ffeefb29958
 
(gdb) p $kv->type()
 
  $98 = Item::REF_ITEM
 
(gdb) p ((Item_ref*)$kv)->ref_type()
 
  $99 = Item_ref::OUTER_REF

so the computation arrives to the line marked with .
But then, things go wrong:

(gdb) p ((Item_ref*)$kv)->ref[0]
 
  $100 = (Item_field *) 0x7ffeefa5f1b8

Item_outer_ref points to an Item_field.
But we try to compute

(*(Item_ref**)((Item_ref*)keyuse->val)->ref)->ref_type() == Item_ref::DIRECT_REF

That is, ref is casted to Item_ref*, while it points to an Item_field object.

Comment by Sergei Petrunia [ 2017-06-29 ]

Looking at varun's patch. It still casts keyuse->val->ref to point to an Item_ref:

              ((((*(Item_ref**)((Item_ref*)keyuse->val)->ref)->type() == Item::REF_ITEM) &&

while the object located at that address is an Item_field.

Comment by Sergei Petrunia [ 2017-06-29 ]

Also checked with sanja : it is normal to have Item_outer_ref(Item_field(...)).

Comment by Sergei Petrunia [ 2017-06-29 ]

varun, please fix the patch to not do invalid typecast.

Comment by Sergei Petrunia [ 2017-07-04 ]

As far as I understand, we can use store_key_field (a direct field-to-field copier) for items in this form:

  • X
  • Item_outer_ref( X )
  • Item_outer_ref(Item_direct_ref( X ))
  • Item_view_ref( X )

where X is an Item_field object.
The check if X is an Item_field object is done like this: keyuse->val->real_item()->type() == Item::FIELD_ITEM.

A counter-example: we cannot use store_key_field for Item_ref(Item_field(X)). This is because Item_ref converts val_int() (and similar) calls to val_result() (and similar) calls. So we would need to use Item_field->result_item there (which we do not do ATM).

Comment by Varun Gupta (Inactive) [ 2019-01-17 ]

Patch
http://lists.askmonty.org/pipermail/commits/2017-October/011570.html

Comment by Alice Sherepa [ 2021-11-02 ]

not reproducible on the current 10.2 d0b611a76d91a5b6dbbc8

Comment by Elena Stepanova [ 2023-06-08 ]

10.1 and even 10.2 are long EOL, and it's not reproducible on higher versions.

Generated at Thu Feb 08 07:50:56 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.