MIN/MAX on indexed VARCHAR with numeric literal in WHERE can disagree with heap table (wrong result / wrong implicit comparison semantics).

Summary

MIN/MAX on indexed VARCHAR with numeric literal in WHERE can disagree with heap table (wrong result / wrong implicit comparison semantics).

Component / Version

• Component: Optimizer
• Version observed: MariaDB 12.3.1

Description

For predicates comparing a string column to a numeric constant, SQL comparison uses numeric conversion rules. When an index exists, MIN/MAX may be satisfied via opt_sum / “Select tables optimized away” using index endpoints. That path can effectively apply string (or index-key) ordering instead of the same numeric semantics as a full table scan, so results differ after ADD KEY.

Steps to reproduce

1. Create table and data, run without index, then add index and run again:

CREATE DATABASE IF NOT EXISTS mdev_min_repro;

USE mdev_min_repro;

DROP TABLE IF EXISTS t;

CREATE TABLE t(x VARCHAR(2));

INSERT INTO t VALUES ('1'), ('13');

SELECT 'heap' AS step, MAX(x) AS max_x FROM t WHERE x < 2;

ALTER TABLE t ADD KEY (x);

SELECT 'idx' AS step, MAX(x) AS max_x FROM t WHERE x < 2;

Optional: EXPLAIN SELECT MAX FROM t WHERE x < 2; after ALTER — expect constant/optimized plan when bug triggers.

Expected result

Both queries return the same max_x.

• Numeric semantics: only '1' satisfies x < 2 (value 13 does not). So MAX should be '1' in both steps.

Actual result

• First row (heap): max_x = '1' (correct).
• Second row (with index): max_x = '13' (incorrect vs heap and vs numeric WHERE semantics).