MySQL Bugs: #20868: Client connection is broken on SQL query error

Bug #20868	Client connection is broken on SQL query error
Submitted:	5 Jul 2006 14:19	Modified:	2 Aug 2006 19:08
Reporter:	Kent Olsen	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.22/5.0BK/5.1BK	OS:	Linux (Fedora Core 5 (64-bit))
Assigned to:	Georgi Kodinov	CPU Architecture:	Any

Description:
A typographical error in a SELECT query can cause the client connection to terminate.  No SQL parse error or diagnostic is returned.  The phenomenon is reproducable from the MySQL command prompt and from a GUI client connected on port 3306.

Note that the underlying cause is incorrect SQL.  Still, this should result in an error message and not the termination of the connection.

Note also that the query returns the proper result when the tables are empty or when table 1 (called `tables`) contains only 1 item.

Here is the offending SQL:

select
  t.id, t.name,
  (select min(c.id)
   from tables ttt, columns ccc 
   where ttt.id = ccc.table_id
     and ttt.id = t.id
   group by ttt.id) as minid
  from tables t, columns c
  where t.id = c.table_id;

Here is the corrected SQL:

select
  t.id, t.name,
  (select min(ccc.id)
   from tables ttt, columns ccc 
   where ttt.id = ccc.table_id
     and ttt.id = t.id
   group by ttt.id) as minid
  from tables t, columns c
  where t.id = c.table_id;

The queries are identical, except that the first statement references an outer table (c.id) in the third line instead of an inner table (ccc.id).

How to repeat:
Create the tables used in the query and insert minimal data from the SQL below and rerun the query.

-- MySQL dump 10.10
--
-- Host: localhost    Database: abt
-- ------------------------------------------------------
-- Server version       5.0.22

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `columns`
--

DROP TABLE IF EXISTS `columns`;
CREATE TABLE `columns` (
  `id` int(11) NOT NULL auto_increment,
  `memo_id` int(11) default NULL,
  `table_id` int(11) default NULL,
  `ordinal` int(11) default NULL,
  `data_integrity_id` int(11) default NULL,
  `format` varchar(20) default NULL,
  `width` int(11) default NULL,
  `scale` int(11) default NULL,
  `oracle_name` varchar(32) default NULL,
  `oracle_type` varchar(20) default NULL,
  `oracle_bytes` int(11) default NULL,
  `db2_name` varchar(32) default NULL,
  `db2_type` varchar(20) default NULL,
  `db2_bytes` int(11) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dumping data for table `columns`
--

/*!40000 ALTER TABLE `columns` DISABLE KEYS */;
LOCK TABLES `columns` WRITE;
INSERT INTO `columns` VALUES
  (1,NULL,1,1,NULL,'NUMBER(11)',4,0,NULL,NULL,NULL,'ID','INTEGER',4),
  (2,NULL,1,NULL,NULL,'DATE',4,0,'BAL_DT','DATE',4,'BALANCE_DATE','DATE',4),
  (3,NULL,1,NULL,NULL,'NUMBER(7,2)',7,2,'BEGIN_BAL_AMT','NUMBER',6,'BEGINNING_BALANCE_AMOUNT','DECIMAL',5),
  (4,NULL,1,NULL,NULL,'NUMBER(7,2)',7,2,'ENDNG_BAL_AMT','NUMBER',6,'ENDING_BALANCE','DECIMAL',5),
  (5,NULL,1,8,NULL,'DATE',4,0,'CRT_DT','DATE',4,'CREATED_DATE','TIMESTAMP',8),
  (6,NULL,1,9,NULL,'VARCHAR2(8)',8,0,'CRT_USR','VARCHAR2',8,'CREATED_BY_USER','VARCHAR',8),
  (7,NULL,1,10,NULL,'DATE',4,0,'MDFD_DT','DATE',4,'CHANGED_DATE','TIMESTAMP',8),
  (8,NULL,1,11,NULL,'VARCHAR2(8)',8,0,'MDFD_USR','VARCHAR2',8,'CHANGED_BY_USER','VARCHAR',8),
  (9,NULL,1,7,NULL,'NUMBER(16)',16,0,'VER_NBR','NUMBER',10,'VERSION','INTEGER',4),
  (10,NULL,1,NULL,NULL,'NUMBER(16)',16,0,'CASE_ID','NUMBER',10,'CASE_ID','INTEGER',4),
  (11,NULL,1,6,NULL,'NUMBER(16)',16,0,'AGNCY_ID','NUMBER',10,'AGENCY_ID','INTEGER',4),
  (12,NULL,2,1,NULL,'NUMBER(16)',16,0,'CASE_ID','NUMBER',10,'ID','INTEGER',4),
  (13,NULL,2,NULL,NULL,'NUMBER(16)',16,0,'ACCT_SMMRY_ID','NUMBER',10,'ACCOUNT_SUMMARY_ID','INTEGER',4),
  (14,NULL,2,NULL,NULL,'DATE',4,0,'TRXN_DT','DATE',4,'TRANSACTION_DATE','DATE',4),
  (15,NULL,2,NULL,NULL,'VARCHAR2(8)',8,0,'TRXN_TYPE_CD','VARCHAR2',8,'TRANSACTION_TYPE','VARCHAR',8),
  (16,NULL,2,NULL,NULL,'NUMBER(7,2)',7,2,'TRXN_AMT','NUMBER',6,'TRANSACTION_AMOUNT','DECIMAL',5),
  (17,NULL,2,NULL,NULL,'NUMBER(7,2)',7,2,'BAL_AMT','NUMBER',6,'BALANCE','DECIMAL',5),
  (18,NULL,2,9,NULL,'DATE',4,0,'CRT_DT','DATE',4,'CREATED_DATE','TIMESTAMP',8),
  (19,NULL,2,10,NULL,'VARCHAR2(8)',8,0,'CRT_USR','VARCHAR2',8,'CREATED_BY_USER','VARCHAR',8),
  (20,NULL,2,11,NULL,'DATE',4,0,'MDFD_DT','DATE',4,'CHANGED_DATE','TIMESTAMP',8),
  (21,NULL,2,12,NULL,'VARCHAR2(8)',8,0,'MDFD_USR','VARCHAR2',8,'CHANGED_BY_USER','VARCHAR',8),
  (22,NULL,2,8,NULL,'NUMBER(16)',16,0,'VER_NBR','NUMBER',10,'VERSION','INTEGER',4),
  (23,NULL,2,7,NULL,'NUMBER(16)',16,0,'AGNCY_ID','NUMBER',10,'AGENCY_ID','INTEGER',4);
UNLOCK TABLES;
/*!40000 ALTER TABLE `columns` ENABLE KEYS */;

--
-- Table structure for table `tables`
--

DROP TABLE IF EXISTS `tables`;
CREATE TABLE `tables` (
  `id` int(11) NOT NULL auto_increment,
  `memo_id` int(11) default NULL,
  `name` varchar(80) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dumping data for table `tables`
--

/*!40000 ALTER TABLE `tables` DISABLE KEYS */;
LOCK TABLES `tables` WRITE;
INSERT INTO `tables` VALUES
  (1,NULL,'account_balance'),
  (2,NULL,'account_summary');
UNLOCK TABLES;
/*!40000 ALTER TABLE `tables` ENABLE KEYS */;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

Back Trace 5.0.23BK Suse Linux 32-bit

Attachment: bt-20868.txt (text/plain), 8.00 KiB.

Thank you for the bug report. Verified on Linux Suse 10 32-bit.
Back trace attached.

miguel@hegel:~/dbs/5.1> 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 to server version: 5.0.23-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> select
    ->   t.id, t.name,
    ->   (select min(c.id)
    ->    from tables ttt, columns ccc 
    ->    where ttt.id = ccc.table_id
    ->      and ttt.id = t.id
    ->    group by ttt.id) as minid
    ->   from tables t, columns c
    ->   where t.id = c.table_id;
ERROR 2013 (HY000): Lost connection to MySQL server during query

miguel@hegel:~/dbs/5.1> 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 3 to server version: 5.1.12-beta-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> select
    ->   t.id, t.name,
    ->   (select min(c.id)
    ->    from tables ttt, columns ccc 
    ->    where ttt.id = ccc.table_id
    ->      and ttt.id = t.id
    ->    group by ttt.id) as minid
    ->   from tables t, columns c
    ->   where t.id = c.table_id;
ERROR 2013 (HY000): Lost connection to MySQL server during query

miguel@hegel:~/dbs/4.1> 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 2 to server version: 4.1.21-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> select
    ->   t.id, t.name,
    ->   (select min(c.id)
    ->    from tables ttt, columns ccc 
    ->    where ttt.id = ccc.table_id
    ->      and ttt.id = t.id
    ->    group by ttt.id) as minid
    ->   from tables t, columns c
    ->   where t.id = c.table_id;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'tables ttt, columns ccc 
   where ttt.id = ccc.table_id
     and ttt.id = t.id
 ' at line 4

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

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

An aggregate function reference was resolved incorrectly and
  caused a crash in count_field_types.

Fixed in 5.0.25, 5.1.12

Noted in 5.0.25, 5.1.12 changelogs.