Bug #114149 Incorrect query result caused by where optimization

• Submitted: 28 Feb 2024 3:20
• Modified: 28 Feb 2024 5:51
• Reporter: Ye Shiyang
• Status: Verified
• Category: MySQL Server: DML
• Severity: S3 (Non-critical)
• Version: 8.0+
• OS: Linux
• CPU Architecture: Any
• Tags: DATE function

[28 Feb 2024 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 2024 5:51] MySQL Verification Team

Hello Ye Shiyang,

Thank you for the report and feedback

regards,
Umesh