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, [email protected] wrote: > At file:///home/tsk/mprog/src/5.3/ > > ------------------------------------------------------------ > revno: 3543 > revision-id: [email protected] > parent: [email protected] > fixes bug(s): https://launchpad.net/bugs/1008686 > committer: [email protected] > 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 > [email protected] > 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 _______________________________________________ Mailing list: https://launchpad.net/~maria-developers Post to : [email protected] Unsubscribe : https://launchpad.net/~maria-developers More help : https://help.launchpad.net/ListHelp
