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
-
Activity
| Field | Original Value | New Value |
|---|---|---|
| Link |
This issue is caused by |
| Description |
{code:sql}
--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 EXPLAIN FORMAT=JSON SELECT * FROM t WHERE pk > 10 OR a > 0; SELECT * FROM t WHERE pk > 10 OR a > 0; # Cleanup DROP TABLE t; {code} The query returns an empty result set (the expected result is 2 rows): {code:sql|title=preview-10.8- SELECT * FROM t WHERE pk > 10 OR a > 0; pk a b {code} 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}}. {code:sql|title=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" } } ] } } {code} |
{code:sql}
--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; {code} The query returns an empty result set (the expected result is 2 rows): {code:sql|title=preview-10.8- SELECT * FROM t WHERE pk > 10 OR a > 0; pk a b {code} 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}}. {code:sql|title=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" } } ] } } {code} |
| Status | Open [ 1 ] | In Progress [ 3 ] |
| Fix Version/s | 10.8.1 [ 26815 ] | |
| Fix Version/s | 10.8 [ 26121 ] | |
| Resolution | Fixed [ 1 ] | |
| Status | In Progress [ 3 ] | Closed [ 6 ] |
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.