From e5bca74bfb47a1c45c995573fd58b1a751ccd884 Mon Sep 17 00:00:00 2001 From: unknown Date: Fri, 18 May 2012 14:52:01 +0300 Subject: Fixed bug mdev-277 as part of the fix for lp:944706 The cause for this bug is that the method JOIN::get_examined_rows iterates over all JOIN_TABs of the join assuming they are just a sequence. In the query above, the innermost subquery is merged into its parent query. When we call JOIN::get_examined_rows for the second-level subquery, the iteration that assumes sequential order of join tabs goes outside the join_tab array and calls the method JOIN_TAB::get_examined_rows on uninitialized memory. The fix is to iterate over JOIN_TABs in a way that takes into account the nested semi-join structure of JOIN_TABs. In particular iterate as select_describe. --- mysql-test/r/subselect_no_semijoin.result | 21 +++++++++++++++++++++ 1 file changed, 21 insertions(+) (limited to 'mysql-test/r/subselect_no_semijoin.result') diff --git a/mysql-test/r/subselect_no_semijoin.result b/mysql-test/r/subselect_no_semijoin.result index f0186c682a4..6bb6b2861f9 100644 --- a/mysql-test/r/subselect_no_semijoin.result +++ b/mysql-test/r/subselect_no_semijoin.result @@ -6589,6 +6589,27 @@ WHERE alias1.a = alias2.a OR ('Moscow') IN ( SELECT a FROM t1 ); MAX( alias2.a ) Arden-Arcade drop table t1; +# +# MDEV-277 CHEAP SQ: Server crashes in st_join_table::get_examined_rows +# with semijoin+materialization, IN and = subqueries +# +CREATE TABLE t1 (a1 INT); +INSERT INTO t1 VALUES (4),(6); +CREATE TABLE t2 (b1 INT); +INSERT INTO t2 VALUES (1),(7); +EXPLAIN +SELECT * FROM t1 +WHERE a1 = (SELECT COUNT(*) FROM t1 WHERE a1 IN (SELECT a1 FROM t1, t2)); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where +2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 +2 SUBQUERY eq_ref distinct_key distinct_key 4 func 1 +3 MATERIALIZED t1 ALL NULL NULL NULL NULL 2 +3 MATERIALIZED t2 ALL NULL NULL NULL NULL 2 Using join buffer (flat, BNL join) +SELECT * FROM t1 +WHERE a1 = (SELECT COUNT(*) FROM t1 WHERE a1 IN (SELECT a1 FROM t1, t2)); +a1 +drop table t1, t2; # return optimizer switch changed in the beginning of this test set optimizer_switch=@subselect_tmp; set @optimizer_switch_for_subselect_test=null; -- cgit v1.2.1