MySQL Bugs: #1555: SELECT statement with HAVING clause, but no GROUP BY clause crashes Service

Bug #1555	SELECT statement with HAVING clause, but no GROUP BY clause crashes Service
Submitted:	15 Oct 2003 3:44	Modified:	18 Oct 2003 14:04
Reporter:	Neil Lofts	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	4.1.0-alpha-max-nt	OS:	Windows (Windows 2000 Advanced Server SP4)
Assigned to:	MySQL Verification Team	CPU Architecture:	Any

Description:
I ran the following SELECT statement...

SELECT 
    agent_code, 
    booking_reference, 
    document_type, 
    document_checksum, 
    departure_date 
FROM 
    datasort_first 
HAVING 
    document_checksum != SUM(document_type)

against a table in mySQL (the agent_code and booking_reference fields are VARCHAR 50, the document_type and document_checksum fields are TINYINT, the departure_date field is DATE)

I expected it to return the records where the document_checksum field did not equal the sum of the document_type field.

Instead, the mysqld-nt.exe service crashed with the following error:
"The instruction at "0x00010005" referenced memory at "0x3a004400". The memory could not be "written".

I restarted the service and modified my statement to:

SELECT 
    agent_code, 
    booking_reference, 
    document_type, 
    document_checksum, 
    departure_date 
FROM 
    datasort_first 
GROUP BY 
    id, 
    booking_reference, 
    document_type
HAVING 
    document_checksum != SUM(document_type)

and the statement worked.

How to repeat:
See description

Can you please provide more information?

Query itself is not enough to repeat the problem. Send us SHOW CREATE TABLE output plus some rows enough to expose the bug.

With random table and data I was unable to repeat it.

Side note: using aggregate function without GROUP BY makes no sence as result. This is unrelated to bug ofcourse. Nothing should crash mysqld

No problem.

Here's the CREATE TABLE script:

CREATE TABLE `datasort_first` (
             `ID` int(11) NOT NULL auto_increment,
             `agent_code` varchar(20) character set latin1 NOT NULL default '0', 
             `company` varchar(255) character set latin1 default '0',
             `booking_reference` varchar(30) character set latin1 NOT NULL default '0',
             `document_name` varchar(64) character set latin1 default '0',
             `document_type` tinyint(4) default NULL,
             `document_checksum` tinyint(4) default NULL,
             `print_status` tinyint(4) default NULL, 
             `departure_date` date default NULL,
             `print_date` date default '0000-00-00', 
             `caa_type` tinyint(11) default NULL, 
             `pref_distibution` tinyint(4) default NULL,
             `agent_name` varchar(255) character set latin1 default NULL, 
             `agent_address_1` varchar(255) character set latin1 default NULL,
             `agent_address_2` varchar(255) character set latin1 default NULL,
             `agent_address_3` varchar(255) character set latin1 default NULL,
             `agent_address_4` varchar(255) character set latin1 default NULL,
             `agent_address_5` varchar(255) character set latin1 default NULL,
             `customer_name` varchar(255) character set latin1 default NULL, 
             `pdf_filepath` varchar(255) character set latin1 default NULL,
             `DS_FilePath` varchar(255) character set latin1 NOT NULL default '',
             PRIMARY KEY(`ID`),
             UNIQUE KEY `index_id` (`ID`),
             KEY `index_id_2` (`ID`)
             TYPE=MyISAM

... and here's some test data...

INSERT INTO datasort_first (agent_code, company, booking_reference, document_name, document_type, document_checksum, print_status, departure_date, print_date, caa_type, pref_distibution, agent_name, agent_address_1, agent_address_2, agent_address_3, agent_address_4, agent_address_5, customer_name, pdf_filepath, DS_FilePath) 
VALUES("584654", "company1", "12345678", "Invoice", 1, 3, 1, 2003-12-02, 0000-00-00, 
NULL, NULL, "A Travel Agent", "1 Travel Street", "Travel Town", "Travelshire", 
"", "TV1 1TV", "The Ninth Traveller", NULL, "D:\DS5441-1.DAT");

INSERT INTO datasort_first (agent_code, company, booking_reference, document_name, document_type, document_checksum, print_status, departure_date, print_date, caa_type, pref_distibution, agent_name, agent_address_1, agent_address_2, agent_address_3, agent_address_4, agent_address_5, customer_name, pdf_filepath, DS_FilePath) 
VALUES("584655", "company1", "12345678", "Other Doc", 2, 3, 1, 2003-10-22, 0000-00-00, 
NULL, NULL, "A Travel Agent", "1 Travel Street", "Travel Town", "Travelshire", 
"", "TV1 1TV", "Another Traveller", NULL, "D:\DS5441-2.DAT");

INSERT INTO datasort_first (agent_code, company, booking_reference, document_name, document_type, document_checksum, print_status, departure_date, print_date, caa_type, pref_distibution, agent_name, agent_address_1, agent_address_2, agent_address_3, agent_address_4, agent_address_5, customer_name, pdf_filepath, DS_FilePath) 
VALUES("584656", "company2", "12345679", "Invoice", 1, 3, 1, 2003-10-29, 0000-00-00, 
NULL, NULL, "Another Travel Agent", "2 Travel Road", "Vacation Town", "Holidayshire", 
"", "VA1 1AV", "Yet Another Traveller", NULL, "D:\DS5441-3.DAT");

INSERT INTO datasort_first (agent_code, company, booking_reference, document_name, document_type, document_checksum, print_status, departure_date, print_date, caa_type, pref_distibution, agent_name, agent_address_1, agent_address_2, agent_address_3, agent_address_4, agent_address_5, customer_name, pdf_filepath, DS_FilePath) 
VALUES("584656", "company2", "12345679", "Other Doc", 2, 3, 1, 2003-10-29, 0000-00-00, 
NULL, NULL, "Another Travel Agent", "2 Travel Road", "Vacation Town", "Holidayshire", 
"", "VA1 1AV", "Yet Another Traveller", NULL, "D:\DS5441-3.DAT");

Under Linux I got expected result (see below). It is probably Windows related.

Miguel,
Can you try repeating it under Windows?

+------------+-------------------+---------------+-------------------+----------------+
| agent_code | booking_reference | document_type | document_checksum | departure_date |
+------------+-------------------+---------------+-------------------+----------------+
| 584654     | 12345678          |             1 |                 3 | 0000-00-00     |
+------------+-------------------+---------------+-------------------+----------------+

Well I tested against a server built with the latest BK tree and the crash
doesn't exists anymore:

mysql> SELECT
    ->     agent_code,
    ->     booking_reference,
    ->     document_type,
    ->     document_checksum,
    ->     departure_date
    -> FROM
    ->     datasort_first
    -> HAVING
    ->     document_checksum != SUM(document_type)\G
*************************** 1. row ***************************
       agent_code: 584654
booking_reference: 12345678
    document_type: 1
document_checksum: 3
   departure_date: 0000-00-00
1 row in set (0.00 sec)

mysql> select version()\G
*************************** 1. row ***************************
version(): 4.1.1-alpha-max-nt
1 row in set (0.00 sec)