maria-developers team mailing list archive

[Sergei Petrunia <psergey@xxxxxxxxxxxx>]

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@xxxxxxxxxxxx wrote:
> At file:///home/tsk/mprog/src/5.3/
> 
> ------------------------------------------------------------
> revno: 3543
> revision-id: timour@xxxxxxxxxxxx-20120613085100-1a3zlttyiebfvzuy
> parent: psergey@xxxxxxxxxxxx-20120610100611-0l0qf7ov53h1elr9
> fixes bug(s): https://launchpad.net/bugs/1008686
> committer: timour@xxxxxxxxxxxx
> 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@xxxxxxxxxxx
> 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