Bug #118571 using index lookup, the query return a wrong result;
Submitted: 2 Jul 7:27 Modified: 2 Jul 8:56
Reporter: SamonBing SamonBing Email Updates:
Status: Analyzing Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:8.0.41 OS:Any
Assigned to: MySQL Verification Team CPU Architecture:Any

[2 Jul 7:27] SamonBing SamonBing
Description:
using index lookup, the query return a wrong result;
after dropping the index, the same query return a right result.

How to repeat:
drop table if exists test_table;
CREATE TABLE `test_table` (
  `c0` tinyint(1) NOT NULL,
  KEY `i0` (`c0`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

INSERT INTO `test_table` VALUES (0),(0),(0);
/*!40000 ALTER TABLE `test_table` ENABLE KEYS */;

-- return a wrong result
SELECT test_table.c0,((SIN(1742704984))=(test_table.c0)) FROM test_table WHERE ((SIN(1742704984))=(test_table.c0)) GROUP BY test_table.c0 HAVING (NOT (MAX((BINARY (test_table.c0)))));

mysql> SELECT test_table.c0,((SIN(1742704984))=(test_table.c0)) FROM test_table WHERE ((SIN(1742704984))=(test_table.c0)) GROUP BY test_table.c0 HAVING (NOT (MAX((BINARY (test_table.c0)))));
+----+-------------------------------------+
| c0 | ((SIN(1742704984))=(test_table.c0)) |
+----+-------------------------------------+
|  0 |                                   0 |
+----+-------------------------------------+

alter table test_table drop index i0;

-- return a right result
SELECT test_table.c0,((SIN(1742704984))=(test_table.c0)) FROM test_table WHERE ((SIN(1742704984))=(test_table.c0)) GROUP BY test_table.c0 HAVING (NOT (MAX((BINARY (test_table.c0)))));

mysql> SELECT test_table.c0,((SIN(1742704984))=(test_table.c0)) FROM test_table WHERE ((SIN(1742704984))=(test_table.c0)) GROUP BY test_table.c0 HAVING (NOT (MAX((BINARY (test_table.c0)))));
Empty set, 1 warning (0.00 sec)