Wrong LEFT JOIN result (INT UNIQUE key vs DOUBLE literal ON predicate)

Summary

LEFT JOIN with ON condition comparing an INT column (backed by a UNIQUE index) to a DOUBLE literal such as 0.1 can incorrectly match the row where the integer is 0.

Description

Table t0 contains a single row with c0 = 0. The join predicate t0.c0 = 0.1 must evaluate to false (0 is not equal to 0.1). Therefore, for a LEFT JOIN from t2, the right-hand side columns from t0 should be NULL when no row satisfies the ON clause. When t0.c0 has a UNIQUE constraint (index lookup / ref-style path), MariaDB incorrectly returns the t0 row (c0 = 0). When t0 has no UNIQUE and no index, the result is correct.

Steps to reproduce

CREATE DATABASE IF NOT EXISTS logic_bug_focus;

USE logic_bug_focus;

DROP TABLE IF EXISTS t0, t2;

CREATE TABLE t0(c0 INT UNIQUE);

CREATE TABLE t2(c0 INT);

INSERT INTO t0(c0) VALUES (0);

INSERT INTO t2(c0) VALUES (1);

SELECT 'with_unique' AS mode, t2.c0 AS t2_c0, t0.c0 AS t0_c0

FROM t2 LEFT JOIN t0 ON t0.c0 = 0.1;

DROP TABLE t0;

CREATE TABLE t0(c0 INT);

INSERT INTO t0(c0) VALUES (0);

SELECT 'heap_no_idx' AS mode, t2.c0 AS t2_c0, t0.c0 AS t0_c0

FROM t2 LEFT JOIN t0 ON t0.c0 = 0.1;

SELECT 'where_filter' AS mode, c0 FROM t0 WHERE c0 = 0.1;

SELECT 'expr' AS mode, (0 = 0.1) AS cmp;

DROP TABLE t0, t2;

Actual result

• with_unique: one row (t2_c0=1, t0_c0=0) — incorrect.
• heap_no_idx: one row (t2_c0=1, t0_c0=NULL) — correct.
• where_filter: empty result set.
• expr cmp: 0 (false).

Expected result

• with_unique: same as heap_no_idx — exactly one row with t2_c0=1 and t0_c0=NULL.