Bug #120674 Incorrect result for `CASE with EXISTS` compared to column

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

[13 Jun 17:32] mu mu

Description:
On identical table data, `WHERE (CASE ... WHEN BIT_COUNT(NULL) THEN EXISTS(SELECT 1) ELSE '0xR' END) = (t0.c0)` should return **2** rows, but MySQL 9.6.0 returns **0** rows.

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

CREATE TABLE t0 (
  c0 decimal(10,0) unsigned zerofill DEFAULT NULL,
  KEY i0 (c0 DESC),
  KEY i1 (c0) USING BTREE
) PACK_KEYS=1 STATS_AUTO_RECALC=0 CHECKSUM=1;

INSERT INTO `t0` (`c0`) VALUES ('141497100');
INSERT INTO `t0` (`c0`) VALUES ('1');
INSERT INTO `t0` (`c0`) VALUES ('1');
INSERT INTO `t0` (`c0`) VALUES ('1');
INSERT INTO `t0` (`c0`) VALUES ('0');
INSERT INTO `t0` (`c0`) VALUES ('0');
INSERT INTO `t0` (`c0`) VALUES (NULL);
INSERT INTO `t0` (`c0`) VALUES (NULL);
INSERT INTO `t0` (`c0`) VALUES (NULL);
INSERT INTO `t0` (`c0`) VALUES (NULL);
INSERT INTO `t0` (`c0`) VALUES (NULL);
INSERT INTO `t0` (`c0`) VALUES (NULL);

SELECT ALL t0.c0 AS ref0 FROM t0 WHERE ((CASE (NULL) >= (NULL) WHEN BIT_COUNT(NULL) THEN  EXISTS (SELECT 1) ELSE '0xR' END)) = (t0.c0);