Bug #18766 adding an index to a table changes the result set for a query

• Submitted: 4 Apr 2006 5:07
• Modified: 4 Apr 2006 10:11
• Reporter: peter conza
• Status: Closed
• Category: MySQL Server
• Severity: S1 (Critical)
• Version: 5.0.19-nt
• OS: server 2003 or winXP
• CPU Architecture: Any

[4 Apr 2006 5:07] peter conza

Description:
the same select statement produces different results after adding an index.

How to repeat:
DROP TABLE IF EXISTS test.testtab;
CREATE TABLE test.testtab (
  `autonum` int(10) unsigned NOT NULL auto_increment,
  `client` varchar(10) default NULL,
  `workdate` date default NULL,
  PRIMARY KEY  (`autonum`)
) ENGINE=myisam DEFAULT CHARSET=latin1;

INSERT INTO test.testtab VALUES 
(1,'ABC','2006-02-02'),(2,'ABC','2006-03-03')
,(3,'DEF','2005-01-01'),(4,'DEF','2005-07-20')
,(5,'GHI','2005-09-01'),(6,'GHI','2006-01-01');

SELECT client FROM test.testtab where datediff(curdate(),workdate)<100 group by client;

you will get two clients ABC and GHI which is the correct result set

now:

ALTER TABLE test.testtab ADD INDEX `byclientworkdate`(`client`, `workdate`);

SELECT client FROM test.testtab where datediff(curdate(),workdate)<100 group by client;

after adding the index you will get only one client ABC which is a bug.

Suggested fix:
i had to remove the index.

[4 Apr 2006 10:07] Hartmut Holzgraefe

test case

Attachment: bug18766.tar.gz (application/x-gzip, text), 787 bytes.

[4 Apr 2006 10:11] Hartmut Holzgraefe

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

this actually looks like a duplicate of bug #16710 which has been fixed recently,
the test case works fine with latest 5.0 sources