The problem appeared on 5.3 tree with the following revision:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
revno: 3725 [merge]
revision-id: igor@askmonty.org-20131122022820-it39kqzjw8kmwd1j
parent: bar@mnogosearch.org-20131121074636-xiubz4tilopaqplg
parent: igor@askmonty.org-20131121231925-j30hvvpxpb55v67m
committer: Igor Babaev <igor@askmonty.org>
branch nick: maria-5.3-merge
timestamp: Thu 2013-11-21 18:28:20 -0800
message:
Merge
------------------------------------------------------------
revno: 3723.1.1
revision-id: igor@askmonty.org-20131121231925-j30hvvpxpb55v67m
parent: igor@askmonty.org-20131114212505-bou201elzcouxzo8
committer: Igor Babaev <igor@askmonty.org>
branch nick: maria-5.3-mdev5103
timestamp: Thu 2013-11-21 15:19:25 -0800
message:
Another attempt to fix bug mdev-5103.
The earlier pushed fix for the bug was incomplete. It did not remove
the main cause of the problem: the function remove_eq_conds()
removed always true multiple equalities from any conjunct, but did not
adjust the list of them stored in Item_cond_and::cond_equal.current_level.
Simplified the test case for the bug and moved it to another test file.
The fix triggered changes in EXPLAIN EXTENDED for some queries.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
SET optimizer_switch = 'derived_with_keys=on,in_to_exists=on';
CREATE TABLE t1 (id INT, a2 CHAR(2), a3 CHAR(3)) ENGINE=MyISAM;
INSERT INTO t1 VALUES (1,'BE','BEL');
CREATE TABLE t2 (id INT, a2 CHAR(2), a3 CHAR(3)) ENGINE=MyISAM;
INSERT INTO t2 VALUES (1,'BE','BEL'),(2,'MX','MEX');
CREATE ALGORITHM=MERGE VIEW v2 AS SELECT DISTINCT * FROM t2;
SELECT * FROM t1 AS outer_t1, v2
WHERE v2.a3 = outer_t1.a3
AND EXISTS ( SELECT * FROM t1 WHERE a2 < v2.a2 AND id = outer_t1.id )
AND outer_t1.a3 < 'J'
ORDER BY v2.id;
Note: I don't know why derived_with_keys makes a difference, but it does.
1
2
mysqld: field.cc:6406: virtual String* Field_string::val_str(String*, String*): Assertion `!table || (!table->read_set || bitmap_is_set(table->read_set, field_index))' failed.
131219 0:07:43 [ERROR] mysqld got signal 6 ;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
#6 0x00007fc734f9f621 in *__GI___assert_fail (assertion=0xcb8af0 "!table || (!table->read_set || bitmap_is_set(table->read_set, field_index))", file=<optimized out>, line=6406, function=0xcbafa0 "virtual String* Field_string::val_str(String*, String*)") at assert.c:81
#7 0x0000000000667f0f in Field_string::val_str (this=0x3723818, val_buffer=0x371ef38, val_ptr=0x36ec478) at field.cc:6406
#8 0x00000000005a5ac4 in Item_field::val_str (this=0x36ec460, str=0x371ef38) at item.cc:2384
#9 0x00000000005efabd in Arg_comparator::compare_string (this=0x371eeb0) at item_cmpfunc.cc:989
#10 0x00000000005d0698 in Arg_comparator::compare (this=0x371eeb0) at item_cmpfunc.h:72
#11 0x00000000005f282c in Item_func_lt::val_int (this=0x371edf0) at item_cmpfunc.cc:1915
#12 0x000000000074f7e9 in do_select (join=0x3727ab8, fields=0x36eb6a0, table=0x0, procedure=0x0) at sql_select.cc:15893
#13 0x000000000072ee0b in JOIN::exec (this=0x3727ab8) at sql_select.cc:2788
#14 0x000000000063c13e in subselect_single_select_engine::exec (this=0x371f448) at item_subselect.cc:3142
#15 0x00000000006349c9 in Item_subselect::exec (this=0x371f318) at item_subselect.cc:588
#16 0x000000000063727f in Item_exists_subselect::val_bool (this=0x371f318) at item_subselect.cc:1483
#17 0x00000000005fbb8b in Item_cond_and::val_int (this=0x3773378) at item_cmpfunc.cc:4580
#18 0x00000000006fc93a in SQL_SELECT::skip_record (this=0x3771b00, thd=0x3666898) at opt_range.h:924
#19 0x0000000000820528 in find_all_keys (param=0x7fc72c4bb0d0, select=0x3771b00, sort_keys=0x3776c88, sort_keys_buf=0x3776ce8 "", buffpek_pointers=0x7fc72c4bb2e0, tempfile=0x7fc72c4bb150, indexfile=0x0) at filesort.cc:627
#20 0x000000000081f1ef in filesort (thd=0x3666898, table=0x37266f0, sortorder=0x3773718, s_length=1, select=0x3771b00, max_rows=18446744073709551615, sort_positions=false, examined_rows=0x7fc72c4bb570) at filesort.cc:247
#21 0x00000000007585ee in create_sort_index (thd=0x3666898, join=0x3725090, order=0x371fa00, filesort_limit=18446744073709551615, select_limit=18446744073709551615, is_order_by=false) at sql_select.cc:19342
#22 0x000000000072eade in JOIN::exec (this=0x3725090) at sql_select.cc:2737
#23 0x000000000072f6a5 in mysql_select (thd=0x3666898, rref_pointer_array=0x3669588, tables=0x36ea9a0, wild_num=1, fields=..., conds=0x371f488, og_num=1, order=0x371fa00, group=0x0, having=0x0, proc_param=0x0, select_options=2147764736, result=0x3725070, unit=0x3668e28, select_lex=0x3669330) at sql_select.cc:3009
#24 0x0000000000726105 in handle_select (thd=0x3666898, lex=0x3668d88, result=0x3725070, setup_tables_done_option=0) at sql_select.cc:288
#25 0x00000000006b4dec in execute_sqlcom_select (thd=0x3666898, all_tables=0x36ea9a0) at sql_parse.cc:5172
#26 0x00000000006abf88 in mysql_execute_command (thd=0x3666898) at sql_parse.cc:2305
#27 0x00000000006b7753 in mysql_parse (thd=0x3666898, rawbuf=0x36ea670 "SELECT * FROM t1 AS outer_t1, v2 \nWHERE v2.a3 = outer_t1.a3 \nAND EXISTS ( SELECT * FROM t1 WHERE a2 < v2.a2 AND id = outer_t1.id )\nAND outer_t1.a3 < 'J' \nORDER BY v2.id", length=172, found_semicolon=0x7fc72c4bccb8) at sql_parse.cc:6173
#28 0x00000000006a9768 in dispatch_command (command=COM_QUERY, thd=0x3666898, packet=0x36e1319 "SELECT * FROM t1 AS outer_t1, v2 \nWHERE v2.a3 = outer_t1.a3 \nAND EXISTS ( SELECT * FROM t1 WHERE a2 < v2.a2 AND id = outer_t1.id )\nAND outer_t1.a3 < 'J' \nORDER BY v2.id", packet_length=172) at sql_parse.cc:1243
#29 0x00000000006a8a54 in do_command (thd=0x3666898) at sql_parse.cc:923
#30 0x00000000006a58dd in handle_one_connection (arg=0x3666898) at sql_connect.cc:1231
#31 0x00007fc735cabb50 in start_thread (arg=<optimized out>) at pthread_create.c:304
#32 0x00007fc73504ea7d in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:112
Stack trace from:
1
2
3
revision-id: sanja@askmonty.org-20131218135951-agw2htf09w418t91
revno: 3737
branch-nick: 5.3
Also reproducible on 5.5, 10.0 trees.