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.
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.