List:Commits« Previous MessageNext Message »
From:Guilhem Bichot Date:August 5 2011 3:07pm
Subject:bzr push into mysql-trunk branch (guilhem.bichot:3415 to 3417)
View as plain text  
 3417 Guilhem Bichot	2011-08-05
      No need to explicitely set mrr_cost_based=off in join_cache.inc: only tests with jcl>=5
      did this, and the join_cache_jclX.test wrappers already set mrr_cost_based=off
      if X>=5.

    modified:
      mysql-test/include/join_cache.inc
      mysql-test/r/join_cache_jcl0.result
      mysql-test/r/join_cache_jcl1.result
      mysql-test/r/join_cache_jcl2.result
      mysql-test/r/join_cache_jcl3.result
      mysql-test/r/join_cache_jcl4.result
      mysql-test/r/join_cache_jcl5.result
      mysql-test/r/join_cache_jcl6.result
      mysql-test/r/join_cache_jcl7.result
      mysql-test/r/join_cache_jcl8.result
 3416 Guilhem Bichot	2011-08-05
      Fix for BUG#12698916 - "JOIN QUERY GIVES WRONG RESULT AT 2ND EXEC. OR
      AFTER FLUSH TABLES [-INT VS NULL]"
      Range optimizer was wrongly changing the TABLE::read_set so InnoDB
      was not filling one column.
     @ mysql-test/include/range.inc
        test for bug.
     @ mysql-test/r/range_all.result
        Without the code fix, the second SELECT would show
        "DEFAULT" instead of "GOOD".
     @ sql/opt_range.cc
        Scenario of the bug is below, using the testcase in range.inc.
        FLUSH TABLES affects InnoDB statistics (records' count), after what
        the plan for SELECT is that "range checked for each record" is used
        for t2: range optimizer is run for each row coming from t1,
        to find a best access path to access t2.
        
        For the first row of t1, access for t2 is a range scan implementing
        "pk < 1", there is no bug there.
        
        For the second row of t1 ("1,6"), access for t2 is more complex:
                        "chosen_range_access_summary": {
                          "range_access_plan": {
                            "type": "index_roworder_intersect",
                            "records": 1,
                            "cost": 1.1,
                            "covering": false,
                            "clustered_pk_scan": true,
                            "intersect_of": [
                              {
                                "type": "range_scan",
                                "index": "col_int_key",
                                "records": 1,
                                "ranges": [
                                  "1 <= col_int_key <= 1"
                                ] /* ranges */
                              }
                            ] /* intersect_of */
                          } /* range_access_plan */,
        This is the intersection of two scans:
        - a scan on the (InnoDB's clustered) pk (we will ignore this one, it
        causes no problem)
        - a range scan using index "col_int_key" and implementing
        "1 <= col_int_key <= 1".
        
        The range scan object is constructed by the constructor of
        QUICK_RANGE_SELECT, at a moment when t2's TABLE::read_set is equal to
        pk+col_int_key+col_varchar (as SELECT asked to read all three
        columns); the constructor saves this read_set (which is a pointer to bitmap)
        into QUICK_RANGE_SELECT::save_read_set.
        
        Then the intersection above is initialized in:
        QUICK_ROR_INTERSECT_SELECT::init_ror_merged_scan()
        which initializes its "intersection members". This calls 
        quick->init_ror_merged_scan() where "quick" is QUICK_RANGE_SELECT.
        QUICK_RANGE_SELECT::init_ror_merged_scan() prepares a bitmap of
        columns which the range scan will read:
        - col_int_key (that's what the range scan is on)
        - pk (added by prepare_for_position(), because in InnoDB the rowid is
        the pk).
        This correctly misses col_varchar: indeed the range scan only wants to
        gather rowids (row "positions"), and col_varchar should be fetched
        only later (by rowid).
        The prepared bitmap is TABLE::tmp_set (TABLE is t2). It is copied to
        QUICK_RANGE_SELECT::column_bitmap, and t2's read_set is set to point
        to column_bitmap.
        So the execution of QUICK_ROR_INTERSECT_SELECT::init_ror_merged_scan()
        looks like:
          // HERE READ_SET = pk+col_int_key+col_varchar
          while ((quick= quick_it++))
          {
            // HERE 'quick' is the QUICK_RANGE_SELECT
            if (quick->init_ror_merged_scan(FALSE))
              DBUG_RETURN(1);
            // HERE READ_SET = pk+col_int_key
            quick->file->extra(HA_EXTRA_KEYREAD_PRESERVE_FIELDS);
            /* All merged scans share the same record buffer in intersection. */
            quick->record= head->record[0];
          }
          // HERE READ_SET = pk+col_int_key (STILL); need_to_fetch_row is true
          if (need_to_fetch_row && head->file->ha_rnd_init(1))
        
        One can already see a first problem (not present in this testcase): if
        there were two "quick", the second "quick" would overwrite the
        TABLE::read_set set by the first "quick" in the loop. But here we have a
        single 'quick' (the clustered pk scan is handled with special code
        elsewhere, is not part of the loop).
        The second problem of the code above is that ha_rnd_init() (which is
        a preparation step for reading rows given their rowids) is called while
        read_set=pk+col_int_key.
        Whereas when we read rows given their rowids, we will want to read
        col_varchar too.
        ha_rnd_init() calls InnoDB's rnd_init() which, through
        change_active_index(), sets up column-reading
        structures (called "templates", see build_template()) for reading
        columns listed in read_set: InnoDB thus creates column-reading
        structures only for pk+col_int_key.
        Then execution progresses and the intersection is determined, in
        QUICK_ROR_INTERSECT_SELECT::get_next().
        This itself calls QUICK_RANGE_SELECT::get_next(), which, at its start,
        sets read_set to column_bitmap, determines a rowid, then restores
        read_set to save_read_set.
        Later, when the rnd_pos() actually happens (to read a row given its
        rowid), at end of QUICK_ROR_INTERSECT_SELECT::get_next(), read_set is
        save_read_set, so is pk+col_int_key+col_varchar (correct), but
        rnd_pos() in InnoDB reads only pk+col_int_key because InnoDB's
        column-reading structures are only for pk+col_int_key, because they
        were set up in rnd_init() which was called at a time when read_set
        was pk+col_int_key.
        
        Because InnoDB does not fill col_varchar, it remains equal to what is
        was; init_read_record() had filled the record with its default values
        (empty_record()) so the client gets this default value.
        
        In 5.5 there is no bug, because InnoDB's rnd_pos() calls
        change_active_index() which re-creates column-reading structures
        based on read_set, and as read_set is correct at that moment,
        new structures are correct.
        
        In 5.6, InnoDB's rnd_pos() doesn't call change_active_index() anymore;
        this change was done by SergeyP in the huge MRR patch with no
        explanation.
        The fix:
        - restore read_set to save_read_set at end of
        QUICK_RANGE_SELECT::init_ror_merged_scan(): there is no reason to
        pollute the rest of execution, read_set serves to many components,
        QUICK_RANGE_SELECT should touch read_set only when it's actually
        reading (taking control of the table), i.e. in its
        QUICK_RANGE_SELECT::get_next().
        - note that QUICK_RANGE_SELECT::get_next() already sets read_set to
        column_bitmap when starting, and sets back to save_read_set when
        ending, which is correct.
        
        An assertion is added to make sure that in
        QUICK_ROR_INTERSECT_SELECT::init_ror_merged_scan() no "quick"
        object causes a permanent modification of TABLE::read_set/write_set pointers.

    modified:
      mysql-test/include/range.inc
      mysql-test/r/range_all.result
      mysql-test/r/range_icp.result
      mysql-test/r/range_icp_mrr.result
      mysql-test/r/range_mrr.result
      mysql-test/r/range_mrr_cost.result
      mysql-test/r/range_none.result
      sql/opt_range.cc
 3415 Jorgen Loland	2011-08-05
      Recorded result files after merge mysql-trunk -> opt-backporting

    modified:
      mysql-test/r/innodb_icp_all.result
      mysql-test/r/innodb_mrr_all.result
      mysql-test/r/innodb_mrr_cost_all.result
      mysql-test/r/myisam_icp_all.result
      mysql-test/r/myisam_mrr_all.result
      mysql-test/r/myisam_mrr_cost_all.result
=== modified file 'mysql-test/include/join_cache.inc'
--- a/mysql-test/include/join_cache.inc	2011-07-29 09:02:31 +0000
+++ b/mysql-test/include/join_cache.inc	2011-08-05 14:47:50 +0000
@@ -1763,9 +1763,6 @@ set @@join_buffer_size=default;
 --echo # JCL>=5 AND MRR ENABLED"
 --echo
 
-SET @old_optimizer_switch=@@optimizer_switch;
-SET optimizer_switch='mrr=on,mrr_cost_based=off';
-
 CREATE TABLE t1 (  col_int_key int(11) NOT NULL,
    col_varchar_key varchar(1) NOT NULL,    
    KEY col_int_key (col_int_key),
@@ -1797,15 +1794,11 @@ eval explain $query;
 eval $query;
 
 DROP TABLE t1,t2;
-SET @@optimizer_switch=@old_optimizer_switch;
 
 --echo
 --echo # BUG#12619510 "JCL: MORE ROWS AND DIFFERENT OUTPUT WITH JCL>=5"
 --echo
 
-SET @old_optimizer_switch=@@optimizer_switch;
-SET optimizer_switch='mrr=on,mrr_cost_based=off';
-
 CREATE TABLE t1 (
 col_int_key int(11) NOT NULL,
 col_datetime_key datetime NOT NULL,
@@ -1857,15 +1850,11 @@ eval explain $query;
 eval $query;
 
 DROP TABLE t1,t2;
-SET @@optimizer_switch=@old_optimizer_switch;
 
 --echo
 --echo # BUG#12619868 "JCL: MORE ROWS OF OUTPUT WHEN JCL>=5"
 --echo
 
-SET @old_optimizer_switch=@@optimizer_switch;
-SET optimizer_switch='mrr=on,mrr_cost_based=off';
-
 CREATE TABLE t1 (col_varchar_key varchar(1));
 CREATE TABLE t2 (
  pk int(11) NOT NULL,
@@ -1904,16 +1893,12 @@ eval explain $query;
 eval $query;
 
 DROP TABLE t1,t2;
-SET @@optimizer_switch=@old_optimizer_switch;
 
 --echo
 --echo # BUG#12722133 - JCL: JOIN QUERY GIVES DIFFERENT RESULTS AT
 --echo # JCL=6 ONLY [NULL VERSUS NULL+#INTS]
 --echo
 
-SET @old_optimizer_switch=@@optimizer_switch;
-SET optimizer_switch='mrr=on,mrr_cost_based=off';
-
 CREATE TABLE t1 (pk INTEGER PRIMARY KEY, k INTEGER, i INTEGER, KEY k(k));
 CREATE TABLE t2 LIKE t1;
 CREATE TABLE t3 LIKE t1;
@@ -1934,4 +1919,3 @@ eval EXPLAIN $query;
 eval $query;
 
 DROP TABLE t1, t2, t3, t4; 
-SET @@optimizer_switch=@old_optimizer_switch;

=== modified file 'mysql-test/include/range.inc'
--- a/mysql-test/include/range.inc	2011-05-19 12:03:55 +0000
+++ b/mysql-test/include/range.inc	2011-08-05 14:30:29 +0000
@@ -1503,3 +1503,34 @@ let $query= SELECT * FROM t100 WHERE I <
 --eval $query
 
 DROP TABLE t10,t100;
+
+--echo #
+--echo # BUG#12698916 - JOIN QUERY GIVES WRONG RESULT AT 2ND EXEC. OR
+--echo # AFTER FLUSH TABLES [-INT VS NULL]
+--echo #
+CREATE TABLE t1 (col_int INT, pk INT) ENGINE=InnoDB;
+
+INSERT INTO t1 VALUES (-100,1),(1,6);
+
+CREATE TABLE t2 (
+  col_int_key INT,
+  col_varchar VARCHAR(100) NOT NULL DEFAULT "DEFAULT",
+  pk INT NOT NULL,
+  PRIMARY KEY (pk),
+  KEY (col_int_key)
+) ENGINE=InnoDB;
+
+INSERT INTO t2 VALUES
+(1,"GOOD",1),(100,"",2),(200,"",3),(300,"",4),(400,"",5),(500,"",8);
+
+let $query=SELECT t1.*,t2.* FROM t1 straight_join t2
+ON t2.col_int_key = t1.col_int WHERE t2.pk < t1.pk;
+
+eval EXPLAIN $query;
+eval $query;
+--echo # need FLUSH so that InnoDB statistics change and thus plan changes
+FLUSH TABLES;
+eval EXPLAIN $query;
+eval $query;
+
+DROP TABLE t1,t2;

=== modified file 'mysql-test/r/join_cache_jcl0.result'
--- a/mysql-test/r/join_cache_jcl0.result	2011-08-05 10:37:03 +0000
+++ b/mysql-test/r/join_cache_jcl0.result	2011-08-05 14:47:50 +0000
@@ -2443,8 +2443,6 @@ set @@join_buffer_size=default;
 # BUG#12586926 "EXTRA ROW WITH JOIN + GROUP BY + ORDER BY WITH
 # JCL>=5 AND MRR ENABLED"
 
-SET @old_optimizer_switch=@@optimizer_switch;
-SET optimizer_switch='mrr=on,mrr_cost_based=off';
 CREATE TABLE t1 (  col_int_key int(11) NOT NULL,
 col_varchar_key varchar(1) NOT NULL,    
 KEY col_int_key (col_int_key),
@@ -2478,12 +2476,9 @@ field1	field2
 NULL	0
 2003-08-21 00:00:00	4
 DROP TABLE t1,t2;
-SET @@optimizer_switch=@old_optimizer_switch;
 
 # BUG#12619510 "JCL: MORE ROWS AND DIFFERENT OUTPUT WITH JCL>=5"
 
-SET @old_optimizer_switch=@@optimizer_switch;
-SET optimizer_switch='mrr=on,mrr_cost_based=off';
 CREATE TABLE t1 (
 col_int_key int(11) NOT NULL,
 col_datetime_key datetime NOT NULL,
@@ -2555,12 +2550,9 @@ w	0	18
 x	0	14
 y	2	7
 DROP TABLE t1,t2;
-SET @@optimizer_switch=@old_optimizer_switch;
 
 # BUG#12619868 "JCL: MORE ROWS OF OUTPUT WHEN JCL>=5"
 
-SET @old_optimizer_switch=@@optimizer_switch;
-SET optimizer_switch='mrr=on,mrr_cost_based=off';
 CREATE TABLE t1 (col_varchar_key varchar(1));
 CREATE TABLE t2 (
 pk int(11) NOT NULL,
@@ -2611,13 +2603,10 @@ NULL	7
 NULL	8
 NULL	9
 DROP TABLE t1,t2;
-SET @@optimizer_switch=@old_optimizer_switch;
 
 # BUG#12722133 - JCL: JOIN QUERY GIVES DIFFERENT RESULTS AT
 # JCL=6 ONLY [NULL VERSUS NULL+#INTS]
 
-SET @old_optimizer_switch=@@optimizer_switch;
-SET optimizer_switch='mrr=on,mrr_cost_based=off';
 CREATE TABLE t1 (pk INTEGER PRIMARY KEY, k INTEGER, i INTEGER, KEY k(k));
 CREATE TABLE t2 LIKE t1;
 CREATE TABLE t3 LIKE t1;
@@ -2645,6 +2634,5 @@ t2_pk	t4_pk	t4_k	t4_i
 NULL	NULL	NULL	NULL
 1	1	9	-1
 DROP TABLE t1, t2, t3, t4;
-SET @@optimizer_switch=@old_optimizer_switch;
 set optimizer_join_cache_level = default;
 set optimizer_switch = default;

=== modified file 'mysql-test/r/join_cache_jcl1.result'
--- a/mysql-test/r/join_cache_jcl1.result	2011-08-05 10:37:03 +0000
+++ b/mysql-test/r/join_cache_jcl1.result	2011-08-05 14:47:50 +0000
@@ -2443,8 +2443,6 @@ set @@join_buffer_size=default;
 # BUG#12586926 "EXTRA ROW WITH JOIN + GROUP BY + ORDER BY WITH
 # JCL>=5 AND MRR ENABLED"
 
-SET @old_optimizer_switch=@@optimizer_switch;
-SET optimizer_switch='mrr=on,mrr_cost_based=off';
 CREATE TABLE t1 (  col_int_key int(11) NOT NULL,
 col_varchar_key varchar(1) NOT NULL,    
 KEY col_int_key (col_int_key),
@@ -2478,12 +2476,9 @@ field1	field2
 NULL	0
 2003-08-21 00:00:00	4
 DROP TABLE t1,t2;
-SET @@optimizer_switch=@old_optimizer_switch;
 
 # BUG#12619510 "JCL: MORE ROWS AND DIFFERENT OUTPUT WITH JCL>=5"
 
-SET @old_optimizer_switch=@@optimizer_switch;
-SET optimizer_switch='mrr=on,mrr_cost_based=off';
 CREATE TABLE t1 (
 col_int_key int(11) NOT NULL,
 col_datetime_key datetime NOT NULL,
@@ -2555,12 +2550,9 @@ w	0	18
 x	0	14
 y	2	7
 DROP TABLE t1,t2;
-SET @@optimizer_switch=@old_optimizer_switch;
 
 # BUG#12619868 "JCL: MORE ROWS OF OUTPUT WHEN JCL>=5"
 
-SET @old_optimizer_switch=@@optimizer_switch;
-SET optimizer_switch='mrr=on,mrr_cost_based=off';
 CREATE TABLE t1 (col_varchar_key varchar(1));
 CREATE TABLE t2 (
 pk int(11) NOT NULL,
@@ -2611,13 +2603,10 @@ NULL	7
 NULL	8
 NULL	9
 DROP TABLE t1,t2;
-SET @@optimizer_switch=@old_optimizer_switch;
 
 # BUG#12722133 - JCL: JOIN QUERY GIVES DIFFERENT RESULTS AT
 # JCL=6 ONLY [NULL VERSUS NULL+#INTS]
 
-SET @old_optimizer_switch=@@optimizer_switch;
-SET optimizer_switch='mrr=on,mrr_cost_based=off';
 CREATE TABLE t1 (pk INTEGER PRIMARY KEY, k INTEGER, i INTEGER, KEY k(k));
 CREATE TABLE t2 LIKE t1;
 CREATE TABLE t3 LIKE t1;
@@ -2645,6 +2634,5 @@ t2_pk	t4_pk	t4_k	t4_i
 NULL	NULL	NULL	NULL
 1	1	9	-1
 DROP TABLE t1, t2, t3, t4;
-SET @@optimizer_switch=@old_optimizer_switch;
 set optimizer_join_cache_level = default;
 set optimizer_switch = default;

=== modified file 'mysql-test/r/join_cache_jcl2.result'
--- a/mysql-test/r/join_cache_jcl2.result	2011-08-05 10:37:03 +0000
+++ b/mysql-test/r/join_cache_jcl2.result	2011-08-05 14:47:50 +0000
@@ -2443,8 +2443,6 @@ set @@join_buffer_size=default;
 # BUG#12586926 "EXTRA ROW WITH JOIN + GROUP BY + ORDER BY WITH
 # JCL>=5 AND MRR ENABLED"
 
-SET @old_optimizer_switch=@@optimizer_switch;
-SET optimizer_switch='mrr=on,mrr_cost_based=off';
 CREATE TABLE t1 (  col_int_key int(11) NOT NULL,
 col_varchar_key varchar(1) NOT NULL,    
 KEY col_int_key (col_int_key),
@@ -2478,12 +2476,9 @@ field1	field2
 NULL	0
 2003-08-21 00:00:00	4
 DROP TABLE t1,t2;
-SET @@optimizer_switch=@old_optimizer_switch;
 
 # BUG#12619510 "JCL: MORE ROWS AND DIFFERENT OUTPUT WITH JCL>=5"
 
-SET @old_optimizer_switch=@@optimizer_switch;
-SET optimizer_switch='mrr=on,mrr_cost_based=off';
 CREATE TABLE t1 (
 col_int_key int(11) NOT NULL,
 col_datetime_key datetime NOT NULL,
@@ -2555,12 +2550,9 @@ w	0	18
 x	0	14
 y	2	7
 DROP TABLE t1,t2;
-SET @@optimizer_switch=@old_optimizer_switch;
 
 # BUG#12619868 "JCL: MORE ROWS OF OUTPUT WHEN JCL>=5"
 
-SET @old_optimizer_switch=@@optimizer_switch;
-SET optimizer_switch='mrr=on,mrr_cost_based=off';
 CREATE TABLE t1 (col_varchar_key varchar(1));
 CREATE TABLE t2 (
 pk int(11) NOT NULL,
@@ -2611,13 +2603,10 @@ NULL	7
 NULL	8
 NULL	9
 DROP TABLE t1,t2;
-SET @@optimizer_switch=@old_optimizer_switch;
 
 # BUG#12722133 - JCL: JOIN QUERY GIVES DIFFERENT RESULTS AT
 # JCL=6 ONLY [NULL VERSUS NULL+#INTS]
 
-SET @old_optimizer_switch=@@optimizer_switch;
-SET optimizer_switch='mrr=on,mrr_cost_based=off';
 CREATE TABLE t1 (pk INTEGER PRIMARY KEY, k INTEGER, i INTEGER, KEY k(k));
 CREATE TABLE t2 LIKE t1;
 CREATE TABLE t3 LIKE t1;
@@ -2645,6 +2634,5 @@ t2_pk	t4_pk	t4_k	t4_i
 NULL	NULL	NULL	NULL
 1	1	9	-1
 DROP TABLE t1, t2, t3, t4;
-SET @@optimizer_switch=@old_optimizer_switch;
 set optimizer_join_cache_level = default;
 set optimizer_switch = default;

=== modified file 'mysql-test/r/join_cache_jcl3.result'
--- a/mysql-test/r/join_cache_jcl3.result	2011-08-05 10:37:03 +0000
+++ b/mysql-test/r/join_cache_jcl3.result	2011-08-05 14:47:50 +0000
@@ -2443,8 +2443,6 @@ set @@join_buffer_size=default;
 # BUG#12586926 "EXTRA ROW WITH JOIN + GROUP BY + ORDER BY WITH
 # JCL>=5 AND MRR ENABLED"
 
-SET @old_optimizer_switch=@@optimizer_switch;
-SET optimizer_switch='mrr=on,mrr_cost_based=off';
 CREATE TABLE t1 (  col_int_key int(11) NOT NULL,
 col_varchar_key varchar(1) NOT NULL,    
 KEY col_int_key (col_int_key),
@@ -2478,12 +2476,9 @@ field1	field2
 NULL	0
 2003-08-21 00:00:00	4
 DROP TABLE t1,t2;
-SET @@optimizer_switch=@old_optimizer_switch;
 
 # BUG#12619510 "JCL: MORE ROWS AND DIFFERENT OUTPUT WITH JCL>=5"
 
-SET @old_optimizer_switch=@@optimizer_switch;
-SET optimizer_switch='mrr=on,mrr_cost_based=off';
 CREATE TABLE t1 (
 col_int_key int(11) NOT NULL,
 col_datetime_key datetime NOT NULL,
@@ -2555,12 +2550,9 @@ w	0	18
 x	0	14
 y	2	7
 DROP TABLE t1,t2;
-SET @@optimizer_switch=@old_optimizer_switch;
 
 # BUG#12619868 "JCL: MORE ROWS OF OUTPUT WHEN JCL>=5"
 
-SET @old_optimizer_switch=@@optimizer_switch;
-SET optimizer_switch='mrr=on,mrr_cost_based=off';
 CREATE TABLE t1 (col_varchar_key varchar(1));
 CREATE TABLE t2 (
 pk int(11) NOT NULL,
@@ -2611,13 +2603,10 @@ NULL	7
 NULL	8
 NULL	9
 DROP TABLE t1,t2;
-SET @@optimizer_switch=@old_optimizer_switch;
 
 # BUG#12722133 - JCL: JOIN QUERY GIVES DIFFERENT RESULTS AT
 # JCL=6 ONLY [NULL VERSUS NULL+#INTS]
 
-SET @old_optimizer_switch=@@optimizer_switch;
-SET optimizer_switch='mrr=on,mrr_cost_based=off';
 CREATE TABLE t1 (pk INTEGER PRIMARY KEY, k INTEGER, i INTEGER, KEY k(k));
 CREATE TABLE t2 LIKE t1;
 CREATE TABLE t3 LIKE t1;
@@ -2645,6 +2634,5 @@ t2_pk	t4_pk	t4_k	t4_i
 NULL	NULL	NULL	NULL
 1	1	9	-1
 DROP TABLE t1, t2, t3, t4;
-SET @@optimizer_switch=@old_optimizer_switch;
 set optimizer_join_cache_level = default;
 set optimizer_switch = default;

=== modified file 'mysql-test/r/join_cache_jcl4.result'
--- a/mysql-test/r/join_cache_jcl4.result	2011-08-05 10:37:03 +0000
+++ b/mysql-test/r/join_cache_jcl4.result	2011-08-05 14:47:50 +0000
@@ -2443,8 +2443,6 @@ set @@join_buffer_size=default;
 # BUG#12586926 "EXTRA ROW WITH JOIN + GROUP BY + ORDER BY WITH
 # JCL>=5 AND MRR ENABLED"
 
-SET @old_optimizer_switch=@@optimizer_switch;
-SET optimizer_switch='mrr=on,mrr_cost_based=off';
 CREATE TABLE t1 (  col_int_key int(11) NOT NULL,
 col_varchar_key varchar(1) NOT NULL,    
 KEY col_int_key (col_int_key),
@@ -2478,12 +2476,9 @@ field1	field2
 NULL	0
 2003-08-21 00:00:00	4
 DROP TABLE t1,t2;
-SET @@optimizer_switch=@old_optimizer_switch;
 
 # BUG#12619510 "JCL: MORE ROWS AND DIFFERENT OUTPUT WITH JCL>=5"
 
-SET @old_optimizer_switch=@@optimizer_switch;
-SET optimizer_switch='mrr=on,mrr_cost_based=off';
 CREATE TABLE t1 (
 col_int_key int(11) NOT NULL,
 col_datetime_key datetime NOT NULL,
@@ -2555,12 +2550,9 @@ w	0	18
 x	0	14
 y	2	7
 DROP TABLE t1,t2;
-SET @@optimizer_switch=@old_optimizer_switch;
 
 # BUG#12619868 "JCL: MORE ROWS OF OUTPUT WHEN JCL>=5"
 
-SET @old_optimizer_switch=@@optimizer_switch;
-SET optimizer_switch='mrr=on,mrr_cost_based=off';
 CREATE TABLE t1 (col_varchar_key varchar(1));
 CREATE TABLE t2 (
 pk int(11) NOT NULL,
@@ -2611,13 +2603,10 @@ NULL	7
 NULL	8
 NULL	9
 DROP TABLE t1,t2;
-SET @@optimizer_switch=@old_optimizer_switch;
 
 # BUG#12722133 - JCL: JOIN QUERY GIVES DIFFERENT RESULTS AT
 # JCL=6 ONLY [NULL VERSUS NULL+#INTS]
 
-SET @old_optimizer_switch=@@optimizer_switch;
-SET optimizer_switch='mrr=on,mrr_cost_based=off';
 CREATE TABLE t1 (pk INTEGER PRIMARY KEY, k INTEGER, i INTEGER, KEY k(k));
 CREATE TABLE t2 LIKE t1;
 CREATE TABLE t3 LIKE t1;
@@ -2645,6 +2634,5 @@ t2_pk	t4_pk	t4_k	t4_i
 NULL	NULL	NULL	NULL
 1	1	9	-1
 DROP TABLE t1, t2, t3, t4;
-SET @@optimizer_switch=@old_optimizer_switch;
 set optimizer_join_cache_level = default;
 set optimizer_switch = default;

=== modified file 'mysql-test/r/join_cache_jcl5.result'
--- a/mysql-test/r/join_cache_jcl5.result	2011-07-29 09:02:31 +0000
+++ b/mysql-test/r/join_cache_jcl5.result	2011-08-05 14:47:50 +0000
@@ -2443,8 +2443,6 @@ set @@join_buffer_size=default;
 # BUG#12586926 "EXTRA ROW WITH JOIN + GROUP BY + ORDER BY WITH
 # JCL>=5 AND MRR ENABLED"
 
-SET @old_optimizer_switch=@@optimizer_switch;
-SET optimizer_switch='mrr=on,mrr_cost_based=off';
 CREATE TABLE t1 (  col_int_key int(11) NOT NULL,
 col_varchar_key varchar(1) NOT NULL,    
 KEY col_int_key (col_int_key),
@@ -2478,12 +2476,9 @@ field1	field2
 NULL	0
 2003-08-21 00:00:00	4
 DROP TABLE t1,t2;
-SET @@optimizer_switch=@old_optimizer_switch;
 
 # BUG#12619510 "JCL: MORE ROWS AND DIFFERENT OUTPUT WITH JCL>=5"
 
-SET @old_optimizer_switch=@@optimizer_switch;
-SET optimizer_switch='mrr=on,mrr_cost_based=off';
 CREATE TABLE t1 (
 col_int_key int(11) NOT NULL,
 col_datetime_key datetime NOT NULL,
@@ -2555,12 +2550,9 @@ w	0	18
 x	0	14
 y	2	7
 DROP TABLE t1,t2;
-SET @@optimizer_switch=@old_optimizer_switch;
 
 # BUG#12619868 "JCL: MORE ROWS OF OUTPUT WHEN JCL>=5"
 
-SET @old_optimizer_switch=@@optimizer_switch;
-SET optimizer_switch='mrr=on,mrr_cost_based=off';
 CREATE TABLE t1 (col_varchar_key varchar(1));
 CREATE TABLE t2 (
 pk int(11) NOT NULL,
@@ -2611,13 +2603,10 @@ NULL	7
 NULL	8
 NULL	9
 DROP TABLE t1,t2;
-SET @@optimizer_switch=@old_optimizer_switch;
 
 # BUG#12722133 - JCL: JOIN QUERY GIVES DIFFERENT RESULTS AT
 # JCL=6 ONLY [NULL VERSUS NULL+#INTS]
 
-SET @old_optimizer_switch=@@optimizer_switch;
-SET optimizer_switch='mrr=on,mrr_cost_based=off';
 CREATE TABLE t1 (pk INTEGER PRIMARY KEY, k INTEGER, i INTEGER, KEY k(k));
 CREATE TABLE t2 LIKE t1;
 CREATE TABLE t3 LIKE t1;
@@ -2645,6 +2634,5 @@ t2_pk	t4_pk	t4_k	t4_i
 1	1	9	-1
 NULL	NULL	NULL	NULL
 DROP TABLE t1, t2, t3, t4;
-SET @@optimizer_switch=@old_optimizer_switch;
 set optimizer_join_cache_level = default;
 set optimizer_switch = default;

=== modified file 'mysql-test/r/join_cache_jcl6.result'
--- a/mysql-test/r/join_cache_jcl6.result	2011-07-29 09:02:31 +0000
+++ b/mysql-test/r/join_cache_jcl6.result	2011-08-05 14:47:50 +0000
@@ -2443,8 +2443,6 @@ set @@join_buffer_size=default;
 # BUG#12586926 "EXTRA ROW WITH JOIN + GROUP BY + ORDER BY WITH
 # JCL>=5 AND MRR ENABLED"
 
-SET @old_optimizer_switch=@@optimizer_switch;
-SET optimizer_switch='mrr=on,mrr_cost_based=off';
 CREATE TABLE t1 (  col_int_key int(11) NOT NULL,
 col_varchar_key varchar(1) NOT NULL,    
 KEY col_int_key (col_int_key),
@@ -2478,12 +2476,9 @@ field1	field2
 NULL	0
 2003-08-21 00:00:00	4
 DROP TABLE t1,t2;
-SET @@optimizer_switch=@old_optimizer_switch;
 
 # BUG#12619510 "JCL: MORE ROWS AND DIFFERENT OUTPUT WITH JCL>=5"
 
-SET @old_optimizer_switch=@@optimizer_switch;
-SET optimizer_switch='mrr=on,mrr_cost_based=off';
 CREATE TABLE t1 (
 col_int_key int(11) NOT NULL,
 col_datetime_key datetime NOT NULL,
@@ -2555,12 +2550,9 @@ w	0	18
 x	0	14
 y	2	7
 DROP TABLE t1,t2;
-SET @@optimizer_switch=@old_optimizer_switch;
 
 # BUG#12619868 "JCL: MORE ROWS OF OUTPUT WHEN JCL>=5"
 
-SET @old_optimizer_switch=@@optimizer_switch;
-SET optimizer_switch='mrr=on,mrr_cost_based=off';
 CREATE TABLE t1 (col_varchar_key varchar(1));
 CREATE TABLE t2 (
 pk int(11) NOT NULL,
@@ -2611,13 +2603,10 @@ NULL	7
 NULL	8
 NULL	9
 DROP TABLE t1,t2;
-SET @@optimizer_switch=@old_optimizer_switch;
 
 # BUG#12722133 - JCL: JOIN QUERY GIVES DIFFERENT RESULTS AT
 # JCL=6 ONLY [NULL VERSUS NULL+#INTS]
 
-SET @old_optimizer_switch=@@optimizer_switch;
-SET optimizer_switch='mrr=on,mrr_cost_based=off';
 CREATE TABLE t1 (pk INTEGER PRIMARY KEY, k INTEGER, i INTEGER, KEY k(k));
 CREATE TABLE t2 LIKE t1;
 CREATE TABLE t3 LIKE t1;
@@ -2645,6 +2634,5 @@ t2_pk	t4_pk	t4_k	t4_i
 1	1	9	-1
 NULL	NULL	NULL	NULL
 DROP TABLE t1, t2, t3, t4;
-SET @@optimizer_switch=@old_optimizer_switch;
 set optimizer_join_cache_level = default;
 set optimizer_switch = default;

=== modified file 'mysql-test/r/join_cache_jcl7.result'
--- a/mysql-test/r/join_cache_jcl7.result	2011-07-29 09:02:31 +0000
+++ b/mysql-test/r/join_cache_jcl7.result	2011-08-05 14:47:50 +0000
@@ -2443,8 +2443,6 @@ set @@join_buffer_size=default;
 # BUG#12586926 "EXTRA ROW WITH JOIN + GROUP BY + ORDER BY WITH
 # JCL>=5 AND MRR ENABLED"
 
-SET @old_optimizer_switch=@@optimizer_switch;
-SET optimizer_switch='mrr=on,mrr_cost_based=off';
 CREATE TABLE t1 (  col_int_key int(11) NOT NULL,
 col_varchar_key varchar(1) NOT NULL,    
 KEY col_int_key (col_int_key),
@@ -2478,12 +2476,9 @@ field1	field2
 NULL	0
 2003-08-21 00:00:00	4
 DROP TABLE t1,t2;
-SET @@optimizer_switch=@old_optimizer_switch;
 
 # BUG#12619510 "JCL: MORE ROWS AND DIFFERENT OUTPUT WITH JCL>=5"
 
-SET @old_optimizer_switch=@@optimizer_switch;
-SET optimizer_switch='mrr=on,mrr_cost_based=off';
 CREATE TABLE t1 (
 col_int_key int(11) NOT NULL,
 col_datetime_key datetime NOT NULL,
@@ -2555,12 +2550,9 @@ w	0	18
 x	0	14
 y	2	7
 DROP TABLE t1,t2;
-SET @@optimizer_switch=@old_optimizer_switch;
 
 # BUG#12619868 "JCL: MORE ROWS OF OUTPUT WHEN JCL>=5"
 
-SET @old_optimizer_switch=@@optimizer_switch;
-SET optimizer_switch='mrr=on,mrr_cost_based=off';
 CREATE TABLE t1 (col_varchar_key varchar(1));
 CREATE TABLE t2 (
 pk int(11) NOT NULL,
@@ -2611,13 +2603,10 @@ NULL	7
 NULL	8
 NULL	9
 DROP TABLE t1,t2;
-SET @@optimizer_switch=@old_optimizer_switch;
 
 # BUG#12722133 - JCL: JOIN QUERY GIVES DIFFERENT RESULTS AT
 # JCL=6 ONLY [NULL VERSUS NULL+#INTS]
 
-SET @old_optimizer_switch=@@optimizer_switch;
-SET optimizer_switch='mrr=on,mrr_cost_based=off';
 CREATE TABLE t1 (pk INTEGER PRIMARY KEY, k INTEGER, i INTEGER, KEY k(k));
 CREATE TABLE t2 LIKE t1;
 CREATE TABLE t3 LIKE t1;
@@ -2645,6 +2634,5 @@ t2_pk	t4_pk	t4_k	t4_i
 1	1	9	-1
 NULL	NULL	NULL	NULL
 DROP TABLE t1, t2, t3, t4;
-SET @@optimizer_switch=@old_optimizer_switch;
 set optimizer_join_cache_level = default;
 set optimizer_switch = default;

=== modified file 'mysql-test/r/join_cache_jcl8.result'
--- a/mysql-test/r/join_cache_jcl8.result	2011-07-29 09:02:31 +0000
+++ b/mysql-test/r/join_cache_jcl8.result	2011-08-05 14:47:50 +0000
@@ -2443,8 +2443,6 @@ set @@join_buffer_size=default;
 # BUG#12586926 "EXTRA ROW WITH JOIN + GROUP BY + ORDER BY WITH
 # JCL>=5 AND MRR ENABLED"
 
-SET @old_optimizer_switch=@@optimizer_switch;
-SET optimizer_switch='mrr=on,mrr_cost_based=off';
 CREATE TABLE t1 (  col_int_key int(11) NOT NULL,
 col_varchar_key varchar(1) NOT NULL,    
 KEY col_int_key (col_int_key),
@@ -2478,12 +2476,9 @@ field1	field2
 NULL	0
 2003-08-21 00:00:00	4
 DROP TABLE t1,t2;
-SET @@optimizer_switch=@old_optimizer_switch;
 
 # BUG#12619510 "JCL: MORE ROWS AND DIFFERENT OUTPUT WITH JCL>=5"
 
-SET @old_optimizer_switch=@@optimizer_switch;
-SET optimizer_switch='mrr=on,mrr_cost_based=off';
 CREATE TABLE t1 (
 col_int_key int(11) NOT NULL,
 col_datetime_key datetime NOT NULL,
@@ -2555,12 +2550,9 @@ w	0	18
 x	0	14
 y	2	7
 DROP TABLE t1,t2;
-SET @@optimizer_switch=@old_optimizer_switch;
 
 # BUG#12619868 "JCL: MORE ROWS OF OUTPUT WHEN JCL>=5"
 
-SET @old_optimizer_switch=@@optimizer_switch;
-SET optimizer_switch='mrr=on,mrr_cost_based=off';
 CREATE TABLE t1 (col_varchar_key varchar(1));
 CREATE TABLE t2 (
 pk int(11) NOT NULL,
@@ -2611,13 +2603,10 @@ NULL	7
 NULL	8
 NULL	9
 DROP TABLE t1,t2;
-SET @@optimizer_switch=@old_optimizer_switch;
 
 # BUG#12722133 - JCL: JOIN QUERY GIVES DIFFERENT RESULTS AT
 # JCL=6 ONLY [NULL VERSUS NULL+#INTS]
 
-SET @old_optimizer_switch=@@optimizer_switch;
-SET optimizer_switch='mrr=on,mrr_cost_based=off';
 CREATE TABLE t1 (pk INTEGER PRIMARY KEY, k INTEGER, i INTEGER, KEY k(k));
 CREATE TABLE t2 LIKE t1;
 CREATE TABLE t3 LIKE t1;
@@ -2645,6 +2634,5 @@ t2_pk	t4_pk	t4_k	t4_i
 1	1	9	-1
 NULL	NULL	NULL	NULL
 DROP TABLE t1, t2, t3, t4;
-SET @@optimizer_switch=@old_optimizer_switch;
 set optimizer_join_cache_level = default;
 set optimizer_switch = default;

=== modified file 'mysql-test/r/range_all.result'
--- a/mysql-test/r/range_all.result	2011-05-26 09:20:09 +0000
+++ b/mysql-test/r/range_all.result	2011-08-05 14:30:29 +0000
@@ -1873,4 +1873,40 @@ SELECT * FROM t100 WHERE I <> 6 OR (I <>
 K	I	J
 101	8	26
 DROP TABLE t10,t100;
+#
+# BUG#12698916 - JOIN QUERY GIVES WRONG RESULT AT 2ND EXEC. OR
+# AFTER FLUSH TABLES [-INT VS NULL]
+#
+CREATE TABLE t1 (col_int INT, pk INT) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (-100,1),(1,6);
+CREATE TABLE t2 (
+col_int_key INT,
+col_varchar VARCHAR(100) NOT NULL DEFAULT "DEFAULT",
+pk INT NOT NULL,
+PRIMARY KEY (pk),
+KEY (col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES
+(1,"GOOD",1),(100,"",2),(200,"",3),(300,"",4),(400,"",5),(500,"",8);
+EXPLAIN SELECT t1.*,t2.* FROM t1 straight_join t2
+ON t2.col_int_key = t1.col_int WHERE t2.pk < t1.pk;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
+1	SIMPLE	t2	ref	PRIMARY,col_int_key	col_int_key	5	test.t1.col_int	1	Using index condition
+SELECT t1.*,t2.* FROM t1 straight_join t2
+ON t2.col_int_key = t1.col_int WHERE t2.pk < t1.pk;
+col_int	pk	col_int_key	col_varchar	pk
+1	6	1	GOOD	1
+# need FLUSH so that InnoDB statistics change and thus plan changes
+FLUSH TABLES;
+EXPLAIN SELECT t1.*,t2.* FROM t1 straight_join t2
+ON t2.col_int_key = t1.col_int WHERE t2.pk < t1.pk;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	
+1	SIMPLE	t2	ALL	PRIMARY,col_int_key	NULL	NULL	NULL	6	Range checked for each record (index map: 0x3)
+SELECT t1.*,t2.* FROM t1 straight_join t2
+ON t2.col_int_key = t1.col_int WHERE t2.pk < t1.pk;
+col_int	pk	col_int_key	col_varchar	pk
+1	6	1	GOOD	1
+DROP TABLE t1,t2;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/range_icp.result'
--- a/mysql-test/r/range_icp.result	2011-05-19 12:03:55 +0000
+++ b/mysql-test/r/range_icp.result	2011-08-05 14:30:29 +0000
@@ -1873,4 +1873,40 @@ SELECT * FROM t100 WHERE I <> 6 OR (I <>
 K	I	J
 101	8	26
 DROP TABLE t10,t100;
+#
+# BUG#12698916 - JOIN QUERY GIVES WRONG RESULT AT 2ND EXEC. OR
+# AFTER FLUSH TABLES [-INT VS NULL]
+#
+CREATE TABLE t1 (col_int INT, pk INT) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (-100,1),(1,6);
+CREATE TABLE t2 (
+col_int_key INT,
+col_varchar VARCHAR(100) NOT NULL DEFAULT "DEFAULT",
+pk INT NOT NULL,
+PRIMARY KEY (pk),
+KEY (col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES
+(1,"GOOD",1),(100,"",2),(200,"",3),(300,"",4),(400,"",5),(500,"",8);
+EXPLAIN SELECT t1.*,t2.* FROM t1 straight_join t2
+ON t2.col_int_key = t1.col_int WHERE t2.pk < t1.pk;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
+1	SIMPLE	t2	ref	PRIMARY,col_int_key	col_int_key	5	test.t1.col_int	1	Using index condition
+SELECT t1.*,t2.* FROM t1 straight_join t2
+ON t2.col_int_key = t1.col_int WHERE t2.pk < t1.pk;
+col_int	pk	col_int_key	col_varchar	pk
+1	6	1	GOOD	1
+# need FLUSH so that InnoDB statistics change and thus plan changes
+FLUSH TABLES;
+EXPLAIN SELECT t1.*,t2.* FROM t1 straight_join t2
+ON t2.col_int_key = t1.col_int WHERE t2.pk < t1.pk;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	
+1	SIMPLE	t2	ALL	PRIMARY,col_int_key	NULL	NULL	NULL	6	Range checked for each record (index map: 0x3)
+SELECT t1.*,t2.* FROM t1 straight_join t2
+ON t2.col_int_key = t1.col_int WHERE t2.pk < t1.pk;
+col_int	pk	col_int_key	col_varchar	pk
+1	6	1	GOOD	1
+DROP TABLE t1,t2;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/range_icp_mrr.result'
--- a/mysql-test/r/range_icp_mrr.result	2011-05-19 12:03:55 +0000
+++ b/mysql-test/r/range_icp_mrr.result	2011-08-05 14:30:29 +0000
@@ -1873,4 +1873,40 @@ SELECT * FROM t100 WHERE I <> 6 OR (I <>
 K	I	J
 101	8	26
 DROP TABLE t10,t100;
+#
+# BUG#12698916 - JOIN QUERY GIVES WRONG RESULT AT 2ND EXEC. OR
+# AFTER FLUSH TABLES [-INT VS NULL]
+#
+CREATE TABLE t1 (col_int INT, pk INT) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (-100,1),(1,6);
+CREATE TABLE t2 (
+col_int_key INT,
+col_varchar VARCHAR(100) NOT NULL DEFAULT "DEFAULT",
+pk INT NOT NULL,
+PRIMARY KEY (pk),
+KEY (col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES
+(1,"GOOD",1),(100,"",2),(200,"",3),(300,"",4),(400,"",5),(500,"",8);
+EXPLAIN SELECT t1.*,t2.* FROM t1 straight_join t2
+ON t2.col_int_key = t1.col_int WHERE t2.pk < t1.pk;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
+1	SIMPLE	t2	ref	PRIMARY,col_int_key	col_int_key	5	test.t1.col_int	1	Using index condition
+SELECT t1.*,t2.* FROM t1 straight_join t2
+ON t2.col_int_key = t1.col_int WHERE t2.pk < t1.pk;
+col_int	pk	col_int_key	col_varchar	pk
+1	6	1	GOOD	1
+# need FLUSH so that InnoDB statistics change and thus plan changes
+FLUSH TABLES;
+EXPLAIN SELECT t1.*,t2.* FROM t1 straight_join t2
+ON t2.col_int_key = t1.col_int WHERE t2.pk < t1.pk;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	
+1	SIMPLE	t2	ALL	PRIMARY,col_int_key	NULL	NULL	NULL	6	Range checked for each record (index map: 0x3)
+SELECT t1.*,t2.* FROM t1 straight_join t2
+ON t2.col_int_key = t1.col_int WHERE t2.pk < t1.pk;
+col_int	pk	col_int_key	col_varchar	pk
+1	6	1	GOOD	1
+DROP TABLE t1,t2;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/range_mrr.result'
--- a/mysql-test/r/range_mrr.result	2011-05-19 12:03:55 +0000
+++ b/mysql-test/r/range_mrr.result	2011-08-05 14:30:29 +0000
@@ -1873,4 +1873,40 @@ SELECT * FROM t100 WHERE I <> 6 OR (I <>
 K	I	J
 101	8	26
 DROP TABLE t10,t100;
+#
+# BUG#12698916 - JOIN QUERY GIVES WRONG RESULT AT 2ND EXEC. OR
+# AFTER FLUSH TABLES [-INT VS NULL]
+#
+CREATE TABLE t1 (col_int INT, pk INT) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (-100,1),(1,6);
+CREATE TABLE t2 (
+col_int_key INT,
+col_varchar VARCHAR(100) NOT NULL DEFAULT "DEFAULT",
+pk INT NOT NULL,
+PRIMARY KEY (pk),
+KEY (col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES
+(1,"GOOD",1),(100,"",2),(200,"",3),(300,"",4),(400,"",5),(500,"",8);
+EXPLAIN SELECT t1.*,t2.* FROM t1 straight_join t2
+ON t2.col_int_key = t1.col_int WHERE t2.pk < t1.pk;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
+1	SIMPLE	t2	ref	PRIMARY,col_int_key	col_int_key	5	test.t1.col_int	1	Using where
+SELECT t1.*,t2.* FROM t1 straight_join t2
+ON t2.col_int_key = t1.col_int WHERE t2.pk < t1.pk;
+col_int	pk	col_int_key	col_varchar	pk
+1	6	1	GOOD	1
+# need FLUSH so that InnoDB statistics change and thus plan changes
+FLUSH TABLES;
+EXPLAIN SELECT t1.*,t2.* FROM t1 straight_join t2
+ON t2.col_int_key = t1.col_int WHERE t2.pk < t1.pk;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	
+1	SIMPLE	t2	ALL	PRIMARY,col_int_key	NULL	NULL	NULL	6	Range checked for each record (index map: 0x3)
+SELECT t1.*,t2.* FROM t1 straight_join t2
+ON t2.col_int_key = t1.col_int WHERE t2.pk < t1.pk;
+col_int	pk	col_int_key	col_varchar	pk
+1	6	1	GOOD	1
+DROP TABLE t1,t2;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/range_mrr_cost.result'
--- a/mysql-test/r/range_mrr_cost.result	2011-05-19 12:03:55 +0000
+++ b/mysql-test/r/range_mrr_cost.result	2011-08-05 14:30:29 +0000
@@ -1873,4 +1873,40 @@ SELECT * FROM t100 WHERE I <> 6 OR (I <>
 K	I	J
 101	8	26
 DROP TABLE t10,t100;
+#
+# BUG#12698916 - JOIN QUERY GIVES WRONG RESULT AT 2ND EXEC. OR
+# AFTER FLUSH TABLES [-INT VS NULL]
+#
+CREATE TABLE t1 (col_int INT, pk INT) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (-100,1),(1,6);
+CREATE TABLE t2 (
+col_int_key INT,
+col_varchar VARCHAR(100) NOT NULL DEFAULT "DEFAULT",
+pk INT NOT NULL,
+PRIMARY KEY (pk),
+KEY (col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES
+(1,"GOOD",1),(100,"",2),(200,"",3),(300,"",4),(400,"",5),(500,"",8);
+EXPLAIN SELECT t1.*,t2.* FROM t1 straight_join t2
+ON t2.col_int_key = t1.col_int WHERE t2.pk < t1.pk;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
+1	SIMPLE	t2	ref	PRIMARY,col_int_key	col_int_key	5	test.t1.col_int	1	Using where
+SELECT t1.*,t2.* FROM t1 straight_join t2
+ON t2.col_int_key = t1.col_int WHERE t2.pk < t1.pk;
+col_int	pk	col_int_key	col_varchar	pk
+1	6	1	GOOD	1
+# need FLUSH so that InnoDB statistics change and thus plan changes
+FLUSH TABLES;
+EXPLAIN SELECT t1.*,t2.* FROM t1 straight_join t2
+ON t2.col_int_key = t1.col_int WHERE t2.pk < t1.pk;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	
+1	SIMPLE	t2	ALL	PRIMARY,col_int_key	NULL	NULL	NULL	6	Range checked for each record (index map: 0x3)
+SELECT t1.*,t2.* FROM t1 straight_join t2
+ON t2.col_int_key = t1.col_int WHERE t2.pk < t1.pk;
+col_int	pk	col_int_key	col_varchar	pk
+1	6	1	GOOD	1
+DROP TABLE t1,t2;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/range_none.result'
--- a/mysql-test/r/range_none.result	2011-05-19 12:03:55 +0000
+++ b/mysql-test/r/range_none.result	2011-08-05 14:30:29 +0000
@@ -1872,4 +1872,40 @@ SELECT * FROM t100 WHERE I <> 6 OR (I <>
 K	I	J
 101	8	26
 DROP TABLE t10,t100;
+#
+# BUG#12698916 - JOIN QUERY GIVES WRONG RESULT AT 2ND EXEC. OR
+# AFTER FLUSH TABLES [-INT VS NULL]
+#
+CREATE TABLE t1 (col_int INT, pk INT) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (-100,1),(1,6);
+CREATE TABLE t2 (
+col_int_key INT,
+col_varchar VARCHAR(100) NOT NULL DEFAULT "DEFAULT",
+pk INT NOT NULL,
+PRIMARY KEY (pk),
+KEY (col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES
+(1,"GOOD",1),(100,"",2),(200,"",3),(300,"",4),(400,"",5),(500,"",8);
+EXPLAIN SELECT t1.*,t2.* FROM t1 straight_join t2
+ON t2.col_int_key = t1.col_int WHERE t2.pk < t1.pk;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
+1	SIMPLE	t2	ref	PRIMARY,col_int_key	col_int_key	5	test.t1.col_int	1	Using where
+SELECT t1.*,t2.* FROM t1 straight_join t2
+ON t2.col_int_key = t1.col_int WHERE t2.pk < t1.pk;
+col_int	pk	col_int_key	col_varchar	pk
+1	6	1	GOOD	1
+# need FLUSH so that InnoDB statistics change and thus plan changes
+FLUSH TABLES;
+EXPLAIN SELECT t1.*,t2.* FROM t1 straight_join t2
+ON t2.col_int_key = t1.col_int WHERE t2.pk < t1.pk;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	
+1	SIMPLE	t2	ALL	PRIMARY,col_int_key	NULL	NULL	NULL	6	Range checked for each record (index map: 0x3)
+SELECT t1.*,t2.* FROM t1 straight_join t2
+ON t2.col_int_key = t1.col_int WHERE t2.pk < t1.pk;
+col_int	pk	col_int_key	col_varchar	pk
+1	6	1	GOOD	1
+DROP TABLE t1,t2;
 set optimizer_switch=default;

=== modified file 'sql/opt_range.cc'
--- a/sql/opt_range.cc	2011-07-28 10:54:44 +0000
+++ b/sql/opt_range.cc	2011-08-05 14:30:29 +0000
@@ -1545,7 +1545,13 @@ end:
   head->prepare_for_position();
   head->file= org_file;
   bitmap_copy(&column_bitmap, head->read_set);
-  head->column_bitmaps_set(&column_bitmap, &column_bitmap);
+
+  /*
+    We have prepared a column_bitmap which get_next() will use. To do this we
+    used TABLE::read_set/write_set as playground; restore them to their
+    original value to not pollute other scans.
+  */
+  head->column_bitmaps_set(save_read_set, save_write_set);
 
   DBUG_RETURN(0);
 
@@ -1588,9 +1594,16 @@ int QUICK_ROR_INTERSECT_SELECT::init_ror
   }
   while ((quick= quick_it++))
   {
+#ifndef DBUG_OFF
+    const MY_BITMAP * const save_read_set= quick->head->read_set;
+    const MY_BITMAP * const save_write_set= quick->head->write_set;
+#endif
     if (quick->init_ror_merged_scan(FALSE))
       DBUG_RETURN(1);
     quick->file->extra(HA_EXTRA_KEYREAD_PRESERVE_FIELDS);
+    // Sets are shared by all members of "quick_selects" so must not change
+    DBUG_ASSERT(quick->head->read_set == save_read_set);
+    DBUG_ASSERT(quick->head->write_set == save_write_set);
     /* All merged scans share the same record buffer in intersection. */
     quick->record= head->record[0];
   }

No bundle (reason: useless for push emails).
Thread
bzr push into mysql-trunk branch (guilhem.bichot:3415 to 3417) Guilhem Bichot10 Aug