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)