#At file:///home/rl136806/mysql/repo/mysql-next-mr-opt-backporting/ based on revid:oystein.grovlen@stripped
3138 Roy Lyseng 2010-05-06
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 checked only the initial predicate, but not the conditional predicates.
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 prevented 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 considering the "found" boolean when performing duplicate elimination.
Notice also that duplicate elimination now sets "found" instead of exiting
from routine. This ensures correct book-keeping of e.g examined_rows.
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
Considers outer-join condition before executing duplicate elimination code.
Eliminated a boolean variable (select_cond_result).
Added Doxygen style function header for evaluate_join_record().
original revid: -c3719.1.70
modified:
mysql-test/r/subselect_sj.result
mysql-test/r/subselect_sj_jcl6.result
mysql-test/t/subselect_sj.test
sql/sql_select.cc
=== modified file 'mysql-test/r/subselect_sj.result'
--- a/mysql-test/r/subselect_sj.result 2010-05-03 12:19:23 +0000
+++ b/mysql-test/r/subselect_sj.result 2010-05-06 14:42:54 +0000
@@ -1121,3 +1121,70 @@ COUNT(*)
1
DROP TABLE t1, t2, t3;
# End of Bug#51487
+#
+# BUG#38075: Wrong result: rows matching a subquery with outer join not returned
+#
+DROP TABLE IF EXISTS ot1, it1, it2;
+CREATE TABLE it2 (
+int_key int(11) NOT NULL,
+datetime_key datetime NOT NULL,
+KEY int_key (int_key),
+KEY datetime_key (datetime_key)
+);
+INSERT INTO it2 VALUES
+(5,'2002-04-10 14:25:30'), (0,'0000-00-00 00:00:00'),
+(0,'2006-09-14 04:01:02'), (4,'0000-00-00 00:00:00'),
+(8,'0000-00-00 00:00:00'), (5,'0000-00-00 00:00:00'),
+(9,'0000-00-00 00:00:00'), (8,'2007-04-01 11:04:17'),
+(1,'0000-00-00 00:00:00'), (7,'2009-01-12 00:00:00'),
+(0,'2009-06-05 00:00:00'), (3,'2006-02-14 18:06:35'),
+(5,'2006-02-21 07:08:16'), (0,'0000-00-00 00:00:00'),
+(7,'0000-00-00 00:00:00'), (0,'0000-00-00 00:00:00'),
+(0,'2007-02-13 00:00:00'), (1,'0000-00-00 00:00:00'),
+(0,'0000-00-00 00:00:00'), (1,'2003-08-11 00:00:00');
+CREATE TABLE ot1 (
+int_nokey int(11) NOT NULL,
+int_key int(11) NOT NULL,
+KEY int_key (int_key)
+);
+INSERT INTO ot1 VALUES
+(5,0), (3,0), (0,2), (3,0), (1,3), (0,0), (1,7), (7,0), (1,7), (0,7),
+(0,9), (8,2), (4,4), (9,3), (0,9), (2,5), (0,5), (8,0), (5,8), (1,5);
+CREATE TABLE it1 (
+int_nokey int(11) NOT NULL,
+int_key int(11) NOT NULL,
+KEY int_key (int_key)
+);
+INSERT INTO it1 VALUES
+(9,5), (0,4);
+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
+0
+0
+0
+0
+2
+2
+3
+5
+5
+7
+7
+7
+8
+9
+9
+EXPLAIN
+SELECT int_key FROM ot1
+WHERE int_nokey IN (SELECT it2.int_key
+FROM it1 LEFT JOIN it2 ON it2.datetime_key);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY it1 index NULL int_key 4 NULL 2 Using index; Start temporary
+1 PRIMARY ot1 ALL NULL NULL NULL NULL 20 Using join buffer
+1 PRIMARY it2 ALL NULL NULL NULL NULL 20 Using where; End temporary
+DROP TABLE ot1, it1, it2;
+# End of BUG#38075
=== modified file 'mysql-test/r/subselect_sj_jcl6.result'
--- a/mysql-test/r/subselect_sj_jcl6.result 2010-05-02 19:14:50 +0000
+++ b/mysql-test/r/subselect_sj_jcl6.result 2010-05-06 14:42:54 +0000
@@ -1125,6 +1125,73 @@ COUNT(*)
1
DROP TABLE t1, t2, t3;
# End of Bug#51487
+#
+# BUG#38075: Wrong result: rows matching a subquery with outer join not returned
+#
+DROP TABLE IF EXISTS ot1, it1, it2;
+CREATE TABLE it2 (
+int_key int(11) NOT NULL,
+datetime_key datetime NOT NULL,
+KEY int_key (int_key),
+KEY datetime_key (datetime_key)
+);
+INSERT INTO it2 VALUES
+(5,'2002-04-10 14:25:30'), (0,'0000-00-00 00:00:00'),
+(0,'2006-09-14 04:01:02'), (4,'0000-00-00 00:00:00'),
+(8,'0000-00-00 00:00:00'), (5,'0000-00-00 00:00:00'),
+(9,'0000-00-00 00:00:00'), (8,'2007-04-01 11:04:17'),
+(1,'0000-00-00 00:00:00'), (7,'2009-01-12 00:00:00'),
+(0,'2009-06-05 00:00:00'), (3,'2006-02-14 18:06:35'),
+(5,'2006-02-21 07:08:16'), (0,'0000-00-00 00:00:00'),
+(7,'0000-00-00 00:00:00'), (0,'0000-00-00 00:00:00'),
+(0,'2007-02-13 00:00:00'), (1,'0000-00-00 00:00:00'),
+(0,'0000-00-00 00:00:00'), (1,'2003-08-11 00:00:00');
+CREATE TABLE ot1 (
+int_nokey int(11) NOT NULL,
+int_key int(11) NOT NULL,
+KEY int_key (int_key)
+);
+INSERT INTO ot1 VALUES
+(5,0), (3,0), (0,2), (3,0), (1,3), (0,0), (1,7), (7,0), (1,7), (0,7),
+(0,9), (8,2), (4,4), (9,3), (0,9), (2,5), (0,5), (8,0), (5,8), (1,5);
+CREATE TABLE it1 (
+int_nokey int(11) NOT NULL,
+int_key int(11) NOT NULL,
+KEY int_key (int_key)
+);
+INSERT INTO it1 VALUES
+(9,5), (0,4);
+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
+0
+0
+0
+0
+2
+2
+3
+5
+5
+7
+7
+7
+8
+9
+9
+EXPLAIN
+SELECT int_key FROM ot1
+WHERE int_nokey IN (SELECT it2.int_key
+FROM it1 LEFT JOIN it2 ON it2.datetime_key);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY it1 index NULL int_key 4 NULL 2 Using index; Start temporary
+1 PRIMARY ot1 ALL NULL NULL NULL NULL 20 Using join buffer
+1 PRIMARY it2 ALL NULL NULL NULL NULL 20 Using where; End temporary; Using join buffer
+DROP TABLE ot1, it1, it2;
+# End of BUG#38075
set join_cache_level=default;
show variables like 'join_cache_level';
Variable_name Value
=== modified file 'mysql-test/t/subselect_sj.test'
--- a/mysql-test/t/subselect_sj.test 2010-04-15 11:23:42 +0000
+++ b/mysql-test/t/subselect_sj.test 2010-05-06 14:42:54 +0000
@@ -816,3 +816,54 @@ DROP TABLE t1, t2, t3;
--echo # End of Bug#51487
+--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_key int(11) NOT NULL,
+ datetime_key datetime NOT NULL,
+ KEY int_key (int_key),
+ KEY datetime_key (datetime_key)
+);
+INSERT INTO it2 VALUES
+ (5,'2002-04-10 14:25:30'), (0,'0000-00-00 00:00:00'),
+ (0,'2006-09-14 04:01:02'), (4,'0000-00-00 00:00:00'),
+ (8,'0000-00-00 00:00:00'), (5,'0000-00-00 00:00:00'),
+ (9,'0000-00-00 00:00:00'), (8,'2007-04-01 11:04:17'),
+ (1,'0000-00-00 00:00:00'), (7,'2009-01-12 00:00:00'),
+ (0,'2009-06-05 00:00:00'), (3,'2006-02-14 18:06:35'),
+ (5,'2006-02-21 07:08:16'), (0,'0000-00-00 00:00:00'),
+ (7,'0000-00-00 00:00:00'), (0,'0000-00-00 00:00:00'),
+ (0,'2007-02-13 00:00:00'), (1,'0000-00-00 00:00:00'),
+ (0,'0000-00-00 00:00:00'), (1,'2003-08-11 00:00:00');
+CREATE TABLE ot1 (
+ int_nokey int(11) NOT NULL,
+ int_key int(11) NOT NULL,
+ KEY int_key (int_key)
+);
+INSERT INTO ot1 VALUES
+ (5,0), (3,0), (0,2), (3,0), (1,3), (0,0), (1,7), (7,0), (1,7), (0,7),
+ (0,9), (8,2), (4,4), (9,3), (0,9), (2,5), (0,5), (8,0), (5,8), (1,5);
+CREATE TABLE it1 (
+ int_nokey int(11) NOT NULL,
+ int_key int(11) NOT NULL,
+ KEY int_key (int_key)
+);
+INSERT INTO it1 VALUES
+ (9,5), (0,4);
+--sorted_result
+SELECT int_key FROM ot1
+WHERE int_nokey IN (SELECT it2.int_key
+ FROM it1 LEFT JOIN it2 ON it2.datetime_key);
+EXPLAIN
+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;
+
+--echo # End of BUG#38075
=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc 2010-05-06 13:50:15 +0000
+++ b/sql/sql_select.cc 2010-05-06 14:42:54 +0000
@@ -16579,11 +16579,18 @@ int do_sj_reset(SJ_TMP_TABLE *sj_tbl)
}
/**
- 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
@@ -16593,7 +16600,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");
@@ -16610,20 +16617,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)
{
/*
@@ -16675,13 +16680,14 @@ 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)
+
+ 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);
- if (res == 1)
- DBUG_RETURN(NESTED_LOOP_OK);
+ else if (res == 1)
+ found= FALSE;
}
else if (join_tab->do_firstmatch)
{
Attachment: [text/bzr-bundle] bzr/roy.lyseng@sun.com-20100506144254-uuq5pedv1pr9dc3p.bundle
| Thread |
|---|
| • bzr commit into mysql-next-mr-bugfixing branch (roy.lyseng:3138) Bug#38075 | Roy Lyseng | 6 May |
