Index ref access misses rows when comparing VARCHAR scientific notation to INT.

A "Wrong Result" bug exists in MariaDB when joining a VARCHAR column to an INT column that has a B-tree index. When the VARCHAR column contains a scientific notation string like '9E1' (which evaluates to 90 in SQL), the index ref access fails to find the matching INT 90 row. Dropping the index restores correct results, confirming this is an index lookup bug.

Problem

When MariaDB uses a VARCHAR column value as a B-tree search key for an INT index via ref access, strings containing scientific notation (e.g., '9E1' = 90) are not properly converted during the index lookup. The B-tree key comparison treats the value as a raw string rather than performing the VARCHAR→INT implicit conversion that normal SQL evaluation does.

This causes the index lookup to miss matching rows, producing wrong query results. The bug is 100% deterministic.

Root Cause Analysis

The bug requires all of the following conditions:

1. A VARCHAR column on one side of the JOIN condition
2. An INT column with a B-tree index on the other side
3. The VARCHAR column containing a scientific notation value (e.g., '9E1') that equals an INT value in the indexed table
4. The optimizer choosing ref access on the INT index using the VARCHAR column as the lookup key

Normal SQL evaluation correctly converts '9E1' to 90 (SELECT '9E1' = 90 returns 1), but the B-tree index comparison does not.

Minimal Reproduction

DROP DATABASE IF EXISTS db9_min;

CREATE DATABASE db9_min;

USE db9_min;

CREATE TABLE t0 (c VARCHAR(64));

CREATE TABLE t1 (c INT, INDEX i (c));

INSERT INTO t0 VALUES ('0');

INSERT INTO t0 VALUES ('9E1');

INSERT INTO t1 VALUES (0);

INSERT INTO t1 VALUES (90);

-- Verify that normal SQL evaluation works correctly

SELECT '9E1' = 90 AS should_be_1;

-- BUG: JOIN with index on INT column misses c=90

-- Optimizer uses ref access: t0 ALL scan -> t1 ref lookup via index i using t0.c as key

EXPLAIN SELECT t1.c FROM t0 JOIN t1 ON t0.c = t1.c;

SELECT t1.c FROM t0 JOIN t1 ON t0.c = t1.c;

-- Workaround: without index, full table scan returns correct results

DROP INDEX i ON t1;

EXPLAIN SELECT t1.c FROM t0 JOIN t1 ON t0.c = t1.c;

SELECT t1.c FROM t0 JOIN t1 ON t0.c = t1.c;

Expected Result

Both SELECT queries should return the same two rows:

-- With index (ref access):

c

0

90

-- Without index (BNL join):

c

0

90

Actual Result

The indexed query returns only one row, missing c=90:

-- With index (ref access): WRONG

c

0

-- Without index (BNL join): CORRECT

c

0

90

The EXPLAIN output for the buggy query:

id  select_type  table  type  possible_keys  key  key_len  ref            rows  Extra

1   SIMPLE       t0     ALL   NULL           NULL NULL     NULL           2     Using where

1   SIMPLE       t1     ref   i              i    5        db9_min.t0.c   1     Using where; Using index