Use_count: Wrong count for key error in maria-5.3

[Philip Stoev]

Not repeatable in maria-5.2, mysql-5.1, mysql-5.5. See also mysql bug #58731. It is best that all such errors are assertions in debug mode.

In the error log:

110621 16:06:04 [Note] Use_count: Wrong count for key at 0x1cdd37f0, 0 should be 1
110621 16:06:04 [Note] Use_count: Wrong count for key at 0x1cdd37f0, 0 should be 1

test case:

CREATE TABLE t1 ( f1 int NOT NULL , f2 int, f3 int, KEY (f3,f2), PRIMARY KEY (f1)) ;
INSERT IGNORE INTO t1 VALUES (19,1,NULL),(20,5,'r');

SELECT *
FROM t1 AS alias1 STRAIGHT_JOIN t1 AS alias2 ON alias2.f2
WHERE
 ( alias1.f1 > 238 OR alias1.f2 NOT BETWEEN 238 AND 238 )
 AND alias1.f3 != 'y' AND alias1.f2 BETWEEN 238 AND 238 AND
( alias1.f3 = 'u' OR alias1.f1 = 245 );

explain:

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE alias1 range PRIMARY,f3 PRIMARY 4 NULL 1 Using where; Rowid-ordered scan
1 SIMPLE alias2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join)

bzr version-info:
revision-id: <email address hidden>
date: 2011-06-17 23:00:26 +0200
build-date: 2011-06-21 16:22:30 +0300
revno: 3051
branch-nick: maria-5.3

optimizer_switch:

index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=off,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=on,mrr_cost_based=off,mrr_sort_keys=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=on,table_elimination=on

[Timour Katchaounov (timour)]

The bug can be reproduced with all optimizer switches set to OFF.
Slightly simplified example:

CREATE TABLE t1 ( f1 int NOT NULL , f2 int, f3 int, KEY (f3,f2), PRIMARY KEY (f1)) ;
INSERT IGNORE INTO t1 VALUES (19,1,NULL),(20,5,'r');
CREATE TABLE t2 ( f1 int NOT NULL , f2 int, f3 int, KEY (f3,f2), PRIMARY KEY (f1)) ;
INSERT IGNORE INTO t2 VALUES (19,1,NULL),(20,5,'r');

SELECT * FROM t1, t2
WHERE (t1.f1 > 238 OR t1.f2 NOT BETWEEN 238 AND 238 ) AND
                t1.f3 != 'y' AND
                t1.f2 BETWEEN 238 AND 238 AND
                ( t1.f3 = 'u' OR t1.f1 = 245 );

[Timour Katchaounov (timour)]

Even simpler, join is not necessary:

CREATE TABLE t1 ( f1 int NOT NULL , f2 int, f3 int, KEY (f3,f2), PRIMARY KEY (f1)) ;
INSERT IGNORE INTO t1 VALUES (19,1,NULL),(20,5,'r');

SELECT * FROM t1 WHERE (t1.f1 > 238 OR t1.f2 NOT BETWEEN 238 AND 238 ) AND t1.f3 != 'y' AND t1.f2 BETWEEN 238 AND 238 AND ( t1.f3 = 'u' OR t1.f1 = 245 );

[Timour Katchaounov (timour)]

Assigned to Igor, because the call to SEL_ARG::test_use_count() that produces
the warning is made from and_range_trees() which has been last modified by
the following changeset:

revno: 2643.116.1
committer: Igor Babaev <email address hidden>
branch nick: maria-5.1-wl24
timestamp: Sun 2009-10-11 21:59:34 -0700
message:
  The main patch for WL#24:
  "index_merge: fair choice between index_merge union and range access"