MySQL Lists are EOL. Please join:

List:Commits« Previous MessageNext Message »
From:Evgeny Potemkin Date:December 17 2009 12:40pm
Subject:Re: bzr commit into mysql-6.0-codebase-bugfixing branch
(roy.lyseng:3760) Bug#38075
View as plain text  
Hi Roy,

The fix is ok. Please re-commit corrected version and I'll approve it.

Regards, Evgen.

Roy Lyseng wrote:
> #At file:///home/rl136806/mysql/repo/mysql-38075/ based on
> revid:zhenxing.he@stripped
> 
>  3760 Roy Lyseng	2009-12-08
>       Bug#38075: Wrong result: rows matching a subquery with outer join not returned
>       
>       The problem is that duplicate weedout handling in evaluate_join_record() is
>       incompatible with outer-join condition handling.
>       Duplicate weedout checking was attempted when the initial predicate was
>       successfully evaluated, but before the conditional predicates were evaluated.
AFAIU it was evaluated, but isn't taken into account.
>       Hence, all the row combinations involving the first row of the innermost
>       table that qualified according to the initial predicate would be candidates
>       for duplicate elimination. This would prevent all row combinations where the
>       first row to qualify from the innermost table was not the first row where the
>       initial predicate evaluated to true.
>       Here, "initial predicate" refers to join_tab->select_cond and "conditional
>       predicates" refers to select_cond of "unmatched" join_tabs.
>       
>       Solved by moving the duplicate elimination to after the point in the function
>       where a complete outer-join predicate has been evaluated.
The fix isn't about moving, but about taking into account already evaluated 
"conditional predicates".
>       
>       The fix also eliminates one boolean local variable and adds a function header.
>       
>       mysql-test/r/subselect_sj2.result
>         Added test result for bug#38075.
>       
>       mysql-test/r/subselect_sj2_jcl6.result
>         Added test result for bug#38075.
>       
>       mysql-test/t/subselect_sj2.test
>         Added test for bug#38075.
>       
>       sql/sql_select.cc
>         Moved the duplicate elimination code so that it is evaluated after the
>         outer-join condition is fully evaluated.
>         Eliminated a boolean variable (select_cond_result).
>         Added Doxygen style function header for evaluate_join_record().
> 
>     modified:
>       mysql-test/r/subselect_sj2.result
>       mysql-test/r/subselect_sj2_jcl6.result
>       mysql-test/t/subselect_sj2.test
>       sql/sql_select.cc
> === modified file 'mysql-test/r/subselect_sj2.result'
> --- a/mysql-test/r/subselect_sj2.result	2009-11-24 11:24:39 +0000
> +++ b/mysql-test/r/subselect_sj2.result	2009-12-08 09:51:13 +0000
> @@ -705,3 +705,75 @@ id	select_type	table	type	possible_keys	
>  1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	1	Using where
>  1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	FirstMatch(t2)
>  drop table t2, t3;
> +# 
> +# BUG#38075: Wrong result: rows matching a subquery with outer join not returned
> +# 
> +DROP TABLE IF EXISTS ot1, it1, it2;
> +CREATE TABLE it2 (
> +int_nokey int(11) NOT NULL,
> +int_key int(11) NOT NULL,
> +datetime_key datetime NOT NULL,
> +varchar_nokey varchar(1) NOT NULL,
> +KEY int_key (int_key),
> +KEY datetime_key (datetime_key)
> +) DEFAULT CHARSET=latin1;
> +INSERT INTO it2 VALUES
> +(7,5,'2002-04-10 14:25:30','w'),(7,0,'0000-00-00 00:00:00','s'),
> +(4,0,'2006-09-14 04:01:02','y'),(0,4,'0000-00-00 00:00:00','c'),
> +(1,8,'0000-00-00 00:00:00','q'),(6,5,'0000-00-00 00:00:00',''),
> +(2,9,'0000-00-00 00:00:00','d'),(6,8,'2007-04-01 11:04:17',''),
> +(0,1,'0000-00-00 00:00:00','p'),(4,7,'2009-01-12 00:00:00','x'),
> +(4,0,'2009-06-05 00:00:00','f'),(7,3,'2006-02-14 18:06:35','x'),
> +(3,5,'2006-02-21 07:08:16','h'),(7,0,'0000-00-00 00:00:00','c'),
> +(8,7,'0000-00-00 00:00:00','m'),(4,0,'0000-00-00 00:00:00','s'),
> +(6,0,'2007-02-13 00:00:00','b'),(9,1,'0000-00-00 00:00:00','o'),
> +(0,0,'0000-00-00 00:00:00','j'),(9,1,'2003-08-11 00:00:00','m');
> +CREATE TABLE ot1 (
> +int_nokey int(11) NOT NULL,
> +int_key int(11) NOT NULL,
> +datetime_key datetime NOT NULL,
> +varchar_nokey varchar(1) NOT NULL,
> +KEY int_key (int_key)
> +) DEFAULT CHARSET=latin1;
> +INSERT INTO ot1 VALUES
> +(5,0,'0000-00-00 00:00:00','k'),(3,0,'2008-04-19 07:51:37','a'),
> +(0,2,'2006-06-03 00:00:00',''),(3,0,'0000-00-00 00:00:00','u'),
> +(1,3,'2000-10-03 15:17:43','e'),(0,0,'2009-04-25 16:10:46','v'),
> +(1,7,'2005-01-11 03:31:23','i'),(7,0,'0000-00-00 00:00:00','t'),
> +(1,7,'2000-03-07 00:00:00','u'),(0,7,'2001-06-14 20:33:16','f'),
> +(0,9,'2005-03-06 05:45:38','u'),(8,2,'0000-00-00 00:00:00','m'),
> +(4,4,'0000-00-00 00:00:00','j'),(9,3,'2002-02-13 21:59:10','f'),
> +(0,9,'0000-00-00 00:00:00','v'),(2,5,'0000-00-00 00:00:00','j'),
> +(0,5,'2001-12-23 00:00:00','g'),(8,0,'2004-05-16 00:00:00','e'),
> +(5,8,'2004-04-02 00:00:00','h'),(1,5,'2003-11-26 00:00:00','z');
> +CREATE TABLE it1 (
> +int_nokey int(11) NOT NULL,
> +int_key int(11) NOT NULL,
> +datetime_key datetime NOT NULL,
> +varchar_nokey varchar(1) NOT NULL,
> +KEY int_key (int_key)
> +) DEFAULT CHARSET=latin1;
> +INSERT INTO it1 VALUES
> +(9,5,'2005-03-17 13:58:09','i'),(0,4,'0000-00-00 00:00:00','t');
> +SELECT int_key FROM ot1
> +WHERE int_nokey IN (SELECT it2.int_key
> +FROM it1 LEFT JOIN it2 ON it2.datetime_key);
> +int_key
> +0
> +0
> +2
> +0
> +3
> +0
> +7
> +0
> +7
> +7
> +9
> +2
> +9
> +5
> +0
> +8
> +5
> +DROP TABLE ot1, it1, it2;
> 
> === modified file 'mysql-test/r/subselect_sj2_jcl6.result'
> --- a/mysql-test/r/subselect_sj2_jcl6.result	2009-11-24 11:24:39 +0000
> +++ b/mysql-test/r/subselect_sj2_jcl6.result	2009-12-08 09:51:13 +0000
> @@ -709,6 +709,78 @@ id	select_type	table	type	possible_keys	
>  1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	1	Using where
>  1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	FirstMatch(t2)
>  drop table t2, t3;
> +# 
> +# BUG#38075: Wrong result: rows matching a subquery with outer join not returned
> +# 
> +DROP TABLE IF EXISTS ot1, it1, it2;
> +CREATE TABLE it2 (
> +int_nokey int(11) NOT NULL,
> +int_key int(11) NOT NULL,
> +datetime_key datetime NOT NULL,
> +varchar_nokey varchar(1) NOT NULL,
> +KEY int_key (int_key),
> +KEY datetime_key (datetime_key)
> +) DEFAULT CHARSET=latin1;
> +INSERT INTO it2 VALUES
> +(7,5,'2002-04-10 14:25:30','w'),(7,0,'0000-00-00 00:00:00','s'),
> +(4,0,'2006-09-14 04:01:02','y'),(0,4,'0000-00-00 00:00:00','c'),
> +(1,8,'0000-00-00 00:00:00','q'),(6,5,'0000-00-00 00:00:00',''),
> +(2,9,'0000-00-00 00:00:00','d'),(6,8,'2007-04-01 11:04:17',''),
> +(0,1,'0000-00-00 00:00:00','p'),(4,7,'2009-01-12 00:00:00','x'),
> +(4,0,'2009-06-05 00:00:00','f'),(7,3,'2006-02-14 18:06:35','x'),
> +(3,5,'2006-02-21 07:08:16','h'),(7,0,'0000-00-00 00:00:00','c'),
> +(8,7,'0000-00-00 00:00:00','m'),(4,0,'0000-00-00 00:00:00','s'),
> +(6,0,'2007-02-13 00:00:00','b'),(9,1,'0000-00-00 00:00:00','o'),
> +(0,0,'0000-00-00 00:00:00','j'),(9,1,'2003-08-11 00:00:00','m');
> +CREATE TABLE ot1 (
> +int_nokey int(11) NOT NULL,
> +int_key int(11) NOT NULL,
> +datetime_key datetime NOT NULL,
> +varchar_nokey varchar(1) NOT NULL,
> +KEY int_key (int_key)
> +) DEFAULT CHARSET=latin1;
> +INSERT INTO ot1 VALUES
> +(5,0,'0000-00-00 00:00:00','k'),(3,0,'2008-04-19 07:51:37','a'),
> +(0,2,'2006-06-03 00:00:00',''),(3,0,'0000-00-00 00:00:00','u'),
> +(1,3,'2000-10-03 15:17:43','e'),(0,0,'2009-04-25 16:10:46','v'),
> +(1,7,'2005-01-11 03:31:23','i'),(7,0,'0000-00-00 00:00:00','t'),
> +(1,7,'2000-03-07 00:00:00','u'),(0,7,'2001-06-14 20:33:16','f'),
> +(0,9,'2005-03-06 05:45:38','u'),(8,2,'0000-00-00 00:00:00','m'),
> +(4,4,'0000-00-00 00:00:00','j'),(9,3,'2002-02-13 21:59:10','f'),
> +(0,9,'0000-00-00 00:00:00','v'),(2,5,'0000-00-00 00:00:00','j'),
> +(0,5,'2001-12-23 00:00:00','g'),(8,0,'2004-05-16 00:00:00','e'),
> +(5,8,'2004-04-02 00:00:00','h'),(1,5,'2003-11-26 00:00:00','z');
> +CREATE TABLE it1 (
> +int_nokey int(11) NOT NULL,
> +int_key int(11) NOT NULL,
> +datetime_key datetime NOT NULL,
> +varchar_nokey varchar(1) NOT NULL,
> +KEY int_key (int_key)
> +) DEFAULT CHARSET=latin1;
> +INSERT INTO it1 VALUES
> +(9,5,'2005-03-17 13:58:09','i'),(0,4,'0000-00-00 00:00:00','t');
> +SELECT int_key FROM ot1
> +WHERE int_nokey IN (SELECT it2.int_key
> +FROM it1 LEFT JOIN it2 ON it2.datetime_key);
> +int_key
> +0
> +8
> +2
> +0
> +7
> +9
> +9
> +5
> +2
> +0
> +0
> +0
> +0
> +3
> +7
> +7
> +5
> +DROP TABLE ot1, it1, it2;
>  set join_cache_level=default;
>  show variables like 'join_cache_level';
>  Variable_name	Value
> 
> === modified file 'mysql-test/t/subselect_sj2.test'
> --- a/mysql-test/t/subselect_sj2.test	2009-10-22 10:45:44 +0000
> +++ b/mysql-test/t/subselect_sj2.test	2009-12-08 09:51:13 +0000
> @@ -884,3 +884,63 @@ explain select 1 from t2 where 
>    c1 in (select convert(c6,char(1)) from t2);
>  drop table t2, t3;
>  
> +--echo # 
> +--echo # BUG#38075: Wrong result: rows matching a subquery with outer join not
> returned
> +--echo # 
> +
> +--disable_warnings
> +DROP TABLE IF EXISTS ot1, it1, it2;
> +--enable_warnings
> +
> +CREATE TABLE it2 (
> +  int_nokey int(11) NOT NULL,
> +  int_key int(11) NOT NULL,
> +  datetime_key datetime NOT NULL,
> +  varchar_nokey varchar(1) NOT NULL,
> +  KEY int_key (int_key),
> +  KEY datetime_key (datetime_key)
> +) DEFAULT CHARSET=latin1;
> +INSERT INTO it2 VALUES
> +  (7,5,'2002-04-10 14:25:30','w'),(7,0,'0000-00-00 00:00:00','s'),
> +  (4,0,'2006-09-14 04:01:02','y'),(0,4,'0000-00-00 00:00:00','c'),
> +  (1,8,'0000-00-00 00:00:00','q'),(6,5,'0000-00-00 00:00:00',''),
> +  (2,9,'0000-00-00 00:00:00','d'),(6,8,'2007-04-01 11:04:17',''),
> +  (0,1,'0000-00-00 00:00:00','p'),(4,7,'2009-01-12 00:00:00','x'),
> +  (4,0,'2009-06-05 00:00:00','f'),(7,3,'2006-02-14 18:06:35','x'),
> +  (3,5,'2006-02-21 07:08:16','h'),(7,0,'0000-00-00 00:00:00','c'),
> +  (8,7,'0000-00-00 00:00:00','m'),(4,0,'0000-00-00 00:00:00','s'),
> +  (6,0,'2007-02-13 00:00:00','b'),(9,1,'0000-00-00 00:00:00','o'),
> +  (0,0,'0000-00-00 00:00:00','j'),(9,1,'2003-08-11 00:00:00','m');
> +CREATE TABLE ot1 (
> +  int_nokey int(11) NOT NULL,
> +  int_key int(11) NOT NULL,
> +  datetime_key datetime NOT NULL,
> +  varchar_nokey varchar(1) NOT NULL,
> +  KEY int_key (int_key)
> +) DEFAULT CHARSET=latin1;
> +INSERT INTO ot1 VALUES
> +  (5,0,'0000-00-00 00:00:00','k'),(3,0,'2008-04-19 07:51:37','a'),
> +  (0,2,'2006-06-03 00:00:00',''),(3,0,'0000-00-00 00:00:00','u'),
> +  (1,3,'2000-10-03 15:17:43','e'),(0,0,'2009-04-25 16:10:46','v'),
> +  (1,7,'2005-01-11 03:31:23','i'),(7,0,'0000-00-00 00:00:00','t'),
> +  (1,7,'2000-03-07 00:00:00','u'),(0,7,'2001-06-14 20:33:16','f'),
> +  (0,9,'2005-03-06 05:45:38','u'),(8,2,'0000-00-00 00:00:00','m'),
> +  (4,4,'0000-00-00 00:00:00','j'),(9,3,'2002-02-13 21:59:10','f'),
> +  (0,9,'0000-00-00 00:00:00','v'),(2,5,'0000-00-00 00:00:00','j'),
> +  (0,5,'2001-12-23 00:00:00','g'),(8,0,'2004-05-16 00:00:00','e'),
> +  (5,8,'2004-04-02 00:00:00','h'),(1,5,'2003-11-26 00:00:00','z');
> +CREATE TABLE it1 (
> +  int_nokey int(11) NOT NULL,
> +  int_key int(11) NOT NULL,
> +  datetime_key datetime NOT NULL,
> +  varchar_nokey varchar(1) NOT NULL,
> +  KEY int_key (int_key)
> +) DEFAULT CHARSET=latin1;
> +INSERT INTO it1 VALUES
> +  (9,5,'2005-03-17 13:58:09','i'),(0,4,'0000-00-00 00:00:00','t');
Please, remove any stuff which isn't used in this test case (fields, keys)
> +
> +SELECT int_key FROM ot1
> +WHERE int_nokey IN (SELECT it2.int_key
> +                    FROM it1 LEFT JOIN it2 ON it2.datetime_key);
It's worth adding an EXPLAIN query to make sure that the plan isn't changed 
after a bug fix and this case still tests what it should.
> +
> +DROP TABLE ot1, it1, it2;
> 
> === modified file 'sql/sql_select.cc'
> --- a/sql/sql_select.cc	2009-12-02 13:21:27 +0000
> +++ b/sql/sql_select.cc	2009-12-08 09:51:13 +0000
> @@ -16653,12 +16653,19 @@ int do_sj_reset(SJ_TMP_TABLE *sj_tbl)
>    DBUG_RETURN(0);
>  }
>  
> -/*
> -  Process one record of the nested loop join.
> +/**
> +  @brief Process one row of the nested loop join.
>  
> -    This function will evaluate parts of WHERE/ON clauses that are
> -    applicable to the partial record on hand and in case of success
> -    submit this record to the next level of the nested loop.
> +  This function will evaluate parts of WHERE/ON clauses that are
> +  applicable to the partial row on hand and in case of success
> +  submit this row to the next level of the nested loop.
> +
> +  @param  join     - The join object
> +  @param  join_tab - The most inner join_tab being processed
> +  @param  error > 0: Error, terminate processing
> +                = 0: (Partial) row is available
> +                < 0: No more rows available at this level
> +  @return Nested loop state (Ok, No_more_rows, Error, Killed)
>  */
>  
>  static enum_nested_loop_state
> @@ -16668,7 +16675,7 @@ evaluate_join_record(JOIN *join, JOIN_TA
>    bool not_used_in_distinct=join_tab->not_used_in_distinct;
>    ha_rows found_records=join->found_records;
>    COND *select_cond= join_tab->select_cond;
> -  bool select_cond_result= TRUE;
> +  bool found= TRUE;
>  
>    DBUG_ENTER("evaluate_join_record");
>    DBUG_PRINT("enter",
> @@ -16685,20 +16692,18 @@ evaluate_join_record(JOIN *join, JOIN_TA
>    }
>    if (select_cond)
>    {
> -    select_cond_result= test(select_cond->val_int());
> +    found= test(select_cond->val_int());
>  
>      /* check for errors evaluating the condition */
>      if (join->thd->is_error())
>        DBUG_RETURN(NESTED_LOOP_ERROR);
>    }
> -
> -  if (!select_cond || select_cond_result)
> +  if (found)
>    {
>      /*
>        There is no select condition or the attached pushed down
>        condition is true => a match is found.
>      */
> -    bool found= 1;
>      while (join_tab->first_unmatched && found)
>      {
>        /*
> @@ -16750,15 +16755,7 @@ evaluate_join_record(JOIN *join, JOIN_TA
>  
>      JOIN_TAB *return_tab= join->return_tab;
>      join_tab->found_match= TRUE;
> -    if (join_tab->check_weed_out_table)
> -    {
> -      int res= do_sj_dups_weedout(join->thd, join_tab->check_weed_out_table);
> -      if (res == -1)
> -        DBUG_RETURN(NESTED_LOOP_ERROR);
> -      if (res == 1)
> -        DBUG_RETURN(NESTED_LOOP_OK);
> -    }
> -    else if (join_tab->do_firstmatch)
> +    if (join_tab->do_firstmatch)
>      {
>        /* 
>          We should return to the join_tab->do_firstmatch after we have 
> @@ -16776,6 +16773,14 @@ evaluate_join_record(JOIN *join, JOIN_TA
>      DBUG_PRINT("counts", ("evaluate_join_record join->examined_rows++: %lu",
>                            (ulong) join->examined_rows));
>  
> +    if (join_tab->check_weed_out_table && found)
Basically, the only real change is adding the "&& found" part. Moving this block 
back and forth doesn't add any value. Thus, please, move it back.
> +    {
> +      int res= do_sj_dups_weedout(join->thd, join_tab->check_weed_out_table);
> +      if (res == -1)
> +        DBUG_RETURN(NESTED_LOOP_ERROR);
> +      else if (res == 1)
> +        found= FALSE;
> +    }
>      if (found)
>      {
>        enum enum_nested_loop_state rc;
> 
> 
> 
> ------------------------------------------------------------------------
> 
> 
Thread
bzr commit into mysql-6.0-codebase-bugfixing branch (roy.lyseng:3760)Bug#38075Roy Lyseng8 Dec
  • Re: bzr commit into mysql-6.0-codebase-bugfixing branch(roy.lyseng:3760) Bug#38075Evgeny Potemkin17 Dec
    • Re: bzr commit into mysql-6.0-codebase-bugfixing branch(roy.lyseng:3760) Bug#38075Roy Lyseng17 Dec
    • Re: bzr commit into mysql-6.0-codebase-bugfixing branch(roy.lyseng:3760) Bug#38075Roy Lyseng21 Dec
  • Re: bzr commit into mysql-6.0-codebase-bugfixing branch(roy.lyseng:3760) Bug#38075Øystein Grøvlen17 Dec
    • Re: bzr commit into mysql-6.0-codebase-bugfixing branch(roy.lyseng:3760) Bug#38075Roy Lyseng21 Dec