Hi Timour, How does this relate to fix for lp:1008773? In this bug, you've added:

+ if (parsing_place != SELECT_LIST) + return;

and in that one:

+ if (const_item()) + return;

How will the fixes work together? Generally, I'm wondering if we're taking the right approach here: from an out-of-context view, it seems that approach "equip every Item with ability to evaluate to NULL under some special circumnstances" is very confusing, and one should just ignore the non-group-by items and produce NULLs instead.. Let's discuss it on irc. On Wed, Jun 13, 2012 at 08:52:19AM +0000, timour@askmonty.org wrote:

At file:///home/tsk/mprog/src/5.3/

------------------------------------------------------------ revno: 3543 revision-id: timour@askmonty.org-20120613085100-1a3zlttyiebfvzuy parent: psergey@askmonty.org-20120610100611-0l0qf7ov53h1elr9 fixes bug(s): https://launchpad.net/bugs/1008686 committer: timour@askmonty.org branch nick: 5.3 timestamp: Wed 2012-06-13 11:51:00 +0300 message: Fix bug lp:1008686

Analysis: The fix for bug lp:985667 implements the method Item_subselect::no_rows_in_result() for all main kinds of subqueries. The purpose of this method is to be called from return_zero_rows() and set Items to some default value in the case when a query returns no rows. Aggregates and subqueries require special treatment in this case.

Every implementation of Item_subselect::no_rows_in_result() called Item_subselect::make_const() to set the subquery predicate to its default value irrespective of where the predicate was located in the query. Once the predicate was set to a constant it was never executed.

At the same time, the JOIN object of the fake select for UNIONs (the one used for the final result of the UNION), was set after all subqueries in the union were executed. Since we set the subquery as constant, it was never executed, and the corresponding JOIN was never created.

In order to decide whether the result of NOT IN is NULL or FALSE, Item_in_optimizer needs to check if the subquery result was empty or not. This is where we got the crash, because subselect_union_engine::no_rows() checks for unit->fake_select_lex->join->send_records, and the join object was NULL.

Solution: If a subquery is in the HAVING clause it must be evaluated in order to know its result, so that we can properly filter the result records. Once subqueries in the HAVING clause are executed even in the case of no result rows, this specific crash will be solved, because the UNION will be executed, and its JOIN will be constructed. Therefore the fix for this crash is to narrow the fix for lp:985667, and to apply Item_subselect::no_rows_in_result() only when the subquery predicate is in the SELECT clause.

=== modified file 'mysql-test/r/subselect.result' --- a/mysql-test/r/subselect.result 2012-06-05 14:25:10 +0000 +++ b/mysql-test/r/subselect.result 2012-06-13 08:51:00 +0000 @@ -6112,5 +6112,30 @@ NULL UNION RESULT <union2,3> ALL NULL NU SELECT SUM(a) AS f1, a AS f2 FROM (t1, t2) HAVING f2 >= ALL (SELECT 4 UNION SELECT 5) AND f1 = 7; f1 f2 drop table t1,t2; +# +# LP BUG#1008686 Server crashes in subselect_union_engine::no_rows on SELECT with impossible +# WHERE and UNION in HAVING +# +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1),(7); +EXPLAIN +SELECT MIN(a) AS min_a, a FROM t1 WHERE 0 HAVING a NOT IN ( SELECT 2 UNION SELECT 5 ) OR min_a != 1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used +3 DEPENDENT UNION NULL NULL NULL NULL NULL NULL NULL No tables used +NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL +SELECT MIN(a) AS min_a, a FROM t1 WHERE 0 HAVING a NOT IN ( SELECT 2 UNION SELECT 5 ) OR min_a != 1; +min_a a +EXPLAIN +SELECT MIN(a) AS min_a, a FROM t1 WHERE 1=2 HAVING a NOT IN ( SELECT a from t1 UNION select a+1 from t1 ) OR min_a != 1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using where +3 DEPENDENT UNION t1 ALL NULL NULL NULL NULL 2 Using where +NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL +SELECT MIN(a) AS min_a, a FROM t1 WHERE 1=2 HAVING a NOT IN ( SELECT a from t1 UNION select a+1 from t1 ) OR min_a != 1; +min_a a +drop table t1; # return optimizer switch changed in the beginning of this test set optimizer_switch=@subselect_tmp;

=== modified file 'mysql-test/t/subselect.test' --- a/mysql-test/t/subselect.test 2012-05-22 12:22:55 +0000 +++ b/mysql-test/t/subselect.test 2012-06-13 08:51:00 +0000 @@ -5190,5 +5190,23 @@ SELECT SUM(a) AS f1, a AS f2 FROM (t1, t

drop table t1,t2;

+--echo # +--echo # LP BUG#1008686 Server crashes in subselect_union_engine::no_rows on SELECT with impossible +--echo # WHERE and UNION in HAVING +--echo # + +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1),(7); + +EXPLAIN +SELECT MIN(a) AS min_a, a FROM t1 WHERE 0 HAVING a NOT IN ( SELECT 2 UNION SELECT 5 ) OR min_a != 1; +SELECT MIN(a) AS min_a, a FROM t1 WHERE 0 HAVING a NOT IN ( SELECT 2 UNION SELECT 5 ) OR min_a != 1; + +EXPLAIN +SELECT MIN(a) AS min_a, a FROM t1 WHERE 1=2 HAVING a NOT IN ( SELECT a from t1 UNION select a+1 from t1 ) OR min_a != 1; +SELECT MIN(a) AS min_a, a FROM t1 WHERE 1=2 HAVING a NOT IN ( SELECT a from t1 UNION select a+1 from t1 ) OR min_a != 1; + +drop table t1; + --echo # return optimizer switch changed in the beginning of this test set optimizer_switch=@subselect_tmp;

=== modified file 'sql/item_subselect.cc' --- a/sql/item_subselect.cc 2012-05-22 12:22:55 +0000 +++ b/sql/item_subselect.cc 2012-06-13 08:51:00 +0000 @@ -892,6 +892,8 @@ void Item_maxmin_subselect::print(String

void Item_maxmin_subselect::no_rows_in_result() { + if (parsing_place != SELECT_LIST) + return; value= Item_cache::get_cache(new Item_null()); null_value= 0; was_values= 0; @@ -901,6 +903,8 @@ void Item_maxmin_subselect::no_rows_in_r

void Item_singlerow_subselect::no_rows_in_result() { + if (parsing_place != SELECT_LIST) + return; value= Item_cache::get_cache(new Item_null()); reset(); make_const(); @@ -1363,6 +1367,8 @@ Item* Item_exists_subselect::expr_cache_

void Item_exists_subselect::no_rows_in_result() { + if (parsing_place != SELECT_LIST) + return; value= 0; null_value= 0; make_const(); @@ -2707,6 +2713,8 @@ void Item_allany_subselect::print(String

void Item_allany_subselect::no_rows_in_result() { + if (parsing_place != SELECT_LIST) + return; value= 0; null_value= 0; was_null= 0;

_______________________________________________ commits mailing list commits@mariadb.org https://lists.askmonty.org/cgi-bin/mailman/listinfo/commits

-- BR Sergei -- Sergei Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog