[MDEV-2912] LP:725050 - Crash with join_cache_hashed with semijoin and join_cache_level=3 Created: 2011-02-25  Updated: 2015-02-02  Resolved: 2012-10-04

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: None
Fix Version/s: None

Type: Bug Priority: Minor
Reporter: Timour Katchaounov (Inactive) Assignee: Igor Babaev
Resolution: Fixed Votes: 0
Labels: Launchpad

Attachments: XML File LPexportBug725050.xml    

 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;

 Comments   
Comment by Timour Katchaounov (Inactive) [ 2011-02-25 ]

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.

Comment by Timour Katchaounov (Inactive) [ 2011-02-25 ]

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.

Comment by Timour Katchaounov (Inactive) [ 2011-02-25 ]

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;

Comment by Igor Babaev [ 2011-02-28 ]

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

Comment by Rasmus Johansson (Inactive) [ 2011-12-13 ]

Launchpad bug id: 725050

Generated at Thu Feb 08 06:45:03 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.