[MDEV-12486] Incorrect results with prefix_index_cluster_optimization enabled - Jira

XML

Word

Printable

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Fixed
Affects Version/s: 10.1.22, 10.1(EOL), 10.2(EOL)
Fix Version/s: 10.1.48, 10.2.35, 10.3.26, 10.4.16, 10.5.7
Component/s: Optimizer, Storage Engine - InnoDB
Labels:
None
Environment:
RHEL7, custom built mariadb using GCC 6.3
Dual 6-core Xeon E5 w/128GB ram

The following query is supposed to return two rows, containing '1' and '3', but it only returns '1'.

 SELECT blog_id FROM wp_blogs WHERE domain IN ('domain.no') AND path IN ( '/fondsinvesteringer/', '/' );

Using extended explain however, it correctly tells me it is supposed to return 2 rows.

This is from a wordpress database. 'blog_id' is a bigint(20) primary key. 'domain' and 'path' are: varchar(200) utf8mb4_unicode_ci.

explain extended SELECT blog_id FROM wp_blogs WHERE domain IN ('domain.no') AND path IN ( '/fondsinvesteringer/', '/' );

id 	select_type 	table 	type 	possible_keys 	key 	key_len 	ref 	rows 	filtered 	Extra

1 	SIMPLE 	wp_blogs 	range 	domain 	domain 	224 	NULL	2 	100.00 	Using where

show columns from wp_blogs;

Field 	Type 	Null 	Key 	Default 	Extra

blog_id 	bigint(20) 	NO 	PRI 	NULL	auto_increment

site_id 	bigint(20) 	NO 		0

domain 	varchar(200) 	NO 	MUL

path 	varchar(100) 	NO

registered 	datetime 	NO 		0000-00-00 00:00:00

last_updated 	datetime 	NO 		0000-00-00 00:00:00

public 	tinyint(2) 	NO 		1

archived 	tinyint(2) 	NO 		0

mature 	tinyint(2) 	NO 		0

spam 	tinyint(2) 	NO 		0

deleted 	tinyint(2) 	NO 		0

lang_id 	int(11) 	NO 	MUL 	0

is caused by

MDEV-6929 Port Facebook Prefix Index Queries Optimization

relates to

MDEV-23600 SIGFPE in row_search_with_covering_prefix

MDEV-12255 innodb_prefix_index_cluster_optimization hits debug build assert on UTF-8 columns

MDEV-25440 Assertion `cmp_rec_rec(rec, old_rec, offsets, old_offsets, m_index) > 0' failed in PageBulk::insert

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.