From: Ole John Aske Date: March 24 2011 9:27am Subject: bzr push into mysql-5.1-telco-7.0 branch (ole.john.aske:4252 to 4253) Bug#51070 List-Archive: http://lists.mysql.com/commits/133737 X-Bug: 51070 Message-Id: <20110324092732.94D64218@fimafeng09.norway.sun.com> MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit 4253 Ole John Aske 2011-03-24 Cherry picked fix for bug#51070 to mysql-5.1-telco-7.0. Required as this fix has already been cherrypicked to the SPJ branch, and we want to minimize the diff between SPJ and main telco branches. See: http://lists.mysql.com/commits/117686 for original commit message. modified: mysql-test/r/subselect4.result mysql-test/t/subselect4.test sql/item_cmpfunc.cc sql/item_subselect.cc 4252 Ole John Aske 2011-03-23 Cherry picked & backported fix for bug#11764737: 'Optimizer is overly eager to request ordered access.' 1) Ensure that JOIN_TAB::sorted and QUICK_SELECT_I::sorted is requested only when strict ordering is required from the handler - Either as a result of the query specifying ORDER/GROUP BY, or the handler being a source in a QUICK access method which require the sources to be ordered 2a Call handler::ha_index_init(int idx, bool sorted) with 'sorted==false' in any access methods requesting a single row (HA_READ_KEY_EXACT) (join_read_key(), ..... 2b) Else: Always use above 'sorted' attributes as arguments to handler::ha_index_init() and other handler methods having a 'bool sorted' argument(::read_range_first()) Furthermore the function 'disable_sorted_access(JOIN_TAB* join_tab)' has been introduced which collect all logic for turning off sort requirement. @ mysql-test/r/partition.result Accept changed result order for this (unordered) result set. modified: mysql-test/r/partition.result sql/ha_ndbcluster.cc sql/opt_range.cc sql/opt_range.h sql/sql_select.cc === modified file 'mysql-test/r/subselect4.result' --- a/mysql-test/r/subselect4.result 2010-08-05 10:42:14 +0000 +++ b/mysql-test/r/subselect4.result 2011-03-24 09:27:05 +0000 @@ -77,6 +77,92 @@ Note 1249 Select 2 was reduced during op CREATE VIEW v1 AS SELECT 1 LIKE ( 1 IN ( SELECT 1 ) ); CREATE VIEW v2 AS SELECT 1 LIKE '%' ESCAPE ( 1 IN ( SELECT 1 ) ); DROP VIEW v1, v2; +# +# Bug#51070: Query with a NOT IN subquery predicate returns a wrong +# result set +# +CREATE TABLE t1 ( a INT, b INT ); +INSERT INTO t1 VALUES ( 1, NULL ), ( 2, NULL ); +CREATE TABLE t2 ( c INT, d INT ); +INSERT INTO t2 VALUES ( NULL, 3 ), ( NULL, 4 ); +CREATE TABLE t3 ( e INT, f INT ); +INSERT INTO t3 VALUES ( NULL, NULL ), ( NULL, NULL ); +CREATE TABLE t4 ( a INT ); +INSERT INTO t4 VALUES (1), (2), (3); +CREATE TABLE t5 ( a INT ); +INSERT INTO t5 VALUES (NULL), (2); +EXPLAIN +SELECT * FROM t1 WHERE ( a, b ) NOT IN ( SELECT c, d FROM t2 ); +id select_type table type possible_keys key key_len ref rows Extra +x PRIMARY x x x x x x x x +x DEPENDENT SUBQUERY x x x x x x x x +SELECT * FROM t1 WHERE ( a, b ) NOT IN ( SELECT c, d FROM t2 ); +a b +EXPLAIN +SELECT * FROM t1 WHERE ( a, b ) NOT IN ( SELECT c, d FROM t2 ) IS NULL; +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 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where +SELECT * FROM t1 WHERE ( a, b ) NOT IN ( SELECT c, d FROM t2 ) IS NULL; +a b +1 NULL +2 NULL +SELECT * FROM t1 WHERE ( a, b ) IN ( SELECT c, d FROM t2 ) IS NULL; +a b +1 NULL +2 NULL +SELECT * FROM t1 WHERE ( a, b ) NOT IN ( SELECT c, d FROM t2 ) IS UNKNOWN; +a b +1 NULL +2 NULL +SELECT * FROM t1 WHERE (( a, b ) NOT IN ( SELECT c, d FROM t2 )) IS UNKNOWN; +a b +1 NULL +2 NULL +SELECT * FROM t1 WHERE 1 = 1 AND ( a, b ) NOT IN ( SELECT c, d FROM t2 ); +a b +EXPLAIN +SELECT * FROM t1 WHERE ( a, b ) NOT IN ( SELECT e, f FROM t3 ); +id select_type table type possible_keys key key_len ref rows Extra +x PRIMARY x x x x x x x x +x DEPENDENT SUBQUERY x x x x x x x x +SELECT * FROM t1 WHERE ( a, b ) NOT IN ( SELECT e, f FROM t3 ); +a b +EXPLAIN +SELECT * FROM t2 WHERE ( c, d ) NOT IN ( SELECT a, b FROM t1 ); +id select_type table type possible_keys key key_len ref rows Extra +x PRIMARY x x x x x x x x +x DEPENDENT SUBQUERY x x x x x x x x +SELECT * FROM t2 WHERE ( c, d ) NOT IN ( SELECT a, b FROM t1 ); +c d +EXPLAIN +SELECT * FROM t3 WHERE ( e, f ) NOT IN ( SELECT c, d FROM t2 ); +id select_type table type possible_keys key key_len ref rows Extra +x PRIMARY x x x x x x x x +x DEPENDENT SUBQUERY x x x x x x x x +SELECT * FROM t3 WHERE ( e, f ) NOT IN ( SELECT c, d FROM t2 ); +e f +EXPLAIN +SELECT * FROM t2 WHERE ( c, d ) NOT IN ( SELECT e, f FROM t3 ); +id select_type table type possible_keys key key_len ref rows Extra +x PRIMARY x x x x x x x x +x DEPENDENT SUBQUERY x x x x x x x x +SELECT * FROM t2 WHERE ( c, d ) NOT IN ( SELECT e, f FROM t3 ); +c d +SELECT * FROM t1 WHERE ( a, b ) NOT IN +( SELECT c, d FROM t2 WHERE c = 1 AND c <> 1 ); +a b +1 NULL +2 NULL +SELECT * FROM t1 WHERE b NOT IN ( SELECT c FROM t2 WHERE c = 1 ); +a b +1 NULL +2 NULL +SELECT * FROM t1 WHERE NULL NOT IN ( SELECT c FROM t2 WHERE c = 1 AND c <> 1 ); +a b +1 NULL +2 NULL +DROP TABLE t1, t2, t3, t4, t5; # # End of 5.1 tests. # === modified file 'mysql-test/t/subselect4.test' --- a/mysql-test/t/subselect4.test 2010-08-05 10:42:14 +0000 +++ b/mysql-test/t/subselect4.test 2011-03-24 09:27:05 +0000 @@ -74,6 +74,68 @@ CREATE VIEW v1 AS SELECT 1 LIKE ( 1 IN ( CREATE VIEW v2 AS SELECT 1 LIKE '%' ESCAPE ( 1 IN ( SELECT 1 ) ); DROP VIEW v1, v2; +--echo # +--echo # Bug#51070: Query with a NOT IN subquery predicate returns a wrong +--echo # result set +--echo # +CREATE TABLE t1 ( a INT, b INT ); +INSERT INTO t1 VALUES ( 1, NULL ), ( 2, NULL ); + +CREATE TABLE t2 ( c INT, d INT ); +INSERT INTO t2 VALUES ( NULL, 3 ), ( NULL, 4 ); + +CREATE TABLE t3 ( e INT, f INT ); +INSERT INTO t3 VALUES ( NULL, NULL ), ( NULL, NULL ); + +CREATE TABLE t4 ( a INT ); +INSERT INTO t4 VALUES (1), (2), (3); + +CREATE TABLE t5 ( a INT ); +INSERT INTO t5 VALUES (NULL), (2); + +--replace_column 1 x 3 x 4 x 5 x 6 x 7 x 8 x 9 x 10 x +EXPLAIN +SELECT * FROM t1 WHERE ( a, b ) NOT IN ( SELECT c, d FROM t2 ); +SELECT * FROM t1 WHERE ( a, b ) NOT IN ( SELECT c, d FROM t2 ); + +EXPLAIN +SELECT * FROM t1 WHERE ( a, b ) NOT IN ( SELECT c, d FROM t2 ) IS NULL; +SELECT * FROM t1 WHERE ( a, b ) NOT IN ( SELECT c, d FROM t2 ) IS NULL; +SELECT * FROM t1 WHERE ( a, b ) IN ( SELECT c, d FROM t2 ) IS NULL; +SELECT * FROM t1 WHERE ( a, b ) NOT IN ( SELECT c, d FROM t2 ) IS UNKNOWN; +SELECT * FROM t1 WHERE (( a, b ) NOT IN ( SELECT c, d FROM t2 )) IS UNKNOWN; + +SELECT * FROM t1 WHERE 1 = 1 AND ( a, b ) NOT IN ( SELECT c, d FROM t2 ); + +--replace_column 1 x 3 x 4 x 5 x 6 x 7 x 8 x 9 x 10 x +EXPLAIN +SELECT * FROM t1 WHERE ( a, b ) NOT IN ( SELECT e, f FROM t3 ); +SELECT * FROM t1 WHERE ( a, b ) NOT IN ( SELECT e, f FROM t3 ); + +--replace_column 1 x 3 x 4 x 5 x 6 x 7 x 8 x 9 x 10 x +EXPLAIN +SELECT * FROM t2 WHERE ( c, d ) NOT IN ( SELECT a, b FROM t1 ); +SELECT * FROM t2 WHERE ( c, d ) NOT IN ( SELECT a, b FROM t1 ); + +--replace_column 1 x 3 x 4 x 5 x 6 x 7 x 8 x 9 x 10 x +EXPLAIN +SELECT * FROM t3 WHERE ( e, f ) NOT IN ( SELECT c, d FROM t2 ); +SELECT * FROM t3 WHERE ( e, f ) NOT IN ( SELECT c, d FROM t2 ); + +--replace_column 1 x 3 x 4 x 5 x 6 x 7 x 8 x 9 x 10 x +EXPLAIN +SELECT * FROM t2 WHERE ( c, d ) NOT IN ( SELECT e, f FROM t3 ); +SELECT * FROM t2 WHERE ( c, d ) NOT IN ( SELECT e, f FROM t3 ); + +SELECT * FROM t1 WHERE ( a, b ) NOT IN + ( SELECT c, d FROM t2 WHERE c = 1 AND c <> 1 ); + +SELECT * FROM t1 WHERE b NOT IN ( SELECT c FROM t2 WHERE c = 1 ); + +SELECT * FROM t1 WHERE NULL NOT IN ( SELECT c FROM t2 WHERE c = 1 AND c <> 1 ); + +DROP TABLE t1, t2, t3, t4, t5; + --echo # --echo # End of 5.1 tests. === modified file 'sql/item_cmpfunc.cc' --- a/sql/item_cmpfunc.cc 2011-01-14 08:54:47 +0000 +++ b/sql/item_cmpfunc.cc 2011-03-24 09:27:05 +0000 @@ -1752,6 +1752,76 @@ bool Item_in_optimizer::fix_fields(THD * } +/** + The implementation of optimized \ [NOT] IN \ + predicates. The implementation works as follows. + + For the current value of the outer expression + + - If it contains only NULL values, the original (before rewrite by the + Item_in_subselect rewrite methods) inner subquery is non-correlated and + was previously executed, there is no need to re-execute it, and the + previous return value is returned. + + - If it contains NULL values, check if there is a partial match for the + inner query block by evaluating it. For clarity we repeat here the + transformation previously performed on the sub-query. The expression + + + ( oc_1, ..., oc_n ) + \ + ( SELECT ic_1, ..., ic_n + FROM \ + WHERE \ + ) + + + was transformed into + + + ( oc_1, ..., oc_n ) + \ + ( SELECT ic_1, ..., ic_n + FROM \ + WHERE \ AND ... ( ic_k = oc_k OR ic_k IS NULL ) + HAVING ... NOT ic_k IS NULL + ) + + + The evaluation will now proceed according to special rules set up + elsewhere. These rules include: + + - The HAVING NOT \ IS NULL conditions added by the + aforementioned rewrite methods will detect whether they evaluated (and + rejected) a NULL value and if so, will cause the subquery to evaluate + to NULL. + + - The added WHERE and HAVING conditions are present only for those inner + columns that correspond to outer column that are not NULL at the moment. + + - If there is an eligible index for executing the subquery, the special + access method "Full scan on NULL key" is employed which ensures that + the inner query will detect if there are NULL values resulting from the + inner query. This access method will quietly resort to table scan if it + needs to find NULL values as well. + + - Under these conditions, the sub-query need only be evaluated in order to + find out whether it produced any rows. + + - If it did, we know that there was a partial match since there are + NULL values in the outer row expression. + + - If it did not, the result is FALSE or UNKNOWN. If at least one of the + HAVING sub-predicates rejected a NULL value corresponding to an outer + non-NULL, and hence the inner query block returns UNKNOWN upon + evaluation, there was a partial match and the result is UNKNOWN. + + - If it contains no NULL values, the call is forwarded to the inner query + block. + + @see Item_in_subselect::val_bool() + @see Item_is_not_null_test::val_int() + */ longlong Item_in_optimizer::val_int() { bool tmp; @@ -1805,7 +1875,7 @@ longlong Item_in_optimizer::val_int() all_left_cols_null= false; } - if (!((Item_in_subselect*)args[1])->is_correlated && + if (!item_subs->is_correlated && all_left_cols_null && result_for_null_param != UNKNOWN) { /* @@ -1819,8 +1889,11 @@ longlong Item_in_optimizer::val_int() else { /* The subquery has to be evaluated */ - (void) args[1]->val_bool_result(); - null_value= !item_subs->engine->no_rows(); + (void) item_subs->val_bool_result(); + if (item_subs->engine->no_rows()) + null_value= item_subs->null_value; + else + null_value= TRUE; if (all_left_cols_null) result_for_null_param= null_value; } === modified file 'sql/item_subselect.cc' --- a/sql/item_subselect.cc 2011-01-14 13:36:47 +0000 +++ b/sql/item_subselect.cc 2011-03-24 09:27:05 +0000 @@ -50,7 +50,7 @@ Item_subselect::Item_subselect(): item value is NULL if select_subselect not changed this value (i.e. some rows will be found returned) */ - null_value= 1; + null_value= TRUE; } @@ -430,9 +430,9 @@ void Item_maxmin_subselect::print(String void Item_singlerow_subselect::reset() { - null_value= 1; + null_value= TRUE; if (value) - value->null_value= 1; + value->null_value= TRUE; } @@ -577,7 +577,7 @@ double Item_singlerow_subselect::val_rea DBUG_ASSERT(fixed == 1); if (!exec() && !value->null_value) { - null_value= 0; + null_value= FALSE; return value->val_real(); } else @@ -592,7 +592,7 @@ longlong Item_singlerow_subselect::val_i DBUG_ASSERT(fixed == 1); if (!exec() && !value->null_value) { - null_value= 0; + null_value= FALSE; return value->val_int(); } else @@ -606,7 +606,7 @@ String *Item_singlerow_subselect::val_st { if (!exec() && !value->null_value) { - null_value= 0; + null_value= FALSE; return value->val_str(str); } else @@ -621,7 +621,7 @@ my_decimal *Item_singlerow_subselect::va { if (!exec() && !value->null_value) { - null_value= 0; + null_value= FALSE; return value->val_decimal(decimal_value); } else @@ -636,7 +636,7 @@ bool Item_singlerow_subselect::val_bool( { if (!exec() && !value->null_value) { - null_value= 0; + null_value= FALSE; return value->val_bool(); } else @@ -654,7 +654,7 @@ Item_exists_subselect::Item_exists_subse bool val_bool(); init(select_lex, new select_exists_subselect(this)); max_columns= UINT_MAX; - null_value= 0; //can't be NULL + null_value= FALSE; //can't be NULL maybe_null= 0; //can't be NULL value= 0; DBUG_VOID_RETURN; @@ -817,15 +817,14 @@ double Item_in_subselect::val_real() */ DBUG_ASSERT(0); DBUG_ASSERT(fixed == 1); - null_value= 0; + null_value= was_null= FALSE; if (exec()) { reset(); - null_value= 1; return 0; } if (was_null && !value) - null_value= 1; + null_value= TRUE; return (double) value; } @@ -838,15 +837,14 @@ longlong Item_in_subselect::val_int() */ DBUG_ASSERT(0); DBUG_ASSERT(fixed == 1); - null_value= 0; + null_value= was_null= FALSE; if (exec()) { reset(); - null_value= 1; return 0; } if (was_null && !value) - null_value= 1; + null_value= TRUE; return value; } @@ -859,16 +857,15 @@ String *Item_in_subselect::val_str(Strin */ DBUG_ASSERT(0); DBUG_ASSERT(fixed == 1); - null_value= 0; + null_value= was_null= FALSE; if (exec()) { reset(); - null_value= 1; return 0; } if (was_null && !value) { - null_value= 1; + null_value= TRUE; return 0; } str->set((ulonglong)value, &my_charset_bin); @@ -879,20 +876,14 @@ String *Item_in_subselect::val_str(Strin bool Item_in_subselect::val_bool() { DBUG_ASSERT(fixed == 1); - null_value= 0; + null_value= was_null= FALSE; if (exec()) { reset(); - /* - Must mark the IN predicate as NULL so as to make sure an enclosing NOT - predicate will return FALSE. See the comments in - subselect_uniquesubquery_engine::copy_ref_key for further details. - */ - null_value= 1; return 0; } if (was_null && !value) - null_value= 1; + null_value= TRUE; return value; } @@ -903,16 +894,15 @@ my_decimal *Item_in_subselect::val_decim method should not be used */ DBUG_ASSERT(0); - null_value= 0; + null_value= was_null= FALSE; DBUG_ASSERT(fixed == 1); if (exec()) { reset(); - null_value= 1; return 0; } if (was_null && !value) - null_value= 1; + null_value= TRUE; int2my_decimal(E_DEC_FATAL_ERROR, value, 0, decimal_value); return decimal_value; } No bundle (reason: useless for push emails).