Details
-
Bug
-
Status: Closed (View Workflow)
-
Blocker
-
Resolution: Done
-
N/A
-
None
Description
--source include/have_innodb.inc
|
|
|
CREATE TABLE t (id int, a tinyint, PRIMARY KEY (id DESC), KEY (a)) ENGINE=InnoDB; |
INSERT INTO t VALUES (1,1),(2,9),(3,4),(4,4),(5,1); |
|
|
SELECT * FROM t WHERE id > 4 AND a = 1; |
|
|
# Cleanup
|
DROP TABLE t; |
|
preview-10.8-MDEV-13756-desc-indexes f3f68d411 |
SELECT * FROM t WHERE id > 4 AND a = 1; |
id a
|
DROP TABLE t; |
The SELECT returns an empty result. It should return the row (5,1).
Not reproducible with MyISAM.
Not reproducible with an ascending key.
Not reproducible on 7efd3c55e or on the current top of the branch but with the reverted patch 8be39fbc002
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 |
This was indeed caused by the attempted MDEV-27582 fix. We will develop an alternative fix that will avoid the file format change that was done as part of the initial MDEV-27582 fix.
| Link |
This issue is caused by |
| Assignee | Sergei Golubchik [ serg ] | Marko Mäkelä [ marko ] |
| Link |
This issue is caused by |
I fixed this by removing all traces of MDEV-27582 from the branch. MDEV-27582 will have to be fixed in a different way later.
| issue.field.resolutiondate | 2022-01-26 09:34:04.0 | 2022-01-26 09:34:04.699 |
| Fix Version/s | N/A [ 14700 ] | |
| Fix Version/s | 10.8 [ 26121 ] | |
| Resolution | Done [ 10200 ] | |
| Status | Open [ 1 ] | Closed [ 6 ] |
I wonder if this might have been caused by the
MDEV-27582fix. I reasoned that we are free to use an internal ordering of KEY a(a ASC, id ASC) instead of KEY a(a ASC, id DESC) for the secondary index. If this query is executed as a covering index scan, then perhaps we should revert the currentMDEV-27582fixdiff --git a/storage/innobase/dict/dict0dict.cc b/storage/innobase/dict/dict0dict.cc--- a/storage/innobase/dict/dict0dict.cc+++ b/storage/innobase/dict/dict0dict.cc@@ -2649,8 +2649,7 @@ dict_index_build_internal_non_clust(if (!indexed[field->col->ind] || index->is_spatial()) {dict_index_add_col(new_index, table, field->col,- field->prefix_len,- field->descending);+ field->prefix_len);}}and fix
MDEV-27582in a different way, by changing the way the FULLTEXT INDEX code is reading the FTS_DOC_ID index inside InnoDB.