EXPLAIN evaluates subqueries when inside VIEWs

[Philip Stoev]

If a subquery is present in a view, EXPLAIN evaluates the query and takes as much time as executing the query itself. Executing EXPLAIN directly on the query from the view definition is instantaneous.

MariaDB [test]> EXPLAIN /*!50100 PARTITIONS */ SELECT * FROM transforms.view_1706_merge; +----+--------------------+------------+------------+--------+-------------------------+-----------------+---------+--------------------+------+-------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+------------+------------+--------+-------------------------+-----------------+---------+--------------------+------+-------------------------------------------------+
| 1 | PRIMARY | <derived2> | NULL | ALL | NULL | NULL | NULL | NULL | 160 | |
| 2 | DERIVED | alias1 | NULL | ALL | PRIMARY,col_varchar_key | NULL | NULL | NULL | 100 | |
| 2 | DERIVED | alias2 | NULL | ALL | NULL | NULL | NULL | NULL | 100 | Using where |
| 3 | DEPENDENT SUBQUERY | SQ1_alias1 | NULL | index | NULL | col_varchar_key | 9 | NULL | 20 | Using index |
| 3 | DEPENDENT SUBQUERY | SQ1_alias2 | NULL | ALL | col_varchar_key | NULL | NULL | NULL | 20 | Range checked for each record (index map: 0x20) |
| 3 | DEPENDENT SUBQUERY | SQ1_alias3 | NULL | eq_ref | PRIMARY,col_int_key | PRIMARY | 4 | test.SQ1_alias2.pk | 1 | Using index condition; Using where |
+----+--------------------+------------+------------+--------+-------------------------+-----------------+---------+--------------------+------+-------------------------------------------------+
6 rows in set (28.86 sec)

backtrace during EXPLAIN which shows that handler functions are being called:

#0 0x08749bea in _checkchunk (irem=0xb6cca220, filename=0x88f3990 "ha_myisam.cc", lineno=776) at safemalloc.c:488
#1 0x08749e08 in _sanity (filename=0x88f3990 "ha_myisam.cc", lineno=776) at safemalloc.c:533
#2 0x0874944d in _myfree (ptr=0xa75c3398, filename=0x88f3990 "ha_myisam.cc", lineno=776, myflags=0) at safemalloc.c:279
#3 0x084a948a in ha_myisam::open (this=0x90c44f10, name=0xb6cb7920 "./test/C", mode=39, test_if_locked=2) at ha_myisam.cc:776
#4 0x083cf02e in handler::ha_open (this=0x90c44f10, table_arg=0xb4d8758, name=0xb6cb7920 "./test/C", mode=39, test_if_locked=2) at handler.cc:2108
#5 0x083cedbf in handler::clone (this=0xb534698, mem_root=0xb455ba8) at handler.cc:2050
#6 0x084a8b93 in ha_myisam::clone (this=0xb534698, mem_root=0xb455ba8) at ha_myisam.cc:557
#7 0x084959cd in DsMrr_impl::setup_two_handlers (this=0xb534a0c) at multi_range_read.cc:947
#8 0x08495678 in DsMrr_impl::dsmrr_init (this=0xb534a0c, h_arg=0xb534698, seq_funcs=0x915dd7c0, seq_init_param=0xa7567d40, n_ranges=1, mode=4,
    buf=0x91000030) at multi_range_read.cc:871
#9 0x084ad9ee in ha_myisam::multi_range_read_init (this=0xb534698, seq=0x915dd7c0, seq_init_param=0xa7567d40, n_ranges=1, mode=4, buf=0x91000030)
    at ha_myisam.cc:2224
#10 0x083b2e0b in QUICK_RANGE_SELECT::reset (this=0xa7567d40) at opt_range.cc:10500
#11 0x08325f86 in join_init_read_record (tab=0xa759ad68) at sql_select.cc:14289
#12 0x08325e28 in join_init_quick_read_record (tab=0xa759ad68) at sql_select.cc:14258
#13 0x08324500 in sub_select (join=0xa75ca2d8, join_tab=0xa759ad68, end_of_records=false) at sql_select.cc:13516
#14 0x08324bcf in evaluate_join_record (join=0xa75ca2d8, join_tab=0xa759ab90, error=0) at sql_select.cc:13714
#15 0x083246a9 in sub_select (join=0xa75ca2d8, join_tab=0xa759ab90, end_of_records=false) at sql_select.cc:13559
#16 0x0832398b in do_select (join=0xa75ca2d8, fields=0xa75c8274, table=0x0, procedure=0x0) at sql_select.cc:13063
#17 0x0830b3cb in JOIN::exec (this=0xa75ca2d8) at sql_select.cc:2450
#18 0x0823d1d0 in subselect_single_select_engine::exec (this=0xa7598a58) at item_subselect.cc:2751
#19 0x08237917 in Item_subselect::exec (this=0xa7598988) at item_subselect.cc:550
#20 0x08239385 in Item_exists_subselect::val_bool (this=0xa7598988) at item_subselect.cc:1248
#21 0x081fee87 in Item_func_not::val_int (this=0xa7598a80) at item_cmpfunc.cc:287
#22 0x081c0962 in Item::val_bool (this=0xa7598a80) at item.cc:187
#23 0x0820aa83 in Item_cond_or::val_int (this=0xa75bf4c0) at item_cmpfunc.cc:4689
#24 0x08242486 in Item_func_trig_cond::val_int (this=0xa75bf6b0) at item_cmpfunc.h:449
#25 0x081c0962 in Item::val_bool (this=0xa75bf6b0) at item.cc:187
#26 0x0820a98b in Item_cond_and::val_int (this=0xa75bf7c0) at item_cmpfunc.cc:4671
#27 0x083248f7 in evaluate_join_record (join=0xa75b8d98, join_tab=0xa75bee70, error=0) at sql_select.cc:13614
#28 0x083246a9 in sub_select (join=0xa75b8d98, join_tab=0xa75bee70, end_of_records=false) at sql_select.cc:13559
#29 0x08324bcf in evaluate_join_record (join=0xa75b8d98, join_tab=0xa75bec98, error=0) at sql_select.cc:13714
#30 0x083246a9 in sub_select (join=0xa75b8d98, join_tab=0xa75bec98, end_of_records=false) at sql_select.cc:13559
#31 0x0832398b in do_select (join=0xa75b8d98, fields=0xa758c0b4, table=0x0, procedure=0x0) at sql_select.cc:13063
#32 0x0830b3cb in JOIN::exec (this=0xa75b8d98) at sql_select.cc:2450
#33 0x0830bb91 in mysql_select (thd=0xb453bf8, rref_pointer_array=0xa758c124, tables=0xa75c79a0, wild_num=0, fields=..., conds=0xa7598f98, og_num=0,
    order=0x0, group=0x0, having=0xa7599a20, proc_param=0x0, select_options=2416200192, result=0xa7599c00, unit=0xa758bd84, select_lex=0xa758c020)
    at sql_select.cc:2667
#34 0x084499c8 in mysql_derived_filling (thd=0xb453bf8, lex=0xb455298, orig_table_list=0xa758b0f0) at sql_derived.cc:295
#35 0x08449303 in mysql_handle_derived (lex=0xb455298, processor=0x84497e5 <mysql_derived_filling(THD*, LEX*, TABLE_LIST*)>) at sql_derived.cc:56
#36 0x082ed03e in open_and_lock_tables_derived (thd=0xb453bf8, tables=0xa758b0f0, derived=true) at sql_base.cc:5117
#37 0x082a8ac7 in open_and_lock_tables (thd=0xb453bf8, tables=0xa758b0f0) at mysql_priv.h:1631
#38 0x082a1e98 in execute_sqlcom_select (thd=0xb453bf8, all_tables=0xa758b0f0) at sql_parse.cc:5036
#39 0x082990bb in mysql_execute_command (thd=0xb453bf8) at sql_parse.cc:2234
#40 0x082a4644 in mysql_parse (thd=0xb453bf8, rawbuf=0xa758af28 "EXPLAIN /*!50100 PARTITIONS */ SELECT * FROM transforms.view_1706_merge", length=71,

[Philip Stoev (pstoev-askmonty)]

To reproduce, load the attached dump and then run EXPLAIN SELECT * FROM transforms.view_1706_merge;

Automatic simplification is not available for bugs like this one.

[Timour Katchaounov (timour)]

This bug is related to processing of derived tables, which will be changed
substantially by MWL#106 (http://askmonty.org/worklog/Server-Sprint/?tid=106).

The bug should be analyzed after MWL#106 is merged with MWL#89.

[Timour Katchaounov (timour)]

As expected, the bug is no longer reproducible after MWL#106
was merged into 5.3, because the core of this task was to avoid
evaluation of derived tables during optimization.