[MDEV-3774] LP:718703 - EXPLAIN evaluates subqueries when inside VIEWs Created: 2011-02-14  Updated: 2012-10-04  Resolved: 2012-10-04

Status: Closed
Project: MariaDB Server
Component/s: None
Fix Version/s: None

Type: Task Priority: Minor
Reporter: Philip Stoev (Inactive) Assignee: Timour Katchaounov (Inactive)
Resolution: Not a Bug Votes: 0
Labels: Launchpad, MariaDB_5.3

Attachments: XML File LPexportBug718703.xml     Zip Archive LPexportBug718703_bug718703.dump.zip    

 Description   

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,

 Comments   
Comment by Philip Stoev (Inactive) [ 2011-02-14 ]

Re: EXPLAIN evaluates subqueries when inside VIEWs
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.

Comment by Philip Stoev (Inactive) [ 2011-02-14 ]

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.
database dump
LPexportBug718703_bug718703.dump.zip

Comment by Timour Katchaounov (Inactive) [ 2011-05-06 ]

Re: EXPLAIN evaluates subqueries when inside VIEWs
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.

Comment by Timour Katchaounov (Inactive) [ 2011-07-08 ]

Re: EXPLAIN evaluates subqueries when inside VIEWs
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.

Comment by Rasmus Johansson (Inactive) [ 2011-07-08 ]

Launchpad bug id: 718703

Generated at Thu Feb 08 06:51:06 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.