CREATE TABLE p1 (id int, p_name char(2), o_id int);

INSERT INTO p1 VALUES(1,'p1',1),(2,'p2',2);

CREATE TABLE t1 (id int, t_name char(2));

INSERT INTO t1 VALUES (1, 't1'),(2,'t2');

CREATE TABLE f1 (id int, p_id int, t_id int, b_id int);

INSERT INTO f1 VALUES (1,1,1,2),(2,1,2,2),(3,2,1,1),(4,2,2,2);

CREATE TABLE o1 (id int, o_name char(2));

INSERT INTO o1 VALUES (1, 'aa'), (2,'bb');

CREATE VIEW v1 AS SELECT 

                         p_name,

                         o_id,

                         f1.id AS f_id,

                         p_id,

                         t_id,

                         b_id FROM p1 JOIN f1 ON p1.id = f1.p_id;

CREATE VIEW v2 AS SELECT 

                         p_name,

                         o_id,

                         f1.id AS f_id,

                         p_id,

                         t_id,

                         b_id,

                         o_name FROM p1 JOIN f1 ON p1.id = f1.p_id JOIN o1 ON o1.id = p1.o_id GROUP BY p_id;

//unexpected output in 10.3: 0 rows

//normal output in 10.2: 2 rows

SELECT v2.p_name,

       t.t_name,

       b.t_name AS b_name

  FROM v2 

  JOIN v1 ON v2.f_id = v1.f_id

  LEFT JOIN o1 AS o ON o.id = v2.o_id  

  LEFT JOIN t1 AS t ON t.id = v1.t_id

  LEFT JOIN t1 AS b ON b.id = v1.b_id

WHERE IF('a' LIKE 'a',t.id = 1 OR b.id = 1, 1=1);

//solution1: drop the IF-Condition in WHERE-statement

SELECT v2.p_name,

       t.t_name,

       b.t_name AS b_name

  FROM v2 

  JOIN v1 ON v2.f_id = v1.f_id

  LEFT JOIN o1 AS o ON o.id = v2.o_id  

  LEFT JOIN t1 AS t ON t.id = v1.t_id

  LEFT JOIN t1 AS b ON b.id = v1.b_id

WHERE t.id = 1 OR b.id = 1;

//Solution2: o1-join after t1-joins

SELECT v2.p_name,

       t.t_name,

       b.t_name AS b_name

  FROM v2 

  JOIN v1 ON v2.f_id = v1.f_id

  #LEFT JOIN o1 AS o ON o.id = v2.o_id  

  LEFT JOIN t1 AS t ON t.id = v1.t_id

  LEFT JOIN t1 AS b ON b.id = v1.b_id

  LEFT JOIN o1 AS o ON o.id = v2.o_id 

WHERE IF('a' LIKE 'a',t.id = 1 OR b.id = 1,1=1);

//solution3: Change of the If-Condition

SELECT v2.p_name,

       t.t_name,

       b.t_name AS b_name

  FROM v2 

  JOIN v1 ON v2.f_id = v1.f_id

  LEFT JOIN o1 AS o ON o.id = v2.o_id  

  LEFT JOIN t1 AS t ON t.id = v1.t_id

  LEFT JOIN t1 AS b ON b.id = v1.b_id

WHERE IF('a' LIKE 'a',t.id = 1,1=1) OR IF('a' LIKE 'a',b.id = 1, 1=1);

//Cleanup

DROP TABLE p1;

DROP TABLE t1;

DROP TABLE f1;

DROP TABLE o1;

DROP VIEW v1;

DROP VIEW v2;

commit 0de3c423cc51d362c3a9b4dc8fa90d549ce0754e

Author: Galina Shalygina <galina.shalygina@mariadb.com>

Date:   Mon Aug 20 17:42:49 2018 +0300

    MDEV-16765: Missing rows with pushdown condition defined with CASE using Item_cond

    The bug appears because of the wrong pushdown into the WHERE clause of the

    materialized derived table/view work. For the excl_dep_on_grouping_fields()

    method that checks if the condition can be pushed into the WHERE clause

    the case when Item_cond is used is missing. For Item_cond elements this

    method always returns positive result (that condition can be pushed).

    So this condition is pushed even if is shouldn't be pushed.

    To fix it new Item_cond::excl_dep_on_grouping_fields() method is added.