| Bug #49867 | Assertion failed - QUICK_ROR_INTERSECT_SELECT::need_sorted_output | ||
|---|---|---|---|
| Submitted: | 22 Dec 2009 5:09 | Modified: | 25 Feb 2010 1:28 |
| Reporter: | Patrick Crews | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
| Version: | 6.0 | OS: | Any |
| Assigned to: | Jørgen Løland | CPU Architecture: | Any |
| Tags: | crashing bug, innodb tables, regression | ||
[22 Dec 2009 5:13]
Patrick Crews
Not present in 5.1
[22 Dec 2009 13:35]
Patrick Crews
Full crash output
Attachment: bug49867_crash_output.txt (text/plain), 17.42 KiB.
[22 Dec 2009 13:57]
Patrick Crews
Stack trace below. This bug is 6.0-codebase only and is not affected by the optimizer variables (optimizer_switch, optimizer_use_mrr, join_cache_level, engine_condition_pushdown)
[29 Dec 2009 17:25]
Philip Stoev
STack trace
#8 0x00637648 in *__GI___assert_fail (assertion=0x8fea0a1 "0", file=0x8fea095 "opt_range.h", line=522,
function=0x8feb580 "virtual void QUICK_ROR_INTERSECT_SELECT::need_sorted_output()") at assert.c:81
#9 0x086a48de in QUICK_ROR_INTERSECT_SELECT::need_sorted_output (this=0xa41d9d8) at opt_range.h:522
#10 0x08567cb5 in test_if_skip_sort_order (tab=0xa52f0dc, order=0xa41bb30, select_limit=18446744073709551615,
no_changes=false, map=0xa41cef8) at sql_select.cc:19258
#11 0x0851b80b in JOIN::exec (this=0xa529518) at sql_select.cc:2484
#12 0x0851f61e in mysql_select (thd=0xa451550, rref_pointer_array=0xa452ab8, tables=0xa41a640, wild_num=0, fields=...,
conds=0xa41b9d8, og_num=1, order=0xa41bb30, group=0x0, having=0x0, proc_param=0x0, select_options=2147748356,
result=0xa41bbc8, unit=0xa4524fc, select_lex=0xa4529b4) at sql_select.cc:3145
#13 0x0857803e in mysql_explain_union (thd=0xa451550, unit=0xa4524fc, result=0xa41bbc8) at sql_select.cc:22235
#14 0x0843c393 in execute_sqlcom_select (thd=0xa451550, all_tables=0xa41a640) at sql_parse.cc:4932
#15 0x0842ab6a in mysql_execute_command (thd=0xa451550) at sql_parse.cc:2148
#16 0x08440a07 in mysql_parse (thd=0xa451550,
inBuf=0xa41a338 "EXPLAIN SELECT table2 .`pk` field1 \nFROM C table1 LEFT JOIN L table2 ON table1 .`col_int_key` = table2 .`col_int_key` \nWHERE table2 .`col_varchar_10_latin1_key` IN ( 'e' ) AND table2 .`pk` IS"..., length=225,
found_semicolon=0xb1e7998c) at sql_parse.cc:5968
#17 0x08425ddf in dispatch_command (command=COM_QUERY, thd=0xa451550, packet=0xa4202b1 "", packet_length=228)
at sql_parse.cc:1073
#18 0x0842462f in do_command (thd=0xa451550) at sql_parse.cc:757
#19 0x0842181a in handle_one_connection (arg=0xa451550) at sql_connect.cc:1164
#20 0x0018b80e in start_thread (arg=0xb1e7a770) at pthread_create.c:300
#21 0x006e07ee in clone () at ../sysdeps/unix/sysv/linux/i386/clone.S:130
[6 Jan 2010 10:06]
Jørgen Løland
The assertion fires because we ask QUICK_ROR_INTERSECT_SELECT to produce ordered output for table2, but this access method cannot order. However, that's OK because the query in the test case produces 0 rows for table2 (JOIN_TAB type is JT_CONST). As such, no ordering is needed. Suggested fix: The access plan seems to be correct, we just need to avoid asking for ordered output in the case of JT_CONST and JT_SYSTEM.
[6 Jan 2010 11:51]
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/96108 3811 Jorgen Loland 2010-01-06 Bug#49867: "Assertion failed - QUICK_ROR_INTERSECT_SELECT::need_sorted_output" The bug is that QUICK_ROR_INTERSECT_SELECT is asked to produce ordered output by calling select->quick->need_sorted_output() in the end of test_if_skip_sort_order(). need_sorted_output() fires an ASSERT because this access method does not support ordering. When QUICK_ROR_INTERSECT_SELECT is chosen, filesort is normally used to provide ordering. However, when the result set is known to have no more than 1 row, filesort is skipped. This is the case in this bug report. The fix is to not ask the access method for an ordered result in test_if_skip_sort_order() if the JOIN_TAB type is JT_CONST or JT_SYSTEM. Both these are known to produce at most 1 record. @ mysql-test/r/innodb_mysql.result Added test for BUG#49867 @ mysql-test/t/innodb_mysql.test Added test for BUG#49867 @ sql/sql_select.cc Do not request ordered output from the access method if it is known to return at most one record.
[25 Jan 2010 10:07]
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/98024 3844 Jorgen Loland 2010-01-25 Bug#49867: "Assertion failed - QUICK_ROR_INTERSECT_SELECT::need_sorted_output" The bug is that QUICK_ROR_INTERSECT_SELECT is asked to produce ordered output by calling select->quick->need_sorted_output() in the end of test_if_skip_sort_order(). need_sorted_output() fires an ASSERT because this access method does not support ordering. When QUICK_ROR_INTERSECT_SELECT is chosen, filesort is normally used to provide ordering. However, when the result set is known to have no more than 1 row, filesort is skipped. This is the case in this bug report. The fix is to not ask the access method for an ordered result in test_if_skip_sort_order() if the JOIN_TAB type is JT_CONST or JT_SYSTEM. Both these are known to produce at most 1 record. @ mysql-test/r/innodb_mysql.result Added test for BUG#49867 @ mysql-test/t/innodb_mysql.test Added test for BUG#49867 @ sql/sql_select.cc Do not request ordered output from the access method if it is known to return at most one record.
[27 Jan 2010 6:27]
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/98275 3854 Jorgen Loland 2010-01-27 Bug#49867: "Assertion failed - QUICK_ROR_INTERSECT_SELECT::need_sorted_output" The bug is that QUICK_ROR_INTERSECT_SELECT is asked to produce ordered output by calling select->quick->need_sorted_output() in the end of test_if_skip_sort_order(). need_sorted_output() fires an ASSERT because this access method does not support ordering. When QUICK_ROR_INTERSECT_SELECT is chosen, filesort is normally used to provide ordering. However, when the result set is known to have no more than 1 row, filesort is skipped. This is the case in this bug report. The fix is to not ask the access method for an ordered result in test_if_skip_sort_order() if the JOIN_TAB type is JT_CONST or JT_SYSTEM. Both these are known to produce at most 1 record. @ mysql-test/r/innodb_mysql.result Added test for BUG#49867 @ mysql-test/t/innodb_mysql.test Added test for BUG#49867 @ sql/sql_select.cc * Fixed incorrect parameter usage in call to create_sort_index(). is_order_by param should be TRUE when used for ORDER BY, FALSE when used for GROUP BY * Do not request ordered output from the access method if it is known to return at most one record.
[27 Jan 2010 6:46]
Jørgen Løland
Pushed to 6.0-codebase-bugfixing
[13 Feb 2010 8:37]
Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20100213083436-9pesg4h55w1mekxc) (version source revid:luis.soares@sun.com-20100211135109-t63avry9fqpgyh78) (merge vers: 6.0.14-alpha) (pib:16)
[25 Feb 2010 1:28]
Paul DuBois
Noted in 6.0.14 changelog. For some queries, QUICK_ROR_INTERSELECT_SELECT was asked to provide sorted output. This raised an assertion because it does not support sorting.
Description: Crashing bug in QUICK_ROR_INTERSECT_SELECT::need_sorted_output (this=0x9f0e9d8) at opt_range.h:522 From the test case: SELECT table2 .`pk` field1 FROM C table1 LEFT JOIN L table2 ON table1 .`col_int_key` = table2 .`col_int_key` WHERE table2 .`col_varchar_10_latin1_key` IN ( 'e' ) AND table2 .`pk` IS NULL ORDER BY field1 ; Produces this crash output: Initial testing indicates that this bug requires the Innodb table. Still need to verify which versions of the server exhibit this bug and to determine if any optimizer switches will prevent us from hitting this bug. How to repeat: MTR test case. Run with loose-innodb #/* Server0: MySQL 6.0.14-alpha-gcov-debug-log */ /*!50400 SET SESSION optimizer_switch = 'firstmatch=on,index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,loosescan=on,materialization=on,semijoin=on' */; /*!50400 SET SESSION optimizer_use_mrr = 'force' */; /*!50400 SET SESSION engine_condition_pushdown = 'ON' */; /*!50400 SET SESSION join_cache_level = 1 */; /*!50400 SET SESSION debug = '' */; #/* Begin test case for query 0 */ --disable_warnings DROP TABLE /*! IF EXISTS */ C; DROP TABLE /*! IF EXISTS */ L; --enable_warnings CREATE TABLE `C` ( `col_varchar_10_latin1_key` varchar(10) DEFAULT NULL, `pk` int(11) NOT NULL AUTO_INCREMENT, `col_int_key` int(11) DEFAULT NULL, PRIMARY KEY (`pk`), KEY `col_varchar_10_latin1_key` (`col_varchar_10_latin1_key`), KEY `col_int_key` (`col_int_key`) ) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=latin1; INSERT INTO `C` VALUES ('a',1,2); CREATE TABLE `L` ( `col_int_key` int(11) DEFAULT NULL, `pk` int(11) NOT NULL AUTO_INCREMENT, `col_varchar_10_latin1_key` varchar(10) DEFAULT NULL, PRIMARY KEY (`pk`), KEY `col_int_key` (`col_int_key`), KEY `col_varchar_10_latin1_key` (`col_varchar_10_latin1_key`) ) ENGINE=InnoDB AUTO_INCREMENT=51 DEFAULT CHARSET=latin1; INSERT INTO `L` VALUES (NULL,1,'f'); INSERT INTO `L` VALUES (NULL,2,'u'); INSERT INTO `L` VALUES (4,3,'c'); INSERT INTO `L` VALUES (9,4,'k'); INSERT INTO `L` VALUES (1,5,'r'); INSERT INTO `L` VALUES (NULL,6,'f'); INSERT INTO `L` VALUES (4,7,'p'); INSERT INTO `L` VALUES (1,8,NULL); INSERT INTO `L` VALUES (7,9,'o'); INSERT INTO `L` VALUES (9,10,'s'); INSERT INTO `L` VALUES (NULL,11,'h'); INSERT INTO `L` VALUES (9,12,'c'); INSERT INTO `L` VALUES (NULL,13,'n'); INSERT INTO `L` VALUES (NULL,14,NULL); INSERT INTO `L` VALUES (NULL,15,'c'); INSERT INTO `L` VALUES (NULL,16,'p'); INSERT INTO `L` VALUES (1,17,'g'); INSERT INTO `L` VALUES (3,18,'u'); INSERT INTO `L` VALUES (NULL,19,NULL); INSERT INTO `L` VALUES (3,20,'t'); INSERT INTO `L` VALUES (7,21,'k'); INSERT INTO `L` VALUES (NULL,22,NULL); INSERT INTO `L` VALUES (7,23,NULL); INSERT INTO `L` VALUES (8,24,'e'); INSERT INTO `L` VALUES (6,25,'i'); INSERT INTO `L` VALUES (3,26,NULL); INSERT INTO `L` VALUES (8,27,'l'); INSERT INTO `L` VALUES (NULL,28,NULL); INSERT INTO `L` VALUES (NULL,29,NULL); INSERT INTO `L` VALUES (9,30,'e'); INSERT INTO `L` VALUES (5,31,'n'); INSERT INTO `L` VALUES (0,32,'t'); INSERT INTO `L` VALUES (6,33,NULL); INSERT INTO `L` VALUES (1,34,'x'); INSERT INTO `L` VALUES (8,35,'p'); INSERT INTO `L` VALUES (2,36,NULL); INSERT INTO `L` VALUES (6,37,'h'); INSERT INTO `L` VALUES (1,38,'w'); INSERT INTO `L` VALUES (8,39,'q'); INSERT INTO `L` VALUES (3,40,'n'); INSERT INTO `L` VALUES (3,41,'k'); INSERT INTO `L` VALUES (3,42,NULL); INSERT INTO `L` VALUES (NULL,43,'m'); INSERT INTO `L` VALUES (4,44,'k'); INSERT INTO `L` VALUES (5,45,'s'); INSERT INTO `L` VALUES (4,46,'g'); INSERT INTO `L` VALUES (5,47,NULL); INSERT INTO `L` VALUES (9,48,'p'); INSERT INTO `L` VALUES (NULL,49,NULL); INSERT INTO `L` VALUES (2,50,'h'); SELECT table2 .`pk` field1 FROM C table1 LEFT JOIN L table2 ON table1 .`col_int_key` = table2 .`col_int_key` WHERE table2 .`col_varchar_10_latin1_key` IN ( 'e' ) AND table2 .`pk` IS NULL ORDER BY field1 ; DROP TABLE C; DROP TABLE L; #/* End of test case for query 0 */