|
Stack trace from maria/5.3 revno 3612:
#3 <signal handler called>
|
#4 0x000000000061ec55 in Item_func_trig_cond::val_int (this=0x3c2cd48) at item_cmpfunc.h:480
|
#5 0x000000000057bce6 in Item::val_bool (this=0x3c2cd48) at item.cc:199
|
#6 0x00000000005d726b in Item_cond_and::val_int (this=0x3c563a8) at item_cmpfunc.cc:4550
|
#7 0x0000000000735cdf in end_send_group (join=0x3c24088, join_tab=0x3c56b78, end_of_records=true) at sql_select.cc:16886
|
#8 0x0000000000732b12 in sub_select (join=0x3c24088, join_tab=0x3c56858, end_of_records=true) at sql_select.cc:15662
|
#9 0x00000000007325e7 in do_select (join=0x3c24088, fields=0x3c24468, table=0x0, procedure=0x0) at sql_select.cc:15384
|
#10 0x000000000071303d in JOIN::exec (this=0x3c24088) at sql_select.cc:2764
|
#11 0x0000000000618b9e in subselect_single_select_engine::exec (this=0x3c1f9f8) at item_subselect.cc:3141
|
#12 0x00000000006112f5 in Item_subselect::exec (this=0x3c1f7f8) at item_subselect.cc:588
|
#13 0x0000000000611837 in Item_in_subselect::exec (this=0x3c1f7f8) at item_subselect.cc:756
|
#14 0x0000000000613da9 in Item_in_subselect::val_bool (this=0x3c1f7f8) at item_subselect.cc:1577
|
#15 0x0000000000597dcd in Item::val_bool_result (this=0x3c1f7f8) at item.h:856
|
#16 0x00000000005cd863 in Item_in_optimizer::val_int (this=0x3c24c38) at item_cmpfunc.cc:1731
|
#17 0x000000000057bce6 in Item::val_bool (this=0x3c24c38) at item.cc:199
|
#18 0x00000000005c97d7 in Item_func_not::val_int (this=0x3c19058) at item_cmpfunc.cc:332
|
#19 0x000000000057bce6 in Item::val_bool (this=0x3c19058) at item.cc:199
|
#20 0x00000000005d726b in Item_cond_and::val_int (this=0x3c2b4d8) at item_cmpfunc.cc:4550
|
#21 0x000000000061ec89 in Item_func_trig_cond::val_int (this=0x3c2b6a8) at item_cmpfunc.h:480
|
#22 0x000000000057bce6 in Item::val_bool (this=0x3c2b6a8) at item.cc:199
|
#23 0x00000000005d726b in Item_cond_and::val_int (this=0x3c2b798) at item_cmpfunc.cc:4550
|
#24 0x00000000006df0a4 in SQL_SELECT::skip_record (this=0x3c2af38, thd=0x3b9d670) at opt_range.h:924
|
#25 0x00000000006e00d1 in JOIN_CACHE::check_match (this=0x3c2b968, rec_ptr=0x3c353b1 "") at sql_join_cache.cc:2445
|
#26 0x00000000006db980 in JOIN_CACHE::generate_full_extensions (this=0x3c2b968, rec_ptr=0x3c353b1 "") at sql_join_cache.cc:2388
|
#27 0x00000000006db75b in JOIN_CACHE::join_matching_records (this=0x3c2b968, skip_last=false) at sql_join_cache.cc:2288
|
#28 0x00000000006db182 in JOIN_CACHE::join_records (this=0x3c2b968, skip_last=false) at sql_join_cache.cc:2085
|
#29 0x00000000007328fd in sub_select_cache (join=0x3c22c68, join_tab=0x3c2a298, end_of_records=true) at sql_select.cc:15500
|
#30 0x0000000000732b12 in sub_select (join=0x3c22c68, join_tab=0x3c29f78, end_of_records=true) at sql_select.cc:15662
|
#31 0x00000000007325e7 in do_select (join=0x3c22c68, fields=0x3ba0248, table=0x0, procedure=0x0) at sql_select.cc:15384
|
#32 0x000000000071303d in JOIN::exec (this=0x3c22c68) at sql_select.cc:2764
|
#33 0x00000000007138c9 in mysql_select (thd=0x3b9d670, rref_pointer_array=0x3ba03d8, tables=0x3c1cd38, wild_num=1, fields=..., conds=0x3c212a8, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147764736, result=0x3c22c18, unit=0x3b9fc08, select_lex=0x3ba0130) at sql_select.cc:2985
|
#34 0x000000000070a283 in handle_select (thd=0x3b9d670, lex=0x3b9fb68, result=0x3c22c18, setup_tables_done_option=0) at sql_select.cc:286
|
#35 0x00000000006957a3 in execute_sqlcom_select (thd=0x3b9d670, all_tables=0x3c1cd38) at sql_parse.cc:5157
|
#36 0x000000000068c562 in mysql_execute_command (thd=0x3b9d670) at sql_parse.cc:2290
|
#37 0x00000000006981fb in mysql_parse (thd=0x3b9d670, rawbuf=0x3c0e738 "SELECT * FROM t1 LEFT JOIN ( \nSELECT * FROM t2 WHERE ( pk, pk ) NOT IN ( \nSELECT MIN(b), SUM(pk) FROM t1\n) \n) AS alias1 ON (a = c) \nWHERE b IS NULL OR a < 'u'", length=159, found_semicolon=0x7f190a76e7e0) at sql_parse.cc:6158
|
#38 0x0000000000689d08 in dispatch_command (command=COM_QUERY, thd=0x3b9d670, packet=0x3be6c31 "", packet_length=159) at sql_parse.cc:1228
|
#39 0x000000000068901c in do_command (thd=0x3b9d670) at sql_parse.cc:923
|
#40 0x0000000000685a77 in handle_one_connection (arg=0x3b9d670) at sql_connect.cc:1231
|
#41 0x00007f1913f79efc in start_thread (arg=0x7f190a76f700) at pthread_create.c:304
|
#42 0x00007f1913520f4d in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:112
|
The problem appeared in maria/5.3 with the following revision:
revno: 3608
|
revision-id: sanja@askmonty.org-20121228124146-bk5tzqdvi4yukdpj
|
parent: timour@askmonty.org-20121219135657-mbje1blpgw2qsw7j
|
committer: sanja@askmonty.org
|
branch nick: work-maria-5.3-MDEV-3873
|
timestamp: Fri 2012-12-28 14:41:46 +0200
|
message:
|
MDEV-3873 & MDEV-3876 & MDEV-3912 : Wrong result (extra rows) with ALL subquery
|
from a MERGE view.
|
Reproducible on maria/5.3, maria/5.5.
Minimal optimizer_switch: derived_merge=on,in_to_exists=on
Full optimizer_switch (default):
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 t1 LEFT JOIN (
|
SELECT * FROM t2 WHERE ( pk, pk ) NOT IN (
|
SELECT MIN(b), SUM(pk) FROM t1
|
)
|
) AS alias1 ON (a = c)
|
WHERE b IS NULL OR a < 'u';
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00
|
1 SIMPLE t2 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (flat, BNL join)
|
3 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00
|
Warnings:
|
Note 1276 Field or reference 'test.t2.b' of SELECT #3 was resolved in SELECT #2
|
Note 1276 Field or reference 'test.t2.pk' of SELECT #3 was resolved in SELECT #2
|
Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t2`.`pk` AS `pk`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t1` left join (`test`.`t2`) on(((not(<in_optimizer>((`test`.`t2`.`pk`,`test`.`t2`.`pk`),<exists>(select min(`test`.`t2`.`b`),sum(`test`.`t2`.`pk`) from `test`.`t1` having (trigcond(((<cache>(`test`.`t2`.`pk`) = min(`test`.`t2`.`b`)) or isnull(min(`test`.`t2`.`b`)))) and trigcond(((<cache>(`test`.`t2`.`pk`) = sum(`test`.`t2`.`pk`)) or isnull(sum(`test`.`t2`.`pk`)))) and trigcond(<is_not_null_test>(min(`test`.`t2`.`b`))) and trigcond(<is_not_null_test>(sum(`test`.`t2`.`pk`)))))))) and (`test`.`t2`.`c` = `test`.`t1`.`a`))) where (isnull(`test`.`t2`.`b`) or (`test`.`t1`.`a` < 'u'))
|
|
Test case:
SET optimizer_switch = 'derived_merge=on,in_to_exists=on';
|
|
CREATE TABLE t1 (a VARCHAR(1)) ENGINE=MyISAM;
|
INSERT INTO t1 VALUES ('x'),('d');
|
|
CREATE TABLE t2 (pk INT PRIMARY KEY, b INT, c VARCHAR(1)) ENGINE=MyISAM;
|
INSERT INTO t2 VALUES (1,2,'v'),(2,150,'v');
|
|
|
SELECT * FROM t1 LEFT JOIN (
|
SELECT * FROM t2 WHERE ( pk, pk ) NOT IN (
|
SELECT MIN(b), SUM(pk) FROM t1
|
)
|
) AS alias1 ON (a = c)
|
WHERE b IS NULL OR a < 'u';
|
|