MySQL Bugs: #7570: INNODB SUBQUERY CRASH

Bug #7570	INNODB SUBQUERY CRASH
Submitted:	29 Dec 2004 11:28	Modified:	29 Dec 2004 22:46
Reporter:	Dimitri Redant	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	4.1.4	OS:	Linux (linux)
Assigned to:		CPU Architecture:	Any

Description:
I've use this query on a INNODB table and the whole server crashed.

The error is within the subquery "(COALESCE((SELECT SUM(amount) FROM invoices WHERE inv_line_id = '2' AND inv_nr_id = '3' AND nr != '' AND year(issuedate) = year(issuedate)-1),0)/SUM(amount)) AS groei".

How to repeat:
SELECT year(issuedate) AS year, SUM(amount) AS amount, (SUM(amount*vat/100)) AS vat, (SUM(amount)+(SUM(amount*vat/100))) AS total, (COALESCE((SELECT SUM(amount) FROM invoices WHERE inv_line_id = '2' AND inv_nr_id = '3' AND nr != '' AND year(issuedate) = year(issuedate)-1),0)/SUM(amount)) AS groei, COUNT(id) AS aantal FROM invoices WHERE inv_line_id = '2' AND inv_nr_id = '3' AND nr != '' GROUP BY year ORDER BY year DESC

Dimitri, please, add dump of the table 'invoices'. It helps us to reproduce the problem. You also didn't mention version of MySQL server and OS.

-- phpMyAdmin SQL Dump
-- version 2.6.0-rc2
-- http://www.phpmyadmin.net
-- 
-- Host: localhost
-- Generation Time: Dec 29, 2004 at 01:21 PM
-- Server version: 4.1.4
-- PHP Version: 4.3.9-1.dotdeb.3
-- 
-- Database: `iem`
-- 

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

-- 
-- Table structure for table `invoices`
-- 

CREATE TABLE invoices (
  id int(11) NOT NULL auto_increment,
  issuedate date default NULL,
  paydate date default NULL,
  expdate date default NULL,
  nr varchar(100) default NULL,
  `comment` text,
  amount double default NULL,
  vat double default NULL,
  title1 varchar(100) default NULL,
  title2 varchar(100) default NULL,
  inv_line_id int(11) default NULL,
  inv_temp_id int(11) default NULL,
  inv_cond_id int(11) default NULL,
  template text,
  project_id int(11) default NULL,
  clientaddress text,
  attentionof varchar(30) default NULL,
  email varchar(255) default NULL,
  inv_nr_id int(11) default NULL,
  people_id int(11) default NULL,
  PRIMARY KEY  (id)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=5 ;

-- 
-- Dumping data for table `invoices`
-- 

INSERT INTO invoices VALUES (1, '2004-12-10', '0000-00-00', '2004-12-20', 'test/152-20', '', 15, 21, 'test', '', 2, 0, 2, '', 0, '', '', '', 3, 3);
INSERT INTO invoices VALUES (2, '2004-12-27', '0000-00-00', '2005-01-06', 'test/152-21', '', 100, 21, 'test', 'tester', 2, 0, 2, '', 0, '', '', '', 3, 3);
INSERT INTO invoices VALUES (3, '2004-12-27', '2004-12-27', '2005-01-06', 'test/152-22', '', -1021, 21, 'test', 'te', 2, 0, 2, '', 0, '', '', '', 3, 3);
INSERT INTO invoices VALUES (4, '2004-12-27', '0000-00-00', '2005-01-16', 'test/152-23', '', 10, 21, 'test', '', 2, 0, 3, '', 0, '', '', '', 3, 3);

Dimitri,

please look at the .err log of the mysqld server.

This is probably a duplicate of Bug #6841 that Sanja fixed to 4.1.8.

Regards,

Heikki

Currently on BK source this crash not happens anymore:

mysql> SELECT year(issuedate) AS year, SUM(amount) AS amount, (SUM(amount*vat/100)) AS
    -> vat, (SUM(amount)+(SUM(amount*vat/100))) AS total, (COALESCE((SELECT SUM(amount)
    -> FROM invoices WHERE inv_line_id = '2' AND inv_nr_id = '3' AND nr != '' AND
    -> year(issuedate) = year(issuedate)-1),0)/SUM(amount)) AS groei, COUNT(id) AS
    -> aantal FROM invoices WHERE inv_line_id = '2' AND inv_nr_id = '3' AND nr != ''
    -> GROUP BY year ORDER BY year DESC;
+------+--------+---------+----------+-------+--------+
| year | amount | vat     | total    | groei | aantal |
+------+--------+---------+----------+-------+--------+
| 2004 |   -896 | -188.16 | -1084.16 |    -0 |      4 |
+------+--------+---------+----------+-------+--------+
1 row in set (0.10 sec)