Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
N/A
-
None
Description
--source include/have_innodb.inc
|
|
|
CREATE OR REPLACE TABLE t (pk INT, a INT, b int, KEY(a), PRIMARY KEY(pk DESC)) ENGINE=InnoDB; |
INSERT INTO t VALUES (1,4,5),(2,9,6),(3,NULL,7),(4,NULL,8); |
|
|
ANALYZE TABLE t PERSISTENT FOR ALL; # Optional, fails either way |
|
|
SELECT * FROM t WHERE pk > 10 OR a > 0; |
|
|
# Cleanup
|
DROP TABLE t; |
The query returns an empty result set (the expected result is 2 rows):
|
preview-10.8-MDEV-13756-desc-indexes 49b38c82 |
SELECT * FROM t WHERE pk > 10 OR a > 0; |
pk a b
|
Not reproducible with the same test case with ASC primary key.
Not reproducible with the same test case with MyISAM.
Not reproducible with index_merge=off.
|
EXPLAIN |
EXPLAIN
|
{
|
"query_block": { |
"select_id": 1, |
"nested_loop": [ |
{
|
"table": { |
"table_name": "t", |
"access_type": "index_merge", |
"possible_keys": ["PRIMARY", "a"], |
"key_length": "5,4", |
"index_merge": { |
"sort_union": [ |
{
|
"range": { |
"key": "a", |
"used_key_parts": ["a"] |
}
|
},
|
{
|
"range": { |
"key": "PRIMARY", |
"used_key_parts": [] |
}
|
}
|
]
|
},
|
"rows": 3, |
"filtered": 100, |
"attached_condition": "t.pk > 10 or t.a > 0" |
}
|
}
|
]
|
}
|
}
|
Attachments
Issue Links
- is caused by
-
MDEV-13756 Implement descending index: KEY (a DESC, b ASC)
-
- Closed
-
Notes: This quick select uses a special case variant of index_merge where one of the merged index scans is a scan on clustered PK.
In that case, the execution gets here:
(gdb) wher 10#0 QUICK_RANGE_SELECT::cmp_next (this=0x7fff9809e690, range_arg=0x7fff98017ef8) at /home/psergey/dev-git2/10.8-rev-index/sql/opt_range.cc:13079#1 0x0000555555d95cf4 in QUICK_RANGE_SELECT::row_in_ranges (this=0x7fff9809e690) at /home/psergey/dev-git2/10.8-rev-index/sql/opt_range.cc:12889#2 0x0000555555d94252 in read_keys_and_merge_scans (thd=0x7fff98000d78, head=0x7fff9801fe78, quick_selects=..., pk_quick_select=0x7fff9809e690, read_record=0x7fff980235d0, intersection=false, filtered_scans=0x0, unique_ptr=0x7fff98023560) at /home/psergey/dev-git2/10.8-rev-index/sql/opt_range.cc:12289#3 0x0000555555d9443f in QUICK_INDEX_MERGE_SELECT::read_keys_and_merge (this=0x7fff98023520) at /home/psergey/dev-git2/10.8-rev-index/sql/opt_range.cc:12323#4 0x0000555555d7857b in QUICK_INDEX_SORT_SELECT::reset (this=0x7fff98023520) at /home/psergey/dev-git2/10.8-rev-index/sql/opt_range.cc:1400and QUICK_RANGE_SELECT::cmp_next() apparently ignores the fact that some key parts are reverse-ordered.