CREATE TABLE t1 (f1 INT);

INSERT INTO t1 VALUES (1);

CREATE TABLE t2 (f2 INT, KEY(f2));

INSERT INTO t2 VALUES (8),(0);

CREATE TABLE t3 (f3 INT);

INSERT INTO t3 VALUES (1),(2);

CREATE TABLE t4 (f4 INT);

INSERT INTO t4 VALUES (0),(5);

SELECT * FROM t1, t2, t3 WHERE f2 IN ( f1 IN ( SELECT f4 FROM t4 ) );

#3  <signal handler called>

#4  0x0000000000677d89 in next_depth_first_tab (join=0x7f1f53969d28, tab=0x7f1f53940c10) at 5.5/sql/sql_select.cc:7672

#5  0x0000000000675df8 in JOIN::get_partial_cost_and_fanout (this=0x7f1f53969d28, end_tab_idx=61, filter_map=18446744073709551615, read_time_arg=0x7f1f543b3518, record_count_arg=0x7f1f543b3520) at 5.5/sql/sql_select.cc:6734

#6  0x000000000077bf81 in JOIN::choose_subquery_plan (this=0x7f1f5396a6b8, join_tables=1) at 5.5/sql/opt_subselect.cc:5454

#7  0x000000000066ed4a in make_join_statistics (join=0x7f1f5396a6b8, tables_list=..., conds=0x0, keyuse_array=0x7f1f5396a9b8) at 5.5/sql/sql_select.cc:3826

#8  0x0000000000665bf2 in JOIN::optimize (this=0x7f1f5396a6b8) at 5.5/sql/sql_select.cc:1229

#9  0x0000000000884178 in subselect_single_select_engine::exec (this=0x7f1f53a88d70) at 5.5/sql/item_subselect.cc:3143

#10 0x000000000087d342 in Item_subselect::exec (this=0x7f1f53a88bd0) at 5.5/sql/item_subselect.cc:655

#11 0x000000000087d86e in Item_in_subselect::exec (this=0x7f1f53a88bd0) at 5.5/sql/item_subselect.cc:828

#12 0x000000000087fc93 in Item_in_subselect::val_bool (this=0x7f1f53a88bd0) at 5.5/sql/item_subselect.cc:1654

#13 0x000000000059ebe1 in Item::val_bool_result (this=0x7f1f53a88bd0) at 5.5/sql/item.h:981

#14 0x000000000081c289 in Item_in_optimizer::val_int (this=0x7f1f5396ac68) at 5.5/sql/item_cmpfunc.cc:1747

#15 0x0000000000805914 in Item::save_in_field (this=0x7f1f5396ac68, field=0x7f1f53940f50, no_conversions=true) at 5.5/sql/item.cc:6059

#16 0x00000000006a3dba in store_key_item::copy_inner (this=0x7f1f543b4170) at 5.5/sql/sql_select.h:1662

#17 0x00000000006a39ad in store_key::copy (this=0x7f1f543b4170) at 5.5/sql/sql_select.h:1552

#18 0x00000000006794b1 in create_ref_for_key (join=0x7f1f53969d28, j=0x7f1f539408f0, org_keyuse=0x7f1f53890660, allow_full_scan=true, used_tables=4611686018427387907) at 5.5/sql/sql_select.cc:8145

#19 0x000000000067845b in get_best_combination (join=0x7f1f53969d28) at 5.5/sql/sql_select.cc:7817

#20 0x000000000066edc5 in make_join_statistics (join=0x7f1f53969d28, tables_list=..., conds=0x7f1f53a88db0, keyuse_array=0x7f1f5396a028) at 5.5/sql/sql_select.cc:3832

#21 0x0000000000665bf2 in JOIN::optimize (this=0x7f1f53969d28) at 5.5/sql/sql_select.cc:1229

#22 0x000000000066c45b in mysql_select (thd=0x7f1f54950060, rref_pointer_array=0x7f1f54953cd0, tables=0x7f1f53a872c0, wild_num=1, fields=..., conds=0x7f1f53a88db0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7f1f53a88fd0, unit=0x7f1f54953380, select_lex=0x7f1f54953a60) at 5.5/sql/sql_select.cc:3080

#23 0x0000000000662fbd in handle_select (thd=0x7f1f54950060, lex=0x7f1f549532d0, result=0x7f1f53a88fd0, setup_tables_done_option=0) at 5.5/sql/sql_select.cc:319

#24 0x000000000063c1fc in execute_sqlcom_select (thd=0x7f1f54950060, all_tables=0x7f1f53a872c0) at 5.5/sql/sql_parse.cc:4689

#25 0x00000000006353de in mysql_execute_command (thd=0x7f1f54950060) at 5.5/sql/sql_parse.cc:2234

#26 0x000000000063ece2 in mysql_parse (thd=0x7f1f54950060, rawbuf=0x7f1f53a87078 "SELECT * FROM t1, t2, t3 WHERE f2 IN ( f1 IN ( SELECT f4 FROM t4 ) )", length=68, parser_state=0x7f1f543b5620) at 5.5/sql/sql_parse.cc:5909

#27 0x0000000000632925 in dispatch_command (command=COM_QUERY, thd=0x7f1f54950060, packet=0x7f1f54a09061 "SELECT * FROM t1, t2, t3 WHERE f2 IN ( f1 IN ( SELECT f4 FROM t4 ) )", packet_length=68) at 5.5/sql/sql_parse.cc:1079

#28 0x0000000000631ab1 in do_command (thd=0x7f1f54950060) at 5.5/sql/sql_parse.cc:793

#29 0x0000000000734122 in do_handle_one_connection (thd_arg=0x7f1f54950060) at 5.5/sql/sql_connect.cc:1266

#30 0x0000000000733be1 in handle_one_connection (arg=0x7f1f54950060) at 5.5/sql/sql_connect.cc:1181

#31 0x0000000000b6c629 in pfs_spawn_thread (arg=0x7f1f54971fc0) at 5.5/storage/perfschema/pfs.cc:1015

#32 0x00007f1f5ac7cb50 in start_thread (arg=<optimized out>) at pthread_create.c:304

#33 0x00007f1f58f3270d in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:112

EXPLAIN EXTENDED

SELECT * FROM t1, t2, t3 WHERE f2 IN ( f1 IN ( SELECT f4 FROM t4 ) );

id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra

1	PRIMARY	t1	system	NULL	NULL	NULL	NULL	1	100.00	

1	PRIMARY	t2	ref	f2	f2	5	const	0	0.00	Using where; Using index

1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	100.00	Using join buffer (flat, BNL join)

2	MATERIALIZED	t4	ALL	NULL	NULL	NULL	NULL	2	100.00	

Warnings:

Note	1003	select 1 AS `f1`,`test`.`t2`.`f2` AS `f2`,`test`.`t3`.`f3` AS `f3` from `test`.`t2` join `test`.`t3` where (`test`.`t2`.`f2` = <cache>(<in_optimizer>(1,1 in ( <materialize> (select `test`.`t4`.`f4` from `test`.`t4` ), <primary_index_lookup>(1 in <temporary table> on distinct_key where ((1 = `<subquery2>`.`f4`)))))))

revno: 3765

revision-id: igor@askmonty.org-20140221052733-ypg4dpfcmy5l0b3q

parent: psergey@askmonty.org-20140219143412-rp3flx1pmhw6zawn

committer: Igor Babaev <igor@askmonty.org>

branch nick: maria-5.3

timestamp: Thu 2014-02-20 21:27:33 -0800

message:

  After constant row substitution the optimizer should call the method

  update_used_tables for the the where condition to update cached

  indicators of constant subexpressions. It should be done before further

  possible simplification of the where condition.

  This change caused simplification of the executed where conditions 

  in many test cases.