EXPLAIN evaluates subqueries when inside VIEWs
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
MariaDB |
Invalid
|
Wishlist
|
Timour Katchaounov |
Bug 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.
| 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,
| 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=
#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:
#8 0x08495678 in DsMrr_impl:
buf=0x91000030) at multi_range_
#9 0x084ad9ee in ha_myisam:
at ha_myisam.cc:2224
#10 0x083b2e0b in QUICK_RANGE_
#11 0x08325f86 in join_init_
#12 0x08325e28 in join_init_
#13 0x08324500 in sub_select (join=0xa75ca2d8, join_tab=
#14 0x08324bcf in evaluate_
#15 0x083246a9 in sub_select (join=0xa75ca2d8, join_tab=
#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_
#19 0x08237917 in Item_subselect:
#20 0x08239385 in Item_exists_
#21 0x081fee87 in Item_func_
#22 0x081c0962 in Item::val_bool (this=0xa7598a80) at item.cc:187
#23 0x0820aa83 in Item_cond_
#24 0x08242486 in Item_func_
#25 0x081c0962 in Item::val_bool (this=0xa75bf6b0) at item.cc:187
#26 0x0820a98b in Item_cond_
#27 0x083248f7 in evaluate_
#28 0x083246a9 in sub_select (join=0xa75b8d98, join_tab=
#29 0x08324bcf in evaluate_
#30 0x083246a9 in sub_select (join=0xa75b8d98, join_tab=
#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_
order=0x0, group=0x0, having=0xa7599a20, proc_param=0x0, select_
at sql_select.cc:2667
#34 0x084499c8 in mysql_derived_
#35 0x08449303 in mysql_handle_
#36 0x082ed03e in open_and_
#37 0x082a8ac7 in open_and_
#38 0x082a1e98 in execute_
#39 0x082990bb in mysql_execute_
#40 0x082a4644 in mysql_parse (thd=0xb453bf8, rawbuf=0xa758af28 "EXPLAIN /*!50100 PARTITIONS */ SELECT * FROM transforms.
Related branches
Changed in maria: | |
importance: | Undecided → Low |
Changed in maria: | |
importance: | Low → Wishlist |
Changed in maria: | |
status: | New → Triaged |
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.