Bug #7331 Mysql returns empty set when using order
Submitted: 15 Dec 2004 18:15 Modified: 26 Jan 2005 23:54
Reporter: Dieter Thome Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:4.1.7 OS:Microsoft Windows (Windows XP SP2)
Assigned to: Timour Katchaounov CPU Architecture:Any

[15 Dec 2004 18:15] Dieter Thome
Description:
A simple SELECT statement that uses an index returns the correct number of rows when placed without 'order by' statement and an empty set when placed with 'order by' statement. Although when you drop one index yout get the correct set. Furthermore when you reduce the number of records, you get the correct set.

How to repeat:
CREATE TABLE `bugtab9` (
  `sid` decimal(8,0) default NULL,
  `wnid` varchar(11) NOT NULL default '',
  KEY `wnid14` (`wnid`(4)),
  KEY `wnid` (`wnid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO `bugtab9` (`sid`, `wnid`) VALUES ('101','01010000000'),('37986','01011000000'),('37987','01011010000'),('39560','01011090000'),('37989','01019000000'),('37990','01019011000'),('37991','01019011000'),('37992','01019019000'),('37993','01019030000'),('37994','01019090000'),('475','02070000000'),('25253','02071100000'),('25255','02071100000'),('25256','02071110000'),('25258','02071130000'),('25259','02071190000'),('25260','02071200000'),('25261','02071210000'),('25262','02071290000'),('25263','02071300000'),('25264','02071310000'),('25265','02071310000'),('25266','02071320000'),('25267','02071320000'),('25269','02071330000'),('25270','02071340000'),('25271','02071350000'),('25272','02071360000'),('25273','02071370000'),('25281','02071391000'),('25282','02071391000'),('25283','02071399000'),('25284','02071400000'),('25285','02071410000'),('25286','02071410000'),('25287','02071420000'),('25288','02071420000'),('25291','02071430000'),('25290','02071440000'),('25292','02071450000'),('25293','02071460000'),('25294','02071470000'),('25295','02071491000'),('25296','02071491000'),('25297','02071499000'),('25298','02072400000'),('25299','02072400000'),('25300','02072410000'),('25301','02072490000'),('25302','02072500000'),('25303','02072510000'),('25304','02072590000'),('25305','02072600000'),('25307','02072610000'),('25308','02072610000'),('25309','02072620000'),('25310','02072620000'),('25311','02072630000'),('25312','02072640000'),('25313','02072650000'),('25314','02072660000'),('25315','02072660000'),('25316','02072670000'),('25317','02072680000'),('25318','02072691000'),('25319','02072691000'),('25320','02072699000'),('25321','02072700000'),('25322','02072710000'),('25323','02072710000'),('25324','02072720000'),('25327','02072720000'),('25328','02072730000'),('25329','02072740000'),('25330','02072750000'),('25331','02072760000'),('25332','02072760000'),('25333','02072770000'),('25335','02072780000'),('25336','02072791000'),('25337','02072791000'),('25338','02072799000'),('25339','02073200000'),('25340','02073200000'),('25341','02073211000'),('25342','02073211000'),('25343','02073215000'),('25344','02073219000'),('25349','02073251000'),('25350','02073251000'),('25351','02073259000'),('25352','02073290000'),('25369','02073300000'),('25371','02073311000'),('25372','02073311000'),('25373','02073319000'),('25374','02073351000'),('25375','02073351000'),('25376','02073359000'),('25378','02073390000'),('25379','02073400000'),('25381','02073410000'),('25382','02073490000'),('25385','02073500000'),('25386','02073511000'),('25388','02073511000'),('25391','02073511000'),('25392','02073515000'),('25397','02073521000'),('25399','02073521000'),('25400','02073521000'),('25402','02073523000'),('25404','02073525000'),('25406','02073531000'),('25411','02073541000'),('25414','02073551000'),('25415','02073551000'),('25416','02073553000'),('25423','02073561000'),('25425','02073561000'),('25427','02073563000'),('43185','02073571000'),('35821','02073579000'),('25442','02073591000'),('42866','02073591000'),('42869','02073599000'),('25447','02073600000'),('25448','02073611000'),('25449','02073611000'),('25450','02073611000'),('25452','02073615000'),('25459','02073621000'),('25461','02073621000'),('25465','02073621000'),('25467','02073623000'),('25469','02073625000'),('25470','02073631000'),('25476','02073641000'),('25479','02073651000'),('25480','02073651000'),('25481','02073653000'),('25484','02073661000'),('25485','02073661000'),('25486','02073663000'),('43186','02073671000'),('35818','02073679000'),('25496','02073681000'),('25497','02073681000'),('25498','02073681000'),('25500','02073685000'),('25501','02073689000'),('37894','02073690000');

SELECT * from bugtab9 where wnid like '0101%' 
-> returns 10 rows

SELECT * from bugtab9 where wnid like '0101%' order by wnid
-> return empty set

Suggested fix:
If you drop the index wnid14 you get the correct result set even when you use the order clause.
[15 Dec 2004 20:05] Hartmut Holzgraefe
verified on linux, also happens with latest bk versions for 4.1 and 5.0
[26 Jan 2005 23:54] Paul DuBois
Mentioned in 4.1.10 change notes.
[10 Feb 2009 8:36] Timour Katchaounov
Hello Michael,

Even if it looks to be the same problem it might not be. For a number
of reasons we do not reopen closed bugs. Could you please submit a new
bug with a test case, and in the new bug mention that it looks like a
regression of this one.

There is no point in reopening this bug as its test case is included in
all MySQL versions, and it has been run as part of the test suite on a
daily basis for years. Therefore the old test case is of no help to us
to analyze and fix the new problem you have encountered.

Thank you!

Timour