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

Incorrect results with prefix_index_cluster_optimization enabled

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Confirmed (View Workflow)
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: 10.1.22, 10.1, 10.2
    • Fix Version/s: 10.1, 10.2
    • 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

            Activity

              People

              Assignee:
              kevg Eugene Kosov
              Reporter:
              Dead2 Hans Kristian Rosbach
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

                Dates

                Created:
                Updated: