Bug #114149 Incorrect query result caused by where optimization
Submitted: 28 Feb 3:20 Modified: 28 Feb 5:51
Reporter: Ye Shiyang Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:8.0+ OS:Linux
Assigned to: CPU Architecture:Any
Tags: DATE function

[28 Feb 3:20] Ye Shiyang
Description:
Sql1 and sql2 should return the same result if condition f1 in sql2 is true. Otherwise,sql2 should return nothing.

However,sql1 and sql2 return different value.

--sql
select f1 from(SELECT (t0.c0 - SUBDATE('2022-07-06' ,INTERVAL 47 MINUTE)) AS f1 FROM t0) as t;

--sql2
select f1 from(SELECT (t0.c0 - SUBDATE('2022-07-06' ,INTERVAL 47 MINUTE)) AS f1 FROM t0) as t where f1;

How to repeat:
CREATE TABLE IF NOT EXISTS t0(c0 SMALLINT(157)); 
INSERT INTO t0(c0) VALUES(1); 

select f1 from(SELECT (t0.c0 - SUBDATE('2022-07-06' ,INTERVAL 47 MINUTE)) AS f1 FROM t0) as t;

f1
-20220705231299

select f1 from(SELECT (t0.c0 - SUBDATE('2022-07-06' ,INTERVAL 47 MINUTE)) AS f1 FROM t0) as t where f1;

f1
-2021

Suggested fix:
the result of sql and sql2 should have the same precision.
[28 Feb 5:51] MySQL Verification Team
Hello Ye Shiyang,

Thank you for the report and feedback

regards,
Umesh