[MDEV-26938] support descending indexes internally in InnoDB Created: 2021-10-29  Updated: 2023-08-07  Resolved: 2022-01-28

Status: Closed
Project: MariaDB Server
Component/s: Storage Engine - InnoDB
Fix Version/s: 10.8.1

Type: Task Priority: Critical
Reporter: Sergei Golubchik Assignee: Marko Mäkelä
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
PartOf
is part of MDEV-13756 Implement descending index: KEY (a DE... Closed
Problem/Incident
causes MDEV-29992 Index corruption with DESC part in un... Open
causes MDEV-30183 Assertion `!memcmp(rec_trx_id, old_pk... Closed

 Description   

see MDEV-13756

Descending attribute will only for for BTREE indexes, for everything else (fulltext, spacial, hash) it'll be ignored as before

 Comments   
Comment by Marko Mäkelä [ 2021-11-03 ]

This cannot be tested before the HA_REVERSE_SORT flag is written to .frm files and will actually be preserved on CREATE TABLE and ALTER TABLE. See the test innodb_fts.create.

Comment by Marko Mäkelä [ 2021-11-04 ]

The interface between TABLE_SHARE and InnoDB is highlighted in this patch, which might be useful for debugging. I corrected the implementation of dtuple_coll_eq(), so that there no longer is any EXPLAIN result difference in the test main.update_use_source.

Comment by Sergei Golubchik [ 2021-11-24 ]

parser and frm support is pushed into bb-10.8-MDEV-26938

main.order_by and main.order_by_innodb show that ORDER BY works.
innodb_fts.create fails — CREATE TABLE is correctly rejected, but ALTER isn't. Likely something inside InnoDB doesn't check for HA_REVERSE_SORT on ALTER.

Comment by Marko Mäkelä [ 2021-11-25 ]

I suggest that we only set the HA_REVERSE_SORT flag for B-tree indexes and never for FULLTEXT, SPATIAL, or HASH indexes that are not ordered.

All issues that I was able to identify were in the SQL layer. I pushed some suggested fixes, and disabled failing test with FIXME comments that identify the root cause. One of them is that in ALTER TABLE, the Key_part_spec::asc is not being set consistently or correctly.

Comment by Matthias Leich [ 2021-11-26 ] 

Some raw ideas for RQG based testing:
 
It looks as if the RQG grammar conf/mariadb/concurrency.yy is some good candidate for RQG testing.
 
The DDL fiddles with ASC and DESC keys. Probably even in case of multi column keys.
 
1. Plain crash testing with concurrent sessions running some DDL/DML mix based on
 
     --grammar=conf/mariadb/concurrency.yy --gendata=conf/mariadb/concurrency.zz --gendata_sql=conf/mariadb/concurrency.sql
 
2. Run "pseudo replication" (== any SQL gets sent to two servers) based on conf/mariadb/concurrency.yy (*)
 
    - only one session runs the DDL/DML mix and the SQL should avoid statements which are unsafe in statement based replication
 
      because otherwise "pseudo replication" is known to break
 
    - probably costly modification in RQG code: When sending a DDL creating a key to the second server convert ASC to DESC and DESC to ASC 
      or
 
      probably less costly: First server is 10.8 with MDEV-26938 , second server is 10.8 without MDEV-26938
 
    - filter out any SQL containing LIMIT and do not execute that
 
    - at test end compare the content of the tables of both servers
 
       Variant: Compare the result sets of SELECTs executed on the first and the second server
 
    (*)   Alternative:  Develop some new grammar or a simplified derivate of conf/mariadb/concurrency.yy

Comment by Matthias Leich [ 2021-11-29 ] 

Results of ~ 15000 RQG tests using conf/mariadb/concurrency.yy and different setups (see 1. in the comment above):
 
In general low fraction of failing tests.
 
The failures observed occur on actual 10.6 too.
 
concurrency.yy has the advantage of using DESC indexes etc. at all.
 
But that does not guarantee to give some exceptional coverage of that feature.
 
The correctness of result sets could not be checked with that setup.
 
 
 
Test campaign with
 
- a derivate of the grammar table_stress_innodb.yy
 
  ASC or DESC added to columns being part of some KEY
 
- some test setup derived from table_stress.cc
 
pluto:/data/rqg/results/1638191819
 
There are several new error patterns. Mostly in connection with Mariabackup.
 
I am now running the same campaign against actual 10.8.

Comment by Sergei Golubchik [ 2021-12-11 ]

pushed into preview-10.8-MDEV-26938-desc-indexes

Comment by Elena Stepanova [ 2021-12-14 ]

The final preview branch in testing is preview-10.8-MDEV-13756-desc-indexes, so further testing will be done under MDEV-13756, while this one will remain "In testing" until MDEV-13756 with all the included parts is either accepted or rejected.

Comment by Elena Stepanova [ 2022-01-26 ]

The correctness part tested in the scope of MDEV-13756.

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