optimizer bug in the index used by mysql/Innodb in the search

[rafarife]

 Description:
     Hello,

     I am working with mysql/InnoDb 4.0.11 under Windows 2000.

     I have the following table:    

   CREATE TABLE `giros` (
    `Doc` varchar(10) NOT NULL default '',
    `Tipo` char(1) NOT NULL default '',
    `NRe` varchar(5) NOT NULL default '',
    `FoP` char(2) NOT NULL default '',
    `FeL` date default NULL,
    `FeV` date default NULL,
    `NRm` varchar(6) NOT NULL default '',
    `Pis` char(2) NOT NULL default '',
    `Imp` decimal(20,4) NOT NULL default '0.0000',
    `Cod` varchar(6) NOT NULL default '',
    `Nom` varchar(40) NOT NULL default '',
    `Dir` varchar(35) NOT NULL default '',
    `Cop` varchar(6) NOT NULL default '',
    `Pob` varchar(30) NOT NULL default '',
    `Pro` varchar(25) NOT NULL default '',
    `BCd` varchar(8) NOT NULL default '',
    `BNm` varchar(40) NOT NULL default '',
    `BDr` varchar(35) NOT NULL default '',
    `BPb` varchar(30) NOT NULL default '',
    `BPr` varchar(25) NOT NULL default '',
    `Cba` varchar(10) NOT NULL default '',
    `Dct` char(2) NOT NULL default '',
    `NLe` text,
    `Cob` tinyint(4) unsigned NOT NULL default '0',
    `Ctb` tinyint(4) unsigned NOT NULL default '0',
    `BCp` varchar(6) NOT NULL default '',
    `Impreso` tinyint(4) unsigned NOT NULL default '0',
    `Est` varchar(20) NOT NULL default '',
    `Linea` int(11) NOT NULL default '0',
     PRIMARY KEY  (`Tipo`,`Doc`,`NRe`),
     KEY `NRmLinea` (`NRm`,`Linea`),
     KEY `TipoFeVCod` (`Tipo`,`FeV`,`Cod`),
     KEY `CodTipoDocFeV` (`Cod`,`Tipo`,`Doc`,`FeV`)
) TYPE=InnoDB;

   I ran the following query:

   SELECT TIPO,DOC,NRE
   FROM GIROS   
   WHERE (GIROS.TIPO='R' AND GIROS.DOC='ZA03003996' AND GIROS.NRE<'01/01') 
   OR (GIROS.TIPO='R' AND GIROS.DOC<'ZA03003996') OR GIROS.TIPO<'R' 
   ORDER BY GIROS.TIPO DESC, GIROS.DOC DESC,GIROS.NRE DESC LIMIT 1

   Time: 0.20 secs.
   Explain:         
   table   type  possible_keys       key        key_len  ref   rows  Extra
   GIROS   range PRIMARY,TipoFeVCod  TipoFeVCod    1     NULL  9417  Using where; Using
index; Using filesort

   I changed the table type to MyIsam (alter table giros type=MyIsam),
   and I ran the above query:

   Time:0.02 secs       
   Explain:
   table   type  possible_keys       key        key_len  ref   rows   Extra
   GIROS   range PRIMARY,TipoFeVCod  PRIMARY       16    NULL 19472   Using where; Using
index


   So, MySQL/MyIsam uses the right index (the primary index) while MySQL/InnoDB uses
   a wrong index (TipoFeVCod).
   
   Thanks in advance,
   Rafa

How-To-Repeat:
   Select ... from giros ...

Fix:
    -

Synopsis:optimizer bug in the index used by mysql/Innodb in the search

Submitter-Id:   <submitter ID>
Originator: Rafa
Organization:   Pecomark
MySQL support:  none
Severity:   non-critical
Priority:   medium
Category:   mysqld-max-nt
Class:  sw-bug
Release:    mysqld 4.0.11 Gamma(InnoDB)

Exectutable:   mysqld-max-nt
Environment:   Pentium III-MMX, 500 MHZ, 540 MB
System:        Windows 2000
Compiler:      -
Architecture:  i



__________________________________________________________________
The NEW Netscape 7.0 browser is now available. Upgrade now!
http://channels.netscape.com/ns/browsers/download.jsp 

Get your own FREE, personal Netscape Mail account today at http://webmail.netscape.com/