Description:
A query result divergence occurs when performing an inner join on a TEXT column and a DECIMAL column with implicit type truncation (e.g. '0a' compared with 0), depending on whether a UNIQUE index exists on the DECIMAL column.
Specifically:
String '0a' implicitly converts to numeric 0 during TEXT to DECIMAL comparison in MySQL.
On a table without a UNIQUE index (d_raw), performing JOIN ON d_raw.t1.c = d_raw.t0.c uses full scan/hash join, correctly truncates '0a' to 0, and returns 1 row.
On a table with a UNIQUE index (d), performing JOIN ON d.t1.c = d.t0.c uses index lookup on d.t0, fails to match '0a' with 0, and incorrectly returns Empty set, 1 warning.
How to repeat:
DROP DATABASE IF EXISTS test_idx;
DROP DATABASE IF EXISTS test_raw;
-- Case 1: Table with UNIQUE index
CREATE DATABASE test_idx;
USE test_idx;
CREATE TABLE t0 (c DECIMAL UNIQUE);
CREATE TABLE t1 (c TEXT);
INSERT INTO t0 VALUES (0);
INSERT INTO t1 VALUES ('0a');
-- Query on table with UNIQUE index
SELECT 1 FROM t1 JOIN t0 ON t1.c = t0.c;
-- Returns: Empty set (Incorrect index lookup behavior)
-- Case 2: Table without UNIQUE index
CREATE DATABASE test_raw;
USE test_raw;
CREATE TABLE t0 (c DECIMAL);
CREATE TABLE t1 (c TEXT);
INSERT INTO t0 VALUES (0);
INSERT INTO t1 VALUES ('0a');
-- Query on table without index
SELECT 1 FROM t1 JOIN t0 ON t1.c = t0.c;
-- Returns: 1 row (Correct implicit conversion behavior)
Description: A query result divergence occurs when performing an inner join on a TEXT column and a DECIMAL column with implicit type truncation (e.g. '0a' compared with 0), depending on whether a UNIQUE index exists on the DECIMAL column. Specifically: String '0a' implicitly converts to numeric 0 during TEXT to DECIMAL comparison in MySQL. On a table without a UNIQUE index (d_raw), performing JOIN ON d_raw.t1.c = d_raw.t0.c uses full scan/hash join, correctly truncates '0a' to 0, and returns 1 row. On a table with a UNIQUE index (d), performing JOIN ON d.t1.c = d.t0.c uses index lookup on d.t0, fails to match '0a' with 0, and incorrectly returns Empty set, 1 warning. How to repeat: DROP DATABASE IF EXISTS test_idx; DROP DATABASE IF EXISTS test_raw; -- Case 1: Table with UNIQUE index CREATE DATABASE test_idx; USE test_idx; CREATE TABLE t0 (c DECIMAL UNIQUE); CREATE TABLE t1 (c TEXT); INSERT INTO t0 VALUES (0); INSERT INTO t1 VALUES ('0a'); -- Query on table with UNIQUE index SELECT 1 FROM t1 JOIN t0 ON t1.c = t0.c; -- Returns: Empty set (Incorrect index lookup behavior) -- Case 2: Table without UNIQUE index CREATE DATABASE test_raw; USE test_raw; CREATE TABLE t0 (c DECIMAL); CREATE TABLE t1 (c TEXT); INSERT INTO t0 VALUES (0); INSERT INTO t1 VALUES ('0a'); -- Query on table without index SELECT 1 FROM t1 JOIN t0 ON t1.c = t0.c; -- Returns: 1 row (Correct implicit conversion behavior)