[MDEV-27623] Wrong result with DESC key on InnoDB table Created: 2022-01-26  Updated: 2022-01-26  Resolved: 2022-01-26

Status: Closed
Project: MariaDB Server
Component/s: Optimizer, Storage Engine - InnoDB
Affects Version/s: N/A
Fix Version/s: N/A

Type: Bug Priority: Blocker
Reporter: Elena Stepanova Assignee: Marko Mäkelä
Resolution: Done Votes: 0
Labels: None

Issue Links:
Problem/Incident
is caused by MDEV-13756 Implement descending index: KEY (a DE... Closed

 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

 Comments   
Comment by Marko Mäkelä [ 2022-01-26 ]

I wonder if this might have been caused by the MDEV-27582 fix. 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 current MDEV-27582 fix

diff --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-27582 in a different way, by changing the way the FULLTEXT INDEX code is reading the FTS_DOC_ID index inside InnoDB.

Comment by Marko Mäkelä [ 2022-01-26 ]

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.

Comment by Marko Mäkelä [ 2022-01-26 ]

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.

Generated at Thu Feb 08 09:54:20 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.