[MDEV-27340] NULL gets lost (becomes empty string), SELECT hangs with DESC index on MyISAM/Aria table Created: 2021-12-21  Updated: 2022-01-26  Resolved: 2022-01-26

Status: Closed
Project: MariaDB Server
Component/s: Server
Affects Version/s: N/A
Fix Version/s: 10.8.1

Type: Bug Priority: Critical
Reporter: Elena Stepanova Assignee: Sergei Golubchik
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
Problem/Incident
is caused by MDEV-13756 Implement descending index: KEY (a DE... Closed
Relates
relates to MDEV-27347 MyISAM/Aria table is marked as crashe... Closed

 Description   

SET max_statement_time= 2; # Just so that if it hangs, then not forever
 
DROP TABLE IF EXISTS t3;
CREATE TABLE t (c CHAR(8), KEY(c DESC)) ENGINE=MyISAM CHARACTER SET utf8mb4;
INSERT INTO t VALUES (''),('foo'),(NULL),(''),('bar');
SELECT DISTINCT c FROM t;
SELECT c FROM t;
 
# Cleanup
DROP TABLE t;

The first query returns three values, without NULL:

preview-10.8-MDEV-13756-desc-indexes 47c18283

SELECT DISTINCT c FROM t;
c
 
bar
foo

If count is added, it claims there are 3 empty strings instead of 2.
The second query, plain SELECT without DISTINCT, hangs (subject to max_statement_time).

Reproducible with MyISAM and Aria. Not reproducible with InnoDB.
Reproducible with big5, utf8mb4, utf8mb3. Not reproducible with latin1. I didn't try other charsets.
Not reproducible without DESC index.



 Comments   
Comment by Sergei Golubchik [ 2021-12-25 ]

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

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