MySQL Bugs: #119132: Unexpected query result when using complex boolean expressions with UNION ALL and LIKE in SELECT query

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

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);

Thank you, Alice, for your report.

It seems the second query block returns the wrong result:

mysql > SELECT t0.c0 AS ref0 FROM t0 WHERE (NOT ((((! (1593547797))) LIKE (IF(t0.c0, t0.c0, t0.c0))) || (t0.c0)));
Empty set, 2 warnings (0.00 sec)

Evaluating the where expression indicates that the above query should return one row:

mysql >  SELECT (NOT ((((! (1593547797))) LIKE (IF(t0.c0, t0.c0, t0.c0))) || (t0.c0))) FROM t0;
+------------------------------------------------------------------------+
| (NOT ((((! (1593547797))) LIKE (IF(t0.c0, t0.c0, t0.c0))) || (t0.c0))) |
+------------------------------------------------------------------------+
|                                                                      0 |
|                                                                      0 |
|                                                                      1 |
|                                                                   NULL |
+------------------------------------------------------------------------+
4 rows in set, 2 warnings (0.00 sec)