mysql> select gid, fbin from fdata where gid = 3557 order by fbin; +------+--------------+ | gid | fbin | +------+--------------+ | 3557 | 1000.007086 | | 3557 | 10000.000708 | | 3557 | 10000.000708 | | 3557 | 10000.000708 | +------+--------------+ 4 rows in set (0.00 sec)
mysql> select gid, fbin from fdata where fbin between 10000.000708 and 10000.000709;
+------+--------------+
| gid | fbin |
+------+--------------+
| 3442 | 10000.000709 |
| 3442 | 10000.000709 |
| 3558 | 10000.000709 |
| 3558 | 10000.000709 |
+------+--------------+
4 rows in set (0.13 sec)
Note that none of these rows have an fbin of 10000.000708, which is the fbin of the last three rows from the first result; furthermore, if I try to extract those three rows by their fbin value directly:
mysql> select gid, fbin from fdata where fbin = 10000.000708; Empty set (0.00 sec)
Yet:
mysql> select 10000.000708 BETWEEN 10000.000708 AND 10000.000709; +----------------------------------------------------+ | 10000.000708 BETWEEN 10000.000708 AND 10000.000709 | +----------------------------------------------------+ | 1 | +----------------------------------------------------+ 1 row in set (0.00 sec)
The definition of the fdata table is:
CREATE TABLE fdata (
fid int(11) NOT NULL auto_increment,
fref varchar(100) NOT NULL default '',
fstart int(10) unsigned NOT NULL default '0',
fstop int(10) unsigned NOT NULL default '0',
fbin double(20,6) NOT NULL default '0.000000',
ftypeid int(11) NOT NULL default '0',
fscore float default NULL,
fstrand enum('+','-') default NULL,
fphase enum('0','1','2') default NULL,
gid int(11) NOT NULL default '0',
ftarget_start int(10) unsigned default NULL,
ftarget_stop int(10) unsigned default NULL,
PRIMARY KEY (fid),
UNIQUE KEY fref (fref,fbin,fstart,fstop,ftypeid,gid),
KEY ftypeid (ftypeid),
KEY gid (gid)
) TYPE=MyISAM;Increasing the precision of fbin from 6 to 8 did not fix the problem; upgrading to the latest 4.1.7 did not fix the problem; downgrading to 4.0.22 did fix the problem.
I'd very much like to upgrade back to 4.1 series, as the improvement to filesort seems to greatly affect my application ... I'm quite willing to apply a software patch locally to fix the problem.
Thanks,
-Aaron
-- Aaron J. Mackey, Ph.D. Dept. of Biology, Goddard 212 University of Pennsylvania email: [EMAIL PROTECTED] 415 S. University Avenue office: 215-898-1205 Philadelphia, PA 19104-6017 fax: 215-746-6697
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
