Bug #121758 Skip scan overestimates rows for CURRENT_DATE() range predicates

Submitted: 13 Nov 7:27 Modified: 17 Nov 4:47
Reporter: Xingyu Yang (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:8.4.13 OS:Any
Assigned to: CPU Architecture:Any

[13 Nov 7:27] Xingyu Yang
Description:
With the classic optimizer, a range predicate on a non-leading index column
can receive a different skip scan selectivity estimate depending on whether
its right operand is CURRENT_DATE() or an equivalent date literal. The range
can be constructed in both cases, but CURRENT_DATE() is ignored by the
filtering estimate. This inflates the skip scan candidate's row count and
cost, and can cause a covering full index scan to be chosen instead.

This is a plan-selection/performance issue. Both forms return the same,
correct result in the attached reproduction. There is no claim that every
query using CURRENT_DATE() should choose skip scan.

The reproduction uses a nullable DATETIME(3), an index on (type, issue_time),
two prefix groups, 1,008 rows, no histogram, and a fixed session timestamp.
It is self-contained and contains no customer data or vendor-specific SQL.

How to repeat:
Run the following statements in order in a fresh connection and an empty
test database. They enable the classic optimizer and fix the session date
at 2026-10-21, so the comparison does not depend on the day it is run.

The table contains 1,008 rows in two index-prefix groups. Both COUNT queries
must return 52. Compare their EXPLAIN Extra values and the skip scan candidate
rows and costs in the optimizer traces.

-- 1. Configure the session.
SELECT VERSION(), @@version_comment, @@version_compile_os, @@version_compile_machine;
SET time_zone='+00:00';
SET timestamp=UNIX_TIMESTAMP('2026-10-21 23:00:00');
SET optimizer_switch='skip_scan=on,hypergraph_optimizer=off';

-- 2. Create the table and deterministic test data.
CREATE TABLE skip_date_digits (n INT NOT NULL);
INSERT INTO skip_date_digits VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
CREATE TABLE skip_date (
  type INT NOT NULL,
  issue_time DATETIME(3),
  KEY type_time (type, issue_time)
) ENGINE=InnoDB;
INSERT INTO skip_date
SELECT MOD(a.n, 2),
       TIMESTAMP('2026-10-23 00:00:00') -
       INTERVAL (a.n + 10 * b.n + 100 * c.n + 1) HOUR
FROM skip_date_digits a, skip_date_digits b, skip_date_digits c;
INSERT INTO skip_date VALUES
  (0, NULL), (1, NULL),
  (0, '2026-10-20 23:59:59.999'), (1, '2026-10-20 23:59:59.999'),
  (0, '2026-10-21 00:00:00.000'), (1, '2026-10-21 00:00:00.000'),
  (0, '2026-10-21 00:00:00.001'), (1, '2026-10-21 00:00:00.001');
ANALYZE TABLE skip_date;
DROP TABLE skip_date_digits;

-- 3. Compare plans and optimizer traces for equivalent bounds.
SET optimizer_trace_max_mem_size = 4194304;
SET optimizer_trace = 'enabled=on';
EXPLAIN FORMAT=TRADITIONAL SELECT COUNT(*) FROM skip_date
WHERE issue_time >= '2026-10-21';
SELECT TRACE FROM information_schema.OPTIMIZER_TRACE;
EXPLAIN FORMAT=TRADITIONAL SELECT COUNT(*) FROM skip_date
WHERE issue_time >= CURRENT_DATE;
SELECT TRACE FROM information_schema.OPTIMIZER_TRACE;
SET optimizer_trace = 'enabled=off';

-- 4. Inspect actual execution and verify that both counts are 52.
EXPLAIN ANALYZE SELECT COUNT(*) FROM skip_date
WHERE issue_time >= '2026-10-21';
EXPLAIN ANALYZE SELECT COUNT(*) FROM skip_date
WHERE issue_time >= CURRENT_DATE;
SELECT COUNT(*) AS literal_rows FROM skip_date
WHERE issue_time >= '2026-10-21';
SELECT COUNT(*) AS current_date_rows FROM skip_date
WHERE issue_time >= CURRENT_DATE;

-- 5. Clean up the test table and reset the session timestamp.
DROP TABLE skip_date;
SET timestamp = DEFAULT;

Actual behavior before the fix
------------------------------
The added main.skip_scan regression was run against the existing server binary
before applying this patch. It observed:

  literal_skip_scan  current_date_skip_scan  same_skip_scan_estimate
  1                  0                       0

  curdate_skip_scan  interval_skip_scan  now_skip_scan
  0                  0                   0

These fields are computed from EXPLAIN Extra (presence of "Using index for
skip scan") and the rows field of potential_skip_scan_indexes in the
optimizer trace. They are not elapsed-time measurements. The row counts
for the two COUNT queries were both 52.

Expected behavior
-----------------
For this no-histogram reproduction, equivalent statement-constant and literal
bounds should contribute consistently to skip scan selectivity. Both queries
should select skip scan and have equal candidate row estimates:

  literal_skip_scan  current_date_skip_scan  same_skip_scan_estimate
  1                  1                       1

The expected estimates are still heuristic; they need not equal the actual
number of matching rows. Access-path selection must remain cost based.

Root cause
----------
Item_func_curdate::get_initial_pseudo_tables() returns INNER_TABLE_BIT because
the function is constant within an execution but may change between
executions. In sql/range_optimizer/index_skip_scan_plan.cc, cost_skip_scan()
currently passes read_tables=0 to where_cond->get_filtering_effect().

In the classic optimizer, Item_func::contributes_to_filter() consequently
retains INNER_TABLE_BIT when checking whether the predicate only refers to
the table being filtered. The dependency check fails, and
Item_func_inequality::get_filtering_effect() returns COND_FILTER_ALLPASS (1.0).
The skip scan candidate is then costed using the unfiltered row count.

For the literal form, the dependency check succeeds and the normal inequality
selectivity is used (approximately 1/3 in this reproduction). The problem is
in the estimate, not in evaluating the date or constructing the range.

Suggested fix:
In cost_skip_scan(), initialize the local used_tables mask to INNER_TABLE_BIT
instead of zero before passing it as read_tables to get_filtering_effect().
This is consistent with the treatment of execution-time constants in range
analysis. The attached three-file patch contains this change and regression
coverage in the existing main.skip_scan test/result files.

The change does not redefine INNER_TABLE_BIT, change CURRENT_DATE()'s global
dependency metadata, or freeze a date across prepared-statement executions.
The tests also check disabled skip scan paths, nondeterministic bounds, NULL,
millisecond boundaries, and date/time-zone changes during prepared reuse.
[13 Nov 7:29] Xingyu Yang
Unified git diff for the cost fix and regression test/result (3 files).

(*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: mysql-8.4.13-current-date-skip-scan.patch (application/octet-stream, text), 10.12 KiB.

[17 Nov 4:47] Chaithra Marsur Gopala Reddy
Hi Xingyu Yang,

Thank you for the test case and contribution. Report is verified as described. Please consider contributing through github. Here are the details - https://dev.mysql.com/community/contributing/

Thanks,
Chaithra

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.041 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.