BUG#920713: Wrong result (missing rows) with firstmatch+BNL, IN subquery, ...

- Disable use of join cache when we're using FirstMatch strategy, and the join
  order is such that subquery's inner tables are interleaved with outer.  Join 
  buffering code is incapable of handling such join orders.

- The testcase requires use of @@debug_optimizer_prefer_join_prefix to hit the bug, 
  but I'm pushing it anyway (including the mention of the variable in .test file), 
  so that it can be found and enabled when/if we get something comparable in the 
  main tree.

1 files changed, 15 insertions, 4 deletions

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: