Details
-
Type:
Bug
-
Status: Closed
-
Priority:
Minor
-
Resolution: Fixed
-
Affects Version/s: None
-
Fix Version/s: None
-
Component/s: None
-
Labels:
Description
The following example causes a crash in 5.3. The reason to set a
debug trace is because it forces the call to print_keyuse() via:
update_ref_and_keys -> print_keyuse_array -> print_keyuse.
create table t1 (a int not null);
set @@optimizer_switch='join_cache_hashed=on,semijoin=on';
set join_cache_level=3;
SET @@debug = 'd:t:O,/tmp/trace.out';
explain
select a from t1 where a in (
select a from t1 where a in (
select a from t1 where a in (
select a from t1 where a in (
select a from t1 where a in (
select a from t1 where a in (
select a from t1 where a in (
select a from t1 where a in (
select a from t1 where a in (
select a from t1 where a in (
select a from t1 where a in (
select a from t1 where a in (
select a from t1 where a in (
select a from t1 where a in (
select a from t1 where a in (
select a from t1 where a in (
select a from t1 where a in (
select a from t1 where a in (
select a from t1 where a in (
select a from t1 where a in (
select a from t1 where a in (
select a from t1 where a in (
select a from t1 where a in (
select a from t1 where a in (
select a from t1 where a in (
select a from t1 where a in (
select a from t1 where a in (
select a from t1 where a in (
select a from t1 where a in (
select a from t1 where a in (
select a from t1 where a in (
select a from t1 where a in (
select a from t1 where a in (
select a from t1 where a in (
select a from t1 where a in (
select a from t1 where a in (
select a from t1 where a in (
select a from t1 where a in (
select a from t1 where a in (
select a from t1 where a in (
select a from t1 where a in (
select a from t1 where a in (
select a from t1 where a in (
select a from t1 where a in (
select a from t1 where a in (
select a from t1 where a in (
select a from t1 where a in (
select a from t1 where a in (
select a from t1 where a in (
select a from t1 where a in (
select a from t1 where a in (
select a from t1 where a in (
select a from t1 where a in (
select a from t1 where a in (
select a from t1 where a in (
select a from t1 where a in (
select a from t1 where a in (
select a from t1 where a in (
select a from t1 where a in (
select a from t1 where a in (
select a from t1 where a in (
select a from t1 where a in ( select a from t1)
)))))))))))))))))))))))))))))))))))))))))))))))))))))))))))));
drop table t1;
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
Re: Carsh with join_cache_hashed with semijoin and join_cache_level=3
Notice that to reproduce the bug:
- join_cache_hashed = on
- join_cache_level > 2,
- semijoin = on
The test case was extracted from subselect_sj2_jcl6.test.
Re: Carsh with join_cache_hashed with semijoin and join_cache_level=3
Reduced test case.
It turns out that in 5.3 there is no need for
all the subquery levels, 2 are enough.
create table t1 (a int not null);
set @@optimizer_switch='join_cache_hashed=on,semijoin=on';
set join_cache_level=3;
SET @@debug = 'd:t:O,/tmp/trace.out';
explain
select a from t1 where a in ( select a from t1);
drop table t1;
Re: Crash with join_cache_hashed with semijoin and join_cache_level=3
Timour,
Your test case reproduces a bug in print_keyuse(KEYUSE *keyuse).
I've fixed this bug, but I'm not sure that this bug relates to your original
problem
Launchpad bug id: 725050
Re: Carsh with join_cache_hashed with semijoin and join_cache_level=3
The same example, but without setting a debug trace causes
a crash in 5.3-mwl89 in a different (but related) place, when
update_ref_and_keys is called for the first subquery
(counted from the outside->in). The call stack in 5.3-mwl89 is:
#0 add_key_field at sql_select.cc:3677
#1 add_key_fields at sql_select.cc:3981
#2 update_ref_and_keys at sql_select.cc:4332
#3 make_join_statistics at sql_select.cc:2955
#4 JOIN::optimize at sql_select.cc:952
#5 st_select_lex::optimize_unflattened_subqueries at sql_lex.cc:3140
#6 JOIN::optimize_unflattened_subqueries at opt_subselect.cc:3617
#7 JOIN::optimize at sql_select.cc:1447
#8 mysql_select
The reason for the difference is that 5.3-mwl89 optimizes subqueries
early during JOIN::optimize of the outer query.