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

```sql
DROP DATABASE IF EXISTS bugrep_mariadb;
CREATE DATABASE bugrep_mariadb;
USE bugrep_mariadb;

CREATE TABLE s1 (n INT, KEY k_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:

```
----------------------------------------+

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

----------------------------------------+
TRUE TRUE <- key used <- comparison value

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

------------------------

------------------------

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

----------------------

---------------------

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

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`:

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