3742 Olav Sandstaa 2012-03-09
Fix for Bug#12667154 SAME QUERY EXEC AS WHERE SUBQ GIVES DIFFERENT
RESULTS ON IN() & NOT IN() COMP #3
Using the added test case to explain what happens and causes the
wrong result from the query when ICP is enabled:
1. The t3 table contains four records. The outer query will read
these and for each of these it will execute the subquery.
2. Before the first execution of the subquery it will be optimized. In
this case the important is what happens to the first table t1:
-make_join_select() will call the range optimizer which will decide
that t1 should be accessed using a range scan on the k1 index
(t1.i1 between 7 and 9). It creates a QUICK_RANGE_SELECT object
for this.
-As the last part of optimization the ICP code pushes the following
condition down to the storage engine for table t1 on the k1 index:
(t1.i1 between 7 and 9) and (t1.i1 is not null)
This produces the following information in the explain for this table:
2 DEPENDENT SUBQUERY t1 range k1 k1 5 NULL 3 Using index condition; Using filesort
Note the use of filesort.
(what happens to table t2 is not important for this example)
3. The first execution of the subquery does (among other things) due
to the need for sorting:
a. Call create_sort_index() which again will call find_all_keys():
b. find_all_keys() will read the required keys for all qualifying
rows from the storage engine. To do this it checks if it has a
quick-select for the table. It will use the quick-select for
reading records. In this case it will read four records from the
storage engine (based on the range criteria). The storage engine
will evaluate the pushed index condition for each record.
c. At the end of create_sort_index() there is code that cleans up a
lot of stuff on the join tab. One of the things that is cleaned
is the select object. The result of this is that the
quick-select object created in make_join_select is deleted.
4. The second execution of the subquery does the same as the first but
the result is different:
a. Call create_sort_index() which again will call find_all_keys()
(same as for the first execution)
b. find_all_keys() will read the keys from the storage engine. To
do this it checks if it has a quick-select for the table. Now
there are NO quick-select object(!) (since it was deleted in
step 3c). So find_all_keys defaults to read the table using a
table scan instead. Instead of reading the four relevant record
in the range it reads the entire table (6 records). It then
evaluates the table's condition (and here it goes wrong). Since
the entire condition has been pushed down to the storage engine
using ICP all 6 records qualify. (Note that the storage engine
will not evaluate the pushed index condition in this case since
it was pushed for the k1 index and now we do a table scan
without any index being used).
So the result is that here we return six qualifying key values
instead of four due to not evaluating the table's condition.
c. As above.
5. The two last execution of the subquery will also produce wrong results
for the same reason.
Summary: The problem occurs due to all but the first executions of the
subquery is done as a table scan without evaluating the table's
condition (which is pushed to the storage engine on a different
index). This is caused by the create_sort_index() function deleting
the quick-select object that should have been used for executing the
subquery as a range scan.
Note that this bug in addition to causing wrong results also can
result in bad performance due to executing the subquery using a table
scan instead of a range scan. This is also an issue in MySQL 5.5.
The fix for this problem is to avoid that the Quick-select-object that
the optimizer created is not deleted when create_sort_index() is doing
clean-up of the join-tab. This will ensure that the quick-select
object and the corresponding pushed index condition will be available
and used by all following executions of the subquery.
@ mysql-test/include/icp_tests.inc
Test case for Bug#12667154
@ mysql-test/r/innodb_icp.result
Test case for Bug#12667154
@ mysql-test/r/innodb_icp_all.result
Test case for Bug#12667154
@ mysql-test/r/innodb_icp_none.result
Test case for Bug#12667154
@ mysql-test/r/myisam_icp.result
Test case for Bug#12667154
@ mysql-test/r/myisam_icp_all.result
Test case for Bug#12667154
@ mysql-test/r/myisam_icp_none.result
Test case for Bug#12667154
@ sql/sql_executor.cc
Fix for Bug#12667154: Change how create_sort_index() cleans up the
join_tab's select and quick-select objects in order to avoid that a
quick-select object created outside of create_sort_index() is deleted.
modified:
mysql-test/include/icp_tests.inc
mysql-test/r/innodb_icp.result
mysql-test/r/innodb_icp_all.result
mysql-test/r/innodb_icp_none.result
mysql-test/r/myisam_icp.result
mysql-test/r/myisam_icp_all.result
mysql-test/r/myisam_icp_none.result
sql/sql_executor.cc
3741 Annamalai Gurusami 2012-03-09 [merge]
Merging from mysql-5.5 to mysql-trunk.
added:
mysql-test/suite/innodb/r/innodb_bug11766634.result
mysql-test/suite/innodb/t/innodb_bug11766634-master.opt
mysql-test/suite/innodb/t/innodb_bug11766634.test
modified:
storage/innobase/fsp/fsp0fsp.cc
=== modified file 'mysql-test/include/icp_tests.inc'
=== modified file 'mysql-test/include/icp_tests.inc'
--- a/mysql-test/include/icp_tests.inc 2012-02-14 14:42:12 +0000
+++ b/mysql-test/include/icp_tests.inc 2012-03-09 10:56:36 +0000
@@ -1150,3 +1150,53 @@
DROP FUNCTION f1;
DROP TABLE t1, t2;
+
+--echo #
+--echo # Bug#12667154 "SAME QUERY EXEC AS WHERE SUBQ GIVES DIFFERENT RESULTS
+--echo # ON IN() & NOT IN() COMP #3"
+--echo #
+
+# Need to avoid materialization for this bug to occur
+SET @save_optimizer_switch_bug12667154= @@optimizer_switch;
+SET SESSION optimizer_switch="materialization=off";
+
+CREATE TABLE t1 (
+ i1 INTEGER,
+ i2 INTEGER,
+ KEY k1 (i1)
+);
+
+INSERT INTO t1 VALUES (9,0), (7,1), (1,9), (7,101), (2,1), (9,102);
+
+CREATE TABLE t2 (
+ pk INTEGER,
+ i2 INTEGER,
+ PRIMARY KEY (pk)
+);
+
+INSERT INTO t2 VALUES (2,1), (3,2), (5,3), (6,4), (7,6), (9,4);
+
+CREATE TABLE t3 (
+ i1 INTEGER,
+ i2 INTEGER
+);
+
+INSERT INTO t3 VALUES (1,0), (1,1), (1,101), (1,102);
+
+let $query=
+SELECT * FROM t3
+WHERE (i1, i2) IN
+(
+ SELECT COUNT(DISTINCT t2.i2), t1.i2
+ FROM t1 JOIN t2 ON t1.i1 = t2.pk
+ WHERE t2.pk BETWEEN 7 AND 9
+ GROUP BY t1.i2
+);
+
+--eval EXPLAIN $query
+--eval $query
+
+DROP TABLE t1, t2, t3;
+
+# Restore optimizer switch setting
+SET optimizer_switch= @save_optimizer_switch_bug12667154;
=== modified file 'mysql-test/r/innodb_icp.result'
--- a/mysql-test/r/innodb_icp.result 2012-02-29 11:17:52 +0000
+++ b/mysql-test/r/innodb_icp.result 2012-03-09 10:56:36 +0000
@@ -1065,5 +1065,55 @@
5 5
DROP FUNCTION f1;
DROP TABLE t1, t2;
+#
+# Bug#12667154 "SAME QUERY EXEC AS WHERE SUBQ GIVES DIFFERENT RESULTS
+# ON IN() & NOT IN() COMP #3"
+#
+SET @save_optimizer_switch_bug12667154= @@optimizer_switch;
+SET SESSION optimizer_switch="materialization=off";
+CREATE TABLE t1 (
+i1 INTEGER,
+i2 INTEGER,
+KEY k1 (i1)
+);
+INSERT INTO t1 VALUES (9,0), (7,1), (1,9), (7,101), (2,1), (9,102);
+CREATE TABLE t2 (
+pk INTEGER,
+i2 INTEGER,
+PRIMARY KEY (pk)
+);
+INSERT INTO t2 VALUES (2,1), (3,2), (5,3), (6,4), (7,6), (9,4);
+CREATE TABLE t3 (
+i1 INTEGER,
+i2 INTEGER
+);
+INSERT INTO t3 VALUES (1,0), (1,1), (1,101), (1,102);
+EXPLAIN SELECT * FROM t3
+WHERE (i1, i2) IN
+(
+SELECT COUNT(DISTINCT t2.i2), t1.i2
+FROM t1 JOIN t2 ON t1.i1 = t2.pk
+WHERE t2.pk BETWEEN 7 AND 9
+GROUP BY t1.i2
+);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t3 ALL NULL NULL NULL NULL 4 Using where
+2 DEPENDENT SUBQUERY t1 range k1 k1 5 NULL 4 Using index condition; Using filesort
+2 DEPENDENT SUBQUERY t2 eq_ref PRIMARY PRIMARY 4 test.t1.i1 1 NULL
+SELECT * FROM t3
+WHERE (i1, i2) IN
+(
+SELECT COUNT(DISTINCT t2.i2), t1.i2
+FROM t1 JOIN t2 ON t1.i1 = t2.pk
+WHERE t2.pk BETWEEN 7 AND 9
+GROUP BY t1.i2
+);
+i1 i2
+1 0
+1 1
+1 101
+1 102
+DROP TABLE t1, t2, t3;
+SET optimizer_switch= @save_optimizer_switch_bug12667154;
set default_storage_engine= @save_storage_engine;
set optimizer_switch=default;
=== modified file 'mysql-test/r/innodb_icp_all.result'
--- a/mysql-test/r/innodb_icp_all.result 2012-02-29 11:17:52 +0000
+++ b/mysql-test/r/innodb_icp_all.result 2012-03-09 10:56:36 +0000
@@ -1065,5 +1065,55 @@
5 5
DROP FUNCTION f1;
DROP TABLE t1, t2;
+#
+# Bug#12667154 "SAME QUERY EXEC AS WHERE SUBQ GIVES DIFFERENT RESULTS
+# ON IN() & NOT IN() COMP #3"
+#
+SET @save_optimizer_switch_bug12667154= @@optimizer_switch;
+SET SESSION optimizer_switch="materialization=off";
+CREATE TABLE t1 (
+i1 INTEGER,
+i2 INTEGER,
+KEY k1 (i1)
+);
+INSERT INTO t1 VALUES (9,0), (7,1), (1,9), (7,101), (2,1), (9,102);
+CREATE TABLE t2 (
+pk INTEGER,
+i2 INTEGER,
+PRIMARY KEY (pk)
+);
+INSERT INTO t2 VALUES (2,1), (3,2), (5,3), (6,4), (7,6), (9,4);
+CREATE TABLE t3 (
+i1 INTEGER,
+i2 INTEGER
+);
+INSERT INTO t3 VALUES (1,0), (1,1), (1,101), (1,102);
+EXPLAIN SELECT * FROM t3
+WHERE (i1, i2) IN
+(
+SELECT COUNT(DISTINCT t2.i2), t1.i2
+FROM t1 JOIN t2 ON t1.i1 = t2.pk
+WHERE t2.pk BETWEEN 7 AND 9
+GROUP BY t1.i2
+);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t3 ALL NULL NULL NULL NULL 4 Using where
+2 DEPENDENT SUBQUERY t1 range k1 k1 5 NULL 4 Using index condition; Using filesort
+2 DEPENDENT SUBQUERY t2 eq_ref PRIMARY PRIMARY 4 test.t1.i1 1 NULL
+SELECT * FROM t3
+WHERE (i1, i2) IN
+(
+SELECT COUNT(DISTINCT t2.i2), t1.i2
+FROM t1 JOIN t2 ON t1.i1 = t2.pk
+WHERE t2.pk BETWEEN 7 AND 9
+GROUP BY t1.i2
+);
+i1 i2
+1 0
+1 1
+1 101
+1 102
+DROP TABLE t1, t2, t3;
+SET optimizer_switch= @save_optimizer_switch_bug12667154;
set default_storage_engine= @save_storage_engine;
set optimizer_switch=default;
=== modified file 'mysql-test/r/innodb_icp_none.result'
--- a/mysql-test/r/innodb_icp_none.result 2012-02-29 11:17:52 +0000
+++ b/mysql-test/r/innodb_icp_none.result 2012-03-09 10:56:36 +0000
@@ -1064,5 +1064,55 @@
5 5
DROP FUNCTION f1;
DROP TABLE t1, t2;
+#
+# Bug#12667154 "SAME QUERY EXEC AS WHERE SUBQ GIVES DIFFERENT RESULTS
+# ON IN() & NOT IN() COMP #3"
+#
+SET @save_optimizer_switch_bug12667154= @@optimizer_switch;
+SET SESSION optimizer_switch="materialization=off";
+CREATE TABLE t1 (
+i1 INTEGER,
+i2 INTEGER,
+KEY k1 (i1)
+);
+INSERT INTO t1 VALUES (9,0), (7,1), (1,9), (7,101), (2,1), (9,102);
+CREATE TABLE t2 (
+pk INTEGER,
+i2 INTEGER,
+PRIMARY KEY (pk)
+);
+INSERT INTO t2 VALUES (2,1), (3,2), (5,3), (6,4), (7,6), (9,4);
+CREATE TABLE t3 (
+i1 INTEGER,
+i2 INTEGER
+);
+INSERT INTO t3 VALUES (1,0), (1,1), (1,101), (1,102);
+EXPLAIN SELECT * FROM t3
+WHERE (i1, i2) IN
+(
+SELECT COUNT(DISTINCT t2.i2), t1.i2
+FROM t1 JOIN t2 ON t1.i1 = t2.pk
+WHERE t2.pk BETWEEN 7 AND 9
+GROUP BY t1.i2
+);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t3 ALL NULL NULL NULL NULL 4 Using where
+2 DEPENDENT SUBQUERY t1 range k1 k1 5 NULL 4 Using where; Using filesort
+2 DEPENDENT SUBQUERY t2 eq_ref PRIMARY PRIMARY 4 test.t1.i1 1 NULL
+SELECT * FROM t3
+WHERE (i1, i2) IN
+(
+SELECT COUNT(DISTINCT t2.i2), t1.i2
+FROM t1 JOIN t2 ON t1.i1 = t2.pk
+WHERE t2.pk BETWEEN 7 AND 9
+GROUP BY t1.i2
+);
+i1 i2
+1 0
+1 1
+1 101
+1 102
+DROP TABLE t1, t2, t3;
+SET optimizer_switch= @save_optimizer_switch_bug12667154;
set default_storage_engine= @save_storage_engine;
set optimizer_switch=default;
=== modified file 'mysql-test/r/myisam_icp.result'
--- a/mysql-test/r/myisam_icp.result 2012-02-29 11:17:52 +0000
+++ b/mysql-test/r/myisam_icp.result 2012-03-09 10:56:36 +0000
@@ -1060,4 +1060,54 @@
5 5
DROP FUNCTION f1;
DROP TABLE t1, t2;
+#
+# Bug#12667154 "SAME QUERY EXEC AS WHERE SUBQ GIVES DIFFERENT RESULTS
+# ON IN() & NOT IN() COMP #3"
+#
+SET @save_optimizer_switch_bug12667154= @@optimizer_switch;
+SET SESSION optimizer_switch="materialization=off";
+CREATE TABLE t1 (
+i1 INTEGER,
+i2 INTEGER,
+KEY k1 (i1)
+);
+INSERT INTO t1 VALUES (9,0), (7,1), (1,9), (7,101), (2,1), (9,102);
+CREATE TABLE t2 (
+pk INTEGER,
+i2 INTEGER,
+PRIMARY KEY (pk)
+);
+INSERT INTO t2 VALUES (2,1), (3,2), (5,3), (6,4), (7,6), (9,4);
+CREATE TABLE t3 (
+i1 INTEGER,
+i2 INTEGER
+);
+INSERT INTO t3 VALUES (1,0), (1,1), (1,101), (1,102);
+EXPLAIN SELECT * FROM t3
+WHERE (i1, i2) IN
+(
+SELECT COUNT(DISTINCT t2.i2), t1.i2
+FROM t1 JOIN t2 ON t1.i1 = t2.pk
+WHERE t2.pk BETWEEN 7 AND 9
+GROUP BY t1.i2
+);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t3 ALL NULL NULL NULL NULL 4 Using where
+2 DEPENDENT SUBQUERY t1 range k1 k1 5 NULL 3 Using index condition; Using filesort
+2 DEPENDENT SUBQUERY t2 eq_ref PRIMARY PRIMARY 4 test.t1.i1 1 NULL
+SELECT * FROM t3
+WHERE (i1, i2) IN
+(
+SELECT COUNT(DISTINCT t2.i2), t1.i2
+FROM t1 JOIN t2 ON t1.i1 = t2.pk
+WHERE t2.pk BETWEEN 7 AND 9
+GROUP BY t1.i2
+);
+i1 i2
+1 0
+1 1
+1 101
+1 102
+DROP TABLE t1, t2, t3;
+SET optimizer_switch= @save_optimizer_switch_bug12667154;
set optimizer_switch=default;
=== modified file 'mysql-test/r/myisam_icp_all.result'
--- a/mysql-test/r/myisam_icp_all.result 2012-02-29 11:17:52 +0000
+++ b/mysql-test/r/myisam_icp_all.result 2012-03-09 10:56:36 +0000
@@ -1060,4 +1060,54 @@
5 5
DROP FUNCTION f1;
DROP TABLE t1, t2;
+#
+# Bug#12667154 "SAME QUERY EXEC AS WHERE SUBQ GIVES DIFFERENT RESULTS
+# ON IN() & NOT IN() COMP #3"
+#
+SET @save_optimizer_switch_bug12667154= @@optimizer_switch;
+SET SESSION optimizer_switch="materialization=off";
+CREATE TABLE t1 (
+i1 INTEGER,
+i2 INTEGER,
+KEY k1 (i1)
+);
+INSERT INTO t1 VALUES (9,0), (7,1), (1,9), (7,101), (2,1), (9,102);
+CREATE TABLE t2 (
+pk INTEGER,
+i2 INTEGER,
+PRIMARY KEY (pk)
+);
+INSERT INTO t2 VALUES (2,1), (3,2), (5,3), (6,4), (7,6), (9,4);
+CREATE TABLE t3 (
+i1 INTEGER,
+i2 INTEGER
+);
+INSERT INTO t3 VALUES (1,0), (1,1), (1,101), (1,102);
+EXPLAIN SELECT * FROM t3
+WHERE (i1, i2) IN
+(
+SELECT COUNT(DISTINCT t2.i2), t1.i2
+FROM t1 JOIN t2 ON t1.i1 = t2.pk
+WHERE t2.pk BETWEEN 7 AND 9
+GROUP BY t1.i2
+);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t3 ALL NULL NULL NULL NULL 4 Using where
+2 DEPENDENT SUBQUERY t1 range k1 k1 5 NULL 3 Using index condition; Using filesort
+2 DEPENDENT SUBQUERY t2 eq_ref PRIMARY PRIMARY 4 test.t1.i1 1 NULL
+SELECT * FROM t3
+WHERE (i1, i2) IN
+(
+SELECT COUNT(DISTINCT t2.i2), t1.i2
+FROM t1 JOIN t2 ON t1.i1 = t2.pk
+WHERE t2.pk BETWEEN 7 AND 9
+GROUP BY t1.i2
+);
+i1 i2
+1 0
+1 1
+1 101
+1 102
+DROP TABLE t1, t2, t3;
+SET optimizer_switch= @save_optimizer_switch_bug12667154;
set optimizer_switch=default;
=== modified file 'mysql-test/r/myisam_icp_none.result'
--- a/mysql-test/r/myisam_icp_none.result 2012-02-29 11:17:52 +0000
+++ b/mysql-test/r/myisam_icp_none.result 2012-03-09 10:56:36 +0000
@@ -1059,4 +1059,54 @@
5 5
DROP FUNCTION f1;
DROP TABLE t1, t2;
+#
+# Bug#12667154 "SAME QUERY EXEC AS WHERE SUBQ GIVES DIFFERENT RESULTS
+# ON IN() & NOT IN() COMP #3"
+#
+SET @save_optimizer_switch_bug12667154= @@optimizer_switch;
+SET SESSION optimizer_switch="materialization=off";
+CREATE TABLE t1 (
+i1 INTEGER,
+i2 INTEGER,
+KEY k1 (i1)
+);
+INSERT INTO t1 VALUES (9,0), (7,1), (1,9), (7,101), (2,1), (9,102);
+CREATE TABLE t2 (
+pk INTEGER,
+i2 INTEGER,
+PRIMARY KEY (pk)
+);
+INSERT INTO t2 VALUES (2,1), (3,2), (5,3), (6,4), (7,6), (9,4);
+CREATE TABLE t3 (
+i1 INTEGER,
+i2 INTEGER
+);
+INSERT INTO t3 VALUES (1,0), (1,1), (1,101), (1,102);
+EXPLAIN SELECT * FROM t3
+WHERE (i1, i2) IN
+(
+SELECT COUNT(DISTINCT t2.i2), t1.i2
+FROM t1 JOIN t2 ON t1.i1 = t2.pk
+WHERE t2.pk BETWEEN 7 AND 9
+GROUP BY t1.i2
+);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t3 ALL NULL NULL NULL NULL 4 Using where
+2 DEPENDENT SUBQUERY t1 range k1 k1 5 NULL 3 Using where; Using filesort
+2 DEPENDENT SUBQUERY t2 eq_ref PRIMARY PRIMARY 4 test.t1.i1 1 NULL
+SELECT * FROM t3
+WHERE (i1, i2) IN
+(
+SELECT COUNT(DISTINCT t2.i2), t1.i2
+FROM t1 JOIN t2 ON t1.i1 = t2.pk
+WHERE t2.pk BETWEEN 7 AND 9
+GROUP BY t1.i2
+);
+i1 i2
+1 0
+1 1
+1 101
+1 102
+DROP TABLE t1, t2, t3;
+SET optimizer_switch= @save_optimizer_switch_bug12667154;
set optimizer_switch=default;
=== modified file 'sql/sql_executor.cc'
--- a/sql/sql_executor.cc 2012-03-01 12:58:27 +0000
+++ b/sql/sql_executor.cc 2012-03-09 10:56:36 +0000
@@ -3927,6 +3927,12 @@
TABLE *table;
SQL_SELECT *select;
JOIN_TAB *tab;
+ /*
+ If the select->quick object is created outside of create_sort_index()
+ we should not delete it on exit from this function.
+ */
+ bool keep_quick= true;
+
DBUG_ENTER("create_sort_index");
if (join->tables == join->const_tables)
@@ -3992,6 +3998,7 @@
get_quick_select_for_ref(thd, table, &tab->ref,
tab->found_records))))
goto err;
+ keep_quick= false;
}
}
@@ -4034,9 +4041,39 @@
tablesort_result_cache= table->sort.io_cache;
table->sort.io_cache= NULL;
- select->cleanup(); // filesort did select
- tab->select= 0;
- table->quick_keys.clear_all(); // as far as we cleanup select->quick
+ /*
+ If the quick select object was created outside of
+ create_sort_index then we need to save it to avoid that it will
+ be deleted when calling select->cleanup().
+ */
+ QUICK_SELECT_I *saved_quick= NULL;
+
+ if (select->quick && keep_quick)
+ {
+ saved_quick= select->quick;
+ select->quick= NULL;
+ saved_quick->range_end();
+ }
+ /*
+ filesort did select so we need to cleanup the select object.
+ Unless we have removed the pointer to the quick select object,
+ cleanup() will delete it.
+ */
+ select->cleanup();
+ // Now we can restore the quick select object
+ if (saved_quick)
+ tab->select->set_quick(saved_quick);
+ /*
+ The select object should now be ready for the next use. If
+ it is re-used then there exists a backup copy of this join
+ tab which has the pointer to it. The join tab will be restored
+ in JOIN::reset(). So here we just deletes the pointer to it.
+ */
+ tab->select= NULL;
+ // If we deleted the quick select object we need to clear quick_keys
+ if (!saved_quick)
+ table->quick_keys.clear_all();
+ // Restore the output resultset
table->sort.io_cache= tablesort_result_cache;
}
tab->set_condition(NULL, __LINE__);
No bundle (reason: useless for push emails).
| Thread |
|---|
| • bzr push into mysql-trunk branch (olav.sandstaa:3741 to 3742) Bug#12667154 | Olav Sandstaa | 12 Mar |
