| Bug #54511 | Assertion failed: cache != 0L in file sql_select.cc::sub_select_cache on HAVING | ||
|---|---|---|---|
| Submitted: | 15 Jun 2010 11:23 | Modified: | 20 Nov 2010 23:23 |
| Reporter: | John Embretsen | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S1 (Critical) |
| Version: | mysql-next-mr-opt-backporting, 6.0.14 | OS: | Any |
| Assigned to: | Jørgen Løland | CPU Architecture: | Any |
[15 Jun 2010 12:04]
John Embretsen
Running EXPLAIN on the offending query results in the following:
mysql> EXPLAIN SELECT COUNT( table1 . `col_varchar_nokey` ) AS field1, COUNT( table1 . `pk` ) AS field2 FROM ( t1 AS table1 INNER JOIN t2 AS table2 ON (table2 . `pk` = table1 . `col_int_key` ) ) WHERE ( ( table2 . `col_varchar_nokey` , table1 . `col_varchar_key` ) IN ( SELECT 'x' , 'd' UNION SELECT 'n' , 'k' ) ) HAVING ( 'f', 'l' ) IN ( SELECT SUBQUERY2_t1 . `col_varchar_nokey` AS SUBQUERY2_field1 , SUBQUERY2_t1 . `col_varchar_key` AS SUBQUERY2_field2 FROM ( t2 AS SUBQUERY2_t1 STRAIGHT_JOIN ( t1 AS SUBQUERY2_t2 INNER JOIN t3 AS SUBQUERY2_t3 ON (SUBQUERY2_t3 . `pk` = SUBQUERY2_t2 . `col_int_nokey` ) ) ON (SUBQUERY2_t3 . `col_varchar_key` = SUBQUERY2_t2 . `col_varchar_key` ) ) GROUP BY SUBQUERY2_field1, SUBQUERY2_field2 )\G;
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: NULL
type: NULL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: NULL
Extra: Impossible HAVING noticed after reading const tables
*************************** 2. row ***************************
id: 4
select_type: DEPENDENT SUBQUERY
table: SUBQUERY2_t1
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 1
Extra: Using temporary; Using filesort
*************************** 3. row ***************************
id: 4
select_type: DEPENDENT SUBQUERY
table: SUBQUERY2_t2
type: ALL
possible_keys: col_varchar_key
key: NULL
key_len: NULL
ref: NULL
rows: 1
Extra: Using where; Using join buffer (BNL, incremental buffers)
*************************** 4. row ***************************
id: 4
select_type: DEPENDENT SUBQUERY
table: SUBQUERY2_t3
type: eq_ref
possible_keys: PRIMARY,col_varchar_key
key: PRIMARY
key_len: 4
ref: test.SUBQUERY2_t2.col_int_nokey
rows: 1
Extra: Using where
*************************** 5. row ***************************
id: 2
select_type: DEPENDENT SUBQUERY
table: NULL
type: NULL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: NULL
Extra: No tables used
*************************** 6. row ***************************
id: 3
select_type: DEPENDENT UNION
table: NULL
type: NULL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: NULL
Extra: No tables used
*************************** 7. row ***************************
id: NULL
select_type: UNION RESULT
table: <union2,3>
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: NULL
Extra:
7 rows in set (0.00 sec)
ERROR:
No query specified
[21 Jun 2010 6:39]
Jørgen Løland
Simple mtr test script that crashes with the same stack trace:
--source include/have_innodb.inc
SET optimizer_switch='materialization=off'; # <- turning mat on removes crash
CREATE TABLE t1 (
i int(11) NOT NULL
) ENGINE=InnoDB;
CREATE TABLE t2 (
v varchar(1)
) ENGINE=InnoDB;
CREATE TABLE t3 (
v varchar(1)
) ENGINE=InnoDB;
SELECT COUNT( i )
FROM t1
HAVING ( 'c' )
IN (
SELECT t2.v
FROM (t2 JOIN t3)
);
[21 Jun 2010 6:43]
Jørgen Løland
Explain of the crashing query with materialization on and off: materialization=on ------------------ id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible HAVING noticed after reading const tables 2 SUBQUERY t2 ALL NULL NULL NULL NULL 1 2 SUBQUERY t3 ALL NULL NULL NULL NULL 1 Using join buffer (BNL, regular buffers) materialization=off (crashes when not explain) ---------------------------------------------- id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible HAVING noticed after reading const tables 2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 1 Using where 2 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 1 Using join buffer (BNL, regular bu
[21 Jun 2010 11:23]
Jørgen Løland
bzrfind: Regression source: sergey.glukhov@sun.com-20100319092029-m2t1vgnovw5es2o6 which is a patch for BUG#48916
[21 Jun 2010 11:31]
Jørgen Løland
Offending commit: http://lists.mysql.com/commits/103797
[22 Jun 2010 11:26]
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/111754 3209 Jorgen Loland 2010-06-22 BUG#54511 - Assertion failed: cache != 0L in file sql_select.cc::sub_select_cache on HAVING In JOIN::optimize(), it is checked if the HAVING clause is impossible, in which case optimization can be stopped since the result is an empty set. However, the result of evaluating the HAVING clause (HAVING is FALSE) was not saved, and return_zero_rows() therefore had to reevaluate it to see if the query should return zero rows or one row with NULL values. In this bug, reevaluating the HAVING clause caused a crash because the HAVING clause contained a cached subselect which had been freed. The fix is to store the outcome of the impossible HAVING clause by setting having_value to Item::COND_FALSE. @ mysql-test/include/subquery_mat.inc Test for BUG#45411 @ mysql-test/r/having.result Updated EXPLAIN EXTENDED output to print "having 0" in cases where an impossible HAVING clause is detected. @ mysql-test/r/subquery_mat.result Test for BUG#45411 @ mysql-test/r/subquery_mat_all.result Test for BUG#45411 @ mysql-test/r/subquery_mat_none.result Test for BUG#45411 @ sql/sql_select.cc When JOIN::optimize detects that the HAVING clause is impossible, set having_value=Item::COND_FALSE and having=NULL.
[24 Jun 2010 11:44]
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/112073 3198 Jorgen Loland 2010-06-24 BUG#54511 - Assertion failed: cache != 0L in file sql_select.cc::sub_select_cache on HAVING Reported ASSERT has gone away after the fix for bug 51242 was merged to mysql-next-mr-opt-bugfixing. This patch adds a test case for bug 54511 as well as code cleanup that sets st_select_lex::having_value= JOIN::having_value (after evaluating the HAVING clause) and JOIN::having= NULL (if the HAVING clause is known to be false). @ mysql-test/include/subquery_mat.inc Test for BUG#45411 @ mysql-test/r/subquery_mat.result Test for BUG#45411 @ mysql-test/r/subquery_mat_all.result Test for BUG#45411 @ mysql-test/r/subquery_mat_none.result Test for BUG#45411 @ sql/sql_select.cc Code cleanup: Set st_select_lex::having_value= JOIN::having_value after evaluating JOIN::having by calling remove_eq_conds from JOIN::optimize. Set JOIN::having= NULL if the HAVING clause is known to be false.
[30 Jun 2010 6:28]
Jørgen Løland
The problem was apparently due to a bad merge of Gluh's patch to next-mr. The bug has been fixed by a recent merge to the opt-backporting tree.
Two patches will be pushed:
* A followup patch that removes the duplication of {having|cond}_value in select_lex and JOIN.
* The test case for code coverage
[30 Jun 2010 6:35]
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/112516 3203 Jorgen Loland 2010-06-30 BUG#54511 - Assertion failed: cache != 0L in file sql_select.cc::sub_select_cache on HAVING Bug fixed by Gluh as part of BUG 48916. This revision contains a test case for code coverage since the repo in 48916 looks different. @ mysql-test/include/subquery_mat.inc Test for BUG#54511 @ mysql-test/r/subquery_mat.result Test for BUG#54511 @ mysql-test/r/subquery_mat_all.result Test for BUG#54511 @ mysql-test/r/subquery_mat_none.result Test for BUG#54511
[30 Jun 2010 8:33]
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/112526 3275 Jorgen Loland 2010-06-30 Bug#54511: Assertion failed: cache != 0L in file sql_select.cc::sub_select_cache on HAVING Code cleanup followup patch: make JOIN::having_value and cond_value point to select_lex versions of the variables with same name to avoid duplication of information. Also add documentation to having_value and cond_value in st_select_lex and JOIN and to optimize_cond(). @ sql/sql_lex.h Add documentation to st_select_lex::having_value and cond_value @ sql/sql_select.cc Make JOIN::having_value and cond_value point to select_lex variables with same name and document optimize_cond() @ sql/sql_select.h Make JOIN::having_value and cond_value point to select_lex variables with same name and document how they work.
[1 Jul 2010 9:36]
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/112657 3278 Jorgen Loland 2010-07-01 Bug#54511: Assertion failed: cache != 0L in file sql_select.cc::sub_select_cache on HAVING Code cleanup followup patch: * remove JOIN::having_value and JOIN::cond_value and use select_lex variables with same name instead to avoid duplication of information. * Add documentation about the relationship between st_select_lex::{where|having} and JOIN::{conds|having} * Add documentation about the relationship between st_select_lex::{having|cond}_value and JOIN::{conds|having} @ sql/sql_lex.h Add documentation to st_select_lex::having_value and cond_value @ sql/sql_select.cc Remove usage of JOIN::having_value and cond_value and use select_lex variables with same name instead to avoid duplicate variables. Also documented optimize_cond() @ sql/sql_select.h Remove variables JOIN::having_value and cond_value and use select_lex variables with same name instead to avoid duplicate variables. Also documented the relationship between JOIN::{having|conds}, st_select_lex::{where|having} and st_select_lex::{cond|having}_value.
[23 Jul 2010 12:30]
Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100723121929-90e9zemk3jkr2ocy) (version source revid:vasil.dimov@oracle.com-20100531152341-x2d4hma644icamh1) (pib:18)
[4 Aug 2010 8:04]
Bugs System
Pushed into mysql-trunk 5.6.1-m4 (revid:alik@ibmvm-20100804080001-bny5271e65xo34ig) (version source revid:vasil.dimov@oracle.com-20100531152341-x2d4hma644icamh1) (merge vers: 5.5.5-m3) (pib:18)
[4 Aug 2010 8:20]
Bugs System
Pushed into mysql-trunk 5.6.1-m4 (revid:alik@ibmvm-20100804081533-c1d3rbipo9e8rt1s) (version source revid:vasil.dimov@oracle.com-20100531152341-x2d4hma644icamh1) (merge vers: 5.5.5-m3) (pib:18)
[13 Aug 2010 2:03]
Paul DuBois
Changes to test suite. No changelog entry needed.
[16 Aug 2010 6:33]
Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100816062819-bluwgdq8q4xysmlg) (version source revid:alik@sun.com-20100816062612-enatdwnv809iw3s9) (pib:20)
[13 Nov 2010 16:11]
Bugs System
Pushed into mysql-trunk 5.6.99-m5 (revid:alexander.nozdrin@oracle.com-20101113155825-czmva9kg4n31anmu) (version source revid:vasil.dimov@oracle.com-20100629074804-359l9m9gniauxr94) (merge vers: 5.6.99-m4) (pib:21)
Description: While executing a complex query with a HAVING clause and multiple subqueries, mysqld crashed as follows: Assertion failed: cache != 0L, file /export/home/tmp/je159969/mysql-dev/bzr-repos/mysql-next-mr-opt-backporting-custom/sql/sql_select.cc, line 16573 100615 10:52:12 - mysqld got signal 6 ; [12] _assert(0x0, 0x0, 0x0, 0x0, 0x0, 0x0), at 0xfffffd7fff1fdc95 [13] sub_select_cache(join = 0x3b09e40, join_tab = 0x3a99498, end_of_records = false), line 16573 in "sql_select.cc" [14] evaluate_join_record(join = 0x3b09e40, join_tab = 0x3a991e8, error = 0), line 17092 in "sql_select.cc" [15] sub_select(join = 0x3b09e40, join_tab = 0x3a991e8, end_of_records = false), line 16796 in "sql_select.cc" [16] do_select(join = 0x3b09e40, fields = (nil), table = 0x3a9bd90, procedure = (nil)), line 16334 in "sql_select.cc" [17] JOIN::exec(this = 0x3a8f620), line 2649 in "sql_select.cc" [18] subselect_single_select_engine::exec(this = 0x3a9e6e8), line 2320 in "item_subselect.cc" [19] Item_subselect::exec(this = 0x3a9e5a0), line 295 in "item_subselect.cc" [20] Item_in_subselect::exec(this = 0x3a9e5a0), line 345 in "item_subselect.cc" [21] Item_in_subselect::val_bool(this = 0x3a9e5a0), line 941 in "item_subselect.cc" [22] Item::val_bool_result(this = 0x3a9e5a0), line 851 in "item.h" [23] Item_in_optimizer::val_int(this = 0x3b561c0), line 1837 in "item_cmpfunc.cc" [24] return_zero_rows(join = 0x3b35fb0, result = 0x3a9e7d8, tables = 0x39b8710, fields = CLASS, send_row = true, select_options = 2147748608ULL, info = 0x1b7eb38 "Impossible HAVING noticed after reading const tables", having = 0x3b561c0), line 11292 in "sql_select.cc" [25] JOIN::exec(this = 0x3b35fb0), line 2570 in "sql_select.cc" [26] mysql_select(thd = 0x39ac580, rref_pointer_array = 0x39ae5c0, tables = 0x39b8710, wild_num = 0, fields = CLASS, conds = 0x3a58dc8, og_num = 0, order = (nil), group = (nil), having = 0x3a9e5a0, proc_param = (nil), select_options = 2147748608ULL, result = 0x3a9e7d8, unit = 0x39addb8, select_lex = 0x39ae3d8), line 3257 in "sql_select.cc" [27] handle_select(thd = 0x39ac580, lex = 0x39add10, result = 0x3a9e7d8, setup_tables_done_option = 0), line 301 in "sql_select.cc" [28] execute_sqlcom_select(thd = 0x39ac580, all_tables = 0x39b8710), line 4779 in "sql_parse.cc" [29] mysql_execute_command(thd = 0x39ac580), line 2260 in "sql_parse.cc" [30] mysql_parse(thd = 0x39ac580, inBuf = 0x39b75d0 "SELECT COUNT( table1 . `col_varchar_nokey` ) AS field1, COUNT( table1 . `pk` ) AS field2 FROM ( B AS table1 INNER JOIN C AS table2 ON (table2 . `pk` = table1 . `col_int_key` ) ) WHERE ( ( table2 . `col_varchar_nokey` , table1 . `col_varchar_key` ) IN ( SELECT 'x' , 'd' UNION SELECT 'n' , 'k' ) ) AND table1 . `col_varchar_key` IN ('z', 'b', 'w') HAVING ( 'f', 'l' ) IN ( SELECT SUBQUERY2_t1 . `col_varchar_nokey` AS SUBQUERY2_field1 , SUBQUERY2_t1 . `col_varchar_key` AS SUBQUERY2_field2 FROM ( C" ..., length = 780U, parser_state = 0xfffffd7fff03cab8), line 5808 in "sql_parse.cc" [31] dispatch_command(command = COM_QUERY, thd = 0x39ac580, packet = 0x39b35a1 " SELECT COUNT( table1 . `col_varchar_nokey` ) AS field1, COUNT( table1 . `pk` ) AS field2 FROM ( B AS table1 INNER JOIN C AS table2 ON (table2 . `pk` = table1 . `col_int_key` ) ) WHERE ( ( table2 . `col_varchar_nokey` , table1 . `col_varchar_key` ) IN ( SELECT 'x' , 'd' UNION SELECT 'n' , 'k' ) ) AND table1 . `col_varchar_key` IN ('z', 'b', 'w') HAVING ( 'f', 'l' ) IN ( SELECT SUBQUERY2_t1 . `col_varchar_nokey` AS SUBQUERY2_field1 , SUBQUERY2_t1 . `col_varchar_key` AS SUBQUERY2_field2 FROM (" ..., packet_length = 783U), line 1085 in "sql_parse.cc" [32] do_command(thd = 0x39ac580), line 771 in "sql_parse.cc" [33] do_handle_one_connection(thd_arg = 0x39ac580), line 1188 in "sql_connect.cc" [34] handle_one_connection(arg = 0x39ac580), line 1127 in "sql_connect.cc" The above was observed on Solaris using mysql-next-mr-opt-backporting revid jorgen.loland@sun.com-20100614075728-r36zunxrjgnlwyl6 With a slightly older revision on Linux, a related assert was hit, which could be the same issue (roughly the same stacktrace): mysqld: sql_select.cc:16542: enum_nested_loop_state sub_select_cache(JOIN*, JOIN_TAB*, bool): Assertion `cache != __null' failed. 100615 10:43:09 - mysqld got signal 6 ; Crash occurs even with optimizer_join_cache_level=1 and optimizer_switch=='default,semijoin=off,materialization=off,mrr=off,index_condition_pushdown=off' Not reproducible with mysql-next-mr or mysql-5.1-bugteam as of 2010-06-14. Reproducible with latest mysql-6.0-codebase-bugfixing. How to repeat: Using the Random Query Generator ($CODE is path to MySQL binaries): bzr branch lp:randgen cd randgen perl ./runall.pl \ --seed=1276587694 \ --threads=1 \ --grammar=conf/optimizer/optimizer_subquery.yy \ --engine=InnoDB \ --queries=1000000 \ --duration=1200 \ --basedir=$CODE \ --Reporter=Shutdown,Backtrace Using a mysql client: DROP TABLE IF EXISTS t1; DROP TABLE IF EXISTS t2; DROP TABLE IF EXISTS t3; CREATE TABLE t1 ( `pk` int(11) NOT NULL AUTO_INCREMENT, `col_int_nokey` int(11) DEFAULT NULL, `col_int_key` int(11) DEFAULT NULL, `col_varchar_key` varchar(1) DEFAULT NULL, `col_varchar_nokey` varchar(1) DEFAULT NULL, PRIMARY KEY (`pk`), KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`) ) ENGINE=InnoDB; CREATE TABLE t2 ( `pk` int(11) NOT NULL AUTO_INCREMENT, `col_int_nokey` int(11) DEFAULT NULL, `col_int_key` int(11) DEFAULT NULL, `col_varchar_key` varchar(1) DEFAULT NULL, `col_varchar_nokey` varchar(1) DEFAULT NULL, PRIMARY KEY (`pk`), KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`) ) ENGINE=InnoDB; INSERT INTO t2 VALUES (20,4,2,'d','d'); CREATE TABLE t3 ( `pk` int(11) NOT NULL AUTO_INCREMENT, `col_int_nokey` int(11) DEFAULT NULL, `col_int_key` int(11) DEFAULT NULL, `col_varchar_key` varchar(1) DEFAULT NULL, `col_varchar_nokey` varchar(1) DEFAULT NULL, PRIMARY KEY (`pk`), KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`) ) ENGINE=InnoDB; SELECT COUNT( table1 . `col_varchar_nokey` ) AS field1, COUNT( table1 . `pk` ) AS field2 FROM ( t1 AS table1 INNER JOIN t2 AS table2 ON (table2 . `pk` = table1 . `col_int_key` ) ) WHERE ( ( table2 . `col_varchar_nokey` , table1 . `col_varchar_key` ) IN ( SELECT 'x' , 'd' UNION SELECT 'n' , 'k' ) ) HAVING ( 'f', 'l' ) IN ( SELECT SUBQUERY2_t1 . `col_varchar_nokey` AS SUBQUERY2_field1 , SUBQUERY2_t1 . `col_varchar_key` AS SUBQUERY2_field2 FROM ( t2 AS SUBQUERY2_t1 STRAIGHT_JOIN ( t1 AS SUBQUERY2_t2 INNER JOIN t3 AS SUBQUERY2_t3 ON (SUBQUERY2_t3 . `pk` = SUBQUERY2_t2 . `col_int_nokey` ) ) ON (SUBQUERY2_t3 . `col_varchar_key` = SUBQUERY2_t2 . `col_varchar_key` ) ) GROUP BY SUBQUERY2_field1, SUBQUERY2_field2 ); Apologies for not providing a less complex reproducible query at this point. Some automatic simplification attempts end up with a different crash (Bug#52336 or Bug#53933). Replacing the "HAVING" keyword in the query above with "AND" does not result in a crash.