Bug #1049 Server Goes Away IF() with GROUP BY
Submitted: 14 Aug 2003 8:49 Modified: 14 Aug 2003 13:39
Reporter: Robert Nice Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:4.1.0 alpha OS:Linux (RH 7.3)
Assigned to: CPU Architecture:Any

[14 Aug 2003 8:49] Robert Nice
Description:
Server Goes Away (doesn't crash, just lose connection?)
with below test case.

How to repeat:
How-To-Repeat:
Schema as follows:
-- MySQL dump 10.2
-- 
-- Host: localhost    Database: test
---------------------------------------------------------
-- Server version       4.1.0-alpha-standard-log

-- 
-- Table structure for table 'Clients'
-- 

DROP TABLE IF EXISTS Clients;
CREATE TABLE Clients (
  ClientID int(10) unsigned NOT NULL auto_increment,
  Company varchar(20) NOT NULL default '',
  FlagReseller smallint(6) NOT NULL default '0',
  PRIMARY KEY  (ClientID)
) TYPE=InnoDB CHARSET=latin1;

-- 
-- Dumping data for table 'Clients'
-- 

/*!40000 ALTER TABLE Clients DISABLE KEYS */;
LOCK TABLES Clients WRITE;
INSERT INTO Clients VALUES (1,'Test Inc',1);
UNLOCK TABLES;
/*!40000 ALTER TABLE Clients ENABLE KEYS */;

-- 
-- Table structure for table 'Summary'
-- 

DROP TABLE IF EXISTS Summary;
CREATE TABLE Summary (
  ClientID int(10) unsigned NOT NULL default '0',
  CompileDate date NOT NULL default '0000-00-00',
  NumberSales int(10) unsigned NOT NULL default '0',
  PRIMARY KEY  (ClientID,CompileDate)
) TYPE=InnoDB CHARSET=latin1;

-- 
-- Dumping data for table 'Summary'
-- 

DROP TABLE IF EXISTS Summary;
CREATE TABLE Summary (
  ClientID int(10) unsigned NOT NULL default '0',
  CompileDate date NOT NULL default '0000-00-00',
  NumberSales int(10) unsigned NOT NULL default '0',
  PRIMARY KEY  (ClientID,CompileDate)
) TYPE=InnoDB CHARSET=latin1;

-- 
-- Dumping data for table 'Summary'
-- 

/*!40000 ALTER TABLE Summary DISABLE KEYS */;
LOCK TABLES Summary WRITE;
INSERT INTO Summary VALUES (1,'2003-01-10',5);
UNLOCK TABLES;
/*!40000 ALTER TABLE Summary ENABLE KEYS */;

mysql> SELECT IF(Clients.FlagReseller, SUM(Summary.NumberSales), -SUM(Summary.NumberSales)) FROM Clients, Summary WHERE Clients.ClientID = Summary.ClientID AND Clients.ClientID = 1 AND Summary.CompileDate BETWEEN '2003-01-01 00:00:00' AND '2003-02-01 00:00:00' GROUP BY Summary.ClientID;
ERROR 2013: Lost connection to MySQL server during query
mysql> show status;
ERROR 2006: MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    2556
Current database: test

+--------------------------+------------+
| Variable_name            | Value      |
+--------------------------+------------+
| Aborted_clients          | 2010       |
| Aborted_connects         | 95         |
| Bytes_received           | 502643958  |
| Bytes_sent               | 1237638714 |
| Com_admin_commands       | 2230713    |
| Com_alter_table          | 143        |
| Com_alter_db             | 0          |
| Com_analyze              | 1          |
| Com_backup_table         | 0          |
| Com_begin                | 0          |
| Com_change_db            | 2319       |
| Com_change_master        | 0          |
| Com_check                | 0          |
| Com_commit               | 0          |
| Com_create_db            | 3          |
| Com_create_function      | 0          |
| Com_create_index         | 9          |
| Com_create_table         | 43         |
| Com_delete               | 100        |
| Com_delete_multi         | 0          |
| Com_do                   | 0          |
| Com_drop_db              | 1          |
| Com_drop_function        | 0          |
| Com_drop_index           | 0          |
| Com_drop_table           | 27         |
| Com_flush                | 0          |
| Com_grant                | 0          |
| Com_ha_close             | 0          |
| Com_ha_open              | 0          |
| Com_ha_read              | 0          |
| Com_help                 | 0          |
| Com_insert               | 20489      |
| Com_insert_select        | 0          |
| Com_kill                 | 0          |
| Com_load                 | 0          |
| Com_load_master_data     | 0          |
| Com_load_master_table    | 0          |
| Com_lock_tables          | 37         |
| Com_optimize             | 0          |
| Com_purge                | 0          |
| Com_purge_before_date    | 0          |
| Com_rename_table         | 0          |
| Com_repair               | 1          |
| Com_replace              | 77395      |
| Com_replace_select       | 0          |
| Com_reset                | 0          |
| Com_restore_table        | 0          |
| Com_revoke               | 0          |
| Com_rollback             | 0          |
| Com_select               | 1683565    |
| Com_set_option           | 17116      |
| Com_show_binlog_events   | 0          |
| Com_show_binlogs         | 0          |
| Com_show_charsets        | 0          |
| Com_show_column_types    | 0          |
| Com_show_create_table    | 49         |
| Com_show_create_db       | 0          |
| Com_show_databases       | 0          |
| Com_show_errors          | 0          |
| Com_show_fields          | 274        |
| Com_show_grants          | 0          |
| Com_show_keys            | 5          |
| Com_show_logs            | 0          |
| Com_show_master_status   | 0          |
| Com_show_new_master      | 0          |
| Com_show_open_tables     | 0          |
| Com_show_privileges      | 0          |
| Com_show_processlist     | 98         |
| Com_show_slave_hosts     | 0          |
| Com_show_slave_status    | 0          |
| Com_show_status          | 7          |
| Com_show_innodb_status   | 2          |
| Com_show_tables          | 84         |
| Com_show_table_types     | 1          |
| Com_show_variables       | 2331       |
| Com_show_warnings        | 0          |
| Com_slave_start          | 0          |
| Com_slave_stop           | 0          |
| Com_truncate             | 1          |
| Com_unlock_tables        | 24         |
| Com_update               | 511292     |
| Com_update_multi         | 58         |
| Connections              | 2557       |
| Created_tmp_disk_tables  | 5          |
| Created_tmp_tables       | 3287       |
| Created_tmp_files        | 34         |
| Delayed_insert_threads   | 0          |
| Delayed_writes           | 0          |
| Delayed_errors           | 0          |
| Flush_commands           | 1          |
| Handler_commit           | 189        |
| Handler_delete           | 28         |
| Handler_read_first       | 19288      |
| Handler_read_key         | 34462171   |
| Handler_read_next        | 116305299  |
| Handler_read_prev        | 0          |
| Handler_read_rnd         | 1000580    |
| Handler_read_rnd_next    | 122848775  |
| Handler_rollback         | 3315       |
| Handler_update           | 82486      |
| Handler_write            | 1996645    |
| Key_blocks_used          | 1267       |
| Key_read_requests        | 346484     |
| Key_reads                | 1267       |
| Key_write_requests       | 118565     |
| Key_writes               | 112        |
| Max_used_connections     | 100        |
| Not_flushed_key_blocks   | 0          |
| Not_flushed_delayed_rows | 0          |
| Open_tables              | 64         |
| Open_files               | 7          |
| Open_streams             | 0          |
| Opened_tables            | 49573      |
| Questions                | 2315985    |
| Qcache_queries_in_cache  | 0          |
| Qcache_inserts           | 0          |
| Qcache_hits              | 0          |
| Qcache_lowmem_prunes     | 0          |
| Qcache_not_cached        | 0          |
| Qcache_free_memory       | 0          |
| Qcache_free_blocks       | 0          |
| Qcache_total_blocks      | 0          |
| Rpl_status               | NULL       |
| Select_full_join         | 304        |
| Select_full_range_join   | 0          |
| Select_range             | 7714       |
| Select_range_check       | 0          |
| Select_scan              | 18616      |
| Slave_open_temp_tables   | 0          |
| Slave_running            | OFF        |
| Slow_launch_threads      | 1          |
| Slow_queries             | 1549       |
| Sort_merge_passes        | 17         |
| Sort_range               | 445        |
| Sort_rows                | 1000558    |
| Sort_scan                | 2456       |
| Table_locks_immediate    | 2323700    |
| Table_locks_waited       | 1          |
| Threads_cached           | 0          |
| Threads_created          | 2556       |
| Threads_connected        | 5          |
| Threads_running          | 1          |
| Uptime                   | 4979813    |
+--------------------------+------------+
143 rows in set (0.00 sec)

mysql> <ctrl-d>Segmentation fault
# 

>C compiler:    2.95.3
>C++ compiler:  2.95.3
>Environment:
    <machine, os, target, libraries (multiple lines)>
System: Linux puberty.fraudscrub.com 2.4.18-3smp #1 SMP Thu Apr 18 07:27:31 EDT 2002 i686 unknown
Architecture: i686

Some paths:  /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc
GCC: Reading specs from /usr/lib/gcc-lib/i386-redhat-linux/2.96/specs
gcc version 2.96 20000731 (Red Hat Linux 7.3 2.96-110)
Compilation info: CC='gcc'  CFLAGS='-O2 -mcpu=pentiumpro'  CXX='gcc'  CXXFLAGS='-O2 -mcpu=pentiumpro -felide-constructors'  LDFLAGS=''  ASFLAGS=''
LIBC:
lrwxrwxrwx    1 root     root           13 Aug 22  2002 /lib/libc.so.6 -> libc-2.2.5.so
-rwxr-xr-x    1 root     root      1260480 Apr 15  2002 /lib/libc-2.2.5.so
-rw-r--r--    1 root     root      2310808 Apr 15  2002 /usr/lib/libc.a
-rw-r--r--    1 root     root          178 Apr 15  2002 /usr/lib/libc.so
Configure command: ./configure '--prefix=/usr/local/mysql' '--with-comment=Official MySQL-standard binary' '--with-extra-charsets=complex' '--with-server-suffix=-standard' '--enable-thread-safe-client' '--enable-local-infile' '--enable-assembler' '--disable-shared' '--with-client-ldflags=-all-static' '--with-mysqld-ldflags=-all-static' '--with-innodb' 'CFLAGS=-O2 -mcpu=pentiumpro' 'CXXFLAGS=-O2 -mcpu=pentiumpro -felide-constructors' 'CXX=gcc'

Suggested fix:
Do the IF() in program logic instead.
[14 Aug 2003 13:39] Indrek Siitan
I was able to repeat this in 4.1.0 release, and on FreeBSD it actually
crashed the system.

However, on the version compiled from the latest development tree
it doesn't crash, but returns one row with a value of "5", so I believe
the bug has been fixed since. For the public, it will be fixed in 4.1.1.