Description:
INDEX_MERGE_INTERSECTION causes wrong query results, when PK is in descending order. Due to the default PK order being in ascending order, the rowid is also sorted in ascending order of same prefix of key in the secondary index. While PK is sorted in descending order, when comparing rowids of the RowIDIntersectionIterator, there may be directional errors during recursive comparison.
For example, the intersect operator is responsible for merging RowIterator child#1 and RowIterator child#2. When the rowid of child#1 is 6 and the rowid of child#2 is 5.
If the PK is arranged in ascending order: it is necessary to continue iterating on child#2 to find a rowid greater than 5 to match child#1's rowid=6.
If the PK is arranged in descending order: it is necessary to continue iterating on child#1 to find a rowid less than 6 to match child#2's rowid=5.
How to repeat:
TEST_CASE:
CREATE TABLE t1 (
pk INT NOT NULL AUTO_INCREMENT,
a INT NOT NULL,
b INT NOT NULL,
c INT NOT NULL,
d INT NOT NULL,
PRIMARY KEY pk (pk desc),
KEY idx_a (a),
KEY idx_b (b),
KEY idx_c (c),
KEY idx_d (d)
);
--disable_query_log
# Inserting a lot of rows in order to enable index_merge intersect
INSERT INTO t1(a,b,c,d) VALUES
( RAND()*5, RAND()*5, RAND()*5, RAND()*5 );
let $cnt=4;
while ($cnt)
{
INSERT INTO t1(a,b,c,d) SELECT 6,6,6,6 FROM t1;
dec $cnt;
}
INSERT INTO t1(a,b,c,d) SELECT 6, RAND()*5, RAND()*5,
RAND()*5 FROM t1 LIMIT 3;
INSERT INTO t1(a,b,c,d) SELECT RAND()*5, 6, RAND()*5,
RAND()*5 FROM t1 LIMIT 3;
INSERT INTO t1(a,b,c,d) SELECT RAND()*5, RAND()*5, 6,
RAND()*5 FROM t1 LIMIT 3;
INSERT INTO t1(a,b,c,d) SELECT RAND()*5, RAND()*5,
RAND()*5, 6 FROM t1 LIMIT 3;
let $cnt=7;
while ($cnt)
{
INSERT INTO t1(a,b,c,d) SELECT RAND()*5, RAND()*5,
RAND()*5, RAND()*5 FROM t1;
dec $cnt;
}
--enable_query_log
ANALYZE TABLE t1;
EXPLAIN SELECT COUNT(*), SUM(a) FROM t1 WHERE b=6 AND c=6;
# RESULT ERROR
SELECT COUNT(*), SUM(a) FROM t1 WHERE b=6 AND c=6;
DROP TABLE t1;
Suggested fix:
Identify the sorting order of the PK. When creating the RowIDIntersectionIterator, we identify the PK sorting order. If sorting in descending order, RowIDIntersectionIterator needs to reverse the iteration direction during READ execution.
Description: INDEX_MERGE_INTERSECTION causes wrong query results, when PK is in descending order. Due to the default PK order being in ascending order, the rowid is also sorted in ascending order of same prefix of key in the secondary index. While PK is sorted in descending order, when comparing rowids of the RowIDIntersectionIterator, there may be directional errors during recursive comparison. For example, the intersect operator is responsible for merging RowIterator child#1 and RowIterator child#2. When the rowid of child#1 is 6 and the rowid of child#2 is 5. If the PK is arranged in ascending order: it is necessary to continue iterating on child#2 to find a rowid greater than 5 to match child#1's rowid=6. If the PK is arranged in descending order: it is necessary to continue iterating on child#1 to find a rowid less than 6 to match child#2's rowid=5. How to repeat: TEST_CASE: CREATE TABLE t1 ( pk INT NOT NULL AUTO_INCREMENT, a INT NOT NULL, b INT NOT NULL, c INT NOT NULL, d INT NOT NULL, PRIMARY KEY pk (pk desc), KEY idx_a (a), KEY idx_b (b), KEY idx_c (c), KEY idx_d (d) ); --disable_query_log # Inserting a lot of rows in order to enable index_merge intersect INSERT INTO t1(a,b,c,d) VALUES ( RAND()*5, RAND()*5, RAND()*5, RAND()*5 ); let $cnt=4; while ($cnt) { INSERT INTO t1(a,b,c,d) SELECT 6,6,6,6 FROM t1; dec $cnt; } INSERT INTO t1(a,b,c,d) SELECT 6, RAND()*5, RAND()*5, RAND()*5 FROM t1 LIMIT 3; INSERT INTO t1(a,b,c,d) SELECT RAND()*5, 6, RAND()*5, RAND()*5 FROM t1 LIMIT 3; INSERT INTO t1(a,b,c,d) SELECT RAND()*5, RAND()*5, 6, RAND()*5 FROM t1 LIMIT 3; INSERT INTO t1(a,b,c,d) SELECT RAND()*5, RAND()*5, RAND()*5, 6 FROM t1 LIMIT 3; let $cnt=7; while ($cnt) { INSERT INTO t1(a,b,c,d) SELECT RAND()*5, RAND()*5, RAND()*5, RAND()*5 FROM t1; dec $cnt; } --enable_query_log ANALYZE TABLE t1; EXPLAIN SELECT COUNT(*), SUM(a) FROM t1 WHERE b=6 AND c=6; # RESULT ERROR SELECT COUNT(*), SUM(a) FROM t1 WHERE b=6 AND c=6; DROP TABLE t1; Suggested fix: Identify the sorting order of the PK. When creating the RowIDIntersectionIterator, we identify the PK sorting order. If sorting in descending order, RowIDIntersectionIterator needs to reverse the iteration direction during READ execution.