Bug #45086 A SELECT using ORDER BY and LIMIT sometimes misses one row
Submitted: 26 May 2009 7:10 Modified: 16 Jul 2009 11:03
Reporter: Lars-Erik Bjørk Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Falcon storage engine Severity:S3 (Non-critical)
Version:6.0.11 OS:Any
Assigned to: Assigned Account CPU Architecture:Any
Tags: F_INDEX

[26 May 2009 7:10] Lars-Erik Bjørk 
Description:
This bug is filed as a part of the investigation of "umbrella" bug#42915 

When running a modified version of falcon_nolimit_int.yy, the test will sometimes fail
with falcon returning 1 row less that expected when using LIMIT, even though the correct number of rows are returned when LIMIT is not used. 

The modified version of falcon_limit_int.yy looks like this:

query:
        select | dml | dml | dml | dml | dml ;

dml:
        update ;

select:
        SELECT * FROM _table where;

where:
        |
        WHERE _field sign value ;

sign:
        < ;

insert:
        INSERT INTO _table ( _field , _field ) VALUES ( value , value ) ;

update:
        UPDATE _table AS X SET _field = value where ;

value:
        _digit | _tinyint_unsigned ;

# Use only indexed fields:

_field:
        `int_key` ;

_table
        `E` ;

The test can be run with the following command from the gentest directory, given you have
branched the mysql-test-extra-6.0 tree:

perl runall.pl \
--basedir=<path_to_your_basedir> \
--mysqld=--loose-innodb-lock-wait-timeout=1 \
--mysqld=--table-lock-wait-timeout=1 \
--mysqld=--loose-falcon-lock-wait-timeout=1 \
--mysqld=--loose-falcon-debug-mask=2 \
--mysqld=--skip-safemalloc \
--grammar=conf/falcon_nolimit_int.yy \
--threads=1 --validator=Limit \ --reporters=Deadlock,ErrorLog,Backtrace,WinPackage \
--duration=1200 \
--vardir=/tmp/vardir \
--mysqld=--log-output=file \
--queries=100000 \
--engine=falcon

How to repeat:
See above
[8 Jun 2009 12:21] Lars-Erik Bjørk 
After analyzing the issue, I have come to the conclusion that this is not a LIMIT bug, but a general index bug. The bug is however more likely to be visible when accessing using LIMIT because this causes a more strict traversal of the index.

The problem is that an index entry is missing from the index.
[16 Jul 2009 11:03] Lars-Erik Bjørk 
Closed as a duplicate of the newer bug#46144, as this gives a more accurate synopsis and description of the problem