LevelDB storage engine (MDEV-3841)

[MDEV-4099] LevelDB: Wrong results with index and range access after INSERT IGNORE or REPLACE Created: 2013-01-26  Updated: 2013-05-03  Resolved: 2013-05-03

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: None
Fix Version/s: None

Type: Technical task Priority: Major
Reporter: Elena Stepanova Assignee: Sergei Petrunia
Resolution: Fixed Votes: 0
Labels: leveldb

Issue Links:
Relates

 Description   

With INSERT IGNORE 3 results are wrong. The first SELECT returns the right number of rows, but it should have been the one with a = 157. The other also two return duplicate rows. With REPLACE, the results are the same, so the first is correct, and the other two are wrong.

CREATE TABLE t1 (pk INT PRIMARY KEY, a SMALLINT, b INT, KEY (a)) ENGINE=LevelDB;
 
INSERT IGNORE INTO t1 VALUES (1, 157, 0), (2, 1898, -504403), (1, -14659,  0);
 
 
 
SELECT * FROM t1;
 
pk	a	b
 
1	-14659	0
 
2	1898	-504403
 
 
 
EXPLAIN EXTENDED
 
SELECT pk FROM t1;
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 
1	SIMPLE	t1	index	NULL	a	3	NULL	1000	100.00	Using index
 
Warnings:
 
Note	1003	/* select#1 */ select `test`.`t1`.`pk` AS `pk` from `test`.`t1`
 
 
 
SELECT pk FROM t1;
 
pk
 
1
 
1
 
2
 
 
 
EXPLAIN EXTENDED
 
SELECT * FROM t1 WHERE a != 97;
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 
1	SIMPLE	t1	range	a	a	3	NULL	20	100.00	Using where
 
Warnings:
 
Note	1003	/* select#1 */ select `test`.`t1`.`pk` AS `pk`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where (`test`.`t1`.`a` <> 97)
 
 
 
SELECT * FROM t1 WHERE a != 97;
 
pk	a	b
 
1	-14659	0
 
1	-14659	0
 
1	-14659	0
 
2	1898	-504403

Test case:

 
 
CREATE TABLE t1 (pk INT PRIMARY KEY, a SMALLINT, b INT, KEY (a)) ENGINE=LevelDB;
 
INSERT IGNORE INTO t1 VALUES (1, 157, 0), (2, 1898, -504403), (1, -14659,  0);
 
SELECT * FROM t1;
 
SELECT pk FROM t1;
 
SELECT * FROM t1 WHERE a != 97;

bzr version-info

revision-id: psergey@askmonty.org-20130125200959-a7gq7phbaiajqs12
 
revno: 4519
 
branch-nick: mysql-5.6-leveldb



 Comments   
Comment by Sergei Petrunia [ 2013-01-28 ]

This is a consequence of CANT-SEE-OWN-CHANGES property. We run the query:

INSERT IGNORE INTO t1 VALUES (1, 157, 0), (2, 1898, -504403), (1, -14659, 0);

the problem is, when we're writing 3rd record (which is a duplicate), we don't see unapplied changes from the 1st record. Secondary indexes hold

{secondary_key_value, pk}

tuples. This way both (157, 1) and (-14659,1) records are written to the secondary index.

Possible ways out:

  • Lift the CANT-SEE-OWN-CHANGES property.
  • Somehow, build secondary indexes on transaction commit. <-- this may be related to the upcoming task of writes-without-reads mode.
Comment by Elena Stepanova [ 2013-02-06 ]

Reproducible on
revision-id: psergey@askmonty.org-20130201180328-ocmbh9uvcoedmihp
revno: 4591
branch-nick: mysql-5.6-leveldb

Generated at Thu Feb 08 06:53:42 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.