Description:
Result mismatch when a condition is pushed down to inner tables.
Set "derived_condition_pushdown=off" will solve this problem, but it will bring a huge performance loss.
########
Have result, which is wrong.
set optimizer_switch="derived_condition_pushdown=on";
select 1 as c0, ref_5.c2 from t1 as ref_4 inner join view_1 as ref_5 where (ref_5.c2 is not NULL);
########
Have No result, which is right.
set optimizer_switch="derived_condition_pushdown=off";
select 1 as c0, ref_5.c2 from t1 as ref_4 inner join view_1 as ref_5 where (ref_5.c2 is not NULL);
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 view_1
AS
SELECT subq_1.c0 AS c0, subq_1.c0 AS c1, subq_1.c1 AS c2, subq_1.c1 AS c3
FROM (
SELECT pi() AS c0, subq_0.c1 AS c1
FROM t3 ref_0
LEFT JOIN (
SELECT ref_1.c_ytd_payment AS c0, pi() AS c1, ref_1.c_discount AS c2
FROM t2 ref_1
WHERE ref_1.c_first IS NULL
) subq_0
ON subq_0.c0 IS NOT NULL
WHERE true
) subq_1
WHERE true
GROUP BY 1, 2, 3, 4
ORDER BY 1, 2, 3, 4;
set optimizer_switch="derived_condition_pushdown=on";
select 1 as c0, ref_5.c2 from t1 as ref_4 inner join view_1 as ref_5 where (ref_5.c2 is not NULL);
set optimizer_switch="derived_condition_pushdown=off";
select 1 as c0, ref_5.c2 from t1 as ref_4 inner join view_1 as ref_5 where (ref_5.c2 is not NULL);
Suggested fix:
The result should be the same whether the derived_condition_pushdown is on or off.
The logic of pushing down conditions that contain reference columns to the derived table may have a bug.
