Re: bzr commit into mysql-5.0-bugteam branch (gshchepa:2662) Bug#37894

[Sergey Petrunia]

Hi!

On Sat, Sep 20, 2008 at 02:16:15AM +0500, Gleb Shchepa wrote:
> #At file:///work/bzr/5.0-bugteam-37894/
> 
>  2662 Gleb Shchepa	2008-09-20
>       Bug #37894: Assertion in init_read_record_seq in handler.h line 1444
>       
>       Select with a "NULL NOT IN" condition containing complex
>       subselect from the same table as in the outer select failed
>       with an assertion.
>       
>       
>       The failure was caused by a concatenation of circumstances:
>       1) an inner select was optimized by make_join_statistics to use
>          the QUICK_RANGE_SELECT access method (that implies an index
>          scan of the table);
>       2) a subselect was independent (constant) from the outer select;
>       3) a condition was pushed down into inner select.
>       
>       During the evaluation of a constant IN expression an optimizer
>       temporary changed the access method from index scan to table
>       scan, but an engine handler was already initialized for index
>       access by make_join_statistics. That caused an assertion.
>       
>       
>       The subselect_single_select_engine::exec method has been
>       modified to substitute a read_record engine handler with
>       its clone for further table scan.
> modified:
>   mysql-test/r/subselect3.result
>   mysql-test/t/subselect3.test
>   sql/item_subselect.cc
>   sql/sql_select.h
> 
> per-file messages:
>   mysql-test/r/subselect3.result
>     Added test case for bug #37894.
>   mysql-test/t/subselect3.test
>     Added test case for bug #37894.
>   sql/item_subselect.cc
>     The subselect_single_select_engine::exec method has been
>     modified to substitute a read_record engine handler with
>     its clone for further table scan.
>   sql/sql_select.h
>     The JOIN_TAB::save_file field has been added to save

> === modified file 'sql/item_subselect.cc'
> --- a/sql/item_subselect.cc	2008-03-28 11:31:52 +0000
> +++ b/sql/item_subselect.cc	2008-09-19 21:16:05 +0000
> @@ -1858,6 +1858,18 @@ int subselect_single_select_engine::exec
>              bool *cond_guard= tab->ref.cond_guards[i];
>              if (cond_guard && !*cond_guard)
>              {
> +              if (tab->table->file->inited != handler::NONE)
> +              {
> +                DBUG_ASSERT(!tab->save_file);
> +                tab->save_file= tab->read_record.file;
> +                if (!(tab->read_record.file=
> tab->table->file->clone(thd->mem_root)))
> +                {
> +                  thd->net.report_error= 1;
> +                  DBUG_RETURN(1);
> +                }
> +                if (tab->read_record.file->ha_external_lock(thd, F_RDLCK))
> +                  DBUG_RETURN(1);
> +              }
The problem is that handler->clone() is an expensive operation, it is too
expensive to be called for every record combination of the outer select. So
the most apparent thing to do is to create only one extra handler object in
course of the query execution.

More, I believe that there is no need to have additional handler object at
all. It should be easy to examine handler->inited, handler->active_index,
save their state and restore it at the end of the scan, please do so.
(If it's not that trivial then I'm missing something, please let me know).

>                /* Change the access method to full table scan */
>                tab->save_read_first_record= tab->read_first_record;
>                tab->save_read_record= tab->read_record.read_record;
> @@ -1879,6 +1891,14 @@ int subselect_single_select_engine::exec
>      for (JOIN_TAB **ptab= changed_tabs; ptab != last_changed_tab; ptab++)
>      {
>        JOIN_TAB *tab= *ptab;
> +      if (tab->save_file)
> +      {
> +        if (tab->read_record.file->ha_external_lock(thd, F_UNLCK) ||
> +            tab->read_record.file->close())
> +          DBUG_RETURN(1);
> +        tab->read_record.file= tab->save_file;
> +        tab->save_file= 0;
> +      }
>        tab->read_record.record= 0;
>        tab->read_record.ref_length= 0;
>        tab->read_first_record= tab->save_read_first_record; 

BR
 Sergey
-- 
Sergey Petrunia, Lead Software Engineer
MySQL AB, www.mysql.com
Office: N/A
Blog: http://s.petrunia.net/blog