diff options
| -rw-r--r-- | mysql-test/r/subselect_sj.result | 31 | ||||
| -rw-r--r-- | mysql-test/r/subselect_sj_jcl6.result | 31 | ||||
| -rw-r--r-- | mysql-test/t/subselect_sj.test | 30 | ||||
| -rw-r--r-- | sql/opt_subselect.cc | 3 |
4 files changed, 94 insertions, 1 deletions
diff --git a/mysql-test/r/subselect_sj.result b/mysql-test/r/subselect_sj.result index 9c4368a809b..a7b3cffb8d2 100644 --- a/mysql-test/r/subselect_sj.result +++ b/mysql-test/r/subselect_sj.result @@ -2118,4 +2118,35 @@ a b 1 1 set optimizer_switch= @tmp_otimizer_switch; DROP TABLE t1,t2,t3; +# +# Bug #901709: assertion failure with record count == 0 +# +CREATE TABLE t1 (a int, KEY (a)); +INSERT INTO t1 VALUES (4), (6); +CREATE TABLE t2 (a int, KEY (a)); +INSERT INTO t2 VALUES (4), (6); +CREATE TABLE t3 (b int); +INSERT INTO t3 VALUES (4); +CREATE TABLE t4 (c int); +SET @tmp_optimizer_switch=@@optimizer_switch; +SET @@optimizer_switch='semijoin=on'; +SET @@optimizer_switch='materialization=on'; +SET @@optimizer_switch='firstmatch=on'; +SET optimizer_switch='semijoin_with_cache=on'; +SET optimizer_prune_level=0; +EXPLAIN +SELECT * FROM t1, t2 +WHERE t1.a = t2.a AND t2.a IN (SELECT b FROM t3 STRAIGHT_JOIN t4); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t3 system NULL NULL NULL NULL 1 +1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 1 +1 PRIMARY t1 ref a a 5 const 1 Using index +1 PRIMARY t2 ref a a 5 test.t3.b 1 Using index +2 MATERIALIZED t4 ALL NULL NULL NULL NULL 0 +SELECT * FROM t1, t2 +WHERE t1.a = t2.a AND t2.a IN (SELECT b FROM t3 STRAIGHT_JOIN t4); +a a +SET optimizer_prune_level=DEFAULT; +SET optimizer_switch=@tmp_optimizer_switch; +DROP TABLE t1,t2,t3,t4; set optimizer_switch=@subselect_sj_tmp; diff --git a/mysql-test/r/subselect_sj_jcl6.result b/mysql-test/r/subselect_sj_jcl6.result index 18882ce1db3..8de5af4cf56 100644 --- a/mysql-test/r/subselect_sj_jcl6.result +++ b/mysql-test/r/subselect_sj_jcl6.result @@ -2124,6 +2124,37 @@ a b 1 1 set optimizer_switch= @tmp_otimizer_switch; DROP TABLE t1,t2,t3; +# +# Bug #901709: assertion failure with record count == 0 +# +CREATE TABLE t1 (a int, KEY (a)); +INSERT INTO t1 VALUES (4), (6); +CREATE TABLE t2 (a int, KEY (a)); +INSERT INTO t2 VALUES (4), (6); +CREATE TABLE t3 (b int); +INSERT INTO t3 VALUES (4); +CREATE TABLE t4 (c int); +SET @tmp_optimizer_switch=@@optimizer_switch; +SET @@optimizer_switch='semijoin=on'; +SET @@optimizer_switch='materialization=on'; +SET @@optimizer_switch='firstmatch=on'; +SET optimizer_switch='semijoin_with_cache=on'; +SET optimizer_prune_level=0; +EXPLAIN +SELECT * FROM t1, t2 +WHERE t1.a = t2.a AND t2.a IN (SELECT b FROM t3 STRAIGHT_JOIN t4); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t3 system NULL NULL NULL NULL 1 +1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 1 +1 PRIMARY t1 ref a a 5 const 1 Using index +1 PRIMARY t2 ref a a 5 test.t3.b 1 Using index +2 MATERIALIZED t4 ALL NULL NULL NULL NULL 0 +SELECT * FROM t1, t2 +WHERE t1.a = t2.a AND t2.a IN (SELECT b FROM t3 STRAIGHT_JOIN t4); +a a +SET optimizer_prune_level=DEFAULT; +SET optimizer_switch=@tmp_optimizer_switch; +DROP TABLE t1,t2,t3,t4; set optimizer_switch=@subselect_sj_tmp; # # BUG#49129: Wrong result with IN-subquery with join_cache_level=6 and firstmatch=off diff --git a/mysql-test/t/subselect_sj.test b/mysql-test/t/subselect_sj.test index 130a5251846..4f8ed7f1643 100644 --- a/mysql-test/t/subselect_sj.test +++ b/mysql-test/t/subselect_sj.test @@ -1937,5 +1937,35 @@ set optimizer_switch= @tmp_otimizer_switch; DROP TABLE t1,t2,t3; +--echo # +--echo # Bug #901709: assertion failure with record count == 0 +--echo # + +CREATE TABLE t1 (a int, KEY (a)); +INSERT INTO t1 VALUES (4), (6); +CREATE TABLE t2 (a int, KEY (a)); +INSERT INTO t2 VALUES (4), (6); +CREATE TABLE t3 (b int); +INSERT INTO t3 VALUES (4); +CREATE TABLE t4 (c int); + +SET @tmp_optimizer_switch=@@optimizer_switch; +SET @@optimizer_switch='semijoin=on'; +SET @@optimizer_switch='materialization=on'; +SET @@optimizer_switch='firstmatch=on'; +SET optimizer_switch='semijoin_with_cache=on'; +SET optimizer_prune_level=0; + +EXPLAIN +SELECT * FROM t1, t2 + WHERE t1.a = t2.a AND t2.a IN (SELECT b FROM t3 STRAIGHT_JOIN t4); +SELECT * FROM t1, t2 + WHERE t1.a = t2.a AND t2.a IN (SELECT b FROM t3 STRAIGHT_JOIN t4); + +SET optimizer_prune_level=DEFAULT; +SET optimizer_switch=@tmp_optimizer_switch; + +DROP TABLE t1,t2,t3,t4; + # The following command must be the last one the file set optimizer_switch=@subselect_sj_tmp; diff --git a/sql/opt_subselect.cc b/sql/opt_subselect.cc index 217966eb296..ff0b80acb0e 100644 --- a/sql/opt_subselect.cc +++ b/sql/opt_subselect.cc @@ -2629,7 +2629,8 @@ bool Firstmatch_picker::check_qep(JOIN *join, - read_time is the same (i.e. FirstMatch doesn't add any cost - remove fanout added by the last table */ - *record_count /= join->positions[idx].records_read; + if (*record_count) + *record_count /= join->positions[idx].records_read; } else { |