MySQL Bugs: #54543: update ignore with incorrect subquery leads to assertion failure: inited==INDEX

Bug #54543	update ignore with incorrect subquery leads to assertion failure: inited==INDEX
Submitted:	16 Jun 2010 6:37	Modified:	7 Oct 2010 23:08
Reporter:	Shane Bester (Platinum Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S1 (Critical)
Version:	5.0.91-debug, 5.1.47-debug, 5.1.49-debug	OS:	Any
Assigned to:	Martin Hansson	CPU Architecture:	Any

Description:
this is similar class of bug to bug #54475

Version: '5.1.47-enterprise-gpl-advanced-debug'  socket: ''  port: 3306  MySQL Enterprise Server - Advanced Edition Debug (GPL)
Assertion failed: inited==INDEX, file .\ha_heap.cc, line 277

mysqld-debug.exe!my_sigabrt_handler()[mysqld.cc:2049]
mysqld-debug.exe!raise()[winsig.c:590]
mysqld-debug.exe!abort()[abort.c:71]
mysqld-debug.exe!_wassert()[assert.c:212]
mysqld-debug.exe!ha_heap::index_read_map()[ha_heap.cc:277]
mysqld-debug.exe!end_update()[sql_select.cc:12503]
mysqld-debug.exe!evaluate_join_record()[sql_select.cc:11447]
mysqld-debug.exe!sub_select()[sql_select.cc:11321]
mysqld-debug.exe!do_select()[sql_select.cc:11077]
mysqld-debug.exe!JOIN::exec()[sql_select.cc:1907]
mysqld-debug.exe!subselect_single_select_engine::exec()[item_subselect.cc:1990]
mysqld-debug.exe!Item_subselect::exec()[item_subselect.cc:280]
mysqld-debug.exe!Item_singlerow_subselect::val_int()[item_subselect.cc:590]
mysqld-debug.exe!Item::save_in_field()[item.cc:5184]
mysqld-debug.exe!fill_record()[sql_base.cc:8270]
mysqld-debug.exe!select_union::send_data()[sql_union.cc:60]
mysqld-debug.exe!end_send()[sql_select.cc:12235]
mysqld-debug.exe!evaluate_join_record()[sql_select.cc:11447]
mysqld-debug.exe!sub_select()[sql_select.cc:11327]
mysqld-debug.exe!do_select()[sql_select.cc:11077]
mysqld-debug.exe!JOIN::exec()[sql_select.cc:2314]
mysqld-debug.exe!mysql_select()[sql_select.cc:2511]
mysqld-debug.exe!mysql_derived_filling()[sql_derived.cc:294]
mysqld-debug.exe!mysql_handle_derived()[sql_derived.cc:56]
mysqld-debug.exe!mysql_multi_update_prepare()[sql_update.cc:1184]
mysqld-debug.exe!mysql_execute_command()[sql_parse.cc:3108]
mysqld-debug.exe!mysql_parse()[sql_parse.cc:5986]
mysqld-debug.exe!dispatch_command()[sql_parse.cc:1233]
mysqld-debug.exe!do_command()[sql_parse.cc:874]
mysqld-debug.exe!handle_one_connection()[sql_connect.cc:1134]
mysqld-debug.exe!pthread_start()[my_winthread.c:85]
mysqld-debug.exe!_callthreadstart()[thread.c:293]
mysqld-debug.exe!_threadstart()[thread.c:277]

How to repeat:
#on debug build:

drop table if exists `t1`;
create table `t1`(`a` int not null)engine=myisam;
insert into `t1` values (1),(2),(3);
update ignore (select (select count(*) from `t1` group by @@server_id, `a`)
as `a` from `t1`) `x`,`t1` set `t1`.`a`=`x`.`a`;

Verified just as described with recent 5.1.49-debug from bzr:

valeriy-kravchuks-macbook-pro:5.1 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.1.49-debug Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> drop table if exists `t1`;
Query OK, 0 rows affected (0.17 sec)

mysql> create table `t1`(`a` int not null)engine=myisam;
Query OK, 0 rows affected (0.26 sec)

mysql> insert into `t1` values (1),(2),(3);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> update ignore (select (select count(*) from `t1` group by @@server_id, `a`)
    -> as `a` from `t1`) `x`,`t1` set `t1`.`a`=`x`.`a`;
ERROR 2013 (HY000): Lost connection to MySQL server during query

No problems with mysql-trunk:

valeriy-kravchuks-macbook-pro:trunk openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.5-m3-debug Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> drop table if exists `t1`;
Query OK, 0 rows affected (0.01 sec)

mysql> create table `t1`(`a` int not null)engine=myisam;
Query OK, 0 rows affected (0.10 sec)

mysql> insert into `t1` values (1),(2),(3);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> update ignore (select (select count(*) from `t1` group by @@server_id, `a`)
    -> as `a` from `t1`) `x`,`t1` set `t1`.`a`=`x`.`a`;
Query OK, 3 rows affected, 12 warnings (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 9

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/117184

3497 Martin Hansson	2010-08-31
      Bug#54543: update ignore with incorrect subquery leads to assertion failure:
      inited==INDEX
      
      When an error occurs while sending the data in a temporary table there was no
      cleanup performed. This caused a failed assertion in the case when different
      access methods were used for populating the table vs. retrieving the data from
      the table if IGNORE was specified and sql_safe_updates = 0. In this case
      execution continues, but the handler expects to continue with the access
      method used for row retrieval.
      
      Fixed by doing the cleanup even if errors occur.

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/117294

3498 Martin Hansson	2010-09-01
      Bug#54543: update ignore with incorrect subquery leads to assertion failure:
      inited==INDEX
      
      When an error occurs while sending the data in a temporary table there was no
      cleanup performed. This caused a failed assertion in the case when different
      access methods were used for populating the table vs. retrieving the data from
      the table if IGNORE was specified and sql_safe_updates = 0. In this case
      execution continues, but the handler expects to continue with the access
      method used for row retrieval.
      
      Fixed by doing the cleanup even if errors occur.

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/117672

3500 Martin Hansson	2010-09-07
      Bug#54543: update ignore with incorrect subquery leads to assertion failure:
      inited==INDEX
      
      When an error occurs while sending the data in a temporary table there was no
      cleanup performed. This caused a failed assertion in the case when different
      access methods were used for populating the table vs. retrieving the data from
      the table if IGNORE was specified and sql_safe_updates = 0. In this case
      execution continues, but the handler expects to continue with the access
      method used for row retrieval.
      
      Fixed by doing the cleanup even if errors occur.

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/117673

3194 Martin Hansson	2010-09-07 [merge]
      Merge of fix for Bug#54543. Test case only (bug is not present in this tree).

Pushed into mysql-trunk 5.6.1-m4 (revid:alik@sun.com-20100928153607-tdsxkdm5cmuym5sq) (version source revid:alik@sun.com-20100928153508-0saa6v93dinqx1u7) (merge vers: 5.6.1-m4) (pib:21)

Pushed into mysql-next-mr (revid:alik@sun.com-20100928153646-pqp8o1a92mxtuj3h) (version source revid:alik@sun.com-20100928153532-lr3gtvnyp2en4y75) (pib:21)

Pushed into mysql-5.5 5.5.7-rc (revid:alik@sun.com-20100928153459-4nudf4zgzlou4s7q) (version source revid:alik@sun.com-20100928153459-4nudf4zgzlou4s7q) (merge vers: 5.5.7-rc) (pib:21)

Noted in 5.5.7, 5.6.1 changelogs.

With an UPDATE IGNORE statement including a subquery that was
evaluated using a temporary table, an error transferring the data 
from the temporary was ignored, causing an assertion to be raised.

Setting report to Need Merge pending push to 5.1.x.

Noted in 5.1.52 changelog.

Pushed into mysql-5.1 5.1.53 (revid:build@mysql.com-20101101184443-o2olipi8vkaxzsqk) (version source revid:build@mysql.com-20101101184443-o2olipi8vkaxzsqk) (merge vers: 5.1.53) (pib:21)