MySQL Bugs: #28816: UPDATE with subquery fails in 5.0.41 with error ERROR 2013 (HY000)

Bug #28816	UPDATE with subquery fails in 5.0.41 with error ERROR 2013 (HY000)
Submitted:	31 May 2007 20:18	Modified:	11 Jul 2007 10:30
Reporter:	Masta Bog	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.41	OS:	Linux (Debian, 2.6.18)
Assigned to:	Assigned Account	CPU Architecture:	Any
Tags:	subquery, UPDATE

Description:
The following query used to work just fine in 4.1 but now fails in 5.0.41:

UPDATE ibf_forums AS f SET last_id = (
  SELECT t.tid FROM ibf_topics t, ibf_posts p
    WHERE p.forum_id = f.id AND p.topic_id = t.tid ORDER BY p.pid DESC LIMIT 1
);

ERROR 2013 (HY000): Lost connection to MySQL server during query

I take it the server thinks the query is fine but something else goes wrong that makes it close the connection. The presence of two tables in the FROM clause of the subquery seems to be what is triggering it because a query of the following form works just fine:

UPDATE ibf_forums AS f SET topics = (
  SELECT COUNT(*) FROM ibf_topics WHERE forum_id = f.id AND approved = 1
);

Query OK, 21 rows affected (0.01 sec)
Rows matched: 221  Changed: 21  Warnings: 0

How to repeat:

UPDATE ibf_forums AS f SET last_id = (
  SELECT t.tid FROM ibf_topics t, ibf_posts p
    WHERE p.forum_id = f.id AND p.topic_id = t.tid ORDER BY p.pid DESC LIMIT 1
);

ERROR 2013 (HY000): Lost connection to MySQL server during query

Thank you for the report.

Please provide output of SHOW CREATE TABLE and SHOW TABLE STATUS for tables ibf_forums, ibf_topics and ibf_posts.

Trying to find a workaround, i discovered that the following query works fine, so my initial assumption (i.e. that the two tables in the FROM clause of the subquery is the culprit) may be wrong:

UPDATE ibf_forums AS f SET last_id = (
  SELECT MAX(t.tid) FROM ibf_topics t, ibf_posts p
    WHERE p.forum_id = f.id AND p.topic_id = t.tid ORDER BY p.pid DESC
);

Query OK, 15 rows affected (0.60 sec)
Rows matched: 72  Changed: 15  Warnings: 0

Here is the requested info (status and schema)

+---------------------------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------+
| Name                      | Engine | Version | Row_format | Rows   | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time          | Collation         | Checksum | Create_options | Comment |
+---------------------------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------+
| ibf_forums                | MyISAM |      10 | Dynamic    |     72 |            360 |       26032 | 281474976710655 |         4096 |        60 |           NULL | 2007-05-29 21:21:57 | 2007-05-31 16:25:22 | 2007-05-30 22:31:08 | latin1_swedish_ci |     NULL |                |         |
| ibf_posts                 | MyISAM |      10 | Dynamic    |  86070 |           1324 |   114031744 | 281474976710655 |      3919872 |         0 |         391693 | 2007-05-29 21:22:04 | 2007-05-31 16:24:35 | 2007-05-30 22:31:22 | latin1_swedish_ci |     NULL |                |         |
| ibf_topics                | MyISAM |      10 | Dynamic    |  16040 |            126 |     2029668 | 281474976710655 |       502784 |         0 |          57220 | 2007-05-29 21:22:29 | 2007-05-31 16:30:47 | 2007-05-30 22:31:23 | latin1_swedish_ci |     NULL |                |         |
+---------------------------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------+

ibf_forums | CREATE TABLE `ibf_forums` (
  `id` smallint(5) NOT NULL default '0',
  `topics` mediumint(6) default NULL,
  `posts` mediumint(6) default NULL,
  `last_post` int(10) default NULL,
  `last_poster_id` mediumint(8) NOT NULL default '0',
  `last_poster_name` varchar(32) default NULL,
  `name` varchar(128) NOT NULL default '',
  `description` text,
  `position` tinyint(2) default NULL,
  `use_ibc` tinyint(1) default NULL,
  `use_html` tinyint(1) default NULL,
  `status` varchar(10) default NULL,
  `start_perms` varchar(255) default NULL,
  `reply_perms` varchar(255) default NULL,
  `read_perms` varchar(255) default NULL,
  `password` varchar(32) default NULL,
  `category` smallint(5) NOT NULL default '0',
  `last_title` varchar(128) default NULL,
  `last_id` int(10) default NULL,
  `sort_key` varchar(32) default NULL,
  `sort_order` varchar(32) default NULL,
  `prune` tinyint(3) default NULL,
  `show_rules` tinyint(1) default NULL,
  `upload_perms` varchar(255) default NULL,
  `preview_posts` tinyint(1) default NULL,
  `allow_poll` tinyint(1) NOT NULL default '1',
  `allow_pollbump` tinyint(1) NOT NULL default '0',
  `inc_postcount` tinyint(1) NOT NULL default '1',
  `skin_id` int(10) default NULL,
  `parent_id` mediumint(5) default '-1',
  `subwrap` tinyint(1) default '0',
  `sub_can_post` tinyint(1) default '1',
  `quick_reply` tinyint(1) default '0',
  `redirect_url` varchar(250) default '',
  `redirect_on` tinyint(1) NOT NULL default '0',
  `redirect_hits` int(10) NOT NULL default '0',
  `redirect_loc` varchar(250) default '',
  `rules_title` varchar(255) NOT NULL default '',
  `rules_text` text NOT NULL,
  `has_mod_posts` tinyint(1) NOT NULL default '0',
  `topic_mm_id` varchar(250) NOT NULL default '',
  `notify_modq_emails` text,
  `allow_thanks` int(1) NOT NULL default '0',
  `allow_hoist` int(1) NOT NULL default '0',
  PRIMARY KEY  (`id`),
  KEY `category` (`category`),
  KEY `id` (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

--------------

ibf_posts | CREATE TABLE `ibf_posts` (
  `append_edit` tinyint(1) default '0',
  `edit_time` int(10) default NULL,
  `pid` int(10) NOT NULL auto_increment,
  `author_id` mediumint(8) NOT NULL default '0',
  `author_name` varchar(32) default NULL,
  `use_sig` tinyint(1) NOT NULL default '0',
  `use_emo` tinyint(1) NOT NULL default '0',
  `ip_address` varchar(16) NOT NULL default '',
  `post_date` int(10) NOT NULL default '0',
  `icon_id` smallint(3) default NULL,
  `post` text,
  `queued` tinyint(1) default NULL,
  `topic_id` int(10) NOT NULL default '0',
  `forum_id` smallint(5) NOT NULL default '0',
  `attach_id` varchar(64) default NULL,
  `attach_hits` int(10) default NULL,
  `attach_type` varchar(128) default NULL,
  `attach_file` varchar(255) default NULL,
  `post_title` varchar(255) default NULL,
  `new_topic` tinyint(1) default '0',
  `edit_name` varchar(255) default NULL,
  PRIMARY KEY  (`pid`),
  KEY `topic_id` (`topic_id`,`author_id`),
  KEY `author_id` (`author_id`),
  KEY `forum_id` (`forum_id`,`post_date`)
) ENGINE=MyISAM AUTO_INCREMENT=391695 DEFAULT CHARSET=latin1

-------------

ibf_topics | CREATE TABLE `ibf_topics` (
  `tid` int(10) NOT NULL auto_increment,
  `title` varchar(70) NOT NULL default '',
  `description` varchar(70) default NULL,
  `state` varchar(8) default NULL,
  `posts` int(10) default NULL,
  `starter_id` mediumint(8) NOT NULL default '0',
  `start_date` int(10) default NULL,
  `last_poster_id` mediumint(8) NOT NULL default '0',
  `last_post` int(10) NOT NULL default '0',
  `icon_id` tinyint(2) default NULL,
  `starter_name` varchar(32) default NULL,
  `last_poster_name` varchar(32) default NULL,
  `poll_state` varchar(8) default NULL,
  `last_vote` int(10) default NULL,
  `views` int(10) default '0',
  `forum_id` smallint(5) NOT NULL default '0',
  `approved` tinyint(1) NOT NULL default '0',
  `author_mode` tinyint(1) default NULL,
  `pinned` tinyint(1) NOT NULL default '0',
  `moved_to` varchar(64) default NULL,
  `rating` text,
  `total_votes` int(5) NOT NULL default '0',
  `thanks_pid` int(10) default NULL,
  PRIMARY KEY  (`tid`),
  KEY `forum_id` (`forum_id`,`approved`,`pinned`),
  KEY `last_post` (`last_post`)
) ENGINE=MyISAM AUTO_INCREMENT=57220 DEFAULT CHARSET=latin1

Right, I just found a workaround. The following modified query works:

UPDATE ibf_forums AS f SET last_id = (
  SELECT MAX(t.tid) FROM ibf_topics t, ibf_posts p
    WHERE p.forum_id = f.id AND p.topic_id = t.tid ORDER BY p.pid DESC LIMIT 1
);

Query OK, 11 rows affected (0.58 sec)
Rows matched: 72  Changed: 11  Warnings: 0

The only change from the intial query is the MAX() around t.tid in the subquery. However, that should shouldn't have any effect since the resultset is limited to one row anyway. 

Once again, there was no issue with the initial query in 4.1.

Strike that: on string type columns using MAX() *does* affect the result (another bug?):

mysql> SELECT t.title FROM ibf_topics t, ibf_posts p WHERE p.forum_id = 200 AND p.topic_id = t.tid AND t.approved = 1 AND p.queued <> 1 ORDER BY p.pid DESC LIMIT 1;
+-----------------------------+
| title                       |
+-----------------------------+
| Rammstein - BCR compilation |
+-----------------------------+
1 row in set (0.00 sec)

mysql> SELECT MAX(t.title) FROM ibf_topics t, ibf_posts p WHERE p.forum_id = 200 AND p.topic_id = t.tid AND t.approved = 1 AND p.queued <> 1 AND ORDER BY p.pid DESC LIMIT 1;
+-------------------------------------------+
| MAX(t.title)                              |
+-------------------------------------------+
| VASCO ROSSI LIVE IN UDINE (9 luglio 2005) |
+-------------------------------------------+

It appears the by using max MAX() it extracted the max entry (last alphabetical) and afterwards LIMIT 1 was applied. Is this normal behaviour?

Going back to the original bug report, the real workaround is to place a GROUP BY clause in the subquery, like this:

mysql> UPDATE ibf_forums AS f SET last_title = (
  SELECT t.title FROM ibf_topics t, ibf_posts p
    WHERE p.forum_id = f.id AND p.topic_id = t.tid GROUP BY t.title ORDER BY p.pid DESC LIMIT 1);

Query OK, 32 rows affected (0.75 sec)
Rows matched: 72  Changed: 32  Warnings: 0

Again, the GROUP BY should not have any effect since LIMIT 1 is used, unless I'm missing something.

Thank you for the feedback.

I can not repeat described behaviour with test data. Please provide your configuration file, error log and indicate which MySQL package you use (file name).

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".