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 Bichot | 10 Aug |
