Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-3774

LP:718703 - EXPLAIN evaluates subqueries when inside VIEWs

    XMLWordPrintable

    Details

      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,

        Attachments

          Activity

            People

            Assignee:
            timour Timour Katchaounov (Inactive)
            Reporter:
            philipstoev Philip Stoev (Inactive)
            Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved: