Bug #120670 Incorrect result for `NOT(...) && GREATEST(...) AND literal`

• Submitted: 13 Jun 17:30
• Modified: 16 Jun 6:38
• Reporter: mu mu
• Status: Not a Bug
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 9.6.0
• OS: Ubuntu
• CPU Architecture: Any

[13 Jun 17:30] mu mu

Description:
On identical table data, `WHERE NOT((NOT(c0)) && GREATEST(c0, ...) AND 0.42641949642449517)` should return **3** rows, but MySQL 9.6.0 returns **4** rows.

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

CREATE TABLE `t0` (
  `c0` int(146) unsigned zerofill DEFAULT NULL COMMENT 'asdf',
  UNIQUE KEY `c0` (`c0`),
  UNIQUE KEY `i1` (`c0`),
  UNIQUE KEY `i0` ((least(1.25936934E8,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);
INSERT INTO `t0` (`c0`) VALUES (0);
INSERT INTO `t0` (`c0`) VALUES (153934357);
INSERT INTO `t0` (`c0`) VALUES (396613325);
INSERT INTO `t0` (`c0`) VALUES (1185140620);

SELECT ALL t0.c0 AS ref0 FROM t0 WHERE (! ((((! (t0.c0))) && (GREATEST(t0.c0, 0.7678046017515187, -875150723, ''))) AND (0.42641949642449517)));

[16 Jun 6:38] Chaithra Marsur Gopala Reddy

Hi mu mu,

This is not a bug. The result depends on GREATEST(c0, ...) with c0 defined as INT UNSIGNED ZEROFILL. In this case it affects the expression result type/value. For c0 = 0, MySQL returns the ZEROFILL-formatted column value from GREATEST(...), which makes the predicate evaluate to true, so returning 4 rows is expected.