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:
None 
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
Triage: Triaged: D1 (Critical)

[14 Jan 2010 15:42] Philip Stoev
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;
[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] Shane Bester
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)