Bug #120669 Incorrect result for `CASE + LIKE + XOR` with `||`

• Submitted: 13 Jun 17:29
• Reporter: mu mu
• Status: Open
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 9.6.0
• OS: Ubuntu
• CPU Architecture: Any

[13 Jun 17:29] mu mu

Description:
On identical table data, a `WHERE` clause combining `||`, `XOR`, `LIKE`, and a multi-branch `CASE` over three columns should return **5** rows, but MySQL 9.6.0 returns **2** rows.

How to repeat:
DROP TABLE IF EXISTS `t0`;

CREATE TABLE `t0` (
  `c0` decimal(10,0) unsigned zerofill DEFAULT NULL,
  `c1` float   DEFAULT NULL COMMENT 'asdf',
  `c2` bigint(20) unsigned zerofill  DEFAULT NULL,
  UNIQUE KEY `c1` (`c1`),
  KEY `i0` (`c1`,`c2`) ,
  KEY `i1` (`c0` DESC,`c2` DESC) USING BTREE
) CHECKSUM=1;

INSERT INTO `t0` (`c0`, `c1`, `c2`) VALUES ('0', 0.561162, 1273997785);
INSERT INTO `t0` (`c0`, `c1`, `c2`) VALUES ('0', 0.0138515, 0);
INSERT INTO `t0` (`c0`, `c1`, `c2`) VALUES ('0', NULL, 0);
INSERT INTO `t0` (`c0`, `c1`, `c2`) VALUES ('0', 0.0, 1094282015);
INSERT INTO `t0` (`c0`, `c1`, `c2`) VALUES ('0', NULL, 0);

SELECT ALL t0.c0 AS ref0, t0.c1 AS ref1, t0.c2 AS ref2 FROM t0 WHERE (t0.c2) || ((('He
O') XOR (t0.c0)) LIKE ((CASE t0.c0 WHEN t0.c0 THEN t0.c2 WHEN t0.c2 THEN t0.c0 WHEN NULL THEN NULL WHEN t0.c2 THEN t0.c0 WHEN NULL THEN t0.c1 ELSE -819716203 END)));