[MDEV-6308] Server crashes in table_multi_eq_cond_selectivity with optimizer_use_condition_selectivity>1 - JIRA

Details

Type: Bug
Status: Closed
Priority: Critical
Resolution: Fixed
Affects Version/s: 10.0.11
Fix Version/s: 10.0.12
Component/s: None
Labels:
None

Description

Initially reported by Jippi on #maria IRC channel.

The problem appeared in 10.0 tree with the following revision:

revno: 4169
revision-id: psergey@askmonty.org-20140425150454-dsk6kba2vn13gw50
parent: psergey@askmonty.org-20140428174939-32ycvsxmajmfdjno
committer: Sergey Petrunya <psergey@askmonty.org>
branch nick: 10.0-cp
timestamp: Fri 2014-04-25 19:04:54 +0400
message:
  MDEV-6003: EITS: ref access, keypart2=const vs keypart2=expr - inconsistent filtered% value
  - Fix table_cond_selectivity() to work correctly for ref access 
    and "keypart2=const" case.

#3  <signal handler called>
#4  0x00000000006bdc35 in table_multi_eq_cond_selectivity (join=0x7f2dfb3b46c8, idx=0, s=0x7f2dfb3b5c40, rem_tables=1, keyparts=2, ref_keyuse_steps=0x7f2e05129c20) at 10.0/sql/sql_select.cc:7224
#5  0x00000000006be413 in table_cond_selectivity (join=0x7f2dfb3b46c8, idx=0, s=0x7f2dfb3b5c40, rem_tables=1) at 10.0/sql/sql_select.cc:7458
#6  0x00000000006beb4a in best_extension_by_limited_search (join=0x7f2dfb3b46c8, remaining_tables=3, idx=0, record_count=1, read_time=0, search_depth=62, prune_level=1, use_cond_selectivity=2) at 10.0/sql/sql_select.cc:7706
#7  0x00000000006bd0e4 in greedy_search (join=0x7f2dfb3b46c8, remaining_tables=3, search_depth=62, prune_level=1, use_cond_selectivity=2) at 10.0/sql/sql_select.cc:6897
#8  0x00000000006bc6cc in choose_plan (join=0x7f2dfb3b46c8, join_tables=3) at 10.0/sql/sql_select.cc:6474
#9  0x00000000006b6195 in make_join_statistics (join=0x7f2dfb3b46c8, tables_list=..., conds=0x7f2dfb1dd930, keyuse_array=0x7f2dfb3b49d0) at 10.0/sql/sql_select.cc:4018
#10 0x00000000006ace96 in JOIN::optimize_inner (this=0x7f2dfb3b46c8) at 10.0/sql/sql_select.cc:1337
#11 0x00000000006abe54 in JOIN::optimize (this=0x7f2dfb3b46c8) at 10.0/sql/sql_select.cc:1023
#12 0x00000000006b39d3 in mysql_select (thd=0x7f2dfd341070, rref_pointer_array=0x7f2dfd3456d8, tables=0x7f2dfb1dc2f8, wild_num=1, fields=..., conds=0x0, og_num=1, order=0x7f2dfb1ddba8, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7f2dfb1ddc98, unit=0x7f2dfd344d78, select_lex=0x7f2dfd345460) at 10.0/sql/sql_select.cc:3289
#13 0x00000000006aa063 in handle_select (thd=0x7f2dfd341070, lex=0x7f2dfd344cb0, result=0x7f2dfb1ddc98, setup_tables_done_option=0) at 10.0/sql/sql_select.cc:372
#14 0x000000000067eddd in execute_sqlcom_select (thd=0x7f2dfd341070, all_tables=0x7f2dfb1dc2f8) at 10.0/sql/sql_parse.cc:5263
#15 0x00000000006771d4 in mysql_execute_command (thd=0x7f2dfd341070) at 10.0/sql/sql_parse.cc:2554
#16 0x0000000000681567 in mysql_parse (thd=0x7f2dfd341070, rawbuf=0x7f2dfb1dc088 "select * from t1 inner join t2 on id = t1_id and (f2='qux' and f1='baz') order by dt DESC", length=89, parser_state=0x7f2e0512b610) at 10.0/sql/sql_parse.cc:6409
#17 0x0000000000674475 in dispatch_command (command=COM_QUERY, thd=0x7f2dfd341070, packet=0x7f2dfd337071 "select * from t1 inner join t2 on id = t1_id and (f2='qux' and f1='baz') order by dt DESC", packet_length=89) at 10.0/sql/sql_parse.cc:1309
#18 0x000000000067381a in do_command (thd=0x7f2dfd341070) at 10.0/sql/sql_parse.cc:1006
#19 0x000000000078ed29 in do_handle_one_connection (thd_arg=0x7f2dfd341070) at 10.0/sql/sql_connect.cc:1379
#20 0x000000000078ea7c in handle_one_connection (arg=0x7f2dfd341070) at 10.0/sql/sql_connect.cc:1293
#21 0x0000000000cbde8e in pfs_spawn_thread (arg=0x7f2e028226f0) at 10.0/storage/perfschema/pfs.cc:1860
#22 0x00007f2e04d16b50 in start_thread (arg=<optimized out>) at pthread_create.c:304
#23 0x00007f2e0320ea7d in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:112

Stack trace from:

revision-id: monty@askmonty.org-20140604102300-i14vdl5m7v3tl6rb
date: 2014-06-04 13:23:00 +0300
build-date: 2014-06-05 16:39:57 +0400
revno: 4221
branch-nick: 10.0

Test case:

set optimizer_use_condition_selectivity=2;

CREATE TABLE t1 (
  id varchar(40) COLLATE utf8_bin,
  dt datetime,
  PRIMARY KEY (id)
);

INSERT INTO t1 VALUES
	('foo','2011-04-12 05:18:08'),
	('bar','2013-09-19 11:37:03');

CREATE TABLE t2 (
  t1_id varchar(40) COLLATE utf8_bin,
  f1 varchar(64),
  f2 varchar(1024),
  KEY (f1,f2(255))
);

INSERT INTO t2 VALUES ('foo','baz','qux'),('bar','baz','qux');

select * from t1 inner join t2 on id = t1_id and (f2='qux' and f1='baz') order by dt DESC;

Gliffy Diagrams

Attachments

Issue Links

relates to

MDEV-7413 optimizer_use_condition_selectivity > 2 crashes 10.0.15+maria-1~wheezy

Closed

Activity

Ascending order - Click to sort in descending order

Hide

Permalink

Sergei Petrunia added a comment - 2014-06-07 08:51

Removed irrelevant parts of the query:

set optimizer_use_condition_selectivity=2;
select * from t1,t2 where t1.id = t2.t1_id and t2.f2='qux' and t2.f1='baz';

Show

Sergei Petrunia added a comment - 2014-06-07 08:51 Removed irrelevant parts of the query: set optimizer_use_condition_selectivity=2; select * from t1,t2 where t1.id = t2.t1_id and t2.f2='qux' and t2.f1='baz';

Hide

Permalink

Sergei Petrunia added a comment - 2014-06-07 09:22

The crash happens when table_multi_eq_cond_selectivity() is invoked for table t2.
The join prefix is (t1, t2).
Access to t2 is REF(t2.f1='baz', t2.f2='qux')

(gdb) p j
  $81 = 1
(gdb) next
(gdb) p ref_keyuse_steps[j -1]
  $82 = 2140

The latter is apparently garbage.

Show

Sergei Petrunia added a comment - 2014-06-07 09:22 The crash happens when table_multi_eq_cond_selectivity() is invoked for table t2. The join prefix is (t1, t2). Access to t2 is REF(t2.f1='baz', t2.f2='qux') (gdb) p j $81 = 1 (gdb) next (gdb) p ref_keyuse_steps[j -1] $82 = 2140 The latter is apparently garbage.

Server crashes in table_multi_eq_cond_selectivity with optimizer_use_condition_selectivity>1

Details

Description

Gliffy Diagrams

Attachments

Issue Links

Activity

People

Dates

Agile