Bug #120582 unique index causes incorrect result when comparing DOUBLE and BIGINT

• Submitted: 31 May 10:11
• Reporter: fan liu
• Status: Open
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 8.0.44
• OS: MacOS
• CPU Architecture: Any

[31 May 10:11] fan liu

Description:
A join between a DOUBLE column and a BIGINT column returns an empty set
when a UNIQUE index exists. After dropping the index, a matching row is
returned.
SELECT t1.c0, t0.c1 FROM t1 STRAIGHT_JOIN t0 WHERE t1.c0 = t0.c1; 
empty set;
drop index c1 on t0;
SELECT t1.c0, t0.c1 FROM t1 STRAIGHT_JOIN t0 WHERE t1.c0 = t0.c1;（2098885691505768900,209888569150576900）

How to repeat:
DROP DATABASE IF EXISTS test;
CREATE DATABASE test;
USE test;
CREATE TABLE `t0` (
  `c1` bigint,
  UNIQUE KEY `c1` (`c1`)
) ENGINE=MEMORY;
CREATE TABLE `t1` (
  `c0` double  ,
  `c1` bigint ,
  `c2` float 
) ENGINE=MEMORY;
INSERT INTO t1(c0) VALUES(209888569150576900);
INSERT INTO t0(c1) VALUES("209888569150576900");
INSERT INTO t0(c1) VALUES(NULL);
SELECT t1.c0, t0.c1 FROM t1 STRAIGHT_JOIN t0 WHERE t1.c0 = t0.c1;
drop index c1 on t0;
SELECT t1.c0, t0.c1 FROM t1 STRAIGHT_JOIN t0 WHERE t1.c0 = t0.c1;