| Bug #3155 | strange results with index (x, y) ... WHERE x=val_1 AND y>=val_2 ORDER BY pk; | ||
|---|---|---|---|
| Submitted: | 12 Mar 2004 6:02 | Modified: | 17 Mar 2004 7:26 |
| Reporter: | Alexander Keremidarski | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server | Severity: | S1 (Critical) |
| Version: | 4.1 | OS: | Any (any) |
| Assigned to: | Ramil Kalimullin | CPU Architecture: | Any |
[12 Mar 2004 7:24]
Alexander Keremidarski
Test case is > 200kb so here is URL of original file http://www.dragondata.com/~toasty/test-schema-and-data.bz2 (431KB)
[17 Mar 2004 3:34]
Ramil Kalimullin
ChangeSet 1.1780 04/03/17 15:30:17 ram@gw.mysql.r18.ru +1 -0 Fix for the bug #3155: strange results with index (x, y) ... WHERE x=val_1 AND y>=val_2 ORDER BY pk; (test case is too big to be included, take a look at the bug database)
[17 Mar 2004 7:26]
Ramil Kalimullin
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:
Added test case.
Description: This bug happens only under special conditions. SELECT ind_column WHERE x = 1 AND y >= 0 FROM tbl ORDER BY pk_column; Returns wrong result ig: 1. Table has Index (ind_column), and composite Secondary index (x, y) ind_col can be indexed by Primary Key or Secondary Index - effect is the same. 2. x is some INTeger column 3. Cardinality in index (x, y) is "high enough". In my case with cardinality < 6 bug doesn't happen 4. Value in x = val_1 is "popular enough". I couldn't find specific margin, but if there are just few such values bug doesn't happen. 5. It depends how value is compared a = 1234 -> bug a = '1234' -> bug a in (1234) -> No bug a in ('1234') -> No bug 6. There is ORDER BY clause. Without it result is always correct. 7. Respective SELECT COUNT(*) query always returns correct result (no ORDER BY there) I failed to minimize test case so I am posting here original test case as sent by Kevin Day. Many special thanks to Kevin for perfect test case! Original report was with InnoDB table, but it is is not relevant. Both MyISAM and InnoDB expose this bug. CREATE TABLE `h` ( `p` int(11) unsigned NOT NULL auto_increment, `c` smallint(11) unsigned NOT NULL default '0', `s` smallint(11) NOT NULL default '0', PRIMARY KEY (`p`), KEY `c` (`c`), KEY `s` (`s`), KEY `cs` (`c`,`s`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; Note that this seems to be related to #2959, but is not! I am testing with mysql-4.1 bk tree ChangeSet@1.1776.1.1, 2004-03-12 12:04:00+04:00, hf@deer.(none) where bug #2959 is already fixed How to repeat: Populate table with attached test-schema-and-data.bz2 Run: -- Return empty set SELECT * FROM h WHERE c=1941 AND s>=0 ORDER BY p DESC; -- Returns correct number of 2655 matched rows SELECT COUNT(*) FROM h WHERE c=1941 AND s>=0 ORDER BY p DESC; -- Returns correct result consisting of 2655 rows SELECT * FROM h IGNORE INDEX(cs) WHERE c=1941 AND s>=0 ORDER BY p DESC; -- Returns correct result consisting of 2655 rows SELECT * FROM h WHERE c=1941 AND s>=0; -- Returns correct result consisting of 2655 rows SELECT * FROM h WHERE c IN(1941) AND s>=0 ORDER BY p DESC;