Bug #3203 A crashing bug with distinct query and a hidden field with SQL_BIG_TABLES
Submitted: 17 Mar 2004 9:00 Modified: 26 Mar 2004 4:34
Reporter: SINISA MILIVOJEVIC Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S1 (Critical)
Version:4.0.19 OS:Any (any)
Assigned to: Michael Widenius CPU Architecture:Any

[17 Mar 2004 9:00] SINISA MILIVOJEVIC
Description:
If there is a distinct query without group by but with one or more hidden fields, for exacmple in order by, then server will crash.

This will happen only if SQL_BIG_TABLES option is used.

How to repeat:

DROP TABLE IF EXISTS Event;
CREATE TABLE Event (
  ID bigint(20) NOT NULL auto_increment,
  attraction bigint(20) NOT NULL default '-1',
  business bigint(20) NOT NULL default '-1',
  attractionListing bigint(20) NOT NULL default '-1',
  feed bigint(20) NOT NULL default '-1',
  feedID varchar(255) NOT NULL default '',
  runTime int(11) default NULL,
  description text,
  ticketInfo text,
  priceInfo text,
  ageRestriction varchar(255) default NULL,
  PRIMARY KEY  (ID),
  KEY attraction (attraction),
  KEY business (business),
  KEY attractionListing (attractionListing),
  KEY feed (feed),
  KEY feedID (feedID)
) TYPE=MyISAM;

INSERT INTO Event VALUES (1,1,1,1,1,'BB',10,'','','','');

DROP TABLE IF EXISTS Listing;
CREATE TABLE Listing (
  ID bigint(20) NOT NULL auto_increment,
  name varchar(255) NOT NULL default '',
  business bigint(20) NOT NULL default '-1',
  address varchar(100) default NULL,
  city varchar(30) NOT NULL default '',
  state char(2) NOT NULL default '',
  zip varchar(10) default NULL,
  zipPlusFour varchar(4) default NULL,
  linkUsage bigint(20) NOT NULL default '-1',
  latitude bigint(20) default NULL,
  longitude bigint(20) default NULL,
  geocoded char(1) default NULL,
  manuallyGeocoded char(1) default NULL,
  tel bigint(20) default NULL,
  review text,
  feed bigint(20) NOT NULL default '-1',
  feedId varchar(255) NOT NULL default '',
  listingState bigint(20) NOT NULL default '1',
  oldImported date default NULL,
  imported date default NULL,
  longReview text,
  normalName varchar(255) default NULL,
  address2 varchar(100) default NULL,
  hashKey bigint(20) NOT NULL default '-1',
  dateClosed date default NULL,
  PRIMARY KEY  (ID),
  KEY name (name),
  KEY business (business),
  KEY feed (feed),
  KEY feedID (feedId),
  KEY hashKey (hashKey),
  KEY normalName (normalName),
  KEY tel (tel),
  KEY address (address),
  KEY city (city),
  KEY state (state)
) TYPE=MyISAM;

INSERT INTO Listing VALUES (1,'Another Flower Shop',1,'123 main st','Philadelphia','PA','11234','9876',-1,1,1,'F','F',2125551234,'',1,'11111',1,'2004-03-04','2004-03-04','','another flower shop','',73732500,NULL);
INSERT INTO Listing VALUES (2,'Linda\'s Flower Shop',2,'123 main st','Philadelphia','PA','11234','9876',-1,2,2,'F','F',1115551235,'',1,'22222',1,'2004-03-04','2004-03-04','','lindas flower shop','',73732500,NULL);
INSERT INTO Listing VALUES (3,'Linda\'s Flower Shop',3,'112 main st','Philadelphia','PA','11234','9876',-1,3,3,'F','F',1115551235,'',1,'33333',1,'2004-03-04','2004-03-04','','lindas flower shop','',73732500,NULL);
INSERT INTO Listing VALUES (4,'Sweat Shop',4,'999 main st','Philadelphia','PA','11234','9876',-1,4,4,'F','F',1115551235,'',6,'33333',1,'2004-03-04','2004-03-04','','sweat shop','',73732500,NULL);

SET SQL_BIG_TABLES=1;
SELECT DISTINCT e.ID, e.attraction, e.business, e.attractionListing, e.feed, e.feedID, e.runTime, e.description, e.ticketInfo, e.priceInfo, e.ageRestriction FROM Event e, Listing li WHERE li.feed=1 AND e.feed=1 AND e.business=li.business ORDER by li.hashKey LIMIT 1,1000;
SET SQL_BIG_TABLES=1;

DROP TABLE IF EXISTS Event, Listing;
[26 Mar 2004 4:34] Michael Widenius
Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

Fix will be in 4.0.19 release