Bug #117714 Incorrect row filtering when using CAST(... AS DATE) adding an AND condition.
Submitted: 14 Mar 15:36
Reporter: shannon shannon Email Updates:
Status: Open Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S2 (Serious)
Version:5.7.42 OS:Any
Assigned to: CPU Architecture:Any

[14 Mar 15:36] shannon shannon
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