Bug #120534 Wrong Query Result Due to Incorrect Evaluation of GREATEST() with Mixed Types in OR (||) Boolean Context
Submitted: 24 May 5:19 Modified: 26 May 12:26
Reporter: Jason Tang Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:8.0.45 OS:Any
Assigned to: CPU Architecture:Any

[24 May 5:19] Jason Tang 
Description:
When using GREATEST() with mixed integer/string/float arguments in a boolean expression combined with OR (||), MySQL returns incorrect empty result set for the negated condition and incorrectly filters rows for the positive condition.

How to repeat:
DROP DATABASE IF EXISTS database0;
CREATE DATABASE database0;
USE database0;

CREATE TABLE `t0` (
  `c0` mediumint(75) unsigned zerofill DEFAULT NULL COMMENT 'asdf',
  KEY `i0` (`c0`) USING BTREE
);

INSERT INTO t0(c0) VALUES (0), (1);

-- Query 1 returns wrong empty set
SELECT ALL t0.c0 AS ref0
FROM t0
WHERE NOT( (GREATEST(t0.c0, t0.c0, '-947119747', 0.259620556843871)) || (t0.c0) );

-- Query 2 returns wrong partial result
SELECT ALL t0.c0 AS ref0
FROM t0
WHERE ( (GREATEST(t0.c0, t0.c0, '-947119747', 0.259620556843871)) || (t0.c0) );

Expected Output
Both queries should return both rows (0 and 1) because the OR condition is always true when at least one input is non-zero.
[26 May 12:26] Roy Lyseng 
Thank you for the bug report.
Verified as described.