Bug #118348 using force index(i0) and ignore index(i0) for the same query, the result set changed

• Submitted: 4 Jun 1:52
• Modified: 4 Jun 11:35
• Reporter: SamonBing SamonBing
• Status: Duplicate
• Category: MySQL Server: DML
• Severity: S3 (Non-critical)
• Version: 8.0.41
• OS: Any
• CPU Architecture: Any

[4 Jun 1:52] SamonBing SamonBing

Description:
using force index(i0) and ignore index(i0)  for the same query, the result set changed.

mysql> SELECT ALL t1.c0 AS ref0, t1.c1 AS ref1, t1.c2 AS ref2, t1.c3 AS ref3 FROM t1 ignore index(i0) WHERE (t1.c0) IN (LEAST(444064219, 6.9996631E7, 0.5782522447705409, 0.45194499169354196));
Empty set (0.00 sec)

mysql> SELECT ALL t1.c0 AS ref0, t1.c1 AS ref1, t1.c2 AS ref2, t1.c3 AS ref3 FROM t1 force index(i0) WHERE (t1.cc0) IN (LEAST(444064219, 6.9996631E7, 0.5782522447705409, 0.45194499169354196));
+------+------------+------+-----------+
| ref0 | ref1       | ref2 | ref3      |
+------+------------+------+-----------+
|  000 | 0000000001 | NULL | 267008705 |
+------+------------+------+-----------+

How to repeat:
CREATE TABLE `t1` (
  `c0` tinyint(3) unsigned zerofill DEFAULT NULL,
  `c1` decimal(10,0) unsigned zerofill DEFAULT NULL COMMENT 'asdf',
  `c2` double unsigned zerofill DEFAULT NULL,
  `c3` decimal(10,0) /*!50606 COLUMN_FORMAT DYNAMIC */ DEFAULT NULL COMMENT 'asdf',
  UNIQUE KEY `c3` (`c3`),
  UNIQUE KEY `i0` (`c0`,`c3`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci STATS_AUTO_RECALC=0 CHECKSUM=1 COMPRESSION='NONE';

insert into t1 values(000,0000000001,NULL,267008705);

mysql> SELECT ALL t1.c0 AS ref0, t1.c1 AS ref1, t1.c2 AS ref2, t1.c3 AS ref3 FROM t1 ignore index(i0) WHERE (t1.c0) IN (LEAST(444064219, 6.9996631E7, 0.5782522447705409, 0.45194499169354196));
Empty set (0.00 sec)

mysql> SELECT ALL t1.c0 AS ref0, t1.c1 AS ref1, t1.c2 AS ref2, t1.c3 AS ref3 FROM t1 force index(i0) WHERE (t1.cc0) IN (LEAST(444064219, 6.9996631E7, 0.5782522447705409, 0.45194499169354196));
+------+------------+------+-----------+
| ref0 | ref1       | ref2 | ref3      |
+------+------------+------+-----------+
|  000 | 0000000001 | NULL | 267008705 |
+------+------------+------+-----------+

Suggested fix:
the query using force index(i0), returned a wrong result.

[4 Jun 8:32] SamonBing SamonBing

It is suspected that the problem is caused by a logic bug in the processing of floating numbers in the LEAST function.

such as the 2 queries:
mysql> SELECT ALL t1.c0 AS ref0, t1.c1 AS ref1, t1.c2 AS ref2, t1.c3 AS ref3 FROM t1 force index(i0) WHERE (t1.c0) IN (LEAST(2,0.3));
+------+------------+------+-----------+
| ref0 | ref1       | ref2 | ref3      |
+------+------------+------+-----------+
|  000 | 0000000001 | NULL | 267008705 |
+------+------------+------+-----------+
1 row in set (0.00 sec)

mysql> SELECT ALL t1.c0 AS ref0, t1.c1 AS ref1, t1.c2 AS ref2, t1.c3 AS ref3 FROM t1 force index(i0) WHERE (t1.c0) IN (LEAST(2,3));
Empty set (0.00 sec)

[4 Jun 11:35] MySQL Verification Team

Hello SamonBing,

Thank you for the bug report.
Imho this is duplicate of Bug #118092, please see Bug #118092.

Regards,
Ashwini Patil