Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.0.0, 5.5.28
-
None
-
None
Description
#2 0x00000000007c1b14 in handle_fatal_signal (sig=11) at maria-5.5/sql/signal_handler.cc:262
|
#3 <signal handler called>
|
#4 0x00000000007e0ae2 in Item_field::print (this=0x3c5b9d0, str=0x7f83f41bb030, query_type=QT_ORDINARY) at maria-5.5/sql/item.cc:6554
|
#5 0x000000000081bbc0 in Item_func::print_op (this=0x3c61698, str=0x7f83f41bb030, query_type=QT_ORDINARY) at maria-5.5/sql/item_func.cc:490
|
#6 0x00000000008034e7 in Item_bool_func2::print (this=0x3c61698, str=0x7f83f41bb030, query_type=QT_ORDINARY) at maria-5.5/sql/item_cmpfunc.h:384
|
#7 0x00000000007fe987 in Item_cond::print (this=0x3c5cf70, str=0x7f83f41bb030, query_type=QT_ORDINARY) at maria-5.5/sql/item_cmpfunc.cc:4559
|
#8 0x0000000000674443 in st_select_lex::print (this=0x3be3cd0, thd=0x3abb300, str=0x7f83f41bb030, query_type=QT_ORDINARY) at maria-5.5/sql/sql_select.cc:22446
|
#9 0x000000000085d272 in subselect_single_select_engine::print (this=0x3be7778, str=0x7f83f41bb030, query_type=QT_ORDINARY) at maria-5.5/sql/item_subselect.cc:3695
|
#10 0x000000000085560c in Item_subselect::print (this=0x3be7640, str=0x7f83f41bb030, query_type=QT_ORDINARY) at maria-5.5/sql/item_subselect.cc:874
|
#11 0x000000000066e935 in change_to_use_tmp_fields (thd=0x3abb300, ref_pointer_array=0x3c43cf8, res_selected_fields=..., res_all_fields=..., elements=2, all_fields=...) at maria-5.5/sql/sql_select.cc:20637
|
#12 0x000000000064219d in JOIN::exec (this=0x3c43780) at maria-5.5/sql/sql_select.cc:2421
|
#13 0x00000000006444c7 in mysql_select (thd=0x3abb300, rref_pointer_array=0x3bd66c8, tables=0x3be2a40, wild_num=0, fields=..., conds=0x0, og_num=3, order=0x3c42ee0, group=0x3be77b8, having=0x0, proc_param=0x0, select_options=2416184064, result=0x3c43690, unit=0x3bd5d98, select_lex=0x3bd6470) at maria-5.5/sql/sql_select.cc:3055
|
#14 0x00000000005e82d1 in mysql_derived_fill (thd=0x3abb300, lex=0x3abd680, derived=0x3bbc4c0) at maria-5.5/sql/sql_derived.cc:885
|
#15 0x00000000005e6f76 in mysql_handle_single_derived (lex=0x3abd680, derived=0x3bbc4c0, phases=96) at maria-5.5/sql/sql_derived.cc:179
|
#16 0x0000000000656477 in st_join_table::preread_init (this=0x3c6dff8) at maria-5.5/sql/sql_select.cc:10380
|
#17 0x0000000000663a51 in sub_select (join=0x3c43178, join_tab=0x3c6dff8, end_of_records=false) at maria-5.5/sql/sql_select.cc:16083
|
#18 0x00000000006634b3 in do_select (join=0x3c43178, fields=0x3abdf18, table=0x0, procedure=0x0) at maria-5.5/sql/sql_select.cc:15785
|
#19 0x0000000000643cca in JOIN::exec (this=0x3c43178) at maria-5.5/sql/sql_select.cc:2835
|
#20 0x00000000006444c7 in mysql_select (thd=0x3abb300, rref_pointer_array=0x3abe060, tables=0x3bbc4c0, wild_num=1, fields=..., conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x3c43158, unit=0x3abd730, select_lex=0x3abde08) at maria-5.5/sql/sql_select.cc:3055
|
#21 0x000000000063b019 in handle_select (thd=0x3abb300, lex=0x3abd680, result=0x3c43158, setup_tables_done_option=0) at maria-5.5/sql/sql_select.cc:316
|
#22 0x00000000006142b5 in execute_sqlcom_select (thd=0x3abb300, all_tables=0x3bbc4c0) at maria-5.5/sql/sql_parse.cc:4615
|
#23 0x000000000060cdc4 in mysql_execute_command (thd=0x3abb300) at maria-5.5/sql/sql_parse.cc:2182
|
#24 0x0000000000616b1d in mysql_parse (thd=0x3abb300, rawbuf=0x3bbc2e8 "SELECT * FROM v", length=15, parser_state=0x7f83f41bc500) at maria-5.5/sql/sql_parse.cc:5730
|
#25 0x000000000060a337 in dispatch_command (command=COM_QUERY, thd=0x3abb300, packet=0x3c3dee1 "", packet_length=15) at maria-5.5/sql/sql_parse.cc:1055
|
#26 0x00000000006095ee in do_command (thd=0x3abb300) at maria-5.5/sql/sql_parse.cc:794
|
#27 0x000000000070be7b in do_handle_one_connection (thd_arg=0x3abb300) at maria-5.5/sql/sql_connect.cc:1253
|
#28 0x000000000070b866 in handle_one_connection (arg=0x3abb300) at maria-5.5/sql/sql_connect.cc:1168
|
#29 0x0000000000b8e10f in pfs_spawn_thread (arg=0x3c3cc40) at maria-5.5/storage/perfschema/pfs.cc:1015
|
#30 0x00007f83ff81aefc in start_thread (arg=0x7f83f41bd700) at pthread_create.c:304
|
branch: maria/5.5
|
bzr version-info
|
revision-id: wlad@montyprogram.com-20121120142439-zvx42vxhc8lurmnv
|
date: 2012-11-20 15:24:39 +0100
|
revno: 3576
|
branch-nick: maria-5.5
|
Built with BUILD/compile-pentium-debug-max-no-ndb
Reproducible with MyISAM, Aria, InnoDB.
Reproducible on current 5.5 and 10.0.
Could not reproduce on 5.3 and on MySQL 5.6 (in both cases it throws a warning "View merge algorithm can't be used here for now (assumed undefined algorithm)").
Minimal optimizer_switch: materialization=on,semijoin=on
Default optimizer_switch:
index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=off
|
EXPLAIN with the default optimizer_switch:
EXPLAIN EXTENDED
|
SELECT * FROM v;
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 4 100.00
|
2 DERIVED t2 ALL NULL NULL NULL NULL 2 100.00 Using temporary
|
2 DERIVED table1 ALL NULL NULL NULL NULL 2 100.00 Using join buffer (flat, BNL join)
|
7 SUBQUERY <subquery8> const distinct_key distinct_key 8 const,const 1 100.00
|
7 SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 Using join buffer (flat, BNL join)
|
8 MATERIALIZED t2 ALL NULL NULL NULL NULL 2 100.00
|
8 MATERIALIZED t3 ALL NULL NULL NULL NULL 2 100.00 Using join buffer (flat, BNL join)
|
5 SUBQUERY <subquery6> const distinct_key distinct_key 8 const,const 1 100.00
|
5 SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 Using join buffer (flat, BNL join)
|
6 MATERIALIZED t2 ALL NULL NULL NULL NULL 2 100.00
|
6 MATERIALIZED t3 ALL NULL NULL NULL NULL 2 100.00 Using join buffer (flat, BNL join)
|
3 SUBQUERY <subquery4> const distinct_key distinct_key 8 const,const 1 100.00
|
3 SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 Using join buffer (flat, BNL join)
|
4 MATERIALIZED t2 ALL NULL NULL NULL NULL 2 100.00
|
4 MATERIALIZED t3 ALL NULL NULL NULL NULL 2 100.00 Using join buffer (flat, BNL join)
|
Warnings:
|
Note 1003 select `v`.`field1` AS `field1`,`v`.`field2` AS `field2` from `test`.`v`
|
Test case:
SET optimizer_switch = 'materialization=on,semijoin=on'; |
|
|
CREATE TABLE t1 (a INT) ENGINE=MyISAM; |
INSERT INTO t1 VALUES (1),(7); |
|
|
CREATE TABLE t2 (b INT) ENGINE=MyISAM; |
INSERT INTO t2 VALUES (4),(6); |
|
|
CREATE TABLE t3 (c INT) ENGINE=MyISAM; |
INSERT INTO t3 VALUES (1),(2); |
|
|
|
|
CREATE ALGORITHM=MERGE VIEW v AS SELECT |
( SELECT a FROM t1 WHERE ( 1, 1 ) IN ( |
SELECT b, c FROM t2, t3 HAVING c > 2 ) ) AS field1, |
b + c AS field2 |
FROM t2, t3 AS table1 |
GROUP BY field1, field2 ORDER BY field1; |
|
|
SELECT * FROM v; |