Jørgen Løland wrote:
> Oystein,
>
> Patch approved, but I recommend two changes:
> - Remove engine=myisam so that the test will run on various engines
I usually do, but left it there since I am not able to check what is
sufficient to reproduce the job. But I guess it is little chance that
this is storage engine related, so I will remove this.
> - Since you do not know what fixed this bug, you should be careful to
> run the test with the specified optimizer switches turned on.
> Just remember to reset them afterwards...
I do not quite understand what you mean here. Do you think I should run
this with different optimizer settings? I checked with Patrick, and he
said it occurred with everything turned on. Anyway, I think the most
important here is to make sure that this works with whatever is the
current settings, not to find with which combination of optimizer_switch
settings one will be able to reproduce it.
--
Øystein
>
> Oystein.Grovlen@stripped wrote:
>> #At file:///home/oysteing/mysql/mysql-6.0-codebase-bugfixing/ based on
>> revid:guilhem@stripped
>>
>> 3718 oystein.grovlen@stripped 2009-11-19
>> Bug#46797 - Crash in fix_semijoin_strategies_for_picked_join_order
>> with semijoin=on
>> Adding test case from this bug report. Has not been able
>> to reproduce the failure, not even on a few older versions of
>> the code.
>> @ mysql-test/r/subselect_sj.result
>> Updated result file with new test for Bug#46797
>> @ mysql-test/r/subselect_sj_jcl6.result
>> Updated result file with new test for Bug#46797
>> @ mysql-test/t/subselect_sj.test
>> Added test case from Bug#46797
>>
>> modified:
>> mysql-test/r/subselect_sj.result
>> mysql-test/r/subselect_sj_jcl6.result
>> mysql-test/t/subselect_sj.test
>> === modified file 'mysql-test/r/subselect_sj.result'
>> --- a/mysql-test/r/subselect_sj.result 2009-11-17 10:12:07 +0000
>> +++ b/mysql-test/r/subselect_sj.result 2009-11-19 09:59:19 +0000
>> @@ -463,3 +463,40 @@ int_key
>> 7
>> DROP TABLE t0, t1, t2;
>> # End of bug#46550
>> +
>> +Bug#46797 "Crash in fix_semijoin_strategies_for_picked_join_order
>> +with semijoin=on"
>> +
>> +CREATE TABLE t1 (
>> +varchar_key varchar(1) DEFAULT NULL,
>> +KEY varchar_key (varchar_key)
>> +) ENGINE=MyISAM DEFAULT CHARSET=latin1;
>> +CREATE TABLE t2 (
>> +varchar_key varchar(1) DEFAULT NULL,
>> +KEY varchar_key (varchar_key)
>> +) ENGINE=MyISAM DEFAULT CHARSET=latin1;
>> +INSERT INTO t2 VALUES
>> +(NULL),(NULL),(NULL),(NULL),('a'),('a'),('a'),('b'),('b'),('b'),('b'),('c'),
>>
>> +('c'),('c'),('c'),('c'),('c'),('c'),('d'),('d'),('d'),('d'),('d'),('d'),('e'),
>>
>> +('e'),('e'),('e'),('e'),('e'),('f'),('f'),('f'),('g'),('g'),('h'),('h'),('h'),
>>
>> +('h'),('i'),('j'),('j'),('j'),('k'),('k'),('l'),('l'),('m'),('m'),('m'),('m'),
>>
>> +('n'),('n'),('n'),('o'),('o'),('o'),('p'),('p'),('p'),('q'),('q'),('q'),('r'),
>>
>> +('r'),('r'),('r'),('s'),('s'),('s'),('s'),('t'),('t'),('t'),('t'),('u'),('u'),
>>
>> +('u'),('u'),('v'),('v'),('v'),('v'),('w'),('w'),('w'),('w'),('w'),('w'),('x'),
>>
>> +('x'),('x'),('y'),('y'),('y'),('y'),('z'),('z'),('z'),('z');
>> +CREATE TABLE t3 (
>> +varchar_key varchar(1) DEFAULT NULL,
>> +KEY varchar_key (varchar_key)
>> +) ENGINE=MyISAM DEFAULT CHARSET=latin1;
>> +INSERT INTO t3 VALUES
>> +(NULL),('c'),('d'),('e'),('f'),('h'),('j'),('k'),('k'),('m'),('m'),('m'),
>>
>> +('n'),('o'),('r'),('t'),('t'),('u'),('w'),('y');
>> +SELECT varchar_key FROM t3 +WHERE (SELECT varchar_key FROM t3 +WHERE
>> (varchar_key,varchar_key) +IN (SELECT t1.varchar_key, t2 .varchar_key
>> +FROM t1 RIGHT JOIN t2 ON t1.varchar_key +) +);
>> +varchar_key
>> +DROP TABLE t1, t2, t3;
>>
>> === modified file 'mysql-test/r/subselect_sj_jcl6.result'
>> --- a/mysql-test/r/subselect_sj_jcl6.result 2009-11-17 10:12:07 +0000
>> +++ b/mysql-test/r/subselect_sj_jcl6.result 2009-11-19 09:59:19 +0000
>> @@ -467,6 +467,43 @@ int_key
>> 7
>> DROP TABLE t0, t1, t2;
>> # End of bug#46550
>> +
>> +Bug#46797 "Crash in fix_semijoin_strategies_for_picked_join_order
>> +with semijoin=on"
>> +
>> +CREATE TABLE t1 (
>> +varchar_key varchar(1) DEFAULT NULL,
>> +KEY varchar_key (varchar_key)
>> +) ENGINE=MyISAM DEFAULT CHARSET=latin1;
>> +CREATE TABLE t2 (
>> +varchar_key varchar(1) DEFAULT NULL,
>> +KEY varchar_key (varchar_key)
>> +) ENGINE=MyISAM DEFAULT CHARSET=latin1;
>> +INSERT INTO t2 VALUES
>> +(NULL),(NULL),(NULL),(NULL),('a'),('a'),('a'),('b'),('b'),('b'),('b'),('c'),
>>
>> +('c'),('c'),('c'),('c'),('c'),('c'),('d'),('d'),('d'),('d'),('d'),('d'),('e'),
>>
>> +('e'),('e'),('e'),('e'),('e'),('f'),('f'),('f'),('g'),('g'),('h'),('h'),('h'),
>>
>> +('h'),('i'),('j'),('j'),('j'),('k'),('k'),('l'),('l'),('m'),('m'),('m'),('m'),
>>
>> +('n'),('n'),('n'),('o'),('o'),('o'),('p'),('p'),('p'),('q'),('q'),('q'),('r'),
>>
>> +('r'),('r'),('r'),('s'),('s'),('s'),('s'),('t'),('t'),('t'),('t'),('u'),('u'),
>>
>> +('u'),('u'),('v'),('v'),('v'),('v'),('w'),('w'),('w'),('w'),('w'),('w'),('x'),
>>
>> +('x'),('x'),('y'),('y'),('y'),('y'),('z'),('z'),('z'),('z');
>> +CREATE TABLE t3 (
>> +varchar_key varchar(1) DEFAULT NULL,
>> +KEY varchar_key (varchar_key)
>> +) ENGINE=MyISAM DEFAULT CHARSET=latin1;
>> +INSERT INTO t3 VALUES
>> +(NULL),('c'),('d'),('e'),('f'),('h'),('j'),('k'),('k'),('m'),('m'),('m'),
>>
>> +('n'),('o'),('r'),('t'),('t'),('u'),('w'),('y');
>> +SELECT varchar_key FROM t3 +WHERE (SELECT varchar_key FROM t3 +WHERE
>> (varchar_key,varchar_key) +IN (SELECT t1.varchar_key, t2 .varchar_key
>> +FROM t1 RIGHT JOIN t2 ON t1.varchar_key +) +);
>> +varchar_key
>> +DROP TABLE t1, t2, t3;
>> set join_cache_level=default;
>> show variables like 'join_cache_level';
>> Variable_name Value
>>
>> === modified file 'mysql-test/t/subselect_sj.test'
>> --- a/mysql-test/t/subselect_sj.test 2009-11-17 10:12:07 +0000
>> +++ b/mysql-test/t/subselect_sj.test 2009-11-19 09:59:19 +0000
>> @@ -357,3 +357,44 @@ WHERE t0.varchar_nokey IN ( DROP TABLE t0, t1,
>> t2;
>>
>> --echo # End of bug#46550
>> +
>> +--echo
>> +--echo Bug#46797 "Crash in
>> fix_semijoin_strategies_for_picked_join_order +--echo with semijoin=on"
>> +--echo
>> +CREATE TABLE t1 (
>> + varchar_key varchar(1) DEFAULT NULL,
>> + KEY varchar_key (varchar_key)
>> +) ENGINE=MyISAM DEFAULT CHARSET=latin1;
>> +
>> +CREATE TABLE t2 (
>> + varchar_key varchar(1) DEFAULT NULL,
>> + KEY varchar_key (varchar_key)
>> +) ENGINE=MyISAM DEFAULT CHARSET=latin1;
>> +INSERT INTO t2 VALUES
>> +
>> (NULL),(NULL),(NULL),(NULL),('a'),('a'),('a'),('b'),('b'),('b'),('b'),('c'),
>>
>> +
>> ('c'),('c'),('c'),('c'),('c'),('c'),('d'),('d'),('d'),('d'),('d'),('d'),('e'),
>>
>> +
>> ('e'),('e'),('e'),('e'),('e'),('f'),('f'),('f'),('g'),('g'),('h'),('h'),('h'),
>>
>> +
>> ('h'),('i'),('j'),('j'),('j'),('k'),('k'),('l'),('l'),('m'),('m'),('m'),('m'),
>>
>> +
>> ('n'),('n'),('n'),('o'),('o'),('o'),('p'),('p'),('p'),('q'),('q'),('q'),('r'),
>>
>> +
>> ('r'),('r'),('r'),('s'),('s'),('s'),('s'),('t'),('t'),('t'),('t'),('u'),('u'),
>>
>> +
>> ('u'),('u'),('v'),('v'),('v'),('v'),('w'),('w'),('w'),('w'),('w'),('w'),('x'),
>>
>> + ('x'),('x'),('y'),('y'),('y'),('y'),('z'),('z'),('z'),('z');
>> +
>> +CREATE TABLE t3 (
>> + varchar_key varchar(1) DEFAULT NULL,
>> + KEY varchar_key (varchar_key)
>> +) ENGINE=MyISAM DEFAULT CHARSET=latin1;
>> +INSERT INTO t3 VALUES
>> +
>> (NULL),('c'),('d'),('e'),('f'),('h'),('j'),('k'),('k'),('m'),('m'),('m'),
>> + ('n'),('o'),('r'),('t'),('t'),('u'),('w'),('y');
>> + +SELECT varchar_key FROM t3 +WHERE (SELECT varchar_key FROM t3
>> + WHERE (varchar_key,varchar_key) + IN (SELECT
>> t1.varchar_key, t2 .varchar_key + FROM t1 RIGHT JOIN t2 ON
>> t1.varchar_key + ) + );
>> +
>> +DROP TABLE t1, t2, t3;
>>
>>
>>
>> ------------------------------------------------------------------------
>>
>>
>
>
