| Bug #48709 | Assertion failed in sql_select.cc:11782: int join_read_key(JOIN_TAB*) | ||
|---|---|---|---|
| Submitted: | 12 Nov 2009 0:20 | Modified: | 12 Mar 2010 16:53 |
| Reporter: | Patrick Crews | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
| Version: | 5.0, 5.1 | OS: | Any |
| Assigned to: | Georgi Kodinov | CPU Architecture: | Any |
| Tags: | crashing bug | ||
[12 Nov 2009 0:21]
Patrick Crews
full crash output (from MTR run of the test case)
Attachment: bug48709_crash_output.txt (text/plain), 12.03 KiB.
[12 Nov 2009 11:40]
Konstantin Osipov
This assertion was introduced by my fix for Bug#41756. It should not affect production, however.
[13 Nov 2009 20:18]
Philip Stoev
my gut feeling is that this is also reproducible with 6.0, just not with the default optimizer settings. If EXPLAIN on the crashing query shows that some optimization is triggered in 6.0, turning this optimization off should cause the assertion to happen in 6.0 as well.
[13 Nov 2009 21:03]
Patrick Crews
When testing against 6.0, the following settings were used - my apologies for neglecting to post these in the initial report: SET GLOBAL OPTIMIZER_SWITCH = 'materialization=off,semijoin=off,loosescan=off,firstmatch=off'; SET GLOBAL optimizer_use_mrr = 'disable'; SET GLOBAL debug = '+d,optimizer_no_icp'; SET GLOBAL engine_condition_pushdown = 1; SET GLOBAL join_cache_level = 1;
[3 Dec 2009 15:52]
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/92706 2859 Georgi Kodinov 2009-12-03 Bug #48709: Assertion failed in sql_select.cc:11782: int join_read_key(JOIN_TAB*) The eq_ref access method TABLE_REF (accessed through JOIN_TAB) to save state and to track if this is the first row it finds or not. This state was not reset on subquery re-execution causing an assert. Fixed by resetting the state before the subquery re-execution.
[3 Dec 2009 15:53]
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/92708 2859 Georgi Kodinov 2009-12-03 Bug #48709: Assertion failed in sql_select.cc:11782: int join_read_key(JOIN_TAB*) The eq_ref access method TABLE_REF (accessed through JOIN_TAB) to save state and to track if this is the first row it finds or not. This state was not reset on subquery re-execution causing an assert. Fixed by resetting the state before the subquery re-execution.
[14 Dec 2009 12:01]
Tor Didriksen
This bug is indeed present in next-mr and 6.0-codebase also.
Randgen with subquery_semijoin.yy generates queries which expose this bug.
A stripped down example:
SELECT BIT_OR( OUTR . `col_time_key` ) AS X FROM C AS OUTR2
LEFT JOIN C AS OUTR
ON ( OUTR2 . `col_varchar_nokey` <> OUTR . `col_varchar_nokey` )
WHERE ( OUTR . `col_int_key` , OUTR . `col_int_key` )
IN ( SELECT DISTINCT INNR . `pk` AS X ,
INNR . `col_int_key` AS Y FROM CC AS INNR
WHERE INNR . `col_varchar_key` <= 'z'
)
AND OUTR . `col_int_nokey` >= 7 XOR OUTR . `col_date_nokey` = '2007-10-18'
[14 Dec 2009 12:03]
Tor Didriksen
The ASSERT affects only debug builds, but we will get wrong use_count and this affects row locking/unlocking??
[15 Dec 2009 15:58]
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/94175 2822 Georgi Kodinov 2009-12-15 Bug #48709: Assertion failed in sql_select.cc:11782: int join_read_key(JOIN_TAB*) The eq_ref access method TABLE_REF (accessed through JOIN_TAB) to save state and to track if this is the first row it finds or not. This state was not reset on subquery re-execution causing an assert. Fixed by resetting the state before the subquery re-execution.
[15 Dec 2009 16:10]
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/94183 3276 Georgi Kodinov 2009-12-15 Bug #48709: Assertion failed in sql_select.cc:11782: int join_read_key(JOIN_TAB*) The eq_ref access method TABLE_REF (accessed through JOIN_TAB) to save state and to track if this is the first row it finds or not. This state was not reset on subquery re-execution causing an assert. Fixed by resetting the state before the subquery re-execution.
[15 Dec 2009 17:10]
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/94217 2859 Georgi Kodinov 2009-12-15 Bug #48709: Assertion failed in sql_select.cc:11782: int join_read_key(JOIN_TAB*) The eq_ref access method TABLE_REF (accessed through JOIN_TAB) to save state and to track if this is the first row it finds or not. This state was not reset on subquery re-execution causing an assert. Fixed by resetting the state before the subquery re-execution.
[15 Dec 2009 17:11]
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/94219 2822 Georgi Kodinov 2009-12-15 Bug #48709: Assertion failed in sql_select.cc:11782: int join_read_key(JOIN_TAB*) The eq_ref access method TABLE_REF (accessed through JOIN_TAB) to save state and to track if this is the first row it finds or not. This state was not reset on subquery re-execution causing an assert. Fixed by resetting the state before the subquery re-execution.
[19 Dec 2009 8:29]
Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20091219082307-f3i4fn0tm8trb3c0) (version source revid:alik@sun.com-20091216180721-eoa754i79j4ssd3m) (merge vers: 6.0.14-alpha) (pib:15)
[19 Dec 2009 8:33]
Bugs System
Pushed into 5.5.1-m2 (revid:alik@sun.com-20091219082021-f34nq4jytwamozz0) (version source revid:alexey.kopytov@sun.com-20091216134707-o96eqw0u2ynvo9gm) (merge vers: 5.5.0-beta) (pib:15)
[19 Dec 2009 8:37]
Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20091219082213-nhjjgmphote4ntxj) (version source revid:alik@sun.com-20091216180221-a5ps59gajad3pip9) (pib:15)
[14 Jan 2010 8:27]
Bugs System
Pushed into 5.0.90 (revid:joro@sun.com-20100114082402-05fod2h6z9x9wok8) (version source revid:joro@sun.com-20091215171006-60wvk91kktlhviit) (merge vers: 5.0.89) (pib:16)
[15 Jan 2010 9:01]
Bugs System
Pushed into 5.1.43 (revid:joro@sun.com-20100115085139-qkh0i0fpohd9u9p5) (version source revid:joro@sun.com-20091216090324-hh91ouoj4gxk12eq) (merge vers: 5.1.42) (pib:16)
[16 Jan 2010 1:51]
Paul DuBois
Noted in 5.0.90, 5.1.43, 5.5.1, 6.0.14 changelogs. Incomplete reset of internal TABLE structures could cause a crash with eq_ref table access in subqueries. Setting report to NDI pending push to Celosia.
[12 Mar 2010 14:15]
Bugs System
Pushed into 5.1.44-ndb-7.0.14 (revid:jonas@mysql.com-20100312135944-t0z8s1da2orvl66x) (version source revid:jonas@mysql.com-20100312115609-woou0te4a6s4ae9y) (merge vers: 5.1.44-ndb-7.0.14) (pib:16)
[12 Mar 2010 14:31]
Bugs System
Pushed into 5.1.44-ndb-6.2.19 (revid:jonas@mysql.com-20100312134846-tuqhd9w3tv4xgl3d) (version source revid:jonas@mysql.com-20100312060623-mx6407w2vx76h3by) (merge vers: 5.1.44-ndb-6.2.19) (pib:16)
[12 Mar 2010 14:47]
Bugs System
Pushed into 5.1.44-ndb-6.3.33 (revid:jonas@mysql.com-20100312135724-xcw8vw2lu3mijrhn) (version source revid:jonas@mysql.com-20100312103652-snkltsd197l7q2yg) (merge vers: 5.1.44-ndb-6.3.33) (pib:16)
[12 Mar 2010 16:53]
Paul DuBois
Fixed in earlier 5.1.x, 5.5.x.
Description: Crashing bug in 5.1 and 5.0 - *not* present in 6.0-codebase Query: 'SELECT `int_key` FROM C WHERE `int_key` != ALL ( SELECT SUBQUERY3_t1 .`pk` FROM CC SUBQUERY3_t1 JOIN C SUBQUERY3_t2 ON SUBQUERY3_t2 .`int_key` ) ORDER BY `pk` ' Causes the following crash output (full output attached as separate file): mysqld: sql_select.cc:11782: int join_read_key(JOIN_TAB*): Assertion `tab->ref.has_record' failed. 091112 3:12:18 - mysqld got signal 6 ; <snip> Thread 1 (process 4062): #0 0xb7fbd430 in __kernel_vsyscall () #1 0xb7f9f1c8 in pthread_kill () from /lib/tls/i686/cmov/libpthread.so.0 #2 0x0910782b in my_write_core (sig=6) at stacktrace.c:310 #3 0x0847c068 in handle_segfault (sig=6) at mysqld.cc:2570 #4 <signal handler called> #5 0xb7fbd430 in __kernel_vsyscall () #6 0xb7dd06d0 in raise () from /lib/tls/i686/cmov/libc.so.6 #7 0xb7dd2098 in abort () from /lib/tls/i686/cmov/libc.so.6 #8 0xb7dc95ce in __assert_fail () from /lib/tls/i686/cmov/libc.so.6 #9 0x086104ed in join_read_key (tab=0xb0dff28) at sql_select.cc:11782 #10 0x0860b28c in sub_select (join=0xb0e0758, join_tab=0xb0dff28, end_of_records=false) at sql_select.cc:11247 #11 0x0863b876 in do_select (join=0xb0e0758, fields=0xb0d30c4, table=0x0, procedure=0x0) at sql_select.cc:11004 #12 0x086699c0 in JOIN::exec (this=0xb0e0758) at sql_select.cc:2255 #13 0x0839a5c2 in subselect_single_select_engine::exec (this=0xb0d3ce8) at item_subselect.cc:1968 #14 0x083a87db in Item_subselect::exec (this=0xb0d3c38) at item_subselect.cc:261 #15 0x08395236 in Item_in_subselect::val_bool (this=0xb0d3c38) at item_subselect.cc:861 #16 0x0825b601 in Item::val_bool_result (this=0xb0d3c38) at item.h:738 #17 0x082e6011 in Item_in_optimizer::val_int (this=0xb0d3fd0) at item_cmpfunc.cc:1703 #18 0x0822d5e1 in Item::val_bool (this=0xb0d3fd0) at item.cc:184 #19 0x082e6507 in Item_func_not::val_int (this=0xb0d3d10) at item_cmpfunc.cc:284 #20 0x0866af84 in SQL_SELECT::skip_record (this=0xb0d4920) at opt_range.h:711 #21 0x0881cb83 in find_all_keys (param=0xb747c66c, select=0xb0d4920, sort_keys=0xb0f3950, buffpek_pointers=0xb747c73c, tempfile=0xb747c800, indexfile=0x0) at filesort.cc:607 #22 0x0881ea0f in filesort (thd=0xb0a1f58, table=0xb0f56a8, sortorder=0xb0d4a28, s_length=1, select=0xb0d4920, max_rows=18446744073709551615, sort_positions=false, examined_rows=0xb747c948) at filesort.cc:243 #23 0x0861e2a4 in create_sort_index (thd=0xb0a1f58, join=0xb0de770, order=0xb0d3e28, filesort_limit=18446744073709551615, select_limit=18446744073709551615, is_order_by=false) at sql_select.cc:13653 #24 0x086692c8 in JOIN::exec (this=0xb0de770) at sql_select.cc:2198 #25 0x0865ad51 in mysql_select (thd=0xb0a1f58, rref_pointer_array=0xb0a33f4, tables=0xb0d2d98, wild_num=0, fields=@0xb0a3390, conds=0xb0d3d10, og_num=1, order=0xb0d3e28, group=0x0, having=0x0, proc_param=0x0, select_options=2147764736, result=0xb0d3ec8, unit=0xb0a308c, select_lex=0xb0a32fc) at sql_select.cc:2444 #26 0x0866a10c in handle_select (thd=0xb0a1f58, lex=0xb0a3030, result=0xb0d3ec8, setup_tables_done_option=0) at sql_select.cc:269 #27 0x084ae6b5 in execute_sqlcom_select (thd=0xb0a1f58, all_tables=0xb0d2d98) at sql_parse.cc:5051 #28 0x084b47c6 in mysql_execute_command (thd=0xb0a1f58) at sql_parse.cc:2246 #29 0x084d31cc in mysql_parse (thd=0xb0a1f58, inBuf=0xb0d2b10 "SELECT `int_key` \nFROM C \nWHERE `int_key` != ALL ( \nSELECT SUBQUERY3_t1 .`pk` \nFROM CC SUBQUERY3_t1 JOIN C SUBQUERY3_t2 ON SUBQUERY3_t2 .`int_key` ) \nORDER BY `pk`", length=174, found_semicolon=0xb747e050) at sql_parse.cc:5970 #30 0x084d5c81 in dispatch_command (command=COM_QUERY, thd=0xb0a1f58, packet=0xb0c6c81 "SELECT `int_key` \nFROM C \nWHERE `int_key` != ALL ( \nSELECT SUBQUERY3_t1 .`pk` \nFROM CC SUBQUERY3_t1 JOIN C SUBQUERY3_t2 ON SUBQUERY3_t2 .`int_key` ) \nORDER BY `pk` ", packet_length=177) at sql_parse.cc:1231 #31 0x084d9c6b in do_command (thd=0xb0a1f58) at sql_parse.cc:872 #32 0x0849b826 in handle_one_connection (arg=0xb0a1f58) at sql_connect.cc:1127 #33 0xb7f9a4ff in start_thread () from /lib/tls/i686/cmov/libpthread.so.0 #34 0xb7e8949e in clone () from /lib/tls/i686/cmov/libc.so.6 How to repeat: #/* Server0: MySQL 5.1.41-gcov-debug-log */ /*!50400 SET SESSION optimizer_switch = 'index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on' */; /*!50400 SET SESSION engine_condition_pushdown = 'ON' */; #/* Begin test case for query 0 */ --disable_warnings DROP TABLE /*! IF EXISTS */ CC; DROP TABLE /*! IF EXISTS */ C; --enable_warnings CREATE TABLE `CC` ( `pk` int(11) NOT NULL AUTO_INCREMENT, `int_key` int(11) DEFAULT NULL, PRIMARY KEY (`pk`), KEY `int_key` (`int_key`), KEY `varchar_key` (`int_key`) ) ENGINE=MyISAM AUTO_INCREMENT=30 DEFAULT CHARSET=latin1; INSERT INTO `CC` VALUES (10,8); INSERT INTO `CC` VALUES (11,9); INSERT INTO `CC` VALUES (12,9); INSERT INTO `CC` VALUES (13,186); INSERT INTO `CC` VALUES (14,NULL); INSERT INTO `CC` VALUES (15,2); INSERT INTO `CC` VALUES (16,3); INSERT INTO `CC` VALUES (17,0); INSERT INTO `CC` VALUES (18,133); INSERT INTO `CC` VALUES (19,1); INSERT INTO `CC` VALUES (20,8); INSERT INTO `CC` VALUES (21,5); INSERT INTO `CC` VALUES (22,5); INSERT INTO `CC` VALUES (23,8); INSERT INTO `CC` VALUES (24,6); INSERT INTO `CC` VALUES (25,51); INSERT INTO `CC` VALUES (26,4); INSERT INTO `CC` VALUES (27,7); INSERT INTO `CC` VALUES (28,6); INSERT INTO `CC` VALUES (29,4); CREATE TABLE `C` ( `pk` int(11) NOT NULL AUTO_INCREMENT, `int_key` int(11) DEFAULT NULL, PRIMARY KEY (`pk`), KEY `int_key` (`int_key`), KEY `varchar_key` (`int_key`) ) ENGINE=MyISAM AUTO_INCREMENT=21 DEFAULT CHARSET=latin1; INSERT INTO `C` VALUES (1,2); INSERT INTO `C` VALUES (2,9); INSERT INTO `C` VALUES (3,3); INSERT INTO `C` VALUES (4,9); INSERT INTO `C` VALUES (5,NULL); INSERT INTO `C` VALUES (6,9); INSERT INTO `C` VALUES (7,3); INSERT INTO `C` VALUES (8,8); INSERT INTO `C` VALUES (9,8); INSERT INTO `C` VALUES (10,53); INSERT INTO `C` VALUES (11,0); INSERT INTO `C` VALUES (12,5); INSERT INTO `C` VALUES (13,166); INSERT INTO `C` VALUES (14,3); INSERT INTO `C` VALUES (15,0); INSERT INTO `C` VALUES (16,1); INSERT INTO `C` VALUES (17,9); INSERT INTO `C` VALUES (18,5); INSERT INTO `C` VALUES (19,6); INSERT INTO `C` VALUES (20,2); SELECT `int_key` FROM C WHERE `int_key` != ALL ( SELECT SUBQUERY3_t1 .`pk` FROM CC SUBQUERY3_t1 JOIN C SUBQUERY3_t2 ON SUBQUERY3_t2 .`int_key` ) ORDER BY `pk` ; DROP TABLE CC; DROP TABLE C; #/* End of test case for query 0 */