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.
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.