[#MDEV-4323] LevelDB: Wrong result (missing rows) with ORDER BY DESC LIMIT after seemingly unrelated DDL/DML

The provided test case is long and looks weird, but I can't remove anything else from it. There are 9 tables in the test, only two of them are involved in the SELECT in question, but removing any previous actions on any of the other tables eliminates the wrong result.
I could not find any differences in valgrind warnings between a "good" and "bad" test case.

Actual result of the test case with LevelDB is an empty set.
Expected result (and actual result with InnoDB and MyISAM):

+------+

| v3   |

+------+

| I    |

| c    |

+------+

EXPLAIN with LevelDB:

+----+-------------+-------+-------+---------------+------+---------+------------+------+-----------+-------+

| id | select_type | table | type  | possible_keys | key  | key_len | ref        | rows | filtered  | Extra |

+----+-------------+-------+-------+---------------+------+---------+------------+------+-----------+-------+

|  1 | SIMPLE      | t8    | index | NULL          | v3   | 13      | NULL       |    1 | 100000.00 | NULL  |

|  1 | SIMPLE      | t4    | ref   | v3            | v3   | 13      | test.t8.v3 |   11 |    100.00 | NULL  |

+----+-------------+-------+-------+---------------+------+---------+------------+------+-----------+-------+

+-------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| Note  | 1003 | /* select#1 */ select `test`.`t8`.`v3` AS `v3` from `test`.`t8` left join `test`.`t4` on((`test`.`t8`.`v3` = `test`.`t4`.`v3`)) where 1 order by `test`.`t8`.`v3` desc limit 10 |

+-------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

EXPLAIN with InnoDB:

+----+-------------+-------+-------+---------------+------+---------+------------+------+----------+-------------+

| id | select_type | table | type  | possible_keys | key  | key_len | ref        | rows | filtered | Extra       |

+----+-------------+-------+-------+---------------+------+---------+------------+------+----------+-------------+

|  1 | SIMPLE      | t8    | index | NULL          | v3   | 13      | NULL       |    2 |   100.00 | Using index |

|  1 | SIMPLE      | t4    | ref   | v3            | v3   | 13      | test.t8.v3 |    1 |   100.00 | Using index |

+----+-------------+-------+-------+---------------+------+---------+------------+------+----------+-------------+

+-------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| Note  | 1003 | /* select#1 */ select `test`.`t8`.`v3` AS `v3` from `test`.`t8` left join `test`.`t4` on((`test`.`t8`.`v3` = `test`.`t4`.`v3`)) where 1 order by `test`.`t8`.`v3` desc limit 10 |

+-------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

EXPLAIN with MyISAM:

+----+-------------+-------+-------+---------------+------+---------+------+------+----------+-----------------------------------------------------------------+

| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                                           |

+----+-------------+-------+-------+---------------+------+---------+------+------+----------+-----------------------------------------------------------------+

|  1 | SIMPLE      | t8    | index | NULL          | v3   | 13      | NULL |    2 |   100.00 | Using index; Using temporary; Using filesort                    |

|  1 | SIMPLE      | t4    | index | v3            | v3   | 13      | NULL |    2 |   100.00 | Using where; Using index; Using join buffer (Block Nested Loop) |

+----+-------------+-------+-------+---------------+------+---------+------+------+----------+-----------------------------------------------------------------+

+-------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| Note  | 1003 | /* select#1 */ select `test`.`t8`.`v3` AS `v3` from `test`.`t8` left join `test`.`t4` on((`test`.`t8`.`v3` = `test`.`t4`.`v3`)) where 1 order by `test`.`t8`.`v3` desc limit 10 |

+-------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

Test case:

SET storage_engine = LevelDB;

CREATE TABLE t1 (

 c1 char(8),

 v1 varchar(1024),

 b1 bigint unsigned,

 v2 varchar(10),

 pk char(16),

 v3 varchar(10),

 d1 date,

 i1 int,

 i2 int,

 dt2 datetime,

 v4 varchar(1024),

 c2 char(8),

 key (c1),

 key (v1),

 key (b1),

 key (v2),

 primary key (pk),

 key (v3),

 key (d1),

 key (i2),

 key (dt2),

 key (v4),

 key (c2)

);

CREATE TABLE t2 (

 dt2 datetime,

 d1 date,

 v4 varchar(1024),

 v1 varchar(1024),

 pk char(16),

 v2 varchar(10),

 v3 varchar(10),

 i2 int,

 c1 char(8),

 c2 char(8),

 dt1 datetime,

 b1 bigint unsigned,

 i1 int,

 key (dt2),

 key (d1),

 key (v4),

 key (v1),

 primary key (pk),

 key (v2),

 key (v3),

 key (i2),

 key (c1),

 key (c2),

 key (b1)

);

CREATE TABLE t3 (

 pk char(16),

 v3 varchar(10),

 i2 int,

 b1 bigint unsigned,

 i1 int,

 c1 char(8),

 v1 varchar(1024),

 dt2 datetime,

 d1 date,

 dt1 datetime,

 c2 char(8),

 v4 varchar(1024),

 v2 varchar(10),

 primary key (pk),

 key (v3),

 key (i2),

 key (b1),

 key (c1),

 key (v1),

 key (dt2),

 key (d1),

 key (c2),

 key (v4),

 key (v2)

);

CREATE TABLE t4 (

 b1 bigint unsigned,

 c2 char(8),

 pk char(16),

 v1 varchar(1024),

 dt2 datetime,

 d1 date,

 dt1 datetime,

 i2 int,

 i1 int,

 v4 varchar(1024),

 v3 varchar(10),

 c1 char(8),

 v2 varchar(10),

 key (b1),

 key (c2),

 primary key (pk),

 key (v3)

);

INSERT INTO t4 VALUES

(9, 'k', 1, 'g', '2005-01-01 00:00:00', '2001-01-01', '2005-08-01 00:00:00', 0, -3, 'a', 'o', 'c', 'e'),

(6, 's', 2, 'A', '2012-12-12 07:09:58', '2006-06-16', '2007-01-01 00:00:00', -1, 1, 'i', 't', 'K', 'c');

CREATE TABLE t5 (

 v3 varchar(10),

 c2 char(8),

 c1 char(8),

 dt1 datetime,

 d1 date,

 pk char(16),

 v4 varchar(1024),

 b1 bigint unsigned,

 i2 int,

 dt2 datetime,

 v2 varchar(10),

 v1 varchar(1024),

 i1 int,

 key (v3),

 key (c2),

 key (c1),

 key (d1),

 primary key (pk),

 key (v4),

 key (b1),

 key (i2),

 key (dt2),

 key (v2),

 key (v1)

);

INSERT INTO t5 VALUES

('H', 'N', 'Q', '2000-02-07 00:00:00', '2005-01-11', 'k1', 'O', 2, -1, '2000-01-01 00:00:00', 'm', 'o', 8),

('s', 'W', 't', '2007-05-03 00:00:00', '2005-05-26', 'k2', 'y', 1, -2, '2005-07-15 00:00:00', 'g', 'i', 4);

CREATE TABLE t6 (

 pk char(16),

 c1 char(8),

 dt1 datetime,

 i1 int,

 v5 varchar(1024),

 dt2 datetime,

 v6 varchar(10),

 v2 varchar(10),

 v3 varchar(10),

 v4 varchar(1024),

 d1 date,

 c2 char(8),

 b1 bigint unsigned,

 i2 int,

 v1 varchar(1024),

 primary key (pk),

 key (c1 ),

 key (dt2 ),

 key (v2 ),

 key (v3 ),

 key (v4 ),

 key (d1 ),

 key (c2 ),

 key (b1 ),

 key (i2 ),

 key (v1 ));

CREATE TABLE t7 (

 d1 date,

 v2 varchar(10),

 v1 varchar(1024),

 c1 char(8),

 i1 int,

 c2 char(8),

 dt1 datetime,

 v4 varchar(1024),

 i2 int,

 pk char(16),

 v6 varchar(10),

 b1 bigint unsigned,

 v5 varchar(1024),

 v3 varchar(10),

 dt2 datetime,

 key (d1),

 key (v2),

 key (v1),

 key (c1),

 key (c2),

 key (v4),

 key (i2),

 primary key (pk),

 key (b1),

 key (v3),

 key (dt2)

);

CREATE TABLE t8 (

 d1 date,

 pk char(16),

 i2 int,

 v2 varchar(10),

 v3 varchar(10),

 dt1 datetime,

 v6 varchar(10),

 v5 varchar(1024),

 v1 varchar(1024),

 c2 char(8),

 dt2 datetime,

 v4 varchar(1024),

 b1 bigint unsigned,

 c1 char(8),

 i1 int,

 key (d1),

 primary key (pk),

 key (i2),

 key (v2),

 key (v3),

 key (v1),

 key (c2),

 key (dt2),

 key (v4),

 key (b1),

 key (c1)

);

INSERT INTO t8 VALUES  # PRESERVE

('2003-02-10', 'a', 4, 't', 'I', '2009-12-07 15:55:58', 'y', 'W', 'u', 't', '2012-12-12 16:36:32', 'j', 5, 'i', 1),

('2002-08-24', 'b', 1, 'w', 'c', '2008-06-16 01:07:12', 'a', 'w', 'j', 'a', '2012-12-12 04:40:12', 't', 4, 'w', 3);

CREATE TABLE t9 (

 c2 char(8),

 dt2 datetime,

 v4 varchar(1024),

 c1 char(8),

 v1 varchar(1024),

 b1 bigint unsigned,

 dt1 datetime,

 i2 int,

 v6 varchar(10),

 v5 varchar(1024),

 v3 varchar(10),

 pk char(16),

 v2 varchar(10),

 i1 int,

 d1 date,

 key (c2),

 key (dt2),

 key (v4),

 key (c1),

 key (v1),

 key (b1),

 key (i2),

 key (v3),

 primary key (pk),

 key (d1)

);

ALTER TABLE t2 ADD INDEX idx (i2, pk);

DROP INDEX idx ON t2;

ALTER TABLE t9 ADD INDEX idx (pk, i2, i1);

ALTER TABLE t6 ADD INDEX idx (i2, pk, i1);

DROP INDEX idx ON t9;

DROP INDEX idx ON t6;

ALTER TABLE t7 ADD INDEX idx (i2, pk);

DROP INDEX idx ON t7;

ALTER TABLE t1 ADD INDEX idx (pk, i2, i1);

DROP INDEX idx ON t1;

ALTER TABLE t2 ADD INDEX idx (i2, pk, i1);

DROP INDEX idx ON t2;

ALTER TABLE t6 ADD INDEX idx (v6(1), v5(200));

DROP INDEX idx ON t6;

ALTER TABLE t7 ADD INDEX idx (v6(7), v5(200));

ALTER TABLE t3 ADD INDEX idx (i2, pk, i1);

DROP INDEX idx ON t3;

DROP INDEX idx ON t7;

ALTER TABLE t5 ADD INDEX idx (i2, pk);

ALTER TABLE t6 ADD INDEX idx (i2, pk);

DROP INDEX idx ON t5;

SELECT t8.v3 FROM t8 LEFT JOIN t4 ON t8.v3 = t4.v3 ORDER BY t8.v3 DESC LIMIT 10;

revision-id: psergey@askmonty.org-20130322111245-43wqvaouclxso5zo

revno: 4807

branch-nick: mysql-5.6-leveldb

LevelDB Storage Engine MS2 (MDEV-4201) [MDEV-4323] LevelDB: Wrong result (missing rows) with ORDER BY DESC LIMIT after seemingly unrelated DDL/DML Created: 2013-03-24 Updated: 2013-03-26 Resolved: 2013-03-26
Status:	Closed
Project:	MariaDB Server
Component/s:	None
Affects Version/s:	None
Fix Version/s:	None

[MDEV-4323] LevelDB: Wrong result (missing rows) with ORDER BY DESC LIMIT after seemingly unrelated DDL/DML Created: 2013-03-24 Updated: 2013-03-26 Resolved: 2013-03-26