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','0101109000
0'),('37989','01019000000'),('37990','01019011000'),('37991','01019011000'),('37992','0101
9019000'),('37993','01019030000'),('37994','01019090000'),('475','02070000000'),('25253','
02071100000'),('25255','02071100000'),('25256','02071110000'),('25258','02071130000'),('25
259','02071190000'),('25260','02071200000'),('25261','02071210000'),('25262','02071290000'
),('25263','02071300000'),('25264','02071310000'),('25265','02071310000'),('25266','020713
20000'),('25267','02071320000'),('25269','02071330000'),('25270','02071340000'),('25271','
02071350000'),('25272','02071360000'),('25273','02071370000'),('25281','02071391000'),('25
282','02071391000'),('25283','02071399000'),('25284','02071400000'),('25285','02071410000'
),('25286','02071410000'),('25287','02071420000'),('25288','02071420000'),('25291','020714
30000'),('25290','02071440000'),('25292','02071450000'),('25293','02071460000'),('25294','
02071470000'),('25295','02071491000'),('25296','02071491000'),('25297','02071499000'),('25
298','02072400000'),('25299','02072400000'),('25300','02072410000'),('25301','02072490000'
),('25302','02072500000'),('25303','02072510000'),('25304','02072590000'),('25305','020726
00000'),('25307','02072610000'),('25308','02072610000'),('25309','02072620000'),('25310','
02072620000'),('25311','02072630000'),('25312','02072640000'),('25313','02072650000'),('25
314','02072660000'),('25315','02072660000'),('25316','02072670000'),('25317','02072680000'
),('25318','02072691000'),('25319','02072691000'),('25320','02072699000'),('25321','020727
00000'),('25322','02072710000'),('25323','02072710000'),('25324','02072720000'),('25327','
02072720000'),('25328','02072730000'),('25329','02072740000'),('25330','02072750000'),('25
331','02072760000'),('25332','02072760000'),('25333','02072770000'),('25335','02072780000'
),('25336','02072791000'),('25337','02072791000'),('25338','02072799000'),('25339','020732
00000'),('25340','02073200000'),('25341','02073211000'),('25342','02073211000'),('25343','
02073215000'),('25344','02073219000'),('25349','02073251000'),('25350','02073251000'),('25
351','02073259000'),('25352','02073290000'),('25369','02073300000'),('25371','02073311000'
),('25372','02073311000'),('25373','02073319000'),('25374','02073351000'),('25375','020733
51000'),('25376','02073359000'),('25378','02073390000'),('25379','02073400000'),('25381','
02073410000'),('25382','02073490000'),('25385','02073500000'),('25386','02073511000'),('25
388','02073511000'),('25391','02073511000'),('25392','02073515000'),('25397','02073521000'
),('25399','02073521000'),('25400','02073521000'),('25402','02073523000'),('25404','020735
25000'),('25406','02073531000'),('25411','02073541000'),('25414','02073551000'),('25415','
02073551000'),('25416','02073553000'),('25423','02073561000'),('25425','02073561000'),('25
427','02073563000'),('43185','02073571000'),('35821','02073579000'),('25442','02073591000'
),('42866','02073591000'),('42869','02073599000'),('25447','02073600000'),('25448','020736
11000'),('25449','02073611000'),('25450','02073611000'),('25452','02073615000'),('25459','
02073621000'),('25461','02073621000'),('25465','02073621000'),('25467','02073623000'),('25
469','02073625000'),('25470','02073631000'),('25476','02073641000'),('25479','02073651000'
),('25480','02073651000'),('25481','02073653000'),('25484','02073661000'),('25485','020736
61000'),('25486','02073663000'),('43186','02073671000'),('35818','02073679000'),('25496','
02073681000'),('25497','02073681000'),('25498','02073681000'),('25500','02073685000'),('25
501','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.

verified on linux, also happens with latest bk versions for 4.1 and 5.0

Mentioned in 4.1.10 change notes.

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