summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--mysql-test/r/subselect_sj.result27
-rw-r--r--mysql-test/r/subselect_sj_jcl6.result27
-rw-r--r--mysql-test/t/subselect_sj.test17
-rw-r--r--sql/opt_subselect.cc19
-rw-r--r--sql/sql_select.cc7
-rw-r--r--sql/sql_select.h7
6 files changed, 99 insertions, 5 deletions
diff --git a/mysql-test/r/subselect_sj.result b/mysql-test/r/subselect_sj.result
index 9c335be4c89..0784b429052 100644
--- a/mysql-test/r/subselect_sj.result
+++ b/mysql-test/r/subselect_sj.result
@@ -2509,4 +2509,31 @@ a b d
8 4 2
5 5 5
DROP TABLE t1, t2;
+#
+# BUG#920713: Wrong result (missing rows) with firstmatch+BNL, IN subquery, ...
+#
+CREATE TABLE t1 ( a VARCHAR(1) ) ENGINE=MyISAM;
+INSERT INTO t1 VALUES ('e'),('w'),('a'),('h'),('x'),('k'),('g');
+CREATE TABLE t2 ( b INT, c VARCHAR(1) );
+INSERT INTO t2 VALUES (0,'j'),(8,'v');
+SELECT * FROM t1 alias1, t2 alias2
+WHERE alias2.c IN (
+SELECT alias4.c FROM t1 alias3, t2 alias4
+);
+a b c
+e 0 j
+e 8 v
+w 0 j
+w 8 v
+a 0 j
+a 8 v
+h 0 j
+h 8 v
+x 0 j
+x 8 v
+k 0 j
+k 8 v
+g 0 j
+g 8 v
+DROP TABLE t1, t2;
set optimizer_switch=@subselect_sj_tmp;
diff --git a/mysql-test/r/subselect_sj_jcl6.result b/mysql-test/r/subselect_sj_jcl6.result
index af7c3484b1f..b970a01c94e 100644
--- a/mysql-test/r/subselect_sj_jcl6.result
+++ b/mysql-test/r/subselect_sj_jcl6.result
@@ -2523,6 +2523,33 @@ a b d
10 2 1
10 2 1
DROP TABLE t1, t2;
+#
+# BUG#920713: Wrong result (missing rows) with firstmatch+BNL, IN subquery, ...
+#
+CREATE TABLE t1 ( a VARCHAR(1) ) ENGINE=MyISAM;
+INSERT INTO t1 VALUES ('e'),('w'),('a'),('h'),('x'),('k'),('g');
+CREATE TABLE t2 ( b INT, c VARCHAR(1) );
+INSERT INTO t2 VALUES (0,'j'),(8,'v');
+SELECT * FROM t1 alias1, t2 alias2
+WHERE alias2.c IN (
+SELECT alias4.c FROM t1 alias3, t2 alias4
+);
+a b c
+e 0 j
+e 8 v
+w 0 j
+w 8 v
+a 0 j
+a 8 v
+h 0 j
+h 8 v
+x 0 j
+x 8 v
+k 0 j
+k 8 v
+g 0 j
+g 8 v
+DROP TABLE t1, t2;
set optimizer_switch=@subselect_sj_tmp;
#
# BUG#49129: Wrong result with IN-subquery with join_cache_level=6 and firstmatch=off
diff --git a/mysql-test/t/subselect_sj.test b/mysql-test/t/subselect_sj.test
index 65d74419dbe..b8275f72604 100644
--- a/mysql-test/t/subselect_sj.test
+++ b/mysql-test/t/subselect_sj.test
@@ -2252,6 +2252,23 @@ WHERE ( b, d ) IN
DROP TABLE t1, t2;
+--echo #
+--echo # BUG#920713: Wrong result (missing rows) with firstmatch+BNL, IN subquery, ...
+--echo #
+# t1 should be MyISAM or InnoDB
+CREATE TABLE t1 ( a VARCHAR(1) ) ENGINE=MyISAM;
+INSERT INTO t1 VALUES ('e'),('w'),('a'),('h'),('x'),('k'),('g');
+CREATE TABLE t2 ( b INT, c VARCHAR(1) );
+INSERT INTO t2 VALUES (0,'j'),(8,'v');
+
+#SET debug_optimizer_prefer_join_prefix= 'alias2,alias4,alias1,alias3';
+
+SELECT * FROM t1 alias1, t2 alias2
+WHERE alias2.c IN (
+ SELECT alias4.c FROM t1 alias3, t2 alias4
+);
+
+DROP TABLE t1, t2;
# The following command must be the last one the file
set optimizer_switch=@subselect_sj_tmp;
diff --git a/sql/opt_subselect.cc b/sql/opt_subselect.cc
index 63a90891720..75490482d93 100644
--- a/sql/opt_subselect.cc
+++ b/sql/opt_subselect.cc
@@ -4079,7 +4079,8 @@ int setup_semijoin_dups_elimination(JOIN *join, ulonglong options,
{
uint i;
DBUG_ENTER("setup_semijoin_dups_elimination");
-
+
+ join->complex_firstmatch_tables= table_map(0);
POSITION *pos= join->best_positions + join->const_tables;
for (i= join->const_tables ; i < join->top_join_tab_count; )
@@ -4165,8 +4166,13 @@ int setup_semijoin_dups_elimination(JOIN *join, ulonglong options,
{
JOIN_TAB *j;
JOIN_TAB *jump_to= tab-1;
+
+ bool complex_range= FALSE;
+ table_map tables_in_range= table_map(0);
+
for (j= tab; j != tab + pos->n_sj_tables; j++)
{
+ tables_in_range |= j->table->map;
if (!j->emb_sj_nest)
{
/*
@@ -4176,11 +4182,12 @@ int setup_semijoin_dups_elimination(JOIN *join, ulonglong options,
SELECT * FROM ot1, nt1 WHERE ot1.col IN (SELECT expr FROM it1, it2)
with a join order of
-
- ot1 it1 nt1 nt2
+ +----- FirstMatch range ----+
+ | |
+ ot1 it1 nt1 nt2 it2 it3 ...
| ^
- | +-------- 'j' point here
+ | +-------- 'j' points here
+------------- SJ_OPT_FIRST_MATCH was set for this table as
it's the first one that produces duplicates
@@ -4195,6 +4202,7 @@ int setup_semijoin_dups_elimination(JOIN *join, ulonglong options,
j[-1].do_firstmatch= jump_to;
jump_to= j; /* Jump back to us */
+ complex_range= TRUE;
}
else
{
@@ -4205,6 +4213,9 @@ int setup_semijoin_dups_elimination(JOIN *join, ulonglong options,
j[-1].do_firstmatch= jump_to;
i+= pos->n_sj_tables;
pos+= pos->n_sj_tables;
+
+ if (complex_range)
+ join->complex_firstmatch_tables|= tables_in_range;
break;
}
case SJ_OPT_NONE:
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 3991fff6960..9d03cdd55a2 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -9153,6 +9153,9 @@ uint check_join_cache_usage(JOIN_TAB *tab,
if (tab->use_quick == 2)
goto no_join_cache;
+
+ if (tab->table->map & join->complex_firstmatch_tables)
+ goto no_join_cache;
/*
Don't use join cache if we're inside a join tab range covered by LooseScan
@@ -9363,7 +9366,7 @@ void check_join_cache_usage_for_tables(JOIN *join, ulonglong options,
{
tab->used_join_cache_level= join->max_allowed_join_cache_level;
}
-
+
uint idx= join->const_tables;
for (tab= first_linear_tab(join, WITHOUT_CONST_TABLES);
tab;
@@ -9448,6 +9451,8 @@ make_join_readinfo(JOIN *join, ulonglong options, uint no_jbuf_after)
bool statistics= test(!(join->select_options & SELECT_DESCRIBE));
bool sorted= 1;
+ join->complex_firstmatch_tables= table_map(0);
+
if (!join->select_lex->sj_nests.is_empty() &&
setup_semijoin_dups_elimination(join, options, no_jbuf_after))
DBUG_RETURN(TRUE); /* purecov: inspected */
diff --git a/sql/sql_select.h b/sql/sql_select.h
index 185bf90ea17..002c5a2df5a 100644
--- a/sql/sql_select.h
+++ b/sql/sql_select.h
@@ -995,6 +995,13 @@ public:
/* We also maintain a stack of join optimization states in * join->positions[] */
/******* Join optimization state members end *******/
+
+ /*
+ Tables within complex firstmatch ranges (i.e. those where inner tables are
+ interleaved with outer tables). Join buffering cannot be used for these.
+ */
+ table_map complex_firstmatch_tables;
+
/*
The cost of best complete join plan found so far during optimization,
after optimization phase - cost of picked join order (not taking into
View Lorry Controller Status