#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.
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 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');
+
+SELECT int_key FROM ot1
+WHERE int_nokey IN (SELECT it2.int_key
+ FROM it1 LEFT JOIN it2 ON it2.datetime_key);
+
+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)
+ {
+ 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;
Attachment: [text/bzr-bundle] bzr/roy.lyseng@sun.com-20091208095113-vlzal08uglcwckfw.bundle
