Bug #50361 Doublenested noncorrelated subquery with FirstMatch and join cache wrong result
Submitted: 15 Jan 2010 11:35 Modified: 23 Nov 2010 3:14
Reporter: Roy Lyseng Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:6.0 OS:Any
Assigned to: Guilhem Bichot CPU Architecture:Any
Tags: firstmatch, join_cache_level, optimizer_switch, semijoin, subquery

[15 Jan 2010 11:35] Roy Lyseng
Description:
A double-nested, non-correlated IN subquery fails when using the FirstMatch strategy and join_cache_level is set to 1. Duplicates from the tables in the subquery are not removed, making the subquery look like a full join of the three tables.

The equivalent four-table query fails in the same way.

The equivalent EXISTS query fails also, when semijoin conversion is enabled (see WL#4389).

How to repeat:
CREATE TABLE t1(
  id INTEGER PRIMARY KEY,
  u  INTEGER NOT NULL UNIQUE,
  v  INTEGER NOT NULL,
  vi INTEGER NOT NULL, INDEX(vi),
  s  CHAR(1));

INSERT INTO t1 VALUES(10, 10, 10, 10, 'l');
INSERT INTO t1 VALUES(20, 20, 20, 20, 'l');

set optimizer_switch='semijoin=on,firstmatch=on';
set join_cache_level=1;

SELECT *
FROM t1
WHERE 1 IN(SELECT 1
             FROM t1
             WHERE 1 IN(SELECT 1
                          FROM t1));
set join_cache_level=0;
SELECT *
FROM t1
WHERE 1 IN(SELECT 1
             FROM t1
             WHERE 1 IN(SELECT 1
                          FROM t1));
[15 Jan 2010 16:54] Valeriy Kravchuk
Verified just as described:

77-52-1-11:6.0-codebase openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 6.0.14-alpha-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> drop table t1;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE t1(
    ->   id INTEGER PRIMARY KEY,
    ->   u  INTEGER NOT NULL UNIQUE,
    ->   v  INTEGER NOT NULL,
    ->   vi INTEGER NOT NULL, INDEX(vi),
    ->   s  CHAR(1));
Query OK, 0 rows affected (0.06 sec)

mysql> 
mysql> INSERT INTO t1 VALUES(10, 10, 10, 10, 'l');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t1 VALUES(20, 20, 20, 20, 'l');
Query OK, 1 row affected (0.00 sec)

mysql> set optimizer_switch='semijoin=on,firstmatch=on';
Query OK, 0 rows affected (0.00 sec)

mysql> set join_cache_level=1;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT *
    -> FROM t1
    -> WHERE 1 IN(SELECT 1
    ->              FROM t1
    ->              WHERE 1 IN(SELECT 1
    ->                           FROM t1));
+----+----+----+----+------+
| id | u  | v  | vi | s    |
+----+----+----+----+------+
| 10 | 10 | 10 | 10 | l    |
| 20 | 20 | 20 | 20 | l    |
| 10 | 10 | 10 | 10 | l    |
| 20 | 20 | 20 | 20 | l    |
| 10 | 10 | 10 | 10 | l    |
| 20 | 20 | 20 | 20 | l    |
| 10 | 10 | 10 | 10 | l    |
| 20 | 20 | 20 | 20 | l    |
+----+----+----+----+------+
8 rows in set (0.02 sec)

mysql> set join_cache_level=0;Query OK, 0 rows affected (0.00 sec)

mysql> SELECT *
    -> FROM t1
    -> WHERE 1 IN(SELECT 1
    ->              FROM t1
    ->              WHERE 1 IN(SELECT 1
    ->                           FROM t1));
+----+----+----+----+------+
| id | u  | v  | vi | s    |
+----+----+----+----+------+
| 10 | 10 | 10 | 10 | l    |
| 20 | 20 | 20 | 20 | l    |
+----+----+----+----+------+
2 rows in set (0.00 sec)
[1 Feb 2010 15:41] Guilhem Bichot
To Roy: what query do you mean exactly by "The equivalent four-table query fails in the same way", please?
[1 Feb 2010 15:48] Roy Lyseng
The equivalent four-table query mentioned above:

SELECT *
FROM t1
WHERE 1 IN(SELECT 1
           FROM t1
           WHERE 1 IN(SELECT 1
                      FROM t1
                      WHERE 1 IN(SELECT 1
                                 FROM t1)));
[2 Feb 2010 15:37] Guilhem Bichot
I cannot reproduce the problem with the four-table query.
But I can reproduce it with the 3-table query:
SELECT *
FROM t1
WHERE 1 IN(SELECT 1
FROM t1
WHERE 1 IN(SELECT 1
FROM t1));
and this is a regression introduced by

epotemkin@mysql.com-20091022104544-fuuzdrsh4ya9fi36
with comment:
BUG#42742: crash in setup_sj_materialization, Copy_field::set
- If a semi-join strategy covers certain [first_table; last_table] 
  range in join order, do reset the sj_strategy member for all tables
  within the range, except the first one.
  Failure to do so caused EXPLAIN/execution code to try applying two 
  strategies at once which would cause all kinds of undesired effects.
[3 Feb 2010 7:50] Roy Lyseng
When the "four-table query" is run on a table with 9 rows in it, I get 6561 (or 9*9*9*9) rows as output. When setting optimizer_join_cache_level=0, the correct number of rows (9) is returned.

When the table contains 2 rows only (as in the original bug case), the correct number of rows is reported also for the four-table query.
[3 Feb 2010 10:49] Guilhem Bichot
Thanks Roy. Indeed if the table has 9 rows:

INSERT INTO t1 VALUES(10, 10, 10, 10, 'l');
INSERT INTO t1 VALUES(20, 20, 20, 20, 'l');
INSERT INTO t1 VALUES(30, 30, 30, 30, 'l');
INSERT INTO t1 VALUES(40, 40, 40, 40, 'l');
INSERT INTO t1 VALUES(50, 50, 50, 50, 'l');
INSERT INTO t1 VALUES(60, 60, 60, 60, 'l');
INSERT INTO t1 VALUES(70, 70, 70, 70, 'l');
INSERT INTO t1 VALUES(80, 80, 80, 80, 'l');
INSERT INTO t1 VALUES(90, 90, 90, 90, 'l');
I see the problem with the four-table query. And it is introduced by the same revision as above.
[8 Feb 2010 20:39] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/99637

3873 Guilhem Bichot	2010-02-08
      Fix for BUG#50361 "Doublenested noncorrelated subquery with FirstMatch and join cache wrong result".
      According to comments in check_join_cache_usage() ("Use join cache with FirstMatch semi-join strategy
      only when semi-join contains only one table", see BUG45191), we should not do join-caching for
      T1 SJ (T2 SJ T3), which is alas the scenario of this bug. The tests in join_cache_usage()
      didn't recognize that the semi-join had more than one inner table, so didn't disable join cache,
      which gave wrong results. Here is how it happened (see also comments added to code):
      a) check_join_cache_usage() knows whether there is only one sj-inner table by reading
      JOIN_TAB::(first|last)_sj_inner_tab (look at start of the function), let's look at how those variables are set.
      b) in the case of "SELECT * FROM t1 WHERE 1 IN (SELECT * FROM t2)",
      when we reach the for() loop of the patched code, "tab" is t2 (the inner table, tab->emb_sj_nest != NULL)
      c) in the case of "SELECT * FROM t1 WHERE 1 IN (SELECT * FROM t2 WHERE 1 IN (SELECT * FROM t3))",
      when we reach the for() loop, "tab" is t1 (the outer table, tab->emb_sj_nest==0) and pos->n_sj_tables is 3
      (loop over t1 and t2 and t3).
      d) in case (b), we take the else{} branch which sets up (first|last)_sj_inner_tab properly
      e) in case (c), we never take this branch (because tab->emb_sj_nest==NULL), so (first|last)_sj_inner_tab
      remain unset, check_join_cache_usage() is thus under-informed and makes a wrong decision.
      Notice how (b) and (c) are inconsistent, but this is not fixed by the patch, we only fix proper setting
      of variables, which was wrong in the case of (c).
      The bugfix is only the change in the if()'s condition, but other parts are changed too because they looked
      suspicious:
      - the body of if() is changed, because if there could be two sj-outer tables (is that possible?),
      on first match we should jump back to the second ("j") and not the first ("tab"), and also when jump_to
      is used in the else{} branch, first_sj_inner_tab should be the third (=inner table) and not the second
      (outer) table
      - the else{} branch is changed, because in the test's scenario, the first sj-inner table is not "tab" (t1)
      but "tab+1".
      Notice how we fix the same code as BUG45191 had fixed.
[12 Feb 2010 13:36] Guilhem Bichot
Oystein suggests that the for(tab) loop which I'm changing, should not loop over outer tables, so I'll work on this.
[23 Feb 2010 20:54] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/101255

3873 Guilhem Bichot	2010-02-23
      Fix for BUG#50361 "Doublenested noncorrelated subquery with FirstMatch and join cache wrong result":
      See commit comment of sql_select.cc for more details.
     @ mysql-test/r/subselect4.result
        result. Without the bugfix, SELECTs would generate extra rows. If only the assertion is added, it fires.
     @ mysql-test/t/subselect4.test
        test for the bug. Multiple tables instead of one are used to make it easier to distinguish
        tables in EXPLAIN.
     @ sql/sql_select.cc
        An assertion is added to make sure that first|last_sj_inner_tab is not left unset by
        setup_semijoin_dups_elimination() for any semijoin inner table participating in a firstmatch range.
        With the testcase of BUG#50361, tables do not have their first|last_sj_inner_tab set (which leads
        to various wrong results in SELECT). That is because "tab" (first table in
        the for() loop) is t1. This isn't normal, an outer table should not be seen in this loop, because, as
        documented in setup_semijoin_dups_elimination(), a firstmatch duplicate-generating range starts with
        an inner table (abbreviated "it").
        Here is the path which leads to this.
        - Semijoin transformation rewrites the query as "t1 SJ (t2, t3)"
        - To prepare for materialization, optimize_semijoin_nests() is fed an initial Query Execution Plan (t2,t3,t1)
        (semijoin inner tables first, see the first lines of choose_plan()), and ordered to create a partial Query
        Execution Plan for t2, t3. It produces plan (t2,t3), with t3 said to use firstmatch. It sounds unnecessary for
        advance_sj_state() to pick semijoin strategies at this stage, but it does not know that it is at this stage, so
        that's how it is.
        - Then choose_plan() runs for the top-level query: picks (t1, t2, t3) and decides to use materialization, marks t3
        accordingly with SJ_OPT_MATERIALIZE
        - Then fix_semijoin_strategies_for_picked_join_order() runs: for the first iteration, it is on t3, sees that it
        should use materialization, so copies the partial QEP saved by optimize_semijoin_nests() onto the POSITION slots of
        t2 and t3. This imports certain information from the partial QEP which has two effects:
          * it sets sj_strategy to SJ_OPT_FIRSTMATCH for t3 instead of SJ_OPT_MATERIALIZE
          * it sets first_firstmatch_table to "0" for t3; in the partial QEP, index "0" was t2, but here index "0" is t1
        - Because the "if (pos->sj_strategy == SJ_OPT_FIRST_MATCH)" in the function is not "else if", we enter
        it, so set the table at index first_firstmatch_table (index 0 i.e. t1) to have SJ_OPT_FIRSTMATCH.
        - This is how t1, an outer table, receives SJ_OPT_FIRSTMATCH and is the first of the firstmatch range.
        - Then setup_semijoin_dups_elimination() runs, loops over t1,t2,t3, with "tab"==t1, which has emb_sj_nest==NULL,
        this leads to t2 and t3 not having first|last_sj_inner_tab set
        - This makes check_join_cache_usage() think that there are no semijoin nests. This is why join caching happens
        even with optimizer_join_cache_level=1, though such level normally excludes semijoins. Even at higher join cache levels, where it is ok to handle semijoin with join caching, the unset first|last_sj_inner_tab make check_join_cache_usage() not recognize that it is a firstmatch range with two tables, a case for which join
        caching is normally excluded (see BUG 45191) ; here the exclusion isn't enforced and a bad result ensues.
        - The fix: change an "if" to "else if".
        - Note: the usage of "tab->emb_sj_nest" in the for() loop of setup_semijoin_dups_elimination() is suspicious, but
        unrelated to this bug report and will be dealt with separately.
[2 Mar 2010 10:41] Øystein Grøvlen
Patch approved.
[5 Mar 2010 15:50] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/102449

3873 Guilhem Bichot	2010-03-05
      Fix for BUG#50361 "Doublenested noncorrelated subquery with FirstMatch and join cache wrong result":
      semijoin materialization was chosen, but the outer table was accidentally set to use firstmatch;
      that is meaningless and made certain structures inconsistent, which led join buffering to be used for
      this two-inner-table firstmatch semijoin, which isn't supported and gave wrong results.
      See commit comment of sql_select.cc for more details.
     @ mysql-test/r/subselect4.result
        result. Without the bugfix, SELECTs would generate extra rows. If only the assertion is added, it fires.
     @ mysql-test/t/subselect4.test
        test for the bug. Multiple tables instead of one are used to make it easier to distinguish
        tables in EXPLAIN.
     @ sql/sql_select.cc
        With the testcase of BUG#50361, tables do not have their first|last_sj_inner_tab set (which leads
        to various wrong results in SELECT). That is because "tab" (first table in
        the for() loop) is t1. This isn't normal, an outer table should not be seen in this loop, because, as
        documented in setup_semijoin_dups_elimination(), a firstmatch duplicate-generating range starts with
        an inner table (abbreviated "it").
        Here is the path which leads to this.
        - Semijoin transformation rewrites the query as "t1 SJ (t2, t3)"
        - To prepare for materialization, optimize_semijoin_nests() is fed an initial Query Execution Plan (t2,t3,t1)
        (semijoin inner tables first, see the first lines of choose_plan()), and ordered to create a partial Query
        Execution Plan for t2, t3. It produces plan (t2,t3), with t3 said to use firstmatch. It sounds unnecessary for
        advance_sj_state() to pick semijoin strategies at this stage, but it does not know that it is at this stage, so
        that's how it is.
        - Then choose_plan() runs for the top-level query: picks (t1, t2, t3) and decides to use materialization, marks t3
        accordingly with SJ_OPT_MATERIALIZE
        - Then fix_semijoin_strategies_for_picked_join_order() runs: for the first iteration, it is on t3, sees that it
        should use materialization, so copies the partial QEP saved by optimize_semijoin_nests() onto the POSITION slots of
        t2 and t3. This imports certain information from the partial QEP which has two effects:
          * it sets sj_strategy to SJ_OPT_FIRSTMATCH for t3 instead of SJ_OPT_MATERIALIZE
          * it sets first_firstmatch_table to "0" for t3; in the partial QEP, index "0" was t2, but here index "0" is t1
        - Because the "if (pos->sj_strategy == SJ_OPT_FIRST_MATCH)" in the function is not "else if", we enter
        it, so set the table at index first_firstmatch_table (index 0 i.e. t1) to have SJ_OPT_FIRSTMATCH.
        - This is how t1, an outer table, receives SJ_OPT_FIRSTMATCH and is the first of the firstmatch range.
        - Then setup_semijoin_dups_elimination() runs, loops over t1,t2,t3, with "tab"==t1, which has emb_sj_nest==NULL,
        this leads to t2 and t3 not having first|last_sj_inner_tab set
        - This makes check_join_cache_usage() think that there are no semijoin nests. This is why join caching happens
        even with optimizer_join_cache_level=1, though such level normally excludes semijoins. Even at higher join cache levels, where it is ok to handle semijoin with join caching, the unset first|last_sj_inner_tab make check_join_cache_usage() not recognize that it is a firstmatch range with two tables, a case for which join
        caching is normally excluded (see BUG 45191) ; here the exclusion isn't enforced and a bad result ensues.
        - The fix: change "if (pos->sj_strategy == SJ_OPT_FIRST_MATCH)" to
        "else if (pos->sj_strategy == SJ_OPT_FIRST_MATCH)"; this way, once we have taken the branch of "if this is materialization", we don't risk taking the branch of "if this is firstmatch" based on the temporary wrong
        pos->sj_strategy.
        - Note: the usage of "tab->emb_sj_nest" in the for() loop of setup_semijoin_dups_elimination() is suspicious, but
        unrelated to this bug report and reported as BUG 51457.
        
        An assertion is added to make sure that first|last_sj_inner_tab is not left unset by
        setup_semijoin_dups_elimination() for any semijoin inner table participating in a firstmatch range.
[8 Mar 2010 13:41] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/102579

3873 Guilhem Bichot	2010-03-08
      Fix for BUG#50361 "Doublenested noncorrelated subquery with FirstMatch and join cache wrong result":
      semijoin materialization was chosen, but the outer table was accidentally set to use firstmatch;
      that is meaningless and made certain structures inconsistent, which led join buffering to be used for
      this two-inner-table firstmatch semijoin, which isn't supported and gave wrong results.
      See commit comment of sql_select.cc for more details.
     @ mysql-test/r/subselect4.result
        result. Without the bugfix, SELECTs would generate extra rows. If only the assertion is added, it fires.
     @ mysql-test/t/subselect4.test
        test for the bug. Multiple tables instead of one are used to make it easier to distinguish
        tables in EXPLAIN.
     @ sql/sql_select.cc
        With the testcase of BUG#50361, tables do not have their first|last_sj_inner_tab set (which leads
        to various wrong results in SELECT). That is because "tab" (first table in
        the for() loop) is t1. This isn't normal, an outer table should not be seen in this loop, because, as
        documented in setup_semijoin_dups_elimination(), a firstmatch duplicate-generating range starts with
        an inner table (abbreviated "it").
        Here is the path which leads to this.
        - Semijoin transformation rewrites the query as "t1 SJ (t2, t3)"
        - To prepare for materialization, optimize_semijoin_nests() is fed an initial Query Execution Plan (t2,t3,t1)
        (semijoin inner tables first, see the first lines of choose_plan()), and ordered to create a partial Query
        Execution Plan for t2, t3. It produces plan (t2,t3), with t3 said to use firstmatch. It sounds unnecessary for
        advance_sj_state() to pick semijoin strategies at this stage, but it does not know that it is at this stage, so
        that's how it is.
        - Then choose_plan() runs for the top-level query: picks (t1, t2, t3) and decides to use materialization, marks t3
        accordingly with SJ_OPT_MATERIALIZE
        - Then fix_semijoin_strategies_for_picked_join_order() runs: for the first iteration, it is on t3, sees that it
        should use materialization, so copies the partial QEP saved by optimize_semijoin_nests() onto the POSITION slots of
        t2 and t3. This imports certain information from the partial QEP which has two effects:
          * it sets sj_strategy to SJ_OPT_FIRSTMATCH for t3 instead of SJ_OPT_MATERIALIZE
          * it sets first_firstmatch_table to "0" for t3; in the partial QEP, index "0" was t2, but here index "0" is t1
        - Because the "if (pos->sj_strategy == SJ_OPT_FIRST_MATCH)" in the function is not "else if", we enter
        it, so set the table at index first_firstmatch_table (index 0 i.e. t1) to have SJ_OPT_FIRSTMATCH.
        - This is how t1, an outer table, receives SJ_OPT_FIRSTMATCH and is the first of the firstmatch range.
        - Then setup_semijoin_dups_elimination() runs, loops over t1,t2,t3, with "tab"==t1, which has emb_sj_nest==NULL,
        this leads to t2 and t3 not having first|last_sj_inner_tab set
        - This makes check_join_cache_usage() think that there are no semijoin nests. This is why join caching happens
        even with optimizer_join_cache_level=1, though such level normally excludes semijoins. Even at higher join cache levels, where it is ok to handle semijoin with join caching, the unset first|last_sj_inner_tab make check_join_cache_usage() not recognize that it is a firstmatch range with two tables, a case for which join
        caching is normally excluded (see BUG 45191) ; here the exclusion isn't enforced and a bad result ensues.
        - The fix: change "if (pos->sj_strategy == SJ_OPT_FIRST_MATCH)" to
        "else if (pos->sj_strategy == SJ_OPT_FIRST_MATCH)"; this way, once we have taken the branch of "if this is materialization", we don't risk taking the branch of "if this is firstmatch" based on the temporary wrong
        pos->sj_strategy.
        - Note: the usage of "tab->emb_sj_nest" in the for() loop of setup_semijoin_dups_elimination() is suspicious, but
        unrelated to this bug report and reported as BUG 51457.
        
        Assertions are added to make sure that, for the strategies which require the first table of the
        range to be an inner table, this requirement is fulfilled.
        For firstmatch, this assertion thus makes sure that first|last_sj_inner_tab is not left unset by
        setup_semijoin_dups_elimination() for any semijoin inner table participating in a firstmatch range.
[15 Mar 2010 15:07] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/103281

3825 Guilhem Bichot	2010-03-15
      Fix for BUG#50361 "Doublenested noncorrelated subquery with FirstMatch and join cache wrong result":
      semijoin materialization was chosen, but the outer table was accidentally set to use firstmatch;
      that is meaningless and made certain structures inconsistent, which led join buffering to be used for
      this two-inner-table firstmatch semijoin, which isn't supported and gave wrong results.
      See commit comment of sql_select.cc for more details.
     @ mysql-test/r/subselect4.result
        result. Without the bugfix, SELECTs would generate extra rows. If only the assertion is added, it fires.
     @ mysql-test/t/subselect4.test
        test for the bug. Multiple tables instead of one are used to make it easier to distinguish
        tables in EXPLAIN.
     @ sql/sql_select.cc
        With the testcase of BUG#50361, tables do not have their first|last_sj_inner_tab set (which leads
        to various wrong results in SELECT). That is because "tab" (first table in
        the for() loop) is t1. This isn't normal, an outer table should not be seen in this loop, because, as
        documented in setup_semijoin_dups_elimination(), a firstmatch duplicate-generating range starts with
        an inner table (abbreviated "it").
        Here is the path which leads to this.
        - Semijoin transformation rewrites the query as "t1 SJ (t2, t3)"
        - To prepare for materialization, optimize_semijoin_nests() is fed an initial Query Execution Plan (t2,t3,t1)
        (semijoin inner tables first, see the first lines of choose_plan()), and ordered to create a partial Query
        Execution Plan for t2, t3. It produces plan (t2,t3), with t3 said to use firstmatch. It sounds unnecessary for
        advance_sj_state() to pick semijoin strategies at this stage, but it does not know that it is at this stage, so
        that's how it is.
        - Then choose_plan() runs for the top-level query: picks (t1, t2, t3) and decides to use materialization, marks t3
        accordingly with SJ_OPT_MATERIALIZE
        - Then fix_semijoin_strategies_for_picked_join_order() runs: for the first iteration, it is on t3, sees that it
        should use materialization, so copies the partial QEP saved by optimize_semijoin_nests() onto the POSITION slots of
        t2 and t3. This imports certain information from the partial QEP which has two effects:
          * it sets sj_strategy to SJ_OPT_FIRSTMATCH for t3 instead of SJ_OPT_MATERIALIZE
          * it sets first_firstmatch_table to "0" for t3; in the partial QEP, index "0" was t2, but here index "0" is t1
        - Because the "if (pos->sj_strategy == SJ_OPT_FIRST_MATCH)" in the function is not "else if", we enter
        it, so set the table at index first_firstmatch_table (index 0 i.e. t1) to have SJ_OPT_FIRSTMATCH.
        - This is how t1, an outer table, receives SJ_OPT_FIRSTMATCH and is the first of the firstmatch range.
        - Then setup_semijoin_dups_elimination() runs, loops over t1,t2,t3, with "tab"==t1, which has emb_sj_nest==NULL,
        this leads to t2 and t3 not having first|last_sj_inner_tab set
        - This makes check_join_cache_usage() think that there are no semijoin nests. This is why join caching happens
        even with optimizer_join_cache_level=1, though such level normally excludes semijoins. Even at higher join cache levels, where it is ok to handle semijoin with join caching, the unset first|last_sj_inner_tab make check_join_cache_usage() not recognize that it is a firstmatch range with two tables, a case for which join
        caching is normally excluded (see BUG 45191) ; here the exclusion isn't enforced and a bad result ensues.
        - The fix: change "if (pos->sj_strategy == SJ_OPT_FIRST_MATCH)" to
        "else if (pos->sj_strategy == SJ_OPT_FIRST_MATCH)"; this way, once we have taken the branch of "if this is materialization", we don't risk taking the branch of "if this is firstmatch" based on the temporary wrong
        pos->sj_strategy.
        - Note: the usage of "tab->emb_sj_nest" in the for() loop of setup_semijoin_dups_elimination() is suspicious, but
        unrelated to this bug report and reported as BUG 51457.
        
        Assertions are added to make sure that, for the strategies which require the first table of the
        range to be an inner table, this requirement is fulfilled.
        For firstmatch, this assertion thus makes sure that first|last_sj_inner_tab is not left unset by
        setup_semijoin_dups_elimination() for any semijoin inner table participating in a firstmatch range.
[24 Mar 2010 8:15] Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20100324081249-yfwol7qtcek6dh7w) (version source revid:alik@sun.com-20100324081113-kc7x1iytnplww91u) (merge vers: 6.0.14-alpha) (pib:16)
[16 Apr 2010 17:30] Paul DuBois
Noted in 6.0.14 changelog.

Double-nested, noncorrelated IN subqueries failed when using the
FirstMatch optimization strategy with join_cache_level set to 1.
[8 May 2010 14:49] Guilhem Bichot
backported to next-mr-opt-backporting guilhem@mysql.com-20100507195803-ajfnsoogxuikyxhe
[16 Aug 2010 6:31] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100816062819-bluwgdq8q4xysmlg) (version source revid:alik@sun.com-20100816062612-enatdwnv809iw3s9) (pib:20)
[13 Nov 2010 16:16] Bugs System
Pushed into mysql-trunk 5.6.99-m5 (revid:alexander.nozdrin@oracle.com-20101113155825-czmva9kg4n31anmu) (version source revid:vasil.dimov@oracle.com-20100629074804-359l9m9gniauxr94) (merge vers: 5.6.99-m4) (pib:21)
[23 Nov 2010 3:14] Paul DuBois
Bug does not appear in any released 5.6.x version. No 5.6.1 changelog entry needed.