Description:
Derived condition pushdown return wrong results with view.
How to repeat:
CREATE TABLE `t1` (
`c_id` int(255) unsigned zerofill NOT NULL
) ENGINE=InnoDB;
INSERT INTO `t1` VALUES (298);
CREATE TABLE `t2` (
`c_id` int(255) unsigned zerofill NOT NULL,
`c_first` varchar(255) DEFAULT NULL,
`c_discount` decimal(62,4) DEFAULT NULL,
`c_ytd_payment` decimal(62,2) DEFAULT NULL,
PRIMARY KEY (`c_id`)
) ENGINE=InnoDB;
CREATE TABLE `t3` (
`C_BIT` bit(1) DEFAULT NULL
) ENGINE=InnoDB;
INSERT INTO `t3` VALUES (NULL);
create view tx_cmplx_003 as select subq_1.c1 as c0, subq_1.c0 as c1, subq_1.c0 as c2, subq_1.c0 as c3, subq_1.c0 as c4, subq_1.c0 as c5, subq_1.c1 as c6, subq_1.c1 as c7, subq_1.c0 as c8, subq_1.c1 as c9, subq_1.c0 as c10, subq_1.c0 as c11 from (select pi() as c0, subq_0.c1 as c1 from t3 as ref_0 left join (select ref_1.c_ytd_payment as c0, pi() as c1, ref_1.c_discount as c2 from t2 as ref_1 where ref_1.c_first is NULL ) as subq_0 on (subq_0.c0 is not NULL) where true ) as subq_1 where true group by 1,2,3,4,5,6,7,8,9,10,11,12 order by 1,2,3,4,5,6,7,8,9,10,11,12;
mysql> set optimizer_switch="derived_condition_pushdown=on";
Query OK, 0 rows affected (0.00 sec)
mysql> select 1 as c0 from t1 as ref_4 inner join tx_cmplx_003 as ref_5 where (ref_5.c6 is not NULL);
+----+
| c0 |
+----+
| 1 |
+----+
1 row in set, 12 warnings (0.00 sec)
mysql> set optimizer_switch="derived_condition_pushdown=off";
Query OK, 0 rows affected (0.00 sec)
mysql> select 1 as c0 from t1 as ref_4 inner join tx_cmplx_003 as ref_5 where (ref_5.c6 is not NULL);
Empty set, 12 warnings (0.00 sec)
