List:Commits« Previous MessageNext Message »
From:Roy Lyseng Date:November 1 2010 3:05pm
Subject:Re: bzr commit into mysql-next-mr-bugfixing branch (roy.lyseng:3270)
Bug#52068
View as plain text  
Thank you Øystein.

On 01.11.10 15.54, Øystein Grøvlen wrote:
> Hi Roy,
>
> Patch looks good. Approved. I have one minor suggestion below.
>
> On 10/29/10 04:57 PM, Roy Lyseng wrote:
>  > #At file:///home/rl136806/mysql/repo/mysql-work5/ based on
> revid:roy.lyseng@stripped
>  >
>  > 3270 Roy Lyseng 2010-10-29
>  > Bug#52068: Optimizer generates invalid semijoin materialization plan
>  >
>  > When MaterializeScan semijoin strategy was used and there were one
>  > or more outer dependent tables before the semijoin tables, the scan
>  > over the materialized table was not properly reset for each row of
>  > the prefix outer tables.
>  >
>  > Example: suppose we have a join order:
>  >
>  > ot1 SJ-Mat-Scan(it2 it3) ot4
>  >
>  > Notice that this is called a MaterializeScan, even though there is an
>  > outer table ahead of the materialized tables. Usually a MaterializeScan
>  > has the outer tables after the materialized table, but this is
>  > a special (but legal) case with outer dependent tables both before and
>  > after the materialized table.
>  >
>  > For each qualifying row from ot1, a new scan over the materialized
>  > table must be set up. The code failed to do that, so all scans after
>  > the first one returned zero rows from the materialized table.
>  >
>  > mysql-test/include/subquery_sj.inc
>  > Bug#52068: Optimizer generates invalid semijoin materialization plan
>  > Added new test.
>  >
>  > mysql-test/r/subquery_sj_all.result
>  > mysql-test/r/subquery_sj_all_jcl6.result
>  > mysql-test/r/subquery_sj_all_jcl7.result
>  > mysql-test/r/subquery_sj_dupsweed.result
>  > mysql-test/r/subquery_sj_dupsweed_jcl6.result
>  > mysql-test/r/subquery_sj_dupsweed_jcl7.result
>  > mysql-test/r/subquery_sj_firstmatch.result
>  > mysql-test/r/subquery_sj_firstmatch_jcl6.result
>  > mysql-test/r/subquery_sj_firstmatch_jcl7.result
>  > mysql-test/r/subquery_sj_loosescan.result
>  > mysql-test/r/subquery_sj_loosescan_jcl6.result
>  > mysql-test/r/subquery_sj_loosescan_jcl7.result
>  > mysql-test/r/subquery_sj_mat.result
>  > mysql-test/r/subquery_sj_mat_jcl6.result
>  > mysql-test/r/subquery_sj_mat_jcl7.result
>  > mysql-test/r/subquery_sj_mat_nosj.result
>  > mysql-test/r/subquery_sj_none.result
>  > mysql-test/r/subquery_sj_none_jcl6.result
>  > mysql-test/r/subquery_sj_none_jcl7.result
>  > Bug#52068: Optimizer generates invalid semijoin materialization plan
>  > Earlier wrong semijoin materialization test results are corrected.
>  >
>  > sql/sql_select.cc
>  > Bug#52068: Optimizer generates invalid semijoin materialization plan
>  >
>  > In sub_select_sjm(), added code that resets the cursor over the
>  > materialized table when more than one scan is needed.
>  >
>  > modified:
>  > mysql-test/include/subquery_sj.inc
>  > mysql-test/r/subquery_sj_all.result
>  > mysql-test/r/subquery_sj_all_jcl6.result
>  > mysql-test/r/subquery_sj_all_jcl7.result
>  > mysql-test/r/subquery_sj_dupsweed.result
>  > mysql-test/r/subquery_sj_dupsweed_jcl6.result
>  > mysql-test/r/subquery_sj_dupsweed_jcl7.result
>  > mysql-test/r/subquery_sj_firstmatch.result
>  > mysql-test/r/subquery_sj_firstmatch_jcl6.result
>  > mysql-test/r/subquery_sj_firstmatch_jcl7.result
>  > mysql-test/r/subquery_sj_loosescan.result
>  > mysql-test/r/subquery_sj_loosescan_jcl6.result
>  > mysql-test/r/subquery_sj_loosescan_jcl7.result
>  > mysql-test/r/subquery_sj_mat.result
>  > mysql-test/r/subquery_sj_mat_jcl6.result
>  > mysql-test/r/subquery_sj_mat_jcl7.result
>  > mysql-test/r/subquery_sj_mat_nosj.result
>  > mysql-test/r/subquery_sj_none.result
>  > mysql-test/r/subquery_sj_none_jcl6.result
>  > mysql-test/r/subquery_sj_none_jcl7.result
>  > sql/sql_select.cc
>
> ...
>
>  > === modified file 'sql/sql_select.cc'
>  > --- a/sql/sql_select.cc 2010-10-26 10:43:50 +0000
>  > +++ b/sql/sql_select.cc 2010-10-29 14:56:12 +0000
>  > @@ -16970,6 +16970,15 @@ sub_select_sjm(JOIN *join, JOIN_TAB *joi
>  > last_tab->read_record.read_record= rr_sequential_and_unpack;
>  > }
>  > }
>  > + else
>  > + {
>  > + if (sjm->is_scan)
>  > + {
>  > + /* Reset the cursor for a new scan over the table */
>  > + if (sjm->table->file->ha_rnd_init(TRUE))
>  > + DBUG_RETURN(NESTED_LOOP_ERROR);
>  > + }
>  > + }
>
> An alternative formatting is
>
> else if (sjm->is_scan)
> {
> /* Reset the cursor for a new scan over the table */
> if (sjm->table->file->ha_rnd_init(TRUE))
> DBUG_RETURN(NESTED_LOOP_ERROR);
> }
>

I did this deliberately to have the test symmetrical with the above
"if (sjm->is_scan)" test. So I prefer to have it the way it is...

Roy
Thread
bzr commit into mysql-next-mr-bugfixing branch (roy.lyseng:3270) Bug#52068Roy Lyseng26 Oct
  • Re: bzr commit into mysql-next-mr-bugfixing branch (roy.lyseng:3270)Bug#52068Øystein Grøvlen28 Oct
    • Re: bzr commit into mysql-next-mr-bugfixing branch (roy.lyseng:3270)Bug#52068Roy Lyseng29 Oct
Re: bzr commit into mysql-next-mr-bugfixing branch (roy.lyseng:3270)Bug#52068Roy Lyseng1 Nov