Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-12486

Incorrect results with prefix_index_cluster_optimization enabled

    XMLWordPrintable

Details

    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

          Activity

            People

              marko Marko Mäkelä
              Dead2 Hans Kristian Rosbach
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

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