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 ] |