Bug #119526 Inconsistent query results caused by the NO_MERGE hint

• Submitted: 9 Dec 3:13
• Modified: 10 Dec 11:59
• Reporter: Go Yakult
• Status: Need Feedback
• Category: MySQL Server: Optimizer
• Severity: S2 (Serious)
• Version: 8.0.41
• OS: Any
• CPU Architecture: Any

[9 Dec 3:13] Go Yakult

Description:
mysql> SELECT v0.c0 AS ref0 FROM v0 WHERE GREATEST(v0.c0, 0.29013432092139513, '2N|Lmozh');
Empty set (0.00 sec)

mysql> SELECT /*+ NO_MERGE(v0)*/ ALL v0.c0 AS ref0 FROM v0 WHERE GREATEST(v0.c0, 0.29013432092139513, '2N|Lmozh'); 
+------+
| ref0 |
+------+
| NULL |
+------+
1 row in set (0.00 sec)

How to repeat:
DROP TABLE IF EXISTS `t0`;
CREATE TABLE `t0` (
  `c0` double unsigned zerofill /*!50606 STORAGE MEMORY */ DEFAULT NULL
)CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
insert into t0 values(NULL);

SELECT v0.c0 AS ref0 FROM v0 WHERE GREATEST(v0.c0, 0.29013432092139513, '2N|Lmozh');
SELECT /*+ NO_MERGE(v0)*/ ALL v0.c0 AS ref0 FROM v0 WHERE GREATEST(v0.c0, 0.29013432092139513, '2N|Lmozh');

[10 Dec 11:59] Roy Lyseng

I cannot repeat the problem.
Is the test case correct, it references both t0 and v0, but only t0 is created?