MySQL Bugs: #117750: FORCE INDEX does not skip records_in

Bug #117750	FORCE INDEX does not skip records_in_range() for 'x IS NOT NULL'
Submitted:	19 Mar 11:46	Modified:	20 Mar 5:44
Reporter:	Kaiwang CHen (OCA)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	8.0, 8.4, 9.2	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	Contribution

Description:
When building min/max tree, SEL_ARG for 'x IS NOT NULL' is
initialized with min/max_value as is_null_string{1, 0} and max_flag
as NO_MAX_RANGE. Then SEL_ARG::store_max_value() becomes a no-op.

As a result, SEL_ARG::is_null_interval(), which is supposed to
return true iff it's a single-point null interval, always evaluates
to true, because it does not check presence of the upper end.

Obviously it is wrong. One of the consequences is leading to
records_in_range() invocations which are meant to be skipped by
"WL#6526: FORCE INDEX to avoid index dives when possible".

How to repeat:
CREATE TABLE t1 (c1 INT, KEY (c1));
INSERT INTO t1 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (NULL), (NULL);
ANALYZE TABLE t1;

set debug='+d,crash_records_in_range';

EXPLAIN SELECT * FROM t1 FORCE INDEX(c1) WHERE c1 IS NOT NULL;

set debug='-d,crash_records_in_range';

Suggested fix:
Check presence of the upper end in SEL_ARG::is_null_interval().

Check presence of the upper end in SEL_ARG::is_null_interval(). (*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: skip_records_in_range_crash_is_not_null.patch (application/octet-stream, text), 2.43 KiB.

Hello Kaiwang,

Thank you for the report and contribution.

regards,
Umesh

BTW, the essentially same query does not call records_in_range().

EXPLAIN SELECT * FROM t1 FORCE INDEX(c1) WHERE c1 > 0;

They create same (NULL, +Inf) range. The only difference is the NULL_RANGE flag:

// NEAR_MIN | NO_MAX_RANGE | NULL_RANGE | SKIP_RECORDS_IN_RANGE = 582
EXPLAIN SELECT * FROM t1 FORCE INDEX(c1) WHERE c1 IS NOT NULL;

// NEAR_MIN | NO_MAX_RANGE | SKIP_RECORDS_IN_RANGE = 518
EXPLAIN SELECT * FROM t1 FORCE INDEX(c1) WHERE c1 > 0;

BTW, an alternative fix is to test NO_MAX_RANGE before invoking is_null_interval() here, because any other usage site already guarantees !(max_flag & NO_MAX_RANGE). However, the comment of is_null_interval() suggests the way in the contributed patch.

  if (key_tree->is_null_interval()) push_position->min_key_flag |= NULL_RANGE;