From: Roy Lyseng Date: October 15 2010 10:33am Subject: bzr commit into mysql-next-mr-bugfixing branch (roy.lyseng:3263) Bug#31480 List-Archive: http://lists.mysql.com/commits/120822 X-Bug: 31480 Message-Id: <20101015103348.BD4011EC@tyr67.norway.sun.com> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="===============3168847291943368239==" --===============3168847291943368239== MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Content-Disposition: inline #At file:///home/rl136806/mysql/repo/mysql-work3/ based on revid:jorgen.loland@stripped 3263 Roy Lyseng 2010-10-15 Bug#31480: Incorrect result for nested subquery when executed via semijoin This problem may occur when we have a query containing at least two nested subqueries; one subquery is transformed into a semijoin, and a subquery that is inner to the transformed subquery (and is not itself transformed away) contains outer references either to the transformed subquery or to it's immediate outer query block. When this situation happens, the transformed query and it's immediate outer query block is consolidated into one query block. In this process, involved tables may be renumbered, and resolved information for the inner expressions may change. Example with a query: select t1.a from t1 where t1.a in (select t2.c from t2 where t2.d >= some(select t3.e from t3 where t1.b=t3.e)); Graph of select_lex objects and table objects for this query, before transformation: A - t1 \ B - t2 \ C - t3 For shorthand, we denote the IN predicate the "outer subquery predicate" and the quantified comparison predicate the "inner subquery predicate". The outer subquery predicate contains select_lex B and the inner subquery predicate contains select_lex C. Now, semijoin transformation is applied to the outer subquery predicate. Here is the query graph after transformation: A - t1, t2 \ C - t3 After the transformation, used_tables information is wrong for the column t1.b. The problem is fixed by adding fix_after_pullout() functions for the subquery objects (Item_subselect, Item_in_subselect, Item_in_optimizer), that can propagate re-resolution down to subqueries that have expressions that are affected by the semijoin transformation. In addition, fix_after_pullout() is extended with another argument that identifies the select_lex object that is being removed. With the current use of fix_after_pullout(), this information is implicit, but it has to be explicit when also handling fields that are referenced within inner subqueries. The new fix_after_pullout() interface then becomes: fix_after_pullout(st_select_lex *parent_select, st_select_lex *removed_select, Item **ref); fix_after_pullout() applied to an Item_field object only needs to handle cases where the depended_from or context->select_lex is equal to either parent_select or removed_select. If the field belongs to a table that is "inner" or "outer" compared to the select_lex level we are transforming, then no information needs to be updated. Note also that information about correlated outer columns must be recorded directly in a subquery predicate object. Suppose we have two select_lex objects A and B. B is part of a subquery that is evaluated on level A. The subquery has a predicate that contains a reference to a column that is resolved to a table t1 that belongs to select_lex A. In order to properly evaluate the subquery predicate, we need to make sure that a row from t1 is present. Thus, the fix_after_pullout() function for Item_field needs to propagate information about the table it is resolved within to an outer subquery predicate, if that predicate is evaluated on the same level as the table t1 is resolved on. mysql-test/r/optimizer_switch.result Added result for test case for Bug#31480 mysql-test/r/subquery_sj_all.result mysql-test/r/subquery_sj_all_jcl6.result mysql-test/r/subquery_sj_all_jcl7.result mysql-test/r/subquery_sj_dupsweed.result mysql-test/r/subquery_sj_dupsweed_jcl6.result mysql-test/r/subquery_sj_dupsweed_jcl7.result mysql-test/r/subquery_sj_firstmatch.result mysql-test/r/subquery_sj_firstmatch_jcl6.result mysql-test/r/subquery_sj_firstmatch_jcl7.result mysql-test/r/subquery_sj_loosescan.result mysql-test/r/subquery_sj_loosescan_jcl6.result mysql-test/r/subquery_sj_loosescan_jcl7.result mysql-test/r/subquery_sj_mat.result mysql-test/r/subquery_sj_mat_jcl6.result mysql-test/r/subquery_sj_mat_jcl7.result Minor plan change (WHERE clause moved from first to second table). The reason for this is that the IN subquery predicate object is updated with correct used tables information. mysql-test/t/optimizer_switch.test Added test case for Bug#31480 Notice that the test case with an inner grouped subquery is wrong when semijoin optimization is turned on. sql/item.cc Item_field::resolved_used_tables() is implemented. Item_field::fix_after_pullout() is changed so that it behaves properly also for fields that are used in a scope that is inner to the select_lex being removed. Also update used_tables information for subqueries that are outer-correlated with respect to this field. Updated fix_after_pullout() in conformance with new interface. sql/item.h Changed interface of fix_after_pullout(). Added interface for resolved_used_tables(). This function returns used table information for the level on which a field's table is resolved, in contrast to used_tables() which returns OUTER_REF_TABLE_BIT if the field is an outer reference. sql/item_cmpfunc.cc Necessary changes to fix_after_pullout(). Item_in_optimizer::fix_after_pullout() resolves itself by dispatching to the wrapped Item_in_subselect object. sql/item_cmpfunc.h Added fix_after_pullout() to class Item_in_optimizer. sql/item_func.cc Necessary changes to fix_after_pullout(). sql/item_func.h Changed interface of fix_after_pullout(). sql/item_row.cc Necessary changes to fix_after_pullout(). sql/item_row.h Changed interface of fix_after_pullout(). sql/item_subselect.cc Item_subselect::fix_after_pullout() resolves all expressions referred from the inner query specification objects (select_lex objects). Item_in_subselect::fix_after_pullout() dispatches the query expression resolution to Item_subselect::fix_after_pullout(), but needs to resolve its left expression explicitly. sql/item_subselect.h Added fix_after_pullout() to classes Item_subselect and Item_in_subselect. sql/sql_select.cc Interface change to fix_list_after_tbl_changes(). Also make sure that transformed-away select_lex object is removed before calling fix_after_pullout(), so that the function operates on a chain of select_lex objects that is valid after semijoin transformation. modified: mysql-test/r/optimizer_switch.result mysql-test/r/subquery_sj_all.result mysql-test/r/subquery_sj_all_jcl6.result mysql-test/r/subquery_sj_all_jcl7.result mysql-test/r/subquery_sj_dupsweed.result mysql-test/r/subquery_sj_dupsweed_jcl6.result mysql-test/r/subquery_sj_dupsweed_jcl7.result mysql-test/r/subquery_sj_firstmatch.result mysql-test/r/subquery_sj_firstmatch_jcl6.result mysql-test/r/subquery_sj_firstmatch_jcl7.result mysql-test/r/subquery_sj_loosescan.result mysql-test/r/subquery_sj_loosescan_jcl6.result mysql-test/r/subquery_sj_loosescan_jcl7.result mysql-test/r/subquery_sj_mat.result mysql-test/r/subquery_sj_mat_jcl6.result mysql-test/r/subquery_sj_mat_jcl7.result mysql-test/t/optimizer_switch.test sql/item.cc sql/item.h sql/item_cmpfunc.cc sql/item_cmpfunc.h sql/item_func.cc sql/item_func.h sql/item_row.cc sql/item_row.h sql/item_subselect.cc sql/item_subselect.h sql/sql_select.cc === modified file 'mysql-test/r/optimizer_switch.result' --- a/mysql-test/r/optimizer_switch.result 2010-08-19 07:10:58 +0000 +++ b/mysql-test/r/optimizer_switch.result 2010-10-15 10:32:50 +0000 @@ -204,3 +204,345 @@ SET optimizer_switch="default"; call run_n_times(1); DROP PROCEDURE run_n_times; DROP TABLE it, ot; +# +# BUG#31480: Incorrect result for nested subquery when executed via semijoin +# +CREATE TABLE t1 (a INT NOT NULL, b INT NOT NULL); +CREATE TABLE t2 (c INT NOT NULL, d INT NOT NULL); +CREATE TABLE t3 (e INT NOT NULL); +CREATE TABLE t4 (f INT NOT NULL, g INT NOT NULL); +INSERT INTO t1 VALUES (1,10); +INSERT INTO t1 VALUES (2,10); +INSERT INTO t1 VALUES (1,20); +INSERT INTO t1 VALUES (2,20); +INSERT INTO t1 VALUES (3,20); +INSERT INTO t1 VALUES (2,30); +INSERT INTO t1 VALUES (4,40); +INSERT INTO t2 VALUES (2,10); +INSERT INTO t2 VALUES (2,20); +INSERT INTO t2 VALUES (4,10); +INSERT INTO t2 VALUES (5,10); +INSERT INTO t2 VALUES (3,20); +INSERT INTO t2 VALUES (2,40); +INSERT INTO t3 VALUES (10); +INSERT INTO t3 VALUES (30); +INSERT INTO t3 VALUES (10); +INSERT INTO t3 VALUES (20); +INSERT INTO t4 VALUES (2,10); +INSERT INTO t4 VALUES (2,10); +INSERT INTO t4 VALUES (3,10); +INSERT INTO t4 VALUES (4,10); +INSERT INTO t4 VALUES (4,20); +INSERT INTO t4 VALUES (4,20); +# Reference to the parent query block (used tables was wrong) +set @@optimizer_switch='materialization=off,semijoin=off'; +EXPLAIN SELECT * FROM t1 AS ta +WHERE ta.a IN (SELECT c FROM t2 AS tb +WHERE tb.d >= SOME(SELECT e FROM t3 as tc +WHERE ta.b=tc.e)); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY ta ALL NULL NULL NULL NULL 7 Using where +2 DEPENDENT SUBQUERY tb ALL NULL NULL NULL NULL 6 Using where +3 DEPENDENT SUBQUERY tc ALL NULL NULL NULL NULL 4 Using where +SELECT * FROM t1 AS ta +WHERE ta.a IN (SELECT c FROM t2 AS tb +WHERE tb.d >= SOME(SELECT e FROM t3 as tc +WHERE ta.b=tc.e)); +a b +2 10 +2 20 +3 20 +2 30 +set @@optimizer_switch='materialization=off,semijoin=on'; +EXPLAIN SELECT * FROM t1 AS ta +WHERE ta.a IN (SELECT c FROM t2 AS tb +WHERE tb.d >= SOME(SELECT e FROM t3 as tc +WHERE ta.b=tc.e)); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY tb ALL NULL NULL NULL NULL 6 Start temporary +1 PRIMARY ta ALL NULL NULL NULL NULL 7 Using where; End temporary; Using join buffer (BNL, regular buffers) +3 DEPENDENT SUBQUERY tc ALL NULL NULL NULL NULL 4 Using where +SELECT * FROM t1 AS ta +WHERE ta.a IN (SELECT c FROM t2 AS tb +WHERE tb.d >= SOME(SELECT e FROM t3 as tc +WHERE ta.b=tc.e)); +a b +2 10 +2 20 +3 20 +2 30 +# Subquery with GROUP BY and HAVING +set @@optimizer_switch='materialization=off,semijoin=off'; +EXPLAIN SELECT * FROM t1 AS ta +WHERE ta.a IN (SELECT c FROM t2 AS tb +WHERE tb.d >= SOME(SELECT SUM(g) FROM t4 as tc +GROUP BY f +HAVING ta.a=tc.f)); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY ta ALL NULL NULL NULL NULL 7 Using where +2 DEPENDENT SUBQUERY tb ALL NULL NULL NULL NULL 6 Using where +3 DEPENDENT SUBQUERY tc ALL NULL NULL NULL NULL 6 Using temporary; Using filesort +SELECT * FROM t1 AS ta +WHERE ta.a IN (SELECT c FROM t2 AS tb +WHERE tb.d >= SOME(SELECT SUM(g) FROM t4 as tc +GROUP BY f +HAVING ta.a=tc.f)); +a b +2 10 +2 20 +3 20 +2 30 +set @@optimizer_switch='materialization=off,semijoin=on'; +# The query result with semijoin is WRONG +EXPLAIN SELECT * FROM t1 AS ta +WHERE ta.a IN (SELECT c FROM t2 AS tb +WHERE tb.d >= SOME(SELECT SUM(g) FROM t4 as tc +GROUP BY f +HAVING ta.a=tc.f)); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY tb ALL NULL NULL NULL NULL 6 Using where; Start temporary +1 PRIMARY ta ALL NULL NULL NULL NULL 7 Using where; End temporary; Using join buffer (BNL, regular buffers) +3 DEPENDENT SUBQUERY tc ALL NULL NULL NULL NULL 6 Using temporary; Using filesort +SELECT * FROM t1 AS ta +WHERE ta.a IN (SELECT c FROM t2 AS tb +WHERE tb.d >= SOME(SELECT SUM(g) FROM t4 as tc +GROUP BY f +HAVING ta.a=tc.f)); +a b +# Subquery with ORDER BY and LIMIT +set @@optimizer_switch='materialization=off,semijoin=off'; +# NOTE: The ordered subquery should have a LIMIT clause to make sense +EXPLAIN SELECT * FROM t1 AS ta +WHERE ta.a IN (SELECT c FROM t2 AS tb +WHERE tb.d IN (SELECT g FROM t4 as tc +WHERE ta.a=tc.f +ORDER BY tc.f)); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY ta ALL NULL NULL NULL NULL 7 Using where +2 DEPENDENT SUBQUERY tb ALL NULL NULL NULL NULL 6 Using where +3 DEPENDENT SUBQUERY tc ALL NULL NULL NULL NULL 6 Using where +SELECT * FROM t1 AS ta +WHERE ta.a IN (SELECT c FROM t2 AS tb +WHERE tb.d IN (SELECT g FROM t4 as tc +WHERE ta.a=tc.f +ORDER BY tc.f)); +a b +2 10 +2 20 +2 30 +4 40 +set @@optimizer_switch='materialization=off,semijoin=on'; +# The query result with semijoin is WRONG +EXPLAIN SELECT * FROM t1 AS ta +WHERE ta.a IN (SELECT c FROM t2 AS tb +WHERE tb.d IN (SELECT g FROM t4 as tc +WHERE ta.a=tc.f +ORDER BY tc.f)); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY tb ALL NULL NULL NULL NULL 6 Start temporary +1 PRIMARY ta ALL NULL NULL NULL NULL 7 Using where; End temporary; Using join buffer (BNL, regular buffers) +3 DEPENDENT SUBQUERY tc ALL NULL NULL NULL NULL 6 Using where +SELECT * FROM t1 AS ta +WHERE ta.a IN (SELECT c FROM t2 AS tb +WHERE tb.d IN (SELECT g FROM t4 as tc +WHERE ta.a=tc.f +ORDER BY tc.f)); +a b +2 10 +2 20 +2 30 +4 40 +# Reference to the transformed-away query block (dependency was wrong) +set @@optimizer_switch='materialization=off,semijoin=off'; +EXPLAIN SELECT * FROM t1 AS ta +WHERE ta.a IN (SELECT c FROM t2 AS tb +WHERE tb.d >= SOME(SELECT e FROM t3 as tc +WHERE tb.d=tc.e)); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY ta ALL NULL NULL NULL NULL 7 Using where +2 DEPENDENT SUBQUERY tb ALL NULL NULL NULL NULL 6 Using where +3 DEPENDENT SUBQUERY tc ALL NULL NULL NULL NULL 4 Using where +SELECT * FROM t1 AS ta +WHERE ta.a IN (SELECT c FROM t2 AS tb +WHERE tb.d >= SOME(SELECT e FROM t3 as tc +WHERE tb.d=tc.e)); +a b +2 10 +2 20 +3 20 +2 30 +4 40 +set @@optimizer_switch='materialization=off,semijoin=on'; +EXPLAIN SELECT * FROM t1 AS ta +WHERE ta.a IN (SELECT c FROM t2 AS tb +WHERE tb.d >= SOME(SELECT e FROM t3 as tc +WHERE tb.d=tc.e)); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY tb ALL NULL NULL NULL NULL 6 Using where; Start temporary +1 PRIMARY ta ALL NULL NULL NULL NULL 7 Using where; End temporary; Using join buffer (BNL, regular buffers) +3 DEPENDENT SUBQUERY tc ALL NULL NULL NULL NULL 4 Using where +SELECT * FROM t1 AS ta +WHERE ta.a IN (SELECT c FROM t2 AS tb +WHERE tb.d >= SOME(SELECT e FROM t3 as tc +WHERE tb.d=tc.e)); +a b +2 10 +2 20 +3 20 +2 30 +4 40 +# Reference above the parent query block (should not be affected) +set @@optimizer_switch='materialization=off,semijoin=off'; +EXPLAIN SELECT * FROM t1 AS t +WHERE t.a NOT IN (SELECT a FROM t1 AS ta +WHERE ta.a IN (SELECT c FROM t2 AS tb +WHERE tb.d >= SOME(SELECT e FROM t3 as tc +WHERE t.b=tc.e))); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t ALL NULL NULL NULL NULL 7 Using where +2 DEPENDENT SUBQUERY ta ALL NULL NULL NULL NULL 7 Using where +3 DEPENDENT SUBQUERY tb ALL NULL NULL NULL NULL 6 Using where +4 DEPENDENT SUBQUERY tc ALL NULL NULL NULL NULL 4 Using where +SELECT * FROM t1 AS t +WHERE t.a NOT IN (SELECT a FROM t1 AS ta +WHERE ta.a IN (SELECT c FROM t2 AS tb +WHERE tb.d >= SOME(SELECT e FROM t3 as tc +WHERE t.b=tc.e))); +a b +1 10 +1 20 +4 40 +set @@optimizer_switch='materialization=off,semijoin=on'; +EXPLAIN SELECT * FROM t1 AS t +WHERE t.a NOT IN (SELECT a FROM t1 AS ta +WHERE ta.a IN (SELECT c FROM t2 AS tb +WHERE tb.d >= SOME(SELECT e FROM t3 as tc +WHERE t.b=tc.e))); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t ALL NULL NULL NULL NULL 7 Using where +2 DEPENDENT SUBQUERY tb ALL NULL NULL NULL NULL 6 Using where; Start temporary +2 DEPENDENT SUBQUERY ta ALL NULL NULL NULL NULL 7 Using where; End temporary; Using join buffer (BNL, regular buffers) +4 DEPENDENT SUBQUERY tc ALL NULL NULL NULL NULL 4 Using where +SELECT * FROM t1 AS t +WHERE t.a NOT IN (SELECT a FROM t1 AS ta +WHERE ta.a IN (SELECT c FROM t2 AS tb +WHERE tb.d >= SOME(SELECT e FROM t3 as tc +WHERE t.b=tc.e))); +a b +1 10 +1 20 +4 40 +# EXISTS with reference to the parent query block +set @@optimizer_switch='materialization=off,semijoin=off'; +EXPLAIN SELECT * FROM t1 AS ta +WHERE ta.a IN (SELECT c FROM t2 AS tb +WHERE EXISTS (SELECT * FROM t3 as tc +WHERE ta.b=tc.e)); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY ta ALL NULL NULL NULL NULL 7 Using where +2 DEPENDENT SUBQUERY tb ALL NULL NULL NULL NULL 6 Using where +3 DEPENDENT SUBQUERY tc ALL NULL NULL NULL NULL 4 Using where +SELECT * FROM t1 AS ta +WHERE ta.a IN (SELECT c FROM t2 AS tb +WHERE EXISTS (SELECT * FROM t3 as tc +WHERE ta.b=tc.e)); +a b +2 10 +2 20 +3 20 +2 30 +set @@optimizer_switch='materialization=off,semijoin=on'; +EXPLAIN SELECT * FROM t1 AS ta +WHERE ta.a IN (SELECT c FROM t2 AS tb +WHERE EXISTS (SELECT * FROM t3 as tc +WHERE ta.b=tc.e)); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY tb ALL NULL NULL NULL NULL 6 Start temporary +1 PRIMARY ta ALL NULL NULL NULL NULL 7 Using where; End temporary; Using join buffer (BNL, regular buffers) +3 DEPENDENT SUBQUERY tc ALL NULL NULL NULL NULL 4 Using where +SELECT * FROM t1 AS ta +WHERE ta.a IN (SELECT c FROM t2 AS tb +WHERE EXISTS (SELECT * FROM t3 as tc +WHERE ta.b=tc.e)); +a b +2 10 +2 20 +3 20 +2 30 +# Scalar subquery with reference to the parent query block +set @@optimizer_switch='materialization=off,semijoin=off'; +EXPLAIN SELECT * FROM t1 AS ta +WHERE ta.a IN (SELECT c FROM t2 AS tb +WHERE tb.d = (SELECT MIN(e) FROM t3 as tc +WHERE ta.b=tc.e)); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY ta ALL NULL NULL NULL NULL 7 Using where +2 DEPENDENT SUBQUERY tb ALL NULL NULL NULL NULL 6 Using where +3 DEPENDENT SUBQUERY tc ALL NULL NULL NULL NULL 4 Using where +SELECT * FROM t1 AS ta +WHERE ta.a IN (SELECT c FROM t2 AS tb +WHERE tb.d = (SELECT MIN(e) FROM t3 as tc +WHERE ta.b=tc.e)); +a b +2 10 +2 20 +3 20 +set @@optimizer_switch='materialization=off,semijoin=on'; +EXPLAIN SELECT * FROM t1 AS ta +WHERE ta.a IN (SELECT c FROM t2 AS tb +WHERE tb.d = (SELECT MIN(e) FROM t3 as tc +WHERE ta.b=tc.e)); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY tb ALL NULL NULL NULL NULL 6 Start temporary +1 PRIMARY ta ALL NULL NULL NULL NULL 7 Using where; End temporary; Using join buffer (BNL, regular buffers) +3 DEPENDENT SUBQUERY tc ALL NULL NULL NULL NULL 4 Using where +SELECT * FROM t1 AS ta +WHERE ta.a IN (SELECT c FROM t2 AS tb +WHERE tb.d = (SELECT MIN(e) FROM t3 as tc +WHERE ta.b=tc.e)); +a b +2 10 +2 20 +3 20 +# Combine scalar subquery with quantified comparison subquery +set @@optimizer_switch='materialization=off,semijoin=off'; +EXPLAIN SELECT * FROM t1 AS ta +WHERE ta.a IN (SELECT c FROM t2 AS tb +WHERE (SELECT MIN(e) FROM t3 as tc +WHERE tb.d=tc.e) < SOME(SELECT e FROM t3 as tc +WHERE ta.b=tc.e)); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY ta ALL NULL NULL NULL NULL 7 Using where +2 DEPENDENT SUBQUERY tb ALL NULL NULL NULL NULL 6 Using where +4 DEPENDENT SUBQUERY tc ALL NULL NULL NULL NULL 4 Using where +3 DEPENDENT SUBQUERY tc ALL NULL NULL NULL NULL 4 Using where +SELECT * FROM t1 AS ta +WHERE ta.a IN (SELECT c FROM t2 AS tb +WHERE (SELECT MIN(e) FROM t3 as tc +WHERE tb.d=tc.e) < SOME(SELECT e FROM t3 as tc +WHERE ta.b=tc.e)); +a b +2 20 +2 30 +set @@optimizer_switch='materialization=off,semijoin=on'; +EXPLAIN SELECT * FROM t1 AS ta +WHERE ta.a IN (SELECT c FROM t2 AS tb +WHERE (SELECT MIN(e) FROM t3 as tc +WHERE tb.d=tc.e) < SOME(SELECT e FROM t3 as tc +WHERE ta.b=tc.e)); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY tb ALL NULL NULL NULL NULL 6 Start temporary +1 PRIMARY ta ALL NULL NULL NULL NULL 7 Using where; End temporary; Using join buffer (BNL, regular buffers) +4 DEPENDENT SUBQUERY tc ALL NULL NULL NULL NULL 4 Using where +3 DEPENDENT SUBQUERY tc ALL NULL NULL NULL NULL 4 Using where +SELECT * FROM t1 AS ta +WHERE ta.a IN (SELECT c FROM t2 AS tb +WHERE (SELECT MIN(e) FROM t3 as tc +WHERE tb.d=tc.e) < SOME(SELECT e FROM t3 as tc +WHERE ta.b=tc.e)); +a b +2 20 +2 30 +DROP TABLE t1, t2, t3, t4; +set @@optimizer_switch='default'; +# End of BUG#31480 === modified file 'mysql-test/r/subquery_sj_all.result' --- a/mysql-test/r/subquery_sj_all.result 2010-10-13 13:27:36 +0000 +++ b/mysql-test/r/subquery_sj_all.result 2010-10-15 10:32:50 +0000 @@ -3784,8 +3784,8 @@ SELECT t1field FROM t1 WHERE t1field IN (SELECT v1field FROM v1); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 index PRIMARY PRIMARY 4 NULL 2 Using where; Using index -1 PRIMARY a eq_ref PRIMARY PRIMARY 4 test.t1.t1field 1 Using index +1 PRIMARY t1 index PRIMARY PRIMARY 4 NULL 2 Using index +1 PRIMARY a eq_ref PRIMARY PRIMARY 4 test.t1.t1field 1 Using where; Using index 4 DEPENDENT SUBQUERY t1 unique_subquery PRIMARY PRIMARY 4 func 1 Using index CREATE PROCEDURE p1() BEGIN === modified file 'mysql-test/r/subquery_sj_all_jcl6.result' --- a/mysql-test/r/subquery_sj_all_jcl6.result 2010-10-13 13:27:36 +0000 +++ b/mysql-test/r/subquery_sj_all_jcl6.result 2010-10-15 10:32:50 +0000 @@ -3788,8 +3788,8 @@ SELECT t1field FROM t1 WHERE t1field IN (SELECT v1field FROM v1); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 index PRIMARY PRIMARY 4 NULL 2 Using where; Using index -1 PRIMARY a eq_ref PRIMARY PRIMARY 4 test.t1.t1field 1 Using index +1 PRIMARY t1 index PRIMARY PRIMARY 4 NULL 2 Using index +1 PRIMARY a eq_ref PRIMARY PRIMARY 4 test.t1.t1field 1 Using where; Using index 4 DEPENDENT SUBQUERY t1 unique_subquery PRIMARY PRIMARY 4 func 1 Using index CREATE PROCEDURE p1() BEGIN === modified file 'mysql-test/r/subquery_sj_all_jcl7.result' --- a/mysql-test/r/subquery_sj_all_jcl7.result 2010-10-13 13:27:36 +0000 +++ b/mysql-test/r/subquery_sj_all_jcl7.result 2010-10-15 10:32:50 +0000 @@ -3788,8 +3788,8 @@ SELECT t1field FROM t1 WHERE t1field IN (SELECT v1field FROM v1); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 index PRIMARY PRIMARY 4 NULL 2 Using where; Using index -1 PRIMARY a eq_ref PRIMARY PRIMARY 4 test.t1.t1field 1 Using index +1 PRIMARY t1 index PRIMARY PRIMARY 4 NULL 2 Using index +1 PRIMARY a eq_ref PRIMARY PRIMARY 4 test.t1.t1field 1 Using where; Using index 4 DEPENDENT SUBQUERY t1 unique_subquery PRIMARY PRIMARY 4 func 1 Using index CREATE PROCEDURE p1() BEGIN === modified file 'mysql-test/r/subquery_sj_dupsweed.result' --- a/mysql-test/r/subquery_sj_dupsweed.result 2010-10-13 13:27:36 +0000 +++ b/mysql-test/r/subquery_sj_dupsweed.result 2010-10-15 10:32:50 +0000 @@ -3783,8 +3783,8 @@ SELECT t1field FROM t1 WHERE t1field IN (SELECT v1field FROM v1); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 index PRIMARY PRIMARY 4 NULL 2 Using where; Using index -1 PRIMARY a eq_ref PRIMARY PRIMARY 4 test.t1.t1field 1 Using index +1 PRIMARY t1 index PRIMARY PRIMARY 4 NULL 2 Using index +1 PRIMARY a eq_ref PRIMARY PRIMARY 4 test.t1.t1field 1 Using where; Using index 4 DEPENDENT SUBQUERY t1 unique_subquery PRIMARY PRIMARY 4 func 1 Using index CREATE PROCEDURE p1() BEGIN === modified file 'mysql-test/r/subquery_sj_dupsweed_jcl6.result' --- a/mysql-test/r/subquery_sj_dupsweed_jcl6.result 2010-10-13 13:27:36 +0000 +++ b/mysql-test/r/subquery_sj_dupsweed_jcl6.result 2010-10-15 10:32:50 +0000 @@ -3787,8 +3787,8 @@ SELECT t1field FROM t1 WHERE t1field IN (SELECT v1field FROM v1); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 index PRIMARY PRIMARY 4 NULL 2 Using where; Using index -1 PRIMARY a eq_ref PRIMARY PRIMARY 4 test.t1.t1field 1 Using index +1 PRIMARY t1 index PRIMARY PRIMARY 4 NULL 2 Using index +1 PRIMARY a eq_ref PRIMARY PRIMARY 4 test.t1.t1field 1 Using where; Using index 4 DEPENDENT SUBQUERY t1 unique_subquery PRIMARY PRIMARY 4 func 1 Using index CREATE PROCEDURE p1() BEGIN === modified file 'mysql-test/r/subquery_sj_dupsweed_jcl7.result' --- a/mysql-test/r/subquery_sj_dupsweed_jcl7.result 2010-10-13 13:27:36 +0000 +++ b/mysql-test/r/subquery_sj_dupsweed_jcl7.result 2010-10-15 10:32:50 +0000 @@ -3787,8 +3787,8 @@ SELECT t1field FROM t1 WHERE t1field IN (SELECT v1field FROM v1); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 index PRIMARY PRIMARY 4 NULL 2 Using where; Using index -1 PRIMARY a eq_ref PRIMARY PRIMARY 4 test.t1.t1field 1 Using index +1 PRIMARY t1 index PRIMARY PRIMARY 4 NULL 2 Using index +1 PRIMARY a eq_ref PRIMARY PRIMARY 4 test.t1.t1field 1 Using where; Using index 4 DEPENDENT SUBQUERY t1 unique_subquery PRIMARY PRIMARY 4 func 1 Using index CREATE PROCEDURE p1() BEGIN === modified file 'mysql-test/r/subquery_sj_firstmatch.result' --- a/mysql-test/r/subquery_sj_firstmatch.result 2010-10-13 13:27:36 +0000 +++ b/mysql-test/r/subquery_sj_firstmatch.result 2010-10-15 10:32:50 +0000 @@ -3784,8 +3784,8 @@ SELECT t1field FROM t1 WHERE t1field IN (SELECT v1field FROM v1); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 index PRIMARY PRIMARY 4 NULL 2 Using where; Using index -1 PRIMARY a eq_ref PRIMARY PRIMARY 4 test.t1.t1field 1 Using index +1 PRIMARY t1 index PRIMARY PRIMARY 4 NULL 2 Using index +1 PRIMARY a eq_ref PRIMARY PRIMARY 4 test.t1.t1field 1 Using where; Using index 4 DEPENDENT SUBQUERY t1 unique_subquery PRIMARY PRIMARY 4 func 1 Using index CREATE PROCEDURE p1() BEGIN === modified file 'mysql-test/r/subquery_sj_firstmatch_jcl6.result' --- a/mysql-test/r/subquery_sj_firstmatch_jcl6.result 2010-10-13 13:27:36 +0000 +++ b/mysql-test/r/subquery_sj_firstmatch_jcl6.result 2010-10-15 10:32:50 +0000 @@ -3788,8 +3788,8 @@ SELECT t1field FROM t1 WHERE t1field IN (SELECT v1field FROM v1); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 index PRIMARY PRIMARY 4 NULL 2 Using where; Using index -1 PRIMARY a eq_ref PRIMARY PRIMARY 4 test.t1.t1field 1 Using index +1 PRIMARY t1 index PRIMARY PRIMARY 4 NULL 2 Using index +1 PRIMARY a eq_ref PRIMARY PRIMARY 4 test.t1.t1field 1 Using where; Using index 4 DEPENDENT SUBQUERY t1 unique_subquery PRIMARY PRIMARY 4 func 1 Using index CREATE PROCEDURE p1() BEGIN === modified file 'mysql-test/r/subquery_sj_firstmatch_jcl7.result' --- a/mysql-test/r/subquery_sj_firstmatch_jcl7.result 2010-10-13 13:27:36 +0000 +++ b/mysql-test/r/subquery_sj_firstmatch_jcl7.result 2010-10-15 10:32:50 +0000 @@ -3788,8 +3788,8 @@ SELECT t1field FROM t1 WHERE t1field IN (SELECT v1field FROM v1); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 index PRIMARY PRIMARY 4 NULL 2 Using where; Using index -1 PRIMARY a eq_ref PRIMARY PRIMARY 4 test.t1.t1field 1 Using index +1 PRIMARY t1 index PRIMARY PRIMARY 4 NULL 2 Using index +1 PRIMARY a eq_ref PRIMARY PRIMARY 4 test.t1.t1field 1 Using where; Using index 4 DEPENDENT SUBQUERY t1 unique_subquery PRIMARY PRIMARY 4 func 1 Using index CREATE PROCEDURE p1() BEGIN === modified file 'mysql-test/r/subquery_sj_loosescan.result' --- a/mysql-test/r/subquery_sj_loosescan.result 2010-10-13 13:27:36 +0000 +++ b/mysql-test/r/subquery_sj_loosescan.result 2010-10-15 10:32:50 +0000 @@ -3788,8 +3788,8 @@ SELECT t1field FROM t1 WHERE t1field IN (SELECT v1field FROM v1); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 index PRIMARY PRIMARY 4 NULL 2 Using where; Using index -1 PRIMARY a eq_ref PRIMARY PRIMARY 4 test.t1.t1field 1 Using index +1 PRIMARY t1 index PRIMARY PRIMARY 4 NULL 2 Using index +1 PRIMARY a eq_ref PRIMARY PRIMARY 4 test.t1.t1field 1 Using where; Using index 4 DEPENDENT SUBQUERY t1 unique_subquery PRIMARY PRIMARY 4 func 1 Using index CREATE PROCEDURE p1() BEGIN === modified file 'mysql-test/r/subquery_sj_loosescan_jcl6.result' --- a/mysql-test/r/subquery_sj_loosescan_jcl6.result 2010-10-13 13:27:36 +0000 +++ b/mysql-test/r/subquery_sj_loosescan_jcl6.result 2010-10-15 10:32:50 +0000 @@ -3792,8 +3792,8 @@ SELECT t1field FROM t1 WHERE t1field IN (SELECT v1field FROM v1); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 index PRIMARY PRIMARY 4 NULL 2 Using where; Using index -1 PRIMARY a eq_ref PRIMARY PRIMARY 4 test.t1.t1field 1 Using index +1 PRIMARY t1 index PRIMARY PRIMARY 4 NULL 2 Using index +1 PRIMARY a eq_ref PRIMARY PRIMARY 4 test.t1.t1field 1 Using where; Using index 4 DEPENDENT SUBQUERY t1 unique_subquery PRIMARY PRIMARY 4 func 1 Using index CREATE PROCEDURE p1() BEGIN === modified file 'mysql-test/r/subquery_sj_loosescan_jcl7.result' --- a/mysql-test/r/subquery_sj_loosescan_jcl7.result 2010-10-13 13:27:36 +0000 +++ b/mysql-test/r/subquery_sj_loosescan_jcl7.result 2010-10-15 10:32:50 +0000 @@ -3792,8 +3792,8 @@ SELECT t1field FROM t1 WHERE t1field IN (SELECT v1field FROM v1); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 index PRIMARY PRIMARY 4 NULL 2 Using where; Using index -1 PRIMARY a eq_ref PRIMARY PRIMARY 4 test.t1.t1field 1 Using index +1 PRIMARY t1 index PRIMARY PRIMARY 4 NULL 2 Using index +1 PRIMARY a eq_ref PRIMARY PRIMARY 4 test.t1.t1field 1 Using where; Using index 4 DEPENDENT SUBQUERY t1 unique_subquery PRIMARY PRIMARY 4 func 1 Using index CREATE PROCEDURE p1() BEGIN === modified file 'mysql-test/r/subquery_sj_mat.result' --- a/mysql-test/r/subquery_sj_mat.result 2010-10-13 13:27:36 +0000 +++ b/mysql-test/r/subquery_sj_mat.result 2010-10-15 10:32:50 +0000 @@ -3794,8 +3794,8 @@ SELECT t1field FROM t1 WHERE t1field IN (SELECT v1field FROM v1); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 index PRIMARY PRIMARY 4 NULL 2 Using where; Using index -1 PRIMARY a eq_ref PRIMARY PRIMARY 4 test.t1.t1field 1 Using index +1 PRIMARY t1 index PRIMARY PRIMARY 4 NULL 2 Using index +1 PRIMARY a eq_ref PRIMARY PRIMARY 4 test.t1.t1field 1 Using where; Using index 4 DEPENDENT SUBQUERY t1 unique_subquery PRIMARY PRIMARY 4 func 1 Using index CREATE PROCEDURE p1() BEGIN === modified file 'mysql-test/r/subquery_sj_mat_jcl6.result' --- a/mysql-test/r/subquery_sj_mat_jcl6.result 2010-10-13 13:27:36 +0000 +++ b/mysql-test/r/subquery_sj_mat_jcl6.result 2010-10-15 10:32:50 +0000 @@ -3798,8 +3798,8 @@ SELECT t1field FROM t1 WHERE t1field IN (SELECT v1field FROM v1); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 index PRIMARY PRIMARY 4 NULL 2 Using where; Using index -1 PRIMARY a eq_ref PRIMARY PRIMARY 4 test.t1.t1field 1 Using index +1 PRIMARY t1 index PRIMARY PRIMARY 4 NULL 2 Using index +1 PRIMARY a eq_ref PRIMARY PRIMARY 4 test.t1.t1field 1 Using where; Using index 4 DEPENDENT SUBQUERY t1 unique_subquery PRIMARY PRIMARY 4 func 1 Using index CREATE PROCEDURE p1() BEGIN === modified file 'mysql-test/r/subquery_sj_mat_jcl7.result' --- a/mysql-test/r/subquery_sj_mat_jcl7.result 2010-10-13 13:27:36 +0000 +++ b/mysql-test/r/subquery_sj_mat_jcl7.result 2010-10-15 10:32:50 +0000 @@ -3798,8 +3798,8 @@ SELECT t1field FROM t1 WHERE t1field IN (SELECT v1field FROM v1); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 index PRIMARY PRIMARY 4 NULL 2 Using where; Using index -1 PRIMARY a eq_ref PRIMARY PRIMARY 4 test.t1.t1field 1 Using index +1 PRIMARY t1 index PRIMARY PRIMARY 4 NULL 2 Using index +1 PRIMARY a eq_ref PRIMARY PRIMARY 4 test.t1.t1field 1 Using where; Using index 4 DEPENDENT SUBQUERY t1 unique_subquery PRIMARY PRIMARY 4 func 1 Using index CREATE PROCEDURE p1() BEGIN === modified file 'mysql-test/t/optimizer_switch.test' --- a/mysql-test/t/optimizer_switch.test 2010-08-19 07:10:58 +0000 +++ b/mysql-test/t/optimizer_switch.test 2010-10-15 10:32:50 +0000 @@ -209,3 +209,199 @@ DROP PROCEDURE run_n_times; DROP TABLE it, ot; # End of Bug#50489 + +--echo # +--echo # BUG#31480: Incorrect result for nested subquery when executed via semijoin +--echo # + +CREATE TABLE t1 (a INT NOT NULL, b INT NOT NULL); +CREATE TABLE t2 (c INT NOT NULL, d INT NOT NULL); +CREATE TABLE t3 (e INT NOT NULL); +CREATE TABLE t4 (f INT NOT NULL, g INT NOT NULL); + +INSERT INTO t1 VALUES (1,10); +INSERT INTO t1 VALUES (2,10); +INSERT INTO t1 VALUES (1,20); +INSERT INTO t1 VALUES (2,20); +INSERT INTO t1 VALUES (3,20); +INSERT INTO t1 VALUES (2,30); +INSERT INTO t1 VALUES (4,40); + +INSERT INTO t2 VALUES (2,10); +INSERT INTO t2 VALUES (2,20); +INSERT INTO t2 VALUES (4,10); +INSERT INTO t2 VALUES (5,10); +INSERT INTO t2 VALUES (3,20); +INSERT INTO t2 VALUES (2,40); + +INSERT INTO t3 VALUES (10); +INSERT INTO t3 VALUES (30); +INSERT INTO t3 VALUES (10); +INSERT INTO t3 VALUES (20); + +INSERT INTO t4 VALUES (2,10); +INSERT INTO t4 VALUES (2,10); +INSERT INTO t4 VALUES (3,10); +INSERT INTO t4 VALUES (4,10); +INSERT INTO t4 VALUES (4,20); +INSERT INTO t4 VALUES (4,20); + +--echo # Reference to the parent query block (used tables was wrong) + +set @@optimizer_switch='materialization=off,semijoin=off'; + +let query= +SELECT * FROM t1 AS ta +WHERE ta.a IN (SELECT c FROM t2 AS tb + WHERE tb.d >= SOME(SELECT e FROM t3 as tc + WHERE ta.b=tc.e)); + +eval EXPLAIN $query; +eval $query; + +set @@optimizer_switch='materialization=off,semijoin=on'; + +eval EXPLAIN $query; +eval $query; + +--echo # Subquery with GROUP BY and HAVING + +set @@optimizer_switch='materialization=off,semijoin=off'; + +let query= +SELECT * FROM t1 AS ta +WHERE ta.a IN (SELECT c FROM t2 AS tb + WHERE tb.d >= SOME(SELECT SUM(g) FROM t4 as tc + GROUP BY f + HAVING ta.a=tc.f)); + +eval EXPLAIN $query; +eval $query; + +set @@optimizer_switch='materialization=off,semijoin=on'; + +--echo # The query result with semijoin is WRONG + +eval EXPLAIN $query; +eval $query; + +--echo # Subquery with ORDER BY and LIMIT + +set @@optimizer_switch='materialization=off,semijoin=off'; + +--echo # NOTE: The ordered subquery should have a LIMIT clause to make sense + +let query= +SELECT * FROM t1 AS ta +WHERE ta.a IN (SELECT c FROM t2 AS tb + WHERE tb.d IN (SELECT g FROM t4 as tc + WHERE ta.a=tc.f + ORDER BY tc.f)); + +eval EXPLAIN $query; +eval $query; + +set @@optimizer_switch='materialization=off,semijoin=on'; + +--echo # The query result with semijoin is WRONG + +eval EXPLAIN $query; +eval $query; + +--echo # Reference to the transformed-away query block (dependency was wrong) + +set @@optimizer_switch='materialization=off,semijoin=off'; + +let query= +SELECT * FROM t1 AS ta +WHERE ta.a IN (SELECT c FROM t2 AS tb + WHERE tb.d >= SOME(SELECT e FROM t3 as tc + WHERE tb.d=tc.e)); + +eval EXPLAIN $query; +eval $query; + +set @@optimizer_switch='materialization=off,semijoin=on'; + +eval EXPLAIN $query; +eval $query; + +--echo # Reference above the parent query block (should not be affected) + +set @@optimizer_switch='materialization=off,semijoin=off'; + +let query= +SELECT * FROM t1 AS t +WHERE t.a NOT IN (SELECT a FROM t1 AS ta + WHERE ta.a IN (SELECT c FROM t2 AS tb + WHERE tb.d >= SOME(SELECT e FROM t3 as tc + WHERE t.b=tc.e))); + +eval EXPLAIN $query; +eval $query; + +set @@optimizer_switch='materialization=off,semijoin=on'; + +eval EXPLAIN $query; +eval $query; + +--echo # EXISTS with reference to the parent query block + +set @@optimizer_switch='materialization=off,semijoin=off'; + +let query= +SELECT * FROM t1 AS ta +WHERE ta.a IN (SELECT c FROM t2 AS tb + WHERE EXISTS (SELECT * FROM t3 as tc + WHERE ta.b=tc.e)); + +eval EXPLAIN $query; +eval $query; + +set @@optimizer_switch='materialization=off,semijoin=on'; + +eval EXPLAIN $query; +eval $query; + +--echo # Scalar subquery with reference to the parent query block + +set @@optimizer_switch='materialization=off,semijoin=off'; + +let query= +SELECT * FROM t1 AS ta +WHERE ta.a IN (SELECT c FROM t2 AS tb + WHERE tb.d = (SELECT MIN(e) FROM t3 as tc + WHERE ta.b=tc.e)); + +eval EXPLAIN $query; +eval $query; + +set @@optimizer_switch='materialization=off,semijoin=on'; + +eval EXPLAIN $query; +eval $query; + +--echo # Combine scalar subquery with quantified comparison subquery + +set @@optimizer_switch='materialization=off,semijoin=off'; + +let query= +SELECT * FROM t1 AS ta +WHERE ta.a IN (SELECT c FROM t2 AS tb + WHERE (SELECT MIN(e) FROM t3 as tc + WHERE tb.d=tc.e) < SOME(SELECT e FROM t3 as tc + WHERE ta.b=tc.e)); + +eval EXPLAIN $query; +eval $query; + +set @@optimizer_switch='materialization=off,semijoin=on'; + +eval EXPLAIN $query; +eval $query; + +DROP TABLE t1, t2, t3, t4; + +set @@optimizer_switch='default'; + +--echo # End of BUG#31480 === modified file 'sql/item.cc' --- a/sql/item.cc 2010-09-28 15:17:29 +0000 +++ b/sql/item.cc 2010-10-15 10:32:50 +0000 @@ -2314,21 +2314,75 @@ table_map Item_field::used_tables() cons } -void Item_field::fix_after_pullout(st_select_lex *new_parent, Item **ref) +table_map Item_field::resolved_used_tables() const { - if (new_parent == depended_from) - depended_from= NULL; - Name_resolution_context *ctx= new Name_resolution_context(); - ctx->outer_context= NULL; // We don't build a complete name resolver - ctx->table_list= NULL; // We rely on first_name_resolution_table instead - ctx->select_lex= new_parent; - ctx->first_name_resolution_table= context->first_name_resolution_table; - ctx->last_name_resolution_table= context->last_name_resolution_table; - ctx->error_processor= context->error_processor; - ctx->error_processor_data= context->error_processor_data; - ctx->resolve_in_select_list= context->resolve_in_select_list; - ctx->security_ctx= context->security_ctx; - this->context=ctx; + if (field->table->const_table) + return 0; // const item + return field->table->map; +} + +void Item_field::fix_after_pullout(st_select_lex *parent_select, + st_select_lex *removed_select, + Item **ref) +{ + if (context->select_lex == removed_select || + context->select_lex == parent_select) + { + if (parent_select == depended_from) + depended_from= NULL; + Name_resolution_context *ctx= new Name_resolution_context(); + ctx->outer_context= NULL; // We don't build a complete name resolver + ctx->table_list= NULL; // We rely on first_name_resolution_table instead + ctx->select_lex= parent_select; + ctx->first_name_resolution_table= context->first_name_resolution_table; + ctx->last_name_resolution_table= context->last_name_resolution_table; + ctx->error_processor= context->error_processor; + ctx->error_processor_data= context->error_processor_data; + ctx->resolve_in_select_list= context->resolve_in_select_list; + ctx->security_ctx= context->security_ctx; + this->context=ctx; + } + else + { + /* + The definition scope of this field item reference is inner to the removed + select_lex object. + No new resolution is needed, but we may need to update the dependency. + */ + if (removed_select == depended_from) + depended_from= parent_select; + } + + if (depended_from) + { + /* + Refresh used_tables information for subqueries between the definition + scope and resolution scope of the field item reference. + */ + st_select_lex *child_select= context->select_lex; + + if (child_select->outer_select() != depended_from) + { + /* + The subquery on this level is outer-correlated with respect to the field + */ + Item_subselect *subq_predicate= child_select->master_unit()->item; + subq_predicate->used_tables_cache|= OUTER_REF_TABLE_BIT; + } + + while (child_select->outer_select() != depended_from) + child_select= child_select->outer_select(); + + /* + child_select is select_lex immediately inner to the depended_from level. + Now, locate the subquery predicate that contains this select_lex and + update used tables information. + */ + Item_subselect *subq_predicate= child_select->master_unit()->item; + + subq_predicate->used_tables_cache|= this->resolved_used_tables(); + subq_predicate->const_item_cache&= this->const_item(); + } } @@ -6804,26 +6858,31 @@ bool Item_outer_ref::fix_fields(THD *thd return err; } -void Item_outer_ref::fix_after_pullout(st_select_lex *new_parent, Item **ref) +void Item_outer_ref::fix_after_pullout(st_select_lex *parent_select, + st_select_lex *removed_select, + Item **ref_arg) { - if (depended_from == new_parent) + if (depended_from == parent_select) { - *ref= outer_ref; - outer_ref->fix_after_pullout(new_parent, ref); + *ref_arg= outer_ref; + outer_ref->fix_after_pullout(parent_select, removed_select, ref_arg); } // @todo: Find an actual test case for this funcion. DBUG_ASSERT(false); } -void Item_ref::fix_after_pullout(st_select_lex *new_parent, Item **refptr) +void Item_ref::fix_after_pullout(st_select_lex *parent_select, + st_select_lex *removed_select, + Item **ref_arg) { // @todo: Find an actual test case where depended_from == new_parent. - DBUG_ASSERT(depended_from != new_parent); - if (depended_from == new_parent) + DBUG_ASSERT(depended_from != parent_select); + if (depended_from == parent_select) depended_from= NULL; } -void Item_direct_view_ref::fix_after_pullout(st_select_lex *new_parent, +void Item_direct_view_ref::fix_after_pullout(st_select_lex *parent_select, + st_select_lex *removed_select, Item **refptr) { DBUG_EXECUTE("where", @@ -6831,11 +6890,11 @@ void Item_direct_view_ref::fix_after_pul "Item_direct_view_ref::fix_after_pullout", QT_ORDINARY);); - (*ref)->fix_after_pullout(new_parent, ref); + (*ref)->fix_after_pullout(parent_select, removed_select, ref); - // @todo: Find an actual test case where depended_from == new_parent. - DBUG_ASSERT(depended_from != new_parent); - if (depended_from == new_parent) + // @todo: Find an actual test case where depended_from == parent_select. + DBUG_ASSERT(depended_from != parent_select); + if (depended_from == parent_select) depended_from= NULL; } === modified file 'sql/item.h' --- a/sql/item.h 2010-09-07 19:07:18 +0000 +++ b/sql/item.h 2010-10-15 10:32:50 +0000 @@ -592,12 +592,20 @@ public: virtual void make_field(Send_field *field); Field *make_string_field(TABLE *table); virtual bool fix_fields(THD *, Item **); - /* - Fix after some tables has been pulled out. Basically re-calculate all - attributes that are dependent on the tables. - */ - virtual void fix_after_pullout(st_select_lex *new_parent, Item **ref) {}; - + /** + Fix after tables have been moved from one select_lex level to the parent + level, e.g by semijoin conversion. + Basically re-calculate all attributes dependent on the tables. + + @param parent_select select_lex that tables are moved to. + @param removed_select select_lex that tables are moved away from, + child of parent_select. + @param ref updated with new ref whenever the function substitutes + this item with another. + */ + virtual void fix_after_pullout(st_select_lex *parent_select, + st_select_lex *removed_select, + Item **ref) {}; /* should be used in case where we are sure that we do not need complete fix_fields() procedure. @@ -1753,11 +1761,16 @@ public: bool send(Protocol *protocol, String *str_arg); void reset_field(Field *f); bool fix_fields(THD *, Item **); - void fix_after_pullout(st_select_lex *new_parent, Item **ref); + void fix_after_pullout(st_select_lex *parent_select, + st_select_lex *removed_select, Item **ref); void make_field(Send_field *tmp_field); int save_in_field(Field *field,bool no_conversions); void save_org_in_field(Field *field); table_map used_tables() const; + /* + Return used table information for the level on which this table is resolved. + */ + table_map resolved_used_tables() const; enum Item_result result_type () const { return field->result_type(); @@ -2573,7 +2586,8 @@ public: bool send(Protocol *prot, String *tmp); void make_field(Send_field *field); bool fix_fields(THD *, Item **); - void fix_after_pullout(st_select_lex *new_parent, Item **ref); + void fix_after_pullout(st_select_lex *parent_select, + st_select_lex *removed_select, Item **ref); int save_in_field(Field *field, bool no_conversions); void save_org_in_field(Field *field); enum Item_result result_type () const { return (*ref)->result_type(); } @@ -2712,7 +2726,8 @@ public: {} bool fix_fields(THD *, Item **); - void fix_after_pullout(st_select_lex *new_parent, Item **ref); + void fix_after_pullout(st_select_lex *parent_select, + st_select_lex *removed_select, Item **ref); bool eq(const Item *item, bool binary_cmp) const; Item *get_tmp_table_item(THD *thd) { @@ -2769,7 +2784,8 @@ public: outer_ref->save_org_in_field(result_field); } bool fix_fields(THD *, Item **); - void fix_after_pullout(st_select_lex *new_parent, Item **ref); + void fix_after_pullout(st_select_lex *parent_select, + st_select_lex *removed_select, Item **ref); table_map used_tables() const { return (*ref)->const_item() ? 0 : OUTER_REF_TABLE_BIT; === modified file 'sql/item_cmpfunc.cc' --- a/sql/item_cmpfunc.cc 2010-09-28 15:17:29 +0000 +++ b/sql/item_cmpfunc.cc 2010-10-15 10:32:50 +0000 @@ -1770,6 +1770,27 @@ bool Item_in_optimizer::fix_fields(THD * } +void Item_in_optimizer::fix_after_pullout(st_select_lex *parent_select, + st_select_lex *removed_select, + Item **ref) +{ + used_tables_cache=0; + not_null_tables_cache= 0; + const_item_cache= 1; + + /* + No need to call fix_after_pullout() on args[0] (ie left expression), + as Item_in_subselect::fix_after_pullout() will do this. + So, just forward the call to the Item_in_subselect object. + */ + + args[1]->fix_after_pullout(parent_select, removed_select, &args[1]); + + used_tables_cache|= args[1]->used_tables(); + not_null_tables_cache|= args[1]->not_null_tables(); + const_item_cache&= args[1]->const_item(); +} + /** The implementation of optimized \ [NOT] IN \ predicates. The implementation works as follows. @@ -1840,6 +1861,7 @@ bool Item_in_optimizer::fix_fields(THD * @see Item_in_subselect::val_bool() @see Item_is_not_null_test::val_int() */ + longlong Item_in_optimizer::val_int() { bool tmp; @@ -4337,7 +4359,9 @@ Item_cond::fix_fields(THD *thd, Item **r } -void Item_cond::fix_after_pullout(st_select_lex *new_parent, Item **ref) +void Item_cond::fix_after_pullout(st_select_lex *parent_select, + st_select_lex *removed_select, + Item **ref) { List_iterator li(list); Item *item; @@ -4351,7 +4375,7 @@ void Item_cond::fix_after_pullout(st_sel while ((item=li++)) { table_map tmp_table_map; - item->fix_after_pullout(new_parent, li.ref()); + item->fix_after_pullout(parent_select, removed_select, li.ref()); item= *li.ref(); used_tables_cache|= item->used_tables(); const_item_cache&= item->const_item(); === modified file 'sql/item_cmpfunc.h' --- a/sql/item_cmpfunc.h 2010-08-12 00:26:10 +0000 +++ b/sql/item_cmpfunc.h 2010-10-15 10:32:50 +0000 @@ -274,6 +274,8 @@ public: { with_subselect= TRUE; } bool fix_fields(THD *, Item **); bool fix_left(THD *thd, Item **ref); + void fix_after_pullout(st_select_lex *parent_select, + st_select_lex *removed_select, Item **ref); bool is_null(); longlong val_int(); void cleanup(); @@ -1513,7 +1515,8 @@ public: list.prepand(nlist); } bool fix_fields(THD *, Item **ref); - void fix_after_pullout(st_select_lex *new_parent, Item **ref); + void fix_after_pullout(st_select_lex *parent_select, + st_select_lex *removed_select, Item **ref); enum Type type() const { return COND_ITEM; } List* argument_list() { return &list; } === modified file 'sql/item_func.cc' --- a/sql/item_func.cc 2010-08-30 08:40:42 +0000 +++ b/sql/item_func.cc 2010-10-15 10:32:50 +0000 @@ -226,7 +226,9 @@ Item_func::fix_fields(THD *thd, Item **r } -void Item_func::fix_after_pullout(st_select_lex *new_parent, Item **ref) +void Item_func::fix_after_pullout(st_select_lex *parent_select, + st_select_lex *removed_select, + Item **ref) { Item **arg,**arg_end; @@ -237,7 +239,7 @@ void Item_func::fix_after_pullout(st_sel { for (arg=args, arg_end=args+arg_count; arg != arg_end ; arg++) { - (*arg)->fix_after_pullout(new_parent, arg); + (*arg)->fix_after_pullout(parent_select, removed_select, arg); Item *item= *arg; used_tables_cache|= item->used_tables(); === modified file 'sql/item_func.h' --- a/sql/item_func.h 2010-07-13 17:29:44 +0000 +++ b/sql/item_func.h 2010-10-15 10:32:50 +0000 @@ -120,7 +120,8 @@ public: // Constructor used for Item_cond_and/or (see Item comment) Item_func(THD *thd, Item_func *item); bool fix_fields(THD *, Item **ref); - void fix_after_pullout(st_select_lex *new_parent, Item **ref); + void fix_after_pullout(st_select_lex *parent_select, + st_select_lex *removed_select, Item **ref); table_map used_tables() const; table_map not_null_tables() const; void update_used_tables(); === modified file 'sql/item_row.cc' --- a/sql/item_row.cc 2010-07-13 17:29:44 +0000 +++ b/sql/item_row.cc 2010-10-15 10:32:50 +0000 @@ -138,13 +138,15 @@ void Item_row::update_used_tables() } } -void Item_row::fix_after_pullout(st_select_lex *new_parent, Item **ref) +void Item_row::fix_after_pullout(st_select_lex *parent_select, + st_select_lex *removed_select, + Item **ref) { used_tables_cache= 0; const_item_cache= 1; for (uint i= 0; i < arg_count; i++) { - items[i]->fix_after_pullout(new_parent, &items[i]); + items[i]->fix_after_pullout(parent_select, removed_select, &items[i]); used_tables_cache|= items[i]->used_tables(); const_item_cache&= items[i]->const_item(); } === modified file 'sql/item_row.h' --- a/sql/item_row.h 2010-07-13 17:29:44 +0000 +++ b/sql/item_row.h 2010-10-15 10:32:50 +0000 @@ -63,7 +63,8 @@ public: return 0; }; bool fix_fields(THD *thd, Item **ref); - void fix_after_pullout(st_select_lex *new_parent, Item **ref); + void fix_after_pullout(st_select_lex *parent_select, + st_select_lex *removed_select, Item **ref); void cleanup(); void split_sum_func(THD *thd, Item **ref_pointer_array, List &fields); table_map used_tables() const { return used_tables_cache; }; === modified file 'sql/item_subselect.cc' --- a/sql/item_subselect.cc 2010-10-13 13:27:36 +0000 +++ b/sql/item_subselect.cc 2010-10-15 10:32:50 +0000 @@ -319,6 +319,69 @@ bool Item_subselect::exec() } +/** + Fix used tables information for a subquery after query transformations. + Common actions for all predicates involving subqueries. + Most actions here involve re-resolving information for conditions + and items belonging to the subquery. + Notice that the usage information from underlying expressions is not + propagated to the subquery predicate, as it belongs to inner layers + of the query operator structure. + However, when underlying expressions contain outer references into + a select_lex on this level, the relevant information must be updated + when these expressions are resolved. +*/ + +void Item_subselect::fix_after_pullout(st_select_lex *parent_select, + st_select_lex *removed_select, + Item **ref) + +{ + /* Clear usage information for this subquery predicate object */ + used_tables_cache= 0; + const_item_cache= 1; + + /* + Go through all query specification objects of the subquery and re-resolve + all relevant expressions belonging to them. + */ + for (SELECT_LEX *sel= unit->first_select(); sel; sel= sel->next_select()) + { + if (sel->where) + sel->where->fix_after_pullout(parent_select, removed_select, + &sel->where); + + if (sel->having) + sel->having->fix_after_pullout(parent_select, removed_select, + &sel->having); + + List_iterator li(sel->item_list); + Item *item; + while ((item=li++)) + item->fix_after_pullout(parent_select, removed_select, li.ref()); + + /* + No need to call fix_after_pullout() for outer-join conditions, as these + cannot have outer references. + */ + + /* Re-resolve ORDER BY and GROUP BY fields */ + + for (ORDER *order= (ORDER*) sel->order_list.first; + order; + order= order->next) + (*order->item)->fix_after_pullout(parent_select, removed_select, + order->item); + + for (ORDER *group= (ORDER*) sel->group_list.first; + group; + group= group->next) + (*group->item)->fix_after_pullout(parent_select, removed_select, + group->item); + } +} + + /* Compute the IN predicate if the left operand's cache changed. */ @@ -1820,6 +1883,19 @@ bool Item_in_subselect::fix_fields(THD * } +void Item_in_subselect::fix_after_pullout(st_select_lex *parent_select, + st_select_lex *removed_select, + Item **ref) +{ + Item_subselect::fix_after_pullout(parent_select, removed_select, ref); + + left_expr->fix_after_pullout(parent_select, removed_select, &left_expr); + + used_tables_cache|= left_expr->used_tables(); + const_item_cache&= left_expr->const_item(); +} + + /** Try to create an engine to compute the subselect via materialization, and if this fails, revert to execution via the IN=>EXISTS transformation. === modified file 'sql/item_subselect.h' --- a/sql/item_subselect.h 2010-07-26 11:34:07 +0000 +++ b/sql/item_subselect.h 2010-10-15 10:32:50 +0000 @@ -124,6 +124,8 @@ public: return null_value; } bool fix_fields(THD *thd, Item **ref); + void fix_after_pullout(st_select_lex *parent_select, + st_select_lex *removed_select, Item **ref); virtual bool exec(); virtual void fix_length_and_dec(); table_map used_tables() const; @@ -170,6 +172,9 @@ public: friend bool Item_field::fix_fields(THD *, Item **); friend int Item_field::fix_outer_field(THD *, Field **, Item **); friend bool Item_ref::fix_fields(THD *, Item **); + friend void Item_field::fix_after_pullout(st_select_lex *parent_select, + st_select_lex *removed_select, + Item **ref); friend void mark_select_range_as_dependent(THD*, st_select_lex*, st_select_lex*, Field*, Item*, Item_ident*); @@ -403,6 +408,8 @@ public: bool test_limit(st_select_lex_unit *unit); virtual void print(String *str, enum_query_type query_type); bool fix_fields(THD *thd, Item **ref); + void fix_after_pullout(st_select_lex *parent_select, + st_select_lex *removed_select, Item **ref); bool setup_engine(); bool init_left_expr_cache(); bool is_expensive_processor(uchar *arg); === modified file 'sql/sql_select.cc' --- a/sql/sql_select.cc 2010-10-13 13:27:36 +0000 +++ b/sql/sql_select.cc 2010-10-15 10:32:50 +0000 @@ -3548,16 +3548,20 @@ static TABLE_LIST *alloc_join_nest(THD * } -void fix_list_after_tbl_changes(SELECT_LEX *new_parent, List *tlist) +void fix_list_after_tbl_changes(st_select_lex *parent_select, + st_select_lex *removed_select, + List *tlist) { List_iterator it(*tlist); TABLE_LIST *table; while ((table= it++)) { if (table->on_expr) - table->on_expr->fix_after_pullout(new_parent, &table->on_expr); + table->on_expr->fix_after_pullout(parent_select, removed_select, + &table->on_expr); if (table->nested_join) - fix_list_after_tbl_changes(new_parent, &table->nested_join->join_list); + fix_list_after_tbl_changes(parent_select, removed_select, + &table->nested_join->join_list); } } @@ -3870,15 +3874,16 @@ bool convert_subquery_to_semijoin(JOIN * sj_nest->sj_on_expr->fix_fields(thd, &sj_nest->sj_on_expr); } + /* Unlink the child select_lex: */ + subq_lex->master_unit()->exclude_level(); /* Walk through sj nest's WHERE and ON expressions and call item->fix_table_changes() for all items. */ - sj_nest->sj_on_expr->fix_after_pullout(parent_lex, &sj_nest->sj_on_expr); - fix_list_after_tbl_changes(parent_lex, &nested_join->join_list); - - /* Unlink the child select_lex so it doesn't show up in EXPLAIN: */ - subq_lex->master_unit()->exclude_level(); + sj_nest->sj_on_expr->fix_after_pullout(parent_lex, subq_lex, + &sj_nest->sj_on_expr); + fix_list_after_tbl_changes(parent_lex, subq_lex, + &sj_nest->nested_join->join_list); //TODO fix QT_ DBUG_EXECUTE("where", --===============3168847291943368239== MIME-Version: 1.0 Content-Type: text/bzr-bundle; charset="us-ascii"; name="bzr/roy.lyseng@stripped" Content-Transfer-Encoding: 7bit Content-Disposition: inline # Bazaar merge directive format 2 (Bazaar 0.90) # revision_id: roy.lyseng@stripped # target_branch: file:///home/rl136806/mysql/repo/mysql-work3/ # testament_sha1: 929c4908c003b8b6551bfa7fd23ec92a06a845c4 # timestamp: 2010-10-15 12:33:48 +0200 # base_revision_id: jorgen.loland@stripped\ # 91b1neg0ms0k67hd # # Begin bundle IyBCYXphYXIgcmV2aXNpb24gYnVuZGxlIHY0CiMKQlpoOTFBWSZTWaTWFjEAJCf/gHZ9Eyf///// f+f/7r////5gMh4ffOe1vd1mdutbfZ733Xu333oKPozyMveH1XZ77r7bt99SePC5YPWd24XbRKhf WLYxvc6ABffTezhwqJbY2ztxd33HT0H0Tsnrzhk99Mrl2xV7dOh63tT7nOamVMCRoS1sdYtdSnEz Zt9O9s9qp3m96GszdOXZjIEoKGQhhT1MyJ6JT9NEj9UPFBmkGmgbUGgAABtT1BoEoQACCITCYSmG mlND0TRoAA0DIAAAGTBKARAknqbCI1MmQ0AaaaaeiAADQ0APUA0AaBJpRECaEwQap5PSekn6m1TR 6iB+phEYAQAHqZNDEGmQRKEJoUxPRiaNDSTZJ6J6aqfjSFNP0UPMqaNPUPap5Gpk/Uh6htJ5QKki CAAgBAaAJkGk9Ik2moeoPU9T1Gm1D0h6Qeo0yZOFRyERckUCPn96vWeRT6FewKEkkIhUpRT9ithY r5hoKHvF96QgEI/j/qsiGCGSYBQX8QCiuQARxFf5K/uVzoj3pirD1iZCgEBhQCNlcFcwBokkvJNY f3B/WH/kNgcw0wMApAOSSUA7Cfwm/sPf76VmUts+g8lCdnynTjbmwsOdPnnLydmPo+jON0kw/z7z 5UrOijjyZhU7ZIoy5VGFU9lmgieTTZo7V4+2q02bLqZZTSpfNtwP2wm2z8KeNRVENkWRLcmGxZpf t04eQpclAgiGvbI4ZePyio6fvE5PZw+O3rBE+f0mlas3YeksRaKiub/WLik5ZyKYXX+Ccz3pu5M3 XAemTPk+H61kO5ok0ZfGjZT1MQGOTndOh7+uPfX1JdwrIsF4W6qos4uck2S0vh7+J5u5pzhivFbh ZmlJ3zzvIasuVv60ZTDsg+SgquEJqzGpSoMC/e83GXEkeahsuwvgOe8wNavOZVYuxYukhoIhDjlA sBnFVVVaS1fMbPFVVVVW+TCfFUR3Znu+m23xq/6pUlEjdo0ZRZSu97suhz2EvCwPBDyXJ8IPpDZd YRYRYLIMwH9OBFg7iDbljxgWTENioQEqAwC2IB9VBFJv0VebG4AqAdmg1bJxyOmQAvNvZHtbvos/ dOkAdVFhM73FXDDyqExzJOaSVnrWlCRCYsVlgA6+D+2XwhPSIMIiwZdjLyqgfLrD5GtyUCsBUQ0U GrPbWue6LWqDLa8smQkWzmU2CIgH1hy0FQTAS0BXoiMgbqUAkRGzIyAiSLGLIpEYQKwhuQAxuPIa M85/jVvX+QB+ABR4mN8+AqYAUyUPAWT7GQUIiAkkLjHQWyKhiXnGP2RYN9UCtRfMQRNIpap6Zsnm p/G+VAH8bHb95+39xivT5wXuBfKEiqrIsGKgoqxjGRkYxGRVFUAtNZN2iGHRfpda6JJl6XFmY6YT OdOyBGCq4S8Mrk2inmo82lZKsyCqqil30GRAGwvnF9umLMOHRNzsHYy7tswk264MZpsTehuaMDAm YIG0TLMCZa3U3aZrNtxwdmk1zTcverVWqgQc+Swo6VKRpFaLVYrVarOtfih9n6CGjxTlkqiVEDv7 S3ycCKcICaR88upj1HceL/ghCVHxaNFT65LT5xZtCnoGMfSpIoonoCHYX64FtWpbS2iCD5UEPbw9 50S/JDUIvGhn9V118Gt0M0kdM1zV9EanR7cLv7rWnru5qhnhgVu0rpqOzoIQOgwibhRE8tPBKGW0 zXa8g+sAqv74sJJIGUQLHIwNO06CDrMDdi12zhPQe4p6XzLLKUuuuuupdLjFYcpPEvqQuNT4Z55W bmmjDF465HJxYLxK6NNupktV1pwrOEnT+z9lLqemLrZ1PuWPndRSbNJpZ98VNn9ydwoKCgoKAnjv 1m2DmJsXWrZ6VU6akhxroJYDAD5JHrduwBC5e80G+1Zr4VG3SZ6VHz1dGapXnvXIOxmouYUah1hH gUSALSteX00CE954WakUkrrDheCse1CCMu83PjtEERj6oze/n5tQcP7NlVRXIguzYahUcsNJxB46 FHAy/e4hSpxr6bQpO77K3RDch16blNaK2cM1biWd5KbTMZlwIiWkykbAxGjD6OzLhCNQYa4chr2C Faufo9o2rTw177eT0ikhYtibxmZ6vzRyj12Z2wHHRz59ZLVz1Nxt/0qpgHkYKSVzN90ESA2oQbWI 5TE/AfOkxdEdK9qqrmtJnMw6BCCNSTlmy9JRJNPoPLa+EsTXaDnawbnHFqlefRsDaEl25J2s1zu3 s87v9thzbORNR4c5/JtYKChchdURrLR8AIxkqa7+qNKwVdFkrECz0d2kd4oiSjW2tNVCH1SbtMm6 S3CvEOqCJzRHjYrsvYRfoGLK7sJ5xlySyGOK7RYaJelJakalJY0XZli42GUDKsZbpmBVkcVGB6U7 aHUEPvfic/VBEO+IjxMmx7rE66LgIXBHL2sWsyGcgzZ4MCYIwaRGESP0xRGxva7Vuty1t90x0iNA jREj1RmOTMaK8mnvAiXtuhzw2G3tMtumeh0x3lM1C759WeTGbC48K7sbnHkxYS4o+Ze8YqRqm0GM 02F5B1KeqtMWhFlmz5CiMsLM7MdI4XSVMGJd1zW1REvQkDmxhxs6MFWUFFjrVXoSVmaO2cj4rRZL efD37LvSQUyjKohs7Sq0bTq6CSGvrYQCSiCT3VZg8vvV3g3qqsZgpGoSgvLIEZ9zl8zttysvcWO5 0aJa1vOg5K0YrwceTJa3uV6eis6sXfMqq1jV8DW2VZEA6CkVVVU+ZFiIxiILwvV5gk36buPJzHpx bes5vLs68b+njpzXhjjt9cJDmQnnEkRkAj8yiKiKqqqqJ4HdEwBDWMP83+/6af5fT2+b1gMlaKqq ivEgGDvxsxOYQFgpqMhSSeSUDraf4t4KHakRSAkV+YgHpEZ9mCfp3bcWklPYnWzBDLCWcSvlqnPT GvLcR811q2tkA5fNjkMXOZVapRX2EC3+9LkklS70LRLBYUQogo9fjoHKbtMQOOy7XSor3r9W/dZJ JVLqFa8Ve6yrAK8BLDGN8ERUBMAAs6N/6AJKIpD2kKCn2Q8dHbVMISEiDgBSC2qC8XHvZopveugD IuvVdVXW9u2HJ3PlF575sT+yR+wqPAVERptY+tPrPrM3XynjOfDS1KfR96xsWMFl1KXUqqrzA648 JJJl0+Xgw/70aqqmq88O0KKCnWK7IsEApxmwN54D6sF3Yli5rannkSNrqbt1VcO+R9V0woFJYghH Jq31j5tarrZlmlpaUgM1QvFUHhlFkH0mOWSsMtWrGRqoFzZlBVNg1rWLJKNBrPXRE5u8klPHXZut wI87Trg7tOhiseVtO2YvVoiFQ2QB5IiMOYsAc0zT8D/v7es5JMe7uDTpxdUcMS+i8emBMXiCMGc9 bCn9u/uama87TqAIShvGIJYUIeu8z3/G1cXCYEwUwdAc3MIMATSg5IrsxEkATIXX1rMmNJWtUqVK lgh0yST1953nifAbRxzaXptzbut1tVcZpprpql35lVeFWoecH0B5nuD0UpDthT7aAucTp+KZ0Dw8 XGC2BvvaMYxjGSSSF/Y8E29btLJMQhYvwD5s+EMmdfdaJsbzwkmqQ87UhhhvSZZAMaU0drALs3OX UTAJNTGDAqUuvYvSUp2tRJLYGjXZra9eCSs2mHBpSSyilGui6+ljmvCSbEmSSRpNRa+FiLqILrrp YehWD3sXoU1tLEiTUERJlSXkWuC1BqDBQ9iwMOMxCKrceDhqKjjIopkUESIPMi1In0fg2TWc5ubi oosTYrYyMVXRJEbQ4iRtIa6tOaoriAiPJjzRJMCIjJwn31/JU2LEzoPGLjjYyWOedEyrmxxwjJg5 NLNyZMWlra9PQvZm+TqnYRq9YleY3IL71dquzVnzLULRN/gnKVpfRLXSNMJw7DJXJbiuMnBdiit9 2Zc2m5+UM4UUM+8jNntFkUOG2EYUkkd1Ed6USSnBZJyIiZPtwOfcU8T1PL0JHxqNTCOGGwMh2PTa wwcsRE1nH29bnf3aECzwu2lVwzKegvXweapxWz+X9Wl2TWlM9J0k9usbablqIxsQBDKWPgAdibbg hkkLofRaSQoQIIJuIiPGVZjCAoc1Hj6jkRyV4Gb7nXoc6jowCIlR0CwN24Zy9B0x4S1opNNpJkbT O5TmOBRcQkoumSIm6IFE0NwyWNDJ8w3T+PiRIQHtUF5NDFTuhUpMA7es5+YiM0JoZL2imC5J646c FTiojBudbqdzpUwVZsYB3Em/rxaWl4XZCcOpkyWbHnknpb1wTgOI8SKeJW6pm1YwkJBOKAnCGmmm OsWjDoKqpA16e0oSOs86aawIJFrYZFqOqAZmi7aJ3YenKpFU0DbRrqEkx2nS2m06XAIG5I75Cgx0 G74QVXN6EwcHUsyFzQBe5IdwUYFqyyO0cOClmcgotG0AbGQREk8gtSJ908ChiWeVNmmPO46SAPve 2dlUELlRxcUuLbrFbcmTiBY0WOpTA9FTJIkejIiJMJFTXdr6+DbMmbm3tzm4N7NZk63rD1utk5Ke 2UN48BqDnPafEYnhU5jpAbyquHdcW5nXq07+q5pAiR8O6VnXiiCF7nb1eeolDxNDz2RMqFvYmcem /LWk0sXFjsvlu5kel+2SmfewiOqps0mxqXERHoIPOg4ngWB4GBhiXV2iZUgOJ+3t7ZuXGLmwuxcL ipSmuwEJbnsKVOCpuKex2P6lMoiAxwPl0iREyVNyT4qyoqDSWJE0ck0sWLMWpku7w5882xqkZuLU 6Ohgze98Gbj6u3sbHJw4bWxms5yObnI3dLexJJzD/Fk9T2vW9RD2BEOY68RQ787IB3JAb4Doxj4s U8oyEQWsIb+lHU1tkRTdO6pt2+JrMRvo4dTK8DFY9sZkQPE8fGr4XpmhySLKbGRTPeKjhj6TYcIE 0QLHkOHjSceaYNnehc23HnYUaYWJUGHDx+5UePJmKkh6JkUKHAxIcNLEXNc9V4JnJ8JQujcoIngb FIm5kiSNyVzcZPoKSORh44szLMFBzWa2D6ui6lMijg/Q6Q9Mjgd1Oc3Eh3SBQYAiJeUHBdyMGarR xLg9sZXhKzSiwtno8VtRJMmjrfb4O5qaGSqJJkzLNFdym0qFDzQ761KEyxopt7UPTsPEjZblTBnu RDuNuZCmxuWt2w39mgLDgU0O10GNjXn1xqKKZ2GKiIYyQ12ONHHBrocxgSWtxzCIuDY25FHnA1Kh +cHQs3MFPK4LtimtZTa588fk8rg63QcAbSQ84ixuJzSc6eaCFW3TbmajDJcR1QhhPPMPGM7qPIxf FXc293ebDwQUErO+Ao1VUt4HkZm8xgqITFEDzGGCxQfa5IbZZHiRY344lpnBoZ+T556HEiBEqTu/ KZFLmiHqt2kbEUefaefIlQ2cNj7CM925oGSpONOL0ejYZZsWbg7dtiKiTt5ulpaHaxYGpt254ul2 tDU4tr7FnB0tTNN7Bgs1WmWNVWDWxXDxedbFyYO1k38pHSMRAxwMK6x7GKIyPKrXPJG+TlNJMoJT CcRFM5eMQUcJsgoc1jASZm1dHTghi9CkRxTWssgp70QZESckEIlhjYAUhY2OTT9KYlpmdEVixY2D 4HqRoPEYkt7Fyw89x5m841YzzQ0RQxpwa1opR5d6yL1Jg0LNzBz0pJtZ0xNi5kUgUKGSR3jEFHna VPIKkh4jy5U91TGgQccmpkPMydxuQK9ZG3hCuY8vjJmabDOhq84uozoVvvCZE7hrlBIQOhguOJKL mA4eSSxYiROhXRprFJC1ZwBWrR7JmOpShabj1AMmI4JSCZPQPLDndpu1Ik6GaZO8jPasxtHBYFMk yDPxYdwC7ki2cQgxckWmhKk8Tc71crw5JECYuUsHLc3MkjBYyMPKG4mxE3KDxi44kE8h5vLEi53d pQV/ZVuqGlmcvS3NXCrHaYJEU0gqkXEklUgE1m4Y2NBkEPqIQEFwUS2YTMkPcSLviONYNAFk5K3R 9RxJzCJYaqCqQKHQiOJdtL4WoicC2Pn52HTVIRLFjgiWsow82MlO0kUJlS4848vg2CZAsTMGh4xg TwdDU9Tiu4MFOTAknW2tTH0hnfm+Xpez4+n165/QM27rJO/uelnt9HRoZw6YwnVt2jEPKmLRBNoh s2y+AnLEW/jwsKzAtta4IvAq45Zb3h1Pr3FnYfrGoUbIdLIf3MyRgMnJoBj7np7Ntz3Y8uOPH77p 4O7U5xi4djyiiRhEhJxBQbqaNzdIxVJKHlx9fujbs+kSXzF+WO7HN7IOHOsfV3gQ77EkKvdPTMar LLsUFhYybnZ4YP6JycSVgsIkVGLCJDvV+MJLAhQYxjGMVVT5CAXyAa8moAHybIJFGEAkScInb9IL z4YoncNIChQQMQ/EQNRlBIiv0vr774YPNDMjuAD5QR/+FT9aAtyIn6HeB+S1TGDxlBkFIgyMuPcj 9IH5ot4fkQA0ZaHfPdMoFlACifuiThEn7iYqRzFSOaKhE5Ql1hxwkBZ++QH8Ik3pB0JJJcJxiTQp JGe2SNYWlkW2BZAblcaw3yMy7/7gWuxvdhY/xSSSr74JNR0hdCQAhoE1reKlQVu/gtwe4tdB+7Mf Z2/zlrnwqT/5lhx/sxEt/VbOjfAh0ip4A3o65ABuo3hTjVUdQIxAgqfzz3fAQAuT80BaINRKBCCq YotSkIOIFkSgK2RYKm1VRqgYQN+k11KU5hU2kB5YSk5sp3ZF3REYjFvG9uiTwBugk4RJcmBKSST+ A6HTBJ/ggNweI/iBgAOxQGRZEgEIiby6tfXYZDzQ1hCCdk2DARERERERETskSOpJHYvCZO0WIaum WUpLucSOhOMVKCYi1FiLQQbEQZYmN4mk25gMTYZRUwXLo38pCD/3KFyL+iLzipyjiAK2TMJgoeFW AQVYKngX8soSB1MPxsh3oAdyH6y/qwAbEFBPZQlQ/+oxo1pVeQR3pIOQf6x5MW79P9dKp6Wl0zKd R9Yq/SH4D9gex3yEH6goh85+v/AhXAHJcSEKrUH6FaIf0l1wLQRqEItFLjJXu2mId4UkWZiYhnhL IiIYAC0IgH4iiqmTT+3Xgy3GwllEvFSpTMz0RmgYhaSXD9Lsk5eOkDZOIalWSiQVBiIqCMEUZAQB gjyhSjAJvEhJykkQQJrMRUBVjqQhmqGkDVijUwMAqWi0HeHrknC+IVJDuT+Ih1FCiQIReVg38v+y 4CgQ+4+895uLyp+0vfaIic3PGBEo7wYnSKHMvuHMzmFPnJMDAo5h4GNY5rmImQyfT4bIE5mEtGLS gsJ56S4vEJhMrxU/7bXUp0tCQdpqcnWzk4uyJ2+EhGh2116CzpX7FNLUwcSmUn6kDXNU1ySdnCT0 n8Qf0zjFu6eaBsYKfO21KXmU1zZ64k/mVDweXA8kcV38aodUXyaMASH2wXJcYAG8cLtzOEzydrUP NR39k+GQtIknU+t7K/bUYHSNg7JSO3dkErUc42OL6W3tNrOU0Sqg00LIGwScTr7dnAlxTrTYoyCF NM4o6bABlrsTOnqqYKksCvFi2eh8Q1zyN8juDL98q+p0Uoz9s6ewL0PkFA0L7Xv93wyEXfH24rvi zWvZ1qdT7EjB0Pt5M3yfJof1PmwND3u9TjxyehuaD7Wl62C0jkx3sHJT1LODB0NJJOtE2sWhrU5t rc867M8XNdxYNjQ4qTJGDwRMEihIwdOm7NPAshRDzm25UHmj+MF32PIcYh7AqYqSTTEssdMiJobr JJiRl6XFcu7XQ1yJilEPlCPfQqoRmI+bvyT4hu6bk+0akJQ9Twp5T3J3FKUPM8D0JHJ4GSRgmSPk fMZNECB6eNB5uZHGj0JScepA3NFS4YLSFU+Q4ePOhU0WNNtQ/EU86NT+s7eYOmWH66JUTrgvGdZp N4nUdInF2up4OTU0OprbabHzafaydbJVTgqyKpm1u14PhZ5GbVo4qm7hKNzvc3Nzep5F3OavEBps A5w4ALRP0tth1kp6b9CvOQyvCJljSwLkSygnyZwKvQCpk8fZYK3UX8GEhI2iOdPicr8q2uTRY5go 4Uc7h1EgiIiJIFiq2IzATd3IZv3zk7+zIeCPykOo8x1G+/J1gFT1mZXpANF37WHwEJQYcTUmc2Ca NcTCBgIlGA4EUEaSxrIUSID7qWEUmGFqSypqJKlqHsefu9vydD5u98nVz1NrS6FKbWLJdraV2lx1 MXyOtrbnJJKb3yZmhySTa3NrTm3NrS540b4SdSLkckjCoRUVSkj5OHY933vK8ia6dbrdDlOazm62 xg2vQs9qWLyHRoPToqIeAymwzQ6OBNeWK5PgdJVk0/GlVnYQoYhwQb1UoYm8bDkKoOxF3uTozFdT zh3LMPt96z7TX55U6LsthNTW8XBvBqDH8fb+auq1rEVST8LV3mf1rkRd3K0HmafS0tvxjKs2IipC bJ+Gk8PCgIisU+LihAyORq2k4cu/t0XnFbvDJa6aiCM2LKiHRVEiG1S3Arvm9hVVP0xRlBixBHAt B1bD3gq2FcayMBOKoLG450iJRZQf06fFz01OnKHQQVERHOiigrMoMNP01v2S2GMhHVRJ8Xyd7g9D S5snrKe5z0fNg8ympqe1sDNsaVy7Y1sTUu2LMmpsbNImqP8WDbvZHswc/QPwaFNEADBYU3PmHuiU anFw1qu9TbrVk5vm1ZsjBudBrbOl1oleBpKcwLzAtsxpiDfCl4LrIAalXfo5Fv0GTHfL6menb2+I VdBnNhpPY+VXxlfHnznliSSEtLFsV7wJelKVQkluUq4xfnn4uvHk3LjG5bR5SLRBtxZ+JL0keIzz LYrkSl0LqWbHoItVW1G6OatCXWuHI3XSNS86NxgrYLy9RMqCovPO0zUkXT0/LgfEmC0YOt62nbXj ho1QPCkSs2p5ZGA0s3uXSipLrr+CbSPIyGN+dnua+aOhFVtvWb2e5+bBlNc70FpESUIZkzZ7QB3i A9cA3EUQrgilAuVVRFwxjTSqPMRfFDnNefmkM9IGNaF9YBthWCCUrwtVXtKqHWHzq8F+nqA5OOC0 qRGKZduAzgvjppMzTOHCWVQgWRFSblYEP4VAxdOlDGT6BQOLCXKJ3hvYDOaABC9yNptjrOiK0OzQ aLvTFKqKmLqXwzrS67YDUS2lm4UDFTRnUsijSnOVBMLJgSNKDKFnkpo9QOpuRw6e05jIq2Tf1iuE ViKGvFPQBAM1cGhRKqElvSE48480kmH27bFi95HB9yt1SX/32L4vj5AsjQipJYFfAT3O90qfBoyp d3LvhLtL3MGD3vivI+0kmtr2PK+74/Ikm5u42d7Q5PVyci7Y2rNK7BjxYGLWcQqUPZEwPOQmclgg bEz9EuULEznlxsKWOhkwaG1wiTCpGqQodgTqcZ0O6JOE9gOxTxFCyZ1ehPGTdCiRt/mC9QLUV4Cb h+rNgTTz5goiIgZCVKQqkMU/kT0BvVI3N/6GImxezTaR6aKUqJfuY22oqpGCmTeZJ0z3sgBg3a1U YiiOyOpqlpK40hJtnpWjpi1qV3hTaR+D1zzMGVSSTx4TN8GF1PRkFwXLEVJs1LOeQ2ohREr7/Y3+ uux7qUoE4SwBtSIbxjqghcHnrq9NBpAKxJ0X3q1UvNJ6DlPMeQvVDEReUpm4Sj/owCnklgWLw6nk 2ngoCXg+6KwIIoMGRVkm4D2XnWKfOygyAcGAGmBhYkEpU1YKQ+Y5zvOo1kmIfKbSnixDF4tPqdn5 3nbu50uh2IGmR+JTzHi7/EP5OU8YHZOoLbFrpG1rflMts2bg/GReg6u1Ba2r25WxBAyBI5d7n7oM CbyLylwfi+j88SNc0vO93QTckRfTBNnGpz+6lioQmEStKDyQAPIENSPuqq+xXIK6DLSRXHZ4jRym ulBXigG+UofIQFaGqaNJhnH54E/10hgh6ft/X6Mr5+LFIRRYqiEiQBSIMYnOfWOMgcupJ1CoptoU jGoVWAQKqu5wPCeFftgzZuxCkONrOcRKhzPuD5e/9Xb8RbTT3vbthrrqW0tpbTTGMFtLaWlDH+/H DMqerpKG8cTwYvFt26zjK9RtUA5AVwgAWBaFICmwqfMep+bvEL7lVvPobHYQ1En1aGTGD1PhZ4Ob 6TpF7I1KB53tIzeH4NHtfR4E8vgtN7ydRFOvfLL/ExJJqdsj5g5h3uv0OD8MzKSTJxd78H6Q2tDU 6A5830cg7KCvTFSpZVrUeiRSKQqi6pFjezkkzXdiTKknrC8PAwNBU6foRsGw0vMvpl55NbLANlZK KVZUKEc4BQugLQTLFyKCVBaBAWKHz44KIsPLGxhsANFRXIegC7IYLh5qFBULrfrpjN/Gy038vD2a ibySZIN1Q3Uc5VSSSu5rbeMT+YjjGsm5JJ8HHDU/oqthI7YM9rGdmghyp9ge1fX01pvixWb5JJ+W WhUkVBVN5RpKDBBCSDIaz4CnxMyIqQdBjzeUtrS5ahZP1gvCYnPQ1qqQYD2TDVj8e9eJGW5nhJI9 XKRkXmhpbVf5JGi7LbVSokbczbEnUL6sKxQ3SOxb4kU2cHsdTa80G7+w+cHzag3fPhaqOHG91S/e 1w5AyhjEdGEzmFJlFl0zXCzLOstFNLdOdmNjk2pAynqKUDywugQ3meg6T721set6jNJNQUCiNVpN ReSWjpiPOngQLwb4M8LVKtUMI/sIGSgFC0srMLEXIxYRIwYKsp/n56ofcRh972E2zEPYclhZ4HDa kB0oFSEIijOTsMySSi2TsOW/gCPjPvvqDhnpraO/N6Ge82nSpxKZZJloByUpGYSGAB6SqNrWIqpV ZssNRbNqsa0oK6Wekq2jpBXvAOG3x76sEMItrIN1lyuIyuioiHT8jOsfHW/KauFEJ70A0BKzJRG+ mguLnsgnqAH1KnSIuIJv9nnaC3IKSPAkMSEgj/MZpWHoN6c1ooGiBWFYjQU4AHHEMKhAO70jSbQz P4rwJywzpCZCBymQmgYDQsdofP86qvuIB6TsMDDpQkTdrp+rkopdprHAMqglVXuEKpvB1t1uuhyk xBDcyCBFEynNTJkSxH3AigHvOB5JuLBQusa8pxZggHcbRV8PHF5JI8Xs5q1fk0OMhNTcqH7PGwex 9X7LyST9H2PxmrpIn3tRa6PoNGx6nGqGWAv1EJB6lSisoFRXCW2Puao/NTGJPgDWZHox4SFsI88j 2KNA4v535Pm6XDKdlVrfB5mCF3+YN5sODU7mCnoMwGfriknjZCFRI0CJsxBfAaVVoe5mkku6WLrX amUja9Q4yT4cXgedT3vB1MQ+LFwb4DQ3Pz8ZHFTwP50kn6uPbMHOEqgCCyCCIiyCv2gAQKSE9p1n DYcEBAekBsRC2oCKDJQbFigyX0XKcYoMWecAT+MA8gBZ5uhtXa6pUpVFREmwLISbJhcNf6O3JDDO fFowJdBapUVN6r3WdBXIxsiWd4eslS+cIpwq+IAwMTsQpFJLJCkiJkYvlhVkGEIxjBIuQQxQuiAt cDd6vVmb5LX3Fk71azCakNhAoCQPs2a/FaJNkmtKDkR4ryfsYQOrdvumM6WjQ4sThMIh65GR/RIp ye5Ubdgncm7BPY2m7yIaL0OQA0niPUiwFp/d1G40hrjxayou5EyeeA4cdl9p5Q01AYQYmUCCrACi CiIibwDrgFD9HeuHtNwTnI/HlNUDs70rDt23GC0slM3GNuDEWMTF1caUuSzGMYhWpEQ0dISGdo65 q5NfPcTXCOgYEyHCJLxNY0a9PGsTRKlWtmSbCN8u/JTYmCwFDdFBTqVMQCAjiieJkbKu9oPYMmZi blJEBL4fh33A4x0ZPj+E8RFR8NpQgGBkIoIyolGNQTUtFUaKmNF2DFPUHSxifyB5ALOPjx6uZN6D 7OkPa0aJ40icCTUZnQUM4BPWKDR6N0FaLf7cgCnIV1ng79RJO/oPUGwbqAGObjOIZOU8Qk8VVVVV kkkklxgXFDiU2CnGphhYsgb5myJNgBZJxvcYBxx0M8gMRADNyMlAblXIBO3jAf+uD+H3PvPh8DlM n6CZ9+SDw771cAPSq794zyfVfAxDtSCkFhO3tVVVVVVVVVVVVVVVVVfXJD6hjC53EIfdhS5vBIiC HoGJtOQ3nHo3blv5LzMg5IInPGDG9SVThEAaHZMByh+urgpwdg0yCbVdZUMFhhi9U7waWaGAyAdE cgCHclSzissPGANgwBolMGZIssSXkaSBpZgbGEkywjFmmMwrBjUslnLQwG5FDBRmWknXSBwK3IIf KKxFewig+6COs7+M7s65aqVU32bbXOo3kloYDFILqi8dUoLzK1oJUEsJMEsqDeGxJEzWCzjUeWrV 8nteOBfHGLyoqFUNcmr9g0hiEYlkLRI5iKpSlK4OubVSQGBzs7UhWE+hOjkyYL0WFj2xmB5sy7vz SRQlVEqFCiVShUUKMzahVxxdMCQIwOiUFWDAxbsq5QAuIcriE8KvjQOdAwFuAWlTIA9Fg1xkFE5s jSiH+LA8GdUhx1m3gHsO6SQ36nP2UJUlLi24kgWS4jjo5jw4a6poE7jYcOWKHkZJagezBlJJhM9r sDMMOysso11ONmCSekNjMvyD8iYqRxDZpBKCScYB0gDlNRBuN6I0r3flyqqm2WszJHoKhI7Hv5ZM /1BoSEbH2B6Vn/Kavmx73IdOJ3zBJJp4CO5TDDeapcKcZrZis+OP6St+rMAagd6xpoQZtyN7B9BH OyHlKEhPEbvab2okgq5mCmevCrokYNrpfucGpk73pBwqNrubJrSti25deCCgwkGGshxlpaaOA/CA bp00yaTOirzHOWAMe4FetwixSZnKcRyH12BXOYxglj0FK/FlDOmk+o/yuLh6AdOPGeG5RJ+r8Kbm 0Sfc8hbUiKhOlseR5Xc3fFwj4e9i1SZBdodZCfGpJVGgqo0MGt5wuG56mbMrQgf0/reRgYeV9jk1 MSN7mwebCRDWDfOg+8cCnSagHX1dTCadlnBhFC5pholttsJS0LikXYMhrmmmbrJJH8RksgToaQsk Txex1tXFoa6dRJOtudTg9DeDeH79Mkk+MkOf+x9XJ7OU4hrW4wLuhaRDeprbN+/gye4Pe1N70Hj6 4t/LVFrzCy1P8P/4u5IpwoSFJrCxiA== --===============3168847291943368239==--