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

Details

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

Description

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

Attachments

Issue Links

is caused by

MDEV-6929 Port Facebook Prefix Index Queries Optimization

Closed

relates to

MDEV-23600 SIGFPE in row_search_with_covering_prefix

Closed

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

Closed

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

Closed

Activity

Ascending order - Click to sort in descending order

Elena Stepanova added a comment - 2017-04-11 16:16

Thanks for the report.

MTR-like test case
--source include/have_innodb.inc

DROP TABLE IF EXISTS wp_blogs;
CREATE TABLE wp_blogs (
blog_id bigint(20) NOT NULL auto_increment,
site_id bigint(20) NOT NULL default '0',
domain varchar(200) NOT NULL default '',
path varchar(100) NOT NULL default '',
registered datetime NOT NULL default '0000-00-00 00:00:00',
last_updated datetime NOT NULL default '0000-00-00 00:00:00',
public tinyint(2) NOT NULL default '1',
archived tinyint(2) NOT NULL default '0',
mature tinyint(2) NOT NULL default '0',
spam tinyint(2) NOT NULL default '0',
deleted tinyint(2) NOT NULL default '0',
lang_id int(11) NOT NULL default '0',
PRIMARY KEY (blog_id),
KEY domain (domain(50),path(5)),
KEY lang_id (lang_id)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;



insert into wp_blogs (domain, path) values ('domain.no', '/fondsinvesteringer/');
insert into wp_blogs (domain, path) values ('domain.no', '/');
insert into wp_blogs (domain, path) values ('foo', 'bar');
insert into wp_blogs (domain, path) values ('bar', 'foo');
insert into wp_blogs (domain, path) values ('foo', 'foo');
insert into wp_blogs (domain, path) values ('bar', 'bar');
insert into wp_blogs (domain, path) values ('foo', 'foobar');
insert into wp_blogs (domain, path) values ('bar', 'foobar');
insert into wp_blogs (domain, path) values ('foobar', 'foobar');


set global innodb_prefix_index_cluster_optimization=off;
SELECT blog_id FROM wp_blogs WHERE domain IN ('domain.no') AND path IN ( '/fondsinvesteringer/', '/' );

set global innodb_prefix_index_cluster_optimization=on;
SELECT blog_id FROM wp_blogs WHERE domain IN ('domain.no') AND path IN ( '/fondsinvesteringer/', '/' );

DROP TABLE wp_blogs;
SET GLOBAL innodb_prefix_index_cluster_optimization=DEFAULT;

Actual results
set global innodb_prefix_index_cluster_optimization=off;
SELECT blog_id FROM wp_blogs WHERE domain IN ('domain.no') AND path IN ( '/fondsinvesteringer/', '/' );
blog_id
2
1
set global innodb_prefix_index_cluster_optimization=on;
SELECT blog_id FROM wp_blogs WHERE domain IN ('domain.no') AND path IN ( '/fondsinvesteringer/', '/' );
blog_id
2

Elena Stepanova added a comment - 2017-04-11 16:16 Thanks for the report. MTR-like test case --source include/have_innodb.inc DROP TABLE IF EXISTS wp_blogs; CREATE TABLE wp_blogs ( blog_id bigint (20) NOT NULL auto_increment, site_id bigint (20) NOT NULL default '0' , domain varchar (200) NOT NULL default '' , path varchar (100) NOT NULL default '' , registered datetime NOT NULL default '0000-00-00 00:00:00' , last_updated datetime NOT NULL default '0000-00-00 00:00:00' , public tinyint(2) NOT NULL default '1' , archived tinyint(2) NOT NULL default '0' , mature tinyint(2) NOT NULL default '0' , spam tinyint(2) NOT NULL default '0' , deleted tinyint(2) NOT NULL default '0' , lang_id int (11) NOT NULL default '0' , PRIMARY KEY (blog_id), KEY domain (domain(50),path(5)), KEY lang_id (lang_id) ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci; insert into wp_blogs (domain, path) values ( 'domain.no' , '/fondsinvesteringer/' ); insert into wp_blogs (domain, path) values ( 'domain.no' , '/' ); insert into wp_blogs (domain, path) values ( 'foo' , 'bar' ); insert into wp_blogs (domain, path) values ( 'bar' , 'foo' ); insert into wp_blogs (domain, path) values ( 'foo' , 'foo' ); insert into wp_blogs (domain, path) values ( 'bar' , 'bar' ); insert into wp_blogs (domain, path) values ( 'foo' , 'foobar' ); insert into wp_blogs (domain, path) values ( 'bar' , 'foobar' ); insert into wp_blogs (domain, path) values ( 'foobar' , 'foobar' ); set global innodb_prefix_index_cluster_optimization= off ; SELECT blog_id FROM wp_blogs WHERE domain IN ( 'domain.no' ) AND path IN ( '/fondsinvesteringer/' , '/' ); set global innodb_prefix_index_cluster_optimization= on ; SELECT blog_id FROM wp_blogs WHERE domain IN ( 'domain.no' ) AND path IN ( '/fondsinvesteringer/' , '/' ); DROP TABLE wp_blogs; SET GLOBAL innodb_prefix_index_cluster_optimization= DEFAULT ; Actual results set global innodb_prefix_index_cluster_optimization=off; SELECT blog_id FROM wp_blogs WHERE domain IN ('domain.no') AND path IN ( '/fondsinvesteringer/', '/' ); blog_id 2 1 set global innodb_prefix_index_cluster_optimization=on; SELECT blog_id FROM wp_blogs WHERE domain IN ('domain.no') AND path IN ( '/fondsinvesteringer/', '/' ); blog_id 2

Marko Mäkelä added a comment - 2020-08-27 08:01

In ~~MDEV-23600~~, I identified and fixed some problems with this option.

Marko Mäkelä added a comment - 2020-08-27 08:01 In MDEV-23600 , I identified and fixed some problems with this option.

People

Assignee:: Marko Mäkelä

Reporter:: Hans Kristian Rosbach

Votes:: 0 Vote for this issue

Watchers:: 3 Start watching this issue

Dates

Created:: 2017-04-11 14:55

Updated:: 2021-04-27 12:31

Resolved:: 2020-09-01 13:27

Git Integration

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

MariaDB Server