Details
-
Type:
Technical task
-
Status: Closed
-
Priority:
Major
-
Resolution: Fixed
-
Affects Version/s: None
-
Fix Version/s: None
-
Component/s: None
-
Labels:
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
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
Reproducible on
revision-id: psergey@askmonty.org-20130201180328-ocmbh9uvcoedmihp
revno: 4591
branch-nick: mysql-5.6-leveldb
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: