| Bug #50335 | Assertion `!(order->used & map)' in eq_ref_table | ||
|---|---|---|---|
| Submitted: | 14 Jan 2010 15:42 | Modified: | 20 Jun 2010 22:39 |
| Reporter: | Philip Stoev | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S2 (Serious) |
| Version: | 5.0.91, 5.1.44, next-mr | OS: | Any |
| Assigned to: | Alexey Kopytov | CPU Architecture: | Any |
[21 Jan 2010 21:06]
Marc ALFF
Analysis: ========= The following test: drop table if exists test.EVENTS_WAITS_HISTORY; CREATE TABLE test.EVENTS_WAITS_HISTORY ( `THREAD_ID` int(11) NOT NULL, `EVENT_ID` bigint(20) unsigned NOT NULL, `EVENT_NAME` varchar(128) NOT NULL, `SOURCE` varchar(64) DEFAULT NULL, `TIMER_START` bigint(20) unsigned DEFAULT NULL, `TIMER_END` bigint(20) unsigned DEFAULT NULL, `TIMER_WAIT` bigint(20) unsigned DEFAULT NULL, `SPINS` int(10) unsigned DEFAULT NULL, `OBJECT_SCHEMA` varchar(64) DEFAULT NULL, `OBJECT_NAME` varchar(512) DEFAULT NULL, `OBJECT_TYPE` varchar(64) DEFAULT NULL, `OBJECT_INSTANCE_BEGIN` bigint(20) NOT NULL, `NESTING_EVENT_ID` bigint(20) unsigned DEFAULT NULL, `OPERATION` varchar(16) NOT NULL, `NUMBER_OF_BYTES` bigint(20) unsigned DEFAULT NULL, `FLAGS` int(10) unsigned DEFAULT NULL ) ENGINE=MYISAM DEFAULT CHARSET=utf8; insert into test.EVENTS_WAITS_HISTORY( THREAD_ID, EVENT_ID, EVENT_NAME, SOURCE, TIMER_START, TIMER_END, TIMER_WAIT, SPINS, OBJECT_SCHEMA, OBJECT_TYPE, OBJECT_INSTANCE_BEGIN, NESTING_EVENT_ID, OPERATION, NUMBER_OF_BYTES, FLAGS) select THREAD_ID, EVENT_ID, EVENT_NAME, SOURCE, TIMER_START, TIMER_END, TIMER_WAIT, SPINS, OBJECT_SCHEMA, OBJECT_TYPE, OBJECT_INSTANCE_BEGIN, NESTING_EVENT_ID, OPERATION, NUMBER_OF_BYTES, FLAGS from performance_schema.EVENTS_WAITS_HISTORY; SELECT * FROM test .`EVENTS_WAITS_HISTORY` JOIN mysql .`time_zone_transition` ON `Transition_time` = `OBJECT_NAME` WHERE NOT ( NOT 2 OR `OBJECT_NAME` != `Time_zone_id` ) ORDER BY `Transition_type_id` , `OBJECT_NAME` ; drop table test.EVENTS_WAITS_HISTORY; crashes exactly in the same place, but does not involve a performance schema table. This seems to be an optimizer bug.
[21 Jan 2010 22:47]
Marc ALFF
Reduced the test case further, and verified in 5.0.91 drop table if exists test.t1; create table test.t1( `OBJECT_NAME` varchar(512) DEFAULT NULL, foo int); insert into t1 values (NULL, 1); insert into t1 values (NULL, 2); insert into t1 values (NULL, 3); insert into t1 values (NULL, 4); insert into t1 values (NULL, 5); insert into t1 values (NULL, 6); insert into t1 values (NULL, 7); SELECT * FROM test .t1 JOIN mysql .`time_zone_transition` ON `Transition_time` = `OBJECT_NAME` WHERE NOT ( NOT 2 OR `OBJECT_NAME` != `Time_zone_id` ) ORDER BY `Transition_type_id` , `OBJECT_NAME` ; drop table test.t1;
[21 Jan 2010 23:03]
Marc ALFF
Crashes in 5.1.44 as well.
[22 Jan 2010 10:03]
Guilhem Bichot
Same stack trace as BUG#49570, which has been fixed in 5.1, so maybe check if it fixes this one too.
[22 Jan 2010 15:26]
MySQL Verification Team
a much nicer testcase: drop table if exists `t1`; create table `t1`(`a` int,`b` int, primary key (`a`,`b`))engine=innodb; insert into `t1` values (); select 1 from `t1` `a` join `t1` `b` using(`a`) where `b`.`b`=`a`.`a` group by `a`.`a`,`b`.`a`; bug #49570 testcase didn't crash in my recent 5.1.43, but the above did.
[25 Feb 2010 15:49]
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/101472 3354 Alexey Kopytov 2010-02-25 Bug #50335: Assertion `!(order->used & map)' in eq_ref_table The problem was in an incorrect debug assertion. The expression used in the failing assertion states that when finding references matching ORDER BY expressions, there can be only one reference to a single table. But that does not make any sense, all test cases for this bug are valid examples with multiple identical WHERE expressions referencing the same table which are also present in the ORDER BY list. Fixed by removing the failing assertion. We also have to take care of the 'found' counter so that we count multiple references only once. We rely on this fact later in eq_ref_table(). @ mysql-test/r/join.result Added a test case for bug #50335. @ mysql-test/t/join.test Added a test case for bug #50335. @ sql/sql_select.cc Removing the assertion in eq_ref_table() as it does not make any sense. We also have to take care of the 'found' counter so that we count multiple references only once. We rely on this fact later in eq_ref_table().
[25 Feb 2010 16:25]
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/101491 3922 Alexey Kopytov 2010-02-25 [merge] Manual merge of the patch for bug #50335. Also picked the test case for bug #45195 that was previously null-merged to mysql-pe.
[1 Mar 2010 8:46]
Bugs System
Pushed into 5.1.45 (revid:joro@sun.com-20100301083827-xnimmrjg6bh33o1o) (version source revid:alexey.kopytov@sun.com-20100225162630-c9av8dkhy9mausyw) (merge vers: 5.1.45) (pib:16)
[2 Mar 2010 14:36]
Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20100302142746-u1gxdf5yk2bjrq3e) (version source revid:alik@sun.com-20100301095421-4cz64ibem1h2quve) (merge vers: 6.0.14-alpha) (pib:16)
[2 Mar 2010 14:41]
Bugs System
Pushed into 5.5.3-m2 (revid:alik@sun.com-20100302072233-t3uqgjzdukt1pyhe) (version source revid:alexey.kopytov@sun.com-20100226130631-8czhisohzf6jyo2x) (merge vers: 5.5.3-m2) (pib:16)
[2 Mar 2010 14:46]
Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100302072432-k8xvfkgcggkwgi94) (version source revid:alik@sun.com-20100301093944-a4rvrmqqco6c0qao) (pib:16)
[8 Apr 2010 17:42]
Paul DuBois
Noted in 5.1.45, 5.5.3, 6.0.14 changelogs. A debugging assertion was incorrectly raised in the optimizer when matching ORDER BY expressions.
[26 Apr 2010 20:06]
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/106567 2858 Alexey Kopytov 2010-04-27 Backport of the fix for bug #50335 to 5.0. The problem was in an incorrect debug assertion. The expression used in the failing assertion states that when finding references matching ORDER BY expressions, there can be only one reference to a single table. But that does not make any sense, all test cases for this bug are valid examples with multiple identical WHERE expressions referencing the same table which are also present in the ORDER BY list. Fixed by removing the failing assertion. We also have to take care of the 'found' counter so that we count multiple references only once. We rely on this fact later in eq_ref_table(). @ mysql-test/r/join.result Added a test case for bug #50335. @ mysql-test/t/join.test Added a test case for bug #50335. @ sql/sql_select.cc Removing the assertion in eq_ref_table() as it does not make any sense. We also have to take care of the 'found' counter so that we count multiple references only once. We rely on this fact later in eq_ref_table().
[1 May 2010 13:47]
Bugs System
Pushed into 5.0.91 (revid:joro@sun.com-20100501134604-ra243s5b389j6ttn) (version source revid:alexey.kopytov@sun.com-20100426200600-op06qy98llzpzgl1) (merge vers: 5.0.91) (pib:16)
[4 May 2010 14:18]
Paul DuBois
Noted in 5.0.91 changelog.
[5 May 2010 15:09]
Bugs System
Pushed into 5.1.47 (revid:joro@sun.com-20100505145753-ivlt4hclbrjy8eye) (version source revid:vvaintroub@mysql.com-20100427092945-q8gxqolhp3prtb1s) (merge vers: 5.1.47) (pib:16)
[28 May 2010 6:03]
Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100524190136-egaq7e8zgkwb9aqi) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (pib:16)
[28 May 2010 6:31]
Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20100524190941-nuudpx60if25wsvx) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[28 May 2010 6:59]
Bugs System
Pushed into 5.5.5-m3 (revid:alik@sun.com-20100524185725-c8k5q7v60i5nix3t) (version source revid:alexey.kopytov@sun.com-20100429203306-tg0wz4y2xyx8edrl) (merge vers: 5.5.5-m3) (pib:16)
[30 May 2010 0:32]
Paul DuBois
Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug. Re-closing.
[17 Jun 2010 12:07]
Bugs System
Pushed into 5.1.47-ndb-7.0.16 (revid:martin.skold@mysql.com-20100617114014-bva0dy24yyd67697) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[17 Jun 2010 12:52]
Bugs System
Pushed into 5.1.47-ndb-6.2.19 (revid:martin.skold@mysql.com-20100617115448-idrbic6gbki37h1c) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[17 Jun 2010 13:34]
Bugs System
Pushed into 5.1.47-ndb-6.3.35 (revid:martin.skold@mysql.com-20100617114611-61aqbb52j752y116) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
Description: The following query crashes the server: SELECT * FROM performance_schema .`EVENTS_WAITS_HISTORY` JOIN mysql .`time_zone_transition` ON `Transition_time` = `OBJECT_NAME` WHERE NOT ( NOT 2 OR `OBJECT_NAME` != `Time_zone_id` ) ORDER BY `Transition_type_id` , `OBJECT_NAME` ; assertion: mysqld: sql_select.cc:7099: bool eq_ref_table(JOIN*, ORDER*, JOIN_TAB*): Assertion `!(order->used & map)' failed. backtrace: #6 0x000000315a42bec9 in __assert_fail () from /lib64/libc.so.6 #7 0x00000000005f4be3 in eq_ref_table (join=0x2b35100, start_order=0x2b315a8, tab=0x2b39338) at sql_select.cc:7099 #8 0x00000000005f4ce6 in only_eq_ref_tables (join=0x2b35100, order=0x2b315a8, tables=1) at sql_select.cc:7130 #9 0x00000000005f9f63 in remove_const (join=0x2b35100, first_order=0x2b315a8, cond=0x2b38368, change_list=true, simple_order=0x2b366b2) at sql_select.cc:7266 #10 0x000000000060d58b in JOIN::optimize (this=0x2b35100) at sql_select.cc:1100 #11 0x0000000000612723 in mysql_select (thd=0x2aa7850, rref_pointer_array=0x2aa95d8, tables=0x2b2fcc0, wild_num=1, fields=<value optimized out>, conds=0x2b31300, og_num=2, order=0x2b315a8, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x2b317e8, unit=0x2aa8fb8, select_lex=0x2aa9408) at sql_select.cc:2452 #12 0x0000000000615783 in handle_select (thd=0x2aa7850, lex=0x2aa8f18, result=0x2b317e8, setup_tables_done_option=0) at sql_select.cc:271 #13 0x000000000059dc06 in execute_sqlcom_select (thd=0x2aa7850, all_tables=0x2b2fcc0) at sql_parse.cc:4953 #14 0x00000000005a6a27 in mysql_execute_command (thd=0x2aa7850) at sql_parse.cc:2135 #15 0x00000000005a73c5 in mysql_parse (thd=0x2aa7850, inBuf=0x2b2f8c0 "SELECT *\nFROM performance_schema .`EVENTS_WAITS_HISTORY` JOIN mysql .`time_zone_transition` ON `Transition_time` = `OBJECT_NAME`\nWHERE NOT ( NOT 2 OR `OBJECT_NAME` != `Time_zone_id` )\nORDER "..., length=240, found_semicolon=0x7f22b152bf88) at sql_parse.cc:5987 #16 0x00000000005a843a in dispatch_command (command=COM_QUERY, thd=0x2aa7850, packet=0x2b27891 "SELECT *\nFROM performance_schema .`EVENTS_WAITS_HISTORY` JOIN mysql .`time_zone_transition` ON `Transition_time` = `OBJECT_NAME`\nWHERE NOT ( NOT 2 OR `OBJECT_NAME` != `Time_zone_id` )\nORDER "..., packet_length=243) at sql_parse.cc:1140 #17 0x00000000005a9303 in do_command (thd=0x2aa7850) at sql_parse.cc:810 #18 0x000000000059a114 in do_handle_one_connection (thd_arg=<value optimized out>) at sql_connect.cc:1172 #19 0x000000000059a2f0 in handle_one_connection (arg=<value optimized out>) at sql_connect.cc:1112 #20 0x0000000000871931 in pfs_spawn_thread (arg=<value optimized out>) at pfs.cc:1011 #21 0x000000315b0073da in start_thread () from /lib64/libpthread.so.0 #22 0x000000315a4e627d in clone () from /lib64/libc.so.6 The backtrace is the same as in bug #49570 but no prepared statements are involved, and the patch from bug #49570 does not fix this crash. How to repeat: Simplified query: SELECT * FROM performance_schema .`EVENTS_WAITS_HISTORY` JOIN mysql .`time_zone_transition` ON `Transition_time` = `OBJECT_NAME` WHERE NOT ( NOT 2 OR `OBJECT_NAME` != `Time_zone_id` ) ORDER BY `Transition_type_id` , `OBJECT_NAME` ; Original query: SELECT * FROM (performance_schema . `EVENTS_WAITS_HISTORY` AS table1 INNER JOIN mysql . `time_zone_transition` AS table2 ON ( table2 . `Transition_time` = table1 . `OBJECT_NAME` ) ) WHERE NOT (NOT ( ( ( table2 . `Time_zone_id` >= 2 OR table2 . `Time_zone_id` < '2006-12-12 03:25:01') OR table1 . `OBJECT_TYPE` >= '2005-09-01 01:45:13') OR table2 . `Time_zone_id` = 1) OR table1 . `OBJECT_NAME` != table2 . `Time_zone_id`) HAVING table2 . `Transition_type_id` = 9 ORDER BY table2 . `Transition_type_id` , table1 . `OBJECT_NAME` LIMIT 170;