Description:
Crash on query, when ORDER BY on FLOAT column, no crash when doing ROUND() with no decimals before ordering. Verified on MySQL 4.1 and 5.0 och two different machines. Contact me if you need some testdata, crash occurs even when tables only contain a few hundred rows.
How to repeat:
SELECT eset.TERM, SUM(eset.weight) WEIGHT FROM (
SELECT td.TERM,
(1 * ((2*POWER(t.wdf,0.5))/(POWER(1 * (0.5 + 0.5 * p.ndl),0.5)+POWER(t.wdf,0.5)))) *
(LN(((tc.r + 0.5)*(9 - 2 - td.n + tc.r + 0.5)) / ((2 - tc.r + 0.5)*(td.n - tc.r + 0.5)))) * t.wdw + 0.1 * 2 * ((1 - p.ndl) / (1 + p.ndl)) weight
FROM
synd_search_pageindex p,
synd_search_termindex t,
synd_search_termdata td ,
(SELECT tc1.termid, COUNT(DISTINCT tc1.pageid) r
FROM synd_search_termindex tc1
WHERE tc1.pageid IN (-301629717,127644521)
GROUP BY tc1.termid) tc
WHERE
t.pageid = p.pageid AND
t.termid = td.termid AND
t.termid = tc.termid AND (p.section LIKE 'n.%') AND
p.pageid IN (-301629717,127644521) AND
td.n / 9 < 0.25 AND td.n > 1
) eset
GROUP BY eset.term
ORDER BY weight DESC
--
-- Table structure for table `synd_search_pagedata`
--
DROP TABLE IF EXISTS synd_search_pagedata;
CREATE TABLE synd_search_pagedata (
PAGEID int(11) NOT NULL default '0',
LENGTH int(11) default NULL,
TS_UPDATE int(11) default NULL,
TS_CHANGE int(11) default NULL,
INFO_ID tinytext,
INFO_TEXT text,
PRIMARY KEY (PAGEID),
FULLTEXT KEY INFO_TEXT (INFO_TEXT)
) TYPE=MyISAM DEFAULT CHARSET=latin1;
--
-- Table structure for table `synd_search_pageindex`
--
DROP TABLE IF EXISTS synd_search_pageindex;
CREATE TABLE synd_search_pageindex (
PAGEID int(11) NOT NULL default '0',
SECTION char(32) NOT NULL default '',
NDL float(4,3) NOT NULL default '1.000',
PRIMARY KEY (PAGEID),
KEY PAGEID (SECTION,PAGEID,NDL)
) TYPE=MyISAM DEFAULT CHARSET=latin1 PACK_KEYS=1 ROW_FORMAT=FIXED;
--
-- Table structure for table `synd_search_queue`
--
DROP TABLE IF EXISTS synd_search_queue;
CREATE TABLE synd_search_queue (
NODE_ID varchar(64) default NULL
) TYPE=MyISAM DEFAULT CHARSET=latin1;
--
-- Table structure for table `synd_search_termdata`
--
DROP TABLE IF EXISTS synd_search_termdata;
CREATE TABLE synd_search_termdata (
TERMID char(16) NOT NULL default '',
TERM char(32) NOT NULL default '',
N int(11) unsigned NOT NULL default '0',
PRIMARY KEY (TERMID)
) TYPE=MyISAM DEFAULT CHARSET=latin1 PACK_KEYS=1 ROW_FORMAT=FIXED;
--
-- Table structure for table `synd_search_termdata_crc32`
--
DROP TABLE IF EXISTS synd_search_termdata_crc32;
CREATE TABLE synd_search_termdata_crc32 (
TERMID int(11) NOT NULL default '0',
TERM char(32) NOT NULL default '',
N int(11) unsigned NOT NULL default '0',
PRIMARY KEY (TERMID)
) TYPE=MyISAM DEFAULT CHARSET=latin1 PACK_KEYS=1 ROW_FORMAT=FIXED;
--
-- Table structure for table `synd_search_termindex`
--
DROP TABLE IF EXISTS synd_search_termindex;
CREATE TABLE synd_search_termindex (
TERMID char(16) NOT NULL default '',
PAGEID int(11) NOT NULL default '0',
WDF smallint(6) NOT NULL default '0',
WDW float(4,3) NOT NULL default '1.000',
PRIMARY KEY (TERMID,PAGEID),
KEY PAGEID (PAGEID,TERMID)
) TYPE=MyISAM DEFAULT CHARSET=latin1 PACK_KEYS=1 ROW_FORMAT=FIXED;
--
-- Table structure for table `synd_search_termindex_crc32`
--
DROP TABLE IF EXISTS synd_search_termindex_crc32;
CREATE TABLE synd_search_termindex_crc32 (
TERMID int(11) NOT NULL default '0',
PAGEID int(11) NOT NULL default '0',
WDF smallint(6) NOT NULL default '1',
WDW float(4,3) NOT NULL default '1.000',
PRIMARY KEY (TERMID,PAGEID),
KEY PAGEID (PAGEID,TERMID)
) TYPE=MyISAM DEFAULT CHARSET=latin1 PACK_KEYS=1 ROW_FORMAT=FIXED;
Suggested fix:
SELECT eset.TERM, ROUND(SUM(eset.weight)*100) WEIGHT FROM (
