From: Ole John Aske Date: January 14 2011 1:37pm Subject: bzr push into mysql-5.1-telco-7.0 branch (ole.john.aske:4111 to 4112) List-Archive: http://lists.mysql.com/commits/128753 Message-Id: <20110114133714.6A255223@fimafeng09.norway.sun.com> MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit 4112 Ole John Aske 2011-01-14 Backport to mysql-5.1-telco-7.0: Fix for bug#58818: Incorrect result for IN/ANY subquery If the ::single_value_transformer() find an existing HAVING condition it used to do the transformation: 1) HAVING cond -> (HAVING Cond) AND (cond_guard (Item_ref_null_helper(...)) As the AND condition in 1) is Mc'Carty evaluated, the right side of the AND cond should be executed only if the original 'HAVING evaluated' to true. However, as we failed to set 'top_level' for the tranformed HAVING condition, 'abort_on_null' was FALSE after transformation. An UNKNOWN having condition will then not terminate evaluation of the transformed having condition, and we incorrectly continued into the Item_ref_null_helper() part. modified: mysql-test/r/subselect.result mysql-test/t/subselect.test sql/item_subselect.cc 4111 Ole John Aske 2011-01-14 Backport to mysql-5.1-telco-7.0: Fix for bug#58628: Incorrect result for 'WHERE NULL NOT IN () create_ref_for_key() allowed any constant part of a REF key to be evaluated and stored into the 'key_buff' during ::optimize(). These 'store_key*' was *not* kept in ref.key_copy[] as they where constant and we assumed we would not have to reevaluate them during JOIN::exec() However, during execute NULL values in REF key has to be detected as they may need special attention - as in 'Full scan on NULL key'. This is done by subselect_uniquesubquery_engine::copy_ref_key() which check if any keyparts evaluated to a NULL-value. As we didn't keep a store_key for a constant value, a NULL-constant was not detected by subselect_uniquesubquery_engine::copy_ref_key() ! This fixs modifies create_ref_for_key() to check if a NULL-value constant was produced - In these cases it keeps the store_key, which then will be reevaluated in JOIN::exec() and trigger correct handling of NULL-valued keys. @ mysql-test/r/update.result Update of result file due to changed (improved) behaviour when constructing a REF key from 'out of bound' values. As the main fix will cause failing const key value ::copy() to be reevaluate inside JOIN::exec(), we will detect out of bound values (which will form an illegal/undefined REF key) before executing the access request. Previously these const REF keys was produced by JOIN::optimize() and any conversion/range errors was ignored. - Possibly causing undefined keys to be produced. modified: mysql-test/r/func_in.result mysql-test/r/update.result mysql-test/t/func_in.test sql/sql_select.cc sql/sql_select.h === modified file 'mysql-test/r/subselect.result' --- a/mysql-test/r/subselect.result 2010-04-15 14:04:24 +0000 +++ b/mysql-test/r/subselect.result 2011-01-14 13:36:47 +0000 @@ -4733,4 +4733,56 @@ ORDER BY (SELECT * FROM t1 WHERE MATCH(a SELECT * FROM t2 UNION SELECT * FROM t2 ORDER BY (SELECT * FROM t1 WHERE MATCH(a) AGAINST ('+abc' IN BOOLEAN MODE)); DROP TABLE t1,t2; +# +# Bug #58818: Incorrect result for IN/ANY subquery +# with HAVING condition +# +CREATE TABLE t1(i INT); +INSERT INTO t1 VALUES (1), (2), (3); +CREATE TABLE t1s(i INT); +INSERT INTO t1s VALUES (10), (20), (30); +CREATE TABLE t2s(i INT); +INSERT INTO t2s VALUES (100), (200), (300); +SELECT * FROM t1 +WHERE t1.i NOT IN +( +SELECT STRAIGHT_JOIN t2s.i +FROM +t1s LEFT OUTER JOIN t2s ON t2s.i = t1s.i +HAVING t2s.i = 999 +); +i +1 +2 +3 +SELECT * FROM t1 +WHERE t1.I IN +( +SELECT STRAIGHT_JOIN t2s.i +FROM +t1s LEFT OUTER JOIN t2s ON t2s.i = t1s.i +HAVING t2s.i = 999 +) IS UNKNOWN; +i +SELECT * FROM t1 +WHERE NOT t1.I = ANY +( +SELECT STRAIGHT_JOIN t2s.i +FROM +t1s LEFT OUTER JOIN t2s ON t2s.i = t1s.i +HAVING t2s.i = 999 +); +i +1 +2 +3 +SELECT * FROM t1 +WHERE t1.i = ANY ( +SELECT STRAIGHT_JOIN t2s.i +FROM +t1s LEFT OUTER JOIN t2s ON t2s.i = t1s.i +HAVING t2s.i = 999 +) IS UNKNOWN; +i +DROP TABLE t1,t1s,t2s; End of 5.1 tests === modified file 'mysql-test/t/subselect.test' --- a/mysql-test/t/subselect.test 2010-04-15 14:04:24 +0000 +++ b/mysql-test/t/subselect.test 2011-01-14 13:36:47 +0000 @@ -3725,4 +3725,53 @@ SELECT * FROM t2 UNION SELECT * FROM t2 DROP TABLE t1,t2; --enable_result_log +--echo # +--echo # Bug #58818: Incorrect result for IN/ANY subquery +--echo # with HAVING condition +--echo # + +CREATE TABLE t1(i INT); +INSERT INTO t1 VALUES (1), (2), (3); +CREATE TABLE t1s(i INT); +INSERT INTO t1s VALUES (10), (20), (30); +CREATE TABLE t2s(i INT); +INSERT INTO t2s VALUES (100), (200), (300); + +SELECT * FROM t1 +WHERE t1.i NOT IN +( + SELECT STRAIGHT_JOIN t2s.i + FROM + t1s LEFT OUTER JOIN t2s ON t2s.i = t1s.i + HAVING t2s.i = 999 +); + +SELECT * FROM t1 +WHERE t1.I IN +( + SELECT STRAIGHT_JOIN t2s.i + FROM + t1s LEFT OUTER JOIN t2s ON t2s.i = t1s.i + HAVING t2s.i = 999 +) IS UNKNOWN; + +SELECT * FROM t1 +WHERE NOT t1.I = ANY +( + SELECT STRAIGHT_JOIN t2s.i + FROM + t1s LEFT OUTER JOIN t2s ON t2s.i = t1s.i + HAVING t2s.i = 999 +); + +SELECT * FROM t1 + WHERE t1.i = ANY ( + SELECT STRAIGHT_JOIN t2s.i + FROM + t1s LEFT OUTER JOIN t2s ON t2s.i = t1s.i + HAVING t2s.i = 999 + ) IS UNKNOWN; + +DROP TABLE t1,t1s,t2s; + --echo End of 5.1 tests === modified file 'sql/item_subselect.cc' --- a/sql/item_subselect.cc 2010-10-06 10:06:47 +0000 +++ b/sql/item_subselect.cc 2011-01-14 13:36:47 +0000 @@ -1131,6 +1131,9 @@ Item_in_subselect::single_value_transfor select_lex->having= join->having= and_items(join->having, item); if (join->having == item) item->name= (char*)in_having_cond; +#ifndef MCP_BUG58818 + select_lex->having->top_level_item(); +#endif select_lex->having_fix_field= 1; /* we do not check join->having->fixed, because Item_and (from and_items) No bundle (reason: useless for push emails).