MySQL Bugs: #58731: Use_count: Wrong count for key at 0x7fffd423ede0, 0 should be 1

Bug #58731	Use_count: Wrong count for key at 0x7fffd423ede0, 0 should be 1
Submitted:	4 Dec 2010 15:24	Modified:	29 Jul 2012 22:52
Reporter:	Shane Bester (Platinum Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	5.5.9, 5.6.2m5	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
recently found in error log when using covering indexes for a query:
[Note] Use_count: Wrong count for key at ... , 0 should be 1

How to repeat:
only seen in mysql-5.1-security when using a valgrind build. see attachments.

drop table if exists `g32`;
create table `g32` (`a` int,`b` int,`c` int,key `idx` (`a`,`c`,`b`))
engine=myisam default charset=latin1;
insert into `g32` values (0,0,0),(0,0,0);
select 1 from `g32` force index(`idx`) where 
`a` >= 1  and `b` <= 1 and `a` =`c` and `c` > 1;

some details....

Attachment: bug58731_infos.txt (text/plain), 5.91 KiB.

Version: '5.5.9-valgrind-max-debug'  socket: 'sock'  port: 3306  Source distribution
101204 17:29:24 [Note] Use_count: Wrong count for key at 0x7f6660012090, 0 should be 1

Verified with current mysql-5.5-security on Mac OS X:

...
Version: '5.5.9-debug'  socket: '/tmp/mysql.sock'  port: 3306  Source distribution
101204 17:26:54 [Note] Use_count: Wrong count for key at 0x10a3f88, 0 should be 1
...

ignore index is a workaround. not sure whether this bug can indicate wrong results or not?  if so, in debug builds i request it be turned into an assertion instead of a printout!

This is a bug in the optimizer and can happen only with segmented keys ...

Could be a bug in the greedy module ...

110307 11:05:50 [Note] Use_count: Wrong count for key at 0x3b16b90, 1 should be 2
110307 11:05:50 [Note] Use_count: Wrong count for key at 0x3b16b90, 1 should be 2
110307 11:16:46 [Note] Use_count: Wrong count for key at 0x3ac5af0, 3 should be 4
110307 11:16:46 [Note] Use_count: Wrong count for key at 0x3ac5af0, 3 should be 4
110307 11:17:31 [Note] Use_count: Wrong count for key at 0x3b69170, 4 should be 6
110307 11:17:31 [Note] Use_count: Wrong count for key at 0x3b69170, 4 should be 6
110307 11:17:31 [Note] Use_count: Wrong count for key at 0x3b69170, 4 should be 6
110307 11:18:25 [Note] Use_count: Wrong count for key at 0x3b2c490, 3 should be 4
110307 11:18:25 [Note] Use_count: Wrong count for key at 0x3b2c490, 3 should be 4
110307 11:32:50 [Note] Use_count: Wrong count for key at 0x3d0a4f0, 3 should be 4
110307 11:32:50 [Note] Use_count: Wrong count for key at 0x3d0a4f0, 3 should be 4

Also seen in 5.6.2m5 (mysql-next-mr-opt-backporting) using Valgrind build. No other surrounding information. This could be serious if the outcome is wrong results. If so, I agree with Shane that we should add an actual assertion ASAP.

Running RQG as follows:

========= RQG 
perl ./runall.pl \
--seed=1774 \
--threads=1 \
--grammar=/randgen/conf/optimizer/range_access.yy \
--queries=1M \
--duration=3600 \
--basedir=/mysql-5.6.2-m5-linux-x86_64-valgrind \
--vardir=/tmp/some_mysql_var_dir \
--mysqld=--init-file=/mysql-5.6.2-m5-linux-x86_64-valgrind/init.sql \
--Reporter=Shutdown,Backtrace \
gendata=/randgen/conf/optimizer/range_access.zz \

========= init.sql
SET GLOBAL optimizer_switch = 'semijoin=off';
SET GLOBAL optimizer_join_cache_level = 8;

(on a Valgrind build) may reproduce the issue.

Noted in 5.6.6 changelog.

For queries with range predicates, the optimizer could miscalculate 
the number of key parts used, possibly leading to a server crash.