When an indexed INT column is equated to a VARCHAR column and the optimizer chooses ref access on the integer index, the index lookup key is built by parsing the string with integer semantics, while the comparison itself is defined to use decimal semantics. For a string such as '1e1', integer parsing stops at the e and yields 1, whereas the value of the comparison is 10. The index is probed at key 1, the row n = 10 is never visited, and the join silently loses the row.

The MariaDB documentation ("Type Conversion", Rules for Conversion on Comparison) states:

If one argument is string and the other argument is integer, they are compared as decimals.

MariaDB's own scalar evaluation agrees: SELECT 10 = '1e1' returns 1, and so does SELECT s1.n = s2.v over the same two rows. It is only the ref access path that disagrees.

Note that the same documentation page already recognises this hazard for the mirror-image case:

Note that if a string column is being compared with a numeric value, MariaDB will not use the index on the column, as there are numerous alternatives that may evaluate as equal

We confirmed that guard is in place: with the index on the string column instead (VARCHAR indexed, compared to an INT column), MariaDB declines ref (EXPLAIN shows Range checked for each record) and returns the correct row. The guard is simply missing in the direction where the integer column carries the index.

Minimal Reproduction

DROP DATABASE IF EXISTS bugrep_mariadb;

CREATE DATABASE bugrep_mariadb;

USE bugrep_mariadb;

CREATE TABLE s1 (n INT, KEY k_n (n));

CREATE TABLE s2 (v VARCHAR(20));

INSERT INTO s1 VALUES (10);

INSERT INTO s2 VALUES ('1e1');

-- The engine's own scalar truth: 10 = '1e1' is TRUE

SELECT 10 = '1e1' AS eq_literal, s1.n = s2.v AS eq_columns,

       CAST('1e1' AS SIGNED) AS as_signed, '1e1' + 0 AS as_double FROM s1, s2;

-- A. INNER JOIN, index usable        -> expected 1 row

SELECT 'A inner join' AS form, s2.v, s1.n FROM s2 JOIN s1 ON s1.n = s2.v;

-- B. same join, index disabled       -> expected 1 row

SELECT 'B ignore index' AS form, s2.v, s1.n FROM s2 JOIN s1 IGNORE INDEX (k_n) ON s1.n = s2.v;

-- C. same predicate as EXISTS        -> expected 1 row

SELECT 'C exists' AS form, s2.v FROM s2 WHERE EXISTS (SELECT 1 FROM s1 WHERE s1.n = s2.v);

-- D. same predicate as scalar subquery -> expected 10

SELECT 'D scalar' AS form, s2.v, (SELECT n FROM s1 WHERE s1.n = s2.v) AS scalar_n FROM s2;

-- E. LEFT JOIN                       -> expected ('1e1', 10), not a NULL-extended row

SELECT 'E left join' AS form, s2.v, s1.n FROM s2 LEFT JOIN s1 ON s1.n = s2.v;

-- F. constant on the right-hand side -> expected 10

SELECT 'F constant' AS form, n FROM s1 WHERE n = '1e1';

EXPLAIN SELECT s2.v, s1.n FROM s2 JOIN s1 ON s1.n = s2.v;

Expected Result

10 = '1e1' is TRUE, so forms A, B, C, D, E and F must all report the single matching pair: A/B/E return ('1e1', 10), C returns ('1e1'), D returns 10, F returns 10.

Actual Result

Measured on MariaDB 12.3.2:

+------------+------------+-----------+-----------+

| eq_literal | eq_columns | as_signed | as_double |

+------------+------------+-----------+-----------+

|          1 |          1 |         1 |        10 |

+------------+------------+-----------+-----------+

      TRUE         TRUE     <- key used     <- comparison value

A. inner join, index usable   -> Empty set (0 rows)      *** WRONG, expected 1 row

+----------------+------+------+

| form           | v    | n    |

+----------------+------+------+

| B ignore index | 1e1  |   10 |     correct

+----------------+------+------+

+----------+------+

| form     | v    |

+----------+------+

| C exists | 1e1  |             correct — directly contradicts A

+----------+------+

+----------+------+----------+

| form     | v    | scalar_n |

+----------+------+----------+

| D scalar | 1e1  |     NULL |    *** WRONG, expected 10

+----------+------+----------+

+-------------+------+------+

| form        | v    | n    |

+-------------+------+------+

| E left join | 1e1  | NULL |    *** WRONG: a fabricated NULL-extended row

+-------------+------+------+

+------------+------+

| form       | n    |

+------------+------+

| F constant |   10 |            correct

+------------+------+

Form A returning zero rows while form C returns one row is an internal contradiction: the two statements express the same predicate over the same two rows, and no reference engine is needed to see that at most one of them can be right.

RIGHT JOIN behaves the same way as LEFT JOIN (('1e1', NULL)).

Affected Value Domain

The rows that are lost are exactly those whose leading-integer parse differs from their numeric value. With s1 holding the single value 10:

CREATE TABLE s4 (v VARCHAR(20));

INSERT INTO s4 VALUES ('10'),(' 10'),(' 10 '),('+10'),('10.0'),('10.4'),('10abc'),

                      ('1e1'),('1E1'),('0.1e2'),('10e0'),('1e+1'),('100e-1');

SELECT GROUP_CONCAT(CONCAT('"',v,'"') ORDER BY v SEPARATOR ' ') AS matched_without_index

  FROM s4 JOIN s1 IGNORE INDEX (k_n) ON s1.n = s4.v;

SELECT GROUP_CONCAT(CONCAT('"',v,'"') ORDER BY v SEPARATOR ' ') AS matched_with_ref_access

  FROM s4 STRAIGHT_JOIN s1 FORCE INDEX (k_n) ON s1.n = s4.v;

matched_without_index   : " 10 " " 10" "+10" "0.1e2" "10" "10.0" "100e-1" "10abc" "10e0" "1e+1" "1E1" "1e1"   (12 rows)

matched_with_ref_access : " 10 " " 10" "+10" "10" "10.0" "10abc" "10e0"                                        (7 rows)

Lost under ref access: '0.1e2', '100e-1', '1e+1', '1E1', '1e1' — i.e. scientific-notation strings. Leading/trailing whitespace (' 10 '), an explicit sign ('+10'), a fractional zero ('10.0') and a trailing garbage suffix ('10abc') are not affected, because their leading-integer parse happens to equal their numeric value. '10.4' is correctly excluded by both paths.

We did not observe spurious extra rows. With s1 holding (0),(1),(10) and s2 holding '1e1', the lookup key 1 does fetch the row n = 1, but the residual Using where condition re-evaluates n = v with decimal semantics and rejects it, so the failure mode is missing rows (and, under outer joins, fabricated NULL-extended rows) rather than extra rows.

Cross-Engine Comparison

Same DDL, same data, same statements, measured on the same machine:

MySQL 9.7.1 executes essentially the same plan shape but does not lose the row; its EXPLAIN FORMAT=TREE shows the index lookup followed by an explicit Filter: (cast(s1.n as double) = cast(s2.v as double)) and the row survives.

DuckDB 1.5.5 also allows INTEGER = VARCHAR implicitly, evaluates 10 = '1e1' as true, and returns ('1e1', 10) for the inner join, the left join, and for the inner join after CREATE INDEX k_n ON s1 — so it is not affected. PostgreSQL 18.4 has no comparable form: it rejects integer = text at parse time (operator does not exist: integer = text) unless an explicit cast is written.