Bug #119132 Unexpected query result when using complex boolean expressions with UNION ALL and LIKE in SELECT query
Submitted: 11 Oct 3:44
Reporter: Alice Alice Email Updates:
Status: Open Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:8.0.41 OS:Any
Assigned to: CPU Architecture:Any

[11 Oct 3:44] Alice Alice 
Description:
Both queries should return the same result set, but one record with a value of 0 is missing.

mysql> SELECT ALL t0.c0 AS ref0 FROM t0 WHERE (((! (1593547797))) LIKE (IF(t0.c0, t0.c0, t0.c0))) || (t0.c0) UNION ALL SELECT t0.c0 AS ref0 FROM t0 WHERE (NOT ((((! (1593547797))) LIKE (IF(t0.c0, t0.c0, t0.c0))) || (t0.c0))) UNION ALL SELECT t0.c0 AS ref0 FROM t0 WHERE ((((! (1593547797))) LIKE (IF(t0.c0, t0.c0, t0.c0))) || (t0.c0)) IS UNKNOWN;
+------------+
| ref0       |
+------------+
| 1769363846 |
|  993558909 |
|       NULL |
+------------+
3 rows in set, 6 warnings (0.00 sec)

mysql> SELECT ALL t0.c0 AS ref0 FROM t0;
+----------------------+
| ref0                 |
+----------------------+
| 00000000001769363846 |
| 00000000000993558909 |
| 00000000000000000000 |
|                 NULL |
+----------------------+
4 rows in set (0.00 sec)

How to repeat:
CREATE TABLE `t0` (
  `c0` bigint(20) unsigned zerofill DEFAULT NULL,
  UNIQUE KEY `i1` (`c0` DESC) USING BTREE ,
  UNIQUE KEY `i0` (`c0` DESC)
);

INSERT INTO `t0` VALUES (1769363846),(993558909),(0),(NULL);