#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;
Attachment: [text/bzr-bundle]
