Bug #58243 | RQG test optimizer_subquery causes server crash when running with ICP | ||
---|---|---|---|
Submitted: | 16 Nov 2010 22:20 | Modified: | 11 Dec 2010 17:45 |
Reporter: | Olav Sandstå | Email Updates: | |
Status: | Closed | Impact on me: | |
Category: | MySQL Server: Optimizer | Severity: | S2 (Serious) |
Version: | 5.6.99 | OS: | Any |
Assigned to: | Olav Sandstå | CPU Architecture: | Any |
Tags: | index_condition_pushdown, optimizer_switch |
[16 Nov 2010 22:20]
Olav Sandstå
[16 Nov 2010 22:25]
Olav Sandstå
The query that causes this crash is: SELECT table1 . `col_varchar_key` AS field1 FROM ( D AS table1 RIGHT JOIN ( ( C AS table2 INNER JOIN CC AS table3 ON (table3 . `col_varchar_key` = table2 . `col_varchar_key` ) ) ) ON (table3 . `col_varchar_key` = table2 . `col_varchar_key` ) ) WHERE ( EXISTS ( ( SELECT SUBQUERY1_t2 . `pk` AS SUBQUERY1_field1 FROM ( C AS SUBQUERY1_t1 RIGHT OUTER JOIN D AS SUBQUERY1_t2 ON (SUBQUERY1_t2 . `pk` = SUBQUERY1_t1 . `col_int_key` ) ) ) ) ) AND table1 . `pk` > 80 AND table1 . `pk` < ( 80 + 237 ) AND ( table1 . `col_varchar_key` IS NULL AND ( 1, 2 ) IN ( SELECT SUBQUERY2_t1 . `pk` AS SUBQUERY2_field1 , COUNT( DISTINCT SUBQUERY2_t1 . `col_int_nokey` ) AS SUBQUERY2_field2 FROM ( CC AS SUBQUERY2_t1 INNER JOIN ( C AS SUBQUERY2_t2 INNER JOIN A AS SUBQUERY2_t3 ON (SUBQUERY2_t3 . `col_int_nokey` = SUBQUERY2_t2 . `pk` ) ) ON (SUBQUERY2_t3 . `col_int_nokey` = SUBQUERY2_t2 . `col_int_key` ) ) WHERE SUBQUERY2_t3 . `col_int_key` = SUBQUERY2_t2 . `col_int_key` ) ) GROUP BY field1 ORDER BY table1 . `col_datetime_key` , field1 LIMIT 1 OFFSET 7 and the pushed index condition that causes the assert in InnoDB: ((`test`.`table1`.`pk` > 80) and (`test`.`table1`. `pk` < (80 + 237)) and isnull(`test`.`table1`.`col_varchar_key`) and <in_optimizer>((1,2),(1,2) in (select `test`.`SUBQUERY2_t1`.`pk` AS `SUBQUERY2_field1`,count(distinct `test`.`SUBQUERY2_t1`.`col_int_nokey`) AS `SUBQUERY2_field2` from (`test`.`CC` `SUBQUERY2_t1` join (`test`.`C` `SUBQUERY2_t2` join `test`.`A` `SUBQ UERY2_t3` on((`test`.`SUBQUERY2_t3`.`col_int_nokey` = `test`.`SUBQUERY2_t2`.`pk`))) on((`test`.`SUBQUERY2_ t3`.`col_int_nokey` = `test`.`SUBQUERY2_t2`.`col_int_key`))) where (`test`.`SUBQUERY2_t3`.`col_int_key` = `test`.`SUBQUERY2_t2`.`col_int_key`))))
[17 Nov 2010 13:29]
Olav Sandstå
Simplified version of the test case that creates the same crash: CREATE TABLE t1 ( pk INTEGER NOT NULL, c1 INTEGER NOT NULL, c2 INTEGER NOT NULL, PRIMARY KEY (pk) ) ENGINE=InnoDB; INSERT INTO t1 VALUES (1,6,7); CREATE TABLE t2 ( c1 INTEGER NOT NULL ) ENGINE=InnoDB; SELECT t1.c1 FROM t1 WHERE t1.pk < 317 AND 2 IN (SELECT COUNT(t2.c1) FROM t2) ORDER BY t1.c2;
[17 Nov 2010 13:31]
Olav Sandstå
Explain output from running the simplified version of test case: id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Using filesort 2 SUBQUERY t2 ALL NULL NULL NULL NULL 1
[17 Nov 2010 13:48]
Olav Sandstå
The index condition we push down to InnoDb on table t1 on the primary key looks like: ((`test`.`t1`.`pk` < 317) and <in_optimizer>(2,2 in (select count(`test`.`t2`.`c1`) from `test`.`t2`)))
[17 Nov 2010 21:29]
Olav Sandstå
The subselect gets pushed down to InnoDB for the following reason: 1. In uses_index_fields_only() the following item get evaluated: <in_optimizer>(2,2 in (select count(`test`.`t2`.`c1`) from `test`.`t2`)) 2. The top level item object representing this item has the following type: class Item_in_optimizer 3. The first check that is done in uses_index_fields_only() is the following code: if (item->const_item()) return TRUE; 4. The implementation of const_item() is the following: virtual bool Item_func::const_item() const { return const_item_cache; } (see sql/item_func.h) 5. In this situation the const_item_cache member is true. So the const_item() method will return true and thus uses_index_fields_only() will return TRUE for the entire item subtree. Because of this is a const item it then will be included in the index condition that can be "safely" pushed down to the storage engine,
[18 Nov 2010 9:07]
Olav Sandstå
Adding the following test for whether the item tree contains a subselect or not as the very first check in uses_index_fields_only() (in sql_select.cc): if (item->with_subselect) return false; solves this problem and the part of the table´s where condition containing the subselect will no longer get pushed down. With this change the reproduction test above passes and the optimizer_subquery test is able to proceed much longer.
[18 Nov 2010 10:29]
Olav Sandstå
Even with the extra test show above the optimizer_subquery test eventually is able to find a query that hits the same assert in InnoDB. The new query looks like: SELECT STRAIGHT_JOIN table1 . `col_datetime_key` AS field1 FROM ( C AS table1 INNER JOIN BB AS table2 ON (table2 . `pk` = table1 . `pk` ) ) WHERE ( NOT EXISTS ( SELECT DISTINCT SUBQUERY1_t1 . `col_varchar_key` AS SUBQUERY1_field1 FROM ( D AS SUBQUERY1_t1 INNER JOIN CC AS SUBQUERY1_t2 ON (SUBQUERY1_t2 . `col_varchar_nokey` = SUBQUERY1_t1 . `col_varchar_key` ) ) WHERE SUBQUERY1_t1 . `col_varchar_nokey` != table1 . `col_varchar_key` AND SUBQUERY1_t1 . `col_varchar_nokey` = ( SELECT MIN( CHILD_SUBQUERY1_t1 . `col_varchar_nokey` ) AS CHILD_SUBQUERY1_field1 FROM ( CC AS CHILD_SUBQUERY1_t1 INNER JOIN C AS CHILD_SUBQUERY1_t2 ON (CHILD_SUBQUERY1_t2 . `col_int_nokey` = CHILD_SUBQUERY1_t1 . `pk` ) ) ) ) ) AND table1 . `col_varchar_key` >= 's' GROUP BY field1 HAVING (field1 > 'f' AND field1 != 9) ORDER BY table1 . `col_date_key` ASC , field1 LIMIT 100
[18 Nov 2010 11:32]
Olav Sandstå
Simplified version of the second RQG generated SQL statement (see previous comment): CREATE TABLE t1 ( i1 INTEGER NOT NULL, c1 VARCHAR(1) NOT NULL ) ENGINE=InnoDB; INSERT INTO t1 VALUES (2,'w'); CREATE TABLE t2 ( i1 INTEGER NOT NULL, c1 VARCHAR(1) NOT NULL, c2 VARCHAR(1) NOT NULL, KEY (c1, i1) ) ENGINE=InnoDB; INSERT INTO t2 VALUES (8,'d','d'); INSERT INTO t2 VALUES (4,'v','v'); CREATE TABLE t3 ( c1 VARCHAR(1) NOT NULL ) ENGINE=InnoDB; INSERT INTO t3 VALUES ('v'); SELECT i1 FROM t1 WHERE EXISTS (SELECT t2.c1 FROM (t2 INNER JOIN t3 ON (t3.c1 = t2.c1)) WHERE t2.c2 != t1.c1 AND t2.c2 = (SELECT MIN(t3.c1) FROM t3));
[18 Nov 2010 11:54]
Olav Sandstå
The explain for the simplified version of the second SQL query: id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 1 Using where 2 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 1 2 DEPENDENT SUBQUERY t2 ref c1 c1 3 test.t3.c1 1 Using index condition; Using where 3 SUBQUERY t3 ALL NULL NULL NULL NULL 1 and the index condition that is pushed down on t2's index is: ((select min(`test`.`t3`.`c1`) from `test`.`t3`) <> `test`.`t1`.`c1`)
[22 Nov 2010 22:39]
Olav Sandstå
The second case hitting the assert is occurring after uses_index_fields_only() has evaluated if the following condition can be pushed down on the index for t2: ((select min(`test`.`t3`.`c1`) from `test`.`t3`) <> `test`.`t1`.`c1`) The following code in uses_index_fields_only(): if (!(item->used_tables() & tbl->map)) return other_tbls_ok; returns TRUE in this case (which leads to the subselect being pushed down to InnoDB). This is caused by item->used_tables() gives a "wrong" information about which tables that are used by to evaluate this item. In this case item->used_tables() returns that no tables are used. The item above has also not the "with_subselect" member set so the proposed code for detecting whether it includes a subselect or not (see above) does not detect this situation.
[23 Nov 2010 15:08]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/124743 3287 Olav Sandstaa 2010-11-23 Fix for Bug#58243 RQG test optimizer_subquery causes server crash when running with ICP The crash was due to hitting an assert in InnoDB that checked that the same transaction could only have one active data access to InnoDB. In these cases there were two ongoing data accesses by the same transaction. This happened because the server was pushing down an index condition that contained a subquery. When InnoDB executed the index condition call back function this would result in a second call to InnoDB due to executing the subquery from within the index conditon function. To avoid this problem we should not push down index conditions that contains an subquery. The existing code for determining which part of a table's where condition that can be pushed down already had code for handling this but due to inconsistent data in the condition's item tree (or wrong use of methods on it) this code did not work as expected. Two cases where this happened (see the code in uses_index_fields_only()): 1. Test for constant items: if (item->const_item()) return TRUE; If the item tree contains a subquery the call to const_item() could in some cases return true even when the subquery has to be executed later. In this case we would include the subquery in the pushed index condition. Fix for this problem: Check the item's with_subselect field. If this is true then do not include it, other let the item tree be included. 2. Test for which tables the item tree contains: if (!(item->used_tables() & tbl->map)) return other_tbls_ok; This code would accept to include an item tree if it did not use the table we are using for push down. If the item tree contained a subquery then the used_tables() method can return the wrong set of tables and this could result in that the item was wrongly included in the condition that was pushed down. Fix for this problem: Remove this test. This will result in that we will potentially recursively traverse more of the item tree. The evaluation will be done by the main switch statement and the decision about whether to accept accesses to data from other tables will be done on the field item. Two test cases are included that would trigger the two cases. @ mysql-test/include/icp_tests.inc Test case for Bug#58243 RQG test optimizer_subquery causes server crash when running with ICP. Two test cases are added that covers each of the two situations where a subquery wrongly could be included in the pushed down index condition. @ mysql-test/r/innodb_icp.result Result file for Bug#58243 RQG test optimizer_subquery causes server crash when running with ICP. @ mysql-test/r/innodb_icp_all.result Result file for Bug#58243 RQG test optimizer_subquery causes server crash when running with ICP. @ mysql-test/r/innodb_icp_none.result Result file for Bug#58243 RQG test optimizer_subquery causes server crash when running with ICP. @ mysql-test/r/myisam_icp.result Result file for Bug#58243 RQG test optimizer_subquery causes server crash when running with ICP. @ mysql-test/r/myisam_icp_all.result Result file for Bug#58243 RQG test optimizer_subquery causes server crash when running with ICP. @ mysql-test/r/myisam_icp_none.result Result file for Bug#58243 RQG test optimizer_subquery causes server crash when running with ICP. @ mysql-test/r/subquery_all.result After the fix for Bug#58243: Change in explain due to the subquery of the following statement no longer get pushed down to the storage engine as part of index condition pushdown: SELECT id FROM t1 where salary = (SELECT MAX(salary) FROM t1); @ mysql-test/r/subquery_all_jcl6.result After the fix for Bug#58243: Change in explain due to the subquery of the following statement no longer get pushed down to the storage engine as part of index condition pushdown: SELECT id FROM t1 where salary = (SELECT MAX(salary) FROM t1); @ mysql-test/r/subquery_nomat_nosj.result After the fix for Bug#58243: Change in explain due to the subquery of the following statement no longer get pushed down to the storage engine as part of index condition pushdown: SELECT id FROM t1 where salary = (SELECT MAX(salary) FROM t1); @ mysql-test/r/subquery_nomat_nosj_jcl6.result After the fix for Bug#58243: Change in explain due to the subquery of the following statement no longer get pushed down to the storage engine as part of index condition pushdown: SELECT id FROM t1 where salary = (SELECT MAX(salary) FROM t1); @ sql/sql_select.cc Changes how uses_index_fields_only() handles conditions containing a subquery: 1. Before accepting to include a query that is const: also check that it does not contain a sub query by checking the item's with_subselect flag. 2. Do not use the item->used_tables() to determine if this query will only access "other tables" than the current table. This method could return wrong (or missing data) about the actual tables needed to execute the item tree. Instead we let the main switch handle this.
[24 Nov 2010 22:21]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/124947 3288 Olav Sandstaa 2010-11-24 Fix for Bug#58243 RQG test optimizer_subquery causes server crash when running with ICP The crash was due to hitting an assert in InnoDB that checked that the same transaction could only have one active data access to InnoDB. In these cases there were two ongoing data accesses by the same transaction. This happened because the server was pushing down an index condition that contained a subquery. When InnoDB executed the index condition call back function this would result in a second call to InnoDB due to executing the subquery from within the index conditon function. To avoid this problem we should not push down index conditions that contains an subquery. The existing code for determining which part of a table's where condition that can be pushed down already had code for handling this but due to inconsistent data in the condition's item tree (or wrong use of methods on it) this code did not work as expected. Two cases where this happened (see the code in uses_index_fields_only()): 1. Test for constant items: if (item->const_item()) return TRUE; If the item tree contains a subquery the call to const_item() could in some cases return true even when the subquery has to be executed later. In this case we would include the subquery in the pushed index condition. Fix for this problem: Check the item's with_subselect field. If this is true then do not include it, other let the item tree be included. 2. Test for which tables the item tree contains: if (!(item->used_tables() & tbl->map)) return other_tbls_ok; This code would accept to include an item tree if it did not use the table we are using for push down. If the item tree contained a subquery then the used_tables() method can return the wrong set of tables and this could result in that the item was wrongly included in the condition that was pushed down. Fix for this problem: Remove this test. This will result in that we will potentially recursively traverse more of the item tree. The evaluation will be done by the main switch statement and the decision about whether to accept accesses to data from other tables will be done on the field item. Two test cases are included that would trigger the two cases. @ mysql-test/include/icp_tests.inc Test case for Bug#58243 RQG test optimizer_subquery causes server crash when running with ICP. Two test cases are added that covers each of the two situations where a subquery wrongly could be included in the pushed down index condition. @ mysql-test/r/innodb_icp.result Result file for Bug#58243 RQG test optimizer_subquery causes server crash when running with ICP. @ mysql-test/r/innodb_icp_all.result Result file for Bug#58243 RQG test optimizer_subquery causes server crash when running with ICP. @ mysql-test/r/innodb_icp_none.result Result file for Bug#58243 RQG test optimizer_subquery causes server crash when running with ICP. @ mysql-test/r/myisam_icp.result Result file for Bug#58243 RQG test optimizer_subquery causes server crash when running with ICP. @ mysql-test/r/myisam_icp_all.result Result file for Bug#58243 RQG test optimizer_subquery causes server crash when running with ICP. @ mysql-test/r/myisam_icp_none.result Result file for Bug#58243 RQG test optimizer_subquery causes server crash when running with ICP. @ mysql-test/r/subquery_all.result After the fix for Bug#58243: Change in explain due to the subquery of the following statement no longer get pushed down to the storage engine as part of index condition pushdown: SELECT id FROM t1 where salary = (SELECT MAX(salary) FROM t1); @ mysql-test/r/subquery_all_jcl6.result After the fix for Bug#58243: Change in explain due to the subquery of the following statement no longer get pushed down to the storage engine as part of index condition pushdown: SELECT id FROM t1 where salary = (SELECT MAX(salary) FROM t1); @ mysql-test/r/subquery_nomat_nosj.result After the fix for Bug#58243: Change in explain due to the subquery of the following statement no longer get pushed down to the storage engine as part of index condition pushdown: SELECT id FROM t1 where salary = (SELECT MAX(salary) FROM t1); @ mysql-test/r/subquery_nomat_nosj_jcl6.result After the fix for Bug#58243: Change in explain due to the subquery of the following statement no longer get pushed down to the storage engine as part of index condition pushdown: SELECT id FROM t1 where salary = (SELECT MAX(salary) FROM t1); @ sql/sql_select.cc Changes how uses_index_fields_only() handles conditions containing a subquery: 1. Before accepting to include a query that is const: also check that it does not contain a sub query by checking the item's with_subselect flag. 2. Do not use the item->used_tables() to determine if this query will only access "other tables" than the current table. This method could return wrong (or missing data) about the actual tables needed to execute the item tree. Instead we let the main switch handle this.
[24 Nov 2010 22:30]
Olav Sandstå
Patch pushed to mysql-next-mr-opt-backporting with revision id: olav.sandstaa@oracle.com-20101124222027-11hi9hwgbg6ut2kb .
[5 Dec 2010 12:39]
Bugs System
Pushed into mysql-trunk 5.6.1 (revid:alexander.nozdrin@oracle.com-20101205122447-6x94l4fmslpbttxj) (version source revid:alexander.nozdrin@oracle.com-20101205122447-6x94l4fmslpbttxj) (merge vers: 5.6.1) (pib:23)
[11 Dec 2010 17:45]
Paul DuBois
Bug does not appear in any released 5.6.x version. No changelog entry needed.