Bug #117714 Incorrect row filtering when using CAST(... AS DATE) adding an AND condition.

• Submitted: 14 Mar 15:36
• Modified: 18 Mar 13:24
• Reporter: Xenovate Xenovate
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S2 (Serious)
• Version: 5.7.42
• OS: Any
• CPU Architecture: Any

[14 Mar 15:36] Xenovate Xenovate

Description:
CREATE TABLE t0(c0 DOUBLE );
CREATE TABLE t1(c0 TEXT(128) );

INSERT INTO t1(c0) VALUES ('0.9');
INSERT INTO t1 VALUES ('3');

INSERT INTO t0(c0) VALUES (0.159);
INSERT INTO t0(c0) VALUES (1051);

select t0.c0,((CAST(t0.c0 AS DATE)) is NULL) from t0;
--Expect Result--
0.159, 0
1051, 1

select * from t1,t0 where (((CAST(t0.c0 AS DATE)) IS NULL));
--Expect Result--
t1.c0, t0.c0
0.9, 1051
3, 1051

SELECT * FROM  t1,t0 where (((((CAST(t0.c0 AS DATE)) IS NULL))AND(t1.c0)));
--Unexpected Result(Only produce 1 row)--
t1.c0, t0.c0
3, 1051

How to repeat:
CREATE TABLE t0(c0 DOUBLE );
CREATE TABLE t1(c0 TEXT(128) );

INSERT INTO t1(c0) VALUES ('0.9');
INSERT INTO t1 VALUES ('3');

INSERT INTO t0(c0) VALUES (0.159);
INSERT INTO t0(c0) VALUES (1051);

select t0.c0,((CAST(t0.c0 AS DATE)) is NULL) from t0;
--Expect Result--
0.159, 0
1051, 1

select * from t1,t0 where (((CAST(t0.c0 AS DATE)) IS NULL));
--Expect Result--
t1.c0, t0.c0
0.9, 1051
3, 1051

SELECT * FROM  t1,t0 where (((((CAST(t0.c0 AS DATE)) IS NULL))AND(t1.c0)));
--Unexpected Result(Only produce 1 row)--
t1.c0, t0.c0
3, 1051

[18 Mar 13:24] MySQL Verification Team

Thank you for the report.